Какие у тебя требования к work context?

Оптимизация медленного SQL-запроса

Когда SQL-запрос выполняется долго, нужен систематический подход к диагностике и оптимизации. Я описываю процесс, который использую на практике.

Шаг 1: Получить информацию о времени выполнения

-- Измерить время выполнения запроса
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
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC;

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

• Время планирования (planning time)
• Время выполнения (execution time)
• Количество просканированных строк
• Узкие места (Sequential Scan вместо Index Scan)

Шаг 2: Анализ плана выполнения

-- PostgreSQL: подробный план
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT *
FROM orders
WHERE status = 'pending'
  AND created_at > NOW() - INTERVAL '7 days';

Ищу следующие проблемы:

Sequential Scan (медленно)

Seq Scan on orders (cost=0.00..10000.00 rows=100000)
  Filter: (status = 'pending')

Важный показатель: cost=0.00..10000.00 — очень дорогой!

Index Scan (быстро)

Index Scan using orders_status_idx on orders
  Index Cond: (status = 'pending')
  Filter: (created_at > now() - ...)

Шаг 3: Проверить наличие индексов

-- Посмотреть индексы таблицы
SELECT
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'orders';

-- Проверить размер и использование индексов
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan as index_scans,
    idx_tup_read as tuples_read,
    idx_tup_fetch as tuples_fetched
FROM pg_stat_user_indexes
WHERE schemaname != 'pg_toast'
ORDER BY idx_scan DESC;

Шаг 4: Создание нужных индексов

-- ❌ Медленный запрос без индекса
SELECT * FROM orders WHERE status = 'pending';
-- Sequential Scan: сканирует все 1 млн строк

-- ✅ Добавить индекс
CREATE INDEX idx_orders_status ON orders(status);

-- ✅ Составной индекс (лучше для фильтрации по нескольким полям)
CREATE INDEX idx_orders_status_created
ON orders(status, created_at);

-- ✅ Частичный индекс (только для активных заказов)
CREATE INDEX idx_orders_pending
ON orders(created_at)
WHERE status = 'pending';

Типы индексов:

-- B-tree (по умолчанию, для большинства случаев)
CREATE INDEX idx_name ON table(column);

-- Hash (для точного совпадения)
CREATE INDEX idx_name ON table USING HASH(column);

-- GiST (для полнотекстового поиска, геометрии)
CREATE INDEX idx_name ON table USING GIST(column);

-- GIN (для JSON, массивов, полнотекстового поиска)
CREATE INDEX idx_name ON table USING GIN(column);

Шаг 5: Проанализировать структуру запроса

-- ❌ МЕДЛЕННО: JOIN без индекса на внешнем ключе
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id  -- Нет индекса на o.user_id!
GROUP BY u.id, u.name;

-- ✅ БЫСТРО: Добавить индекс
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- ❌ МЕДЛЕННО: Фильтр на вычисляемом поле
SELECT *
FROM users
WHERE YEAR(created_at) = 2025;  -- Функция предотвращает использование индекса!

-- ✅ БЫСТРО: Переписать условие
SELECT *
FROM users
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

Шаг 6: Оптимизация запроса

Проблема 1: N+1 Problem

# ❌ ПЛОХО: N+1 запросов
users = User.query.all()  # 1 запрос
for user in users:
    orders = user.orders  # N запросов (по одному для каждого пользователя)

# ✅ ХОРОШО: Eager loading
from sqlalchemy.orm import joinedload

users = User.query.options(joinedload(User.orders)).all()  # 1 запрос
for user in users:
    orders = user.orders  # Уже в памяти, БД не запрашиваем

Проблема 2: Лишние колонки

-- ❌ МЕДЛЕННО: SELECT *
SELECT *
FROM users
WHERE status = 'active';
-- Сканирует даже большие колонки (text, blob)

-- ✅ БЫСТРО: Только нужные колонки
SELECT id, name, email, created_at
FROM users
WHERE status = 'active';
-- Скан меньше

Проблема 3: LIMIT без ORDER BY

-- ❌ МЕДЛЕННО: Очень дорого вычислять LIMIT без ORDER BY
SELECT *
FROM orders
LIMIT 10;

-- ✅ БЫСТРО: Всегда используй ORDER BY с LIMIT
SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 10;

Проблема 4: Сложный WHERE с OR

-- ❌ МЕДЛЕННО: OR предотвращает индекс
SELECT *
FROM products
WHERE category = 'Electronics' OR category = 'Books';

-- ✅ БЫСТРО: Используй IN
SELECT *
FROM products
WHERE category IN ('Electronics', 'Books');

-- ✅ ДАЖЕ БЫСТРЕЕ: UNION
SELECT * FROM products WHERE category = 'Electronics'
UNION ALL
SELECT * FROM products WHERE category = 'Books';

Шаг 7: Оптимизация на уровне приложения

# ❌ МЕДЛЕННО: Большой запрос
orders = db.session.query(Order).filter(
    Order.created_at >= start_date
).all()  # Загружает 1 млн строк в память!

# ✅ БЫСТРО: Пакетная обработка
page_size = 1000
for offset in range(0, total_count, page_size):
    orders = db.session.query(Order).filter(
        Order.created_at >= start_date
    ).limit(page_size).offset(offset).all()
    # Обрабатываем по 1000 строк

# ✅ ДАЖЕ БЫСТРЕЕ: Используй курсор
with db.engine.connect() as conn:
    result = conn.execute(
        "SELECT * FROM orders WHERE created_at >= %s",
        (start_date,)
    )
    for row in result:
        # Обрабатываем строку по строке (streaming)
        process(row)

Шаг 8: Партиционирование таблицы

-- Для очень больших таблиц: партиционировать по дате
CREATE TABLE orders (
    id BIGINT,
    user_id INT,
    created_at TIMESTAMP,
    status VARCHAR(50)
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

-- Запрос сканирует только нужную партицию
SELECT * FROM orders WHERE created_at >= '2025-01-01';
-- Сканирует только p2025!

Шаг 9: Статистика таблицы

-- PostgreSQL: пересчитать статистику
ANALYZE orders;

-- MySQL: оптимизировать таблицу
OPTIMIZE TABLE orders;

-- SQL Server: обновить индексы
ALTER INDEX ALL ON orders REBUILD;

Шаг 10: Кэширование результатов

# Использовать Redis для кэширования
import redis
from functools import wraps

cache = redis.Redis()

def cached_query(timeout=300):
    def decorator(func):
        @wraps(func)
        def wrapper(*args, **kwargs):
            # Создать ключ кэша
            key = f"{func.__name__}:{args}:{kwargs}"
            
            # Проверить кэш
            result = cache.get(key)
            if result:
                return json.loads(result)
            
            # Выполнить запрос
            result = func(*args, **kwargs)
            
            # Сохранить в кэш
            cache.setex(key, timeout, json.dumps(result))
            return result
        return wrapper
    return decorator

@cached_query(timeout=3600)  # Кэш на 1 час
def get_top_products():
    return db.session.query(Product).order_by(
        Product.sales.desc()
    ).limit(100).all()

Пример: Полная оптимизация

-- БЫЛО: 30 секунд
SELECT u.id, u.name, COUNT(o.id) as order_count, SUM(o.amount) as total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE YEAR(o.created_at) = 2025
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 10
ORDER BY total DESC;

-- СТАЛО: 0.2 секунды
-- 1. Добавил индекс
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

-- 2. Переписал условие (без функции на дате)
SELECT u.id, u.name, COUNT(o.id) as order_count, SUM(o.amount) as total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2025-01-01' AND o.created_at < '2026-01-01'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 10
ORDER BY total DESC
LIMIT 1000;  -- Добавил LIMIT

-- 3. Убрал JOIN если возможно (денормализовать при необходимости)

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

1. ✅ Запустить EXPLAIN ANALYZE
2. ✅ Найти Sequential Scans (должны быть Index Scans)
3. ✅ Создать индексы на часто фильтруемые поля
4. ✅ Убрать функции из WHERE (вычисляют всегда)
5. ✅ Использовать INNER JOIN вместо LEFT когда возможно
6. ✅ Убрать SELECT * (выбирать только нужные колонки)
7. ✅ Добавить LIMIT для больших результатов
8. ✅ Пересчитать статистику таблицы
9. ✅ Использовать кэширование в приложении
10. ✅ При необходимости: партиционирование, денормализация, денормализация

Резюме

Порядок действий при медленном запросе:

1. EXPLAIN ANALYZE — найти проблему
2. Индексы — на часто фильтруемые поля
3. Переписать запрос — убрать функции, лишние JOIN'ы
4. Оптимизировать структуру — выбирать нужные колонки
5. Кэширование — если запрос часто повторяется
6. Денормализация — в крайних случаях