Какие знаешь способы анализа запросов в SQL?

Способы анализа запросов в SQL

Анализ SQL запросов критичен для оптимизации производительности приложения. Медленные запросы часто становятся узким местом приложения, особенно при растущих объёмах данных.

1. EXPLAIN и EXPLAIN ANALYZE

Маг инструмент для анализа плана выполнения запроса. Показывает, как база данных планирует выполнять запрос.

EXPLAIN (планирование без выполнения)

EXPLAIN
SELECT * FROM users WHERE email = user@example.com;

-- Результат:
-- Seq Scan on users  (cost=0.00..35.50 rows=1 width=200)
--   Filter: (email = user@example.com)

Читаем слева направо, снизу вверх:

• Seq Scan — полное сканирование таблицы (медленно)
• cost=0.00..35.50 — начальная стоимость..максимальная стоимость (условные единицы)
• rows=1 — ожидаемое количество строк
• width=200 — средняя ширина строки в байтах

EXPLAIN ANALYZE (выполнение запроса с анализом)

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = user@example.com;

-- Результат:
-- Seq Scan on users  (cost=0.00..35.50 rows=1 width=200)
-- (actual time=0.052..0.054 rows=1 loops=1)
--   Filter: (email = user@example.com)
--   Rows Removed by Filter: 999
-- Planning Time: 0.087 ms
-- Execution Time: 0.102 ms

Добавляется actual time — реальное время выполнения.

Интерпретация

• Если rows(estimated) >> rows(actual) — плохая статистика, нужна ANALYZE таблицы
• Если actual time > стоимость запроса, запрос медленный
• Seq Scan на большой таблице указывает на отсутствие индекса

2. Индексы

Индексы ускоряют поиск, но замедляют INSERT/UPDATE/DELETE.

Проверка использования индекса

-- Создаём индекс
CREATE INDEX idx_users_email ON users(email);

-- Проверяем план
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = user@example.com;

-- Результат:
-- Index Scan using idx_users_email on users
-- (cost=0.29..8.31 rows=1 width=200)
-- (actual time=0.034..0.036 rows=1 loops=1)

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

-- B-Tree индекс (по умолчанию, самый универсальный)
CREATE INDEX idx_email ON users(email);

-- Индекс на нескольких столбцах (составной)
CREATE INDEX idx_user_status ON users(status, created_at);

-- Частичный индекс (только для активных пользователей)
CREATE INDEX idx_active_users ON users(id)
WHERE status = active;

-- EXPLAIN покажет использование индекса
EXPLAIN ANALYZE
SELECT * FROM users WHERE status = active;

3. ANALYZE команда

Обновляет статистику таблицы для оптимизатора:

-- Обновить статистику для таблицы users
ANALYZE users;

-- Обновить статистику для конкретного столбца
ANALYZE users(email);

-- После ANALYZE оптимизатор лучше выбирает план

Когда нужно запускать ANALYZE:

• После massive INSERT/DELETE
• После создания индекса
• Периодически (например, ночью через cron)

4. Slow Query Log

Пересчёт всех запросов, выполняемых дольше threshold.

PostgreSQL (log_min_duration_statement)

-- В postgresql.conf или через ALTER SYSTEM
ALTER SYSTEM SET log_min_duration_statement = 1000; -- 1 сек
SELECT pg_reload_conf();  -- Перезагрузить конфиг

-- Медленные запросы появятся в логе
-- tail -f /var/log/postgresql/postgresql.log | grep "duration"

MySQL (slow_query_log)

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;  -- 2 сек
SET GLOBAL log_queries_not_using_indexes = ON;

5. Statistics и хистограммы

База собирает статистику о распределении данных:

-- PostgreSQL: просмотреть статистику
SELECT * FROM pg_stats
WHERE tablename = users
AND attname = email;

-- Результат показывает:
-- - null_frac: доля NULL значений
-- - avg_width: средняя ширина
-- - n_distinct: количество уникальных значений
-- - correlation: корреляция с физическим порядком

6. Query Execution Time

В Python с SQLAlchemy

import time
from sqlalchemy import text, event
from sqlalchemy.engine import Engine

@event.listens_for(Engine, "before_cursor_execute")
def receive_before_cursor_execute(conn, cursor, statement, params, context, executemany):
    conn.info.setdefault(query_start_time, []).append(time.time())

@event.listens_for(Engine, "after_cursor_execute")
def receive_after_cursor_execute(conn, cursor, statement, params, context, executemany):
    total = time.time() - conn.info[query_start_time].pop(-1)
    if total > 0.5:  # Логируем запросы дольше 500ms
        print(f"Query took {total:.3f}s")
        print(statement)

В Django

from django.db import connection
from django.test.utils import CaptureQueriesContext

with CaptureQueriesContext(connection) as ctx:
    users = User.objects.filter(status=active)

for query in ctx.captured_queries:
    print(f"Time: {query[time]}s")
    print(f"SQL: {query[sql]}")

7. N+1 Problem Detection

Верьте частая проблема: основной запрос + N запросов для каждой строки.

# Плохо — N+1 queries
users = User.objects.all()  # 1 запрос
for user in users:
    print(user.profile.bio)  # N запросов (по одному для каждого user)

# Хорошо — select_related / prefetch_related
users = User.objects.select_related(profile).all()  # 1 JOIN запрос
for user in users:
    print(user.profile.bio)  # Нет дополнительных запросов

В Django Debug Toolbar

# settings.py
if DEBUG:
    INSTALLED_APPS += [debug_toolbar]
    MIDDLEWARE += [debug_toolbar.middleware.DebugToolbarMiddleware]

# Панель покажет все запросы и время выполнения

8. JOIN анализ

-- Плохой JOIN (Cartesian product)
SELECT u.*, p.* FROM users u, posts p
WHERE u.id = p.user_id;  -- Условие в WHERE

-- Хороший JOIN (явный)
SELECT u.*, p.* FROM users u
INNER JOIN posts p ON u.id = p.user_id;

-- EXPLAIN покажет разницу
EXPLAIN ANALYZE
SELECT u.*, p.* FROM users u
INNER JOIN posts p ON u.id = p.user_id;

-- Ищем Hash Join (плохо для больших таблиц)
-- Ищем Nested Loop (плохо, если нет индекса)
-- Хорошо видеть Index Scan

9. Query Profiling и benchmarking

import time
from contextlib import contextmanager

@contextmanager
def timer(name):
    start = time.perf_counter()
    try:
        yield
    finally:
        elapsed = time.perf_counter() - start
        print(f"{name}: {elapsed:.3f}s")

# Использование
with timer("Query 1"):
    users = User.objects.all()

with timer("Query 2"):
    active_users = User.objects.filter(is_active=True)

10. pg_stat_statements (PostgreSQL)

Расширение, показывающее статистику по всем запросам:

-- Включить расширение
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Просмотреть топ медленных запросов
SELECT query, calls, mean_time, max_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Результат:
-- query                                          calls  mean_time  max_time
-- SELECT * FROM users WHERE email = $1          1000   25.5       150.2
-- SELECT * FROM posts WHERE user_id = $1        5000   2.3        10.5

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

1. Найти медленные запросы — slow query log, pg_stat_statements
2. Запустить EXPLAIN ANALYZE — понять план выполнения
3. Добавить индексы — если Seq Scan на большой таблице
4. Оптимизировать JOIN — переписать запрос
5. Использовать кэширование — Redis для часто повторяющихся запросов
6. Денормализовать данные — если запросы слишком сложные
7. Запустить ANALYZE — обновить статистику
8. Повторить EXPLAIN ANALYZE — проверить улучшение

Знание способов анализа SQL — это ключ к написанию быстрых приложений на Python.