Индексы в MySQL

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

Недавно пришлось оптимизировать БД одного из поддерживаемых мной сайтов. В раздел генерации отчетов был добавлен ряд сложных выборок с джойнами и подзапросами, что и вызвало проблемы с производительностью при генерации этих самых отчетов. Для малонагруженных сайтов я редко добавляю индексы со старта проекта, за исключением первичных и уникальных ключей, поэтому пришлось это делать во время работы проекта. Как и предполагалось, индексы сократили время выборок на порядок, с ~20 секунд, до < 1 секунды.

Что такое индексы в MySQL?

Вкратце, это механизм ускорения работы СУБД. Добавление индексов в таблицу позволяет увеличить скорость выборки и сортировки данных. Также ускоряться некоторые другие операции, подробнее о них стоит прочитать в документации.

Как работать с индексами?

Просмотр текущих индексов в таблице:

SHOW INDEX FROM your_table_name;

Создание индексов:

-- Простой индекс.
CREATE INDEX index_name ON your_table_name(column_name);
 
-- Составной индекс.
CREATE INDEX index_name ON your_table_name(column_name_first, column_name_second);

Удаление индексов:

DROP INDEX index_name ON your_table_name;

Как именовать индексы?

Самым простым вариантом для начинающих, как мне кажется, является совмещение наименования таблицы (для которой создаются индексы) и наименования столбца (-ов). Например:

CREATE INDEX user_age ON `user` (age);
CREATE INDEX user_balance_salary ON `user` (balance, salary);
 
-- Или так:
CREATE INDEX index_user_age ON `user` (age);
CREATE INDEX index_user_balance_salary ON `user` (balance, salary);

Желательно прибавлять к наименованию индекса строку «index» или «ix», так обычно проще ориентироваться. Показанный выше подход к именованию индексов хорошо подойдет для небольших БД с несколькими десятками таблиц. Для проектов с внушительным количеством таблиц и множеством индексов желательно придумать сокращения.

Для каких полей добавлять индексы?

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

SELECT id, name FROM `user` WHERE age > 50 AND salary > 100000;

Индекс может выглядеть вот так:

CREATE INDEX user_age_salary ON `user` (age, salary);

Для оптимизации запросов желательно пользоваться инструментом EXPLAIN.

Что важно знать об индексах?

Индексы имеют не только плюсы, но и минусы, впрочем, как почти все на свете. Не стоит создавать индексы для всего подряд, так как на поддержание их работы расходуется память (можно сказать, что индекс дублирует данные из целевой таблицы) и часть производительности при обновлении данных. Если бы индексы были полезны всегда, то СУБД автоматически создавала бы их при любом удобном случае.