子查询优化

MySQL优化器是如何处理子查询的

子查询语法

• 在SELECT子句中
  select (select m1 from t1 limit 1)
• 在FROM子句中
  select m,n from (select m2+1 as m, n2 as n from t2 where m2 > 2) as t; 在FROM子句后面的子查询称为派生表，t为派生表
• 在WHERE 或 ON子句的表达式中
  select * from t1 where m1 in (select m2 from t2)

区分子查询（以下都是不相关子查询）

按返回的结果集区分

• 标量子查询：只返回一个单一值得子查询
  
• 行子查询：返回一条记录的子查询
• 列子查询：查询一个列的数据，这个列的数据要多条记录
• 表子查询：返回多条记录，多个列

按与外层查询的关系区分

• 不相关子查询：子查询可单独运行，不依赖外层查询的值
• 相关子查询：子查询的执行需要依赖外层查询的值
  select * from t1 where m1 in (select m2 from t2 where n1=n2)

EXISTS子查询

判断子查询的结果集是否有记录，返回TRUE/FALSE，不在乎记录的具体内容 select * from t1 where exists (select 1 from t2);

子查询在MySQL中是如何运行的

MySQL对IN子查询进行很多优化，如果IN子查询符合转换为半连接的条件，查询优化器会优分把该子查询转换为半连接，然后再考虑下面5种执行半连接查询的策略中哪个成本最低，最后选择成本最低的执行策略来执行子查询。

• Table pullout 子查询中的表上拉
• Duplicate Weedout 重复值消除
• LooseScan 松散扫描
• Semi-join Materialization 半连接物化
• FirstMatch 首次匹配

如果IN子查询不符合转换为半连接的条件，查询优化器会从下面的两种策略中找出一种 成本更低的方式执行子查询：

• 先将子查询物化，再执行查询；
• 执行IN到EXISTS的转换。

1. 将子查询转化成半连接

半连接只是MySQL内部的一种执行子查询的方式
半连接，对于s1表中的某条记录，只关心s2表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最终的结果集只保留s1表记录。

2. 将子查询物化

• 子查询结果集转成物化表
  在不相关子查询中，不直接将子查询的结果集当作外层查询的参数，而是将结果集写入一个临时表中。也称为物化。如果子查询的结果集太大，那么临时表转而使用基于磁盘的存储引擎保存结果集的记录，否则，直接基于内存的存储引擎保存结果集的记录。基于内存的物化表有哈希索引，基于磁盘的物化表有B+树索引
• 物化表转连接
  转换成连接后，查询优化器通过评估不同的连接顺序的成本决定选取哪种最低的连接方式执行查询操作

3. 将子查询IN转换EXISTS子查询

将子查询IN转换EXISTS子查询，就可以使用索引