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

Ответ 1

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

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

Для простоты представьте, что таблица - это просто большой 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)

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

Ответ 2

Проверьте презентации, такие как " Больше владения искусством индексирования".

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

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

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

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

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

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

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

Ответ 3

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

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

Вы можете создавать составные индексы - индекс, который содержит несколько столбцов. 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 будет использовать только ONE для каждого запроса.

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

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

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

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

Если у вас широкий столбец, где МОСТ различий происходит в первых нескольких символах, вы можете использовать только первые N символов в вашем индексе. Пример. У нас есть столбец ReferenceNumber, определенный как varchar (255), но 97% случаев, ссылочный номер - 10 символов или меньше. Я изменил индекс, чтобы посмотреть только на первые 10 символов и немного улучшить производительность.

Ответ 4

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

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

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

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

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

Нет, если он не UNIQUE (это означает, что он уже проиндексирован), или вы только выполняете поиск точных совпадений в этом поле (не используя полнотекстовый поиск LIKE или mySQL).

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

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

Ответ 5

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

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

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

Takeaway: не над индексом

Ответ 6

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

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

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

(а) Собираетесь ли вы использовать какие-либо из этих столбцов при поиске конкретной информации?

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

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

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

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

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

Попробуйте индекс FULLTEXT.

Ответ 7

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

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