http://blog.163.com/li_hx/blog/static/18399141320150253652874/
一概述
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,只能是内连接。
待续...