MySQL 相关子查询
子查询系列的上一篇文章《MySQL 不相关子查询怎么执行?》提到过,MySQL 有可能把不相关
子查询转换为相关
子查询。
这一篇我们就来聊聊不相关子查询转换为相关子查询,以及相关子查询执行的那些事。
本文不相关子查询都是指的
IN 子查询
,内容基于 MySQL 8.0.29 源码。
目录 [TOC]
正文
1. explain type、ref 列的显示逻辑
本文示例 SQL 中的表,都来自于官方提供的测试数据库 sakila,下载链接如下: https://downloads.mysql.com/docs/sakila-db.tar.gz
相关子查询有两种来源(也许还有其它来源?
):
- 一种是我们纯手工打造的。
- 另一种就是从不相关子查询转换来的了。
通过 explain 查看这两种 SQL 的执行计划,子查询的 type、ref 列可能一样,也可能不一样,难免让人困惑。
我们先来弄清楚两种 SQL 的 explain 结果中,子查询的 type、ref 列为什么会显示不一样?
示例 SQL 1:
1-- 为了保证 EXISTS 子查询不会转换为半连接
2-- 先把半连接优化关闭
3SET optimizer_switch="semijoin=off";
4
5-- 纯手工打造的相关子查询
6EXPLAIN SELECT * FROM city
7WHERE city_id < 100 AND EXISTS (
8 SELECT city_id FROM address
9 WHERE city.city_id = address.city_id
10)
11
12-- explain 结果如下,为了方便查看
13-- 我调整了 ref 列的位置
14-- 并且删掉了 partitions 列
15+----+--------------------+---------+-------+---------------------+----------------+----------------+---------+------+----------+-------------+
16| id | select_type | table | type | ref | possible_keys | key | key_len | rows | filtered | Extra |
17+----+--------------------+---------+-------+---------------------+----------------+----------------+---------+------+----------+-------------+
18| 1 | PRIMARY | city | range | <null> | PRIMARY | PRIMARY | 2 | 99 | 100.0 | Using where |
19| 2 | DEPENDENT SUBQUERY | address | ref | sakila.city.city_id | idx_fk_city_id | idx_fk_city_id | 2 | 1 | 100.0 | Using index |
20+----+--------------------+---------+-------+---------------------+----------------+----------------+---------+------+----------+-------------+
子查询 type 列的值为 ref
,表示 address 表使用 idx_fk_city_id
索引(key 列的值)进行等值
范围扫描。
子查询 ref 列的值为 sakila.city.city_id
,表示 where 条件中 address.city_id 字段值来源于主查询 city 表的 city_id 字段值。
示例 SQL 2:
1-- 为了保证 IN 子查询不会转换为半连接
2-- 先把半连接优化关闭
3SET optimizer_switch="semijoin=off";
4
5-- 不相关子查询
6EXPLAIN SELECT * FROM city
7WHERE city_id < 100 AND city_id IN (
8 SELECT city_id FROM address
9)
10
11-- explain 结果如下
12-- 可以看到不相关子查询已经转换为相关子查询了
13-- 为了方便查看,我调整了 ref 列的位置
14-- 并且删掉了 partitions 列
15+----+--------------------+---------+----------------+--------+----------------+----------------+---------+------+----------+-------------+
16| id | select_type | table | type | ref | possible_keys | key | key_len | rows | filtered | Extra |
17+----+--------------------+---------+----------------+--------+----------------+----------------+---------+------+----------+-------------+
18| 1 | PRIMARY | city | range | <null> | PRIMARY | PRIMARY | 2 | 99 | 100.0 | Using where |
19| 2 | DEPENDENT SUBQUERY | address | index_subquery | func | idx_fk_city_id | idx_fk_city_id | 2 | 1 | 100.0 | Using index |
20+----+--------------------+---------+----------------+--------+----------------+----------------+---------+------+----------+-------------+
子查询 type 列的值为 index_subquery
,ref 列的值为 func
。
这 2 列的值看起来挺唬人的,但实际上和示例 SQL 1
的 type = ref,ref = sakila.city.city_id 并没有什么不一样,无非是换了一身行头而已。
我们先从源码里看看 type = index_subquery 是怎么来的:
1// sql/opt_explain.cc
2bool Explain_join::explain_join_type() {
3 const join_type j_t = type == JT_UNKNOWN ? JT_ALL : type;
4 const char *str = join_type_str[j_t];
5 // 访问方式是 eq_ref
6 if ((j_t == JT_EQ_REF ||
7 // 访问方式是 ref
8 j_t == JT_REF ||
9 // 访问方式是 ref_or_null
10 j_t == JT_REF_OR_NULL) &&
11 // 当前 select 语句是子查询
12 join->query_expression()->item) {
13 /*
14 For backward-compatibility, we have special presentation of "index
15 lookup used for in(subquery)": we do not show "ref/etc", but
16 "index_subquery/unique_subquery".
17 */
18 // 如果这个 if 判断条件成立
19 // 就说明 IN 子查询已经转换为【相关子查询】了
20 if (join->query_expression()->item->engine_type() ==
21 Item_subselect::INDEXSUBQUERY_ENGINE)
22 str = (j_t == JT_EQ_REF)
23 ? "unique_subquery"
24 : "index_subquery";
25 }
26
27 fmt->entry()->col_join_type.set_const(str);
28 return false;
29}
上面代码是 explain 结果中 type 列的显示逻辑。
从代码可以看到 IN 子查询转换为相关子查询之后,type 列的显示逻辑如下:
- 表的访问方式是
eq_ref
,type 列的值为unique_subquery
。 - 表的访问方式是
ref
或ref_or_null
,type 列的值为index_subquery
。
由此,我们就揭开了 index_subquery 的神秘面纱,实际上它就是 ref 或 ref_no_null。
另外,从代码的英文注释中,我们可以看到,type 列之所以这么显示是为了向后兼容。
接下来,我们再来看看 ref 列的显示逻辑:
1// sql/sql_select.h
2class store_key {
3 ......
4 virtual const char *name() const {
5 // Compatible with legacy behavior.
6 // where 条件字段是正常字段(另一个表的字段)
7 // 返回的是字段全名,即 db.table.field
8 if (item->type() == Item::FIELD_ITEM) {
9 return item->full_name();
10 } else {
11 return "func";
12 }
13 }
14 ......
15}
IN 子查询转换为相关子查询之后,主查询 where 条件
的 city_id
字段和子查询 select 子句
的 city_id
字段会组成新条件
(address.city_id = city.city_id),附加到子查询 where 条件中。
新条件的 city.city_id 字段类型 REF_ITEM
,而不是 FIELD_ITEM,通过在调试控制台执行如下命令可以验证:
这里 REF_ITEM 是对 FIELD_ITEM 的引用,这是源码中对包含子查询的 IN 条件字段所做的优化,我们在此不深入具体细节。
1# 新条件 city.city_id 字段的表名
2(lldb) print ((Item_field *)((Item_cache_int *)item->real_item())->get_example())->table_name
3(const char *) $16 = "city"
4
5# 新条件 city.city_id 字段的字段名
6(lldb) print ((Item_field *)((Item_cache_int *)item->real_item())->get_example())->field_name
7(const char *) $17 = "city_id"
8
9# 新条件 city.city_id 的类型
10(lldb) print item->type()
11(Item::Type) $18 = REF_ITEM
所以,新条件类型是 REF_ITEM,命中了前面代码中的 else 分支(return "func"
),explain 结果的 ref 列就显示为 func 了。
ref 列的值虽然显示为 func,但是新条件 city.city_id
字段还是读取的主查询 city_id 字段值,只不过是中间隔了一层,其它并没有什么特殊的。
厘清了两种 SQL explain 结果 type、ref 列的不同之处,就可以开始介绍不相关子查询转换为相关子查询的逻辑了。
因为在介绍过程中会用到 optimizer trace,所以先来简单了解下 optimizer trace 的相关知识点。
2. optimizer trace
通过 optimizer trace,我们可以了解到 MySQL 准备阶段、优化阶段、执行阶段的一些内部细节。特别是可以了解 MySQL 选择某个执行计划的决策依据。
optimizer trace 默认为关闭状态,如果需要,可以通过执行以下 SQL 开启:
1SET optimizer_trace = "enabled=on"
开启了 optimizer trace,执行 SQL 语句之后,可以通过以下 SQL 得到 optimizer trace 结果:
1SELECT * FROM information_schema.OPTIMIZER_TRACE
OPTIMIZER_TRACE 表有 4 个字段:
QUERY
:SQL 语句。TRACE
:json 格式的 optimizer trace 内容,如果内容长度超过系统变量optimizer_trace_max_mem_size
的值就会被截断。
该系统变量控制的是一条 SQL 的 optimizer trace 内容长度,默认值是 1048576(字节)。MISSING_BYTES_BEYOND_MAX_MEM_SIZE
:如果 optimizer trace 内容因超长被截断,这个字段记录了被截断的字节数。INSUFFICIENT_PRIVILEGES
:如果用户执行 QUERY 字段中的 SQL 语句权限不够,导致 TRACE 字段内容为空,该字段会显示为 1。
如果使用客户端(如 Navicat),我们执行一条 SQL,客户端可能会额外执行一些统计 SQL。
因为默认配置只会保留最近一条 SQL 的 optimizer trace 内容,使用客户端有可能导致我们看不到自己的 SQL optimizer trace 内容。
这种情况下,我们需要修改 2 个系统变量的值:
optimizer_trace_offset
:从最近执行的哪条 SQL 开始保存 optimizer trace 内容,默认值为-1
,表示从最近执行的 1 条 SQL 开始保存 optimizer trace 内容。optimizer_trace_limit
:保存多少条 optimizer trace 内容,默认值为 1。
3. IN 子查询转换
IN 子查询有 3 大执行策略:
- 转换为半连接,这是最优先的执行策略。
- 子查询物化。
- 转换为相关子查询。
如果子查询中存在像 group by 子句这样的限制因素,或者因为成本问题不能转换为半连接,那就要在物化和相关子查询两种策略中二选一了。
3.1 要不要转换?
还是以前面的 IN 子查询 SQL 为例,我们通过 optimizer trace 来看看 MySQL 在物化和相关子查询两种策略中二选一的过程。
1-- 为了保证 IN 子查询不会转换为半连接
2-- 先把半连接优化关闭
3SET optimizer_switch="semijoin=off";
4
5-- 开启 optimizer trace
6SET optimizer_trace = "enabled=on";
7
8-- 执行 select 语句
9SELECT * FROM city
10WHERE city_id < 100 AND city_id IN (
11 SELECT city_id FROM address
12);
13
14-- 获取 select 语句的跟踪信息
15SELECT * FROM information_schema.OPTIMIZER_TRACE;
以下是 optimizer trace 中关于物化和相关子查询两种策略的决策依据:
1{
2 "execution_plan_for_potential_materialization": {
3 "subq_mat_decision": {
4 "parent_fanouts": [
5 {
6 "select#": 1,
7 "subq_attached_to_table": true,
8 "table": "`city`",
9 "fanout": 99,
10 "cacheable": true
11 }
12 ],
13 "cost_to_create_and_fill_materialized_table": 123.849,
14 "cost_of_one_EXISTS": 0.349669,
15 "number_of_subquery_evaluations": 99,
16 "cost_of_materialization": 133.749,
17 "cost_of_EXISTS": 34.6172,
18 "chosen": false
19 }
20 }
21}
chosen 字段值为 false
,表示 MySQL 没有
使用物化方式执行子查询,原因是使用物化方式的成本(cost_of_materialization = 133.749
)比相关子查询的成本(cost_of_EXISTS = 34.6172
)更高。
知道了结果,我们再来看看物化和相关子查询的成本是怎么计算的。
使用物化方式执行子查询的成本:
parent_fanouts.fanout = 99
表示预估的主查询 city 表中满足 city_id < 100 的记录数量。
number_of_subquery_evaluations 表示子查询的执行次数
,因为对于主查询中满足 city_id < 100 的每一条记录,相关子查询都要执行一次,所以,这个字段值等于
parent_fanouts.fanout。
cost_to_create_and_fill_materialized_table 表示创建临时表的成本,加上
把子查询中的所有记录都写入临时表的成本。
cost_of_materialization 表示使用物化方式执行 IN 子查询的总成本,计算逻辑如下:
cost_of_materialization = cost_to_create_and_fill_materialized_table
(123.849) + number_of_subquery_evaluations
(99) * 0.1 = 133.749。
其中 0.1 是从主查询中读取一条记录之后,拿到 city_id 字段值,去临时表中查询记录的成本常数,可以通过以下 SQL 获取:
1SELECT
2 cost_name, cost_value, default_value
3FROM mysql.server_cost
4WHERE cost_name = 'memory_temptable_row_cost'
5
6+---------------------------+------------+---------------+
7| cost_name | cost_value | default_value |
8+---------------------------+------------+---------------+
9| memory_temptable_row_cost | <null> | 0.1 |
10+---------------------------+------------+---------------+
查询 cost_name 等于 memory_temptable_row_cost
的成本常数,因为使用的是内存临时表。
如果子查询使用的是磁盘临时表,则需要查询 cost_name 等于 disk_temptable_row_cost
的成本常数。
转换为相关子查询的执行成本:
cost_of_EXISTS = cost_of_one_EXISTS
(0.349669) * number_of_subquery_evaluations
(99) = 34.6172。
cost_of_one_EXISTS 表示子查询执行一次的成本,number_of_subquery_evaluations 表示子查询的执行次数。
3.2 怎么转换?
还是以前面的示例 SQL 为例:
1SELECT * FROM city
2WHERE city_id < 100 AND city_id IN (
3 SELECT city_id FROM address
4)
在查询准备阶段,还没有确定子查询的执行策略之前,就会把主查询 where 条件中的 IN 条件字段和子查询 select 子句中的字段组成新条件
,并附加到子查询的 where 条件中。
也就是把 city 表的 city_id 字段和 address 表的 city_id 字段组成新条件
,附加到子查询中,看起来就像是这样的 select 语句:
1SELECT * FROM city
2WHERE city_id < 100 AND EXISTS (
3 SELECT city_id FROM address
4 WHERE city.city_id = address.city_id
5)
那么问题来了,如果查询优化阶段决定 IN 子查询不转换为相关子查询,附加到子查询 where 条件中的新条件
怎么办?
这个好办,再删掉就是了。
在构造的时候,新条件会被打上标记,表示这个条件是 IN 子查询转换为相关子查询时新构造的。
有了这个标记,就能知道要删除子查询 where 条件中的那个条件了。
4. 执行流程
不管是 IN 子查询转换来的,还是我们纯手工打造的相关子查询,到了执行阶段,流程就一样了。
还是以前面的示例 SQL 1
为例,来介绍相关子查询的主要执行流程:
1SELECT * FROM city
2WHERE city_id < 100 AND EXISTS (
3 SELECT city_id FROM address
4 WHERE city.city_id = address.city_id
5)
步骤 1,主查询从 city 表读取一条记录。
步骤 2,判断主查询记录是否匹配主查询 where 条件。
因为 city_id < 100
在前,先判断主查询记录是否满足这个条件。
如果满足,则执行子查询,否则,回到步骤 1
。
假设主查询读取到 city 表的 city_id 字段值为 8,此时,要执行的子查询就是这样的了:
1SELECT city_id FROM address
2WHERE address.city_id = 8
如果执行子查询查到了记录,说明主查询记录满足 city_id < 100 和 EXISTS 子查询两个条件,把主查询记录返回给客户端,否则,回到步骤 1
。
重复执行
步骤 1 ~ 2,直到读完主查询 city 表中满足 city_id < 100 的所有记录
,执行流程结束。
通过 optimizer trace 也可以验证主查询每读取一条满足 city_id < 100 的记录,EXISTS 子查询都要执行一次,如下:
1{
2 "join_execution": {
3 "select#": 1,
4 "steps": [
5 {
6 // 主查询一共有 99 满足 where 条件的记录
7 // steps 中有 99 个 subselect_execution
8 "subselect_execution": {
9 "select#": 2,
10 "steps": [
11 {
12 "join_execution": {
13 "select#": 2,
14 "steps": []
15 }
16 }
17 ]
18 }
19 // 此处省略 98 个 subselect_execution
20 // ......
21 }
22 ]
23 }
24}
以下是 optimizer trace 的部分内容截图,expanded_query 就是经过 MySQL 展开处理之后的 select 语句,我做了一些简化和处理,如下:
join_execution 的 steps 后面,99 items
就是 99 个折叠起来的 subselect_execution
。
5. 最佳实践
MySQL 读取主查询的一条记录之后,判断记录是否匹配 where 条件,是按照我们写 SQL 时字段在 where 条件中出现的顺序进行判断的。
由于判断主查询记录是否匹配 IN 子查询条件时,需要执行子查询,成本比较高,所以,我们写 SQL 的时候最好是把不包含子查询的 where 条件放在前面,包含子查询的 where 条件放在最后
。
这个逻辑在《MySQL 不相关子查询怎么执行?》 这篇文章中也介绍过,这里不再重复介绍了。
6. 总结
本文主要介绍了以下内容:
- 不相关子查询转换为相关子查询之后,explain 结果中:
- 子查询 type 列的值 unique_subquery 是 eq_ref 的别名;index_subquery 是 ref 或 ref_or_null 的别名。
- 子查询 ref 列的值会显示为 func,这是因为主查询 IN 条件字段和子查询 select 子句字段组成的新条件中,IN 条件字段
引用
了主查询表中的字段,而不是
直接使用主查询表中的字段。
- 不相关子查询,如果不能转换为半连接,则会在物化和相关子查询两种策略中二选一。
- 两种策略二选一的依据是
子查询执行成本
,哪个执行成本低就选择哪个。通过 optimizer trace 可以看到两种执行策略的成本。 - 简单介绍了相关子查询的执行流程。
欢迎扫码关注公众号,我们一起学习更多 MySQL 知识: