Кратко: запрос
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status='active')
может быть выполнен разными планами; выбор зависит от статистики, индексов и оптимизатора. Ниже — возможные планы, оценки затрат, рекомендации по индексам/схеме и риски.
1) Возможные планы выполнения
- Nested loop с index lookup (для каждого ряда orders делается поиск по customers):
- когда есть индекс по customers.id или по customers(id) + фильтр по status поддерживается отдельно.
- Стоимость приблизительно $O(N_o\cdot \log N_c)$ (или $O(N_o)$ при хеш/балансном дереве/индексе), где $N_o$ — число строк в orders, $N_c$ — в customers.
- Hash semi-join / materialize подзапрос:
- сначала выбираются id активных клиентов ($N_a$), строится хеш-таблица; затем probe по orders.customer_id.
- Стоимость приближённо $O(N_a+N_o)$. Эффективен, если $N_a$ и $N_o$ помещаются в память.
- Merge join:
- если обе таблицы отсортированы/индексированы по ключу (orders.customer_id и customers.id), можно выполнить сортово-сливающий (merge) semi-join. Стоимость $O(N_o+N_a)$ с дополнительной стоимостью сортировки, если нужна.
- Полный скан customers на каждую строку orders (недопустимо медленно):
- если нет индексов и оптимизатор не материализует подзапрос — худший случай $O(N_o\cdot N_c)$.
2) Какие индексы ускорят запрос
- Обязательное: индекс/PK по customers.id (обычно уже есть).
- Индекс по orders.customer_id (внешний ключ) — ускоряет выборку полей orders и заменяет full scan orders при других условиях.
- Индекс по customers.status или составной индекс по $(status,id)$:
- если селективность status хорошая — индекс по $status$ позволяет быстро получить набор активных id;
- ещё лучше составной индекс $(status,id)$ (или $(status)INCLUDE(id)$ в SQL Server) — позволяет выбрать id без дополнительного доступа к строке.
- Покрывающий индекс для orders (например, индекс по customer_id с INCLUDED колонками, если DB поддерживает) уменьшит I/O при выдаче всех столбцов.
- Для merge-join полезны индексированные/сортированные по ключу колонны в обеих таблицах.
3) Изменения в схеме и настройках, которые помогут
- Объявить customers.id PRIMARY KEY/NOT NULL (устраняет проблемы с NULL и дубликатами).
- Поддерживать точную статистику (ANALYZE/UPDATE STATISTICS) — оптимизатор выберет лучший план.
- Увеличить рабочую память для хеш-таблиц (например, work_mem в PostgreSQL), чтобы избежать спиллинга на диск.
- Рассмотреть партицирование customers по status или orders по customer_id, если данные большие и часто фильтруются по status.
- Введение materialized view / кэшированного набора активных customer_id, если статус меняется редко и запросы частые.
4) Риски и нюансы связанных с подзапросом IN
- NULL: если подзапрос может вернуть NULL, семантика IN сложнее — наличие NULL и отсутствие совпадений даёт результат UNKNOWN (т.е. строка не отбирается). Для id (PK NOT NULL) это обычно не проблема.
- Коррелированные подзапросы: если подзапрос был коррелированным, возможны многократные вычисления (пер-строчно) и большой overhead. В нашем примере подзапрос не коррелирован.
- Большой набор результатов подзапроса: материализация/хеширование большого числа id может потребовать много памяти/диска и замедлить запрос.
- Дупликаты в подзапросе не влияют на семантику, но могут повлиять на планы (оптимизатор может выполнять deduplicate/sort).
- Разные СУБД по-разному оптимизируют IN: некоторые не переписывают в semi-join, и тогда проще/быстрее переписать запрос в JOIN/EXISTS вручную.
- Семантика JOIN vs IN: прямой INNER JOIN эквивалентен только при гарантированном уникальном customers.id; иначе JOIN может дублировать строки orders (повтор каждой matching customer).
5) Практические рекомендации
- Убедиться, что customers.id — PRIMARY KEY (NOT NULL), создать индекс $(status,id)$ если status селективен.
- Создать индекс на orders(customer_id).
- Проверить план выполнения (EXPLAIN/EXPLAIN ANALYZE). Если оптимизатор выбирает дорогостоящий nested loop — принудительно проверить hash join или переписать:
- Рекомендуемые эквиваленты:
- JOIN: SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.status='active'
- EXISTS: SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.status='active')
- Контролировать память для хеш-join и обновлять статистику.
- Если набор активных клиентов редко меняется и большой, подумать о кэшировании (materialized view) и обновлении по расписанию.
Если нужно, могу привести пример конкретных индексов для вашей СУБД (PostgreSQL / MySQL / SQL Server) и объяснить ожидаемый план EXPLAIN для реальных объёмов.