Определенная оптимизация применима к сравнениям, которые используют IN оператор, чтобы протестировать результаты подзапроса (или то использование =ANY, который эквивалентен). Этот раздел обсуждает эту оптимизацию, особенно относительно проблем это NULL существующие значения. Последняя часть обсуждения включает предложения на том, что можно сделать, чтобы помочь оптимизатору.

Рассмотрите следующее сравнение подзапроса:

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

MySQL оценивает запросы "снаружи к внутренней части." Таким образом, Это сначала получает значение внешнего выражения outer_expr, и затем выполняет подзапрос и получает строки, которые он производит.

Очень полезная оптимизация должна "сообщить" подзапросу, что единственные строки интереса - те где внутреннее выражение inner_expr равно outer_expr. Это делается, отталкивая соответствующее равенство в подзапрос WHERE пункт. Таким образом, сравнение преобразовывается в это:

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

После преобразования MySQL может использовать вниз продвинутое равенство, чтобы ограничить число строк, которые это должно исследовать, оценивая подзапрос.

Более широко, сравнение N значения к подзапросу, который возвращается N- строки значения подвергается тому же самому преобразованию. Если oe_i и ie_i представьте соответствующие внешние и внутренние значения выражения, это сравнение подзапроса:

(oe_1, ..., oe_N) IN  (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

Становится:

EXISTS (SELECT 1 FROM ... WHERE subquery_where                          AND oe_1 = ie_1                          AND ...                          AND oe_N = ie_N)

Следующее обсуждение принимает единственную пару внешних и внутренних значений выражения для простоты.

У преобразования, только описанного, есть свои ограничения. Это допустимо, только если мы игнорируем возможный NULL значения. Таким образом, "pushdown" работы стратегии пока оба из этих двух условий являются истиной:

Когда или или оба из тех условий не содержат, оптимизация более сложна.

Предположите это outer_expr как известно, не -NULL значение, но подзапрос не производит строку так, что outer_expr = inner_expr. Затем outer_expr IN (SELECT ...) оценивает следующим образом:

В этой ситуации, подходе поиска строк с outer_expr = inner_expr больше не действительно. Необходимо искать такие строки, но если ни один не находится, также ищет строки где inner_expr NULL. Примерно говоря, подзапрос может быть преобразован в:

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND        (outer_expr=inner_expr OR inner_expr IS NULL))

Потребность оценить дополнительное IS NULL условие состоит в том, почему MySQL имеет ref_or_null метод доступа:

mysql> EXPLAIN    -> SELECT outer_expr IN (SELECT
        t2.maybe_null_key    ->                       FROM t2, t3
        WHERE ...)    -> FROM t1;*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: t1...*************************** 2. row ***************************           id: 2  select_type: DEPENDENT SUBQUERY        table: t2         type: ref_or_nullpossible_keys: maybe_null_key          key: maybe_null_key      key_len: 5          ref: func         rows: 2        Extra: Using where; Using index...

unique_subquery и index_subquery специфичные для подзапроса методы доступа также имеют "или NULL"разновидности. Однако, они не видимы в EXPLAIN вывод, таким образом, следует использовать EXPLAIN EXTENDED сопровождаемый SHOW WARNINGS (отметьте checking NULL в предупреждающем сообщении):

mysql> EXPLAIN
        EXTENDED    -> SELECT outer_expr
        IN (SELECT maybe_null_key FROM t2) FROM t1\G*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: t1...*************************** 2. row ***************************           id: 2  select_type: DEPENDENT SUBQUERY        table: t2         type: index_subquerypossible_keys: maybe_null_key          key: maybe_null_key      key_len: 5          ref: func         rows: 2        Extra: Using indexmysql> SHOW WARNINGS\G*************************** 1. row ***************************  Level: Note   Code: 1003Message: select (`test`.`t1`.`outer_expr`,         (((`test`.`t1`.`outer_expr`) in t2 on         maybe_null_key checking NULL))) AS `outer_expr IN (SELECT         maybe_null_key FROM t2)` from `test`.`t1`

Дополнительное OR ... IS NULL условие делает выполнение запроса немного более сложным (и некоторая оптимизация в пределах подзапроса становится неподходящей), но обычно это терпимо.

Ситуация намного хуже когда outer_expr может быть NULL. Согласно интерпретации SQL NULL как "неизвестное значение," NULL IN (SELECT inner_expr ...) должен оценить к:

Для надлежащей оценки необходимо быть в состоянии проверить ли SELECT произвел любые строки вообще, таким образом, outer_expr = inner_expr не может быть оттолкнут в подзапрос. Это - проблема, потому что много подзапросов реального мира становятся очень медленными, если равенство не может быть оттолкнуто.

По существу должны быть различные способы выполнить подзапрос в зависимости от значения outer_expr.

Оптимизатор предпочитает соответствие SQL скорости, таким образом, это учитывает возможность что outer_expr мог бы быть NULL.

Если outer_expr NULL, чтобы оценить следующее выражение, необходимо работать SELECT определить, производит ли это какие-либо строки:

NULL IN (SELECT inner_expr FROM ... WHERE subquery_where)

Необходимо выполнить оригинал SELECT здесь, без любых вниз продвинутых равенств вида, упомянутого ранее.

С другой стороны, когда outer_expr не NULL, абсолютно важно что это сравнение:

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

будьте преобразованы в это выражение, которое использует вниз продвинутое условие:

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

Без этого преобразования подзапросы будут медленными. Чтобы решить дилемму того, оттолкнуть ли или не оттолкнуть условия в подзапрос, условия обертываются в "триггерные" функции. Таким образом, выражение следующей формы:

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

преобразовывается в:

EXISTS (SELECT 1 FROM ... WHERE subquery_where                          AND trigcond(outer_expr=inner_expr))

Более широко, если сравнение подзапроса основано на нескольких парах внешних и внутренних выражений, преобразование берет это сравнение:

(oe_1, ..., oe_N) IN (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

и преобразовывает это в это выражение:

EXISTS (SELECT 1 FROM ... WHERE subquery_where                          AND trigcond(oe_1=ie_1)                          AND ...                          AND trigcond(oe_N=ie_N)       )

Каждый trigcond(X) специальная функция, которая оценивает к следующим значениям:

Отметьте, что триггерные функции не являются триггерами вида, с которым Вы создаете CREATE TRIGGER.

Равенства, которые обертываются в trigcond() функции не являются первыми предикатами class для оптимизатора запросов. Большинство оптимизации не может иметь дело с предикатами, которые могут быть включены и выключены во время выполнения запроса, таким образом, они принимают любого trigcond(X) быть неизвестной функцией и проигнорировать это. В настоящее время инициированные равенства могут использоваться той оптимизацией:

Когда оптимизатор использует инициированное условие создать некоторый индексировать основанный на поиске доступ (что касается первых двух элементов предыдущего списка), у этого должна быть стратегия нейтрализации случая, когда условие выключается. Эта стратегия нейтрализации всегда является тем же самым: Сделайте полное сканирование таблицы. В EXPLAIN вывод, нейтрализация обнаруживается как Full scan on NULL key в Extra столбец:

mysql> EXPLAIN SELECT t1.col1,    -> t1.col1 IN (SELECT t2.key1 FROM t2 WHERE t2.col2=t1.col2) FROM t1\G*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: t1        ...*************************** 2. row ***************************           id: 2  select_type: DEPENDENT SUBQUERY        table: t2         type: index_subquerypossible_keys: key1          key: key1      key_len: 5          ref: func         rows: 2        Extra: Using where; Full scan on NULL key

Если Вы работаете EXPLAIN EXTENDED сопровождаемый SHOW WARNINGS, можно видеть инициированное условие:

*************************** 1. row ***************************  Level: Note   Code: 1003Message: select `test`.`t1`.`col1` AS `col1`,         <in_optimizer>(`test`.`t1`.`col1`,         <exists>(<index_lookup>(<cache>(`test`.`t1`.`col1`) in t2         on key1 checking NULL         where (`test`.`t2`.`col2` = `test`.`t1`.`col2`) having         trigcond(<is_not_null_test>(`test`.`t2`.`key1`))))) AS         `t1.col1 IN (select t2.key1 from t2 where t2.col2=t1.col2)`         from `test`.`t1`

У использования инициированных условий есть некоторые импликации производительности. A NULL IN (SELECT ...) выражение теперь может вызвать полное сканирование таблицы (который является медленным), когда оно ранее не сделало. Это - цена, заплаченная за корректные результаты (цель стратегии триггерного условия состояла в том, чтобы улучшить соответствие и не скорость).

Для многократно-табличных подзапросов, выполнения NULL IN (SELECT ...) будет особенно медленным, потому что оптимизатор соединения не оптимизирует для случая, где внешнее выражение NULL. Это принимает тот подзапрос оценки с NULL на левой стороне очень редки, даже если есть статистические данные, которые указывают иначе. С другой стороны, если внешнее выражение могло бы быть NULL но никогда фактически, нет никакой потери производительности.

Чтобы помочь оптимизатору запросов лучше выполнить Ваши запросы, используйте эти подсказки:

subquery_materialization_cost_based включает управлению выбором между материализацией подзапроса и IN -> EXISTS преобразование подзапроса. См. Раздел 8.8.5.2, "Управляя Переключаемой Оптимизацией".