Как оптимизировать SQL-запросы?

# SQL Query Optimization: Полное руководство

Основные принципы оптимизации

Оптимизация SQL — это итеративный процесс: EXPLAIN → Analyse → Optimize → Measure. Без измерения нет оптимизации.

1. Understand Query Execution Plans

Использование EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
WHERE u.created_at > '2024-01-01'
ORDER BY order_count DESC;

Результат показывает:

• Seq Scan vs Index Scan (sequential = медленнее)
• Стоимость операции (Cost)
• Количество строк
• Фактическое время выполнения

Красные флаги:

• Seq Scan на большие таблицы → нужен индекс
• Hash Join vs Nested Loop → выбор метода join
• Filter в конце плана → predicate pushdown problem

2. Индексирование

Правило: Index for WHERE, JOIN, ORDER BY

-- ❌ Плохо: нет индекса на WHERE
SELECT * FROM users WHERE created_at > '2024-01-01';  -- Seq Scan

-- ✅ Хорошо: индекс на часто используемую колонку
CREATE INDEX idx_users_created_at ON users(created_at);
SELECT * FROM users WHERE created_at > '2024-01-01';  -- Index Scan

Составные индексы (Composite Index)

-- Если часто фильтруем по (status, created_at)
CREATE INDEX idx_orders_status_date 
ON orders(status, created_at DESC);

SELECT * FROM orders 
WHERE status = 'completed' AND created_at > '2024-01-01';

Порядок колонок важен!

• Слева направо: WHERE conditions → ORDER BY DESC → SELECT
• Более селективные колонки в начале

Partial Index (для фильтрации)

-- Индекс только для активных заказов (экономим место)
CREATE INDEX idx_orders_active 
ON orders(user_id) 
WHERE status = 'completed';

3. Избегай SELECT *

-- ❌ Плохо: загружаем все 50 колонок
SELECT * FROM large_table;

-- ✅ Хорошо: только нужные колонки
SELECT id, name, email FROM large_table;

Почему:

• Меньше данных из диска
• Меньше сетевого трафика
• Лучше использует индексы (Index-Only Scan)

4. Pushdown Predicates (фильтрование как можно раньше)

-- ❌ Плохо: фильтруем после JOIN (5млн + 2млн = 7млн обработано)
SELECT u.id, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total > 1000;

-- ✅ Хорошо: фильтруем ДО JOIN (5млн + 50k обработано)
SELECT u.id, o.total
FROM users u
JOIN (SELECT * FROM orders WHERE total > 1000) o ON u.id = o.user_id;

5. JOIN Optimization

Порядок JOIN'ов имеет значение

-- ❌ Плохо: начинаем с большой таблицы
SELECT *
FROM large_transactions t
JOIN small_users u ON t.user_id = u.id
JOIN small_categories c ON t.category_id = c.id;

-- ✅ Хорошо: начинаем с маленьких таблиц
SELECT *
FROM small_users u
JOIN small_categories c ON t.category_id = c.id
JOIN large_transactions t ON t.user_id = u.id;

Использование INNER vs LEFT

-- ❌ Плохо: LEFT JOIN когда нужен INNER
SELECT u.id FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NOT NULL;  -- по сути INNER JOIN

-- ✅ Хорошо: явно INNER
SELECT u.id FROM users u
INNER JOIN orders o ON u.id = o.user_id;

6. Агрегация и Window Functions

GROUP BY оптимизация

-- ❌ Плохо: GROUP BY на много колонок
SELECT user_id, date, hour, minute, COUNT(*)
FROM events
GROUP BY user_id, date, hour, minute;

-- ✅ Хорошо: GROUP только по нужным
SELECT user_id, DATE(timestamp), COUNT(*)
FROM events
GROUP BY user_id, DATE(timestamp);

Window Functions vs Joins

-- ❌ Плохо: самоджойн для рангирования
SELECT e1.id, e1.amount, 
  (SELECT COUNT(*) FROM events e2 
   WHERE e2.user_id = e1.user_id AND e2.amount > e1.amount) as rank
FROM events e1;

-- ✅ Хорошо: window function (одна таблица, один pass)
SELECT id, amount,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) as rank
FROM events;

7. Partition Pruning (для больших таблиц)

-- Таблица с партиционированием по дате
CREATE TABLE events (
    id BIGINT,
    user_id UUID,
    timestamp TIMESTAMPTZ,
    amount DECIMAL
) PARTITION BY RANGE (DATE(timestamp));

-- ✅ Хорошо: запрос только за один день
SELECT COUNT(*) FROM events 
WHERE DATE(timestamp) = '2024-03-21';  -- сканирует только одну партицию

-- ❌ Плохо: функция на колонке
SELECT COUNT(*) FROM events 
WHERE EXTRACT(MONTH FROM timestamp) = 3;  -- сканирует все партиции

8. Кэширование и статистика

-- Обновляем статистику (query planner использует её)
ANALYZE table_name;

-- Посмотреть статистику
SELECT relname, last_vacuum, last_analyze 
FROM pg_stat_user_tables
WHERE relname = 'orders';

9. Избегай дорогих операций

-- ❌ Плохо: LIKE с % в начале (не использует индекс)
SELECT * FROM users WHERE name LIKE '%john%';

-- ✅ Хорошо: LIKE с % в конце (использует индекс)
SELECT * FROM users WHERE name LIKE 'john%';

-- ✅ Лучше: точный поиск или full-text search
SELECT * FROM users WHERE name = 'john';
SELECT * FROM users WHERE to_tsvector(name) @@ plainto_tsquery('john');

Избегай функций на индексированных колонках

-- ❌ Плохо: функция не использует индекс
SELECT * FROM events WHERE YEAR(created_at) = 2024;

-- ✅ Хорошо: range выражение
SELECT * FROM events 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

10. CTEs и Subqueries

-- ✅ CTE для чистоты (sometimes optimized, sometimes not)
WITH active_users AS (
    SELECT id FROM users WHERE status = 'active'
),
user_orders AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
)
SELECT au.id, uo.order_count
FROM active_users au
LEFT JOIN user_orders uo ON au.id = uo.user_id;

-- ⚠️ Если CTE материализуется — может быть медленнее
-- Лучше раскрыть в один запрос
SELECT u.id, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id;

11. Батчинг для INSERT/UPDATE

# ❌ Плохо: 1000 отдельных запросов
for row in data:
    cursor.execute("INSERT INTO users VALUES (%s, %s)", row)

# ✅ Хорошо: один батч
data_list = [(row1), (row2), ...]
cursor.executemany("INSERT INTO users VALUES (%s, %s)", data_list)

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

[ ] EXPLAIN ANALYZE — понимаю план?
[ ] Индексы на WHERE/JOIN колонки?
[ ] SELECT конкретные колонки?
[ ] Predicate pushdown — фильтры как можно раньше?
[ ] JOIN в правильном порядке?
[ ] INNER vs LEFT/RIGHT правильно?
[ ] Статистика актуальна (ANALYZE)?
[ ] Никаких функций на индексированных колонках?
[ ] Partition pruning работает?
[ ] Результаты — нет ли неожиданных Seq Scans?

Инструменты профилирования

PostgreSQL:

• pg_stat_statements — какие запросы медленные
• EXPLAIN ANALYZE — план выполнения
• auto_explain — логирование медленных запросов

BigQuery:

• Query Execution Graph
• Slots и Cost Analysis
• Time Travel для debug

MySQL/Percona:

• EXPLAIN FORMAT=JSON
• pt-query-digest — анализ медленных логов

Итого

Оптимизация SQL = 80% правильное индексирование + 15% правильная архитектура query + 5% микрооптимизация. Всегда EXPLAIN ANALYZE перед и после!