SQL для разработчиков: от основ до оптимизации запросов

SQL: язык, которому 50 лет, но он не устарел

SQL изобрели в IBM в 1974 году. С тех пор появились NoSQL, NewSQL, GraphQL, временны́е базы данных, документные хранилища. Но SQL не умер — он стал стандартом для большинства задач работы с данными.

Реляционные СУБД (PostgreSQL, MySQL, SQLite, MS SQL, Oracle) хранят данные в большинстве корпоративных систем мира. И даже "NoSQL" системы (ClickHouse, DuckDB, BigQuery) используют SQL-диалект.

Знание SQL — это инвестиция с гарантированной отдачей для любого разработчика.

Архитектура запроса: как PostgreSQL исполняет SQL

Понимание этого даёт инсайт, почему одни запросы быстрые, а другие — нет:

Текст запроса
    ↓
[Parser] — проверка синтаксиса
    ↓
[Rewriter] — разворачивание Views, правила
    ↓
[Planner/Optimizer] — КЛЮЧЕВОЙ ЭТАП!
  - Оценка стоимости разных планов
  - Выбор порядка JOIN-ов
  - Выбор алгоритма соединения (Hash Join, Nested Loop, Merge Join)
  - Решение: использовать индекс или seq scan
    ↓
[Executor] — выполнение выбранного плана
    ↓
Результат

Планировщик работает на основе статистики (pg_statistics). Устаревшая статистика → неоптимальный план → медленный запрос. Поэтому важен ANALYZE или автовакуум.

EXPLAIN ANALYZE: видим что происходит

-- Всегда используйте ANALYZE для реального времени (но он выполняет запрос!)
-- Для SELECT это безопасно. Для DML используйте ROLLBACK:
-- BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) as order_count, SUM(o.total) as total_sum
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
  AND o.status = 'completed'
GROUP BY u.id, u.name
ORDER BY total_sum DESC
LIMIT 20;

-- Типичный вывод и как его читать:
/*
Limit  (cost=1250.45..1250.50 rows=20 width=48) (actual time=45.231..45.234 rows=20 loops=1)
  ->  Sort  (cost=1250.45..1253.95 rows=1400 width=48) (actual time=45.228..45.231 rows=20 loops=1)
        Sort Key: (sum(o.total)) DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  HashAggregate  (cost=1190.23..1204.73 rows=1400 width=48) (actual time=44.123..45.012 rows=1823 loops=1)
              Group Key: u.id, u.name
              Batches: 1  Memory Usage: 657kB
              ->  Hash Join  (cost=485.30..1148.73 rows=8300 width=24) (actual time=2.341..38.201 rows=9843 loops=1)
                    Hash Cond: (o.user_id = u.id)
                    Buffers: shared hit=342 read=891   ← ВАЖНО! 891 блоков с диска!
                    ->  Seq Scan on orders o  (cost=0.00..456.23 rows=12800 width=16)
                                              ↑ SEQ SCAN на большой таблице = тревожный сигнал!
                          Filter: ((status)::text = 'completed'::text)
                          Rows Removed by Filter: 23456
                    ->  Hash  (cost=423.55..423.55 rows=4940 width=16) (actual time=2.103..2.103 rows=4892 loops=1)
                          ->  Index Scan using idx_users_created on users u
                                Index Cond: (created_at > '2024-01-01'::date)

Planning Time: 0.523 ms
Execution Time: 45.789 ms   ← Реальное время выполнения
*/

Что искать в EXPLAIN:

Индексы: типы и когда применять

B-Tree (по умолчанию)

Подходит для: =, <, >, BETWEEN, LIKE 'prefix%', ORDER BY, диапазоны дат.

-- Обычный индекс
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Составной индекс (порядок важен!)
-- Покрывает: WHERE user_id = X AND status = Y
-- Покрывает: WHERE user_id = X (только первая колонка)
-- НЕ покрывает: WHERE status = Y (без user_id)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Частичный индекс (только для подмножества строк)
-- Гораздо меньше, работает быстрее для частых запросов с фильтром
CREATE INDEX idx_orders_active ON orders(created_at)
WHERE status IN ('pending', 'processing');

-- Индекс с включёнными колонками (covering index)
-- SELECT user_id, total FROM orders WHERE status = 'completed'
-- будет выполнен только из индекса, без обращения к таблице!
CREATE INDEX idx_orders_status_covering ON orders(status)
INCLUDE (user_id, total);

Hash индекс

Только для = (равенство). Быстрее B-Tree для равенства, но нет диапазонов:

CREATE INDEX idx_sessions_token ON sessions USING HASH (token);
-- Отлично для: WHERE token = 'abc123' (авторизация)

GIN (Generalized Inverted Index)

Для массивов, JSONB, полнотекстового поиска, pg_trgm:

-- Полнотекстовый поиск
CREATE INDEX idx_articles_fts ON articles
USING GIN (to_tsvector('russian', title || ' ' || body));

-- Поиск по JSONB
CREATE INDEX idx_devices_meta ON devices USING GIN (metadata jsonb_path_ops);
-- Запрос: WHERE metadata @> '{"type": "sensor"}'

-- pg_trgm для LIKE '%substring%' (иначе seq scan!)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);
-- Запрос: WHERE name ILIKE '%насос%'

BRIN (Block Range INdex)

Для очень больших таблиц с монотонно возрастающими данными (временны́е метки):

-- Таблица телеметрии: 10 миллиардов строк
-- B-Tree индекс займёт 200 ГБ
-- BRIN займёт 1 МБ! (хранит мин/макс по блокам)
CREATE INDEX idx_telemetry_time_brin ON telemetry USING BRIN (measured_at)
WITH (pages_per_range = 128);

-- Работает только если данные ФИЗИЧЕСКИ упорядочены по времени
-- (INSERT в хронологическом порядке)

Оконные функции: SQL нового уровня

Оконные функции — одна из самых мощных возможностей SQL, которую многие не знают.

-- Задача: для каждого заказа показать его номер в последовательности
-- заказов этого клиента и общее количество заказов клиента

SELECT
    id,
    user_id,
    created_at,
    total,
    
    -- Номер строки в партиции (по каждому user_id отдельно)
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_number,
    
    -- Ранг (при одинаковых значениях — одинаковый ранг, следующий пропускается)
    RANK() OVER (PARTITION BY user_id ORDER BY total DESC) AS rank_by_total,
    
    -- Dense Rank (без пропусков)
    DENSE_RANK() OVER (PARTITION BY user_id ORDER BY total DESC) AS dense_rank,
    
    -- Количество строк в партиции
    COUNT(*) OVER (PARTITION BY user_id) AS total_orders,
    
    -- Нарастающая сумма
    SUM(total) OVER (PARTITION BY user_id ORDER BY created_at
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
    
    -- Скользящее среднее (последние 3 заказа)
    AVG(total) OVER (PARTITION BY user_id ORDER BY created_at
                     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3,
    
    -- Предыдущее и следующее значение
    LAG(total, 1)  OVER (PARTITION BY user_id ORDER BY created_at) AS prev_order_total,
    LEAD(total, 1) OVER (PARTITION BY user_id ORDER BY created_at) AS next_order_total,
    
    -- Процент от общей суммы клиента
    ROUND(total / SUM(total) OVER (PARTITION BY user_id) * 100, 2) AS pct_of_customer_total,
    
    -- Процентиль
    PERCENT_RANK() OVER (PARTITION BY user_id ORDER BY total) AS percentile

FROM orders
ORDER BY user_id, created_at;

Практический пример: анализ телеметрии

-- Обнаружение аномалий: значения > avg + 2*stddev
WITH stats AS (
    SELECT
        device_id,
        AVG(temperature)    AS avg_temp,
        STDDEV(temperature) AS std_temp
    FROM telemetry
    WHERE measured_at > NOW() - INTERVAL '7 days'
    GROUP BY device_id
),
windowed AS (
    SELECT
        t.*,
        s.avg_temp,
        s.std_temp,
        -- Z-score
        (t.temperature - s.avg_temp) / NULLIF(s.std_temp, 0) AS z_score,
        -- Скользящее среднее за 5 измерений
        AVG(t.temperature) OVER (
            PARTITION BY t.device_id
            ORDER BY t.measured_at
            ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
        ) AS moving_avg_5,
        -- Предыдущее значение (для расчёта скорости изменения)
        LAG(t.temperature) OVER (
            PARTITION BY t.device_id ORDER BY t.measured_at
        ) AS prev_temp,
        LAG(t.measured_at) OVER (
            PARTITION BY t.device_id ORDER BY t.measured_at
        ) AS prev_time
    FROM telemetry t
    JOIN stats s ON s.device_id = t.device_id
    WHERE t.measured_at > NOW() - INTERVAL '24 hours'
)
SELECT
    device_id,
    measured_at,
    temperature,
    ROUND(z_score::numeric, 2)      AS z_score,
    ROUND(moving_avg_5::numeric, 2) AS moving_avg,
    -- Скорость изменения (°C/мин)
    ROUND(
        (temperature - prev_temp) /
        NULLIF(EXTRACT(EPOCH FROM (measured_at - prev_time)) / 60.0, 0)
    , 2) AS rate_per_min,
    
    CASE
        WHEN ABS(z_score) > 3 THEN 'КРИТИЧЕСКАЯ АНОМАЛИЯ'
        WHEN ABS(z_score) > 2 THEN 'Аномалия'
        ELSE 'Норма'
    END AS status

FROM windowed
WHERE ABS(z_score) > 2
ORDER BY ABS(z_score) DESC;

CTE: читаемые и повторно используемые запросы

-- CTE (Common Table Expression) — именованные подзапросы
-- Делают сложные запросы читаемыми

WITH
-- Шаг 1: активные устройства за последние 24 часа
active_devices AS (
    SELECT DISTINCT device_id
    FROM telemetry
    WHERE measured_at > NOW() - INTERVAL '24 hours'
),

-- Шаг 2: статистика по каждому устройству
device_stats AS (
    SELECT
        t.device_id,
        COUNT(*)                          AS reading_count,
        AVG(t.temperature)                AS avg_temp,
        MAX(t.temperature)                AS max_temp,
        MIN(t.temperature)                AS min_temp,
        SUM(CASE WHEN t.fault THEN 1 ELSE 0 END) AS fault_count
    FROM telemetry t
    INNER JOIN active_devices ad ON ad.device_id = t.device_id
    WHERE t.measured_at > NOW() - INTERVAL '24 hours'
    GROUP BY t.device_id
),

-- Шаг 3: ранжирование по количеству аварий
ranked AS (
    SELECT
        *,
        RANK() OVER (ORDER BY fault_count DESC) AS fault_rank
    FROM device_stats
)

-- Финальный запрос
SELECT
    r.device_id,
    d.name,
    d.location,
    r.reading_count,
    ROUND(r.avg_temp::numeric, 2) AS avg_temp,
    r.max_temp,
    r.fault_count,
    r.fault_rank,
    CASE WHEN r.fault_count > 10 THEN '

🔴

Требует внимания' ELSE '🟢 OK' END AS status FROM ranked r JOIN devices d ON d.id = r.device_id ORDER BY r.fault_rank;

Рекурсивные CTE: для деревьев и графов

-- Дерево категорий оборудования
WITH RECURSIVE category_tree AS (
    -- Базовый случай: корневые категории
    SELECT
        id,
        name,
        parent_id,
        1           AS depth,
        name::text  AS path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Рекурсивный шаг: дочерние категории
    SELECT
        c.id,
        c.name,
        c.parent_id,
        ct.depth + 1,
        ct.path || ' > ' || c.name
    FROM categories c
    INNER JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT
    depth,
    REPEAT('  ', depth - 1) || name AS name_indented,
    path
FROM category_tree
ORDER BY path;

-- Результат:
-- Оборудование
--   Насосное оборудование
--     Центробежные насосы
--     Шестерённые насосы
--   Нагреватели
--     Ленточные

Транзакции и ACID

-- Пример транзакции: перевод средств
-- ACID: Atomicity, Consistency, Isolation, Durability

BEGIN;

-- Несколько операций — или все, или ничего!
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
INSERT INTO transactions (from_id, to_id, amount, created_at)
    VALUES (1, 2, 1000, NOW());

-- Проверка (если не OK — откатываем всё)
DO $$
DECLARE
    balance DECIMAL;
BEGIN
    SELECT balance INTO balance FROM accounts WHERE id = 1;
    IF balance < 0 THEN
        RAISE EXCEPTION 'Недостаточно средств!';
    END IF;
END;
$$;

COMMIT;  -- Всё OK, фиксируем
-- или ROLLBACK; -- Если что-то пошло не так

-- Уровни изоляции транзакций:
-- READ UNCOMMITTED: видит незафиксированные данные (грязное чтение)
-- READ COMMITTED: видит только зафиксированные (по умолчанию в PG)
-- REPEATABLE READ: повторное чтение даёт тот же результат
-- SERIALIZABLE: полная изоляция, как последовательное выполнение

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Партиционирование: для больших таблиц

-- Партиционирование таблицы телеметрии по месяцам

CREATE TABLE telemetry (
    id          BIGSERIAL,
    device_id   INT NOT NULL,
    measured_at TIMESTAMPTZ NOT NULL,
    temperature FLOAT,
    pressure    FLOAT,
    current     FLOAT
) PARTITION BY RANGE (measured_at);

-- Создаём партиции по месяцам
CREATE TABLE telemetry_2024_01 PARTITION OF telemetry
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE telemetry_2024_02 PARTITION OF telemetry
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- ... и так далее

-- Индексы создаются на каждой партиции отдельно
CREATE INDEX ON telemetry_2024_01 (device_id, measured_at);
CREATE INDEX ON telemetry_2024_02 (device_id, measured_at);

-- Автоматическое создание партиций (pg_partman)
-- SELECT partman.create_parent('public.telemetry', 'measured_at',
--        'native', 'monthly');

-- Преимущества:
-- 1. Partition pruning: запрос за январь сканирует только telemetry_2024_01
-- 2. Быстрое удаление старых данных: DROP TABLE telemetry_2023_01
-- 3. Параллельное сканирование разных партиций

N+1 проблема: самая частая ошибка

-- N+1: вместо одного запроса делаем N+1
-- Типичная ошибка при работе с ORM

-- 

❌

ПЛОХО (в Python/PHP коде): -- users = db.execute("SELECT * FROM users LIMIT 100") -- for user in users: -- orders = db.execute("SELECT * FROM orders WHERE user_id = ?", user.id) -- Итого: 1 + 100 = 101 запрос! -- ✅ ХОРОШО: один JOIN SELECT u.id, u.name, u.email, COUNT(o.id) AS order_count, SUM(o.total) AS total_spent FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id, u.name, u.email LIMIT 100; -- ✅ ХОРОШО: два запроса с IN (для сложных случаев) -- users = db.execute("SELECT * FROM users LIMIT 100") -- user_ids = [u.id for u in users] -- orders = db.execute("SELECT * FROM orders WHERE user_id = ANY(?)", user_ids) -- Итого: 2 запроса!

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

-- 1. UPSERT (INSERT или UPDATE если существует)
INSERT INTO device_status (device_id, status, updated_at)
VALUES (1, 'online', NOW())
ON CONFLICT (device_id) DO UPDATE SET
    status     = EXCLUDED.status,
    updated_at = EXCLUDED.updated_at;

-- 2. Batch INSERT (вместо N отдельных INSERT)
INSERT INTO telemetry (device_id, measured_at, temperature)
VALUES
    (1, '2024-01-01 10:00', 25.3),
    (1, '2024-01-01 10:01', 25.4),
    (2, '2024-01-01 10:00', 22.1)
-- До 1000 строк в одном запросе — намного быстрее!

-- 3. COPY для массовой загрузки (самый быстрый способ)
-- \COPY telemetry FROM '/data/telemetry.csv' CSV HEADER

-- 4. Materialized View для сложных агрегатов
CREATE MATERIALIZED VIEW daily_device_summary AS
SELECT
    device_id,
    DATE(measured_at)  AS day,
    AVG(temperature)   AS avg_temp,
    MAX(temperature)   AS max_temp,
    COUNT(*)           AS readings
FROM telemetry
GROUP BY device_id, DATE(measured_at);

CREATE UNIQUE INDEX ON daily_device_summary(device_id, day);

-- Обновление (можно конкурентно, без блокировки SELECT)
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_device_summary;

-- 5. EXPLAIN сначала, оптимизировать потом!
-- Никогда не оптимизируйте наугад. Всегда смотрите план.

PostgreSQL: важные настройки performance

-- Ключевые параметры postgresql.conf для production:

-- Память (зависит от RAM сервера):
-- shared_buffers = 25% RAM (напр. 4GB для 16GB)
-- effective_cache_size = 75% RAM
-- work_mem = 64MB (для сортировок и hash join)
-- maintenance_work_mem = 1GB (для VACUUM, CREATE INDEX)

-- Диск (для SSD):
-- random_page_cost = 1.1 (вместо 4.0)
-- effective_io_concurrency = 200

-- Параллелизм:
-- max_parallel_workers_per_gather = 4
-- max_worker_processes = 8

-- Checkpoint:
-- checkpoint_completion_target = 0.9
-- wal_buffers = 64MB

-- Проверка текущих настроек:
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'max_connections');

-- Статистика медленных запросов (pg_stat_statements):
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
    substring(query, 1, 80) AS query_short,
    calls,
    ROUND(total_exec_time::numeric / 1000, 2) AS total_sec,
    ROUND(mean_exec_time::numeric, 2) AS mean_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Заключение

SQL — не просто "язык запросов", это язык управления данными. Понимание планировщика, правильное использование индексов, оконные функции и CTE — это инструменты, которые превращают "работает" в "работает быстро и масштабируется".

Практический совет: запустите pg_stat_statements на вашем production-сервере прямо сейчас. Посмотрите топ-20 медленных запросов. С вероятностью 80% — там найдётся очевидная оптимизация, которая ускорит приложение в разы.

Инвестируйте в "Use The Index, Luke" (use-the-index-luke.com) — лучшее бесплатное руководство по индексам SQL. И всегда: EXPLAIN ANALYZE перед любой "оптимизацией".