Индексы MySQL: как ускорить запросы

Запрос, который на тестовой базе отрабатывал за три миллисекунды, на проде с миллионом строк вдруг тянется полторы секунды. Код тот же, схема та же — изменился только объём данных. Почти всегда причина одна: MySQL перебирает таблицу целиком, потому что подходящего индекса нет. Разберём, как индексы устроены, как найти запросы, которым их не хватает, и как не сделать хуже, навесив индекс на каждую колонку.

Что такое индекс и почему без него медленно

Индекс — отдельная структура рядом с таблицей, где значения выбранных колонок лежат в отсортированном виде вместе со ссылкой на строку. Аналогия — алфавитный указатель в конце книги: чтобы найти термин, не нужно листать все страницы подряд.

Без индекса СУБД делает полный перебор (full table scan): читает каждую строку и проверяет условие. Стоимость такого перебора растёт линейно — вдвое больше данных, вдвое дольше запрос. Поиск по B-дереву растёт логарифмически, поэтому разница между таблицей на 10 тысяч и на 10 миллионов строк почти не чувствуется.

Большинство индексов в MySQL (PRIMARY KEY, UNIQUE, обычные INDEX, FULLTEXT) хранятся в B-деревьях; исключения — R-деревья для пространственных типов, хеш-индексы в движке MEMORY и инвертированные списки у полнотекстовых индексов InnoDB. B-дерево хорошо отвечает на вопросы «равно», «в диапазоне» и «начинается с», но искать «похожее по смыслу» принципиально не умеет — для этого нужны векторные базы данных.

Кластерный индекс: почему PRIMARY KEY стоит особняком

В InnoDB первичный ключ — не просто ещё один индекс: строки таблицы физически хранятся внутри него, это кластерный индекс. Если PRIMARY KEY не объявлен, InnoDB возьмёт первый UNIQUE-индекс со всеми колонками NOT NULL, а если и такого нет — создаст скрытый GEN_CLUST_INDEX по синтетическому шестибайтовому идентификатору строки.

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

• Первичный ключ должен быть коротким — он копируется в каждую запись каждого вторичного индекса. BIGINT AUTO_INCREMENT — 8 байт, составной ключ из двух VARCHAR(255) — беда для всей таблицы.
• Поиск по вторичному индексу — два шага: найти запись в индексе, получить первичный ключ, затем идти за остальными колонками в кластерный индекс. Второй шаг можно исключить — см. раздел про покрывающие индексы.

Как найти запросы, которым не хватает индекса

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

Первый шаг — собрать факты, а не гадать. Включаем лог медленных запросов:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = 'ON';

Последнюю опцию на нагруженном сервере включайте ненадолго: она пишет в лог и безобидные запросы к маленьким справочникам, раздувая файл. Разбирать лог вручную не нужно — pt-query-digest из Percona Toolkit сгруппирует запросы по шаблону и покажет, кто съедает больше всего суммарного времени. Именно суммарного: запрос на 20 мс, вызываемый пять тысяч раз в минуту, вредит сильнее одного отчёта на две секунды.

Читаем EXPLAIN

EXPLAIN показывает план, по которому оптимизатор собирается выполнять запрос. Смотреть стоит на четыре вещи:

• type — способ доступа. ALL — полный перебор таблицы, index — перебор всего индекса (тоже плохо). Хорошие значения: const, eq_ref, ref, range.
• key — какой индекс выбран. NULL при непустом possible_keys значит, что оптимизатор счёл индекс невыгодным.
• rows — оценка числа строк к чтению: прикидка по статистике, а не точное число.
• Extra — Using index хорошо (покрывающий индекс), Using filesort и Using temporary — повод присмотреться к ORDER BY и GROUP BY.

Кроме привычной таблицы есть FORMAT=TREE (дерево операций, единственный формат, показывающий hash join) и FORMAT=JSON — его удобно разбирать программно. Формат по умолчанию переключается переменной сессии:

SET @@explain_format = TREE;
EXPLAIN SELECT id, total FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20;

EXPLAIN ANALYZE вместо догадок

EXPLAIN ANALYZE не просто строит план, а выполняет запрос и показывает рядом с оценками факт: время до первой строки, время работы каждого узла, реальное число строк и проходов. Работает только в древовидном формате.

EXPLAIN ANALYZE
SELECT o.id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid' AND c.city = 'Москва'\G

Главное здесь — расхождение между cost=... rows=... и actual ... rows=.... Если оптимизатор ждал 30 строк, а прочитал 300 тысяч, план построен на устаревшей статистике: помогает ANALYZE TABLE orders;. И помните, что запрос реально выполняется, — на UPDATE и DELETE в проде так экспериментировать не стоит.

Составные индексы и правило левого префикса

Частая ошибка — вешать отдельный индекс на каждую колонку из WHERE. MySQL почти всегда использует для таблицы один индекс, и три одноколоночных проигрывают одному правильно составленному.

Составной индекс работает по правилу левого префикса: (status, created_at, user_id) подходит для условий по status, по status и created_at, по всем трём колонкам. Но запросу, который фильтрует только по created_at, он не поможет — это не левый префикс.

Порядок колонок: сначала те, где проверка на равенство, затем колонка диапазона или сортировки. После первой колонки с диапазоном (>, <, BETWEEN, LIKE 'x%') следующие части индекса поиск по дереву уже не сужают.

-- запрос
SELECT id FROM orders
WHERE status = 'paid' AND created_at >= '2026-01-01'
ORDER BY created_at DESC;

-- правильный индекс
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

Такой индекс закрывает и фильтрацию, и сортировку: Using filesort из плана исчезнет.

Покрывающий индекс

Если в индексе есть все колонки, которые читает запрос, к данным таблицы СУБД не обращается вовсе — ответ собирается из самого индекса. В EXPLAIN это видно как Using index.

ALTER TABLE orders ADD INDEX idx_cover (status, created_at, total);

SELECT total FROM orders
WHERE status = 'paid' AND created_at >= '2026-01-01';

Приём мощный, но не бесплатный: каждая лишняя колонка увеличивает индекс и объём записи при обновлениях. Тяжёлые TEXT-поля добавлять в него смысла нет.

Почему индекс есть, а запрос всё равно медленный

• Функция поверх колонки. WHERE DATE(created_at) = '2026-09-04' убивает индекс. Переписывайте диапазоном: created_at >= '2026-09-04' AND created_at < '2026-09-05'.
• Ведущий шаблон в LIKE. LIKE '%телефон%' индексом не ускорить — нужен полнотекстовый индекс или отдельный поисковый движок.
• Несовпадение типов и кодировок. Сравнение строковой колонки с числом или JOIN utf8mb3-колонки с utf8mb4 заставляет приводить типы построчно — индекс не работает.
• OR по разным колонкам. Часто быстрее UNION ALL двух запросов, каждый из которых бьёт в свой индекс.
• Низкая селективность. Индекс по колонке с двумя значениями оптимизатор проигнорирует: прочитать половину таблицы через индекс дороже, чем просто просканировать её.

Специальные виды индексов

Префиксный. Индексировать длинный VARCHAR целиком накладно, а для TEXT и BLOB префикс обязателен: CREATE INDEX idx_name ON customers (name(20));. Лимит длины ключа в InnoDB — 3072 байта для форматов строк DYNAMIC и COMPRESSED, 767 байт для REDUNDANT и COMPACT.

Функциональный. С MySQL 8.0 можно индексировать выражение, синтаксис требует двойных скобок: CREATE INDEX idx_year ON orders ((YEAR(created_at)));. Внутри это скрытая виртуальная генерируемая колонка. В MariaDB такого синтаксиса нет — там заводят обычную GENERATED-колонку и индексируют её.

По убыванию. Тоже с 8.0: INDEX (a ASC, b DESC) действительно хранится в указанном порядке. Спасает сортировки вида ORDER BY a, b DESC, которые раньше всегда уходили в filesort.

Невидимый. Индекс можно пометить как INVISIBLE: сервер продолжит его поддерживать, а оптимизатор перестанет использовать. В MariaDB это ignored indexes (с 10.6), ключевое слово IGNORED. Штатный способ проверить, не сломается ли что-то после удаления индекса, ничего не удаляя.

Лишние индексы вредят не меньше отсутствующих

Каждый индекс обновляется при каждом INSERT, UPDATE и DELETE и занимает место на диске и в буферном пуле — на таблице с активной записью десяток индексов ощутимо замедляет саму запись. Отдельная категория — избыточные: если есть (a, b), то индекс (a) не нужен, первый и так покрывает запросы по левому префиксу.

Найти кандидатов на удаление помогает схема sys:

SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

Первое представление опирается на статистику Performance Schema, поэтому доверять ему можно только после долгой работы сервера под реальной нагрузкой: сразу после перезапуска в списке окажутся вообще все индексы. Безопасный порядок — сделать индекс невидимым, понаблюдать неделю за логом медленных запросов и только потом делать DROP INDEX.

Порядок действий на практике

1. Собрать реальные медленные запросы из лога, а не оптимизировать наугад.
2. Отсортировать их по суммарному вкладу во время работы сервера.
3. Для верхних 3–5 посмотреть EXPLAIN: type: ALL, Using filesort, Using temporary.
4. Добавить один составной индекс под конкретный шаблон и сверить EXPLAIN ANALYZE до и после.
5. Если план выглядит странно — обновить статистику через ANALYZE TABLE.
6. Раз в квартал проходить по sys.schema_unused_indexes и чистить лишнее.

И ещё: индекс лечит не всякую медленную страницу. Если один и тот же тяжёлый запрос выполняется на каждый хит, дешевле его закешировать, чем разгонять. Для WordPress общий чек-лист мы собрали в материале как ускорить WordPress в 2026, а про то, как убрать повторяющиеся обращения к базе, — в заметке про объектный кеш на Redis.

Коротко

Индекс — отсортированная копия части данных, превращающая перебор в адресный поиск. Начинайте с измерений, а не с гипотез: лог медленных запросов покажет, где болит, EXPLAIN ANALYZE — почему. Один продуманный составной индекс под реальный шаблон запроса даёт больше, чем пять одноколоночных, а невидимые индексы позволяют проверить удаление без риска. Актуальные ветки MySQL на осень 2026 — 8.4 LTS и 9.7 LTS; поддержка 8.0 закончилась весной 2026 года, так что обновление стоит поставить в очередь вместе с оптимизацией.