MySQL 不相关子查询怎么执行?

chujichenxuyuan
发布于 2023-2-1 11:15
浏览
0收藏

作者 | 操盛春

来源 | 一树一溪(ID:lovespring52)

转载请联系授权(微信ID:csch52)

经过上一篇 where field in (...) 的开场准备,本文正式开启子查询系列,这个系列会介绍子查询的各种执行策略,计划包括以下主题:

  • 不相关子查询 (Subquery)
  • 相关子查询 (Dependent Subquery)
  • 嵌套循环连接 (Blocked Nested Loop Join)
  • 哈希连接 (Hash Join)
  • 表上拉 (Table Pullout)
  • 首次匹配 (First Match)
  • 松散扫描 (Loose Scan)
  • 重复值消除 (Duplicate Weedout)
  • 子查询物化 (Materialize)

上面列表中,从表上拉(Table Pullout)开始的 5 种执行策略都用 Join 实现,所以把嵌套循环连接、哈希连接也包含在这个系列里面了。

子查询系列文章的主题,在写作过程中可能会根据情况调整,也可能会插入其它不属于这个系列的文章。

本文我们先来看看不相关子查询是怎么执行的?

本文内容基于 MySQL 8.0.29 源码。

1. 概述

从现存的子查询执行策略来看,半连接 (Semijoin) 加入之前,不相关子查询有两种执行策略:

策略 1,子查询物化,也就是把子查询的执行结果存入临时表,这个临时表叫作​​物化表​​。

explain select_type = ​​SUBQUERY​​ 就表示使用了物化策略执行子查询,如下:

+----+-------------+---------+------------+-------+------------------------+----------------+---------+--------+------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys          | key            | key_len | ref    | rows | filtered | Extra                    |
+----+-------------+---------+------------+-------+------------------------+----------------+---------+--------+------+----------+--------------------------+
| 1  | PRIMARY     | city    | <null>     | ALL   | <null>                 | <null>         | <null>  | <null> | 600  |  33.33   | Using where              |
| 2  | SUBQUERY    | address | <null>     | range | PRIMARY,idx_fk_city_id | idx_fk_city_id | 2       | <null> | 9    | 100.0    | Using where; Using index |
+----+-------------+---------+------------+-------+------------------------+----------------+---------+--------+------+----------+--------------------------+

策略 2,转换为相关子查询,explain select_type = ​​DEPENDENT SUBQUERY​​,如下:

+----+--------------------+---------+------------+-----------------+------------------------+---------+---------+--------+------+----------+-------------+
| id | select_type        | table   | partitions | type            | possible_keys          | key     | key_len | ref    | rows | filtered | Extra       |
+----+--------------------+---------+------------+-----------------+------------------------+---------+---------+--------+------+----------+-------------+
| 1  | PRIMARY            | city    | <null>     | ALL             | <null>                 | <null>  | <null>  | <null> | 600  | 33.33    | Using where |
| 2  | DEPENDENT SUBQUERY | address | <null>     | unique_subquery | PRIMARY,idx_fk_city_id | PRIMARY | 2       | func   | 1    |  5.0     | Using where |
+----+--------------------+---------+------------+-----------------+------------------------+---------+---------+--------+------+----------+-------------+

本文我们要介绍的就是使用​​物化​​策略执行不相关子查询的过程,不相关子查询转换为相关子查询的执行过程,留到下一篇文章。

2. 执行流程

我们介绍的执行流程,不是整条 SQL 的完整执行流程,只会涉及到子查询相关的那些步骤。

MySQL 不相关子查询怎么执行?-鸿蒙开发者社区

​查询优化阶段​​,MySQL 确定了要使用物化策略执行子查询之后,就会创建临时表。

关于创建临时表的更多内容,后面有一小节单独介绍。

​执行阶段​​​,server 层从存储引擎读取到​​主查询​​​的​​第一条​​记录之后,就要判断记录是否匹配 where 条件。

判断包含子查询的那个 where 条件字段时,发现子查询需要物化,就会执行子查询。

为了方便描述,我们给​​包含子查询的那个 where 条件字段​​取个名字:sub_field,后面在需要时也会用到这个名字。

执行子查询的过程,是从存储引擎一条一条读取子查询表中的记录。每读取到一条记录,都写入临时表中。

子查询的记录都写入临时表之后,从主查询记录中拿到 ​​sub_field​​​ 字段值,去临时表中查找,如果找到了记录,​​sub_field​​ 字段条件结果为 true,否则为 false。

主查询的所有 where 条件都判断完成之后,如果每个 where 条件都成立,记录就会返回给客户端,否则继续读取下一条记录。

server 层从存储引擎读取​​主查询​​​的第 2 ~ N 条记录,判断记录是否匹配 where 条件时,就可以直接用 ​​sub_field​​​ 字段值去临时表中查询是否有相应的记录,以判断 ​​sub_field​​ 字段条件是否成立。

从以上内容可以见,子查询物化只会执行一次。

3. 创建临时表

临时表是在查询优化阶段创建的,它也是一个正经表。既然是正经表,那就要确定它使用什么存储引擎。

临时表会优先使用​​内存​​存储引擎,MySQL 8 有两种内存存储引擎:

  • 从 5.7 继承过来的​​MEMORY​​ 引擎。
  • 8.0 新加入的​​TempTable​​ 引擎。

有了选择就要发愁,MySQL 会选择哪个引擎?

这由我们决定,我们可以通过系统变量 ​​internal_tmp_mem_storage_engine​​ 告诉 MySQL 选择哪个引擎,它的可选值为 TempTable(默认值)、MEMORY。

然而,​​internal_tmp_mem_storage_engine​​​ 指定的引擎并不一定是最终的选择,有两种情况会导致临时表使用​​磁盘​​​存储引擎 ​​InnoDB​​。

这两种情况如下:

情况 1,如果我们指定了使用 ​​MEMORY​​ 引擎,而子查询结果中包含 BLOB 字段,临时表就只能使用 InnoDB 引擎了。

为啥?因为 MEMORY 引擎不支持 BLOB 字段。

情况 2,如果系统变量 ​​big_tables​​​ 的值为 ​​ON​​​,并且子查询中没有指定 ​​SQL_SMALL_RESULT​​ Hint,临时表也只能使用 InnoDB 引擎。

big_tables 的默认值为 OFF。

这又为啥?

因为 ​​big_tables = ON​​ 是告诉 MySQL 我们要执行的所有 SQL 都包含很多记录,临时表需要使用 InnoDB 引擎。

然而,时移事迁,如果某天我们发现有一条执行频繁的 SQL,虽然要使用临时表,但是记录数量比较少,使用​​内存​​存储引擎就足够用了。

此时,我们就可以通过 Hint 告诉 MySQL 这条 SQL 的结果记录数量很少,MySQL 就能心领神会的直接使用 ​​internal_tmp_mem_storage_engine​​ 中指定的内存引擎了。

SQL可以这样指定 Hint:

SELECT * FROM city WHERE country_id IN (
  SELECT SQL_SMALL_RESULT address_id FROM address WHERE city_id < 10
) AND city < 'China'

捋清楚了选择存储引擎的逻辑,接下来就是字段了,临时表会包含哪些字段?

这里没有复杂逻辑需要说明,临时表只会包含子查询 ​​SELECT 子句​​​中的字段,例如:上面的示例  SQL 中,临时表包含的字段为 ​​address_id​​。

使用临时表存放子查询的结果,是为了提升整个 SQL 的执行效率。如果临时表中的记录数量很多,根据主查询字段值去临时表中查找记录的成本就会比较高。

所以,MySQL 还会为临时表中的字段创建索引,索引的作用有两个:

  • 提升查询临时表的效率。
  • 保证临时表中记录的唯一性,也就是说创建的索引是​​唯一索引​​。

说完了字段,我们再来看看索引结构,这取决于临时表最终选择了哪个存储引擎:

  • MEMORY、TempTable 引擎,都使用 HASH 索引。
  • InnoDB 引擎,使用 BTREE 索引。

4. 自动优化

为了让 SQL 执行的更快,MySQL 在很多细节处做了优化,对包含子查询的 where 条件判断所做的优化就是其中之一。

介绍这个优化之前,我们先准备一条 SQL:

SELECT * FROM city WHERE country_id IN (
  SELECT address_id FROM address WHERE city_id < 10
) AND city < 'China'

主查询 city 表中有以下记录:

MySQL 不相关子查询怎么执行?-鸿蒙开发者社区

示例  SQL where 条件中,country_id 条件包含子查询,如果不对 where 条件判断做优化,从 city 表中每读取一条记录之后,先拿到 country_id 字段值,再去临时表中查找记录,以判断条件是否成立。

从上面 city 表的记录可以看到, city_id = 73 ~ 78 的记录,country_id 字段值都是 44。

从 city 表中读取到 city_id = 73 的记录之后,拿到 country_id 的值 44,去临时表中查找记录。

不管是否找到记录,都会有一个结果,为了描述方便,我们假设结果为 ​​true​​。

接下来从 city 表中读取 city_id = 74 ~ 78 的记录,因为它们的 country_id 字段值都是 44,实际上没有必要再去临时表里找查找记录了,直接复用 city_id = 73 的判断结果就可以了,这样能节省几次去临时表查找记录的时间。

由上所述,总结一下 MySQL 的​​优化逻辑​​:

对于包含子查询的 where 条件字段,如果连续几条记录的字段值都相同,​​这组记录​​​中,只有​​第一条​​​记录会根据 where 条件字段值去​​临时表​​​中查找是否有对应记录,这一组的​​剩余​​​记录直接​​复用​​第一条记录的判断结果。

5. 手动优化

上一小节介绍的是 MySQL 已经做过的优化,但还有一些可以做而没有做的优化,我们写 SQL 的时候,可以自己优化,也就是手动优化。

我们还是使用前面的示例 SQL 来介绍手动优化:

MySQL 不相关子查询怎么执行?-鸿蒙开发者社区

主查询有两个 where 条件,那么判断 where 条件是否成立有两种执行顺序:

  • 先判断 country_id 条件,如果结果为 true,再判断 city 条件。
  • 先判断 city 条件,如果结果为 true,再判断 country_id 条件。

MySQL 会按照 where 条件出现的顺序判断,也就是说,我们把哪个 where 条件写在前面,MySQL 就先判断哪个。对于示例 SQL 来说,就是上面所列的第一种执行顺序。

为了更好的比较两种执行顺序的优劣,我们用量化数据来说明。

根据 country_id 字段值去子查询临时表中查找记录的成本,会​​高于​​判断 city 字段值是否小于 China 的成本,所以,假设执行一次 country_id 条件判断的成本为 5,执行一次 city 条件判断的成本为 1。

对于主查询的某一条记录,假设 country_id 条件成立,city 条件不成立,两种执行顺序成本如下:

  • 先判断 country_id 条件,成本为 5,再判断 city 条件,成本为 1,总成本 5 + 1 = 6。
  • 先判断 city 条件,成本为 1,因为条件不成立,不需要再判断 country_id 条件,总成本为 1。

上面所列场景,第一种执行顺序的成本​​高于​​第二种执行顺序的成本,而 MySQL 使用的是第一种执行顺序。

MySQL 没有为这种场景做优化,我们可以手动优化,写 SQL 的时候,把这种包含子查询的 where 条件​​放在最后​​,尽可能让 MySQL 少做一点无用工,从而让 SQL 可以执行的更快一点。

6. 总结

对于 where 条件包含子查询的 SQL,我们可以做一点优化,就是把这类 where 条件放在最后,让 MySQL 能够少做一点无用功,提升 SQL 执行效率。

分类
标签
已于2023-2-1 11:15:02修改
收藏
回复
举报
回复
    相关推荐