MySQL子查询的执行原理和优化(转载)

天秤燕
1,115 阅读10分钟

序言: 这是我的第一篇blog,文章内容在转载的基础上做了一点修订和补充.

相关链接

数据库及版本说明

  • 数据库: MySQL
  • 版本: 8.0.30 GA

步入正题🎯

一. 子查询的语法形式和分类

1. 语法形式

子查询的语法规定，子查询可以在一个外层查询的各种位置出现，这里我们只介绍常用的几个：

① from子句:

SELECT m, n FROM (SELECT m2 + 1 AS m, n2 AS n FROM t2 WHERE m2 > 2) AS t; 这个例子中的子查询是：(SELECT m2 + 1 AS m, n2 AS n FROM t2 WHERE m2 > 2)，这个放在FROM子句中的子查询相当于一个表，但又和我们平常使用的表有点不一样，这种由子查询结果集组成的表称之为派生表(Derived Tables)。

② where子句:

SELECT * FROM t1 WHERE m1 = (SELECT MIN(m2) FROM t2); 

SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);

2. 分类(非派生表)

① 按返回的结果集区分:

  • 标量子查询，只返回一个单一值的子查询称之为标量子查询，比如：SELECT * FROM t1 WHERE m1 = (SELECT m1 FROM t1 LIMIT 1);
  • 单行子查询，就是只返回一条记录的子查询，不过这条记录需要包含多个列（只包含一个列就成了标量子查询了）。比如：SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);单行子查询属于行子查询范畴.
  • 列子查询，就是只返回一个列的数据，不过这个列的数据需要包含多条记录（只包含一条记录就成了标量子查询了）。比如：SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);
  • 表子查询，就是子查询的结果既包含很多条记录，又包含很多个列，比如：SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2);其中的 (SELECT m2, n2 FROM t2) 就是一个表子查询，这里需要和行子查询对比一下，单行子查询中我们用了 LIMIT 1 来保证子查询的结果只有一条记录。表子查询属于行子查询范畴.

② 按与外层查询关系来区分:

  • 不相关子查询，就是子查询可以单独运行出结果，而不依赖于外层查询的值，我们就可以把这个子查询称之为不相关子查询。
  • 相关子查询，就是需要依赖于外层查询的值的子查询称之为相关子查询。比如：SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 WHERE n1 = n2);

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

⚠ 接下来讲的内容都是关于非派生表子查询, 关于派生表子查询优化, 请查阅相关文档(MySQL :: MySQL 8.0 Reference Manual :: 8.2.2.4 Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization)

1. 不相关子查询

SELECT (SELECT col1 FROM t2) FROM t1;
SELECT * FROM t1 WHERE col1 > (SELECT MAX(col2) FROM t2);
SELECT col1,col2 FROM t1 WHERE (col1,col2) IN (SELECT col1,col2 FROM t2);

执行顺序: 先执行"()"内部的SELECT语句, 将上一步子查询得到的结果当作外层查询的参数, 再执行外部的SELECT语句.

2. 相关子查询

情景再现: 查找部门中只有2个人的员工的信息.

SELECT * FROM t1 AS t WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.col2 = t.col2);

含有exists的相关子查询

SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.col2 = t2.col2);
  1. 先从外层查询中获取一条记录，本例中是先从 t 表中获取一条记录。
  2. 然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，就是 t 表中找出 t.col2 列的值，然后执行子查询。
  3. 最后根据子查询的查询结果来检测外层查询 WHERE 子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。
  4. 然后重复以上步骤，直到 t 中的记录全部匹配完。

三. IN类型子查询面临的问题和优化器优化

1. 面临的问题

下面是一个带有IN类型的行子查询语句

SELECT col1,col2 FROM t1 WHERE (col1,col2) IN (SELECT col1,col2 FROM t2);

IN子查询由于其结果集可能大于1，因此会面临这些问题：

  1. 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表 ，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
  2. 结果集太多，可能内存中都放不下.
  3. 默认情况下，子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引 ，所以查询性能会受到一定的影响。

解决方案: 剔除重复数据, 减小临时表大小; 产生的临时表如果内存放不下, 就放到磁盘中; 为临时表增加索引; 在条件合适的情况下, 可以转换为使用join进行表连接操作, 这样就不需要临时表了.

2. 优化器做出的努力✊

①. 对IN, = ANY, 或 EXISTS的优化

  • Materialization

    Materialization: 物化, 优化器使用物化来实现更有效的子查询处理。物化通过将子查询结果生成为临时表（通常在内存中）来加速查询执行。 MySQL 第一次需要子查询结果时，会将结果具体化到一个临时表中。任何后续需要结果时，MySQL 都会再次引用临时表。优化器可以使用哈希索引对表进行索引，以使查找快速且成本低。索引包含唯一值以消除重复并使表更小。如果子查询的结果集非常大，超过了系统变量 tmp_table_size或者 max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为 B+ 树索引。

物化是处理不相关子查询的基本方式, 绝大多数情况下都适用, 但是, 除了一种情况:SELECT * FROM t1 WHERE (t1.a,t1.b) NOT IN (SELECT t2.a,t2.b FROM t2 WHERE *where_condition*);要求不管是WHERE还是IN中的字段值, 都不能为NULL.

对于单列子查询, 无论WHERE还是IN中的字段值是否为null, 都可以物化.
对于多列子查询, 要求不管是WHERE还是IN/NOT IN中的字段值, 都不能为NULL.(令人眼花缭乱的NULL处理🤮)
mysql> select 1, 2 where (1, 2)  in ((1, null), (1, 2));
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.00 sec)

mysql> select 1, 2 where (1, null) in ((1, 2), (1, null));
Empty set (0.00 sec)

----------------------------------------------------------------------------------------
mysql> select 1, 2 where (1, 2) not in ((1, null), (1, 3));
Empty set (0.00 sec)

mysql> select 1, 2 where (1, null) not in ((1, 2), (1, 3));
Empty set (0.00 sec)

  • Semijoin

    Semijoin: 半连接, 支持多种执行策略, 如table pullout, duplicate weedout, first match, loose scan.

💡: 由于子查询中可能存在重复的值, 如果直接将子查询转为表连接, 可能会出现重复的数据, 所以, IN 子查询和表连接之间并不完全等价. 因此, 引出了"半连接"这个概念: 对于外部表的某条记录来说，我们只关心在子查询表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最终的结果集中也只保留外部表的记录。

  1. table pullout: 当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表上拉到外层查询的 FROM 子句中(即: inner join)，并把子查询中的搜索条件合并到外层查询的搜索条件中. 一句话: 因为列值是唯一的, 所以可以将子查询转为表连接.
  2. duplicate weedout: 先直接转为inner join, 然后, 为了消除重复数据，可以建立一个临时表，并设置主键id，每当某条外部表中的记录要加入结果集时，就首先把这条记录的id值加入到这个临时表里，如果添加成功，说明之前这条外部表中的记录并没有加入最终的结果集，是一条需要的结果；如果添加失败，说明之前这条外部表中的记录已经加入过最终的结果集，直接把它丢弃。一句话: 使用临时表来淘汰外部表中符合条件的重复数据.
  3. first match: 是一种最原始的半连接执行方式，跟相关子查询的执行方式是一样的，就是说先取一条外层查询中的记录，然后到子查询的表中寻找符合匹配条件的记录，如果能找到一条，则将该外层查询的记录放入最终的结果集并且停止查找更多匹配的记录，如果找不到则把该外层查询的记录丢弃掉。然后再开始取下一条外层查询中的记录，重复上边这个过程。
  4. loose scan: 当子查询select_exp中的列是索引列且where条件也是此索引列时, 使用索引扫描子查询表，查到第一个和外部表的列值相同的情况后即可返回, 不再继续. 相当于是利用了索引的有序性来去重.

💡: 松散扫描用于复合索引, 即使where条件中的列没有按"左前缀原则"来过滤. 实现原理: 假设子查询表的列col1, col2组合成复合索引(col1, col2), where条件中只包含col2的范围过滤, 例如col2 > 10, 则可以进行如下所述的松散扫描策略, 先从col1的第一个值开始, 找到其中col2列中符合条件的范围的记录, 将这些主键保存起来, 再进行下一个col1的第二个值, 依次类推, 相当于是利用了复合索引各个字段的有序性, 分而治之, 在每一组中找到符合条件的记录.

半连接的要求:

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

  • EXISTS 转换策略

    将不相关子查询转换为EXISTS类型相关子查询.

SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE *where_condition*);
SELECT * FROM t1 WHERE EXISTS (SELECT t2.b FROM t2 WHERE *where_condition* AND t1.a=t2.b);
  1. 在未开启optimizer_switch系统变量中materialization标志的情况下, 优化器可能会将其重写为 EXISTS 相关子查询, 但是这样会导致对于外部表的每一行, 内部子查询都要进行一次全表扫描查询.
  2. 如果物化占用内存太大, 会使用磁盘, 性能下降, 也会尝试转换为exists.

②: 对NOT IN, <> ALL 或 NOT EXISTS的优化

  • Materialization

处理方式同IN, 注意NULL的处理.

  • EXISTS 转换策略

💡: 反连接(antijoin)是从MySQL8.0.17引入的, 对于外部表中的每一行，只要在子查询表中找到匹配项，就可以丢弃外部表中的行。简而言之，任何IN (SELECT…SELECT…FROM…，或 EXISTS (SELECT…(FROM…)形式的否定的子查询都转换为反连接。

③: 对派生表(以及视图, 公用表表达式(WITH语句))的优化

  • 将派生表合并到外部查询块中
  • 将派生表物化为内部临时表

总结🍒

1. 如果IN子查询符合转换为 semi-join 的条件，查询优化器会优先把该子查询转换为 semi-join，然后再考虑下边执行半连接的策略中哪个成本最低:

  • table pullout
  • duplicate weedout
  • first match
  • loose scan

2. 如果IN子查询不符合转换为 semi-join 的条件，那么查询优化器会从下边两种策略中找出一种成本更低的方式执行子查询:

  • 先将子查询物化之后再执行查询.
  • 执行 IN → EXISTS 转换.
avatar
文章
阅读
粉丝