Чек-лист оптимизации SQL-запросов

Короткий ответ

Медленный запрос почти всегда упирается в одно из трёх: база читает слишком много строк, передаёт слишком много данных или приложение делает слишком много запросов. Чек-лист ниже идёт именно в этом порядке. Примеры — для PostgreSQL, но идеи применимы к MySQL и другим СУБД.

Шаг 0: измерьте, а не угадывайте

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

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders WHERE customer_id = 42;

Что искать в плане:

• Seq Scan по большой таблице там, где ожидалась выборка нескольких строк;
• большая разница между ожидаемым (rows=) и фактическим числом строк — повод обновить статистику через ANALYZE;
• узлы, где тратится основная часть времени.

Чтобы найти, какие запросы вообще оптимизировать, включите расширение pg_stat_statements — оно показывает самые тяжёлые запросы по суммарному времени. Помните, что EXPLAIN ANALYZE реально выполняет запрос, поэтому UPDATE и DELETE проверяйте внутри транзакции с ROLLBACK.

Чек-лист

1. Выбирайте только нужные колонки

SELECT * тянет лишние данные по сети, мешает использовать покрывающие индексы и ломается, когда в таблицу добавляют тяжёлую колонку. Перечисляйте поля явно.

2. Пишите sargable-условия

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

Также следите за совпадением типов: сравнение текстовой колонки с числом может привести к приведению типов и отказу от индекса.

3. Проверьте индексы

• Индексируйте колонки из WHERE, JOIN и ORDER BY часто выполняемых запросов.
• В составном индексе порядок важен: сначала колонки с условием равенства, потом диапазон или сортировка. Индекс (customer_id, created_at) подходит для «заказы клиента за период».
• Индексируйте внешние ключи — PostgreSQL не создаёт для них индексы автоматически.
• Не создавайте индексы «на всякий случай»: каждый замедляет запись и занимает место. Неиспользуемые видно в pg_stat_user_indexes.

4. Пагинация без OFFSET

OFFSET 100000 LIMIT 20 заставляет базу прочитать и выбросить сто тысяч строк. Чем дальше страница, тем медленнее. Используйте keyset-пагинацию — продолжение от последнего увиденного значения:

SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Нужен индекс (created_at, id). Минус — нельзя прыгнуть сразу на 500-ю страницу, но для лент и бесконечной прокрутки это и не нужно.

5. Обрабатывайте данные пакетами

• Вставляйте много строк одним INSERT ... VALUES (...), (...) или через COPY, а не тысячей отдельных запросов.
• Большие UPDATE и DELETE делите на порции по ключу — так короче блокировки и меньше нагрузка на репликацию.
• Не держите транзакцию открытой во время сетевых вызовов из приложения.

6. Уберите N+1

N+1 — когда приложение получает список из N записей, а затем делает ещё по запросу на каждую. Сто заказов — сто один запрос. Часто возникает незаметно в ORM.

Решения:

• один запрос с JOIN;
• второй запрос с WHERE id = ANY($1) для всех нужных id сразу;
• в ORM — жадная загрузка связей (include, select_related, prefetch_related и аналоги).

Найти N+1 помогает лог запросов в режиме разработки: если при открытии одной страницы видно десятки одинаковых запросов с разными id — это оно.

7. Проверьте результат

После правки снова запустите EXPLAIN (ANALYZE, BUFFERS) на реалистичном объёме данных. Запрос, быстрый на ста строках тестовой базы, может вести себя иначе на миллионах.

Частые ошибки

• Оптимизировать запрос, который выполняется раз в сутки, вместо того, что вызывается тысячи раз в час.
• Тестировать на пустой локальной базе.
• Добавлять индекс, не проверив, что план его действительно использует.
• Лечить медленные запросы кешем, не разобравшись в причине.