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

Одна из самых болезненных и критически важных тем в работе аналитика — оптимизация SQL-запросов. Если вы сталкивались с ситуацией, когда ваш запрос «висит» минутами, а то и часами, или того хуже, приводит к нагрузке на базу, эта статья для вас.

Умение писать не только правильный, но и быстрый код — это то, что отделяет джуниор-аналитика от сеньора, которому можно доверить работу с критической инфраструктурой.

Почему запросы тормозят? Фундаментальные причины

Прежде чем бросаться «чинить» запрос, нужно понять, что именно мы чиним. Давайте посмотрим на ключевые причины, почему ваши запросы медленные:

Отсутствие или неверное использование индексов

Представьте, что вам нужно найти конкретное предложение в книге без оглавления. Вам придётся перелистывать все страницы. Индекс в базе данных — это и есть то самое оглавление. Это специальная структура данных, которая позволяет быстро найти строки, соответствующие условию в WHERE без полного сканирования таблицы.

Проблема: запрос SELECT * FROM orders WHERE customer_id = 12345 без индекса на customer_id вынудит СУБД просканировать всю таблицу orders. Если в ней миллионы строк, это займёт очень много времени.

Решение: создать индекс или использовать уже существующий. Чтобы создать индекс, нужно:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Теперь поиск будет почти мгновенным.

Важные нюансы об индексах:

• Индексы замедляют INSERT, UPDATE и DELETE. При каждой модификации данных СУБД приходится перестраивать индексы. Слишком большое их количество может убить производительность операций записи.
• Тип данных имеет значение. Индексы хорошо работают с высокоселективными столбцами где много уникальных значений, например, email или id клиента/заказа. Индексировать колонку gender (со значениями 'M', 'F'), как правило, бессмысленно.

Отсутствие или не использование партиций

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

При обращении к таблице заранее проверяйте, по какому полю она партицирована.

Неоптимальные JOIN'ы

Сама по себе операция JOIN мощная, но её неправильное использование — частая причина замедления запросов. Ниже представлены самые частые ошибки при использовании джоинов.

• Отсутствие условия соединения (или неверное условие): случайный CROSS JOIN, порождающий миллиарды строк — классическая ошибка, которая может привести к печальным последствиям.
• Соединение по неиндексированным полям: если вы соединяете две большие таблицы по полю, на котором нет индекса, СУБД будет вынуждена делать Nested Loops, сравнивая каждую строку одной таблицы с каждой строкой другой (де-факто, CROSS JOIN с фильтром). Это катастрофически медленно.
• Неправильный порядок JOIN'ов: оптимизатор запросов обычно умный, но иногда он может выбрать неверную стратегию. Если вы соединяете 5 таблиц, и одна из них очень маленькая, а остальные большие, часто эффективнее начать с маленькой, чтобы сразу сократить результирующий набор.

Проблемы с выборкой данных

SELECT * — зло. Выборка всех колонок, особенно из широких таблиц, создает избыточную нагрузку на дисковую подсистему и сеть. Выбирайте только те столбцы, которые действительно нужны. SELECT id, name, email почти всегда лучше, чем SELECT *.

Использование ненужной сортировки, группировки или DISTINCT в запросах

Часто бывает, что во время исследования данных мы писали ORDER BY, GROUP BY или DISTINCT и забыли его убрать. Это сильно затормозит ваш запрос, поэтому не забывайте проверять свой скрипт перед запуском и удалять все лишнее.

Лайфхак: если вам всё-таки нужно уникализировать записи, то попробуйте вместо DISTINCT использовать GROUP BY – эффект вас приятно удивит. Например, вместо SELECT DISTINCT client_id FROM clients используйте SELECT client_id FROM clients GROUP BY client_id.

Отсутствие фильтрации

Всегда используйте фильтрацию. Зачастую для аналитики вам нужны данные максимум за последние 2-3 года, поэтому нет смысла обрабатывать данные с 2000 года, которые содержат миллионы строк.

Бесплатный курс «Основы SQL»
Научитесь работать c одним из главных инструментов аналитика — библиотекой SQL
Записаться

Практические приёмы оптимизации

Давайте перейдём от теории к конкретным рецептам. Ваш лучший друг в этом деле — EXPLAIN (или EXPLAIN ANALYZE). Эта команда покажет «план выполнения» запроса — пошаговую инструкцию, которую использует СУБД. Нам нужно научиться его читать.

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

• Seq Scan (Sequential Scan): полное сканирование таблицы. Красный флаг для больших таблиц.
• Index Scan / Index Only Scan: хорошо! Запрос использует индекс.
• Nested Loop: нормально для маленьких таблиц, плохо для больших.
• Hash Join / Merge Join: эффективные алгоритмы соединения для больших наборов данных.
• Sort: дорогая операция, особенно если она происходит на диске, а не в памяти.

Пример использования:

EXPLAIN ANALYZE
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 100000;

В выводе вы увидите дерево операций и их стоимость. Ваша цель — минимизировать общую стоимость и избавиться от Seq Scan на больших таблицах.

Порядок чтения SQL-запроса: почему это важно для оптимизации

Прежде чем переходить к исправлению запросов, важно понять, как СУБД считает запрос — это позволит по-другому посмотреть на ваш запрос и понять, где вы ошиблись.

Многие ошибочно полагают, что СУБД выполняет запрос в том порядке, в котором он написан. На самом деле, порядок выполнения кардинально отличается от порядка написания. Понимание этой разницы — ключ к осознанной оптимизации.

Порядок написания:

SELECT -- 5: выборка полей
    e.name,
    d.department_name,
    COUNT(o.id) as order_count
FROM employees e -- 1: определение таблиц
JOIN departments d ON e.department_id = d.id -- 2: соединения
LEFT JOIN orders o ON e.id = o.employee_id -- 3: дополнительные соединения
WHERE e.salary > 50000 -- 4: фильтрация
GROUP BY e.id, e.name, d.department_name -- 6: группировка
HAVING COUNT(o.id) > 5 -- 7: фильтрация после группировки
ORDER BY order_count DESC -- 8: сортировка
LIMIT 10; -- 9: ограничение вывода

Порядок выполнения (приблизительный):

• FROM/JOIN — определение и соединение таблиц;
• WHERE — фильтрация строк;
• GROUP BY — агрегация;
• HAVING — фильтрация агрегированных данных;
• SELECT — вычисление выражений;
• ORDER BY — сортировка;
• LIMIT/OFFSET — ограничение результата.

Запомните этот порядок — ниже он нам ещё пригодится.

Курс-симулятор «Аналитик данных»
Станьте аналитиком данных, за которым работодатели выстраиваются в очередь. За 3-5 месяцев обучения на реальных задачах бизнеса
Записаться на консультацию

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

Теперь мы готовы посмотреть на наши запросы новым взглядом и исправить их.

1. Замена коррелированных подзапросов на JOIN

Коррелированный подзапрос выполняется для каждой строки внешнего запроса. Это аналог цикла в программировании и частая причина медленной работы запросов.

Медленный вариант:

SELECT
    e1.employee_id,
    e1.first_name,
    e1.salary,
    (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id) AS avg_dept_salary
FROM employees e1
WHERE e1.salary > 50000;

Здесь для каждого из, скажем, 10 000 сотрудников выполняется отдельный подзапрос для расчёта средней зарплаты по его отделу.

Быстрый вариант:

SELECT
    e1.employee_id,
    e1.first_name,
    e1.salary,
    e2.avg_dept_salary
FROM employees e1
JOIN (
    SELECT department_id, AVG(salary) AS avg_dept_salary
    FROM employees
    GROUP BY department_id
) e2 ON e1.department_id = e2.department_id
WHERE e1.salary > 50000;

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

2. Использование EXISTS вместо IN для проверки существования

Особенно это актуально, когда подзапрос в IN может вернуть большой набор данных.

Медленный вариант:

SELECT *
FROM customers c
WHERE c.id IN (SELECT customer_id FROM orders WHERE total_amount > 1000);

Подзапрос IN должен вычислить и сохранить весь список customer_id, а потом проверить вхождение в него для каждого клиента. Вместо этого используйте EXISTS.

Быстрый вариант:

SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id AND o.total_amount > 1000
);

EXISTS останавливается, как только находит первую же подходящую строку. Это почти всегда работает гораздо быстрее, особенно когда совпадений мало.

3. Ранняя фильтрация и агрегация

Старайтесь максимально сокращать объём данных на самых ранних этапах запроса.

Медленный вариант:

SELECT
    c.country,
    AVG(o.total_amount)
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'shipped'
GROUP BY c.country;

Здесь сначала делаются все соединения по всем данным, и только потом применяется фильтр o.status = 'shipped' и агрегация.

Быстрый вариант:

WITH filtered_orders AS (
    SELECT customer_id, total_amount
    FROM orders
    WHERE status = 'shipped' -- Фильтруем СРАЗУ!
)
SELECT
    c.country,
    AVG(fo.total_amount)
FROM customers c
JOIN filtered_orders fo ON fo.customer_id = c.id
GROUP BY c.country;

Используя CTE (WITH), мы сначала создаём сильно уменьшенный набор отфильтрованных заказов и уже с ним работаем дальше. Это резко снижает нагрузку на этапе JOIN.

4. Избегание функций в условиях WHERE

Использование функций над колонками в WHERE не позволяет использовать индекс.

Медленный вариант (не использует индекс по created_at):

SELECT *
FROM orders
WHERE DATE_PART('year', created_at) = 2023;

Быстрый вариант (позволяет использовать индекс):

SELECT *
FROM orders
WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

5. Фильтрация внутри JOIN

Давайте вспомним порядок выполнения запроса. На первых местах у нас стоит FROM и JOIN, именно поэтому можно не дожидаться выполнения WHERE, а сразу ограничить размер выборки. Давайте разберём на примере.

Медленный вариант:

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.is_active = true OR d.id IS NULL;

Быстрый вариант:

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id AND d.is_active = true;

Объясню на примере, почему это работает. Например, нам нужен список всех сотрудников и их активных заказов за последний месяц.

Данные:

• Таблица employees: 10,000 сотрудников;
• Таблица orders: 1,000,000 заказов, но только 50,000 из них имеют статус 'active' и созданы за последний месяц.

Вариант 1: условие в WHERE (медленный вариант):

SELECT
    e.id,
    e.name,
    o.order_date,
    o.amount
FROM employees e
LEFT JOIN orders o ON e.id = o.employee_id  -- Соединяем по ВСЕМ заказам
WHERE o.status = 'active'                   -- А потом отфильтровываем
    AND o.created_date >= TRUNC(SYSDATE) - INTERVAL '30 days';

Что здесь происходит (проблема):

1. СУБД выполняет LEFT JOIN между 10,000 сотрудников и 1,000,000 заказов.
2. На этом этапе создаётся промежуточный результат размером до 10,000 * 1,000,000 строк (теоретически), хотя фактически это будет меньше из-за связи e.id = o.employee_id, но всё равно огромно.
3. Только после этого гигантского соединения применяется условие WHERE, которое отсекает 95% данных.

Фактически, LEFT JOIN превратился в INNER JOIN, потому что условие WHERE o.status = 'active' исключает всех сотрудников без заказов (для них o.status будет NULL).

Вариант 2: условие внутри JOIN (быстрый вариант):

SELECT
    e.id,
    e.name,
    o.order_date,
    o.amount
FROM employees e
LEFT JOIN orders o ON e.id = o.employee_id 
                   AND o.status = 'active'           -- Фильтр ВНУТРИ соединения!
                   AND o.created_date >= TRUNC(SYSDATE) - INTERVAL '30 days';

Что здесь происходит (оптимально):

1. СУБД берёт таблицу orders и СРАЗУ применяет к ней условия o.status = 'active' AND o.created_date >= TRUNC(SYSDATE) - INTERVAL '30 days'
2. Из 1,000,000 заказов остаётся всего ~50,000.
3. Теперь выполняется LEFT JOIN между 10,000 сотрудников и уже отфильтрованными 50,000 заказов.

Мы сократили объём данных для операции соединения в 20 раз, при этом сохранили всех сотрудников, даже тех, у кого нет активных заказов. Если вам нужно убрать сотрудников без заказов, то замените LEFT JOIN на INNER JOIN.

Лайфхак: чаще всего INNER JOIN работает быстрее, чем LEFT JOIN, поэтому если вам не нужны все строки из левой таблицы, то используйте внутреннее соединение.

Культура производства: что делать, чтобы не тушить пожары

• Профилирование — прежде всего. Не гадайте на кофейной гуще. Всегда используйте EXPLAIN ANALYZE перед запуском тяжелого запроса.
• Мониторьте долгие запросы. Для джобов настройте логирование со временем выполнения, чтобы вы всегда могли увидеть, сколько времени тратится на выполнение каждого запроса. Также можно настроить алерт-систему, которая будет сообщать вам о запросах, выполняющихся дольше N секунд.
• Пишите модульные запросы. Разбивайте сложные запросы на части с помощью CTE (WITH). Это не только улучшает читаемость, но и помогает оптимизатору.
• Помните о статистике. СУБД собирает статистику о распределении данных в таблицах. Если статистика устарела, оптимизатор может выбрать неверный план. При резком изменении данных может потребоваться принудительный сбор статистики (ANALYZE table_name).

---

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

Чтобы начать оптимизацию текущих запросов, не обязательно сразу пытаться исправить все. Начните с малого:

1. Избавьтесь от SELECT *.
2. Убедитесь, что все JOIN и условия в WHERE по ключевым полям имеют индексы.
3. Включите EXPLAIN ANALYZE в ваш ежедневный workflow.

Следуя этим принципам, вы превратитесь из «тушильщика» медленных запросов в архитектора быстрых и надёжных решений, которые будут стабильно работать даже на растущих объёмах данных. Это навык, который окупится сторицей. Удачи в оптимизации!

Присоединяйтесь к нам в Telegram!
Общение с единомышленниками и специалистами на опыте, а также много полезностей: статьи, руководства, вебинары и практические задания
Подписаться