Изображение


Содержание

1. Введение: Почему медленные SQL-запросы убивают аналитику и бизнес
2. Архитектура СУБД и план выполнения запросов: EXPLAIN/EXPLAIN ANALYZE
3. Настройка среды анализа: клиенты, профилировщики и мониторинг
4. Индексы B-Tree: типы, выбор столбцов, покрывающие индексы
5. Продвинутые индексы: Hash, GiST, GIN, Brin, частичные и функциональные
6. Оптимизация JOIN-операций: Nested Loop, Hash Join, Merge Join
7. Работа с агрегациями и GROUP BY: оконные функции вместо подзапросов
8. Подзапросы, CTE (WITH) и временные таблицы: когда что использовать
9. Пагинация, сортировка и LIMIT/OFFSET: проблемы больших смещений
10. Блокировки, транзакции и уровни изоляции: влияние на производительность
11. Секционирование таблиц и материализованные представления
12. Кэширование, статистика СУБД и автовакуум/обслуживание


Введение: Почему медленные SQL-запросы убивают аналитику и бизнес

Аналитическая инфраструктура сталкивается с парадоксом роста: чем больше данных накапливает компания, тем медленнее принимаются решения. В 2026 году стандартный объём аналитической таблицы редко опускается ниже 500 млн строк. Маркетинговые дашборды, финансовые отчёты, ML-фичи и операционные метрики зависят от SQL-запросов, которые должны возвращать результат за секунды, а не часы. Когда запрос выполняется дольше 30 секунд, пользователь теряет контекст, дашборд тормозит, а фоновые джобы начинают конфликтовать за ресурсы ввода-вывода и оперативную память.

Проблема не в «тяжёлых базах данных». Проблема в архитектуре запросов. Оптимизатор СУБД (query planner) выбирает путь выполнения на основе статистики, доступных индексов и конфигурации сервера. Если статистика устарела, индекс отсутствует или написан некорректно, оптимизатор выбирает последовательное сканирование (Seq Scan/Full Table Scan). На таблице в 2 млрд строк это означает чтение сотен гигабайт с диска, генерацию временных данных в памяти, переполнение `work_mem` или `tmp_table_size`, и в итоге — свопинг на диск. Результат: загрузка CPU 90-100%, IOWAIT зашкаливает, остальные сессии блокируются или отваливаются по таймауту.

SQL-оптимизация запросов решает эту проблему системно. Это не набор случайных «хаков», а дисциплина, сочетающая понимание физической архитектуры СУБД, математику стоимости операций (cost-based optimization) и инженерную практику написания запросов. Правильно оптимизированный запрос использует индексное покрытие, выбирает оптимальный алгоритм соединения, минимизирует пересылку данных между узлами кластера и оставляет ресурсы на параллельную обработку. В 2026 году это критично: стоимость облачных вычислений привязана к потреблению CPU и IOPS. Медленный запрос = прямой финансовый убыток.

В этом руководстве мы разберём более 40 проверенных приёмов, которые работают в PostgreSQL 16+, MySQL 8.4+ и ClickHouse. Вы научитесь читать планы выполнения, выбирать индексы под конкретные паттерны выборки, переписывать CTE и подзапросы, избегать антипаттернов пагинации и настраивать окружение для стабильной работы с большими объёмами. Материал рассчитан на аналитиков данных, инженеров аналитики, разработчиков бэкенда и DBA, работающих с production-базами. Для выполнения инструкций потребуется доступ к тестовой или staging-БД с возможностью выполнения `EXPLAIN ANALYZE`, изменения индексов и просмотра метрик производительности.



Архитектура СУБД и план выполнения запросов: EXPLAIN/EXPLAIN ANALYZE

Прежде чем оптимизировать запрос, необходимо понять, как СУБД его исполняет. Оптимизатор запросов (query optimizer) строит план выполнения (execution plan) на основе эвристик и статистики. План описывает последовательность операций: сканирование, фильтрация, соединение, сортировка, агрегация. Каждый шаг имеет оценку стоимости (cost), времени (planned time) и фактического выполнения (actual time).

`EXPLAIN` показывает предполагаемый план без выполнения запроса. `EXPLAIN ANALYZE` выполняет запрос и подставляет реальные метрики. В 2026 году рекомендуется всегда использовать `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)` для получения полной картины.

sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)

SELECT 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.name

HAVING COUNT(o.id) > 5;


Ключевые узлы плана:
- Seq Scan / Table Scan: полное чтение таблицы. Допустимо только для малых таблиц (30% данных.
- Index Scan: чтение по индексу с последующим переходом в кучу (heap) за строками. Эффективно при селективности 10% от общего числа)
- Данные меняются редко (индексы замедляют `INSERT/UPDATE/DELETE`)

Порядок столбцов в составном индексе критичен. Оптимизатор использует только префикс индекса. `(status, created_at)` полезен для `WHERE status='active' AND created_at > ...`, но бесполезен для `WHERE created_at > ... AND status='active'`.

Покрывающие индексы (Covering Index / Index Only Scan)
Если запрос запрашивает только столбцы, входящие в индекс, СУБД может не обращаться к куче таблицы. В PostgreSQL:
sql
CREATE INDEX idx_users_status_email ON users(status) INCLUDE (email, name);

-- Запрос: SELECT email, name FROM users WHERE status = 'active';

-- План: Index Only Scan (без доступа к таблице)

Это сокращает I/O в 2-5 раз. В MySQL (InnoDB) покрывающий индекс работает аналогично, но требует аккуратного порядка: первичный ключ автоматически включается во все вторичные индексы.

Частотные проблемы
- Index Skip Scan: если первый столбец индекса имеет мало уникальных значений, оптимизатор может пропускать диапазоны. В PostgreSQL 15+ поддерживается, в MySQL — через оптимизатор `skip_scan`.
- Bloat: частые `UPDATE/DELETE` оставляют мёртвые версии строк. Индекс растёт, производительность падает. Решение: `REINDEX CONCURRENTLY` + регулярный `VACUUM`.

⚠️ Предупреждение: не создавайте индексы на столбцы с низкой селективностью (пол, статус «активен/неактивен» без фильтрации по дате). Оптимизатор проигнорирует их и выберет Seq Scan, а индексы будут замедлять запись.

💡 Совет: проверяйте использование индексов через `pg_stat_user_indexes` / `SHOW INDEX STATUS`. Индексы с `idx_scan = 0` за 30+ дней удаляйте. Они только занимают место и замедляют вставки.



Продвинутые индексы: Hash, GiST, GIN, Brin, частичные и функциональные

B-Tree покрывает 70% сценариев, но аналитика 2026 года требует специализированных структур.

Hash-индексы
Подходят только для точного равенства (`=`). Быстрее B-Tree, но не поддерживают диапазоны и сортировку. В PostgreSQL: `CREATE INDEX idx_hash ON table USING HASH(col)`. В MySQL InnoDB неявно использует адаптивный хеш-индекс для горячих страниц.

GiST / GIN (PostgreSQL)
- GiST: обобщённые деревья поиска. Для геометрии, диапазонов, полнотекстового поиска.
- GIN: инвертированный индекс. Идеален для JSONB, массивов, текстовых полей с множеством уникальных значений.
sql
CREATE INDEX idx_user_props_gin ON users USING GIN(properties jsonb_path_ops);

SELECT * FROM users WHERE properties @> '{"role": "admin"}';

GIN читает только релевантные блоки, пропуская нерелевантные данные.

BRIN (Block Range INdex)
Суммирует минимальные/максимальные значения для каждого блока таблицы. Занимает в 100-1000 раз меньше места, чем B-Tree. Эффективен для временных рядов, логов, больших таблиц с диапазоном `WHERE date BETWEEN ...`.

Частичные индексы
Индексируют только подмножество строк, удовлетворяющих условию.
sql
CREATE INDEX idx_orders_active_recent ON orders(created_at) 

WHERE status = 'pending' AND created_at > CURRENT_DATE - INTERVAL '30 days';

Сокращает размер индекса на 80-95%, ускоряет вставки, идеально для аналитики «горячих» данных.

Функциональные индексы
Индексируют результат выражения.
sql
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

Без индекса СУБД будет сканировать таблицу, применяя функцию к каждой строке.

⚠️ Внимание: GIN и GiST замедляют `UPDATE/DELETE`. Используйте только для read-heavy таблиц. BRIN требует `VACUUM` для обновления границ блоков.

💡 Совет: комбинировайте типы. Для JSONB-аналитики используйте `GIN` + `INCLUDE` для часто запрашиваемых ключей. Для временных рядов — `BRIN` по дате + частичный индекс по статусу.



Оптимизация JOIN-операций: Nested Loop, Hash Join, Merge Join

JOIN — самая ресурсоёмкая операция в аналитических запросах. Выбор алгоритма определяет разницу между 2 секундами и 20 минутами.

Nested Loop Join
Для каждой строки из левой таблицы ищет совпадение в правой по индексу.
- ✅ Идеально: маленькая левая таблица (1 млн строк, нужно индексировать промежуточный результат.
- ❌ Плохо: оверхед на создание/удаление, транзакционные ограничения, отсутствие статистики по умолчанию (`ANALYZE tmp_table` обязательно).

Правила выбора
1. Если результат CTE используется 1 раз → подзапрос или inline CTE.
2. Если CTE используется 2+ раз или сложный → `MATERIALIZED` или временная таблица.
3. Если промежуточный результат >500k строк → временная таблица с индексом.
4. Всегда запускайте `ANALYZE` на временных таблицах перед использованием в JOIN.

⚠️ Внимание: в MySQL временные таблицы могут создаваться в памяти, но при превышении `tmp_table_size` / `max_heap_table_size` свопятся на диск. Настройте параметры: `tmp_table_size=512M`.

💡 Совет: используйте `EXPLAIN` на CTE. Если видите `Subquery Scan` или `Materialize`, проверьте, не лучше ли перенести логику во временную таблицу с явным индексом.



Пагинация, сортировка и LIMIT/OFFSET: проблемы больших смещений

Классическая пагинация `LIMIT 10 OFFSET 1000000` — один из главных антипаттернов в аналитике. СУБД обязана отсортировать весь результат, пропустить первые 1 млн строк и вернуть 10. Это O(N log N) + O(N) пропусков. На таблицах в 50+ млн строк запрос выполняется 10-40 секунд.

Почему OFFSET медленный?
Даже с индексом `ORDER BY created_at`, СУБД читает индекс, но не может сразу перейти к смещению без сканирования предыдущих записей. Если данные не помещаются в индекс (нужен `Sort`), создаётся временный файл на диске.

Оптимизация: Keyset Pagination (Cursor-based)
Вместо смещения используйте последний идентификатор/значение предыдущей страницы:
sql
-- Страница 1

SELECT id, created_at, name FROM orders ORDER BY created_at DESC, id DESC LIMIT 20;

-- Страница 2 (последний id=500, date='2026-01-15 12:00:00')

SELECT id, created_at, name FROM orders 

WHERE (created_at, id) < ('2026-01-15 12:00:00', 500) 

ORDER BY created_at DESC, id DESC LIMIT 20;

Это позволяет использовать индекс напрямую. Сложность O(1) на страницу. Работает даже на 10 млрд строк.

Если OFFSET неизбежен (например, UI требует номера страницы)
1. Добавьте составной индекс `(sort_col, id)`.
2. Ограничьте глубину пагинации (макс. 100 страниц).
3. Используйте `/*+ INDEX(orders idx_sort) */` (MySQL hint) или настройте `enable_seqscan = off` (PostgreSQL, временно для анализа).
4. Кешируйте результаты первых страниц в Redis/Memcached.

Сортировка и `work_mem`
`ORDER BY` без индекса требует `Sort` step. Если результат > `work_mem`, СУБД использует дисковую сортировку (`external merge`). Увеличьте `work_mem` для аналитических сессий:
sql
SET LOCAL work_mem = '512MB'; -- действует только на текущую транзакцию

Но не злоупотребляйте: при 100 параллельных сессиях `512MB * 100 = 50GB` RAM.

⚠️ Предупреждение: `ORDER BY RAND()` в PostgreSQL/MySQL требует сортировки всех строк. На таблицах >1M строк это гарантированный таймаут. Используйте `TABLESAMPLE` или предвычисленные случайные ключи.

💡 Совет: для аналитических дашбордов избегайте пагинации. Агрегируйте данные на сервере, возвращайте итоговые метрики, а не сырые строки.



Блокировки, транзакции и уровни изоляции: влияние на производительность

Аналитические запросы часто выполняются параллельно с OLTP-операциями. Блокировки и уровни изоляции транзакций напрямую влияют на скорость чтения.

Уровни изоляции (SQL Standard)
1. Read Uncommitted: чтение «грязных» данных. Редко используется.
2. Read Committed (по умолчанию в PG/MySQL): каждая инструкция видит снимок на момент начала. Чистые данные, но возможны неконсистентные чтения внутри транзакции.
3. Repeatable Read: снимок на момент начала транзакции. Гарантирует консистентность, но увеличивает накладные расходы на версионирование.
4. Serializable: строгая сериализация. Максимальная безопасность, минимальная производительность.

Влияние на аналитику
- Длительные `SELECT` в `Repeatable Read` или `Serializable` удерживают старые версии строк (MVCC). Это мешает `VACUUM`/очистке, растёт bloat.
- `SELECT ... FOR UPDATE` блокирует строки на запись. Аналитические отчёты не должны блокировать таблицы. Используйте `FOR SHARE` или читайте из реплик.
- В PostgreSQL `ANALYZE` и `VACUUM` не блокируют чтение, но `VACUUM FULL` блокирует всё. Используйте `VACUUM` + `REINDEX CONCURRENTLY`.

Оптимизация для аналитики
1. Выполняйте тяжёлые запросы на read-only репликах.
2. Устанавливайте `SET LOCAL default_transacti;`
3. Избегайте `BEGIN;` без `COMMIT;`. Зависшие транзакции удерживают undo-логи и блокируют очистку.
4. Используйте `SET statement_timeout = 60000;` для отмены зависших сессий.

Блокировки и `pg_stat_activity` / `SHOW ENGINE INNODB STATUS`
Проверяйте ожидания:
sql
-- PostgreSQL

SELECT pid, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state = 'active' AND wait_event IS NOT NULL;

-- MySQL

SELECT * FROM information_schema.innodb_locks;

Если аналитический запрос ждёт `Lock: Relation`, проверьте, не блокирует ли его `INSERT/UPDATE` из другого сервиса.

⚠️ Внимание: не используйте `NOLOCK` (SQL Server) или хаки для чтения «грязных» данных в финансовых отчётах. Консистентность важнее скорости. Используйте реплики или snapshot isolation.

💡 Совет: настройте `autovacuum` (PG) или `innodb_adaptive_flushing` (MySQL) так, чтобы очистка происходила в фоне, а не во время аналитических окон.



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

Когда таблица превышает 50-100 млн строк, индексы перестают быть панацеей. Секционирование и материализованные представления (MV) меняют архитектуру доступа к данным.

Секционирование (Partitioning)
Разделяет таблицу на физические части по ключу (обычно дата или регион). Оптимизатор исключает нерелевантные секции (Partition Pruning).
sql
-- PostgreSQL декларативное секционирование

CREATE TABLE orders (id SERIAL, created_at DATE, amount DECIMAL) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2025_q1 PARTITION OF orders FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

CREATE TABLE orders_2025_q2 PARTITION OF orders FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

-- Запрос: SELECT SUM(amount) FROM orders WHERE created_at BETWEEN '2025-01-10' AND '2025-02-20';

-- Сканируется только orders_2025_q1. Остальные пропускаются.

В MySQL: `PARTITION BY RANGE (YEAR(created_at))`. В ClickHouse: `ENGINE = MergeTree() ORDER BY (date)`.

Правила секционирования
1. Ключ секционирования должен быть в `WHERE`. Иначе сканируются все секции (overhead > обычной таблицы).
2. Не создавайте >100 секций. Оптимизатор тратит время на планирование.
3. Индексы создаются на уровне секций или глобально (глобальные индексы медленнее, но удобнее).
4. Архивируйте старые секции: `DETACH PARTITION`, сжатие, перенос в холодное хранилище.

Материализованные представления (Materialized Views)
Хранят результат запроса физически. Обновляются явно.
sql
CREATE MATERIALIZED VIEW mv_daily_sales AS

SELECT date_trunc('day', created_at) as day, SUM(amount) as total

FROM orders GROUP BY 1;

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;

- ✅ Хорошо: агрегаты, дашборды, данные, меняющиеся раз в час/день.
- ❌ Плохо: часто меняющиеся данные (overhead на `REFRESH`), необходимость индексов на MV.

Инкрементальный refresh
В 2026 году многие СУБД поддерживают частичное обновление:
- PostgreSQL: `REFRESH CONCURRENTLY` требует `UNIQUE INDEX` на MV.
- ClickHouse: `OPTIMIZE TABLE ... FINALIZE` или `MATERIALIZED VIEW` с `TO` таблицей.
- MySQL: триггеры или cron-джобы с `INSERT ... ON DUPLICATE KEY UPDATE`.

⚠️ Внимание: `REFRESH` блокирует чтение (без `CONCURRENTLY`). Планируйте обновление на низкую нагрузку. Проверяйте размер MV: если он >50% исходной таблицы, возможно, проще использовать индексы или секционирование.

💡 Совет: комбинируйте. Секционируйте сырые данные, создавайте MV на агрегатах, индексируйте MV по фильтрам дашбордов. Запрос к MV выполняется за миллисекунды.



Кэширование, статистика СУБД и автовакуум/обслуживание

Производительность SQL зависит не только от запроса, но и от состояния СУБД. Статистика, кэш и фоновые процессы определяют, сможет ли оптимизатор выбрать правильный план.

Статистика (ANALYZE)
Оптимизатор использует гистограммы распределения значений, корреляции и плотность данных. Устаревшая статистика = неверный план = Seq Scan вместо Index Scan.
sql
ANALYZE users; -- обновляет статистику

ANALYZE VERBOSE users; -- показывает прогресс

-- PostgreSQL: autovacuum_analyze_threshold = 50 + 0.2 * reltuples

В MySQL: `ANALYZE TABLE orders;` или включите `innodb_stats_persistent=ON`.

Кэширование (Buffer Pool / Shared Buffers)
- PostgreSQL: `shared_buffers` (обычно 25% RAM), `effective_cache_size` (75% RAM).
- MySQL: `innodb_buffer_pool_size` (50-70% RAM).
Если `shared hit` <> 10%` от `reltuples`, запускайте `VACUUM ANALYZE` вручную или корректируйте настройки автовакуума.