Оптимизация SQL-запросов
Разработчику
Аналитику
Тестировщику
Архитектору
Инженеру
Оптимизация, план и хинты
Медленный запрос редко "чинится сам". Смотрят план выполнения — сканирование, индекс, join, сортировка — меняют запрос или индекс и сравнивают снова. Полезно различать роль индекса (кластерный PK, вторичный по email, покрывающий) и структуру (B⁺, GIN) — типы индексов по роли, пять структур.
Ниже — SQLite (наглядный байт-код) и блок про PostgreSQL в конце. Интерактив на странице.
Полный путь запроса (Transport Subsystem → Query Processor → Execution Engine → Storage Engine) — в Принципах работы SQL-движка. Здесь разбираем этап Execution Engine — план выполнения и оптимизатор.
План запроса
Когда SQL уже разобран парсером, СУБД должна выбрать, как наиболее эффективно выполнить запрос. Именно это делает компонент СУБД, называемый оптимизатором запросов (Query Optimizer). Оптимизатор анализирует несколько возможных способов извлечения данных и выбирает тот, который, по его мнению, будет выполнен быстрее всего.
То, как именно СУБД собирается выполнять наш запрос, детально описывается в плане выполнения запроса (execution plan). Самый простой пример:
SELECT * FROM orders WHERE customer_id = 123;
План покажет:
- использование индекса на поле customer_id;
- последовательное сканирование таблицы (если индекса нет);
- соединение с другой таблицей (JOIN);
- использование временных таблиц;
- какие ресурсы будут задействованы (время, память).
Для этого мы просто добавим ключевое слово EXPLAIN ("объяснить") перед SELECT и увидим результат.
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
Play ITЗагрузка интерактивного демо…
В результате мы увидим план запроса, к примеру:
| addr | opcode | p1 | p2 | p3 | p4 | p5 | comment |
|---|---|---|---|---|---|---|---|
| 0 | Init | 0 | 11 | 0 | 0 | ||
| 1 | OpenRead | 0 | 4 | 0 | 3 | 0 | |
| 2 | Rewind | 0 | 10 | 0 | 0 | ||
| 3 | Column | 0 | 2 | 1 | 0 | ||
| 4 | Ne | 2 | 9 | 1 | BINARY-8 | 84 | |
| 5 | Rowid | 0 | 3 | 0 | 0 | ||
| 6 | Column | 0 | 1 | 4 | 0 | ||
| 7 | Column | 0 | 2 | 5 | 0 | ||
| 8 | ResultRow | 3 | 3 | 0 | 0 | ||
| 9 | Next | 0 | 3 | 0 | 1 | ||
| 10 | Halt | 0 | 0 | 0 | 0 | ||
| 11 | Transaction | 0 | 0 | 18 | 0 | 1 | |
| 12 | Integer | 123 | 2 | 0 | 0 | ||
| 13 | Goto | 0 | 1 | 0 | 0 |
Такой вывод EXPLAIN QUERY PLAN показывает SQLite план выполнения запроса.
- addr — это адрес инструкции в байт-коде виртуальной машины SQLite.
- opcode — это код операции, выполняемой на данном шаге.
- p1, p2, p3, p4, p5 - операнды для кода операции, их значение и смысл зависят от opcode;
- comment - дополнительные комментарии к операции.
- Init (0) - инициализация выполнения запроса.
- OpenRead (1) - открытие таблицы orders для чтения (p2=4 — это номер файла таблицы), p3=3 указывает на индекс, который будет использоваться (если есть), а p1 - номер открываемого курсора (в данном случае 0).
- Rewind (2) - перемещение указателя чтения в начало таблицы orders.
- Column (3) - загрузка значения столбца customer_id(p2=2 - номер столбца, p3=1 - номер курсора).
- Ne (4) - сравнение (Not Equal) значения customer_id (p1=2) с константой 123 (p4=”BINARY-8 84” - представление числа 123 в бинарном формате). Если не равно, то переходит к следующей инструкции.
- Rowid (5) - получение значения rowed (уникального идентификатора строки) текущей строки.
- Column (6) - загрузка значения первого столбца (p2=1).
- Column (7) - загрузка значения второго столбца (p2=2).
- ResultRow (8) - формирование результирующей строки из полученных значений столбцов.
- Next (9) - перемещение указателя чтения на следующую строку. p3=3 указывает, что нужно проверять условия WHERE для этой строки.
- Halt (10) - завершение выполнения запроса, если больше строк нет.
- Transaction (11) - начало (или окончание, тут неявно) транзакции, 18 - флаги.
- Integer (12) - загрузка целого числа 123 (p1=123) в регистр (p2=2), это константа из условия WHERE.
- Goto (13) - переход к инструкции по адресу 1 (OpenRead), это цикл, который повторяется, пока есть строки, удовлетворяющие условию.
Словом, SQLite выполняет запрос, используя сканирование таблицы. Он последовательно перебирает строки в таблице orders, сравнивает значение customer_id с 123, и если значение совпадает, возвращает строку. Цикл Rewind, Next, Ne, Goto отражает этот процесс итерации по строкам. Если таблица orders имеет индекс по столбцу customer_id, то план выполнения мог бы быть более эффективным (было бы меньше операций Next).
Типы планов:
- EXPLAIN – показывает логический план (что будет сделано);
- EXPLAIN ANALYZE : Выполняет запрос и показывает фактическое время выполнения шагов.
Оптимизация
Чтобы оптимизировать запрос, нужно убедиться что используется индекс по столбцу, и вообще существует ли он. Чтобы узнать, есть ли индекс, в SQLite можно использовать:
SELECT name
FROM sqlite_master
WHERE type='index'
AND tbl_name='orders';
Выполнив эту команду, выйдет таблица со списком индексов - в нашем случае она пустая, ведь индексов нет. Также есть команда SHOW INDEX FROM <имя_таблицы>, но это не стандартная команда для SQLite, так что в нашем случае она не сработает.
Поэтому мы создаём новый индекс:
CREATE INDEX idx_customer_id
ON orders (customer_id);
Теперь, повторив запрос на поиск индекса, мы увидим "idx_customer_id".
Хоть мы и не заметили, но повторный запрос станет эффективнее после создания индекса. Можно конечно оптимизировать, выбрав не все столбцы в таблице (SELECT *), а выбрав конкретные (например, SELECT customer_name, order_date, order_total). Но посмотрим тот же запрос. Повторив EXPLAIN:
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
Мы увидим уже другую таблицу:
| addr | opcode | p1 | p2 | p3 | p4 | p5 | comment |
|---|---|---|---|---|---|---|---|
| 0 | Init | 0 | 13 | 0 | 0 | ||
| 1 | OpenRead | 0 | 4 | 0 | 3 | 0 | |
| 2 | OpenRead | 1 | 5 | 0 | k(2,,) | 2 | |
| 3 | Integer | 123 | 1 | 0 | 0 | ||
| 4 | SeekGE | 1 | 12 | 1 | 1 | 0 | |
| 5 | IdxGT | 1 | 12 | 1 | 1 | 0 | |
| 6 | DeferredSeek | 1 | 0 | 0 | 0 | ||
| 7 | IdxRowid | 1 | 2 | 0 | 0 | ||
| 8 | Column | 0 | 1 | 3 | 0 | ||
| 9 | Column | 1 | 0 | 4 | 0 | ||
| 10 | ResultRow | 2 | 3 | 0 | 0 | ||
| 11 | Next | 1 | 5 | 1 | 0 | ||
| 12 | Halt | 0 | 0 | 0 | 0 | ||
| 13 | Transaction | 0 | 0 | 19 | 0 | 1 | |
| 14 | Goto | 0 | 1 | 0 | 0 |
Первый план выполнения (без индекса) использовал полное сканирование таблицы orders. База данных проверяла каждую строку в таблице, сравнивая значение customer_id с 123. Это очень неэффективно для больших таблиц.
Второй план выполнения (с индексом) использует, как видно из результата, другие методы:
- SeekGE (4) - SQLite ищет первую запись в индексе, где customer_id больше или равно 123. Это бинарный поиск, который значительно быстрее, чем полное сканирование. После нахождения записи с customer_id больше или равного 123, SQLite проверяет, равно ли это значение точно 123. Если да, то эта запись подходит для запроса.
- IdxGT (5) - выполняется дополнительная проверка для того, чтобы быть уверенными, что текущая запись соответствует условию = 123.
- DeferredSeek (6) - здесь инициируется поиск по индексу для последующих записей с тем же значением customer_id, если найдена запись, удовлетворяющая условию = 123. Это не всегда используется.
- IdxRow (7) получает rowid из индекса для найденной записи.
- Column (8, 9) извлекает необходимые столбцы из основной таблицы с помощью rowid.
- ResultRow (10) формирует результат (строку) из извлечённых столбцов.
- Next (11) переходит к следующей записи в индексе, чтобы найти другие совпадения, если они есть.
- Halt (12) завершает поиск, когда больше подходящих записей нет.
План выполнения использует индекс для поиска, но только для нахождения первой записи. Он итерирует по результатам поиска, что, в случае, если совпадений много, может быть неэффективно по сравнению с простым EQUAL сравнением. Ключевой фактор, определяющий эффективность — это количество совпадений. Если совпадений много, то итерация по ним может стать узким местом.
В целом, индекс в нашем случае используется, но всё ещё не так эффективно. Почему? Индекс используется для быстрого поиска, но при условии = он фактически превращается в последовательный поиск среди результатов, возвращаемых SeekGE. Индекс помогает найти первую запись с >= (больше или равно), а затем SQL-движок проверяет, равно ли значение 123. И плохо как раз будет, если таких совпадений много.
Покрывающий индекс (нужные столбцы в индексе) помогает, когда запрос часто читает узкий набор полей по условию в индексе. Не дублируйте в индексе все столбцы широкой таблицы "на всякий случай" — индекс ускоряет чтение, но замедляет запись.
Как можно ещё оптимизировать запрос? Можно настроить ORDER BY или LIMIT - если нужен не весь результат, а только несколько записей с customer_id = 123, добавьте LIMIT — это сигнализирует движку, что он не должен искать все совпадения. А если важно упорядочивание результатов, используя ORDER BY. Как только SQL-движок найдёт нужное количество строк, он прервёт поиск.
Если вы обратили внимание, в индексированном плане пунктов больше. Количество пунктов в плане выполнения не всегда показывает скорость - он показывает последовательность операций, выполняемых SQL-движком. Главное — время, потраченное для выполнения каждой операции. Можно иметь 15 пунктов, но потраченное время на поиск в индексе может быть очень маленьким, в то время как работа с основной таблице будет медленнее.
Оптимизация — это процесс выбора наиболее эффективного способа выполнения SQL-запроса. Здесь важно учитывать - какие индексы доступны, какие таблицы участвуют (JOIN), какие фильтры применяются (WHERE), сколько строк может вернуть запрос, сколько памяти и CPU используется, и какая статистика по данным. Цель - минимизировать затрату ресурсов (время, дисковые операции, память) для получения тех же результатов. План выполнения представляет собой пошаговое описание того, как СУБД собирается выполнять запрос, показывая какие таблицы читаются, как соединяются данные, какие индексы используются, как фильтруются строки и в каком порядке выполняются операции.
Зачем вообще это нужно? Это позволяет, как мы поняли выше, выявить узкие места, оценить влияние индексов, прогнозировать нагрузку, диагностировать проблемы. Если запрос тормозит - смотрим план и ищем причину, пробуем разные варианты написания SQL и сравниваем планы. В SQLite, как и в других СУБД, план выполнения содержит определённые ключевые слова, которые обозначают типы операций.
Вот основные из них:
| Операция | Значение |
|---|---|
| EXPLAIN / EXPLAIN QUERY PLAN | Показывает логический план выполнения запроса |
| SCAN TABLE / FULL SCAN | Полное сканирование таблицы (медленно, особенно при больших объёмах данных) |
| SEARCH TABLE / INDEX SCAN / INDEX SEEK | Поиск через индекс (гораздо быстрее полного сканирования) |
| USE TEMP B-TREE / USE TEMP INDEX | Использование временной структуры данных (может быть медленным) |
| COMPOUND SELECT | Выполняется объединение нескольких SELECT'ов (UNION, INTERSECT и т.п.) |
| OPEN CURSOR / CLOSE CURSOR | Открытие/закрытие курсора для чтения данных |
| COLUMN | Получение значения конкретной колонки |
| COMPARE / NE / EQ / GT / LT | Операции сравнения (не равно, равно, больше, меньше) Not Equal, Equal, Greater Then, Less Then. |
| ROWID / IDXROWID | Обращение к уникальной строке через её rowid |
| NEXT / PREV | Переход к следующей/предыдущей строке (чаще всего в цикле) |
| RESULT ROW | Формирование строки результата |
| HALT | Окончание выполнения запроса |
| GOTO | Перепрыгивание на другую инструкцию (цикл, условный переход) |
К примеру, SeekGE означает "искать первую запись которая больше или равна" (Greater or Equal).
Чтобы расшифровать план выполнения, нужно смотреть на шаги с большими числами строк или стоимости — это потенциальные проблемы, искать fullscan (полное сканирование, если видим Table Scan или Seq Scan — это означает, что происходит полный просмотр таблицы. Такой способ может быть медленным, особенно при большом объёме данных.), проверять использование индексов (к примеру SEARCH, INDEX, Seek), и следить за временем. Запрос EXPLAIN ANALYZE будет указывать время выполнения каждого шага. И обратите внимание на временные таблицы и сортировки - если они излишние, тоже могут замедлить работу.
Словом, чтобы оптимизировать, нужно:
- выбирать только нужные столбцы;
- добавлять составные индексы, если используется несколько условий;
- использовать LIMIT и OFFSET для ограничения количества возвращаемых строк;
- использовать ORDER BY с индексом - если сортировка часто встречается в запросах;
- избегать подзапросов в WHERE, если можно использовать JOIN;
- нормализовывать или денормализовывать данные в зависимости от задачи.
Оптимизатор
★ Как работает оптимизатор?
- Парсинг запроса – разбирается структура SQL;
- Семантический анализ – проверяются названия таблиц, столбцов, права пользователя;
- Генерация возможных планов – оптимизатор создаёт десятки или даже тысячи вариантов выполнения одного и того же запроса.
- Оценка стоимости каждого плана – учитывается количество строк, наличие индексов, размер таблиц, статистика БД и т.д.;
- Выбор оптимального плана – физическое выполнение запроса по выбранному плану;
- Исполнение плана – физическое выполнение запроса по выбранному плану.
Факторы, влияющие на оптимизацию:
| Фактор | Влияние |
|---|---|
| Индексы | Ускоряют поиск нужных записей |
| Статистика | Помогает оптимизатору правильно оценить стоимость операций |
| Размер таблиц | Влияет на выбор между последовательным сканированием и использованием индекса. |
| JOIN | Оптимизатор решает порядок соединений. |
| Подзапросы | Могут быть переписаны в JOIN |
| Query Hints | Подсказывают оптимизатору, как работать с запросом. |
Хинты
★ Хинты – специальные директивы, подсказки запроса, которые программист может добавить в SQL-запрос, чтобы повлиять на поведение оптимизатора.
Хинты позволяют:
- принудительно использовать определённый индекс;
- изменить тип соединения;
- отключить определённые оптимизации;
- указать движку, как обрабатывать данные.
Примеры хинтов в разных СУБД:
Oracle:
SELECT /*+ INDEX(employees emp_last_name_idx) */ *
FROM employees
WHERE last_name = 'Smith';
SQL Server (осторожно):
-- Эквивалент READ UNCOMMITTED: возможны незафиксированные ("грязные") строки
SELECT order_id, customer_id FROM customers WITH (NOLOCK);
Не используйте для денег, остатков, отчётов, где важна точность. Альтернативы: READ COMMITTED SNAPSHOT, снимки для отчётов.
PostgreSQL не имеет табличных хинтов в стиле Oracle; влияют через:
SET LOCAL statement_timeout = '30s';
MySQL:
SELECT * FROM table USE INDEX (index_for_column)
WHERE column = 1;
Кстати говоря, а что такое RowID? Чем он отличается от обычной записи с ID? В SQLite, к примеру, это уникальный 64-битный целочисленный идентификатор, автоматически назначаемый каждой строке в таблице. Он используется СУБД для быстрого доступа к строкам и присутствует неявно во всех таблицах, кроме тех, что созданы с ключевым словом WITHOUT ROWID. Если просто создать таблицу, то id (первичный ключ) будет работать как RowID.
Виды оптимизаций
Давайте изучим виды оптимизаций:
- SQL-оптимизация подразумевает переписывание запросов (уменьшение количества данных в SELECT/WHERE), использование EXISTS вместо IN, оптимизацию JOIN-ов и подзапросов;
- Индексная оптимизация включает создание правильных индексов, избегание лишних индексов, использование составных индексов;
- Оптимизация на уровне СУБД — это настройка конфигурации сервера (буферный пул, количество соединений), обновление статистики, использование партиционирования;
- Аппаратная или системная оптимизация включает в себя шардинг (разделение БД на части для уменьшения нагрузки), репликацию (чтение с реплик, запись на мастер), использование SSD, увеличение ОЗУ, горячее или холодное хранение данных;
- Кэширование включает в себя запись результатов в кэш, использование Redis/Memcached (это реализация через прикладной уровень) и кэш запросов (в некоторых СУБД).
EXISTS и IN: EXISTS устойчив к NULL в подзапросе; NOT IN с NULL может дать пустой результат (Фильтрация и трёхзначная логика). Планировщик часто строит одинаковый semi-join — сравнивайте через EXPLAIN. Для "нет связи" предпочтительнее NOT EXISTS.
Для упрощения сложных запросов, объединения нескольких таблиц, ограничения доступа к данным (например, скрыть некоторые столбцы) можно использовать View (представления). К примеру, у нас есть таблица, где 10 столбцов, из них 2 - секретные (допустим, данные клиентов). И мы не хотим показывать их аналитику - тогда мы создаём представление которое содержит лишь 8 открытых столбцов (CREATE VIEW <имя> AS SELECT <только нужные 8 столбцов>), даём доступ к этому представлению аналитику, а к основной таблице - закрываем доступ. Итого - аналитик не сможет сделать SELECT * в основную таблицу, и даже может и не знать о существовании других столбцов.
Также можно использовать секционированные таблицы — это разделение таблицы на несколько частей (секций) по какому-то признаку (например, по дате), что позволяет эффективно управлять большими объёмами данных и при выборке по диапазону секций улучшает производительность. К примеру, логи за разные месяцы — каждая часть таблицы соответствует одному месяцу.
Временные таблицы - ещё одно средство, они существуют только в рамках одного сеанса или транзакции, полезны для промежуточных вычислений (могут кстати иметь индексы, триггеры и прочее) - для этого используем команду CREATE TEMPORARY TABLE temp_results AS SELECT ...; и можем временно туда что-то положить и сравнивать, работать, анализировать в одном запросе. К примеру, делаем запрос в одну таблицу, сохраняем во временную, а из второй таблицы удаляем все, что NOT IN временной таблице. Темпоральные таблицы хранятся в базе данных tempdb. Существует два вида временных таблиц: локальные и глобальные. Они отличаются друг от друга именами, видимостью и доступностью. В качестве первого символа имен локальных временных таблиц в качестве первого символа в именах имеется единый знак чисел (#); Они видны только текущему подключению для пользователя и удаляются, когда пользователь отключается от экземпляра SQL Server. Глобальные временные таблицы имеют два числовых знака (##) в качестве первых символов их имен; Они видны любому пользователю после создания и удаляются, когда все пользователи, ссылающиеся на таблицу, отключаются от экземпляра SQL Server.
В оптимизации частую проблему вызывает широкая таблица - которая содержит много столбцов (это возникает при денормализации). Такие таблицы сложнее обслуживать, работают они медленно, но всё же часто применяются. Задачи порой могут прилететь в стиле "почему всё так долго", хотя используется огромная широкая таблица 😊
Существуют, кстати и системные таблицы. Они хранят метаданные о структуре СУБД - список таблиц, столбцов, индексов и т.д. Например, в SQLite это sqlite_master, information_schema в MySQL/PostgreSQL.
План выполнения в PostgreSQL
В PostgreSQL план читают командами EXPLAIN и EXPLAIN ANALYZE (см. пример ниже; в отличие от табличного вывода SQLite выше).
Практика именно по JSONB-фильтрам, операторам @>/->> и индексам GIN с разбором EXPLAIN — в статье Практикум PostgreSQL по JSONB.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 123;
| Режим | Поведение |
|---|---|
EXPLAIN | Оценочный план без выполнения запроса |
EXPLAIN ANALYZE | Выполняет запрос и показывает фактическое время и число строк |
BUFFERS | Попадания и чтения из shared buffers |
VERBOSE | Имена столбцов и выражений в узлах плана |
Важно: EXPLAIN ANALYZE для UPDATE/DELETE изменяет данные — используйте только в тестовой среде.
Узлы сканирования
| Узел | Когда применяется |
|---|---|
Seq Scan | Нет подходящего индекса или низкая селективность |
Index Scan | Высокая селективность, небольшой результат |
Index Only Scan | Все нужные столбцы покрыты индексом |
Bitmap Index Scan | Средняя селективность (порядка 0,1–10 %) |
Узлы соединений
| Узел | Сценарий |
|---|---|
Nested Loop | Маленькое внешнее отношение |
Hash Join | Равенство по ключу, достаточно work_mem |
Merge Join | Предварительно отсортированные входы |
Метрики оценки: cost=startup..total, rows, width. С ANALYZE добавляются actual time, фактические rows, loops (критично для вложенных циклов).
На что смотреть при разборе — большие расхождения rows (оценка vs факт), Seq Scan на крупных таблицах, лишние Sort и материализации. После изменений схемы или данных обновляйте статистику: ANALYZE table_name;.
Подробнее о метаданных для диагностики: Словарь данных и системные каталоги.
Статистика и ANALYZE
Оптимизатор опирается на статистику в pg_statistic (собирается командой ANALYZE). Без актуальной статистики оценка rows в плане расходится с реальностью — возможны лишние Seq Scan и медленные join.
ANALYZE orders;
ANALYZE VERBOSE shop_data.products;
-- Когда последний раз собирали статистику
SELECT schemaname, relname, last_analyze, n_live_tup, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE schemaname = 'shop_data'
ORDER BY n_mod_since_analyze DESC;
Повышение детализации для "тяжёлого" столбца:
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;
ANALYZE orders;
| Параметр | Влияние |
|---|---|
random_page_cost | Выше на HDD → чаще seq scan; на SSD обычно 1.1–1.5 |
effective_cache_size | Оценка доступности данных в кэше |
default_statistics_target | Объём выборки для ANALYZE (по умолчанию 100) |
work_mem | Память на сортировку и hash join в одном узле плана |
Симптомы устаревшей статистики: в EXPLAIN ANALYZE оценка rows сильно отличается от фактических; один и тот же запрос с разными параметрами ведёт себя непредсказуемо. Решение — ANALYZE после массовых загрузок или > ~20% изменений таблицы (часто срабатывает autovacuum).
Практика на схеме shop_data: Практикум shop_data.
См. также
- Семь стратегий масштабирования БД
- Девять рычагов производительности БД — партиции,
EXPLAINи др. - Сложные индексы
- Материализованные представления