Преобразование больших предикатов IN в подзапросы

Начиная с MariaDB 10.3, оптимизатор преобразует некоторые большие предикаты IN в подзапросы IN.

То есть, предикат IN в форме

column [NOT] IN (const1, const2, .... )

преобразуется в эквивалентный подзапрос IN:

column [NOT] IN (select ... from temporary_table)

что открывает новые возможности для оптимизатора запросов.

Преобразование происходит, если выполняются следующие условия:

• список IN содержит более 1000 элементов (это можно контролировать через параметр in_predicate_conversion_threshold).
• условие [NOT] IN находится на верхнем уровне в предложении WHERE/ON.

Управление оптимизацией

• Оптимизация включена по умолчанию. MariaDB 10.3.18 (и отладочные сборки до этого) ввели переменную in_predicate_conversion_threshold. Установите значение 0 для отключения оптимизации.

Преимущества оптимизации

Если column является префиксом ключа, оптимизатор MariaDB обработает условие

column [NOT] IN (const1, const2, .... )

попытаясь построить доступ по диапазону. Если список большой, анализ может потребовать много памяти и времени ЦП. Проблема усугубляется, когда column является частью многоколоночного индекса, а запрос содержит условия по другим частям индекса.

Преобразование предикатов IN в подзапросы обходит анализ диапазонов, что означает, что фаза оптимизации запроса будет использовать меньше ЦП и памяти.

Возможные недостатки преобразования:

• Оптимизация может преобразовать доступ по ключу 'элементы списка IN' в табличный сканирование (если нет другого пригодного индекса для таблицы)
• Оценки количества строк, соответствующих IN (...), менее точны.

См. также

• Оператор IN

Ссылки

https://jira.mariadb.org/browse/MDEV-12176