EXPLAIN с опцией ANALYZE выполняет запрос и дополняет план фактическим временем работы и фактическим количеством строк по каждому узлу. Отсюда следствие: на UPDATE, DELETE и INSERT такую команду на боевой базе запускают только внутри транзакции с откатом.
Как ускорить медленные SQL-запросы: диагностика, индексы и оптимизация

Медленные SQL-запросы — частая причина проблем с производительностью приложения. Страница грузится пять секунд вместо 300 мс, API отдаёт таймаут, под нагрузкой падает сервер: обычно за этим стоит запрос без индекса или неэффективный JOIN. Большинство таких случаев закрывается стандартными инструментами: EXPLAIN ANALYZE, правильными индексами и несколькими принципами написания запросов. Ниже — рабочий порядок действий для PostgreSQL и MySQL на продакшне.
Прежде чем что-то оптимизировать, нужно знать, что именно тормозит. Ощущение «тормозит страница заказов» для работы не годится: нужен список запросов, отсортированный по времени.
PostgreSQL: pg_stat_statements
Расширение pg_stat_statements отслеживает статистику планирования и выполнения всех запросов на сервере: число вызовов, суммарное и среднее время выполнения в миллисекундах.
-- Включить расширение (один раз) CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- Топ-10 запросов по среднему времени выполнения SELECT query, calls, round(total_exec_time::numeric, 2) AS total_ms, round(mean_exec_time::numeric, 2) AS avg_ms, round(stddev_exec_time::numeric, 2) AS stddev_ms, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;
Сортировка по mean_exec_time выводит самые тяжёлые отдельные запросы, но приоритет для оптимизации обычно другой: произведение calls * mean_exec_time, то есть суммарное время. Запрос на 40 мс, который выполняется 200 раз при каждом открытии страницы, съедает больше ресурсов, чем отчёт на 3 секунды раз в час.
MySQL: slow query log
В MySQL медленный лог запросов управляется системными переменными slow_query_log и long_query_time, порог по умолчанию — 10 секунд; для разбора лога есть утилита mysqldumpslow.
-- Включить на лету (или прописать в my.cnf) SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; -- логировать запросы дольше 1 секунды SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
# Разбор лога: топ-10 по суммарному времени mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
Порог в 10 секунд по умолчанию пропустит почти всё, что на самом деле мешает пользователю. Начните с одной секунды, разберите найденное, затем снижайте порог дальше.
PostgreSQL: auto_explain
Модуль auto_explain автоматически записывает планы выполнения медленных запросов в лог сервера, без ручного запуска EXPLAIN для каждого из них; порог задаётся параметром auto_explain.log_min_duration.
# postgresql.conf shared_preload_libraries = 'auto_explain' auto_explain.log_min_duration = 1000 # мс auto_explain.log_analyze = true
На продакшне это экономит часы: редкие запросы, которые тормозят раз в сутки на конкретных данных, воспроизвести вручную почти невозможно, а в логе их план уже лежит. У режима есть цена: log_analyze добавляет накладные расходы на измерение времени узлов, поэтому порог стоит держать высоким и не логировать всё подряд.
EXPLAIN (ANALYZE, BUFFERS) SELECT u.name, COUNT(o.id) AS order_count FROM users u JOIN orders o ON o.user_id = u.id WHERE u.created_at > '2024-01-01' GROUP BY u.name ORDER BY order_count DESC;
Опция BUFFERS показывает, сколько страниц запрос прочитал из общего кэша и сколько пришлось поднять с диска. Запрос с одинаковым планом может работать 20 мс на прогретом кэше и 2 секунды на холодном, и без счётчиков буферов эта разница выглядит мистикой.
Ключевые узлы плана
| Узел | Что означает | Когда это проблема |
| Seq Scan | Полное сканирование таблицы | Большая таблица без ограничивающих условий |
| Index Scan | Поиск по индексу с чтением строк из таблицы | Норма |
| Index Only Scan | Чтение только из индекса, без обращения к таблице | Лучший вариант для покрывающих запросов |
| Bitmap Heap Scan | Два прохода: сбор ссылок по индексу, затем чтение таблицы | Норма, когда строк по условию много |
| Nested Loop | Вложенный цикл по соединению | Опасно, если внешняя выборка большая |
| Hash Join | Соединение через хэш-таблицу | Норма для больших наборов |
| Sort | Сортировка | Sort Method: external merge означает сортировку с временными файлами на диске |
На что смотреть в первую очередь
- Расхождение rows и actual rows. Первое — оценка планировщика, второе — факт. Разница в десятки раз означает, что планировщик строит план по неверным представлениям о данных: пора обновлять статистику.
- Seq Scan на большой таблице. Первый кандидат на индекс, особенно если фильтр отбирает небольшую долю строк.
- cost. Это не миллисекунды, а относительные единицы планировщика. Сравнивать их имеет смысл между узлами одного плана или между вариантами одного запроса, но не с абсолютным временем.
- Самый дорогой узел по actual time. План читается снизу вверх и изнутри наружу; время родительского узла включает время дочерних.
Индексы — главный рычаг в оптимизации базы данных. И главный источник проблем, если ставить их наугад.
Типы индексов PostgreSQL
-- B-tree (по умолчанию): =, <, >, BETWEEN, ORDER BY CREATE INDEX idx_orders_user_id ON orders(user_id); -- Составной: фильтрация сразу по нескольким колонкам CREATE INDEX idx_orders_user_status ON orders(user_id, status); -- Частичный: индексируем только нужное подмножество строк CREATE INDEX idx_orders_pending ON orders(created_at) WHERE status = 'pending'; -- Индекс на выражение CREATE INDEX idx_users_lower_email ON users(lower(email)); -- GIN: массивы, JSONB, полнотекстовый поиск CREATE INDEX idx_products_tags ON products USING GIN(tags);
Индекс GIN (Generalized Inverted Index, обобщённый инвертированный индекс) предназначен для составных значений: массивов, JSONB и данных полнотекстового поиска в типе tsvector. Для обычного поиска по равенству в скалярной колонке он не нужен: там работает B-tree.
Частичный индекс недооценён. Если из 50 млн заказов в обработке висит 3 тысячи, индекс по условию WHERE status = 'pending' будет в тысячи раз меньше полного, быстрее обновляться и целиком поместится в кэш.
Порядок колонок в составном индексе
Правило простое: колонки с условием равенства идут первыми, колонка с диапазоном — последней. Для запроса WHERE user_id = 5 AND created_at > '2024-01-01' подходит индекс (user_id, created_at), а не (created_at, user_id). Во втором случае база сможет использовать только первую колонку диапазоном, а отбор по user_id придётся делать перебором по всем подходящим датам.
То же правило работает и для сортировки. Если запрос фильтрует по user_id и сортирует по created_at, индекс (user_id, created_at) отдаёт строки уже в нужном порядке, и узел Sort из плана исчезает.
Добавленный индекс не гарантирует, что планировщик станет им пользоваться. Типичные причины разобраны ниже.
Функция поверх колонки в условии
WHERE LOWER(email) = 'user@example.com' не задействует обычный индекс по email: база ищет значение выражения, а не колонки. Решение — индекс на выражение (lower(email)).
Неявное приведение типов
Сравнение числовой колонки со строковым литералом или перепутанные int и bigint в соединении заставляют базу приводить тип, и индекс по исходной колонке выпадает из плана. Проверяется это быстро: через EXPLAIN до и после приведения литерала к нужному типу.
Низкая селективность
Если условие отбирает половину таблицы, последовательное чтение дешевле случайных обращений по индексу. Планировщик считает это осознанно и выбирает Seq Scan.
Избыток индексов на таблице с интенсивной записью
Каждая вставка и каждое обновление индексируемой колонки обновляют все соответствующие индексы. Таблица, в которую активно пишут, с десятком индексов — повод для ревизии: обычно половина из них не используется.
SELECT schemaname, relname AS table_name, indexrelname AS index_name, idx_scan AS times_used, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;
Два предостережения. Во-первых, счётчики накапливаются с момента последнего сброса статистики: если его делали вчера, нули ничего не доказывают, нужен хотя бы полный бизнес-цикл, включая месячные отчёты. Во-вторых, перед удалением проверьте, не обслуживает ли индекс ограничение уникальности или первичный ключ: такие индексы удалять нельзя, даже если поиском по ним никто не пользуется.
Классика ORM-разработки: один запрос за списком плюс по одному запросу на каждую запись. В логе это выглядит как сотни быстрых запросов вместо одного; каждый из них сам по себе в топ медленных не попадёт, а суммарно съест секунды на сетевых походах и парсинге.
Проблема N+1
# Плохо: 1 + N запросов users = session.query(User).all() # 1 запрос for user in users: print(user.orders) # по запросу на каждого пользователя
# Хорошо: один запрос с соединением (SQLAlchemy) users = session.query(User).options(joinedload(User.orders)).all()
# Django ORM: то же самое users = User.objects.prefetch_related('orders')
На чистом SQL решение то же: одно соединение вместо цикла.
SELECT u.id, u.name, o.id AS order_id, o.total FROM users u LEFT JOIN orders o ON o.user_id = u.id;
Проще всего ловить N+1 счётчиком запросов на одно обращение к приложению: django-debug-toolbar для Django, логирование движка в SQLAlchemy, любой APM (система мониторинга производительности приложений) с трассировкой обращений к базе. Полезная привычка — тест, который падает, когда на эндпоинт уходит больше заданного числа запросов.
SELECT * вместо списка колонок
-- Плохо: тянем все колонки, включая большие текстовые поля SELECT * FROM products WHERE category_id = 5; -- Хорошо: только то, что нужно приложению SELECT id, name, price FROM products WHERE category_id = 5;
Звёздочка закрывает дорогу к Index Only Scan: раз нужны все колонки, база обязана идти в таблицу. Явный список колонок плюс индекс, который их покрывает, оставляет чтение в пределах индекса. Плюс меньше трафика между базой и приложением, что заметно на широких таблицах с текстовыми полями.
Фильтрация в приложении вместо базы
# Плохо: вычитываем всё, фильтруем в коде orders = db.query("SELECT * FROM orders") recent = [o for o in orders if o.created_at > last_month]
-- Хорошо: отбор выполняет база SELECT id, user_id, total, created_at FROM orders WHERE created_at > NOW() - INTERVAL '30 days';
База умеет отбирать строки по индексу, приложение — только перебором, да ещё и после передачи всех данных по сети. Та же логика относится к сортировке, агрегатам и ограничению выборки: ORDER BY, COUNT, LIMIT дешевле выполнить на стороне СУБД.
OR против UNION ALL
-- Вариант 1 SELECT id, total FROM orders WHERE status = 'pending' OR status = 'processing'; -- Вариант 2: отдельная ветка на каждое условие SELECT id, total FROM orders WHERE status = 'pending' UNION ALL SELECT id, total FROM orders WHERE status = 'processing';
Иногда второй вариант быстрее: каждая ветка самостоятельно использует индекс. Иногда планировщик и для первого варианта собирает Bitmap Heap Scan по двум условиям и выигрывает. Универсального правила тут нет, и это как раз случай, когда гадать бессмысленно: сравните оба варианта через EXPLAIN ANALYZE на своих данных и объёмах.
Обновить статистику
Команда ANALYZE собирает статистику о содержимом таблиц, которой планировщик пользуется при построении планов. Устаревшая статистика — одна из самых обидных причин медленных запросов: индексы на месте, запрос написан корректно, а план выбран неудачный.
-- Конкретная таблица ANALYZE orders; -- Вся база ANALYZE; -- Больше деталей по колонке с неравномерным распределением значений ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500; ANALYZE orders;
Увеличение детализации нужно не везде. Типичный кандидат — колонка, где несколько значений занимают почти всю таблицу, а редкие встречаются в долях процента: по умолчанию планировщик о редких значениях знает слишком мало и ошибается в оценке числа строк.
Ключевые параметры PostgreSQL
# postgresql.conf work_mem = 64MB # память на одну операцию сортировки или хэширования shared_buffers = 25% RAM # общий кэш страниц в памяти effective_cache_size = 75% RAM # подсказка планировщику об объёме кэша ОС random_page_cost = 1.1 # для SSD
work_mem задаёт базовый объём памяти, который одна операция сортировки или хэш-таблица может использовать до того, как начнёт писать временные файлы на диск. Если в плане встретилось Sort Method: external merge, сортировка ушла на диск, и увеличение work_mem даёт прямой выигрыш. Важно помнить про множитель: память выделяется на операцию, а не на запрос и не на сессию, поэтому сложный запрос с несколькими сортировками при высокой конкурентности съест кратно больше.
Параметр планировщика random_page_cost по умолчанию равен 4.0 и ориентирован на характеристики обычных жёстких дисков; для хранилищ с быстрым произвольным доступом документация рекомендует снижать это значение. На практике для SSD ставят 1.1–1.5. Эффект прямой: при высокой цене случайного чтения планировщик предпочитает последовательное сканирование индексам, хотя на SSD разница между случайным и последовательным доступом невелика. Параметр часто забывают, а он меняет выбор плана для всей базы сразу.
shared_buffers и effective_cache_size — ориентиры, а не догма. Первый задаёт размер собственного кэша PostgreSQL, второй лишь сообщает планировщику, на какой объём кэширования в системе можно рассчитывать, и памяти сам по себе не занимает.
Бывает, что проблема не в отдельном запросе, а в архитектуре хранения. Тогда индексы дают всё меньший выигрыш: индекс по таблице в сотни миллионов строк перестаёт помещаться в память, обслуживание и перестроение занимают часы, а удаление старых данных превращается в многочасовой DELETE.
PostgreSQL поддерживает декларативное партиционирование таблиц, в том числе по диапазону значений, начиная с версии 10.
CREATE TABLE orders ( id BIGSERIAL, user_id INTEGER, created_at TIMESTAMP NOT NULL, total NUMERIC ) PARTITION BY RANGE (created_at); CREATE TABLE orders_2024 PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2025-01-01'); CREATE TABLE orders_2025 PARTITION OF orders FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
Партиционирование оправдано, когда таблица выросла до десятков и сотен миллионов строк, а большинство запросов фильтруют по ключу разбиения: тогда база отсекает ненужные секции целиком. Бонусом удаление старого периода становится операцией над секцией, а не массовым удалением строк. Если же запросы ходят по всем секциям сразу, выигрыша не будет, зато добавятся накладные расходы на планирование.
Второй системный приём — кэширование на стороне приложения. Если один и тот же запрос с одинаковыми параметрами выполняется тысячи раз в минуту, а данные меняются редко, его результат стоит держать в Redis или Memcached. Карточка товара по идентификатору не обязана ходить в базу при каждом открытии страницы. Цена решения — согласованность: нужна понятная стратегия инвалидации, иначе пользователи будут видеть устаревшие данные.
Частые вопросы про оптимизацию SQL-запросов
Нужно ли добавлять индекс на каждый внешний ключ?
Почти всегда да. PostgreSQL не создаёт индекс на колонку внешнего ключа автоматически, в отличие от первичного ключа, для которого уникальный индекс создаётся при определении ограничения. Поэтому запросы вида WHERE user_id = ? по таблице заказов без отдельного индекса приводят к полному сканированию. Проверить текущие внешние ключи можно так:
SELECT conname, conrelid::regclass AS table_name, a.attname AS column_name FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey) WHERE c.contype = 'f';
EXPLAIN показывает Seq Scan, но запрос быстрый. Это проблема?
Нет. На маленькой таблице последовательное чтение быстрее обращений через индекс: база поднимает её целиком за один проход, обычно прямо из кэша. Планировщик выбирает такой план сознательно, опираясь на статистику. Беспокоиться стоит, когда Seq Scan стоит на таблице в миллионы строк, а условие отбирает единицы записей.
Как ускорить SQL-запрос без изменения кода приложения?
Три рычага работают без правок в коде. Первый — индексы: добавление индекса не требует изменений в запросах. Второй — свежая статистика: ANALYZE по проблемным таблицам, при необходимости с увеличенной детализацией по отдельным колонкам. Третий — конфигурация: work_mem для сортировок, random_page_cost под реальный тип дисков, effective_cache_size под фактический объём памяти. Параллельно стоит включить pg_stat_statements и auto_explain, чтобы следующий виток оптимизации начинался с данных, а не с догадок.
Стоит ли переходить на NoSQL ради производительности?
Только если задача плохо ложится на реляционную модель: экстремальный темп записи с простым доступом по ключу, документы с непредсказуемой схемой, кэш и сессии. Чаще под видом «проблемы масштаба» скрывается отсутствие индекса, N+1 в ORM или конфигурация по умолчанию на сервере с SSD. Прежде чем менять СУБД, пройдите пять шагов выше: смена хранилища переносит сложность в приложение и обходится дороже, чем аудит индексов.
Порядок один и тот же: найти медленные запросы по статистике, прочитать EXPLAIN ANALYZE, добавить нужные индексы, убрать антипаттерны, настроить конфигурацию под своё железо. Первые два шага закрывают большую часть проблем; партиционирование и кэширование — инструменты для случаев, когда упёрлись в объём данных, а не в отдельный запрос. Начните с pg_stat_statements или медленного лога MySQL прямо сегодня: список кандидатов на оптимизацию соберётся за сутки работы под обычной нагрузкой.
Если не хочется держать собственную команду эксплуатации баз, разверните PostgreSQL или MySQL в Cloud Databases в VK Cloud: управляемый сервис с автоматическими резервными копиями в объектное хранилище, восстановлением на точку во времени для PostgreSQL и встроенным анализом запросов через holistic.dev и pgBadger. SLA составляет 99,95% с финансовыми гарантиями. Детали по версиям и настройкам — в документации VK Cloud.
Оставьте заявку, чтобы получить консультацию
Наши специалисты свяжутся с вами в ближайшее время и ответят на все вопросы.
Узнавайте о выходе новых статей в блоге первыми!
Будем держать в курсе новостей и облачных трендов


Почитать по теме

Presigned URL в S3: как выдавать временный доступ к объекту без передачи ключей

Что такое токенизация и почему языковые модели считают текст не так, как человек

