badcasedaily1 16 часов назад

Плохие JOIN’ы: приемы, которые (нечаянно) кладут прод

Простой

5 мин

9.1K

Блог компании OTUSSQL*Программирование*PostgreSQL*MySQL*

Обзор

Привет, Хабр!

Сегодня разбираем один из самых коварных способов убить базу — плохие JOIN'ы. Казалось бы, простое дело: связать пару таблиц — и вперёд. Но если в ON засунуть LOWER(email), забыть про индексы или перепутать LEFT JOIN с INNER — сервер мигом начнет дышать на ладан.

В каждой секции:

Пример, который бьет по продакшену.
Что именно ломается.
Конкретный рефакторинг + индексы + что логировать.
Когда все‑таки можно нарушать и не стыдиться.

Все примеры — PostgreSQL, но подавляющее большинство выводов одинаково валит MySQL и SQL Server.

Cartesian product без ON: «SELECT * FROM users, payments»

SELECT  u.id, p.amount
FROM    users u, payments p;      -- упс

На тестовом кластере: users — 1 млн строк, payments — 2 млн. Итог: 2 000 000 000 rows в hash join»е, 9 GB temp на диске. Бру‑таль‑но.

Оптимизатор не нашёл условиях соединения и сделал чистый CROSS JOIN, а мы даже не заметили: в старом ANSI-89 синтаксисе запятая — это именно он. Результат растёт мультипликативно.

Всегда пишем явный JOIN … ON, даже если кажется, что «и так понятно». В PostgreSQL ≥ 15 можно включить standard_conforming_strings и FROM … JOIN, чтобы linters ловили запятую‑JOIN. Если действительно нужен декартов продукт — пишите CROSS JOIN и ставьте комментарий почему.

JOIN по функции (LOWER(email))

SELECT  u.id, s.id
FROM    users u
JOIN    subscriptions s
        ON LOWER(u.email) = LOWER(s.email);

По плану — Seq Scan на обеих таблицах, 12 с вместо 40 мс.

Любая функция в ON скрывает колонку за черным ящиком — оптимизатор не может воспользоваться индексом и идёт в full scan.

Заводим functional index:

CREATE INDEX CONCURRENTLY idx_users_email_lower ON users (LOWER(email));

и такой же на subscriptions. Если база не умеет функциональные индексы (Hello, старый MySQL) — держим нормализованное поле email_lower и триггер на обновление. Валидируем данные заранее (email → lowercase) и соединяемся голыми колонками.

LEFT JOIN + WHERE column IS NOT NULL: превращение в INNER JOIN

SELECT  o.id, r.id
FROM    orders o
LEFT JOIN refunds r ON r.order_id = o.id
WHERE   r.id IS NOT NULL;         -- Моментально «съедает» все NULL'ы

Если хотели сохранить строки без возврата — увы, они исчезли; индекс на orders тоже не нужен, потому что фильтр идёт ПОСЛЕ join»а.

Фильтр WHERE r.id IS NOT NULL убирает все строки, где refunds не нашелся — получается обычный INNER JOIN, только с лишним шагом. План раздут, время — вдвое хуже.

Пишите прямо INNER JOIN (оптимизатор отблагодарит). Если правда нужен LEFT JOIN, переносим фильтр в ON:

LEFT JOIN refunds r 
       ON r.order_id = o.id
      AND r.processed = TRUE;

Это сохраняет «левую» часть.

Несогласованные типы (INT vs TEXT)

SELECT  c.id, o.id
FROM    customers  c         -- id VARCHAR
JOIN    orders     o         -- customer_id INT
        ON c.id = o.customer_id;

20 млн строк, но план упорно читает orders по PK, а customers — seq scan: индекс не пригодился.

Сначала СУБД приводит младший тип к старшему по precedence; здесь — orders.customer_id::text. Индекс на orders.customer_id игнорируется. В MS SQL эта же проблема вызывает «implicit conversion» warning.

Приводим оба поля к одному типу в схеме, а не в запросе. В крайнем случае создаём computed/virtual column и индексируем её. CI‑проверка: simple script, который ищет :: или CAST( внутри JOIN … ON.

OR в ON-условии: оптимизатор разводит руками

SELECT  *
FROM    payments p
JOIN    invoices i
      ON (i.id = p.invoice_id OR i.external_id = p.invoice_external_id);

План показывает два seq scan + nested loop, даже при индексах.

OR делает условие неконъюнктивным; оценка селективности падает, индексы часто игнорируются.

Поэтому разбиваем на UNION ALL двух запросов, где каждое условие — в отдельном JOIN. Или ставим partial indexes и используем UNION. В PostgreSQL иногда помогает ENABLE_SEQSCAN = off для теста — если видите 10х ускорение, знаете, где копать.

Не-саргабельные выражения (DATE(created_at) = …)

SELECT  *
FROM    logs l
JOIN    users u ON u.id = l.user_id
WHERE   DATE(l.created_at) = CURRENT_DATE;   -- ах да, надо же «только за сегодня»

Каждый вызов DATE() ‑ того же порядка, что FULL SCAN: 6 сек вместо 50 мс.

Функция над колонкой — тот же чёрный ящик, что и в кейсе #2: индекс теряется, plan — seq scan на 14 ГБ.

Переписываем диапазоном

WHERE l.created_at >= CURRENT_DATE
  AND l.created_at <  CURRENT_DATE + INTERVAL '1 day';

Ставим expression index CREATE INDEX … (DATE(created_at)) — но тогда успокаиваем DBA и объясняем, почему так надо.

Join без индексов: «слепой merge-join»

SELECT  *
FROM      big_a a
JOIN      big_b b ON b.a_id = a.id;

С обеих сторон full scan + sort + merge, время растёт O(n log n).

Без b‑tree под ключом a_id оптимизатор вынужден сортировать оба результата. В MySQL EXPLAIN покажет type: ALL = full scan.

Фиксим:

CREATE INDEX CONCURRENTLY idx_big_b_a_id ON big_b (a_id);

Не забываем про analyze, иначе статистика старая и PG продолжит seq scan потому что думает, что это дёшево.

CROSS APPLY / LATERAL как цикл «for each row»

SELECT  u.id,
        l.last_login
FROM    users u
CROSS APPLY (
   SELECT  last_login
   FROM    logins
   WHERE   user_id = u.id
   ORDER BY created_at DESC
   LIMIT 1
) l;

Для 500 k пользователей — 500 k * (индекс‑lookup + sort), latency — минуты.

CROSS APPLY (SQL Server) или LATERAL (Postgres) запускает подзапрос для каждой строки исходной таблицы. Если внутри сортировка/агрегация — получаем N раз.

Реписываем на оконные функции:

SELECT DISTINCT ON (u.id)
       u.id,
       l.last_login
FROM   users u
JOIN   logins l ON l.user_id = u.id
ORDER  BY u.id, l.created_at DESC;

Или агрегация + join:

WITH last AS (
    SELECT user_id, MAX(created_at) AS last_login
    FROM   logins
    GROUP  BY user_id
)
SELECT u.id, last.last_login
FROM   users u
LEFT JOIN last ON last.user_id = u.id;

JOIN к материализованному виду-чудовищу

SELECT  *
FROM    orders o
JOIN    sales_report_monthly m
          ON m.customer_id = o.customer_id;

sales_report_monthly — это SELECT … GROUP BY month, 150 GB, no indexes.

Часто выносят логику в view или CTE, а потом соединяют без ограничений. Оптимизатор разворачивает view как есть, и join происходит поверх огромного, уже агрегированного датасета.

Материализуем результат (Materialized View) + индекс. Или инлайн‑агрегация: считаем GROUP BY на минимальном подмножестве, потом join. Не боимся временных таблиц, они часто дешевле многократного пересчёта.

Fan-out join: many-to-many + отсутствие DISTINCT

SELECT  p.id, t.tag
FROM    products p
JOIN    products_tags pt ON pt.product_id = p.id
JOIN    tags t           ON t.id = pt.tag_id;

В семантике это ок, но фронт рассчитывает получить по одному тегу, а вместо этого — тысячи дубликатов, JSON‑ответ пухнет.

Каждый join множит строки; без "UNIQUE" в products_tags или DISTINCT после join получаем лавину. Плюс — order by после join сортирует уже раздутую выборку.

Ставим UNIQUE (product_id, tag_id) — плюс DB сама гарантирует консистентность. На стороне SQL — SELECT p.id, ARRAY_AGG(t.tag) … GROUP BY p.id (Postgres) или JSON_ARRAYAGG в MySQL 8.

В тяжелых случаях — денормализованное поле «tags jsonb» + триггер на обновление.

Вывод

JOIN — это не просто «склеить таблички». Это контракт между вами и оптимизатором. Нарушаем контракт — платим ресурсами.

Берегите индексы, не прячьте ключи в функциях, проверяйте типы и не бойтесь EXPLAIN ANALYZE.

Если вы сталкиваетесь с проблемами производительности из‑за неэффективных JOIN‑ов, приглашаем вас на открытый урок «SQL: Оконные функции — когда GROUP BY уже не хватает», который пройдет 2 июля в 20:00. Узнайте, как избежать ошибок, которые могут «сломать» ваш продакшн.

Также пройдите вступительное тестирование, чтобы оценить свой уровень знаний SQL и получить полезный гайд.

Хабы: