Имеет ли смысл использовать индекс, который будет иметь низкую мощность?

Я в основном разработчик ActionScript и ни в коем случае не специалист в SQL, но время от времени мне приходится разрабатывать простые серверные вещи. Итак, я думал, что попрошу более опытных людей по поводу вопроса в названии.

Мое понимание заключается в том, что вы не получаете многого, указав индекс в столбце, который будет содержать несколько разных значений. У меня есть столбец с вещественным значением (на самом деле это небольшой int, но я использую его как флаг), и этот столбец используется в предложениях WHERE большинства запросов, которые у меня есть. В теоретическом "среднем" случае половина значений записей будет равна 1, а другая половина - 0. Таким образом, в этом случае механизм базы данных мог бы избежать полного сканирования таблицы, но в любом случае ему придется читать много строк (всего строк /2).

Итак, должен ли я сделать этот столбец индексом?

Для записи я использую Mysql 5, но меня больше интересует общее обоснование того, почему оно делает/не имеет смысла индексировать столбец, который я знаю, который будет иметь низкую мощность.

Спасибо заранее.

Ответы

Ответ 1

Индекс может помочь даже при низких значениях мощности, если:

  • Если одно из возможных значений очень редко по сравнению с другими значениями и вы его ищете.

    Например, очень мало слепых женщин, поэтому этот запрос:

    SELECT  *
FROM    color_blind_people
WHERE   gender = 'F'

    скорее всего, выиграет от индекса на gender.

  • Когда значения, как правило, группируются в таблице порядка:

    SELECT  *
FROM    records_from_2008
WHERE   year = 2010
LIMIT 1

    Хотя здесь существует только 3 отчетных лет, записи с более ранними годами, скорее всего, добавлены во-первых, поэтому очень многие записи должны быть отсканированы до возвращения первой записи 2010, если не для индекса.

  • Когда вам нужно ORDER BY / LIMIT:

    SELECT  *
FROM    people
ORDER BY
        gender, id
LIMIT 1

    Без индекса требуется a filesort. Хотя он несколько оптимизирован для LIMIT, ему все равно потребуется полное сканирование таблицы.

  • Когда индекс охватывает все поля, используемые в запросе:

    CREATE INDEX (low_cardinality_record, value)

SELECT  SUM(value)
FROM    mytable
WHERE   low_cardinality_record = 3

  • Когда вам понадобится DISTINCT:

    SELECT  DISTINCT color
FROM    tshirts

    MySQL будет использовать INDEX FOR GROUP-BY, и если у вас мало цветов, этот запрос будет мгновенным даже с миллионами записей.

    Это пример сценария, когда индекс в поле с малой мощностью более эффективен, чем индекс в поле с высокой мощностью.

Обратите внимание, что если производительность DML не сильно зависит от проблемы, тогда безопасно создавать индекс.

Если оптимизатор считает, что индекс неэффективен, индекс просто не будет использоваться.

Ответ 2

Возможно, стоит добавить логическое поле в составной индекс. Например, если у вас есть большая таблица сообщений, которую обычно нужно заказывать по дате, но у вас также есть логическое поле "Удалено", поэтому вы часто запрашиваете ее так:

SELECT ... FROM Messages WHERE Deleted = 0 AND Date BETWEEN @start AND @end

Вы, безусловно, выиграете от наличия составного индекса в полях Deleted и Date.

Ответ 3

Я обычно делаю простой индекс "иметь индекс" и "не имею" индексный тест. По моему опыту вы получаете большую часть производительности по запросам, которые используют ORDER BY индексированным столбцом. В случае, если у вас есть сортировка по этой колонке, скорее всего, поможет индексирование.

Ответ 4

ИМХО это ограниченная полезность. Я предполагаю, что в большинстве случаев есть другие критерии, которые вы используете в своих запросах в дополнение к флагом, которые, вероятно, помогают намного больше.

В 50%, я бы, наверное, сделал бенчмаркинг с/без, и посмотрим, имеет ли это значение.

Ответ 5

Когда половина значений записей будет равна 1, а другая половина - 0, нет смысла ставить индекс для этого столбца. Оптимизатор запросов скорее всего не использует его.

Однако, как правило, у вас есть небольшой набор "активных" записей и все более широкий набор "неактивных". Например, в системе отслеживания ошибок вы заботитесь об активных ошибках и вряд ли каждый смотрит на завершенные и заархивированные. Для такого случая хитрость заключается в том, чтобы использовать столбец "dateInactivation", в котором хранится метка времени, когда запись была деактивирована/удалена. Как следует из названия, значение равно NULL, когда запись активна, но после деактивации запишите в системную дату и время. Таким образом, индекс в этом столбце имеет высокую селективность по мере увеличения числа "удаленных" записей, поскольку каждая запись будет иметь уникальное (не строго говоря) значение. Запрос будет иметь

"... AND dateInactivated is NULL ..."

как часть предиката, и индекс будет тянуть только правильный набор строк, которые вас интересуют.