mysql是如何子查询优化的？

子查询优化

子查询在MySQL中是怎么执行的

标量子查询、行子查询的执行方式（只有一个结果集的子查询）

我们经常在下边两个场景中使用到标量子查询或者行子查询：

• SELECT子句中，我们前边说过的在查询列表中的子查询必须是标量子查询。
• 子查询使用=、>、<、>=、<=、<>、!=、<=>等操作符和某个操作数组成一个布尔表达式，这样的子查询必须是标量子查询或者行子查询。

对于相关的标量子查询或者行子查询来说，比如下边这个查询：

SELECT * FROM s1 WHERE 
    key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);

它的执行方式就是这样的：

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

IN子查询优化

物化表

如果IN子查询是先执行子查询得到所有结果集会怎么样？

• 结果集太多，可能内存中都放不下～

• 对于外层查询来说，如果子查询的结果集太多，那就意味着IN子句中的参数特别多，这就导致：

  • 无法有效的使用索引，只能对外层查询进行全表扫描（成本计算器需要计算结果集中的每个结果需要回表次数，计算每个结果集是一次随机IO，所以太多的话会放弃索引走全表扫描，详情见优化器成本计算一章）。
  • 在对外层查询执行全表扫描时，由于IN子句中的参数太多，这会导致检测一条记录是否符合和IN子句中的参数匹配花费的时间太长。

于是MySQL不直接将子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里。写入临时表的过程是这样的：

• 该临时表的列就是子查询结果集中的列。
• 使用Memory存储引擎的临时表，而且会为该表建立哈希索引。（建立hash索引可以快速命中是否在结果集里面）
• 写入临时表的记录会被去重。（表中记录的所有列建立主键或者唯一索引就好了）

如果子查询的结果集非常大，超过了系统变量tmp_table_size或者max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。

优化正式开始——物化表连接

SELECT * FROM s1 
    WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

当我们把子查询进行物化之后，假设子查询物化表的名称为materialized_table，该物化表存储的子查询结果集的列为m_val

也就是说其实上边的查询就相当于表s1和子查询物化表materialized_table进行内连接：

SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;

s1作驱动表

materialized_table作驱动表

MySQL查询优化器会通过运算来选择上述成本更低的方案来执行查询。

将子查询转换为semi-join

SELECT * FROM s1 
    WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

直接转化为内连接会怎么样？

转化后的sql：

SELECT s1.* FROM s1 INNER JOIN s2 
    ON s1.key1 = s2.common_field 
    WHERE s2.key3 = 'a';

1. 对于s1表的某条记录来说，s2表中没有任何记录满足s1.key1 = s2.common_field这个条件，那么该记录自然也不会加入到最后的结果集。
2. 对于s1表的某条记录来说，s2表中有且只有1条记录满足s1.key1 = s2.common_field这个条件，那么该记录会被加入最终的结果集。
3. 对于s1表的某条记录来说，s2表中至少有2条记录满足s1.key1 = s2.common_field这个条件，那么该记录会被多次加入最终的结果集。

对于1和2来说转化为全连接是没有问题的，但是对于3来说，会出现重复数据的问题。

所以MySQL提出了一个新概念 — 半连接（英文名：semi-join）。将s1表和s2表进行半连接的意思就是：对于s1表的某条记录来说，我们只关心在s2表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最终的结果集中只保留s1表的记录

SELECT s1.* FROM s1 SEMI JOIN s2
    ON s1.key1 = s2.common_field
    WHERE key3 = 'a';

怎么实现半连接呢？

1. Table pullout （子查询中的表上拉）：当子查询的查询列表处只有主键或者唯一索引列时

2. DuplicateWeedout execution strategy （重复值消除）： 建立一个临时表t_1，sql转化为全连接后将符合条件的s1结果丢到t_1中，并根据主键去重，最终返回临时表的结果。

3. LooseScan execution strategy （松散扫描）：

   SELECT * FROM s1 
       WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b');
   

   将s2作为驱动表执行查询的话，那么执行过程就是这样：

半连接 的适用条件

• 该子查询必须是和IN语句组成的布尔表达式，并且在外层查询的WHERE或者ON子句中出现。
• 外层查询也可以有其他的搜索条件，只不过和IN子查询的搜索条件必须使用AND连接起来。
• 该子查询必须是一个单一的查询，不能是由若干查询由UNION连接起来的形式。
• 该子查询不能包含GROUP BY或者HAVING语句或者聚集函数。

半连接 的不适用条件

• 外层查询的WHERE条件中有其他搜索条件与IN子查询组成的布尔表达式使用OR连接起来

  SELECT * FROM s1 
      WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a')
          OR key2 > 100;
  
  

• 使用NOT IN而不是IN的情况

  SELECT * FROM s1 
      WHERE key1 NOT IN (SELECT common_field FROM s2 WHERE key3 = 'a')
  

• 在SELECT子句中的IN子查询的情况

  SELECT key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a') FROM s1 ;
  

• 子查询中包含GROUP BY、HAVING或者聚集函数的情况

  SELECT * FROM s1 
      WHERE key2 IN (SELECT COUNT(*) FROM s2 GROUP BY key1);
  

• 子查询中包含UNION的情况

  SELECT * FROM s1 WHERE key1 IN (
      SELECT common_field FROM s2 WHERE key3 = 'a' 
      UNION
      SELECT common_field FROM s2 WHERE key3 = 'b'
  );
  

MySQL仍然留了两手绝活来优化不能转为semi-join查询的子查询，那就是：

• 对于不相关子查询来说，可以尝试把它们物化之后再参与查询

  比如我们上边提到的这个查询：

  SELECT * FROM s1 
      WHERE key1 NOT IN (SELECT common_field FROM s2 WHERE key3 = 'a')
  

  先将子查询物化，然后再判断key1是否在物化表的结果集中可以加快查询执行的速度。

  请注意这里将子查询物化之后不能转为和外层查询的表的连接，只能是先扫描s1表，然后对s1表的某条记录来说，判断该记录的key1值在不在物化表中。

• 不管子查询是相关的还是不相关的，都可以把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)
  

思考：什么时候用exists子查询，什么时候用in查询？

• 对于IN查询：mysql会将子查询物化为一个内存表inner join一下或者半连接查询，这都导致两个问题：

  1. 子查询的表很大：全表扫描子查询的S2表建立物化表花费时间很长
  2. IN的字段没有使用索引：如果子查询表S2作驱动表同1，S1表作驱动表的话需要循环全表扫描S2

• 对于EXISTS：先全表扫描S1表数据，再将S1的结果集和S2作关联对比，如果符合条件返回结果。类似于S1 inner join S2，S1作驱动表，不同的是匹配到一条记录就返回。

  1. 如果S1表很大，需要全表扫描，比较消耗时间
  2. 关联字段没有索引的话，需要重复全表扫描S2

结论：

• IN字段或者关联字段一定需要建立索引
• 子查询S2表是小表，S1表大用IN效率高一些
• 子查询S2表是大表，S1表小用EXISTS效率高一些