Mysql индексы без боли: как читать Explain и ускорять запросы

Чтобы ускорить запросы в MySQL без «боли», начните с чтения плана выполнения через mysql explain, затем подберите индекс под конкретные условия WHERE/JOIN и порядок сортировки. Индекс помогает серверу не сканировать лишние строки, но может замедлить записи. Действуйте по чек‑листу: диагностируйте план, внесите точечную правку индекса, перепроверьте фактическое время.

Что проверить в первую очередь перед правкой индексов

• Запрос воспроизводим: один и тот же SQL, одинаковые параметры, понятный объём данных.
• Есть ли в запросе «якорь» селективности: WHERE по конкретным полям, ограничение диапазона, точный JOIN.
• Не ломает ли запрос индексы функциями/приведениями типов в WHERE (например, DATE(col), CAST).
• Какие индексы уже есть и не дублируют ли они друг друга (одинаковые префиксы составных ключей).
• Проверены ли колlation/типы столбцов в JOIN (несовпадение типов часто ведёт к скану).
• Понятна ли цена изменений: ускорение чтения vs удорожание INSERT/UPDATE/DELETE и рост места.

Как устроены индексы в MySQL и почему это важно

MySQL индексы — это отдельные структуры данных, которые позволяют находить строки без полного прохода по таблице. Для InnoDB основной рабочий вариант — BTREE‑индексы: они эффективны для равенства, диапазонов и упорядочивания. Индекс ускоряет чтение, но добавляет накладные расходы на запись и может «не сработать», если условия запроса не совпадают с порядком полей индекса.

• Кому подходит: таблицы, где чтений больше, чем записей; запросы с фильтрацией, JOIN, ORDER BY, LIMIT; частые выборки «последних N».
• Когда не стоит делать: маленькие таблицы (скан дешевле), колонки с очень низкой селективностью (например, почти всегда одно значение), индекс «на всякий случай» без конкретного запроса, а также когда workload — в основном записи.

Сравнение типов индексов: BTREE, HASH, FULLTEXT, RTREE — когда выбирать

Выбор типа индекса зависит от движка и характера поиска. В большинстве прикладных задач вы будете работать с BTREE (InnoDB). HASH и RTREE — нишевые, FULLTEXT — для полнотекстового поиска. Если ваша цель — оптимизация запросов mysql, сначала убедитесь, что используете поддерживаемый индексом паттерн и у вас есть доступ к изменению схемы.

Когда какой тип уместен

• BTREE: универсальный вариант для =, IN, >/<, BETWEEN, префиксных условий по составному индексу, оптимизации ORDER BY.
• HASH: быстрый поиск по равенству в памяти (например, MEMORY). Для InnoDB «чистого» HASH‑индекса как у MEMORY нет; не планируйте на него архитектуру.
• FULLTEXT: поиск по словам/фразам. Не заменяет BTREE для фильтрации по ID/дате/статусу, часто применяется вместе.
• RTREE: пространственные данные (GIS), специализированные запросы.

Что понадобится перед работой

• Доступ: EXPLAIN, SHOW CREATE TABLE, чтение information_schema/performance_schema (по возможности).
• Права на изменение схемы (или согласованный процесс миграций).
• Тестовая среда или окно, где безопасно прогнать DDL (создание индекса может занять время).
• Понимание версии MySQL и движка таблиц (InnoDB/MEMORY и т. п.).

Чтение вывода EXPLAIN: поля, которыми реально стоит пользоваться

Подготовка перед разбором плана (мини‑чеклист):

• Уберите «шум»: оставьте в запросе только нужные поля, временно уберите лишние JOIN, если они не влияют на результат.
• Проверьте типы параметров: строки в кавычках, числа без кавычек; не смешивайте INT и VARCHAR в сравнениях.
• Убедитесь, что сравнение идёт по «сырому» столбцу, без функций слева от оператора.
• Зафиксируйте цель: уменьшить скан (rows), убрать filesort/temporary, добиться ref/range вместо ALL.

1. Снимите план: EXPLAIN для запроса в текущем виде

   Запустите EXPLAIN (или EXPLAIN FORMAT=JSON, если удобно) для вашего SELECT. Это «точка А», к которой вы будете возвращаться после каждой правки.

   • Быстрый шаблон: EXPLAIN SELECT ...
   • Если есть сомнения по фактическому времени — позже переходите к EXPLAIN ANALYZE.
2. Сфокусируйтесь на type: чем ближе к ALL, тем хуже

   Поле type показывает способ доступа: ALL — полный скан, range — диапазон, ref/eq_ref — точные обращения по ключу. Для ускорение запросов mysql обычно цель — уйти от ALL на больших таблицах.

3. Проверьте key и possible_keys: почему выбран (или не выбран) индекс

   possible_keys — кандидаты, key — фактический выбор. Если possible_keys пуст, индекса для такого предиката нет (или условие неиндексируемое). Если кандидаты есть, но key пуст — MySQL решил, что скан дешевле, либо условие не подходит по порядку полей.

4. Оцените rows и filtered: где происходит основной «пролив»

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

5. Смотрите Extra: temporary, filesort и индексное покрытие

   В Extra отметьте Using temporary и Using filesort — это частые признаки неоптимального ORDER BY/GROUP BY. Using index может означать покрывающий индекс (не всегда), когда данные берутся из индекса без чтения строк таблицы.

   • Если нужен ORDER BY — проверьте, совпадает ли его порядок с порядком столбцов в индексе.
   • Если SELECT тянет много колонок — покрывающий индекс может стать слишком «толстым», оцените цену.
6. Сопоставьте plan с запросом: какое условие должно «вести»

   Сверьте: какие предикаты в WHERE и ON реально отрабатывают по индексу. Часто проблема не в отсутствии индекса, а в том, что ведущий столбец составного индекса выбран неудачно.

Расшифровка планов: как находить узкие места и лишние сканирования

• Есть ли type=ALL на крупной таблице без жёсткого LIMIT и без высокой селективности — признак, что индекс не используется.
• key выбран, но rows всё равно большой: возможно, индекс не по тем столбцам или условие слишком широкое (нужен более селективный префикс).
• Using filesort при ORDER BY: проверьте, можно ли поддержать сортировку индексом (порядок и направления столбцов, отсутствие «разнородных» условий).
• Using temporary на GROUP BY: проверьте группировку по индексируемому префиксу и не тянете ли лишние поля.
• JOIN по колонкам разных типов/коллаций: даже с индексами сервер может уйти в скан из-за преобразований.
• Условия вида WHERE function(col)=... или WHERE col+0=... часто выключают индекс (предикат не sargable).
• Широкий SELECT * мешает покрытию и увеличивает I/O — проверьте, можно ли вернуть только нужные поля.
• OR по разным столбцам: часто приводит к плохим планам; иногда помогает разбиение на UNION ALL с отдельными индексами.

Практическая чек‑лист‑последовательность для ускорения конкретного запроса

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

1. Стабилизируйте запрос: зафиксируйте SQL и параметры; исключите случайные условия и разные выборки данных.
2. Снимите базовый план: сохраните текущий mysql explain и отметьте проблемный шаг (ALL/filesort/temporary/большие rows).
3. Проверьте «индексируемость» предикатов: уберите функции над столбцом в WHERE/ON, проверьте сравнение одинаковых типов.
4. Соберите правильный составной индекс: начинайте с наиболее селективных условий равенства, затем добавляйте диапазонные/сортировку, если это нужно для плана.
5. Создайте индекс явно и точечно: если вопрос «как создать индекс в mysql» — используйте CREATE INDEX или ALTER TABLE ... ADD INDEX на нужные поля и имя индекса, понятное по назначению.
6. Переснимите план и сравните: изменился ли type, уменьшились ли rows, исчезли ли filesort/temporary.
7. Уберите дубли: если новый индекс перекрывает старый по левому префиксу и тот больше нигде не нужен — планируйте удаление после проверки.

Частые ошибки при работе с индексами

• Добавлять одиночные индексы «на каждую колонку» вместо одного составного под реальный WHERE/JOIN.
• Ставить в начало составного индекса низкоселективный столбец (например, статус), из-за чего индекс плохо фильтрует.
• Надеяться, что индекс поможет при LIKE '%текст%' (обычно не поможет; там чаще нужен FULLTEXT или изменение поиска).
• Игнорировать ORDER BY: запрос фильтруется индексом, но затем сортируется «внешне» (filesort) — итог всё равно медленный.
• Не учитывать цену записи: на горячих таблицах лишний индекс может ухудшить общую производительность.
• Оставлять функции/выражения над индексируемым полем в условии и удивляться, что план не меняется.
• Сравнивать VARCHAR с числом/другой коллацией в JOIN и получать скан при наличии индексов.

После изменений: как валидировать эффект (EXPLAIN ANALYZE, профайлинг, метрики)

Подтверждайте улучшения не только планом, но и фактическими измерениями. Подход выбирайте по тому, что вам доступно в окружении и насколько рискованно вмешательство.

• EXPLAIN ANALYZE — уместно, когда нужно увидеть фактические шаги и время выполнения по операциям; используйте на стенде или в контролируемых условиях.
• Performance Schema / sys schema — уместно, когда нужны топ‑запросы и агрегаты по времени/вызовам без ручного бенчмарка; полезно для поиска кандидатов на индексацию.
• Slow Query Log — уместно, когда вы хотите поймать реальные медленные запросы в проде и затем разбирать их по одному.
• Прикладные метрики и трассировка (APM/логирование времени) — уместно, когда важно влияние на пользовательские сценарии и «хвосты» латентности, а не только среднее время.

Короткие ответы на типичные сомнения по индексам и EXPLAIN

Если EXPLAIN показывает key=NULL, индекс точно не нужен?

Нет. Это означает, что для текущего вида предикатов индекс не применяется или не считается выгодным. Сначала проверьте sargable‑условия и соответствие типов, затем думайте про новый индекс.

Почему добавил индекс, а план не изменился?

Часто причина — порядок столбцов в составном индексе, выражения над колонкой в WHERE или низкая селективность. Ещё вариант: оптимизатор оценивает, что скан дешевле на текущей статистике.

Нужно ли всегда добиваться Using index?

Нет. Покрывающий индекс полезен, но может раздуть размер индекса и ухудшить записи. Цель — минимальный общий I/O и стабильное время, а не конкретная строка в Extra.

Как понять, что нужен составной индекс, а не одиночный?

Если запрос фильтруется по нескольким полям одновременно (WHERE + JOIN, либо WHERE + ORDER BY), одиночные индексы часто не дают нужного плана. Составной индекс строят под реальный набор условий и их порядок.

FULLTEXT заменяет обычные mysql индексы для поиска по тексту?

Он решает задачу поиска по словам, но не заменяет BTREE для фильтрации по ID, датам, статусам и для JOIN. Обычно FULLTEXT дополняет схему, а не «универсален».

Можно ли ускорить запросы mysql только переписыванием SQL, без индексов?

Да: убрать функции в WHERE, уменьшить выборку колонок, заменить OR на UNION ALL в некоторых случаях, пересобрать JOIN. Но если фильтрация по большому объёму данных неизбежна, индекс часто даёт основной эффект.

Как безопаснее всего катить новый индекс на прод?

Сначала проверьте на копии данных или стенде, затем добавляйте индекс в окно низкой нагрузки и мониторьте время запросов и нагрузку на запись. После — убедитесь, что индекс реально используется и не дублирует существующие.