http://note.youdao.com/yws/public/redirect/share?id=50f2c387a5d6c9bc816c4b5282cf410e&type=false 1
一概述
MySQL子查询优化的技术或优化策略，包括三种，分别为：
1 semi-join：半连接优化技术，本质上是把子查询上拉到父查询中，与父查询的表做join/semi-join的操作。关键词是“上拉”。
2 Materialization：物化子查询，子查询的结果通常缓存在内存或临时表中
3 EXISTS strategy：把半连接转换为EXISTS操作。本质上是把父表的条件下推到子查询中关键词是“下推”。
|
子查询格式 |
可选的优化策略 |
|
IN/=ANY |
semi-join， Materialization，EXISTS strategy |
|
NOT IN/<>ALL |
Materialization， EXISTS strategy |
二 semi join
1 什么是半连接？
semi join，半连接操作，是关系代数规定的扩展操作符之一。对于“R semi-join S”其语义为：连接后的结果中，只包括R与S在公共属性上的交集所限定的R中的部分元组。
anti join，反半连接，语义与半连接相反。即“R semi-join S”相当于“S anti-join R”。
2 为什么要用半连接优化子查询？
对于子查询，其子查询部分相对于父表的每个符合条件的元组，都要把子查询执行一轮。效率低下。用半连接操作优化子查询，是把子查询上拉到父查询中，这样子查询的表和父查询中的表是并列关系，父表的每个符合条件的元组，只需要在子表中找符合条件的元组即可，不需要“父表的每个符合条件的元组，都要把子查询执行一轮”，所以效率提高。
这种优化方式，称为“上拉/扁平化”。
3 半连接的优化策略
MySQL提供5种优化策略，来进一步优化半连接操作，分别是：
3.1 DUPS_WEEDOUT/重复剔除：执行普通的两表内连接操作，用临时表缓存结果，在临时表中在所查询的列上（a in subquery，MySQL自动在临时表的a列上建立主键）通过主键去除重复的元组。在执行计划中，可以看到“Start temporary/End temporary”。
参考资料：
3.2 LOOSE_SCAN/松散扫描：在执行连接的时候，半连接的表S（R semi-join S）其元组需要有序（a in select b from t，b上存在索引，其元组的顺序按照b成分组状，则使用b上可用的索引读取元组的时候，可以按序引序把相同的值的元组有序读到），此时，根据索引拿出每组重复元组中的第一个元组（其他重复元组被读到后跳过，所以要求S的元组有序），与R表进行连接。
参考资料：
3.3 FIRST_MATCH/首次匹配：两表做普通的内连接，连接后的结果，存于临时表，在每次保存到临时表前，在临时表中检查是否有相同值的元组存在，不存在则保存。
参考资料：
3.4 MATERIALIZE_LOOKUP/索引式物化：把子查询的结果物化到临时表，执行连接的时候，可以用临时表的索引（MySQL自动为临时表创建索引）完成连接操作。这种情况下，完成连接的时候，通常被物化后的子查询的结果是连接操作的内表，这样才便于使用索引快速定位内表的元组。
3.5 MATERIALIZE_SCAN/扫描式物化：类似上一个。只是临时表的索引不能辅助加快连接，只能通过全表扫描的方式，扫描临时表中的元组，来完成半连接操作。这种情况下，完成连接的时候，通常被物化后的子查询的结果是连接操作的外表，所以需要全表扫描。注意，两种物化方式要求子查询是“非相关的子查询”，这样其结果才稳定不变可被物化（内存化/缓存化）。
参考资料：
这5种策略的选择，是通过代价估算的方式，来挑出其中最优的策略。
需要注意的是：
3.6半连接操作对于表达“半”含义的表，具有“存在即可”的含义，如果有多个元组符合连接条件，则不能让每个符合条件的元组都与外表进行连接，所以，对于半连接的表即一个内表来说，就需要把满足条件的重复元组去掉或使用索引等方式进行“只选择一个”式的操作。
3.7 如果需要更进一步学习，可以参考代码中相关函数：
advance_sj_state()
semijoin_firstmatch_loosescan_access_paths()
semijoin_loosescan_fill_driving_table_position()
setup_semijoin_dups_elimination()
等等。
3.8 注意：半连接的这5种子优化策略，需要通过代价估算完成最优选择。
4 MySQL对什么样的子查询支持使用半连接进行优化？
子查询语句必须满足：
子查询语句必须同时满足（如果其中有一个不满足，则采用EXISTS策略优化子查询--这一点，就是SEMI-JOIN策略和EXISTS策略之间的区别。即据此知道对于子查询，MySQL是如何决定使用哪种优化策略的）：
4.1 谓词必须是：IN/=ANY（不可以是NOT IN）
4.2 子查询必须是一个简单子查询，不能包括：UNION/GROUP BY/HAVING/聚集函数。如果包含有ORDER BY则不可以带有LIMIT子句。
4.3 表的总数（外表和内表之和）不能超过61（MySQL支持的最多可连接的表的个数）。
4.4 子查询位于WHERE/JOIN-ON子句中，且首层不存在OR/NOT操作（即首层的条件子句中只能是AND操作符连接的表达式。如果与OR操作在同层的子查询不可以被半连接优化，但可以被“物化策略”优化）。
4.5 查询块中不可以包括：STRAIGHT_JOIN（与子查询同层的连接子句中不可以包括STRAIGHT_JOIN）。
4.6 半连接参数必须打开(set optimizer_switch='semijoin=on';)，否则采用EXISTS策略优化子查询。
4.7 不是UPDATE/DELETE命令（在UPDATE/DELETE命令子查询不被半连接优化）。
4.8 子查询语句不能是无表子句（如子查询形如“select 1”是不能被半连接优化的）。父查询语句也不能是无表子句。
其他：
4.9 子查询是相关子查询或不相关子查询均可。
4.10 可以带有DISTINCT/LIMIT子句，但LIMIT不可以和ORDER BY合用。
5 半连接进一步优化为内连接
MySQL支持把子查询优化为半连接, 还支持把优化后的半连接进一步优化为内连接。优化的条件，是子查询的目标列，使用了主键或唯一键。例如，子查询中的目标列是K2表的主键列：
CREATE TABLE K1 (pk1 INT PRIMARY KEY, a1 INT);
CREATE TABLE K2 (pk2 INT PRIMARY KEY, a2 INT);
INSERT INTO K1 VALUES (1,1), (2,2), (3,null);
INSERT INTO K2 VALUES (1,1), (2,2), (3,null);
SELECT * FROM K1 WHERE a1 IN (SELECT pk2 FROM K2);
mysql> EXPLAIN SELECT * FROM K1 WHERE a1 IN (SELECT pk2 FROM K2);
+----+-------------+-------+------------+--------+---------------+---------+---------+---------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------+------+----------+-------------+
| 1 | SIMPLE | K1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
| 1 | SIMPLE | K2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | m.K1.a1 | 1 | 100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------
| Level | Code | Message|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------
| Note | 1003 | /* select#1 */ select `m`.`k1`.`pk1` AS `pk1`,`m`.`k1`.`a1` AS `a1` from `m`.`k2` join `m`.`k1` where (`m`.`k2`.`pk2` = `m`.`k1`.`a1`) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
这个示例中，MySQL首先把子查询优化为半连接（semi join），然后，调用pull_out_semijoin_tables()函数把半连接优化为内连接。其支持的形式为：
... WHERE oe IN (SELECT it1.primary_key WHERE p(it1, it2) ... )
谓词p，只能是内连接。
三 物化策略 1 什么是物化策略？ 如果子查询执行一次即可以得到结果,即子查询的结果是稳定的,则这样的子查询可以被缓存起来，多次使用。缓存即是物化。缓存到内存中，如果内存中放不下，则会写外存。在MySQL中，这个缓存对应的是临时表（即：物化利用了临时表的机制）。 相关子查询依赖于父查询，结果不确定，所以能被物化的，一定是“非相关子查询”。 2 物化策略与半连接中的物化子策略有什么不同吗？ 物化策略，outside-in materialization，取个名字叫“外化”。此策略只用于“非相关子查询”。这相当于把子查询执行一次，然后缓存结果供多次使用。 而半连接中的物化子策略，尽管也是只可以用于“非相关子查询”，但优化后，查询语句执行的是实实在在的内连接或半连接操作（如果子查询的目标列存在唯一索引，则可以进一步把半连接优化为内连接操作，如前所述）。 如果子查询不能被优化为半连接，则形如下式的（子查询位于OR操作中），可以使用物化策略优化（主要在于OR操作，但非OR的IN子查询，也可能被物化，但属于半连接操作的物化）。 SELECT ... FROM ... WHERE (expr1, ..., exprN) [NOT] IN (SELECT ... ) OR expr; 四 EXISTS策略 1 什么是EXISTS策略？ EXISTS策略，首先对应于SEMI-JOIN策略。其主要表述的，是下推的含义。下推什么呢？下推父查询的条件部分到子查询的条件部分。其形式如下： 初始格式： outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where) 下推后的格式： EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr) 这样做的好处，是起到当子查询不可消除的时候、用父查询的条件在子查询中起到限制子查询元组个数的作用。 2 EXISTS策略的限制？ 并不是所有的子查询，都可以使用EXISTS策略，因为下推条件会带来一些问题。这个问题就是对NULL值的处理。 满足如下条件的，可以用下推来优化子查询： 2.1 outer_expr和inner_expr都不可以有NULL值，且 2.2 SQL语句的语义，并不需要从子查询的返回结果（下推后的子查询的返回值，只能是FALSE或TRUE）为FALSE的情况下区分NULL值。 如果不能全部满足上述2个条件，MySQL会“下推”父查询的条件到子查询，但下推后的格式变化为： 情况一： outer_expr不可为NULL EXISTS (SELECT 1 FROM ... WHERE subquery_where AND (outer_expr=inner_expr OR inner_expr IS NULL)) 情况二：outer_expr可为NULL，不可以下推。
五 进一步挖掘
1 MySQL仅仅是用半连接/物化/EXISTS策略来优化子查询的吗？
Convert the subquery to a join, or use table pullout and run the query as an inner join between subquery tables and outer tables. Table pullout pulls a table out from the subquery to the outer query.
这表明MySQL是可以把子查询优化为连接操作的（这是一种特例：把半连接进一步优化为连接，需要子查询的目标列为唯一键，如outer_expr IN (SELECT field_primary_key...)）。
2 三种优化策略之间的关系
2.1 默认使用半连接进行优化，可以使用“set optimizer_switch='semijoin=off';”命令关闭半连接策略。
2.2 如果半连接参数关闭,则可以使用物化策略或EXISTS策略,使用哪种,由decide_subquery_strategy()函数决定，此函数调用compare_costs_of_subquery_strategies()函数进行两种策略的代价计算，决定选用代价小的优化策略。可以使用“set optimizer_switch='materialization=off'; ”命令关闭物化策略，进而指示MySQL优化器使用EXISTS策略。
3 如何区分是使用了哪种优化策略？
|
策略名称 |
查询执行计划显示项 |
值 |
说明 |
|
半连接策略 |
SHOW WARNINGS命令显示内容 |
semi join |
（子查询的select列为主键或者唯一索引） |
|
物化策略 |
select_type |
SUBQUERY/SIMPLE |
（子查询的select列有空值） |
|
SHOW WARNINGS命令显示内容 |
<materialize>(query fragment) |
非半连接的物化，即物化策略中的物化 |
|
|
select_type |
MATERIALIZED |
被物化策略物化的子查询（子查询的select列没有空值） |
|
|
EXISTS策略 |
select_type |
DEPENDENT SUBQUERY |
DEPENDENT说明了依赖的关系，EXISTS策略把子查询变为了相关子查询 |