Як оптимізатор MySQL виконує підзапити з IN і EXISTS?

У старих версіях MySQL підзапит у WHERE ... IN (SELECT ...) часто виконувався як корельований - для кожного рядка зовнішньої таблиці. Звідси міф «підзапити в MySQL повільні, завжди пишіть JOIN». Сучасний оптимізатор перетворює їх на ефективні плани.

Semi-join - для IN і EXISTS: «рядки зовнішньої таблиці, для яких є хоч один збіг». На відміну від звичайного JOIN, не розмножує рядки при кількох збігах.

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

Стратегії semi-join, з яких оптимізатор обирає за вартістю:

• Table pullout - підзапит перетворюється на звичайний JOIN, якщо збіг гарантовано один (наприклад, за первинним ключем).
• FirstMatch - при обході зупинитися на першому збігу.
• LooseScan - пройти індекс підзапиту, беручи лише перше значення кожної групи.
• Materialization - обчислити підзапит один раз у тимчасову таблицю з індексом і шукати в ній.
• Duplicate Weedout - виконати як звичайний JOIN, а дублікати прибрати потім.

Antijoin (MySQL 8.0.17+) - для NOT IN, NOT EXISTS: «рядки, для яких збігів немає». Раніше такі запити часто виконувалися значно гірше.

Як побачити, що сталося:

EXPLAIN FORMAT=TREE
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

У плані видно Semi-join, Materialize чи Nested loop semijoin. Після EXPLAIN (звичайного) SHOW WARNINGS показує, на який запит оптимізатор переписав оригінал.

Коли оптимізація не спрацьовує:

• підзапит з LIMIT, UNION, агрегатами в певних формах;
• підзапит у UPDATE/DELETE з тієї ж таблиці;
• NOT IN з колонкою, що може бути NULL (семантика NULL заважає antijoin - і результат може бути порожнім).

Керування - підказки оптимізатору (/*+ SEMIJOIN(FIRSTMATCH) */, NO_SEMIJOIN) і optimizer_switch. Але це крайній захід: спершу перевірити індекси й статистику (ANALYZE TABLE).

Докладніше в документації: Semi-join і antijoin