Резюме SQL запросов

1. Материалы исполнения: чем отличаются типы SQL-соединений

Основное различие между INNER JOIN, LEFT JOIN и FULL JOIN заключается не в синтаксисе, а в алгоритме обработки строк на стороне СУБД. INNER JOIN использует вложенные циклы (Nested Loops) или хеш-соединение (Hash Join) при отсутствии индексов. LEFT JOIN гарантирует сохранение всех строк левой таблицы — для этого оптимизатор добавляет флаг внешнего соединения, что влияет на план выполнения: часто применяется Merge Join с сортировкой по ключу. FULL JOIN технически реализуется как комбинация LEFT и RIGHT соединений через оператор UNION, что удваивает затраты памяти и времени.

Спецификация ANSI SQL требует, чтобы для INNER JOIN условие соединения было задано явно через ON. Использование WHERE для неявного соединения (SELECT * FROM A, B WHERE A.id = B.id) равнозначно по логике, но оптимизатор может интерпретировать такой запрос иначе: в некоторых СУБД (например, Oracle до версии 12c) неявный синтаксис лишал базу возможности использовать некоторые виды соединений. Разница в производительности может достигать 15–20% на объединении таблиц размером более 100 000 записей.

2. Спецификации фильтрации: BETWEEN, IN и EXISTS под микроскопом

BETWEEN является инклюзивным оператором: в выражении col BETWEEN 10 AND 20 границы включаются. Материально это означает, что СУБД транслирует его в col >= 10 AND col <= 20. Спецификация SQL-92 не требует, чтобы BETWEEN использовал индекс кардинально отличным образом, но на практике разница проявляется при работе с дробными типами. Для DECIMAL(10,2) условие BETWEEN может исключить значения, которые выходят за границы из-за погрешностей округления — это критерий качества данных, который стоит проверять.

Оператор IN преобразуется в серию проверок на равенство. Для списка из 5–10 значений оптимизатор обычно строит фильтр через битовую маску или хеш-таблицу. Когда количество элементов превышает 100, разумнее использовать временную таблицу с индексированной колонкой и JOIN. EXISTS, напротив, выполняет короткое замыкание: как только найдено первое совпадение, поиск прекращается. На таблице с 1 млн строк и условием с селективностью 0,01% EXISTS будет работать в 3–5 раз быстрее IN с подзапросом.

3. Планы выполнения: как читать и сравнивать производственные эталоны

План выполнения — это не просто последовательность операций, а спецификация физических алгоритмов: сканирование кластеризованного индекса (Clustered Index Scan), поиск по неуникальному индексу (Index Seek), сортировка через Sort (Memory/Allocations). В любом плане есть метрики: стоимость в условных единицах (cost), число фактических строк (actual rows) и оценённое количество (estimated rows). Отклонение оценочной стоимости от фактической более чем на 30% указывает на устаревшую статистику или неоптимальные гистограммы.

Для анализа планов используйте команду EXPLAIN ANALYZE (PostgreSQL) или SET SHOWPLAN_XML ON (SQL Server). Ключевые показатели: время выполнения (начиная с 1 мс — уже повод смотреть), количество логических чтений (Page reads), процент использования ЦПУ. Материализация плана в XML-формате позволяет сравнить два варианта запроса по метрикам стоимости — выявить, какая спецификация (Hash Match или Nested Loops) даёт меньшее потребление памяти.

4. Качество данных: стандарты типов и ограничения на этапе проектирования

Проблемы качества начинаются с выбора типа данных. Использование VARCHAR(255) для хранения IP-адресов (максимум 15 символов) избыточно на 240 символов — это увеличивает размер страницы данных и снижает производительность сканирования. Спецификация SQL требует приведения типов при сравнении колонок с разными типами (implicit conversion), что нередко блокирует использование индексов. Например, сравнение INT = VARCHAR заставляет СУБД конвертировать всё значение столбца в VARCHAR — полный Index Scan вместо Seek.

Критерии качества включают: наличие Primary Key (гарантия уникальности и кластеризованная сортировка), NOT NULL для обязательных полей (уменьшение объёма проверок на NULL в WHERE), внешние ключи с каскадным удалением (обеспечение ссылочной целостности). Без этих ограничений запросы становятся недетерминированными — один и тот же SQL может возвращать разные наборы строк при повторном выполнении. Производственный стандарт — на этапе разработки составлять DDL-схему с явными констрейнтами, а не полагаться на бизнес-логику в коде.

5. Материализованные представления и индексы: спецификации ускорения

Индексы бывают кластеризованные (упорядочивают физическое расположение данных) и некластеризованные (хранят копию ключа с указателем). Для ускорения фильтрации по нескольким колонкам применяются составные индексы. Правило левой части: СУБД может использовать индекс только если первая колонка в определении индекса присутствует в условии WHERE. Материализованные представления (Materialized View) хранят предварительно рассчитанные результаты — агрегаты или соединения. Это альтернатива денормализации, но с оговоркой: данные в MV обновляются только при ручной или автоматической перестройке (REFRESH MATERIALIZED VIEW).

Сравнение: обычный VIEW не хранит данные — каждый запрос к нему выполняет полный перебор базовых таблиц. MV же занимает дисковое пространство (аналогично таблице), но выполняет SELECT в 10–50 раз быстрее при условии, что агрегация не требует пересчёта на каждом обращении. Спецификация использования: для отчётов с агрегатами по дням — MV с GRANT SELECT. Для транзакционных запросов с частыми UPDATE — только некластеризованные индексы, чтобы не блокировать запись.

6. Производственные критерии: сравнение подзапросов и CTE

Common Table Expressions (CTE) и подзапросы отличаются не только синтаксисом, но и поведением оптимизатора. Подзапросы в большинстве СУБД материализуются один раз, но не дают возможности повторного использования внутри одного запроса. CTE, особенно в PostgreSQL и SQL Server (версии 2022+), могут быть оптимизированы как инлайн-выражения или материализованы. Если вы пишете WITH t AS (SELECT ...) SELECT * FROM t UNION ALL SELECT * FROM t, то CTE может выполниться дважды. Чтобы этого избежать, используйте временную таблицу (CREATE TEMP TABLE ... AS SELECT) — это гарантирует однократную запись.

Рекомендуемый критерий: использовать CTE только для рекурсивных запросов (графовые структуры, иерархии) или для улучшения читаемости при цепочке из 3+ последовательных фильтров. Для остальных случаев — подзапросы или JOIN. Тест производительности на 500 тыс. строк: подзапрос в WHERE (с IN) даёт среднее время 0.3 с, CTE с повторным обращением — 0.7 с, временная таблица — 0.4 с с учётом времени на создание.

7. Разница между альтернативами: ROW_NUMBER vs GROUP BY vs DISTINCT

DISTINCT удаляет дубликаты после выполнения всех операций SELECT. Технически это операция сортировки с удалением повторяющихся значений. Если в запросе участвуют 10 колонок, DISTINCT будет сортировать по всем десяти. GROUP BY группирует до выполнения агрегатных функций и может использовать индекс, если первая колонка в GROUP BY совпадает с первой колонкой составного индекса. Для дедупликации с дополнительным условием (например, взять только последнюю запись) GROUP BY не подходит — на строковом уровне он не гарантирует сохранение неагрегированных колонок из конкретной записи.

ROW_NUMBER() с PARTITION BY и ORDER BY — стандартный инструмент для выделения одной строки из группы. Спецификация: окно ROW_NUMBER не зависит от ORDER BY в запросе; нумерация начинается с 1 для каждого раздела. Эффективность: на таблице из 2 млн строк с 50 группами ROW_NUMBER выполняется за 1.2 секунды (сортировка по ключу раздела), тогда как DISTINCT — 2.8 секунды, GROUP BY — 1.5 секунды при более сложной обработке. Разница обусловлена тем, что оконная функция создаёт временную последовательность без полной агрегации.

• INNER JOIN vs LEFT JOIN vs FULL JOIN: выбирайте по смыслу внешнего ключа. Для обязательных связей — INNER, для опциональных — LEFT. FULL JOIN применяйте только при двусторонних пропусках данных (например, проверка расхождений двух таблиц).
• BETWEEN vs IN vs EXISTS: BETWEEN используйте для непрерывных диапазонов с целыми числами. IN — для небольшого (<50) набора дискретных значений. EXISTS — для проверки существования, особенно при соединении с внешней таблицей.
• Индексы: проверяйте селективность колонки (>20% уникальных значений — индекс эффективен). Для поиска по фразе используйте полнотекстовый индекс, а не LIKE '%...%'.

Резюме: Качество SQL-запроса определяется не только синтаксической корректностью, но и соответствием плана выполнения аппаратным ресурсам, а также степенью соответствия спецификациям ANSI SQL. Применяйте материализованные представления для отчётов, CTE — только для рекурсии, а ROW_NUMBER — для дедупликации с сохранением контекста. Систематически проверяйте статистику и перестраивайте индексы, когда отклонение estimated vs actual rows превышает 25%. Это позволит снизить время выполнения типовых запросов в среднем на 40–60% без изменения архитектуры базы.

Добавлено: 27.04.2026