mysql 5.6 子查询_mysql5.6子查询的优化

The semi-join FirstMatch strategy executes a subquery very similar to how the IN-TO-EXISTS strategy familiar from earlier versions of MySQL works: for each matching row in the outer table, check for a match in the inner table. When a match is found, return the row from the outer table, otherwise continue scanning the inner table until reaching the end.

Here is a query that is processed using FirstMatch strategy:

SELECT * FROM nation

WHERE n_nationkey IN (SELECT c_nationkey FROM customer);

Notice the FirstMatch(nation) indication in explain output, which means that after a match has been found in table customer, the query executor goes back to scan more rows in table nation:

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+----------+------+---------------+---------------+---------+-------------------------+------+---------------------------------+

| 1 | PRIMARY | nation | ALL | PRIMARY | NULL | NULL | NULL | 25 | |

| 1 | PRIMARY | customer | ref | i_c_nationkey | i_c_nationkey | 5 | dbt3.nation.n_nationkey | 1115 | Using index; FirstMatch(nation) |

Here is the result from IN-TO-EXISTS transformation, which can still be enabled by setting optimizer_switch flags:

set optimizer_switch='semijoin=off,materialization=off' :

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+--------------------+----------+----------------+---------------+---------------+---------+------+------+-------------+

| 1 | PRIMARY | nation | ALL | NULL | NULL | NULL | NULL | 25 | Using where |

| 2 | DEPENDENT SUBQUERY | customer | index_subquery | i_c_nationkey | i_c_nationkey | 5 | func | 1115 | Using index |

As this is basically the same strategy that MySQL 5.5 would choose, there is usually no speedup to gain when FirstMatch is chosen. The advantage may however come when FirstMatch is not the most efficient strategy, or when the cost-based optimizer chooses a more efficient table order. The optimizer can determine a better table order, because it will estimate a realistic cost for a semi-join operation. This contrasts MySQL 5.5 where the placement of a subquery in the query plan was strictly rule-based.