9 апреля 2026 г.

Оптимизация SQL запросов — как находить и устранять узкие места

Зачем аналитику оптимизировать запросы

Аналитик пишет запрос, запускает — и ждёт. Минуту, пять, пятнадцать. На маленьких таблицах всё работает мгновенно, но когда данных миллионы строк, неоптимальный запрос может положить сервер или просто сжечь ваше время. На собеседованиях тему оптимизации спрашивают на middle+ позициях: ожидают, что вы умеете читать план выполнения и понимаете, почему один запрос быстрее другого.

Почему запросы тормозят

Основные причины медленных запросов:

  • Full Table Scan (Seq Scan) — СУБД перебирает все строки таблицы вместо использования индекса.
  • Отсутствие индексов — фильтрация и JOIN идут по столбцам без индексов.
  • Избыточные данные — SELECT * вместо нужных столбцов, отсутствие WHERE.
  • Неэффективные JOIN — соединение больших таблиц без условий или с условиями на неиндексированных столбцах.
  • Коррелированные подзапросы — подзапрос выполняется для каждой строки внешнего запроса.
  • Сортировка больших объёмов — ORDER BY без индекса требует временного хранения и сортировки всех строк.

Как читать EXPLAIN

EXPLAIN — главный инструмент диагностики. Он показывает план выполнения запроса: в каком порядке СУБД читает таблицы, какие индексы использует и сколько строк ожидает обработать.

EXPLAIN ANALYZE
SELECT u.username, COUNT(*) AS answers
FROM training_events te
JOIN users u ON u.id = te.user_id
WHERE te.created_at >= '2025-01-01'
GROUP BY u.username
ORDER BY answers DESC
LIMIT 10;

На что смотреть в плане:

Что видите Что делать
Seq Scan на большой таблице Проверить, есть ли индекс на столбцах в WHERE и JOIN
Nested Loop с большим числом строк Убедиться, что внутренний цикл использует индекс
Sort с высоким cost Рассмотреть индекс для ORDER BY
Hash Join или Merge Join Обычно эффективны, но проверьте размер hash-таблицы
actual rows сильно отличается от rows Статистика устарела, выполните ANALYZE

EXPLAIN ANALYZE выполняет запрос по-настоящему и показывает реальное время. Обычный EXPLAIN только оценивает план без выполнения.

Индексы — фундамент оптимизации

Индексы ускоряют поиск с O(n) до O(log n). Базовое правило: если столбец часто встречается в WHERE, JOIN ON или ORDER BY — на него нужен индекс.

-- Без индекса: Seq Scan по таблице events (10M строк)
-- С индексом: Index Scan за миллисекунды
CREATE INDEX idx_events_user_created
ON training_events (user_id, created_at);

Но индексы — не бесплатны. Каждый индекс замедляет INSERT/UPDATE и занимает место. Не создавайте индексы «на всякий случай» — создавайте на основе реальных медленных запросов.

Оптимизация JOIN

JOIN — самая ресурсоёмкая операция в SQL. Несколько правил:

1. Убедитесь, что столбцы в ON проиндексированы. Это критично. JOIN без индекса на большой таблице — почти гарантированный тормоз.

-- Индекс на orders.user_id ускоряет этот JOIN
SELECT u.username, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= '2025-01-01';

2. Фильтруйте до JOIN, а не после. Чем меньше строк входит в соединение, тем быстрее.

-- Плохо: сначала JOIN всех строк, потом фильтр
SELECT u.username, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= '2025-01-01';

-- Лучше: фильтруем orders до JOIN
SELECT u.username, sub.amount
FROM users u
JOIN (
    SELECT user_id, amount
    FROM orders
    WHERE created_at >= '2025-01-01'
) sub ON sub.user_id = u.id;

На практике оптимизатор PostgreSQL часто делает эту перестановку сам. Но в сложных запросах с несколькими JOIN он может ошибиться — и тогда ручная оптимизация помогает.

3. Избегайте лишних JOIN. Если данные из таблицы не используются в SELECT и не влияют на фильтрацию — не подключайте её. Подробнее о типах соединений — в гайде по JOIN.

Подзапрос vs CTE vs временная таблица

Три способа разбить сложный запрос на части:

Подзапрос (subquery) — встроен в основной запрос. Оптимизатор может встроить (inline) подзапрос в основной план, что иногда эффективнее.

SELECT username
FROM users
WHERE id IN (
    SELECT user_id FROM subscriptions WHERE status = 'active'
);

CTE (Common Table Expression) — блок WITH. Читабельнее, но в PostgreSQL < 12 CTE создаёт «забор оптимизации» — оптимизатор не может протолкнуть фильтры внутрь CTE. Начиная с PostgreSQL 12, CTE по умолчанию инлайнятся.

WITH active_subs AS (
    SELECT user_id FROM subscriptions WHERE status = 'active'
)
SELECT u.username
FROM users u
JOIN active_subs s ON s.user_id = u.id;

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

CREATE TEMP TABLE active_users AS
SELECT user_id FROM subscriptions WHERE status = 'active';

CREATE INDEX idx_temp_user ON active_users (user_id);

SELECT u.username
FROM users u
JOIN active_users a ON a.user_id = u.id;

Когда что выбрать:

  • Простой запрос — подзапрос (оптимизатор справится).
  • Сложный запрос, важна читаемость — CTE.
  • Результат нужен многократно или оптимизатор ошибается — временная таблица.

Типичные антипаттерны

Подробный разбор — в статье про SQL антипаттерны. Вот топ-5:

  1. SELECT * — тянет все столбцы, включая ненужные. Увеличивает объём данных и может помешать Index Only Scan.
  2. Функция на индексированном столбце — WHERE YEAR(created_at) = 2025 не использует индекс. Перепишите: WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'.
  3. OR вместо IN — WHERE status = 'a' OR status = 'b' менее оптимально, чем WHERE status IN ('a', 'b').
  4. Коррелированный подзапрос — выполняется для каждой строки. Замените на JOIN.
  5. DISTINCT вместо правильного JOIN — если DISTINCT убирает дубли, значит JOIN написан неправильно.

Практический чеклист оптимизации

  1. Запустите EXPLAIN ANALYZE и найдите самый дорогой узел.
  2. Если Seq Scan — проверьте, нужен ли индекс.
  3. Если Nested Loop с большими таблицами — проверьте индексы в ON.
  4. Уберите SELECT * — выбирайте только нужные столбцы.
  5. Фильтруйте данные как можно раньше (WHERE до JOIN).
  6. Замените коррелированные подзапросы на JOIN.
  7. Для оконных функций — убедитесь, что PARTITION BY использует индексированный столбец.
  8. Проверьте актуальность статистики: ANALYZE table_name.

Вопросы с собеседований

— Как вы оптимизируете медленный SQL-запрос? — Первый шаг — EXPLAIN ANALYZE, чтобы увидеть план выполнения. Смотрю на Seq Scan (нужен ли индекс), Nested Loop (проверяю индексы), Sort (можно ли добавить индекс для ORDER BY). Убираю SELECT *, фильтрую данные раньше, заменяю коррелированные подзапросы на JOIN.

— Чем CTE отличается от подзапроса с точки зрения производительности? — В PostgreSQL 12+ CTE по умолчанию инлайнятся — производительность аналогична подзапросу. В более ранних версиях CTE создавал «забор оптимизации»: оптимизатор не мог протолкнуть WHERE-условия внутрь CTE. Если нужна материализация — используйте WITH ... AS MATERIALIZED.

— Почему запрос с индексом может работать медленнее, чем без индекса? — Если запрос возвращает значительную долю таблицы (например, 30%+), Seq Scan может быть быстрее: он читает данные последовательно с диска, а Index Scan делает случайные обращения. Оптимизатор это учитывает.

— Как ускорить запрос с GROUP BY на большой таблице? — Убедиться, что есть индекс, соответствующий WHERE + GROUP BY. Фильтровать данные до группировки. Если нужна только часть групп — использовать HAVING или фильтрацию в подзапросе. На очень больших таблицах — рассмотреть материализованное представление или предагрегацию.

— Что быстрее: EXISTS или IN? — Зависит от данных. EXISTS останавливается при первом совпадении — эффективнее, когда подзапрос возвращает много строк. IN лучше, когда подзапрос возвращает мало уникальных значений. На практике PostgreSQL часто превращает одно в другое, но знать разницу полезно.

FAQ

Можно ли оптимизировать запрос без создания индексов?

Да. Убрать SELECT *, переписать коррелированный подзапрос в JOIN, фильтровать данные раньше, убрать ненужные ORDER BY и DISTINCT — всё это ускоряет запрос без изменения схемы. Но если узкое место — Seq Scan на миллионах строк, без индекса не обойтись.

Влияет ли порядок таблиц в JOIN на производительность?

В теории оптимизатор сам выбирает оптимальный порядок соединения. На практике — при большом количестве JOIN (5+ таблиц) оптимизатор может ошибиться. В PostgreSQL есть параметр join_collapse_limit, который ограничивает перебор вариантов. Если запрос тормозит — попробуйте поменять порядок таблиц вручную.

Стоит ли всегда использовать EXPLAIN ANALYZE вместо EXPLAIN?

EXPLAIN ANALYZE выполняет запрос по-настоящему — это даёт точные числа, но на тяжёлом запросе может занять минуты. Используйте обычный EXPLAIN для первичной оценки, а ANALYZE — когда нужно подтвердить гипотезу или увидеть реальные vs ожидаемые строки. И помните: EXPLAIN ANALYZE на DELETE/UPDATE выполнит изменения — оборачивайте в транзакцию с ROLLBACK.

Потренируйте вопросы по SQL на реальных задачах — откройте тренажёр. 1500+ вопросов, которые спрашивают на собеседованиях аналитика. Бесплатно.

Тренироваться в Telegram