Mysql深度讲解 – 子查询优化

前言

上一篇【Mysql深度讲解 – Join语句】详细说了Join的相关内容，本篇会讲一下子查询如何优化。一般来说在正常的业务情况下大多数sql语句都会有子查询，一个表从另一个表里查询数据，本篇会通过几个例子列举这样的sql语句的优化思路。更多Mysql调优内容请点击【Mysql优化-深度讲解系列目录】。

子查询优化的思路

Mysql中对于子查询的优化其实会尽量优化为Join语句执行，下面这两个sql都含有子查询，大体上子查询可以分为两种。

select * from t1 where a in (select a from t2); -- 第一种，写在where后面作为一个条件
select * from (select * from t1) as t; -- 第二种，写在from后买你作为一个表

按照返回类结果区分

标量子查询
那些只返回一个单一值的子查询称之为标量子查询。比如：
select * from t1 where a in (select max(a) from t2);

行子查询
返回一条记录的子查询，不过这条记录可能包含多个列。比如：
select * from t1 where (a, b) = (select a, b from t2 limit 1);

列子查询
返回一个列的数据的子查询，包含多行记录。比如：
select * from t1 where a in (select a from t2);

表子查询
子查询的结果既包含很多条记录，又包含很多个列。比如：
select * from t1 where (a, b) in (select a,b from t2);

按照外层查询关系区分

相关子查询
如果子查询的执行需要依赖于外层查询的值或者字段，我们就可以把这个子查询称之为相关子查询。比如：
select * from t1 where a in (select a from t2 where t1.a = t2.a);

不相关子查询
如果子查询可以单独运行出结果，而不依赖于外层查询的值或者字段，我们就可以把这个子查询称之为不相关子查询。前边介绍的那些子查询全部都可以看作不相关子查询。

子查询的执行过程

对于相关标量子查询或者行子查询，比如：
select * from t1 where b = (select b from t2 where t1.a = t2.a limit 1);
它的执行步骤是

1. 先从外层查询中获取一条记录，本例中也就是先从t1表中获取一条记录。
2. 然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，本例就是从t1表中获取的那条记录中找 出t1.a列的值，然后执行子查询。
3. 最后根据子查询的结果来检测外层查询where子句的条件是否成立，如果成立，就把外层查询的那 条记录加入到结果集，否则就丢弃。
4. 再次执行第一步，获取第二条外层查询中的记录，依次类推。。。

对于不相关标量子查询或者行子查询，比如：
select * from t1 where a = (select a from t2 limit 1);
它的执行步骤是：

1. 执行 select a from t2 limit 1 这个子查询。
2. 然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询 select * from t1 where a = …;

IN子查询

对于标量查询，一般来说由于数量被限制了，Mysql执行的相对比较快。但是对于不是标量的查询，Mysql就必须做大量的工作去计算查询成本。比如： select * from t1 where a in (select a from t2);
对于不相关的IN子查询来说，如果子查询的结果集中的记录条数很少，那么把子查询和外层查询分别看成两个单 独的单表查询效率还是蛮高的，但是如果单独执行子查询后的结果集太多的话，就会导致这些问题：

1. 结果集太多，可能内存中都放不下。
2. 对于外层查询来说，如果子查询的结果集太多，那就意味着IN子句中的参数特别多，这会导致无法有效的使用索引，只能对外层查询进行全表扫描。此外在对外层查询执行全表扫描时，由于IN子句中的参数太多，这会导致检测一条记录 是否符合和IN子句中的参数匹配花费的时间太长。

物化表

在mysql中，不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里。这个将子查询结果集中的记录保存到临时表的过程称之为物化Materialize。那个存储子查询结果集的临时表称之为物化表。正因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有B+树索引），通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。写入临时表的过程是这样的：

1. 该临时表的列就是子查询结果集中的列。
2. 写入临时表的记录会被去重。IN语句是判断某个操作数在不在某个集合中，集合中的值重不重复对 整个IN语句的结果并不影响，所以我们在将结果集写入临时表时对记录进行去重可以让临时表变得 更小。临时表也是个表，只要为表中记录的所有列建立主键或者唯一索引就可以进行去重。
3. 一般情况下子查询结果集不会特别大，所以会为它建立基于内存的使用Memory存储引擎的临时 表，而且会为该表建立哈希索引。IN语句的本质就是判断某个操作数在不在某个集合里，如果集合 中的数据建立了哈希索引，那么这个匹配的过程就是很快的。
4. 如果子查询的结果集非常大，超过了系统变量tmp_table_size或者max_heap_table_size，临时表 会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。

分析SQL

还是对于上面的那个sql：select * from t1 where a in (select a from t2);
当我们把子查询进行物化之后，假设子查询物化表的名称为temp_table，该物化表存储的子查询结果集的列为temp_a，再此过程中还包含给列temp_a去重的步骤，那么这个查询其实可以从下边两种角度来看待：

• 从表t1的角度来看待，整个查询的意思其实是：对于t1表中的每条记录来说，如果该记录的a列的 值在子查询对应的物化表中，则该记录会被加入最终的结果集。
• 从子查询物化表的角度来看待，整个查询的意思其实是：对于子查询物化表的每个值来说，如果能 在t1表中找到对应的a列的值与该值相等的记录，那么就把这些记录加入到最终的结果集。

也就是说其实上边的查询就相当于表t1和子查询物化表temp_table进行内连接：
select * from t1 inner join temp_table on t1.a = temp_table. temp_a;
转化成内连接之后，查询优化器就可以评估不同连接顺序需要的成本是多少，选取成本最低的那种查询方式执行查询。

半连接(semi-join)

虽然将子查询进行物化之后再执行查询会提高一些效率，但是建立临时表也是需要成本的，如果可以将子查询直接转换为join岂不是更有效率。那可否绕过物化操作直接把子查询转换为连接呢，对此我们对比下这两个sql：

select * from t1 where a in (select a from t2);
select t1.* from t1 inner join t2 on t1.a = t2.a;

如果说a是两个表的主键，或者仅仅是t2表的主键，那么就不需要对某个列进行去重，建立物化表就很得不偿失。因为理论上来说两个sql的结果很像，只不过第二个sql没有做去重，所以IN子查询和两表连接之间并不完全等价。但是为了充分发挥查询优化器的作用，Mysql会对第二个sql进行半连接(semi-join)。

将t1表和t2表进行半连接的意思就是：对于t1表的某条记录来说，我们只关心在t2表中是否存在 与之匹配的记录是否存在，而不关心具体有多少条记录与之匹配，最终的结果集中只保留t1表的记录。要注意的是semi-join 只属于MySQL内部采用的一种执行子查询的方式，MySQL并没有提供面向用户的semi-join语法 。所以我们可以理解为Mysql为了优化，做了这样一个事情：

select * from t1 where a in (select a from t2);	-- 原sql
select t1.* from t1 semi join t2 on t1.a = t2.a;	-- Mysql执行时优化的结果

半连接的内部实现

为了实现半连接Mysql也会根据不同的情况进行不同的处理，简而言之就是去重。

Table Pullout(子表上拉)
这种属于不要去重的情况，当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表上拉到外层查询的FROM子句中， 并把子查询中的搜索条件合并到外层查询的搜索条件中。比如：select * from t1 where a in (select a from t2 where t2.b = 1); -- a是主键可以直接把t2表上拉到外层查询的FROM子句中，并且把子查询中的搜索条件合并到外层查询的搜索条件 中，上拉之后的查询就是这样的：select * from t1 inner join t2 on t1.a = t2.a where t2.b = 1;

DuplicateWeedout Execution Strategy(重复值消除)
对于这种情况就是我们上面说的建立temp表的方法，凡是重复的值直接丢掉即可，只保留不重复的值。

FirstMath Execution Strategy(首次匹配)
这种办法是取外层表的一条数据，然后到子表中查询，找到的第一条符合条件的就放到结果集当中，并且停止匹配更多的记录。如果没有找到则抛弃当前外层记录，继续拿下一条外层记录去子表里查找第一条符合的记录，循环往复。

LooseScan(松散扫描)
这种方法回去扫描索引，当扫描到第一个可以匹配的索引的时候，就返回到结果集中。

半连接的适用条件

对于某些使用IN语句的相关相关子查询，比方这个查询：

select * from t1 where a in (select b from t2 where t1.b = t2.b);
//可以转换为半连接，然后再使用上面介绍的几种semi-join实现方式来进行实现。
select * from t1 semi join t2 on t1.a = t2.a and t1.b = t2.b;  -- 再次提醒: semi join这样的语法是不存在的。

但是注意：由于相关子查询并不是一个独立的查询，所以不能转换为物化表来执行查询。 不是所有包含IN子查询的查询语句都可以转换为semi-join，只有形如下面这样的查询才可以被转换为semi-join：

SELECT ... FROM outer_tables WHERE expr IN (SELECT ... FROM inner_tables ...) AND ...
//或者
SELECT ... FROM outer_tables WHERE (oe1, oe2, ...) IN (SELECT ie1, ie2, ... FROM inner_tables ...) AND ...

如一下几种情况就不能转换为semi-join：

• 外层查询的WHERE条件中有其他搜索条件与IN子查询组成的布尔表达式使用OR连接起来
• 使用NOT IN而不是IN的情况
• 子查询中包含GROUP BY、HAVING或者聚集函数的情况
• 子查询中包含UNION的情况

那么对于不能转为semi-join查询的子查询，有其他方式来进行优化。对于不相关子查询来说，可以尝试把它们物化之后再参与查询。比如对于使用了NOT IN下面这个sql:

select * from t1 where a not in (select a from t2 where t2.a = 1);

请注意这里将子查询物化之后不能转为和外层查询的表的连接，因为用的是not in只能是先扫描t1表，然后对t1表 的某条记录来说，判断该记录的a值在不在物化表中。不管子查询是相关的还是不相关的，都可以把IN子查询尝试专为EXISTS子查询。其实对于任意一个IN子查询来说，都可以被转为EXISTS子查询，通用的例子如下：

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
//可以被转换为：
EXISTS (SELECT inner_expr FROM ... WHERE subquery_where AND outer_expr=inner_expr)

这样转换的好处是，转换前本来不能用到索引，但是转换后可能就能用到索引了，比如：

select * from t1 where a in (select a from t2 where t2.e = t1.e);
//这个sql里面的子查询时用不到索引的，转换后变为：
select * from t1 where exists (select 1 from t2 where t2.e = t1.e and t1.a = t2.a);

转换之后t2表就能用到a字段的索引了。所以，如果IN子查询不满足转换为semi-join的条件，又不能转换为物化表或者转换为物化表的成本太大，那 么它就会被转换为EXISTS查询。

总结

本篇简述了Mysql中的子查询优化思路，以及Mysql查询优化器针对sql语句中子查询的优化原理。本篇中多数的内容其实都是通过查询优化器和explain命令查看的，查询优化器已经再以往的博客中详细说过了，因此下一篇【Mysql深度讲解 – explain关键字（一）】会详细说下explain关键字的内容。