Семь механизмов SQL, на которых спотыкаются в проде: отбор строк, ускорение чтения и дедлокиСтатья · дайджест за 22 ноября
Инженерный натюрморт рабочего стола дата-инженера с чертежами механизмов PostgreSQL, книгой по SQL и терминалом оптимизации запросов
Статья4 раздела≈ 10 мин

Семь механизмов SQL, на которых спотыкаются в проде: отбор строк, ускорение чтения и дедлоки

В SQL легко поймать странный баг из-за одной мелочи: ты пишешь SELECT в самой первой строчке, а база вычисляет его почти в самом конце. Пробуешь написать WHERE SUM(sales) > 4000 — ловишь ошибку синтаксиса. Ставишь GROUP BY — пропадают отдельные чеки, которые ещё нужны. А потом таблица разрастается до десятков миллионов строк, запросы ползут, и две транзакции внезапно вешают друг друга.

Возьмём одну простую таблицу продаж store_sales(id, store, sales, sale_date) и разберём на ней семь базовых механизмов SQL. Примеры проверены на PostgreSQL, но сама логика работает в большинстве реляционных СУБД.

Порядок фильтрации и оконные функции: когда строки схлопываются, а когда нет

Начнём с путаницы между WHERE и HAVING. Разница тут в моменте срабатывания. Если нужно выкинуть лишние исходные строки до любых подсчётов, за это отвечает WHERE. Он идёт до GROUP BY, поэтому спокойно опирается на обычные индексы и сразу режет объём данных.

HAVING включается уже после GROUP BY, когда строки сгруппированы и суммы готовы. В нём проверяют условия по агрегатам вроде SUM() или COUNT(). Если сунуть фильтр по магазину в HAVING, база сначала просуммирует все магазины подряд, а потом выбросит лишнее. Зря потраченное время.

-- Режем лишние магазины через WHERE,
-- потом группируем и отбираем суммы свыше 4000
SELECT store, SUM(sales) AS total_sales
FROM store_sales
WHERE store IN ('store1', 'store2')
GROUP BY store
HAVING SUM(sales) > 4000;

Здесь сначала остаются только записи по store1 и store2, затем база складывает продажи и оставляет магазины с итогом выше 4000. В WHERE так написать нельзя: на этом шаге сумм ещё не существует.

Теперь другая задача: вывести каждую продажу, но рядом показать общую выручку магазина и место чека в рейтинге. Обычный GROUP BY подведёт — он схлопнет все строки магазина в одну итоговую запись. Тут выручают оконные функции с OVER(). Они считают итог или ранг по «окну» строк, связанному с текущей записью, но сами строки не склеивают.

-- Считаем выручку магазина и ранг чека,
-- оставляя каждую продажу отдельной строкой
SELECT
  store,
  sale_date,
  sales,
  SUM(sales) OVER (PARTITION BY store) AS store_total,
  ROW_NUMBER() OVER (
    PARTITION BY store
    ORDER BY sales DESC
  ) AS rn
FROM store_sales;

Конструкция PARTITION BY store делит таблицу на независимые окна по магазинам, а ORDER BY sales DESC внутри ROW_NUMBER() нумерует чеки от крупного к мелкому. Покликай шаги в мини-примере ниже, чтобы увидеть разницу вживую.

Как store_sales меняется в WHERE, HAVING и OVER()

На переключении между третьим и четвёртым шагом всё видно сразу: GROUP BY сворачивает четыре строки в две суммы, а HAVING оставляет одну. Оконная функция ничего не прячет и просто дописывает расчётные колонки справа.

CTE против вложенных подзапросов: читаемость и скрытая материализация

Когда запрос обрастает тремя уровнями вложенных SELECT, читать его приходится из середины наружу. Для таких случаев в SQL есть CTE (обобщённое табличное выражение), которое задаётся через WITH. Оно объявляет именованный временный набор перед основным запросом: код читается сверху вниз, блок можно переиспользовать несколько раз, а с WITH RECURSIVE — обходить деревья категорий.

-- Выносим агрегацию в именованный шаг top_stores
WITH top_stores AS (
  SELECT store, SUM(sales) AS total_sales
  FROM store_sales
  GROUP BY store
)
SELECT *
FROM top_stores
WHERE total_sales > 4000;

На код-ревью часто всплывает миф: переписал подзапрос в CTE — и всё ускорится в пять раз. Увы, нет. По документации PostgreSQL о WITH-запросах, начиная с 12-й версии планировщик по умолчанию не материализует однократный CTE без побочных эффектов. Он просто встраивает его в основной запрос, чтобы протолкнуть WHERE внутрь к индексам. Многократно используемый CTE по умолчанию вычисляется один раз и кэшируется. Поведением можно управлять явно через AS MATERIALIZED или AS NOT MATERIALIZED, если планировщик выбрал неудачный план.

Три способа ускорить чтение: секции, материализованные вьюхи и покрывающие индексы

Когда store_sales вырастает до десятков миллионов строк, в ход идут три разных инструмента ускорения.

Первый — секционирование (Partitioned Tables). Большую таблицу физически делят на куски, например по годам sale_date: до конца 2022-го, 2023-й, 2024-й и 2025+. Подробности синтаксиса есть в разделе документации PostgreSQL о партиционировании.

-- Главная таблица задаёт правило разбиения по дате
CREATE TABLE store_sales (
  id BIGINT,
  store TEXT,
  sales NUMERIC,
  sale_date DATE NOT NULL
) PARTITION BY RANGE (sale_date);

-- Физическая секция под 2024 год
CREATE TABLE store_sales_2024 PARTITION OF store_sales
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Если в запросе есть WHERE sale_date >= '2024-01-01', сработает partition pruning (отсечение секций): база вообще не откроет файлы за 2022–2023 годы. Старый год при этом удаляется мгновенно через DROP TABLE, без тяжёлого DELETE. На маленьких таблицах партиции лучше не городить — они только усложнят схему.

Второй способ — материализованные представления (MATERIALIZED VIEW). Обычный VIEW ничего на диске не хранит: это сохранённый запрос, который каждый раз считается заново. Данные всегда свежие, но тяжёлый отчёт грузит базу. Материализованное представление сохраняет готовый результат на диск: чтение мгновенное, но цифры застывают до явного обновления.

-- Сохраняем готовый расчёт выручки на диск
CREATE MATERIALIZED VIEW mv_store_totals AS
SELECT store, SUM(sales) AS total_sales
FROM store_sales
GROUP BY store;

-- Уникальный индекс для обновления без блокировок
CREATE UNIQUE INDEX idx_mv_store_totals_store
  ON mv_store_totals (store);

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_store_totals;

Обычный REFRESH блокирует читателей на всё время пересчёта. Флаг CONCURRENTLY снимает блокировку, но требует обязательный UNIQUE INDEX на представлении.

Третий способ — покрывающие индексы (Covering Indexes) с INCLUDE. Обычный индекс по store находит нужные строки, но потом базе всё равно приходится ходить в основную таблицу (heap) за колонкой sales. Если добавить sales в INCLUDE, PostgreSQL положит её в листовые страницы B-tree индекса без участия в сортировке ключа.

-- Кладём sales в листья индекса для чтения без таблицы
CREATE INDEX idx_store_sales_cov
  ON store_sales (store)
  INCLUDE (sales);

Тогда планировщик может выбрать Index-Only Scan и прочитать ответ прямо из индекса. По документации Index-Only Scans, под капотом PostgreSQL опирается на visibility map (карту видимости страниц), которую обновляет autovacuum. Если карта устарела, походы в таблицу вернутся. Плюс лишние колонки в INCLUDE раздувают индекс и тормозят INSERT/UPDATE. Проверить, сработало ли отсечение секций или Index-Only Scan, всегда можно через EXPLAIN (ANALYZE, BUFFERS).

Дедлоки: как две транзакции вешают друг друга и три правила защиты

Дедлок (deadlock, взаимная блокировка) возникает при циклическом ожидании. Транзакция A заблокировала строку 1 и ждёт строку 2, а транзакция B держит строку 2 и ждёт строку 1. Никто не может двинуться дальше.

Типичный случай в продакшене — встречные переводы между счетами (1 -> 2 и 2 -> 1), когда код блокирует строки в том порядке, в каком пришли параметры. PostgreSQL сам находит такой цикл и принудительно откатывает одну из транзакций с ошибкой deadlock detected (код SQLSTATE 40P01).

-- Блокируем строки в одном порядке по возрастанию id
BEGIN;

SELECT id, sales
FROM store_sales
WHERE id IN (101, 205)
ORDER BY id
FOR UPDATE;

UPDATE store_sales
SET sales = sales + 500
WHERE id = 101;

COMMIT;

Защита держится на трёх простых правилах. Во-первых, транзакции должны быть короткими: никаких сетевых вызовов и внешних API внутри BEGIN ... COMMIT. Во-вторых, таблицы и строки всегда блокируют в одном предсказуемом порядке, например по возрастанию id — тогда встречные переводы просто встают в очередь. В-третьих, в коде приложения закладывают повтор (retry) транзакции при ошибке 40P01, чтобы редкий конфликт под нагрузкой прошёл для пользователя незаметно.

Хочу подробный разборГолоса помогают выбрать, что разобрать подробно. За этот материал 0 голосов.
Источники · 4