Индексы в базе данных 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);
Как видим, теперь указан используемый индекс и число записей, которые БД пришлось пробежать - всего 3. Это именно 3 записи с именем "Иван".
Когда использовать индексы
Наверное, первое, о чем вы подумали: "О, круто! Нужно создать индексы на все поля и будет счастье". К сожалению, это так не работает. Индексы РЕАЛЬНО ускоряют работу поиска только от определенного числа записей. В среднем должно быть несколько десятков тысяч записей, если не сотен, чтобы почуствовать профит от индекса. И всё сильно будет зависеть конкретно от вашей базы, от количества запросов, от мощности железа. Так что если никаких проблем в работе не наблюдается (а в первую очередь нужно исходить именно из этого!), то и индексы создавать не нужно. Потому что помимо того что они попросту не принесут пользы, так они могут нанести еще и вред, так как:
- индекс всегда занимает место;
- при внесении изменений в столбцах, входящих в индекс, происходит перестройка индекса. А это далеко не самая дешевая операция.
Если вы делаете личный блог, в котором 100 записей, индексы вам вряд ли понадобятся (за исключением первичных ключей). Однако когда вы работаете с высоконагруженным проектом, база данных которого содержит миллионы строк, без них вряд ли получится обойтись.
Комментарии