Оптимизация SQL: EXPLAIN и индексы на практике

Когда страница начинает грузиться по 5 секунд, в 9 случаях из 10 виноват не PHP, а SQL. Запрос без индекса на таблице в миллион строк — это полный скан, и никакой кэш в Redis не спасёт, если сама БД отвечает медленно.

EXPLAIN: смотрим, что делает запрос

EXPLAIN показывает план выполнения запроса. Какие таблицы читает, какие индексы использует, сколько строк планирует обработать.

EXPLAIN SELECT * FROM posts
WHERE status = 'published' AND published_at <= NOW()
ORDER BY published_at DESC
LIMIT 20;

Что смотрим:

• type — тип доступа. ALL (full scan) — плохо, ref или range — хорошо, const/eq_ref — отлично.
• rows — сколько строк БД ожидает прочитать. Если миллион — ждите тормозов.
• key — какой индекс используется. NULL — индекс не используется.
• Extra — дополнительная информация. Using filesort и Using temporary — красные флаги.

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

Индекс — это отдельная структура данных (B-tree), позволяющая быстро находить строки по значению. Стоимость — место на диске и замедление вставки/обновления.

Простой индекс:

CREATE INDEX idx_posts_status ON posts(status);

Составной индекс — на несколько колонок. Порядок имеет значение:

CREATE INDEX idx_posts_status_published ON posts(status, published_at);

Этот индекс работает для:

• WHERE status = ?
• WHERE status = ? AND published_at < ?
• WHERE status = ? ORDER BY published_at

Но не работает для:

• WHERE published_at < ? — нужно совпадение слева направо.

Это называется leftmost prefix rule и это первое, что нужно запомнить про составные индексы.

Когда индексы НЕ помогают

• WHERE LOWER(email) = ? — функция от колонки убивает индекс. Решение — функциональный индекс или хранить email уже в нижнем регистре.
• WHERE name LIKE '%foo%' — поиск с ведущим % не использует индекс. Для текстового поиска — full-text или Elasticsearch.
• WHERE status != 'published' — отрицание плохо ложится на индекс.
• Маленькие таблицы — БД быстрее прочитает всё, чем лезть в индекс.

Главные грехи

N+1. Получили 100 постов, для каждого отдельным запросом достаём автора. 101 запрос вместо 2. В Laravel — with() или load().

SELECT * на широких таблицах. Вытащили 30 полей, использовали 3. Лишний трафик и память.

Индексы на каждое поле. Каждый индекс замедляет INSERT и UPDATE. Индекс должен быть оправдан реальным запросом.

Индекс на колонку с низкой кардинальностью. Поле is_active (true/false) — индекс почти бесполезен, БД быстрее прочитает таблицу.

Простой алгоритм оптимизации

1. Найти медленный запрос (slow log, APM, Sentry).
2. Запустить EXPLAIN.
3. Посмотреть на type и rows.
4. Если full scan — добавить индекс под условия WHERE и ORDER BY.
5. Перепроверить EXPLAIN.
6. Замерить на проде (на staging данных в 1000 раз меньше — оптимизация может дать другой результат).

Запомнить

• EXPLAIN — первый инструмент при тормозах.
• Составной индекс работает слева направо.
• Функция от колонки убивает индекс.
• Индекс — это компромисс: быстрее чтение, медленнее запись.
• Оптимизировать нужно конкретный запрос на реальных данных, а не «всё подряд на всякий случай».