Movatterモバイル変換

Certain optimizations are applicable to comparisons that use theIN (or=ANY) operator to test subquery results. This section discusses these optimizations, particularly with regard to the challenges thatNULL values present. The last part of the discussion suggests how you can help the optimizer.

Consider the following subquery comparison:

outer_expr IN (SELECTinner_expr FROM ... WHEREsubquery_where)

MySQL evaluates queries“from outside to inside.” That is, it first obtains the value of the outer expressionouter_expr, and then runs the subquery and captures the rows that it produces.

A very useful optimization is to“inform” the subquery that the only rows of interest are those where the inner expressioninner_expr is equal toouter_expr. This is done by pushing down an appropriate equality into the subquery'sWHERE clause to make it more restrictive. The converted comparison looks like this:

EXISTS (SELECT 1 FROM ... WHEREsubquery_where ANDouter_expr=inner_expr)

After the conversion, MySQL can use the pushed-down equality to limit the number of rows it must examine to evaluate the subquery.

More generally, a comparison ofN values to a subquery that returnsN-value rows is subject to the same conversion. Ifoe_i andie_i represent corresponding outer and inner expression values, this subquery comparison:

(oe_1, ...,oe_N) IN  (SELECTie_1, ...,ie_N FROM ... WHEREsubquery_where)

Becomes:

EXISTS (SELECT 1 FROM ... WHEREsubquery_where                          ANDoe_1 =ie_1                          AND ...                          ANDoe_N =ie_N)

For simplicity, the following discussion assumes a single pair of outer and inner expression values.

The“pushdown” strategy just described works if either of these conditions is true:

Suppose thatouter_expr is known to be a non-NULL value but the subquery does not produce a row such thatouter_expr =inner_expr. Thenouter_expr IN (SELECT ...) evaluates as follows:

In this situation, the approach of looking for rows withouter_expr =inner_expr is no longer valid. It is necessary to look for such rows, but if none are found, also look for rows whereinner_expr isNULL. Roughly speaking, the subquery can be converted to something like this:

EXISTS (SELECT 1 FROM ... WHEREsubquery_where AND        (outer_expr=inner_expr ORinner_expr IS NULL))

The need to evaluate the extraIS NULL condition is why MySQL has theref_or_null access method:

mysql> EXPLAIN       SELECTouter_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...

Theunique_subquery andindex_subquery subquery-specific access methods also have“orNULL” variants.

The additionalOR ... IS NULL condition makes query execution slightly more complicated (and some optimizations within the subquery become inapplicable), but generally this is tolerable.

The situation is much worse whenouter_expr can beNULL. According to the SQL interpretation ofNULL as“unknown value,”NULL IN (SELECTinner_expr ...) should evaluate to:

For proper evaluation, it is necessary to be able to check whether theSELECT has produced any rows at all, soouter_expr =inner_expr cannot be pushed down into the subquery. This is a problem because many real world subqueries become very slow unless the equality can be pushed down.

Essentially, there must be different ways to execute the subquery depending on the value ofouter_expr.

The optimizer chooses SQL compliance over speed, so it accounts for the possibility thatouter_expr might beNULL:

To solve the dilemma of whether or not to push down conditions into the subquery, the conditions are wrapped within“trigger” functions. Thus, an expression of the following form:

outer_expr IN (SELECTinner_expr FROM ... WHEREsubquery_where)

Is converted into:

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

More generally, if the subquery comparison is based on several pairs of outer and inner expressions, the conversion takes this comparison:

(oe_1, ...,oe_N) IN (SELECTie_1, ...,ie_N FROM ... WHEREsubquery_where)

And converts it to this expression:

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

Eachtrigcond(X) is a special function that evaluates to the following values:

Equalities that are wrapped withintrigcond() functions are not first class predicates for the query optimizer. Most optimizations cannot deal with predicates that may be turned on and off at query execution time, so they assume anytrigcond(X) to be an unknown function and ignore it. Triggered equalities can be used by those optimizations:

When the optimizer uses a triggered condition to create some kind of index lookup-based access (as for the first two items of the preceding list), it must have a fallback strategy for the case when the condition is turned off. This fallback strategy is always the same: Do a full table scan. InEXPLAIN output, the fallback shows up asFull scan on NULL key in theExtra column:

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

If you runEXPLAIN followed bySHOW WARNINGS, you can see the triggered condition:

*************************** 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`

The use of triggered conditions has some performance implications. ANULL IN (SELECT ...) expression now may cause a full table scan (which is slow) when it previously did not. This is the price paid for correct results (the goal of the trigger-condition strategy is to improve compliance, not speed).

For multiple-table subqueries, execution ofNULL IN (SELECT ...) is particularly slow because the join optimizer does not optimize for the case where the outer expression isNULL. It assumes that subquery evaluations withNULL on the left side are very rare, even if there are statistics that indicate otherwise. On the other hand, if the outer expression might beNULL but never actually is, there is no performance penalty.

To help the query optimizer better execute your queries, use these suggestions:

Thesubquery_materialization_cost_based flag of theoptimizer_switch system variable enables control over the choice between subquery materialization andIN-to-EXISTS subquery transformation. SeeSection 10.9.2, “Switchable Optimizations”.