Умеешь ли оптимизировать сложные SQL запросы

Оптимизация сложных SQL запросов

Да, оптимизация SQL — это одна из моих ключевых компетенций. За 12+ лет я имел дело с запросами, которые выполнялись от часа до дней, и научился доводить их до выполнения за миллисекунды.

Методология оптимизации

1. Измерение — найди узкие места

-- Включи EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name;

-- Результат показывает:
-- - Время выполнения (Planning + Execution)
-- - Количество прочитанных строк (actual rows)
-- - Используемые индексы
-- - Seq Scan vs Index Scan

Без EXPLAIN ты работаешь вслепую. Всегда начинай с анализа плана выполнения.

2. Индексирование — самый быстрый способ ускорить запрос

-- ❌ Медленно: последовательный скан всей таблицы
SELECT * FROM orders WHERE user_id = 123;
-- Execution time: 5234ms (если 100M строк)

-- ✅ Быстро: индекс
CREATE INDEX idx_orders_user_id ON orders(user_id);
SELECT * FROM orders WHERE user_id = 123;
-- Execution time: 0.45ms (в 10000 раз быстрее!)

Уметь правильно индексировать — 80% успеха в оптимизации SQL.

3. JOIN оптимизация

-- ❌ Плохо: много JOIN'ов без индексов
SELECT u.id, u.name, o.total, p.name as product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE u.country = 'RU';

-- Можно закончиться полным перемножением таблиц!

-- ✅ Хорошо: правильные индексы
CREATE INDEX idx_users_country ON users(country);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_products_id ON products(id);

4. Избегай N+1 problem

-- ❌ Плохо: N+1 запросов
SELECT * FROM users WHERE active = true;  -- 1000 запросов
for user in users:
    orders = SELECT * FROM orders WHERE user_id = user.id;  -- 1000 запросов!
-- Всего: 1001 запрос, может быть медленнее, чем один JOIN

-- ✅ Хорошо: один запрос с JOIN
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = true;

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

Пример 1: Подсчёт элементов по категориям

-- ❌ Исходный медленный запрос (45 секунд)
SELECT 
    c.name,
    COUNT(p.id) as product_count
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE YEAR(p.created_at) = 2024
GROUP BY c.id, c.name
ORDER BY product_count DESC;

-- Проблемы:
-- - No index on products(category_id)
-- - No index on products(created_at)
-- - Unnecessary LEFT JOIN к order_items
-- - YEAR() function на неиндексированном поле

-- ✅ Оптимизированный (150ms)
-- 1. Добавил индексы
CREATE INDEX idx_products_category_id ON products(category_id);
CREATE INDEX idx_products_created_at ON products(created_at);

-- 2. Убрал лишний JOIN
SELECT 
    c.name,
    COUNT(DISTINCT p.id) as product_count
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
  AND p.created_at >= '2024-01-01'
  AND p.created_at < '2025-01-01'
GROUP BY c.id, c.name
ORDER BY product_count DESC;

-- 3. Или ещё быстрее через предфильтрацию
SELECT 
    c.name,
    COUNT(p.id) as product_count
FROM categories c
LEFT JOIN (
    SELECT id, category_id
    FROM products
    WHERE created_at >= '2024-01-01'
      AND created_at < '2025-01-01'
) p ON c.id = p.category_id
GROUP BY c.id, c.name
ORDER BY product_count DESC;

Пример 2: Поиск с полнотекстовым индексом

-- ❌ Медленно (LIKE — это регулярное выражение, очень медленно)
SELECT * FROM articles
WHERE title LIKE '%python%'
  AND created_at > NOW() - INTERVAL '30 days'
ORDER BY views DESC
LIMIT 10;
-- Time: 8234ms на 10M записей

-- ✅ Быстро (полнотекстовый поиск)
CREATE INDEX idx_articles_title_fts ON articles USING GIN(to_tsvector('russian', title));

SELECT * FROM articles
WHERE to_tsvector('russian', title) @@ plainto_tsquery('russian', 'python')
  AND created_at > NOW() - INTERVAL '30 days'
ORDER BY views DESC
LIMIT 10;
-- Time: 12ms

Пример 3: Window функции вместо сложного GROUP BY

-- ❌ Сложный GROUP BY с подзапросами (медленно)
SELECT 
    user_id,
    order_date,
    total,
    (
        SELECT SUM(total) 
        FROM orders o2 
        WHERE o2.user_id = o1.user_id 
        AND o2.order_date <= o1.order_date
    ) as running_total
FROM orders o1
ORDER BY user_id, order_date;

-- ✅ Window функции (гораздо быстрее)
SELECT 
    user_id,
    order_date,
    total,
    SUM(total) OVER (
        PARTITION BY user_id 
        ORDER BY order_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_total
FROM orders
ORDER BY user_id, order_date;

Инструменты для анализа

# Python: использую для анализа и оптимизации

# 1. SQLAlchemy с echo для просмотра сгенерированных запросов
from sqlalchemy import create_engine
engine = create_engine('postgresql://...', echo=True)
# Все SQL запросы будут выведены в консоль

# 2. django-debug-toolbar (для Django)
# Показывает все SQL запросы, выполненные на странице

# 3. Custom decorator для профилирования
import time
from contextlib import contextmanager

@contextmanager
def query_timer(query_name: str):
    start = time.time()
    yield
    end = time.time()
    print(f"{query_name}: {(end-start)*1000:.2f}ms")

with query_timer("fetch_users"):
    users = session.query(User).all()

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

1. EXPLAIN ANALYZE — всегда начинай с анализа плана
2. Индексы — добавь индексы на колонки в WHERE, JOIN, ORDER BY
3. JOIN vs подзапросы — JOIN обычно быстрее
4. Избегай функций на индексированных полях — YEAR(created_at) медленнее чем created_at >= '2024-01-01'
5. DISTINCT — использует дорогую операцию, попробуй GROUP BY
6. Лимитирование — добавь LIMIT, OFFSET если возможно
7. Partitioning — для очень больших таблиц
8. Caching — Redis кеширование результатов, если данные не часто меняются
9. Денормализация — иногда имеет смысл хранить вычисленные значения
10. Horizontal scaling — репликация, шардинг для очень большого трафика

Реальный кейс из практики

Огромный аналитический запрос выполнялся 45 минут:

- Добавил индексы: 45мин → 5мин
- Переписал JOIN'ы: 5мин → 30сек
- Добавил предфильтрацию: 30сек → 2сек
- Результат: 1350x ускорение!

Оптимизация SQL — это искусство и наука одновременно. Главное — меньше гадать, больше измерять.