10 вопросов по теме «Data Engineering: Оптимизация SQL» на собеседовании

Короткий ответ: План читают снизу вверх и изнутри наружу: листья — это доступ к таблицам (Seq Scan / Index Scan), выше идут джойны и агрегации. Ищут самый дорогой узел, полные сканы больших таблиц под фильтром и выбранный метод джойна.

Подробно:

1. Направление чтения — от самых вложенных узлов (чтение данных) к корню (итоговый результат). В EXPLAIN ANALYZE сравнивают оценку (rows=) с фактом (actual rows): расхождение в разы выдаёт кривую статистику оптимизатора.
2. Способ доступа — Seq Scan (полный скан) против Index / Index Only Scan. Полный скан большой таблицы под селективным фильтром — первый кандидат на оптимизацию.
3. Метод джойна — Nested Loop, Hash Join, Merge Join (см. таблицу).
4. Дорогой узел — с наибольшим cost/временем; на нём и концентрируют усилия.

EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= DATE '2026-01-01';

-- Hash Join  (cost=...  rows=...)        <- метод джойна
--   ->  Seq Scan on orders o            <- полный скан: фильтр не по индексу
--         Filter: (created_at >= ...)
--   ->  Hash
--         ->  Seq Scan on customers c

⚠️ Частая ошибка: смотреть только на итоговый cost и игнорировать расхождение rows vs actual rows — именно оно выдаёт устаревшую статистику и выбор плохого плана.

Короткий ответ: Главное — сократить объём читаемых данных: обрезка партиций по WHERE на ключе партиционирования, чтение только нужных колонок (никаких SELECT *), ранняя фильтрация (предикатный пушдаун) и отказ от лишних DISTINCT/ORDER BY.

Подробно:

1. Меньше строк — фильтр по колонке партиционирования включает обрезку партиций; предикаты проталкиваются к сканеру (predicate pushdown), чтобы не тянуть лишнее с диска.
2. Меньше колонок — в колоночном хранилище читаются только перечисленные поля; SELECT * убивает это преимущество и раздувает I/O.
3. Убрать лишнюю работу — DISTINCT и ORDER BY требуют сортировки и шафла; часто DISTINCT лишь маскирует фан-аут джойна, а ORDER BY во вложенном запросе бесполезен.
4. Ранние джойны и предагрегация — фильтровать и агрегировать до соединения, если это уменьшает объём на входе джойна.

-- Медленно: полный скан + все колонки + лишний DISTINCT + функция на ключе
SELECT DISTINCT *
FROM events e JOIN users u ON u.id = e.user_id
WHERE date_trunc('day', e.ts) = DATE '2026-06-01';

-- Быстро: обрезка партиций, нужные колонки, предикат на сырой колонке
SELECT e.user_id, e.event_type, u.country
FROM events e
JOIN users u ON u.id = e.user_id
WHERE e.event_date = DATE '2026-06-01';   -- ключ партиционирования = event_date

⚠️ Частая ошибка: оборачивать колонку партиционирования функцией (date_trunc, CAST) — это ломает обрезку партиций, и движок сканирует всю таблицу.

Короткий ответ: Соединять так, чтобы промежуточные результаты были минимальны: маленькую таблицу раздавать через broadcast/hash, большие — через sort-merge, фильтровать до джойна и следить за фан-аутом (размножением строк) и случайными кросс-джойнами.

Подробно:

1. Порядок джойнов — сначала самые селективные соединения и фильтры, чтобы поздние джойны работали на меньших объёмах.
2. Broadcast (map-side) join — если одна сторона мала, её копию рассылают на все ноды и джойнят локально, без шафла большой таблицы.
3. Shuffle sort-merge / hash — когда обе стороны большие: данные шафлятся по ключу. Дороже из-за перекладки по сети.
4. Фан-аут — джойн по неуникальному ключу размножает строки (1→N) и раздувает последующие агрегаты; кардинальность ключа нужно проверять.

-- Broadcast маленького справочника (Spark SQL)
SELECT /*+ BROADCAST(d) */ f.order_id, f.amount, d.name
FROM fact_sales f
JOIN dim_product d ON d.id = f.product_id;

-- Фан-аут: если dim_product не уникален по id, строки размножатся,
-- и SUM(amount) завысит выручку. Измерение дедуплицируют заранее.

⚠️ Частая ошибка: забыть условие соединения (или задать всегда истинное) — получается декартово произведение; и агрегировать поверх фан-аут-джойна, незаметно завышая суммы.

Короткий ответ: Партиционирование раскладывает таблицу по каталогам/файлам на основе колонки (обычно даты). WHERE по этой колонке позволяет прочитать только подходящие партиции (partition pruning), а предикатный пушдаун опускает фильтр к сканеру/формату файла, отсекая лишние блоки ещё до чтения.

Подробно:

1. Обрезка партиций — план выбирает лишь партиции, удовлетворяющие предикату; остальные каталоги/файлы не открываются вовсе.
2. Предикатный пушдаун — фильтр применяется на уровне файла: по min/max статистике row-group в Parquet не подходящие группы строк пропускаются (data skipping).
3. Условие работы — предикат должен стоять на сырой колонке партиционирования, без обёрток-функций.
4. Гранулярность — слишком мелкие партиции (по часам за годы) плодят миллионы мелких файлов; слишком крупные дают слабую обрезку.

-- events партиционирована по event_date
SELECT count(*)
FROM events
WHERE event_date BETWEEN DATE '2026-06-01' AND DATE '2026-06-07';
-- читаются 7 партиций из тысяч

-- Анти-паттерн: функция на ключе -> обрезка не срабатывает
-- WHERE year(event_date) = 2026;   -- сканирует всё

⚠️ Частая ошибка: фильтровать по производному выражению от ключа партиционирования (CAST, date_trunc, year()) — обрезка отключается и движок читает всю таблицу.

Короткий ответ: Два идиоматичных подхода. MERGE (апсерт) сопоставляет источник и приёмник по ключу и обновляет или вставляет строки; INSERT OVERWRITE PARTITION полностью перезаписывает затронутые партиции пересчитанными данными. Оба идемпотентны — повторный прогон даёт тот же результат.

Подробно:

1. MERGE / апсерт — годится для точечных изменений и медленно меняющихся измерений (SCD); нужен надёжный ключ и, для дедупа, выбор последней версии строки.
2. INSERT OVERWRITE партиции — проще и надёжнее для больших пересчётов по дням: удаляем и пишем партицию целиком, без состояния «наполовину».
3. Идемпотентность — залог безопасных ретраев пайплайна: повтор не плодит дубликаты.
4. Выбор — MERGE при малой доле изменений; OVERWRITE при полном пересчёте окна дат.

-- Апсерт по ключу
MERGE INTO dim_customer t
USING staging_customer s ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET name = s.name, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (customer_id, name, updated_at)
  VALUES (s.customer_id, s.name, s.updated_at);

-- Идемпотентная перезапись партиции за день
INSERT OVERWRITE TABLE fact_daily PARTITION (dt = DATE '2026-06-30')
SELECT user_id, sum(amount) AS revenue
FROM raw_events
WHERE event_date = DATE '2026-06-30'
GROUP BY user_id;

⚠️ Частая ошибка: делать инкремент через INSERT INTO без ключа/перезаписи — при ретрае строки дублируются; либо MERGE по недедуплицированному источнику (несколько строк на ключ) обновляет непредсказуемо — источник надо заранее свести к одной строке на ключ (ROW_NUMBER).

Короткий ответ: Точный COUNT(DISTINCT) требует держать все уникальные значения и делать глобальную дедупликацию с шафлом — тяжело по памяти и плохо масштабируется на миллиарды строк. Приближённый счёт на HyperLogLog (APPROX_COUNT_DISTINCT) даёт ~1–2% погрешности при фиксированной, крошечной памяти.

Подробно:

1. Цена точного distinct — данные шафлятся по значению, чтобы посчитать уникальные глобально; на высокой кардинальности это дорого и часто упирается в память.
2. HyperLogLog — вероятностная структура: константная память (килобайты) на любой объём, ошибка порядка 2%. Идеален для дашбордов «уникальные пользователи».
3. Предагрегация — считать метрики в промежуточной таблице по дням, а поверх агрегировать; HLL-скетчи ещё и складываются (мержатся) между периодами без пересчёта с нуля.
4. Когда точность обязательна — биллинг, финансы, аудит: только точный COUNT(DISTINCT).

-- Дорого: точная глобальная дедупликация
SELECT count(DISTINCT user_id)
FROM events WHERE event_date = DATE '2026-06-30';

-- Дёшево и масштабируемо: HyperLogLog, ~2% погрешности
SELECT approx_count_distinct(user_id) AS uniq_users
FROM events
WHERE event_date = DATE '2026-06-30';

⚠️ Частая ошибка: гонять точный COUNT(DISTINCT) по миллиардам строк ради дашборда, где 2% погрешности неважны — запрос падает по памяти или тянется минутами.

Короткий ответ: OLTP обслуживает точечные запросы (найти/обновить несколько строк), где B-tree-индекс даёт быстрый seek. Аналитические колоночные склады сканируют миллионы строк по немногим колонкам, поэтому точечные индексы бесполезны — данные физически упорядочивают партициями и кластеризацией, а zone maps (min/max по блокам) отсекают ненужные блоки при скане.

Подробно:

1. OLTP — селективные запросы по ключу; B-tree ускоряет seek и обеспечивает уникальность/внешние ключи.
2. Warehouse — запросы читают колонки целиком по большим диапазонам; выгоднее меньше сканировать, чем «прыгать» по индексу на каждую строку.
3. Механизмы склада — партиционирование (обрезка), кластеризация/сортировка данных, zone maps / min-max статистика на блок для пропуска блоков (data skipping).

⚠️ Частая ошибка: пытаться «навесить индекс» в колоночном складе на медленный аналитический запрос — правильный рычаг это ключ партиционирования/кластеризации, а не вторичный B-tree-индекс.

Короткий ответ: ROW_NUMBER() OVER (PARTITION BY ключ ORDER BY версия) нумерует строки внутри группы; фильтр rn = 1 оставляет одну — самую свежую. Оконная функция делает это за один проход с сортировкой, тогда как эквивалент через self-join + агрегат требует лишнего соединения и часто фан-аута.

Подробно:

1. Дедуп — PARTITION BY по бизнес-ключу, ORDER BY по времени убыв.; берём rn = 1 (выбор последней версии).
2. Скользящие агрегаты — running sum/avg через оконную рамку без самоджойна.
3. Цена окна — требует шафла по PARTITION BY и сортировки внутри партиции; тяжёлые части — сортировка и перекос по ключу.
4. Окно против джойна — заменяя коррелированный подзапрос или self-join окном, убираем повторное чтение таблицы и риск размножения строк.

-- Оставить последнюю запись на пользователя
WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id
                            ORDER BY updated_at DESC) AS rn
  FROM staging_users
)
SELECT * FROM ranked WHERE rn = 1;

⚠️ Частая ошибка: ставить фильтр rn = 1 в WHERE того же SELECT, где вычисляется ROW_NUMBER — оконные функции нельзя фильтровать в WHERE; нужен подзапрос/CTE или QUALIFY там, где он поддерживается.

Короткий ответ: Перекос (skew) — когда несколько ключей содержат непропорционально много строк, и при шафле по ключу одна нода-редьюсер получает гигантскую партицию, а остальные простаивают. Лечат солёнием (salting): к горячему ключу добавляют случайный суффикс, разбивая его на N подключей, а затем доагрегируют.

Подробно:

1. Симптом — джойн или группировка, где 99% задач завершились, а одна «висит» — классический признак перекоса.
2. Солёние (salting) — горячий ключ разбивают на key || '_' || rand(0..N), размазывая нагрузку по N задачам; финальный этап суммирует частичные результаты.
3. Отдельная обработка горячих ключей — известные тяжёлые ключи (NULL, «guest», топ-клиент) джойнят или агрегируют отдельно, остальное — обычным путём.
4. Broadcast — если перекошенную сторону джойна можно раздать целиком, шафл и перекос исчезают.

-- Соление горячего ключа при агрегации
SELECT user_id, sum(cnt) AS total
FROM (
  SELECT user_id,
         concat(user_id, '_', cast(floor(rand() * 16) AS int)) AS salted,
         count(*) AS cnt
  FROM events
  GROUP BY user_id, salted        -- нагрузка размазана по 16 группам
) t
GROUP BY user_id;                  -- доагрегация частичных сумм

⚠️ Частая ошибка: искать «медленный запрос» в целом, когда виснет одна задача из-за одного ключа (часто NULL в ключе джойна) — сначала смотрят распределение по ключу, а не наращивают память кластера.

Короткий ответ: Стоимостной оптимизатор (CBO) перебирает варианты плана и выбирает самый дешёвый по оценке, опираясь на статистику таблиц: число строк, кардинальность колонок, гистограммы, размер. Если статистика устарела, оценки строк неверны — оптимизатор выбирает не тот метод джойна или порядок, и план деградирует. Поэтому после больших загрузок запускают ANALYZE.

Подробно:

1. Что оценивает CBO — селективность предикатов и кардинальность промежуточных результатов из собранной статистики.
2. Почему план плохой — недооценил строки → выбрал Nested Loop или broadcast вместо hash-джойна; переоценил → лишний шафл и сортировка.
3. Признак — в EXPLAIN ANALYZE оценка rows= резко расходится с actual rows.
4. Лечение — регулярный ANALYZE / сбор статистики, особенно после массовой вставки или перезаписи партиций; хинты — крайняя мера, а не замена статистике.

-- Обновить статистику после загрузки
ANALYZE events;                     -- PostgreSQL
-- ANALYZE TABLE events COMPUTE STATISTICS FOR ALL COLUMNS;  -- Spark SQL

-- Диагностика: оценка против факта
EXPLAIN ANALYZE SELECT ... ;
-- rows=1000  (actual rows=2500000)   <- статистика устарела

⚠️ Частая ошибка: лечить симптом хинтами вместо обновления статистики; или забыть пересчитать статистику после INSERT OVERWRITE больших партиций — план остаётся построенным на «старом» размере таблицы.