五、关于子查询的优化
子查询分类
两种写法：
1 | |
按返回结果集区分
1. 标量子查询
只返回一个单一值的子查询称之为标量子查询。
1 | |
2. 行子查询
只返回一条记录的子查询，不过这条记录需要包含多个列。
1 | |
3. 列子查询
返回一个列的数据的子查询，包含多条记录。
1 | |
4. 表子查询
子查询的结果既包含很多条记录，又包含很多个列。
1 | |
按与外层查询关系区分
1. 相关子查询
如果子查询的执行需要依赖于外层查询的值，这个子查询称之为相关子查询。
1 | |
2. 不相关子查询
如果子查询可以单独运行出结果，而不依赖于外层查询的值，这个子查询称之为不相关子查询。上面的几个子查询全部都可以看作不相关子查询。
子查询执行原理
对于 不相关标量子查询 或 行子查询
如：
1 | |
执行步骤：
- 执行
SELECT * FROM t2 LIMIT 1这个子查询。 - 然后再将上一步子查询的结果当做外层查询的参数再执行外层查询
SELECT * FROM t1 WHERE a = ...。
对于 相关标量子查询 或 行子查询
如：
1 | |
执行步骤：
- 先从外层查询中获取一条记录，本例中也就是先从 t1 表中获取一条记录。
- 然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，本例中就是从 t1 表中获取的那条记录中找出 t1.a 列的值，然后执行子查询。
- 最后根据子查询的查询结果来检测外层查询
WHERE子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。 - 再次执行第一步，获取第二条外层查询中的记录，依次类推。
IN 子查询优化
如果子查询中记录条数比较多的情况，如：
1 | |
对于上一步 相关标量子查询 或 行子查询 ，如果子查询的结果集的记录比较少，那么按照上面的方式 把内外查询分别看成两个独立的查询，效率还算比较高。
如果对于包含 IN 语句的子查询，结果集比较多，会导致以下问题：
- 结果集太多，多到内容都放不下。
- 对于外层查询来说，如果子查询的结果集太多，那就意味着
IN子句中的参数特别多，这会导致：- 无法有效的使用索引，只能对外层查询进行全表扫描。
- 在对外层查询执行全表扫描时，由于
IN子句中的参数太多，这会导致检测一条记录是否符合和IN子句中的参数匹配花费的时间太长。
临时表
在 MySQL 中，不直接将 不相关子查询 的结果集当作外层查询的参数，而是将该结果集写入一个 临时表 里。写入临时表的过程是这样的：
- 该临时表的列就是子查询结果集中的列。
- 写入临时表的记录会被去重。
IN语句是判断某个操作数在不在某个集合中，集合中的值重不重复对整个 IN 语句的结果并不影响，所以我们在将结果集写入临时表时对记录进行去重可以让临时表变得更小。
临时表也是个表，只要为表中记录的所有列建立主键或者唯一索引就可以进行去重。 - 一般情况下子查询结果集不会特别大，所以会为它建立基于内存的使用 Memory 存储引擎 的临时表，而且会为该表建立 哈希索引。
IN 语句的本质就是判断某个操作数在不在某个集合里，如果集合中的数据建立了 哈希索引，那么这个匹配的过程就是很快的。 - 如果子查询的结果集非常大，超过了系统变量
tmp_table_size或者max_heap_table_size，临时表会转而使用 基于磁盘的存储引擎 来保存结果集中的记录，索引类型为 B+树索引。
物化表(Materialize)
物化就是将子查询结果集中的记录保存到临时表的过程。存储子查询结果集的临时表称为物化表。正因为物化表中的记录都建立了索引(基于内存的物化表有哈希索引，基于磁盘的有 B+树索引)，
通过索引执行 IN 语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。
对于上面的查询，当把子查询进行物化之后，假设子查询物化表的名称为 materialized_table ，该物化表存储的子查询结果集的列为 m_val ，这个查询可以从以下两种角度来看待：
- 从 表
t1的角度来看待，整个查询的意思是：对于t1表中的每条记录来说，如果该记录的a列的值在子查询对应的物化表中，则该记录会被加入最终的结果集。 - 从子查询 物化表 的角度来看待，整个查询的意思是：对于子查询物化表的每个值来说，如果能在
t1表中找到对应的a列的值与该值相等的记录，那么就把这些记录加入到最终的结果集。
以上查询的优化
上边的查询相当于表 t1 和子查询物化表 materialized_table 进行内连接：
1 | |
转化成内连接之后，查询优化器就可以评估不同连接顺序需要的成本是多少，选取成本最低的那种查询方式执行查询。
半连接（semi-join）
虽然将子查询进行物化之后再执行查询会有建立临时表的成本，但是可以将子查询转换为 JOIN 还是会更有效率一点的。
如何不进行物化操作直接把子查询转换为连接呢？
对比以下查询语句：
1 | |
区别：第二个查询的结果没有 去重。
所以 IN 子查询 和 两表内连接 并不完全等价，但将子查询转换为连接可以充分发挥优化器的作用，所以 MySQL 提出了一个新概念 **半连接 (semi-join)**，
将 t1 表 和 t2 表 进行半连接的意思就是：对于 t1 表 的某条记录来说，只关心在 t2 表 中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最终的结果集中只保留 t1 表 的记录。
semi-join 只是在 MySQL 内部采用的一种执行子查询的方式，MySQL 并没有提供面向用户的 semi-join 语法。
1. Table pullout（在子查询中的表上拉）
直接将 IN 语句改为 JOIN。
当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表上拉到外层查询的 FROM 子句中，并把子查询中的搜索条件合并到外层查询的搜索条件中。
如：
1 | |
优化后：
1 | |
2. DuplicateWeedout（重复值消除）
利用主键去重，会产生临时表。
1 | |
转换为半连接查询后，t1 表中的某条记录可能在 t2 表中有多条匹配的记录，所以该条记录可能多次被添加到最后的结果集中，为了消除重复，我们可以建立一个临时表，比方说这个临时表长这样:
1 | |
这样在执行连接查询的过程中，每当某条 t1 表中的记录要加入结果集时，就首先把这条记录的主键值加入到这个临时表里，如果添加成功，说明之前这条 t1 表中的记录并没有加入最终的结果集，现在把该记录添加到最终的结果集;如果添加失败，说明这条之前这条 t1 表中的记录已经加入过最终的结果集，这里直接把它丢弃就好了，这种使用临时表消除 semi-join 结果集中的重复值的方式称之为 DuplicateWeedout。
3. FirstMatch（首次匹配）
逻辑上的去重，不产生临时表。
FirstMatch 是一种最原始的半连接执行方式，先取一条外层查询的中的记录，然后到子查询的表中寻找符合匹配条件的记录，如果能找到一条，则将该外层查询的记录放入最终的结果集并且停止查找更多匹配的记录，如果找不到则把该外层查询的记录丢弃掉;然后再开始取下一条外层查询中的记录，重复上边这个过程。
4. LooseScan（松散索引扫描）
利用 Hash 索引去重。
不能转换为半连接的情况
注意：由于相关子查询并不是一个独立的查询，所以不能转换为物化表来执行查询。
以下几种情况 不能 转换为 semi-join ：
- 外层查询的
WHERE条件中有其他搜索条件与IN子查询组成布尔表达式使用OR连接。 - 使用
NOT IN而不是IN的情况。 - 子查询中包含
GROUP BYHAVING或者 聚集函数 的情况。 - 子查询中包含
UNION的情况。
对于派生表的优化
1 | |
对于上面的 sql，子查询是放在 from 后面的，这个子查询的结果相当于一个 派生表，表的名称是 t，有 a，b 两个字段。对于派生表，有两种执行方式：
把派生表物化
可以将派生表的结果集写到一个 内部的临时表 中，然后就把这个物化表当作普通表一样参与查询。当然，在对派生表进行物化时，使用了一种称为 延迟物化 的策略，
也就是在查询中真正使用到派生表时才回去尝试物化派生表，而不是还没开始执行查询就把派生表物化掉。比如:
1 | |
首先会先执行子查询，并找到满足 a = 1 的结果集，如果找不到，则不会进行物化。
将派生表和外层的表合并，也就是将查询重写为没有派生表的形式
1 | |
等价于：
1 | |