Ниже — краткие, практичные способы оптимизации запросов с вложенными подзапросами и пример переписывания. Сначала — общие принципы, затем конкретный пример.
1) Переписывание запроса
- Замените коррелированные подзапросы в SELECT или WHERE на JOIN/агрегацию или оконные функции. Коррелированный подзапрос часто даёт стоимость порядка $O(n\times m)$ вместо $O(n+m)$.
- Используйте EXISTS вместо IN, если подзапрос возвращает много строк или содержит NULL.
- Для получения «последней» записи по группе удобнее оконные функции: ROW_NUMBER()/FIRST_VALUE или агрегация + join на агрегат.
- Избегайте функций над индексируемыми колонками в фильтрах (не sargable), перенесите вычисления в сторону констант/парам.
- При больших промежуточных наборах данных рассмотрите материализацию: временная таблица или MATERIALIZED VIEW.
2) Индексы и покрывающие индексы
- Добавьте составной индекс, охватывающий фильтры и сортировку, например $(custome{r}_{i}d,create{d}_{a}tDESC,amount)$ — это покрывающий индекс для запроса «последний заказ клиента».
- Для частичных случаев используйте partial/filtered index (например, только active = true).
- Индексы на колонках, используемых в JOIN/WHERE/ORDER BY: уменьшат число просканируемых строк и исключат дополнительную сортировку.
3) Статистика и планировщик
- Обновите статистику: PostgreSQL: `ANALYZE table;`, MySQL: `ANALYZE TABLE table;`.
- Увеличьте target statistics для специфичных колонок при необходимости (Postgres: `ALTER TABLE ... ALTER COLUMN ... SET STATISTICS`), чтобы планировщик точнее оценивал кардинальность.
- Используйте EXPLAIN (ANALYZE, BUFFERS) / EXPLAIN FORMAT=JSON, чтобы увидеть узкие места: nested loop, hash join, seq scan и т.п.
4) Другие практики
- Уменьшите набор возвращаемых колонок (не SELECT *).
- Пагинация: избегайте OFFSET большого значения — используйте keyset pagination.
- Разделение/партиционирование больших таблиц по дате или по ключу.
- Для читаемых часто, но редко меняющихся агрегатов — материализованные представления.
- Для конкретных СУБД: в MySQL старых версий подзапросы часто хуже — перепишите в JOIN; в Postgres — оконные функции и CTE (WITH) бывают полезны, но помните: в старых Postgres CTE материализируются по умолчанию (проверить).
Пример: исходный медленный фрагмент (коррелированный подзапрос для последнего заказа и счётчик):
SELECT c.id, c.name,
(SELECT o.amount FROM orders o WHERE o.customer_id = c.id ORDER BY o.created_at DESC LIMIT 1) AS last_amount,
(SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS orders_cnt
FROM customers c
WHERE c.status = 'active';
Переписанные варианты (предпочтительный — окно + фильтрация по rn = 1):
WITH orders_last AS (
SELECT customer_id, amount AS last_amount, orders_cnt
FROM (
SELECT customer_id,
amount,
row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn,
count(*) OVER (PARTITION BY customer_id) AS orders_cnt
FROM orders
) t
WHERE rn = 1
)
SELECT c.id, c.name, ol.last_amount, coalesce(ol.orders_cnt, 0) AS orders_cnt
FROM customers c
LEFT JOIN orders_last ol ON ol.customer_id = c.id
WHERE c.status = 'active';
Альтернатива через агрегат + join (если created_at однозначно или есть tie-breaker):
WITH agg AS (
SELECT customer_id, max(created_at) AS last_created_at, count(*) AS orders_cnt
FROM orders
GROUP BY customer_id
)
SELECT c.id, c.name, o.amount AS last_amount, coalesce(a.orders_cnt, 0) AS orders_cnt
FROM customers c
LEFT JOIN agg a ON a.customer_id = c.id
LEFT JOIN orders o ON o.customer_id = a.customer_id AND o.created_at = a.last_created_at
WHERE c.status = 'active';
Рекомендации по индексам и статистике для этого примера
- Индекс: CREATE INDEX idx_orders_customer_created_amount ON orders(customer_id, created_at DESC, amount);
- Статистика: PostgreSQL: RUN `ANALYZE orders; ANALYZE customers;` и при необходимости увеличить статистический таргет по колонке created_at.
- Проверка: EXPLAIN (ANALYZE, BUFFERS) — ищите замену Nested Loop на Hash Join/Index Scan и уменьшение числа прочитанных страниц.
Коротко: перепишите коррелированные подзапросы в JOIN/окна, создайте покрывающие индексы по колонкам JOIN/WHERE/ORDER BY, обновите статистику и анализируйте план через EXPLAIN, при необходимости материализуйте тяжёлые агрегации или партиционируйте таблицы.