Spec-Zone .ru
спецификации, руководства, описания, API
|
Определенная оптимизация применима к сравнениям, которые используют IN
оператор,
чтобы протестировать результаты подзапроса (или то использование =ANY
, который
эквивалентен). Этот раздел обсуждает эту оптимизацию, особенно относительно проблем это NULL
существующие значения. Последняя часть обсуждения включает предложения на том, что можно сделать, чтобы помочь
оптимизатору.
Рассмотрите следующее сравнение подзапроса:
outer_expr
IN (SELECTinner_expr
FROM ... WHEREsubquery_where
)
MySQL оценивает запросы "снаружи к внутренней части." Таким образом, Это сначала получает значение
внешнего выражения outer_expr
, и затем выполняет подзапрос и получает
строки, которые он производит.
Очень полезная оптимизация должна "сообщить"
подзапросу, что единственные строки интереса - те где внутреннее выражение inner_expr
равно outer_expr
. Это делается, отталкивая соответствующее равенство в
подзапрос WHERE
пункт. Таким образом, сравнение преобразовывается в это:
EXISTS (SELECT 1 FROM ... WHEREsubquery_where
ANDouter_expr
=inner_expr
)
После преобразования MySQL может использовать вниз продвинутое равенство, чтобы ограничить число строк, которые это должно исследовать, оценивая подзапрос.
Более широко, сравнение N
значения к подзапросу, который возвращается
N
- строки значения подвергается тому же самому преобразованию. Если
oe_i
и ie_i
представьте
соответствующие внешние и внутренние значения выражения, это сравнение подзапроса:
(oe_1
, ...,oe_N
) IN (SELECTie_1
, ...,ie_N
FROM ... WHEREsubquery_where
)
Становится:
EXISTS (SELECT 1 FROM ... WHEREsubquery_where
ANDoe_1
=ie_1
AND ... ANDoe_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 ... WHEREsubquery_where
AND (outer_expr
=inner_expr
ORinner_expr
IS NULL))
Потребность оценить дополнительное IS
NULL
условие состоит в том, почему MySQL имеет ref_or_null
метод доступа:
mysql>EXPLAIN
->SELECT
->outer_expr
IN (SELECT t2.maybe_null_keyFROM 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
*************************** 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>outer_expr
IN (SELECT maybe_null_key FROM t2) FROM t1\GSHOW 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 (SELECTinner_expr
FROM ... WHEREsubquery_where
)
Необходимо выполнить оригинал SELECT
здесь, без любых вниз продвинутых равенств вида, упомянутого ранее.
С другой стороны, когда outer_expr
не NULL
, абсолютно важно что это сравнение:
outer_expr
IN (SELECTinner_expr
FROM ... WHEREsubquery_where
)
будьте преобразованы в это выражение, которое использует вниз продвинутое условие:
EXISTS (SELECT 1 FROM ... WHEREsubquery_where
ANDouter_expr
=inner_expr
)
Без этого преобразования подзапросы будут медленными. Чтобы решить дилемму того, оттолкнуть ли или не оттолкнуть условия в подзапрос, условия обертываются в "триггерные" функции. Таким образом, выражение следующей формы:
outer_expr
IN (SELECTinner_expr
FROM ... WHEREsubquery_where
)
преобразовывается в:
EXISTS (SELECT 1 FROM ... WHEREsubquery_where
AND trigcond(outer_expr
=inner_expr
))
Более широко, если сравнение подзапроса основано на нескольких парах внешних и внутренних выражений, преобразование берет это сравнение:
(oe_1
, ...,oe_N
) IN (SELECTie_1
, ...,ie_N
FROM ... WHEREsubquery_where
)
и преобразовывает это в это выражение:
EXISTS (SELECT 1 FROM ... WHEREsubquery_where
AND trigcond(oe_1
=ie_1
) AND ... AND trigcond(oe_N
=ie_N
) )
Каждый trigcond(
специальная функция,
которая оценивает к следующим значениям:X
)
X
когда "соединенное" внешнее выражение oe_i
не NULL
TRUE
когда "соединенное"
внешнее выражение oe_i
NULL
Отметьте, что триггерные функции не являются триггерами вида, с которым
Вы создаете CREATE TRIGGER
.
Равенства, которые обертываются в trigcond()
функции не являются первыми
предикатами class для оптимизатора запросов. Большинство оптимизации не может иметь дело с предикатами, которые
могут быть включены и выключены во время выполнения запроса, таким образом, они принимают любого trigcond(
быть неизвестной функцией и
проигнорировать это. В настоящее время инициированные равенства могут использоваться той оптимизацией:X
)
Ссылочная оптимизация: trigcond(
может использоваться, чтобы создать X
=Y
[OR Y
IS NULL])ref
, eq_ref
, или
ref_or_null
табличные доступы.
Индексируйте основанные на поиске механизмы выполнения подзапроса: trigcond(
может использоваться, чтобы создать X
=Y
)unique_subquery
или index_subquery
доступы.
Генератор табличного условия: Если подзапрос будет соединением нескольких таблиц, то инициированное условие будет проверено как можно скорее.
Когда оптимизатор использует инициированное условие создать некоторый индексировать основанный на поиске доступ
(что касается первых двух элементов предыдущего списка), у этого должна быть стратегия нейтрализации случая,
когда условие выключается. Эта стратегия нейтрализации всегда является тем же самым: Сделайте полное
сканирование таблицы. В 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
но никогда фактически, нет никакой потери
производительности.
Чтобы помочь оптимизатору запросов лучше выполнить Ваши запросы, используйте эти подсказки:
Объявите столбец как NOT NULL
если это действительно.
(Это также помогает другим аспектам оптимизатора, упрощая тестирование условия на столбец.)
Если Вы не должны отличить a NULL
от FALSE
подзапросите результат, можно легко избежать медленного пути
выполнения. Замените сравнение, которое похоже на это:
outer_expr
IN (SELECTinner_expr
FROM ...)
с этим выражением:
(outer_expr
IS NOT NULL) AND (outer_expr
IN (SELECTinner_expr
FROM ...))
Затем NULL IN (SELECT ...)
никогда не будет оцениваться, потому что
MySQL прекращает оценивать AND
части, как только результат выражения является четким.
subquery_materialization_cost_based
включает управлению выбором между
материализацией подзапроса и IN -> EXISTS
преобразование подзапроса. См. Раздел 8.8.5.2, "Управляя Переключаемой
Оптимизацией".