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

Большинство медленных запросов MySQL медленные по одной причине: сервер читает гораздо больше строк, чем возвращает, потому что нет индекса, который позволил бы сразу перейти к нужным. Решение - индекс, столбцы которого соответствуют тому, как запрос фильтрует и сортирует: сначала столбцы с равенством, затем столбец диапазона или сортировки, - а инструмент, который покажет, правильно ли вы всё сделали, - EXPLAIN. Смотрите на столбец type (ALL означает полное сканирование таблицы), столбец key (какой индекс выбран, если выбран), rows (сколько строк MySQL собирается просмотреть) и Extra (дорогие здесь - Using filesort и Using temporary). Затем запустите EXPLAIN ANALYZE, чтобы увидеть, что произошло на самом деле, а не что предположил оптимизатор.

Это руководство объясняет, как InnoDB хранит таблицы и индексы, почему порядок столбцов в составном индексе решает, будет ли он использоваться, какие шаблоны заставляют MySQL игнорировать имеющийся индекс и как читать обе формы EXPLAIN, а в конце разбирает пример. Оно относится к MySQL 8.0 и 8.4 LTS.

Как InnoDB хранит таблицу#

Таблица InnoDB - это B+-дерево, упорядоченное по первичному ключу. Листовые страницы этого дерева содержат строки целиком. Это кластерный индекс, и он не опционален: если вы не объявили первичный ключ, InnoDB использует первый индекс UNIQUE NOT NULL, а если такого нет, придумывает скрытый 6-байтовый идентификатор строки под названием GEN_CLUST_INDEX, который нельзя использовать в запросах.

Любой другой индекс - вторичный: отдельное B+-дерево, упорядоченное по индексируемым столбцам, листья которого содержат эти столбцы плюс значение первичного ключа. Поэтому поиск строки через вторичный индекс - это два поиска: один по вторичному индексу, чтобы найти первичный ключ, и второй по кластерному индексу, чтобы достать строку.

Отсюда три практических следствия:

• Держите первичный ключ маленьким. Он копируется в каждый вторичный индекс. BIGINT занимает 8 байт; UUID, хранимый как CHAR(36), - не меньше 36 байт в каждой записи индекса, а в utf8mb4 индекс резервирует место из расчёта четыре байта на символ.
• Последовательные первичные ключи вставляются дёшево. Ключ AUTO_INCREMENT дописывается в конец дерева. Случайные UUID вставляются по всему дереву, расщепляя страницы и фрагментируя кэш. Если вам нужны UUID, храните их как BINARY(16) с UUID_TO_BIN(UUID(), 1), что переставляет временную часть так, чтобы значения шли примерно последовательно.
• Запрос, которому нужны только индексированные столбцы, может пропустить второй поиск. Это покрывающий индекс, в EXPLAIN он виден как Using index, и часто это самый крупный выигрыш из доступных.

Всегда давайте таблицам явный первичный ключ. MySQL 8 может требовать его через sql_require_primary_key=ON, а репликация и многие инструменты без него ведут себя плохо.

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

Составной индекс по (a, b, c) отсортирован по a, затем по b внутри каждого a, затем по c. Как телефонный справочник, отсортированный по фамилии, а затем по имени, он полезен для любого поиска, начинающегося слева.

Отсюда правило: сначала столбцы с равенством, затем один столбец диапазона или сортировки, последним. Как только индекс доходит до условия диапазона, столбцы после него уже не сужают поиск, а только фильтруют. Среди нескольких столбцов с равенством порядок важен меньше, чем принято думать; ставьте первым тот, который встречается в большинстве запросов, чтобы индекс обслуживал их все.

В MySQL 8.0.13 появился skip scan, который может использовать (a, b) для WHERE b = 2, когда у a очень мало различных значений, выполняя поиск отдельно для каждого a. Он отображается как Using index for skip scan. Это спасательный круг, а не проектное решение.

Селективность первого столбца важна меньше, чем утверждают старые советы. Важно, чтобы индекс соответствовал форме запроса. Индекс только по столбцу с низкой кардинальностью (status с тремя значениями) редко бывает полезен; тот же столбец как первая часть (status, created_at) для «последних оплаченных заказов» - отличный вариант.

Когда MySQL игнорирует ваш индекс#

Вы создали индекс, а EXPLAIN всё равно говорит type: ALL. Обычные причины:

-- A function on the column: the index holds email, not LOWER(email)SELECT * FROM users WHERE LOWER(email) = 'ana@example.com';-- Implicit conversion: phone is VARCHAR, the literal is a numberSELECT * FROM users WHERE phone = 5551234;-- Leading wildcard: the index is sorted from the first characterSELECT * FROM products WHERE name LIKE '%phone%';-- Arithmetic on the columnSELECT * FROM orders WHERE created_at + INTERVAL 1 DAY > NOW();-- Date functions instead of a rangeSELECT * FROM orders WHERE YEAR(created_at) = 2026;

Решения по порядку: перепишите условие так, чтобы столбец стоял сам по себе (created_at > NOW() - INTERVAL 1 DAY, created_at >= '2026-01-01' AND created_at < '2027-01-01'), заключайте строковые литералы в кавычки, чтобы они совпадали с типом столбца, а для поиска внутри текста используйте индекс FULLTEXT, а не LIKE '%...%'. Когда без функции не обойтись, MySQL 8.0.13 и новее поддерживают функциональные индексы - обратите внимание на двойные скобки:

CREATE INDEX idx_users_email_lower ON users ((LOWER(email)));

Две менее очевидные причины. Соединение столбцов с разными кодировками или правилами сравнения не даёт использовать индекс на преобразуемой стороне, и это частое следствие недоделанной миграции на utf8mb4. А ещё оптимизатор может справедливо решить, что полное сканирование дешевле: если условию соответствует 40% таблицы, последовательное чтение таблицы выигрывает у десятков тысяч поисков по индексу. В этом случае не навязывайте индекс; исправьте запрос, чтобы ему требовалось меньше строк.

EXPLAIN, столбец за столбцом#

EXPLAIN SELECT id, total FROM ordersWHERE customer_id = 4821 AND status = 'paid'ORDER BY created_at DESC LIMIT 20\G

*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: orders   partitions: NULL         type: refpossible_keys: idx_customer          key: idx_customer      key_len: 8          ref: const         rows: 2310     filtered: 10.00        Extra: Using where; Using filesort

Если в клиенте mysql завершить оператор на \G вместо ;, каждая строка выводится вертикально, и EXPLAIN так читать гораздо проще.

Значения type простыми словами: const - одна строка по первичному ключу или уникальному индексу; eq_ref - одна строка на каждую строку предыдущей таблицы в соединении; ref - несколько строк, совпадающих по равенству в неуникальном индексе; range - диапазон индекса (BETWEEN, >, IN); index читает весь индекс; ALL читает всю таблицу. index - вовсе не такая хорошая новость, как кажется: это полное сканирование индекса вместо таблицы.

Пометки в Extra, которые стоит узнавать:

• Using index - покрывающий индекс: ответ получен из одного индекса. Хорошо.
• Using index condition - index condition pushdown, фильтрация внутри индекса до извлечения строк. Хорошо.
• Using where - строки фильтруются после чтения. Нормально, но при большом rows это значит, что работа тратится впустую.
• Using filesort - результаты сортируются после чтения, в памяти или на диске. Дорого на большом числе строк.
• Using temporary - построена временная таблица, обычно для GROUP BY или DISTINCT.
• Using join buffer (hash join) - hash join, используемый начиная с 8.0.18, когда у соединения нет подходящего индекса. Признак того, что столбцу соединения нужен индекс.

В примере план нашёл 2310 заказов клиента через idx_customer, затем отфильтровал их по статусу (по оценке, остаётся 10%) и отсортировал их все, чтобы вернуть 20. Это работает, но читает в сто раз больше строк, чем возвращает.

key_len - незаметная деталь, которая отвечает на вопрос «полностью ли используется мой составной индекс?». INT - это 4 байта, BIGINT - 8, столбец, допускающий NULL, добавляет 1, VARCHAR(n) в utf8mb4 считается как 4 × n + 2. Если key_len покрывает только первый столбец трёхстолбцового индекса, два других для поиска не используются.

EXPLAIN ANALYZE и древовидный формат#

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

EXPLAIN ANALYZE SELECT id, total FROM ordersWHERE customer_id = 4821 AND status = 'paid'ORDER BY created_at DESC LIMIT 20;

-> Limit: 20 row(s)  (cost=520 rows=20) (actual time=9.84..9.85 rows=20 loops=1)    -> Sort: orders.created_at DESC, limit input to 20 row(s) per chunk       (cost=520 rows=2310) (actual time=9.84..9.84 rows=20 loops=1)        -> Filter: (orders.`status` = 'paid')  (cost=520 rows=231)           (actual time=0.11..9.52 rows=1874 loops=1)            -> Index lookup on orders using idx_customer (customer_id=4821)               (cost=520 rows=2310) (actual time=0.10..9.21 rows=2310 loops=1)

Читайте от самой внутренней строки наружу. Каждый шаг показывает оценку (cost, rows) и реальность (actual time как первая строка..все строки в миллисекундах, rows, loops). Искать стоит две вещи:

• Оценки, далёкие от реальности. Здесь ожидалось, что фильтр оставит 231 строку, а он оставил 1874. Большие расхождения значат, что оптимизатор планирует по плохой статистике и для других значений может выбрать не тот план. ANALYZE TABLE orders; обновляет статистику индексов; для неиндексированных столбцов с перекошенными значениями помогает гистограмма: ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS;.
• Куда уходит время. Для вложенных шагов умножайте время на loops. Шаг, на котором фактическое время скачет, и есть место, где делается работа.

EXPLAIN ANALYZE выполняет запрос, включая любые побочные эффекты вызываемых им функций, и длится столько же, сколько сам запрос. Для UPDATE и DELETE используйте обычный EXPLAIN или выполните эквивалентный SELECT. EXPLAIN FORMAT=TREE показывает то же дерево только с оценками, ничего не выполняя; EXPLAIN FORMAT=JSON добавляет подробности о стоимости. MySQL Workbench рисует JSON-форму в виде диаграммы, и на больших соединениях некоторым так проще.

Индексы для соединений, GROUP BY и префиксов#

Соединения подчиняются той же логике, по одной таблице за раз. MySQL читает первую таблицу плана, а затем для каждой строки ищет совпадающие строки в следующей. Для этого поиска нужен индекс по столбцу соединения второй таблицы - как правило, на стороне внешнего ключа. InnoDB автоматически создаёт индекс, когда вы объявляете FOREIGN KEY, и это одна из причин, почему объявленные внешние ключи редко дают медленные соединения, а необъявленные «логические» - часто. В EXPLAIN хорошо проиндексированное соединение показывает eq_ref или ref на внутренней таблице; ALL с Using join buffer (hash join) означает, что каждая строка одной таблицы сравнивается с хэшем другой.

На GROUP BY и DISTINCT можно ответить, обходя индекс по порядку вместо построения временной таблицы, если группируемые столбцы - самый левый префикс индекса, а условия равенства стоят перед ними. GROUP BY customer_id по индексу, начинающемуся с customer_id, идёт потоком; тот же запрос по неиндексированному столбцу показывает Using temporary. Для COUNT(*) с группировкой по столбцу узкий индекс по этому столбцу ещё и покрывающий, так что сама таблица вообще не затрагивается.

Длинные строковые столбцы можно индексировать по префиксу: INDEX (url(100)) индексирует первые 100 символов. Лимит InnoDB на ключ индекса - 3072 байта при формате строк DYNAMIC по умолчанию, то есть 768 символов utf8mb4, поэтому VARCHAR(1000) целиком проиндексировать нельзя. Префиксный индекс не может быть покрывающим и не может обслуживать ORDER BY по полному значению; для точного поиска по длинным значениям вроде URL часто лучше индексировать хэш, хранимый в генерируемом столбце, - статья JSON и генерируемые столбцы показывает этот приём.

Разбор примера#

Снова запрос выше: последние оплаченные заказы клиента. В таблице есть индекс только по customer_id. Решение - индекс, соответствующий обоим равенствам и сортировке:

ALTER TABLE orders  ADD INDEX idx_customer_status_created (customer_id, status, created_at),  ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM=INPLACE, LOCK=NONE запрашивает построение онлайн: чтение и запись продолжаются, пока индекс создаётся, а если это невозможно, оператор завершается ошибкой, а не блокирует таблицу молча. На большой таблице это всё равно расходует I/O и временное место, поэтому стройте индекс в спокойное время.

-> Limit: 20 row(s)  (cost=12.4 rows=20) (actual time=0.06..0.09 rows=20 loops=1)    -> Index lookup on orders using idx_customer_status_created       (customer_id=4821, status='paid') (reverse)       (cost=12.4 rows=1874) (actual time=0.06..0.08 rows=20 loops=1)

Прочитано двадцать строк, без сортировки, без фильтра: индекс отдаёт строки в порядке created_at, читаемом в обратную сторону для DESC, а LIMIT останавливается после двадцати. С десяти миллисекунд до меньше чем одной, и, что важнее, стоимость больше не растёт с числом заказов клиента. Добавление total четвёртым столбцом сделало бы индекс покрывающим и избавило бы ещё и от поисков в кластерном индексе - это стоит делать для запроса, который выполняется при каждом просмотре страницы, но не для того, что выполняется раз в час.

Теперь удалите индекс, который заменяет этот. (customer_id) - префикс нового индекса, поэтому он избыточен и только тратит запись и память.

Как держать индексы под контролем#

Индексы не бесплатны. Каждый обновляется при каждом INSERT, при DELETE и при любом UPDATE, затрагивающем его столбцы, и каждый занимает место в buffer pool, которое иначе кэшировало бы данные. Схема sys находит те, что себя не окупают:

-- Indexes not used since the server startedSELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'appdb';-- Indexes made redundant by another (a prefix of a longer one)SELECT table_name, redundant_index_name, dominant_index_nameFROM sys.schema_redundant_indexes WHERE table_schema = 'appdb';

«Не используется с момента запуска» что-то значит, только если сервер проработал полный деловой цикл - индекс, который использует лишь отчёт на конец месяца, 29 дней выглядит неиспользуемым. Перед удалением сделайте его невидимым: он продолжит обновляться, но будет скрыт от оптимизатора:

ALTER TABLE orders ALTER INDEX idx_customer INVISIBLE;-- watch for a week; if nothing slows down:ALTER TABLE orders DROP INDEX idx_customer;-- or, if something did:ALTER TABLE orders ALTER INDEX idx_customer VISIBLE;

Невидимые индексы появились в 8.0, и это самый безопасный способ проверить удаление. MySQL 8 также поддерживает настоящие убывающие индексы (created_at DESC в определении), полезные, когда запрос сортирует по одному столбцу по возрастанию, а по другому по убыванию, чего возрастающий индекс обслужить не может.

Найти, каким запросам вообще нужно внимание, - задача журнала медленных запросов. О том же применительно к PostgreSQL читайте в статьях индексы PostgreSQL и EXPLAIN ANALYZE для медленных запросов; идеи переносятся, формат вывода - нет.

FAQ#

Сколько индексов должно быть у таблицы?

Столько, сколько нужно запросам, и не больше. Большинству таблиц хватает первичного ключа плюс двух-пяти вторичных индексов. Таблица с интенсивной записью и десятью индексами платит десятью обновлениями за каждую вставку; проверьте sys.schema_unused_indexes и удалите то, чем ничто не пользуется.

Важен ли порядок столбцов в WHERE?

Нет. Оптимизатор свободно переставляет условия; WHERE b = 2 AND a = 1 использует индекс по (a, b) точно так же, как WHERE a = 1 AND b = 2. Важен порядок столбцов в определении индекса.

Почему EXPLAIN показывает индекс в possible_keys, но key равен NULL?

Оптимизатор рассмотрел его и оценил полное сканирование как более дешёвое - обычно потому, что условию соответствует большая доля таблицы, или потому, что статистика устарела. Выполните ANALYZE TABLE и проверьте снова; если он по-прежнему предпочитает сканирование, скорее всего, он прав, и запросу нужно более селективное условие.

Стоит ли использовать FORCE INDEX?

Редко и как временную меру. Навязанный индекс остаётся навязанным и после того, как данные изменились и план стал неверным. Сначала исправьте статистику, индекс или запрос, а если подсказка всё же нужна, предпочитайте синтаксис подсказок оптимизатора /*+ INDEX(orders idx_name) */, описанный для 8.0 и новее.

Блокирует ли добавление индекса таблицу?

В MySQL 8 обычно нет. Добавление вторичного индекса к таблице InnoDB - онлайн-операция, допускающая чтение и запись, не считая коротких блокировок метаданных в начале и в конце. Запрашивайте это явно через ALGORITHM=INPLACE, LOCK=NONE, чтобы оператор завершился ошибкой, а не заблокировал таблицу, если онлайн невозможен.