Mysql индексы без боли: когда нужны, какие бывают и как ускорить запросы без вреда

Индексы в MySQL ускоряют чтение, когда запросы отбирают небольшую долю строк по предсказуемым условиям, но легко замедляют записи и усложняют планы, если добавлены наугад. Практика простая: сначала подтвердите проблему через EXPLAIN/ANALYZE, затем выберите минимально нужный индекс под конкретный WHERE/JOIN/ORDER BY и проверьте регрессии.

Коротко: что важно про индексы

Индексы MySQL помогают там, где есть фильтрация/соединения/сортировки по индексируемым полям, и плохо помогают при чтении "почти всей таблицы".
Каждый индекс - это ускорение чтения и цена на INSERT/UPDATE/DELETE, место на диске и больше работы оптимизатора.
Индекс должен соответствовать форме запроса: порядок колонок в составном индексе критичен.
Покрывающие индексы уменьшают обращения к таблице, но их легко "перекормить" лишними колонками.
Перед созданием индекса MySQL фиксируйте базовую метрику и план выполнения, после - сравнивайте и держите план отката.
Любая оптимизация запросов MySQL начинается не с индексов, а с диагностики: какие запросы, какая селективность, какие планы.

Когда индексы действительно ускоряют запросы

Индекс даёт выигрыш, когда он уменьшает объём чтения: вместо полного сканирования таблицы (ALL) сервер читает небольшую часть B-Tree/Hash и быстро находит подходящие строки. На практике это чаще всего: фильтры по равенству/диапазону, JOIN по ключам, ORDER BY/LIMIT по индексному порядку.

Индекс обычно не нужен (или бесполезен), когда:

условие возвращает большую часть таблицы (низкая селективность),
в WHERE используется функция/преобразование над колонкой (ломает использование индекса),
фильтр по "слабому" флагу (например, is_active) без дополнительной колонки,
запрос всегда читает почти всё и делает агрегации по многим строкам - поможет уже другой дизайн/агрегации, а не индекс.

Пример: план выполнения до и после

Ситуация	Запрос	Что смотрим в плане	Ожидаемый эффект
До	`EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND created_at >= '2026-01-01' ORDER BY created_at DESC LIMIT 20;`	`type=ALL`, `key=NULL`, большой `rows`, `Extra: Using filesort`	Полный проход + отдельная сортировка
После	`EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND created_at >= '2026-01-01' ORDER BY created_at DESC LIMIT 20;`	`type=range/ref`, выбран `key=idx_orders_user_created`, меньше `rows`, без `Using filesort` (в идеале)	Чтение узкого диапазона в нужном порядке

Что проверить перед решением "индексировать или нет"

Какие именно запросы "болят" (slow log / performance_schema / APM) и повторяются ли они.
Есть ли LIMIT и насколько он мал относительно объёма данных.
Какие условия в WHERE/JOIN/ORDER BY и совпадают ли они с существующими индексами.
Не скрывается ли проблема в неверных типах/коллациях/приведениях (из-за которых индекс не используется).

Типы индексов в MySQL и случаи их применения

Для InnoDB базовый рабочий инструмент - B-Tree индексы (включая PRIMARY KEY и вторичные). Они подходят для равенств, диапазонов, сортировки и большинства JOIN. Полнотекстовые (FULLTEXT) - для поиска по словам, SPATIAL - для геоданных. UNIQUE помогает и целостности, и плану, но не является "ускорителем" сам по себе.

Что обычно требуется по доступам и инструментам

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

Сравнение подходов: какой индекс выбрать под задачу

Задача	Подход/индекс	Когда уместно	Когда лучше не делать
Быстрый поиск по точному значению	B-Tree по колонке (или составной под WHERE)	WHERE col = ?; JOIN по ключу	Если col почти всегда одинаковая и не комбинируется с другими условиями
Диапазон по дате/числу	B-Tree с ведущей колонкой диапазона (или второй после равенства)	created_at >= ? AND created_at < ?	Если диапазон огромный и всё равно читаете "почти всё"
Уникальность и быстрый доступ	UNIQUE	Нельзя допускать дубликаты (email, external_id)	Если уникальность "логическая", но данные грязные и миграция не готова
Поиск по словам	FULLTEXT	Нужно искать по тексту, а не по префиксу	Если устраивает LIKE 'prefix%' и данных немного

Чек-лист перед добавлением нового типа индекса

Уточните, что таблица InnoDB и текущие индексы mysql не решают задачу уже сейчас.
Проверьте, что запрос формулируется так, чтобы индекс мог использоваться (без функций над колонкой в WHERE).
Зафиксируйте план выполнения (EXPLAIN) и поведение на тестовом стенде/реплике.

Выбор колонок для индексирования: селективность, кардинальность, паттерны

Ниже - безопасная схема выбора индекса под конкретный запрос: от диагностики к минимальному DDL. Это и есть практическая настройка MySQL для быстродействия на уровне запросов и схемы: сначала измеряем, потом меняем.

Мини-чеклист подготовки перед работой с индексами

Соберите 3-5 реальных запросов-кандидатов (из slow log/APM), а не синтетические примеры.
Сохраните текущие планы: EXPLAIN и, если можно, EXPLAIN ANALYZE на тех же параметрах.
Проверьте существующие индексы: SHOW INDEX FROM table_name; и нет ли дубликатов/почти-дубликатов.
Подготовьте план отката (DROP INDEX) и окно накатки (DDL может быть тяжёлым).
Если нагрузка высокая - сначала тестируйте на стенде/реплике и оценивайте влияние на запись.

Зафиксируйте проблемный запрос и его форму.
Сведите варианты к одному "шаблону": какие колонки в WHERE, какие JOIN, есть ли ORDER BY, есть ли LIMIT. Индекс подбирается под форму запроса, а не под таблицу "вообще".
- Команда: EXPLAIN FORMAT=TRADITIONAL ... (или JSON, если удобнее анализировать).
- Если доступно: EXPLAIN ANALYZE ... для проверки фактических операций.
Оцените селективность фильтров и порядок применения.
Выбирайте колонки, которые сильнее сокращают выборку, и используйте их ближе к началу (особенно в составных индексах). Для равенств это обычно лучше, чем для "широких" диапазонов.
- Практика: сравните "сколько строк остаётся" после каждого условия через простые COUNT по тем же предикатам.
- Смотрите на rows в EXPLAIN: он грубый, но помогает видеть порядок величин.
Проверьте паттерны условий: =, IN, диапазоны, префиксы.
B-Tree хорошо работает с =, IN (умеренно), BETWEEN, префиксным LIKE 'abc%'. Условия вида LIKE '%abc%', WHERE DATE(created_at)=... часто ломают использование индекса.
- Если нужна функция - подумайте о нормализации данных или о вычисляемой/материализованной колонке (если это допустимо в вашей версии/архитектуре).
Спроектируйте индекс под WHERE + JOIN + ORDER BY.
Если запрос фильтрует по a и сортирует по b, часто нужен составной индекс (a, b). Для JOIN индекс должен быть на колонке соединения со стороны, где много строк.
- Правило: сортировка "в индексе" возможна, если порядок колонок индекса согласован с ORDER BY и предикатами.
Сделайте DDL минимальным и проверяемым.
Добавляйте один индекс за итерацию и проверяйте эффект; так проще атрибутировать улучшение/регрессию. Для добавления используйте ALTER TABLE ... ADD INDEX или CREATE INDEX (эквивалентно по смыслу).
- Пример: CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);
Подтвердите улучшение и отсутствие побочных эффектов.
Сравните план, время выполнения и нагрузку на запись. Это ключевой шаг, чтобы анализ производительности MySQL был доказательным, а не "кажется быстрее".
- Повторите: EXPLAIN/EXPLAIN ANALYZE на тех же параметрах.
- Проверьте частоту и скорость операций записи на таблицу после добавления индекса.

Чек-лист после выбора и добавления индекса

План выполнения стал использовать нужный ключ (key=...), а тип доступа стал уже (ref/range вместо ALL) там, где ожидается.
Пропала лишняя сортировка/временная таблица, если вы на это рассчитывали (Using filesort/Using temporary).
Нет ухудшения критичных INSERT/UPDATE/DELETE на той же таблице (сравнение до/после).
Индекс не дублирует существующий (в том же порядке колонок или как левый префикс).

Составные индексы: порядок, покрытие и опасные сочетания

В составном индексе порядок колонок определяет, какие условия смогут использовать индекс. Обычно сначала идут колонки с равенством (и высокой селективностью), затем - диапазон/сортировка. Покрывающий индекс полезен, если запрос выбирает ограниченный набор колонок, но его легко превратить в "толстый" индекс, который тормозит запись.

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

Левый префикс соблюдён: запрос использует первые колонки индекса без "пропусков" (иначе часть индекса не работает).
Равенства идут до диапазона: если есть a = ? и b > ?, порядок (a, b) обычно полезнее, чем (b, a).
ORDER BY согласован с индексом: сортировка по тем же колонкам и в совместимом направлении (учитывая предикаты).
JOIN-колонки проиндексированы на стороне с большим объёмом чтения, и типы/коллации совпадают (иначе будут преобразования и отказ от индекса).
Покрытие оправдано: добавленные "в хвост" колонки реально убирают чтение таблицы, а не просто "на всякий случай".
Нет индексов-дублей: новый индекс не является точной копией другого и не перекрывается левым префиксом без надобности.
EXPLAIN показывает ожидаемый ключ и нет деградации в других важных запросах к той же таблице.

Побочные эффекты индексов: операции записи, хранение и планы выполнения

Индексы ускоряют чтение, но увеличивают стоимость записи и могут привести к неожиданным планам: оптимизатор выбирает другой индекс, меняется порядок JOIN, появляются временные таблицы. Ниже - типовые ошибки, из-за которых индексы "без боли" превращаются в долг.

Индексация колонок с низкой селективностью: индекс почти не отсекает строки, а запись дорожает.
Индекс под несуществующий паттерн: запросы используют функции/приведения и индекс не применяется.
Слишком много индексов на таблице: растёт стоимость каждой записи, увеличиваются времена DDL и бэкапов.
Дублирующие индексы: одинаковые наборы колонок (или один является левым префиксом другого без необходимости) - лишние расходы.
Неправильный порядок колонок в составном индексе: ожидали ускорение, но индекс используется частично или не используется вовсе.
Ожидание, что один индекс решит всё: разные запросы требуют разных форм индексов; компромисс нужно подтверждать тестами.
Регрессия планов после изменений: новый индекс может "соблазнить" оптимизатор и ухудшить другой запрос на той же таблице.
Игнорирование стоимости DDL: добавление индекса на большой таблице может занять значимое время и создать нагрузку.

Чек-лист, если после индекса стало хуже

Сравните EXPLAIN/EXPLAIN ANALYZE до/после: какой ключ выбран и где изменился порядок операций.
Проверьте, не стало ли больше Using temporary/Using filesort.
Убедитесь, что статистика актуальна (и что запрос параметризован так же, как в проде).
Проверьте влияние на запись: время/очереди/lock waits на таблице.
Если улучшение не подтверждается измерениями - готовьте откат индекса.

Практический чеклист внедрения, тестирования и отката индексов

Безопасная работа с индексами - это управляемые итерации: один индекс за раз, измерение, мониторинг, откат. Это снижает риск "случайно сломать" и превращает индексы в контролируемую часть настройки MySQL для быстродействия.

Порядок внедрения (рекомендуется)

Сформулируйте цель: какой запрос ускоряем, какие признаки успеха (план, время, нагрузка).
Снимите baseline: EXPLAIN/ANALYZE, замеры времени на одинаковых параметрах, метрики нагрузки.
Добавьте один индекс: минимальный по колонкам, без "на будущее".
Сравните до/после: план, время, побочные эффекты на запись и конкуренцию.
Проверьте соседние запросы: топ запросов к той же таблице, чтобы не получить регрессию.

Критерии отката индекса

Ключевые запросы не ускорились по измерениям (а не по ощущению) или план не изменился ожидаемо.
Заметно ухудшились INSERT/UPDATE/DELETE или выросли ожидания блокировок на таблице.
Оптимизатор стал выбирать новый индекс в неподходящих запросах и это ухудшило общую картину.
Индекс оказался дубликатом или перекрывается существующим без выигрыша.

Как откатить

Удаление: DROP INDEX idx_name ON table_name; (или ALTER TABLE table_name DROP INDEX idx_name;).
После удаления повторите EXPLAIN/ANALYZE и проверку метрик, чтобы подтвердить возврат к baseline.

Альтернативы индексам (когда уместны)

Переписать запрос: убрать функции над колонками в WHERE, использовать диапазоны по исходному типу, сократить выбираемые поля (иногда это сильнее любой "магии").
Изменить модель данных: добавить отдельную колонку для фильтрации/сортировки, денормализовать "горячие" атрибуты, если это оправдано.
Предагрегации/материализация: если отчётные запросы читают слишком много строк, лучше хранить агрегаты отдельно.
Кэширование на уровне приложения: если запросы повторяются и данные обновляются редко, индекс не обязан решать проблему один.

Чек-лист перед тем как считать работу завершённой

Документировано: какой запрос, какой индекс, почему выбран такой порядок колонок.
Есть сравнение планов и замеров до/после.
Проверено влияние на запись и на топ запросов к таблице.
Определён ответственный и сценарий отката (команда, критерий, окно).

Разбираем типичные сомнения и ошибки

Нужно ли индексировать все колонки из WHERE?

MySQL индексы без боли: когда нужны, какие бывают и как не навредить запросам - иллюстрация

Нет. Индекс должен соответствовать наиболее частому и дорогому паттерну; лишние индексы увеличивают стоимость записи и могут ухудшить планы.

Почему EXPLAIN показывает, что индекс есть, но он не используется?

Часто причина в функциях/приведениях в WHERE, несовпадении типов/коллаций в JOIN или в низкой селективности, из-за которой оптимизатор выбирает сканирование.

Поможет ли индекс, если запрос возвращает половину таблицы?

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

Что важнее: селективность или порядок колонок в составном индексе?

Оба фактора связаны: обычно сначала ставят равенства (часто и более селективные), затем диапазоны/сортировку. Неправильный порядок легко делает индекс бесполезным.

Можно ли делать покрывающий индекс "на всякий случай"?

Не стоит: широкий индекс заметно увеличивает стоимость записи и объём хранения. Добавляйте колонки в покрытие только если измеримо уменьшается чтение таблицы.

Как понять, что индекс надо удалить?

Если он не используется в реальных запросах, дублирует другой или ухудшает записи без подтверждённого выигрыша чтения - это кандидат на удаление после проверки на стенде.

Чем полезен EXPLAIN ANALYZE по сравнению с EXPLAIN?

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