Индексы MySQL - каковы лучшие практики?

Эффективная загрузка данных : индексы ускоряют извлечение, но замедляют вставку и удаление, а также обновление значений в индексированных столбцах. То есть индексы замедляют большинство операций, связанных с записью. Это происходит потому, что запись строки требует записи не только строки данных, но и изменения любых индексов. Чем больше индексов имеет таблица, тем больше изменений необходимо внести и тем больше среднее снижение производительности. Большинство таблиц получают много чтений и мало записей, но для таблицы с высоким процентом записи стоимость обновления индекса может быть значительной.

Избегайте индексов : если вам не нужен конкретный индекс для повышения производительности запросов, не создавайте его.

Дисковое пространство : индекс занимает дисковое пространство, а несколько индексов, соответственно, занимают больше места. Это может привести к более быстрому достижению предельного размера таблицы, чем при отсутствии индексов. По возможности избегайте индексов.

Вывод: не переборщить с индексом

Я не буду повторять некоторые полезные советы в других ответах, но добавлю:

Составные индексы

Вы можете создавать составные индексы - индекс, который включает несколько столбцов. MySQL может использовать их от слева до справа . Итак, если у вас есть:

Table A
Id
Name
Category
Age
Description

, если у вас есть составной индекс, который включает имя / категорию / возраст в этом порядке, эти предложения WHERE будут использовать индекс:

WHERE Name='Eric' and Category='A'

WHERE Name='Eric' and Category='A' and Age > 18

, но

WHERE Category='A' and Age > 18

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

Explain

Используйте Explain / Explain Extended, чтобы понять, какие индексы доступны MySQL и какой из них фактически выбирает. MySQL будет использовать только ОДИН ключ для каждого запроса .

EXPLAIN EXTENDED SELECT * from Table WHERE Something='ABC'

Журнал медленных запросов

Включите журнал медленных запросов , чтобы увидеть, какие запросы выполняются медленно.

Широкие столбцы

Если у вас есть широкий столбец, в котором БОЛЬШИНСТВО различий происходит в первых нескольких символах, вы можете использовать только первые N символов в своем индексе. Пример: у нас есть столбец ReferenceNumber, определенный как varchar (255), но в 97% случаев ссылочный номер составляет 10 символов или меньше. Я изменил индекс, чтобы смотреть только на первые 10 символов, и немного улучшил производительность.

1/2) Индексы ускоряют определенные операции выбора, но замедляют другие операции, такие как вставка, обновление и удаление. Это может быть прекрасный баланс.

3) используйте полнотекстовый индекс или, возможно, сфинкса

Вам обязательно стоит почитать об индексировании, об этом много написано, и важно понимать, что происходит.

Вообще говоря, индекс устанавливает порядок строк в таблице.

Для простоты представьте, что таблица - это просто большой файл CSV. Всякий раз, когда вставляется строка, она вставляется в конец . Таким образом, «естественный» порядок таблицы - это просто порядок, в котором были вставлены строки.

Представьте, что у вас есть этот CSV-файл, загруженный в очень элементарное приложение для работы с электронными таблицами. Все, что делает эта электронная таблица, - отображает данные и нумерует строки в последовательном порядке.

Теперь представьте, что вам нужно найти все строки, которые имеют некоторое значение «M» в третьем столбце. Учитывая то, что у вас есть, у вас есть только один вариант. Вы просматриваете таблицу, проверяя значение третьего столбца для каждой строки. Если у вас много строк, этот метод («сканирование таблицы») может занять много времени!

Теперь представьте, что в дополнение к этой таблице у вас есть индекс. Этот конкретный индекс является индексом значений в третьем столбце. В указателе перечислены все значения из третьего столбца в некотором значимом порядке (например, в алфавитном порядке), и для каждого из них предоставляется список номеров строк, в которых появляется это значение.

Теперь у вас есть хорошая стратегия для поиска всех строк, в которых значение третьего столбца равно «M». Например, вы можете выполнить бинарный поиск ! В то время как сканирование таблицы требует, чтобы вы просмотрели N строк (где N - количество строк), двоичный поиск требует только просмотра записей индекса log-n в самом худшем случае.Вау, это намного проще!

Конечно, если у вас есть этот индекс и вы добавляете строки в таблицу (в конце, поскольку именно так работает наша концептуальная таблица), вам нужно обновлять индекс каждый раз. Таким образом, вы делаете немного больше работы, пока пишете новые строки, но вы экономите массу времени, когда что-то ищете.

Таким образом, в целом индексация создает компромисс между эффективностью чтения и эффективностью записи. Без индексов вставка может быть очень быстрой - ядро ​​базы данных просто добавляет строку в таблицу. Когда вы добавляете индексы, движок должен обновлять каждый индекс при выполнении вставки.

С другой стороны, чтение стало намного быстрее.

Надеюсь, это касается ваших первых двух вопросов (поскольку другие ответили - вам нужно найти правильный баланс).

Ваш третий сценарий немного сложнее. Если вы используете LIKE, механизмы индексации обычно помогают повысить скорость чтения до первого «%». Другими словами, если вы выбираете WHERE column LIKE 'foo% bar%', база данных будет использовать индекс, чтобы найти все строки, в которых столбец начинается с «foo», а затем необходимо просканировать этот промежуточный набор строк, чтобы найти подмножество. который содержит «бар». SELECT ... WHERE column LIKE '% bar%' не может использовать индекс. Надеюсь, вы понимаете почему.

Наконец, вам нужно подумать об индексах более чем для одного столбца. Концепция та же и ведет себя аналогично материалу LIKE - по сути, если у вас есть индекс на (a, b, c), движок будет продолжать использовать индекс слева направо, насколько это возможно. Таким образом, поиск по столбцу a может использовать индекс (a, b, c), как и индекс (a, b).Однако движку потребуется выполнить полное сканирование таблицы, если вы искали WHERE b = 5 AND c = 1)

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

В целом, индексы помогают ускорить поиск в базе данных, но их недостатком является использование дополнительного дискового пространства и замедление INSERT / UPDATE / DELETE запросов. Используйте EXPLAIN и прочитайте результаты, чтобы узнать, когда MySQL использует ваши индексы.

Если таблица имеет шесть столбцов и все они доступны для поиска, должен ли я индексировать их все или ни одного?

Индексирование всех шести столбцов не всегда является лучшей практикой.

(a) Будете ли вы использовать любой из этих столбцов при поиске конкретной информации?

(b) Какова селективность этих столбцов (сколько отдельных значений хранится в них по сравнению с общим количеством записей в таблице)?

MySQL использует оптимизатор на основе затрат, который пытается найти "самый дешевый" путь при выполнении запроса. И поля с низкой селективностью не являются хорошими кандидатами.

Каковы отрицательные последствия индексирования для производительности?

Уже ответил: дополнительное дисковое пространство, снижение производительности при вставке - обновлении - удалении.

Если у меня есть колонка VARCHAR 2500, которая доступна для поиска в некоторых частях моего сайта, должен ли я ее индексировать?

Попробуйте FULLTEXT Index.

. Ознакомьтесь с презентациями, например, Подробнее об искусстве индексирования .

Обновление 12/2012: Я опубликовал свою новую презентацию: Как создавать индексы, правда . Я представил это в октябре 2012 года на ZendCon в Санта-Кларе и в декабре 2012 года на Percona Live London.

Разработка лучших индексов - это процесс, который должен соответствовать запросам, которые вы запускаете в своем приложении.

Трудно рекомендовать какие-либо общие правила о том, какие столбцы лучше всего индексировать, или следует ли индексировать все столбцы, без столбцов, какие индексы должны охватывать несколько столбцов и т. Д. Это зависит от запросов, которые вам нужно выполнить.

Да, есть некоторые накладные расходы, поэтому вам не следует создавать индексы без необходимости. Но вы должны создать индексы, которые будут полезны для запросов, которые вам нужно выполнять быстро. Накладные расходы на индекс обычно намного перевешиваются его преимуществами.

Для столбца VARCHAR (2500) вы, вероятно, захотите использовать индекс FULLTEXT или индекс префикса:

CREATE INDEX i ON SomeTable(longVarchar(100));

Обратите внимание, что обычный индекс не может помочь, если вы ищете слова, которые могут быть в середине этого длинного varchar. Для этого используйте полнотекстовый индекс.

Если в таблице шесть столбцов, и все они доступны для поиска, следует ли проиндексировать все из них или ни один из них

Вы выполняете поиск по полю или по нескольким полям? По каким полям чаще всего выполняется поиск ? Какие бывают типы полей? (Индекс работает лучше, например, с INT, чем с VARCHAR) Вы пробовали использовать EXPLAIN для выполняемых запросов?

Какое отрицательное влияние на производительность индексирование

UPDATE и INSERT будут медленнее. Также есть требования к дополнительному пространству для хранения, но в наши дни это обычно неважно.

Если у меня есть столбец VARCHAR 2500, который доступен для поиска из частей моего сайта, должен ли я его проиндексировать

Нет, если только он УНИКАЛЬНЫЙ (что означает, что он уже проиндексирован) или вы выполняете поиск только по точному совпадает с этим полем (без использования полнотекстового поиска LIKE или mySQL).

Обычно я помещаю индекс в любые поля, которые буду искать или выбирать, используя предложение WHERE

Я обычно индексирую поля, которые запрашиваются чаще всего, а затем INT / BOOLEAN / ENUM, а не поля, которые VARCHARS. Не забывайте, что часто вам нужно создать индекс для комбинированных полей, а не для отдельного поля. Используйте EXPLAIN и проверьте медленный журнал.