SQL — выполнение запросов и ограничения

Вот структурированная выжимка в формальном академическом стиле, оптимизированная для Obsidian.

Внутреннее устройство и выполнение SQL-запросов (Query Planner)

Теги: sql databases interview queryplanner performance

SQL является декларативным языком программирования: разработчик описывает требуемый результат, а СУБД (через компонент Query Planner/Optimizer) самостоятельно определяет наиболее эффективный алгоритм извлечения данных.

1. Порядок написания vs Порядок выполнения

Фундаментальное правило для понимания SQL — различие между синтаксической структурой запроса и логической последовательностью его обработки ядром СУБД.

✍️ Порядок написания (Синтаксический)

1. SELECT (выборка столбцов)
2. FROM (основная таблица)
3. JOIN (присоединяемые таблицы)
4. WHERE (фильтрация строк)
5. GROUP BY (группировка)
6. HAVING (фильтрация групп)
7. ORDER BY (сортировка)
8. LIMIT / OFFSET (ограничение выборки)

⚙️ Порядок выполнения (Логический)

1. FROM и JOIN — определение источников данных и формирование декартова произведения/соединения.
2. WHERE — первичная фильтрация строк (до агрегации). Именно поэтому здесь недоступны алиасы из SELECT.
3. GROUP BY — формирование групп на основе заданных ключей.
4. HAVING — вторичная фильтрация (применяется к уже сформированным группам).
5. SELECT — проекция данных: вычисление выражений, функций, применение алиасов.
6. ORDER BY — сортировка результирующего набора.
7. LIMIT / OFFSET — финальное усечение количества возвращаемых строк.

2. Жизненный цикл SQL-запроса

При поступлении запроса в СУБД он проходит 4 основные стадии:

1. Парсер (Parser): Лексический и синтаксический анализ. Проверка на соответствие грамматике SQL.
2. Анализатор (Analyzer / Binder): Семантический анализ. Проверка существования таблиц/столбцов, валидация типов данных и проверка прав доступа (ACL).
3. Оптимизатор (Query Optimizer / Planner): Генерация возможных планов выполнения и выбор оптимального (с наименьшей стоимостью).
4. Исполнитель (Executor): Физическое выполнение выбранного плана (обращение к памяти и дисковой подсистеме).

3. Механизмы работы Оптимизатора (Cost-Based Optimizer)

Современные СУБД используют CBO (Cost-Based Optimizer) — оптимизатор на основе стоимости. Для оценки стоимости (Cost) различных планов выполнения СУБД использует статистику, которая включает:

• Общее количество строк в таблицах (Tuples).
• Кардинальность (количество уникальных значений в столбцах).
• Наличие и тип индексов.
• Гистограммы распределения данных (помогают оценить селективность условия WHERE).

4. Физические операторы (Как СУБД обрабатывает данные)

Оптимизатор транслирует логические операции в физические алгоритмы.

Методы доступа к данным (Access Methods)

• Sequential Scan (Full Table Scan): Последовательное чтение всей таблицы с диска. Применяется при отсутствии индексов или если запрос требует чтения большей части таблицы (низкая селективность).
• Index Scan: Поиск идентификаторов строк (RowID/CTID) в B-Tree индексе с последующим обращением к таблице за самими данными.
• Index Only Scan: Извлечение данных исключительно из индекса (без обращения к таблице), если индекс содержит все запрашиваемые столбцы (Covering Index).

Алгоритмы соединения таблиц (Join Algorithms)

• Nested Loop Join (Вложенные циклы): Итеративный обход: для каждой строки из внешней таблицы ищется совпадение во внутренней. Эффективен при малых объемах данных и наличии индексов на ключах соединения.
• Hash Join (Хэш-соединение): Построение хэш-таблицы в оперативной памяти из меньшей таблицы, затем сканирование большей таблицы с поиском по хэшу. Оптимален для больших объемов неиндексированных данных.
• Merge Join (Соединение слиянием): Параллельный проход по двум предварительно отсортированным наборам данных. Наиболее эффективен, если данные уже отсортированы (например, извлечены через Index Scan).

Алгоритмы агрегации (GROUP BY)

• HashAggregate: Построение хэш-таблицы в памяти для подсчета агрегатов. Быстро, но требовательно к RAM.
• GroupAggregate: Требует предварительной сортировки данных по ключу группировки. Используется при нехватке памяти или если данные уже отсортированы.

Сортировка (ORDER BY)

• In-memory Sort (QuickSort): Выполняется в оперативной памяти (в PostgreSQL ограничено параметром work_mem).
• External Merge Sort: Выполняется с использованием временных файлов на диске. Крайне медленная операция, которой следует избегать путем создания индексов.

5. Ключевые концепции для собеседований

• Predicate Pushdown (Проталкивание предикатов): Оптимизационная техника, при которой условия из блока WHERE применяются на самом раннем этапе (до выполнения JOIN), чтобы минимизировать объем данных, передаваемых на следующие узлы обработки.
• Влияние LIMIT на план запроса: Наличие LIMIT может радикально изменить план выполнения. Оптимизатор может отказаться от Hash Join (требующего полного сканирования) в пользу Nested Loop, чтобы прервать выполнение сразу после нахождения нужного количества строк (Short-circuiting).
• Анализ производительности: Для диагностики неоптимальных запросов используется команда EXPLAIN (показывает предполагаемый план) и EXPLAIN ANALYZE (выполняет запрос и показывает реальное время и количество строк на каждом узле плана).

В SQL работа с датами немного отличается в зависимости от СУБД (PostgreSQL, MySQL, Oracle, MS SQL), но основные сценарии у всех одинаковые.

Ниже приведен список самых важных функций, разбитых по задачам. Основной синтаксис дан по стандарту ANSI SQL / PostgreSQL (с пометками для MySQL).

Работа с датой и временем

Типы данных

1. Текущая дата и время

CURRENT_DATE                     -- 2024-05-15 (тип DATE)
CURRENT_TIME                     -- 14:30:00 (тип TIME)
CURRENT_TIMESTAMP, NOW()         -- 2024-05-15 14:30:00.123+03 (тип TIMESTAMPTZ)

2. Извлечение частей даты (EXTRACT)

EXTRACT(YEAR   FROM col)         -- Год (2024)
EXTRACT(MONTH  FROM col)         -- Месяц (1..12)
EXTRACT(DAY    FROM col)         -- День месяца (1..31)
EXTRACT(DOW    FROM col)         -- День недели (0 - Вс, 1 - Пн ... 6 - Сб)
EXTRACT(EPOCH  FROM col)         -- Unix timestamp в секундах

3. Округление даты (DATE_TRUNC) — для GROUP BY

Обрезает дату до начала указанного интервала (возвращает TIMESTAMP).

DATE_TRUNC('day',   col)         -- 2024-05-15 00:00:00
DATE_TRUNC('week',  col)         -- Начало недели (Пн, 00:00:00)
DATE_TRUNC('month', col)         -- 2024-05-01 00:00:00
DATE_TRUNC('year',  col)         -- 2024-01-01 00:00:00

4. Арифметика с интервалами (INTERVAL)

col + INTERVAL '3 days'          -- Прибавить 3 дня
col - INTERVAL '1 month'         -- Вычесть 1 месяц
col + INTERVAL '2 hours 30 mins' -- Прибавить 2.5 часа
CURRENT_DATE - INTERVAL '7 days' -- Ровно неделю назад от текущего дня

5. Разница между датами и Возраст

date1 - date2                    -- Разница в днях (целое число: 15)
AGE(date1, date2)                -- Интервал: '1 year 2 mons 3 days'
AGE(birth_date)                  -- Возраст относительно CURRENT_DATE
EXTRACT(YEAR FROM AGE(birthday)) -- Полных лет (число)

6. Преобразование и Форматирование

📌 Констрейнты (Ограничения целостности) в SQL

Констрейнты задают правила валидации данных на уровне движка СУБД. При нарушении правила транзакция откатывается с ошибкой.

1. Именование констрейнтов (Конвенция)

2. Синтаксис создания (ALTER TABLE)

-- 1. PRIMARY KEY
ALTER TABLE users 
    ADD CONSTRAINT pk_users PRIMARY KEY (id);
 
-- 2. FOREIGN KEY (с каскадными действиями)
-- Варианты ON DELETE / ON UPDATE: RESTRICT, CASCADE, SET NULL, NO ACTION
ALTER TABLE orders 
    ADD CONSTRAINT fk_orders_user_id 
    FOREIGN KEY (user_id) REFERENCES users(id) 
    ON DELETE RESTRICT 
    ON UPDATE CASCADE;
 
-- 3. UNIQUE (одиночный и составной)
ALTER TABLE users 
    ADD CONSTRAINT uq_users_email UNIQUE (email);
 
ALTER TABLE user_roles 
    ADD CONSTRAINT uq_user_roles_user_role UNIQUE (user_id, role);
 
-- 4. CHECK (логические выражения над колонками)
ALTER TABLE products 
    ADD CONSTRAINT chk_products_price CHECK (price > 0);
 
ALTER TABLE bookings 
    ADD CONSTRAINT chk_bookings_dates CHECK (end_date > start_date);
 
-- 5. NOT NULL (синтаксис отличается от остальных)
ALTER TABLE users 
    ALTER COLUMN email SET NOT NULL;
 
-- 6. EXCLUDE (PostgreSQL: исключение пересечений)
CREATE EXTENSION IF NOT EXISTS btree_gist;
 
ALTER TABLE bookings 
    ADD CONSTRAINT excl_bookings_no_overlap 
    EXCLUDE USING gist (room_id WITH =, daterange(start_date, end_date) WITH &&);