Индексы MySQL - каковы лучшие практики?
Я уже некоторое время использую индексы в моих базах данных MySQL, но так и не узнал о них должным образом. Обычно я ставлю индекс для любых полей, которые я буду искать или выбирать, используя WHERE пункт, но иногда это не кажется таким черным и белым.
Каковы лучшие практики для индексов MySQL?
Примеры ситуаций / дилемм:
Если в таблице шесть столбцов и все они доступны для поиска, следует ли мне индексировать их все или ни одного из них?
,
Каковы негативные воздействия индексации на производительность?
,
Если у меня есть столбец VARCHAR 2500, доступный для поиска по частям моего сайта, нужно ли его индексировать?
7 ответов
Вы должны определенно потратить некоторое время на чтение по индексированию, об этом много написано, и важно понимать, что происходит.
Вообще говоря, индекс налагает порядок в строках таблицы.
Для простоты представьте, что таблица - это просто большой CSV-файл. Всякий раз, когда вставляется строка, она вставляется в конце. Таким образом, "естественный" порядок таблицы - это просто порядок, в котором были вставлены строки.
Представьте, что вы загрузили этот CSV-файл в очень простое приложение для работы с электронными таблицами. Все, что делает эта электронная таблица, это отображает данные и нумерует строки в последовательном порядке.
Теперь представьте, что вам нужно найти все строки, которые имеют некоторое значение "M" в третьем столбце. Учитывая, что у вас есть в наличии, у вас есть только один вариант. Вы сканируете таблицу, проверяя значение третьего столбца для каждой строки. Если у вас много строк, этот метод ("сканирование таблицы") может занять много времени!
Теперь представьте, что в дополнение к этой таблице у вас есть индекс. Этот конкретный индекс является индексом значений в третьем столбце. Индекс перечисляет все значения из третьего столбца в некотором значимом порядке (скажем, в алфавитном порядке) и для каждого из них предоставляет список номеров строк, в которых появляется это значение.
Теперь у вас есть хорошая стратегия для поиска всех строк, где значение третьего столбца равно "M". Например, вы можете выполнить бинарный поиск! В то время как сканирование таблицы требует, чтобы вы просматривали N строк (где N - количество строк), бинарный поиск требует только просмотра записей индекса log-n, в самом худшем случае. Вау, это намного проще!
Конечно, если у вас есть этот индекс, и вы добавляете строки в таблицу (в конце концов, так работает наша концептуальная таблица), вам нужно обновлять индекс каждый раз. Таким образом, вы делаете немного больше работы, когда пишете новые строки, но вы экономите массу времени, когда вы что-то ищете.
Итак, в целом, индексирование создает компромисс между эффективностью чтения и эффективностью записи. Без индексов вставка может быть очень быстрой - ядро базы данных просто добавляет строку в таблицу. По мере добавления индексов движок должен обновлять каждый индекс при выполнении вставки.
С другой стороны, чтение становится намного быстрее.
Надеюсь, что это покрывает ваши первые два вопроса (как ответили другие - вам нужно найти правильный баланс).
Ваш третий сценарий немного сложнее. Если вы используете LIKE, механизмы индексирования обычно помогают увеличить скорость чтения до первого "%". Другими словами, если вы ВЫБИРАЕТЕ столбец WHERE LIKE 'foo% bar%', база данных будет использовать индекс, чтобы найти все строки, где столбец начинается с "foo", а затем потребуется просканировать этот промежуточный набор строк, чтобы найти подмножество. который содержит "бар". SELECT... WHERE LIKE "% bar%" не может использовать индекс. Я надеюсь, вы понимаете, почему.
Наконец, вам нужно начать думать об индексах более чем в одном столбце. Концепция та же самая, и она ведет себя аналогично LIKE - по сути, если у вас есть индекс для (a,b,c), движок будет продолжать использовать индекс слева направо, как может. Таким образом, поиск по столбцу a может использовать индекс (a,b,c), как и поиск по (a,b). Тем не менее, движок должен был бы выполнить полное сканирование таблицы, если вы искали ГДЕ b=5 И c=1)
Надеюсь, это поможет пролить немного света, но я должен повторить, что вам лучше потратить несколько часов на поиски хороших статей, которые подробно объясняют эти вещи. Это также хорошая идея, чтобы прочитать документацию вашего конкретного сервера баз данных. То, как индексы реализуются и используются планировщиками запросов, может варьироваться довольно широко.
Посмотрите такие презентации, как More Mastering the Art of Indexing.
Обновление 12/2012: я опубликовал новую презентацию: как правильно составлять индексы. Я представил это в октябре 2012 года на ZendCon в Санта-Кларе и в декабре 2012 года в Percona Live London.
Разработка лучших индексов - это процесс, который должен соответствовать запросам, которые вы выполняете в своем приложении.
Трудно рекомендовать какие-либо универсальные правила о том, какие столбцы лучше всего индексировать, или нужно ли индексировать все столбцы, нет столбцов, какие индексы должны охватывать несколько столбцов и т. Д. Это зависит от запросов, которые нужно выполнить.
Да, есть некоторые накладные расходы, поэтому вы не должны создавать индексы без необходимости. Но вы должны создать индексы, которые будут полезны для запросов, которые вам нужны для быстрого выполнения. Накладные расходы на индекс обычно значительно перевешиваются его преимуществом.
Для столбца VARCHAR(2500) вы, вероятно, захотите использовать индекс FULLTEXT или индекс префикса:
CREATE INDEX i ON SomeTable(longVarchar(100));
Обратите внимание, что обычный индекс не может помочь, если вы ищете слова, которые могут быть в середине этого длинного varchar. Для этого используйте полнотекстовый индекс.
Я не буду повторять некоторые полезные советы в других ответах, но добавлю:
Составные индексы
Вы можете создавать составные индексы - индекс, который включает несколько столбцов. 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 Extended, чтобы понять, какие индексы доступны для MySQL, а какие - на самом деле. MySQL будет использовать только один ключ на запрос.
EXPLAIN EXTENDED SELECT * from Table WHERE Something='ABC'
Журнал медленных запросов
Включите медленный журнал запросов, чтобы увидеть, какие запросы выполняются медленно.
Широкие колонны
Если у вас есть широкий столбец, где MOST различения происходит в первых нескольких символах, вы можете использовать только первые N символов в вашем индексе. Пример: у нас есть столбец ReferenceNumber, определенный как varchar(255), но в 97% случаев ссылочный номер составляет 10 символов или менее. Я изменил индекс, чтобы посмотреть только на первые 10 символов, и немного улучшил производительность.
Если в таблице шесть столбцов и все они доступны для поиска, следует ли мне проиндексировать все из них или ни одного из них
Вы ищете по полю по полю или некоторые поиски используют несколько полей? В каких полях больше всего ищется? Какие типы полей? (Индекс работает лучше на INT, чем на VARCHAR, например). Вы пытались использовать EXPLAIN для выполняемых запросов?
Каковы отрицательные воздействия индексации на производительность
ОБНОВЛЕНИЯ и ВСТАВКИ будут медленнее. Есть также дополнительные требования к месту для хранения, но это обычно неважно в наши дни.
Если у меня есть столбец VARCHAR 2500, доступный для поиска по частям моего сайта, я должен проиндексировать его
Нет, если только он не УНИКАЛЬНЫЙ (что означает, что он уже проиндексирован) или вы ищете только точные совпадения в этом поле (без использования LIKE или полнотекстового поиска mySQL).
Обычно я ставлю индекс для любых полей, которые я буду искать или выбирать, используя предложение WHERE
Обычно я индексирую наиболее запрашиваемые поля, а затем INTs/BOOLEANs/ENUM, а не поля, которые являются VARCHARS. Не забывайте, что часто вам нужно создать индекс для комбинированных полей, а не индекс для отдельного поля. Используйте EXPLAIN и проверьте медленный журнал.
Эффективная загрузка данных: индексы ускоряют поиск, но замедляют вставки и удаления, а также обновления значений в индексированных столбцах. То есть индексы замедляют большинство операций, связанных с записью. Это происходит потому, что запись строки требует записи не только строки данных, но и изменений любых индексов. Чем больше индексов в таблице, тем больше нужно внести изменений и тем больше среднее снижение производительности. Большинство таблиц получают много чтений и мало записей, но для таблицы с высоким процентом записей стоимость обновления индекса может быть значительной.
Избегайте индексов: если вам не нужен определенный индекс, чтобы помочь запросам работать лучше, не создавайте его.
Дисковое пространство: индекс занимает место на диске, а несколько индексов занимают соответственно больше места. Это может привести к более быстрому достижению предела размера таблицы, чем при отсутствии индексов. Избегайте индексов, где это возможно.
Еда на вынос: не переусердствовать
В целом, индексы помогают ускорить поиск в базе данных, имея недостаток в использовании дополнительного дискового пространства и замедлении INSERT / UPDATE / DELETE запросы. использование EXPLAIN и прочитайте результаты, чтобы узнать, когда MySQL использует ваши индексы.
Если в таблице шесть столбцов и все они доступны для поиска, следует ли мне индексировать их все или ни одного из них?
Индексирование всех шести столбцов не всегда является лучшей практикой.
(а) Собираетесь ли вы использовать какие-либо из этих столбцов при поиске конкретной информации?
(б) Какова избирательность этих столбцов (сколько разных значений хранится в сравнении с общим количеством записей в таблице)?
MySQL использует оптимизатор на основе затрат, который пытается найти "самый дешевый" путь при выполнении запроса. И поля с низкой селективностью не являются хорошими кандидатами.
Какое отрицательное влияние на производительность оказывает индексирование?
Уже ответил: дополнительное дисковое пространство, более низкая производительность при вставке - обновлении - удалении.
Если у меня есть столбец VARCHAR 2500, доступный для поиска по частям моего сайта, мне его индексировать?
Попробуйте индекс FULLTEXT.
1/2) Индексы ускоряют определенные операции выбора, но замедляют другие операции, такие как вставка, обновление и удаление. Это может быть прекрасный баланс.
3) использовать полнотекстовый индекс или, возможно, сфинкс