Индексы в базе данных MySQL

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

Для начала стоит понять, как вообще работают запросы. Когда мы пишем запрос типа:

SELECT * FROM users WHERE name="Иван";

MySQL берет все записи в таблице и по очереди перебирает каждую из них и смотрит, подходит ли она под условие.

Таким образом, если у нас есть 100000 записей, то в худшем случае чтобы найти какую-то одну запись потребуется пройтись по всем этим строкам.

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

Если вы знаете что такое бинарный поиск, то можете пропустить этот абзац. Если же нет - то я в качестве объяснения приведу классический пример с телефонным справочником. Представьте, что вам нужно найти номер телефона в старом бумажном телефонном справочнике, зная ФИО человека. Вы можете построчно искать нужного человека, просматривая одну страничку за другой. Таким образом, если в справочнике 100 страниц, то в процессе поиска можно все их просмотреть, и человек найдется на последней страничке. Однако, зная, что в справочнике ФИО перечислены в алфавитном порядке, можно открыть справочник посередине. Посмотрев какая сейчас буква, можно понять, в какой из половин искать дальше. Далее мы берем нужную половину справочника и открываем страничку уже в её середине. И так далее. Таким образом максимальное количество страниц, которое нам нужно посмотреть уже не 100, как в случае с простым перебором, а всего 6! Этот алгоритм поиска называется бинарным поиском. И работает он только на отсортированных данных.

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

Первичный ключ при этом тоже является индексом, да еще и уникальным. А если значения в индексе уникальные, то значит при самом первом нахождении соответствия можно поиск остановить. Ведь у нас может быть только один пользователь с id=5. А вот если мы создадим индекс по полю name в таблице users и начнём искать Иванов, то после нахождения первого Ивана останавливать поиск нельзя - ведь могут быть и другие. Поэтому нужно будет проверить все соседние значения, пока Иваны не закончатся. Таким образом мы понимаем, что поиск по уникальным индексам займёт меньше всего времени в общем случае.

Чтобы создать индекс для какого-либо поля используется запрос:

CREATE INDEX имя_индекса ON имя_таблицы(имя_поля);

В нашем случае для поля name запрос будет выглядеть так:

CREATE INDEX name_idx ON users(name);

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

Индексы могут использоваться для запросов, если поле, на которое создан индекс, используется в следующих частях запроса:

• WHERE;
• ORDER BY;
• внутри функций MIN и MAX.

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

Зачастую в запросах используется поиск не по одному полю, а по нескольким сразу. В таком случае можно сделать составной индекс, который будет работать для нескольких полей сразу.
К примеру, если бы в табличке users было поле gender, хранящее пол пользователя, а на сайте была бы какая-то страничка, формирующая запрос:

SELECT * FROM users WHERE gender="м" AND name="Женя" 

То можно было бы создать индекс по обоим полям:

CREATE INDEX gender_name_idx ON users(gender, name);

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

При построении составного индекса будет создан отсортированный список со значениями столбцов, почти такой же как и при создании индекса по одному полю. Но отличие всё же есть. В этом списке будет уже несколько столбцов. Они будут в том порядке, который был использован при создании индекса. И использоваться индекс будет в том же порядке, в котором эти столбцы были перечислены при его создании. В нашем случае сначала выберутся все строки, в которых gender="м", а потом уже среди оставшихся будут выбраны те, у которых name="Женя".

Селективность индексов

Однако, алгоритм, рассмотренный выше, не является оптимальным. Ведь получается, что на первом шаге мы сначала отбрасываем по полу, к примеру, 50% записей. А потом среди оставшихся 50% ищем совпадение по имени, которое к примеру, встречается лишь в 5 записях. То есть будь у нас 100000 записей, мы бы сначала сделали столь громадный поиск по ним и сделали выборку в 50000 записей, а потом среди этих записей нашли всего 5, нужных нам.

А что если поменять местами порядок фильтров? Если сначала сделать поиск по имени Женя, получить всего 10 записей, а уже по ним отфильтровать по полу всего 5 записей, будет гораздо оптимальнее.

Поэтому правильнее будет удалить наш созданный ранее индекс:

DROP INDEX gender_name_idx ON users;

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

CREATE INDEX name_gender_idx ON users(name, gender);

Так мы плавно подошли к понятию селективности индекса. Чем меньше в столбце одинаковых значений, тем выше селективность по нему. Селективность столбца name выше, чем столбца gender. Поля с наиболее высокой селективностью должны быть первыми в составных индексах.

EXPLAIN

Чтобы проанализировать работу индексов при конкретном запросе есть специальное слово EXPLAIN. Оно ставится перед ключевым словом SELECT. Пример запроса с EXPLAIN:

EXPLAIN SELECT * FROM users WHERE name="Иван";

Здесь нас в первую очередь интересуют столбцы:
key - используемый индекс (в нашем случае никаких индексов для поля name пока не создано, поэтому тут null)
possible_keys - все возможные индексы, подходящие для запроса (аналогично написанному выше)
rows - число записей, которые пришлось проверить прежде чем отдать результат (в нашей таблице по-прежнему 9 записей)

Давайте теперь создадим индекс на поле name:

CREATE INDEX name_idx ON users(name);

Повторим наш запрос с EXPLAIN

Как видим, теперь указан используемый индекс и число записей, которые БД пришлось пробежать - всего 3. Это именно 3 записи с именем "Иван".

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

Наверное, первое, о чем вы подумали: "О, круто! Нужно создать индексы на все поля и будет счастье". К сожалению, это так не работает. Индексы РЕАЛЬНО ускоряют работу поиска только от определенного числа записей. В среднем должно быть несколько десятков тысяч записей, если не сотен, чтобы почуствовать профит от индекса. И всё сильно будет зависеть конкретно от вашей базы, от количества запросов, от мощности железа. Так что если никаких проблем в работе не наблюдается (а в первую очередь нужно исходить именно из этого!), то и индексы создавать не нужно. Потому что помимо того что они попросту не принесут пользы, так они могут нанести еще и вред, так как:

• индекс всегда занимает место;
• при внесении изменений в столбцах, входящих в индекс, происходит перестройка индекса. А это далеко не самая дешевая операция.

Если вы делаете личный блог, в котором 100 записей, индексы вам вряд ли понадобятся (за исключением первичных ключей). Однако когда вы работаете с высоконагруженным проектом, база данных которого содержит миллионы строк, без них вряд ли получится обойтись.