Реальные примеры оптимизации SQL: IN против JOIN на базе Битрикс

Контекст

Пример взят из реального PHP‑кода Dinamique CMS (файл вроде cms/core/MetaData/MetaData.inc.php): на большом каталоге выборка «значений полей по видимым элементам раздела» выполнялась десятки секунд. На проекте с 1С‑Битрикс вы не увидите тех же имён таблиц, но тот же антипаттерн — WHERE id IN (SELECT … DISTINCT …) — встречается в кастомных отчётах, обменах и сырых SQL к своим таблицам рядом с ядром.

Что было медленно

Внутренний подзапрос строит множество mark для условия по родителю и видимости; внешний запрос ищет строки в wc_field_joins, чьи id попадают в это множество. Дублирующийся SELECT DISTINCT внутри IN лишний: уникальность списка для полуоткрытого IN (…) не требуется отдельным DISTINCT на уровне SQL — а оптимизатор MySQL может обрабатывать такую конструкцию через временную таблицу или повторные проходы, особенно если на внутренних полях нет удачного индекса.

-- Исходный стиль запроса (имена таблиц из примера; время — порядка ~40 с на данных автора)
SELECT DISTINCT value
FROM wc_field_joins AS fj
WHERE fj.id IN (
    SELECT DISTINCT mark
    FROM wc_ddata_catalogItem
    WHERE parent = '40'
      AND visible = '1'
);

Что сработало быстрее

Эквивалент по смыслу — соединить справочник полей с каталожной строкой по условию совпадения mark и fj.id, а фильтры по разделу и видимости перенести в предикаты соединения или в WHERE. Тогда оптимизатор может идти от узкой выборки каталога с индексом по parent/visible и подхватывать join‑ключ, вместо того чтобы сначала материализовать громоздкий список для IN.

-- Переписывание через JOIN (в заметке оригинала время стало пренебрежимо малым при той же схеме)
SELECT DISTINCT fj.value
FROM wc_field_joins AS fj
INNER JOIN wc_ddata_catalogItem AS ci
    ON ci.mark = fj.id
   AND ci.parent = '40'
   AND ci.visible = '1';

Важно: «0 секунд» в старой записи — это относительный эффект на конкретном датасете и кеше; на своей копии базы всегда проверяйте EXPLAIN ANALYZE (или EXPLAIN в соответствующей версии) и время при холодном буфере.

Перенос на мысленный эксперимент с Битрикс

В ядре магазинов и инфоблоков запросы генерируются ORM и компонентами, но там, где вы пишете свой SQL к b_iblock_element_property, заказам или HL‑блокам, полезно помнить:

• Согласованные типы в условии соединения (INT к INT, одинаковая сортировка строк) — иначе индекс не используется.
• Индекс под реальный фильтр (например, IBLOCK_ID, SECTION_ID, флаги активности) часто важнее формы IN vs JOIN.
• Иногда быстрее окажется два шага: один компактный SELECT id во временную таблицу или в PHP‑массив, второй — … WHERE id IN (…) с коротким списком — это нужно мерить, а не выбирать по привычке.

Итог

Замена зависимого подзапроса с IN (SELECT …) на явный JOIN с теми же предикатами — базовый приём. В продакшене он не гарантирует выигрыш в каждой версии MySQL и каждой статистике, но в кейсах вроде описанного CMS он как раз и убрал узкое место; для Битрикс‑проекта смысл тот же: держать план под контролем и не раздувать внутренний IN без необходимости.