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

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

Сегодня разбираем один из самых коварных способов убить базу — плохие 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 и получить полезный гайд.

ссылка на оригинал статьи https://habr.com/ru/articles/920004/