Оптимизация SQL-запросов: как читать планы выполнения и создавать покрывающие индексы

Представьте ситуацию: ваш API отвечает за 200 мс, а вчера он отвечал за 20. Вы смотрите на код приложения - там ничего не менялось. Проблема скрывается глубже, в базе данных. Часто разработчики винят железо или сеть, но на самом деле виновником выступает неэффективный SQL-запрос, который выполняет лишние операции. Чтобы исправить это, нужно уметь читать то, что говорит нам движок базы данных через план выполнения.

Почему запросы тормозят: механика работы СУБД

СУБД (Система Управления Базами Данных) - это программа, которая хранит данные и обеспечивает их целостность. Когда вы отправляете SQL-запрос, сервер не просто «ищет» строки. Он проходит несколько этапов: парсинг (проверка синтаксиса), оптимизация (выбор стратегии) и выполнение. Именно на этапе оптимизации рождается план выполнения. Если оптимизатор ошибся в выборе стратегии, запрос будет медленным, даже если логика верна.

Основная причина медленных запросов - отсутствие правильных индексов. Индекс работает как оглавление книги: вместо того чтобы читать все страницы подряд (полное сканирование таблицы), база данных сразу переходит к нужному месту. Но индекс - это не панацея. Если вы создадите индекс только на поле, по которому фильтруете, но будете выбирать другие поля из таблицы, база данных всё равно будет делать обратные переходы к основной таблице. Это называется «access by row ID» и часто убивает производительность.

Анатомия плана выполнения: что мы видим в EXPLAIN

Чтобы понять, что происходит под капотом, мы используем команду EXPLAIN (или EXPLAIN ANALYZE в PostgreSQL). Она показывает дерево операций. Давайте разберем ключевые узлы этого дерева на примере PostgreSQL, так как его вывод наиболее информативен для анализа.

• Seq Scan (Последовательное сканирование): База читает таблицу с начала до конца. Для маленьких таблиц (до 1000 строк) это нормально. Для миллионов строк - катастрофа. Если видите Seq Scan на большой таблице, значит, нет подходящего индекса.
• Index Scan / Index Only Scan: Использование индекса. Index Only Scan - это святой Грааль. Он означает, что все необходимые данные были получены прямо из индекса, без обращения к основной таблице. Это максимально быстрая операция.
• Nested Loop Join: Вложенный цикл. Подходит, когда одна из таблиц маленькая (после фильтрации). Если обе таблицы большие, лучше использовать Hash Join или Merge Join.
• Hash Join: Хеш-соединение. Эффективно для больших объемов данных, когда условия соединения простые (равенство).

Важно смотреть не только на тип операции, но и на оценки строк (rows) и время (actual time). Если оптимизатор ожидает 100 строк, а на деле их миллион, значит, статистика устарела, и ему нужны свежие данные.

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

Обычный индекс хранит значения столбца и указатель на строку. Покрывающий индекс (Covering Index) - это расширенная версия обычного индекса, которая дополнительно хранит значения других столбцов, необходимых для запроса. Благодаря этому база данных может ответить на запрос, прочитав только сам индекс, минуя дорогую операцию чтения с диска из основной таблицы.

Как это выглядит на практике? Допустим, у нас есть таблица orders (заказы) с полями customer_id, status и total_amount. Мы хотим найти сумму заказов активных клиентов:

SELECT total_amount 
FROM orders 
WHERE customer_id = 101 AND status = 'active';

Если у нас есть индекс только на customer_id, база найдет строки, где customer_id = 101, но затем ей придется идти в основную таблицу, чтобы проверить status и получить total_amount. Это медленно.

Решение - создать составной индекс, который покрывает весь запрос:

CREATE INDEX idx_orders_covering 
ON orders (customer_id, status) 
INCLUDE (total_amount);

Здесь customer_id и status используются для поиска (фильтрации), а total_amount добавлен через INCLUDE, чтобы его можно было прочитать прямо из индекса. Теперь план выполнения покажет Index Only Scan. Скорость вырастет в разы, особенно если таблица большая и не помещается в оперативную память целиком.

Стратегия создания индексов: от простого к сложному

Не стоит создавать индекс на каждое поле. Каждый индекс замедляет запись (INSERT, UPDATE, DELETE), потому что базу данных приходится обновлять и саму таблицу, и все связанные с ней индексы. Поэтому индексируем только то, что действительно тормозит чтение.

1. Начните с WHERE. Поля, которые чаще всего используются в условиях фильтрации, должны быть первыми в списке колонок индекса.
2. Добавьте JOIN. Если вы часто связываете таблицы, убедитесь, что внешние ключи проиндексированы.
3. Закройте SELECT. Добавьте поля из SELECT в индекс через INCLUDE (в PostgreSQL) или просто включите их в составной индекс (в MySQL/Oracle), чтобы достичь статуса «покрывающего».
4. Учитывайте порядок. Если вы используете ORDER BY, поле сортировки должно идти после полей фильтрации, иначе база будет выполнять дополнительную операцию сортировки.

Частые ошибки при оптимизации

Даже опытные разработчики попадают в ловушки. Вот три самых распространенных:

1. Функции над колонками в WHERE. Если вы пишете WHERE LOWER(email) = '[email protected]', обычный индекс на email бесполезен. Оптимизатор видит функцию и понимает, что искать по индексу нельзя. Решение: создать функциональный индекс CREATE INDEX ON users (LOWER(email)).

2. Игнорирование статистики. Оптимизатор принимает решения на основе статистики распределения данных. Если вы недавно импортировали гигабайты данных, статистика могла устареть. Команда ANALYZE table_name принудительно обновляет эту информацию. Без актуальной статистики оптимизатор может выбрать плохой план (например, Nested Loop вместо Hash Join).

3. Слишком много индексов. Каждый лишний индекс увеличивает размер базы и время записи. Если у вас 5 индексов на одну таблицу, а запись происходит реже, чем чтение, - ок. Если запись интенсивная (логирование событий, телеметрия), пересмотрите набор индексов. Лучше иметь один сложный покрывающий индекс, чем пять простых.

Практический чек-лист для проверки запроса

Перед тем как деплоить новый запрос в продакшн, прогоните его через этот список:

• Запустил EXPLAIN ANALYZE? Видел реальные времена выполнения?
• Есть ли Seq Scan на таблицах больше 10 000 строк?
• Можно ли заменить Index Scan на Index Only Scan, добавив поля в INCLUDE?
• Проверил ли я, что поля в WHERE не обёрнуты в функции?
• Актуальна ли статистика? (Прогнал ANALYZE?)
• Не перегрузил ли я таблицу индексами, замедляющими запись?

Инструменты мониторинга

В PostgreSQL есть встроенное расширение pg_stat_statements. Оно хранит статистику по всем выполненным запросам: сколько раз выполнялся, среднее время, количество чтений из памяти и дисков. Это лучший способ найти «узкие места». Просто сортируйте запросы по mean_exec_time (среднее время выполнения) или total_exec_time (общее время), и вы увидите, какие именно SQL-команды съедают ресурсы сервера.

В MySQL аналогом служит системная таблица performance_schema.events_statements_summary_by_digest. Принцип тот же: находим самые долгие и частые запросы, анализируем их планы и добавляем недостающие индексы.

FAQ