Mysql查询优化器之关于⼦查询的优化
下⾯这些sql都含有⼦查询：
mysql> lect * from t1 where a in (lect a from t2);
mysql> lect * from (lect * from t1) as t;
按返回的结果集区分⼦查询
1、标量⼦查询
那些只返回⼀个单⼀值的⼦查询称之为标量⼦查询。⽐如：
lect * from t1 where a in (lect max(a) from t2);
2、⾏⼦查询
返回⼀条记录的⼦查询，不过这条记录需要包含多个列。⽐如：
lect * from t1 where (a, b) = (lect a, b from t2 limit 1);
3、列⼦查询
返回⼀个列的数据的⼦查询，包含多条记录。⽐如:
lect * from t1 where a in (lect a from t2);
4、表⼦查询
⼦查询的结果既包含很多条记录，⼜包含很多个列。⽐如：
lect * from t1 where (a, b) in (lect a,b from t2);
按与外层查询关系来区分⼦查询
1、相关⼦查询
如果⼦查询的执⾏需要依赖于外层查询的值，我们就可以把这个⼦查询称之为相关⼦查询。⽐如：
lect * from t1 where a in (lect a from t2 where t1.a = t2.a);
2、不相关⼦查询
如果⼦查询可以单独运⾏出结果，⽽不依赖于外层查询的值，我们就可以把这个⼦查询称之为不相关⼦查询。前边介绍的那些⼦查询全部都可以看作不相关⼦查。
⼦查询在MySQL中是怎么执⾏的
1、对于不相关标量⼦查询或者⾏⼦查询
⽐如：lect * from t1 where a = (lect a from t2 limit 1);
它的执⾏步骤是：
1）执⾏lect a from t2 limit 1这个⼦查询。
2）然后在将上⼀步⼦查询得到的结果当作外层查询的参数再执⾏外层查询lect * from t1 where a = …;
春天的家乡2、对于相关标量⼦查询或者⾏⼦查询
⽐如：lect * from t1 where b = (lect b from t2 where t1.a = t2.a limit 1);
它的执⾏步骤是：公安面试题
1）先从外层查询中获取⼀条记录，本例中也就是先从t1表中获取⼀条记录。
2）然后从上⼀步骤中获取的那条记录中找出⼦查询中涉及到的值，本例中就是从t1表中获取的那条记录中找出t1.a列的值，然后执⾏⼦查询。
3）最后根据⼦查询的查询结果来检测外层查询WHERE⼦句的条件是否成⽴，如果成⽴，就把外层查询的那条记录加⼊到结果集，否则就丢弃。
4）再次执⾏第⼀步，获取第⼆条外层查询中的记录，依次类推。。。
3、IN⼦查询优化
mysql对IN⼦查询进⾏了优化。
⽐如：lect * from t1 where a in (lect a from t2);
对于不相关的IN⼦查询来说，如果⼦查询的结果集中的记录条数很少，那么把⼦查询和外层查询分别看成两个单独的单表查询效率还是蛮⾼的，但是如果单独执⾏⼦查询后的结果集太多的话，就会导致
这些问题：
• 结果集太多，可能内存中都放不下
• 对于外层查询来说，如果⼦查询的结果集太多，那就意味着IN⼦句中的参数特别多，这会导致：
• ⽆法有效的使⽤索引，只能对外层查询进⾏全表扫描。
• 在对外层查询执⾏全表扫描时，由于IN⼦句中的参数太多，这会导致检测⼀条记录是否符合和IN⼦句中的参数匹配花费的时间太长
在mysql中，不直接将不相关⼦查询的结果集当作外层查询的参数，⽽是将该结果集写⼊⼀个临时表⾥。写⼊临时表的过程是这样的：
1）该临时表的列就是⼦查询结果集中的列。
2）写⼊临时表的记录会被去重。IN语句是判断某个操作数在不在某个集合中，集合中的值重不重复对整个IN语句的结果并不影响，所以我们在将结果集写⼊临时表时对记录进⾏去重可以让临时表变得更
⼩。临时表也是个表，只要为表中记录的所有列建⽴主键或者唯⼀索引就可以进⾏去重。
3）⼀般情况下⼦查询结果集不会特别⼤，所以会为它建⽴基于内存的使⽤Memory存储引擎的临时表，⽽且会为该表建⽴哈希索引。IN语句的本质就是判断某个操作数在不在某个集合⾥，如果集合中的
数据建⽴了哈希索引，那么这个匹配的过程就是很快的。
4）如果⼦查询的结果集⾮常⼤，超过了系统变量tmp_table_size或者max_heap_table_size，临时表会转⽽使⽤基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。
这个将⼦查询结果集中的记录保存到临时表的过程称之为物化（Materialize）。那个存储⼦查询结果集的临时表称之为物化表。正因为物化表中的记录都建⽴了索引（基于内存的物化表有哈希索引，基于磁盘的有B+树索引），通过索引执⾏IN语句判断某个操作数在不在⼦查询结果集中变得⾮常快，从⽽提升了⼦查询语句的性能。
还是对于上⾯的那个sql：
mysql> lect * from t1 where a in (lect a from t2);
当我们把⼦查询进⾏物化之后，假设⼦查询物化表的名称为materialized_table，该物化表存储的⼦查询结果集的列为m_val，那么这个查询其实可以从下边两种⾓度来看待：
• 从表t1的⾓度来看待，整个查询的意思其实是：对于t1表中的每条记录来说，如果该记录的a列的值在⼦查询对应的物化表中，则该记录会被加⼊最终的结果集。
• 从⼦查询物化表的⾓度来看待，整个查询的意思其实是：对于⼦查询物化表的每个值来说，如果能在t1表中找到对应的a列的值与该值相等的记录，那么就把这些记录加⼊到最终的结果集。
也就是说其实上边的查询就相当于表t1和⼦查询物化表materialized_table进⾏内连接：
lect * from t1 inner join materialized_table on t1.a = m_val;
转化成内连接之后，查询优化器就可以评估不同连接顺序需要的成本是多少，选取成本最低的那种查询⽅式执⾏查询。
虽然将⼦查询进⾏物化之后再执⾏查询会有建⽴临时表的成本，但是可以将⼦查询转换为JOIN还是会更有效率⼀点的。那能不能不进⾏物化操作直接把⼦查询转换为连接呢。
我们对⽐下⾯两个sql：
lect * from t1 where a in (lect a from t2);
黄月季花语lect t1.* from t1 inner join t2 on t1.a = t2.a;
这两个sql的查询结果其实很像，只是说对于第⼆个sql的结果集没有去重，所以IN⼦查询和两表连接之间并不完全等价，但是将⼦查询转换为连接⼜真的可以充分发挥优化器的作⽤，所以MySQL提出了⼀个新概念半连接（mi-join），将t1表和t2表进⾏半连接的意思就是：对于t1表的某条记录来说，我们只关⼼在t2表中是否存在与之匹配的记录是否存在，⽽不关⼼具体有多少条记录与之匹配，最终的结果集中只保留t1表的记录。mi-join只是在MySQL内部采⽤的⼀种执⾏⼦查询的⽅式，MySQL并没有提供⾯向⽤户的mi-join语法。
那么怎么实现mi-join呢？
（1）Table pullout （⼦查询中的表上拉）
当⼦查询的查询列表处只有主键或者唯⼀索引列时，可以直接把⼦查询中的表上拉到外层查询的FROM⼦句中，并把⼦查询中的搜索条件合并到外层查询的搜索条件中。
⽐如：lect * from t1 where a in (lect a from t2 where t2.b = 1); – a是主键
我们可以直接把t2表上拉到外层查询的FROM⼦句中，并且把⼦查询中的搜索条件合并到外层查询的搜索条件中，上拉之后的查询就是这样的：
lect * from t1 inner join t2 on t1.a = t2.a where t2.b = 1; -– a是主键
（2）DuplicateWeedout execution strategy （重复值消除）
对于这个查询来说：
lect * from t1 where a in (lect e from t2 where t2.b = 1); – e只是⼀个普通字段
转换为半连接查询后，t1表中的某条记录可能在t2表中有多条匹配的记录，所以该条记录可能多次被添加到最后的结果集中，为了消除重复，我们可以建⽴⼀个临时表，⽐⽅说这个临时表长这样：
CREATE TABLE tmp (
id PRIMARY KEY
针头简笔画);
这样在执⾏连接查询的过程中，每当某条t1表中的记录要加⼊结果集时，就⾸先把这条记录的主键值加⼊到这个临时表⾥，如果添加成功，说明之前这条t1表中的记录并没有加⼊最终的结果集，现在把该记录添加到最终的结果集；如果添加失败，说明这条之前这条t1表中的记录已经加⼊过最终的结果集，这⾥直接把它丢弃就好了，这种使⽤临时表消除mi-join结果集中的重复值的⽅式称之为DuplicateWeedout。
（3）FirstMatch execution strategy （⾸次匹配）
FirstMatch是⼀种最原始的半连接执⾏⽅式，就是我们最开始的思路，先取⼀条外层查询的中的记录，然后到⼦查询的表中寻找符合匹配条件的记录，如果能找到⼀条，则将该外层查询的记录放⼊最终的结果集并且停⽌查找更多匹配的记录，如果找不到则把该外层查询的记录丢弃掉；然后再开始取下⼀条外层查询中的记录，重复上边这个过程。
（4）LooScan（松散索引扫描）
⼦查询扫描了⾮唯⼀索引，因为是⾮唯⼀索引，所以可能有相同的值，可以利⽤索引去重。
对于某些使⽤IN语句的相关⼦查询，⽐⽅这个查询：
lect * from t1 where a in (lect b from t2 where t1.b = t2.b);
它可以转换为半连接：
lect * from t1 mi join t2 on t1.a = t2.a and t1.b = t2.b;
如⼀下⼏种情况就不能转换为mi-join：
• 外层查询的WHERE条件中有其他搜索条件与IN⼦查询组成的布尔表达式使⽤OR连接起来
• 使⽤NOT IN⽽不是IN的情况
• ⼦查询中包含GROUP BY、HAVING或者聚集函数的情况
• ⼦查询中包含UNION的情况
那么对于不能转为mi-join查询的⼦查询，有其他⽅式来进⾏优化：
• 对于不相关⼦查询来说，可以尝试把它们物化之后再参与查询
吉林大学新民校区
⽐如对于使⽤了NOT IN下⾯这个sql:
lect * from t1 where a not in (lect 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)公司收购
加减混合运算这样转换的好处是，转换前本来不能⽤到索引，但是转换后可能就能⽤到索引了，⽐如：
lect * from t1 where a in (lect a from t2 where t2.e = t1.e);
济南博物馆这个sql⾥⾯的⼦查询时⽤不到索引的，转换后变为：
lect * from t1 where exists (lect 1 from t2 where t2.e = t1.e and t1.a = t2.a)
转换之后t2表就能⽤到a字段的索引了。
所以，如果IN⼦查询不满⾜转换为mi-join的条件，⼜不能转换为物化表或者转换为物化表的成本太⼤，那么它就会被转换为EXISTS查询。
对于派⽣表的优化
lect * from (lect a, b from t1) as t;
上⾯这个sql，⼦查询是放在from后⾯的，这个⼦查询的结果相当于⼀个派⽣表，表的名称是t，有a，b两个字段。
对于派⽣表，有两种执⾏⽅式：
（⼀）把派⽣表物化
我们可以将派⽣表的结果集写到⼀个内部的临时表中，然后就把这个物化表当作普通表⼀样参与查询。当然，在对派⽣表进⾏物化时，使⽤了⼀种称为延迟物化的策略，也就是在查询中真正使⽤到派⽣表时才回去尝试物化派⽣表，⽽不是还没开始执⾏查询就把派⽣表物化掉。⽐如：
lect * from (lect * from t1 where a = 1) as derived1 inner join t2 on derived1.a = t2.a where t2.a =10;
如果采⽤物化派⽣表的⽅式来执⾏这个查询的话，那么执⾏时⾸先会到t1表中找出满⾜t1.a = 10的记录，如果找不到，说明参与连接的t1表记录就是空的，所以整个查询的结果集就是空的，所以也就没有必要去物化查询中的派⽣表了。
（⼆）将派⽣表和外层的表合并，也就是将查询重写为没有派⽣表的形式
⽐如下⾯这个sql:
lect * from (lect * from t1 where a = 1) as t;
和下⾯的sql是等价的：
lect * from t1 where a = 1;
再看⼀些复杂⼀点的sql：
lect * from (lect * from t1 where a = 1) as t inner join t2 on t.a = t2.a where t2.b = 1;
我们可以将派⽣表与外层查询的表合并，然后将派⽣表中的搜索条件放到外层查询的搜索条件中，就像下⾯这样：
lect * from t1 inner join t2 on t1.a = t2.a where t1.a = 1 and t2.b = 1;
这样通过将外层查询和派⽣表合并的⽅式成功的消除了派⽣表，也就意味着我们没必要再付出创建和访问临时表的成本了。可是并不是所有带有派⽣表的查询都能被成功的和外层查询合并，当派⽣表中有这些语句就不可以和外层查询合并：
聚集函数，⽐如MAX()、MIN()、SUM()啥的
DISTINCT
GROUP BY
HAVING
LIMIT
UNION 或者 UNION ALL
派⽣表对应的⼦查询的SELECT⼦句中含有另⼀个⼦查询
所以MySQL在执⾏带有派⽣表的时候，优先尝试把派⽣表和外层查询合并掉，如果不⾏的话，再把派⽣表物化掉执⾏查询。
