聊一聊 MySQL 相关子查询

数据库 MySQL
这一篇我们就来聊聊不相关子查询转换为相关子查询,以及相关子查询执行的那些事。

子查询系列的上一篇文章​​《MySQL 不相关子查询怎么执行?》​​​提到过,MySQL 有可能把不相关​子查询转换为相关子查询。

这一篇我们就来聊聊不相关子查询转换为相关子查询,以及相关子查询执行的那些事。

本文不相关子查询都是指的 IN 子查询,内容基于 MySQL 8.0.29 源码。

正文

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:

-- 为了保证 EXISTS 子查询不会转换为半连接
-- 先把半连接优化关闭
SET optimizer_switch="semijoin=off";

-- 纯手工打造的相关子查询
EXPLAIN SELECT * FROM city
WHERE city_id < 100 AND EXISTS (
SELECT city_id FROM address
WHERE city.city_id = address.city_id
)

-- explain 结果如下,为了方便查看
-- 我调整了 ref 列的位置
-- 并且删掉了 partitions 列
+----+--------------------+---------+-------+---------------------+----------------+----------------+---------+------+----------+-------------+
| id | select_type | table | type | ref | possible_keys | key | key_len | rows | filtered | Extra |
+----+--------------------+---------+-------+---------------------+----------------+----------------+---------+------+----------+-------------+
| 1 | PRIMARY | city | range | <null> | PRIMARY | PRIMARY | 2 | 99 | 100.0 | Using where |
| 2 | DEPENDENT SUBQUERY | address | ref | sakila.city.city_id | idx_fk_city_id | idx_fk_city_id | 2 | 1 | 100.0 | Using index |
+----+--------------------+---------+-------+---------------------+----------------+----------------+---------+------+----------+-------------+

子查询 type 列的值为 ref,表示 address 表使用 idx_fk_city_id 索引(key 列的值)进行等值范围扫描。

子查询 ref 列的值为 sakila.city.city_id,表示 where 条件中 address.city_id 字段值来源于主查询 city 表的 city_id 字段值。

示例 SQL 2:

-- 为了保证 IN 子查询不会转换为半连接
-- 先把半连接优化关闭
SET optimizer_switch="semijoin=off";

-- 不相关子查询
EXPLAIN SELECT * FROM city
WHERE city_id < 100 AND city_id IN (
SELECT city_id FROM address
)

-- explain 结果如下
-- 可以看到不相关子查询已经转换为相关子查询了
-- 为了方便查看,我调整了 ref 列的位置
-- 并且删掉了 partitions 列
+----+--------------------+---------+----------------+--------+----------------+----------------+---------+------+----------+-------------+
| id | select_type | table | type | ref | possible_keys | key | key_len | rows | filtered | Extra |
+----+--------------------+---------+----------------+--------+----------------+----------------+---------+------+----------+-------------+
| 1 | PRIMARY | city | range | <null> | PRIMARY | PRIMARY | 2 | 99 | 100.0 | Using where |
| 2 | DEPENDENT SUBQUERY | address | index_subquery | func | idx_fk_city_id | idx_fk_city_id | 2 | 1 | 100.0 | Using index |
+----+--------------------+---------+----------------+--------+----------------+----------------+---------+------+----------+-------------+

子查询 type 列的值为 index_subquery,ref 列的值为 func。

这 2 列的值看起来挺唬人的,但实际上和示例 SQL 1 的 type = ref,ref = sakila.city.city_id 并没有什么不一样,无非是换了一身行头而已。

我们先从源码里看看 type = index_subquery 是怎么来的:

// sql/opt_explain.cc
bool Explain_join::explain_join_type(){
const join_type j_t = type == JT_UNKNOWN ? JT_ALL : type;
const char *str = join_type_str[j_t];
// 访问方式是 eq_ref
if ((j_t == JT_EQ_REF ||
// 访问方式是 ref
j_t == JT_REF ||
// 访问方式是 ref_or_null
j_t == JT_REF_OR_NULL) &&
// 当前 select 语句是子查询
join->query_expression()->item) {
/*
For backward-compatibility, we have special presentation of "index
lookup used for in(subquery)": we do not show "ref/etc", but
"index_subquery/unique_subquery".
*/
// 如果这个 if 判断条件成立
// 就说明 IN 子查询已经转换为【相关子查询】了
if (join->query_expression()->item->engine_type() ==
Item_subselect::INDEXSUBQUERY_ENGINE)
str = (j_t == JT_EQ_REF)
? "unique_subquery"
: "index_subquery";
}

fmt->entry()->col_join_type.set_const(str);
return false;
}

上面代码是 explain 结果中 type 列的显示逻辑。

从代码可以看到 IN 子查询转换为相关子查询之后,type 列的显示逻辑如下:

  • 表的访问方式是eq_ref,type 列的值为 unique_subquery。
  • 表的访问方式是ref 或 ref_or_null,type 列的值为 index_subquery。

由此,我们就揭开了 index_subquery 的神秘面纱,实际上它就是 ref 或 ref_no_null。

另外,从代码的英文注释中,我们可以看到,type 列之所以这么显示是为了向后兼容。

接下来,我们再来看看 ref 列的显示逻辑:

// sql/sql_select.h
class store_key {
......
virtual const char *name() const {
// Compatible with legacy behavior.
// where 条件字段是正常字段(另一个表的字段)
// 返回的是字段全名,即 db.table.field
if (item->type() == Item::FIELD_ITEM) {
return item->full_name();
} else {
return "func";
}
}
......
}

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 条件字段所做的优化,我们在此不深入具体细节。

# 新条件 city.city_id 字段的表名
(lldb) print ((Item_field *)((Item_cache_int *)item->real_item())->get_example())->table_name
(const char *) $16 = "city"
# 新条件 city.city_id 字段的字段名
(lldb) print ((Item_field *)((Item_cache_int *)item->real_item())->get_example())->field_name
(const char *) $17 = "city_id"
# 新条件 city.city_id 的类型
(lldb) print item->type()
(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 开启:

SET optimizer_trace = "enabled=on"

开启了 optimizer trace,执行 SQL 语句之后,可以通过以下 SQL 得到 optimizer trace 结果:

SELECT * 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 子句这样的限制因素,或者因为成本问题不能转换为半连接,那就要在物化和相关子查询两种策略中二选一了。

(1)要不要转换?

还是以前面的 IN 子查询 SQL 为例,我们通过 optimizer trace 来看看 MySQL 在物化和相关子查询两种策略中二选一的过程。

-- 为了保证 IN 子查询不会转换为半连接
-- 先把半连接优化关闭
SET optimizer_switch="semijoin=off";

-- 开启 optimizer trace
SET optimizer_trace = "enabled=on";

-- 执行 select 语句
SELECT * FROM city
WHERE city_id < 100 AND city_id IN (
SELECT city_id FROM address
);

-- 获取 select 语句的跟踪信息
SELECT * FROM information_schema.OPTIMIZER_TRACE;

以下是 optimizer trace 中关于物化和相关子查询两种策略的决策依据:

{
"execution_plan_for_potential_materialization": {
"subq_mat_decision": {
"parent_fanouts": [
{
"select#": 1,
"subq_attached_to_table": true,
"table": "`city`",
"fanout": 99,
"cacheable": true
}
],
"cost_to_create_and_fill_materialized_table": 123.849,
"cost_of_one_EXISTS": 0.349669,
"number_of_subquery_evaluations": 99,
"cost_of_materialization": 133.749,
"cost_of_EXISTS": 34.6172,
"chosen": false
}
}
}

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 获取:

SELECT
cost_name, cost_value, default_value
FROM mysql.server_cost
WHERE cost_name = 'memory_temptable_row_cost'

+---------------------------+------------+---------------+
| cost_name | cost_value | default_value |
+---------------------------+------------+---------------+
| memory_temptable_row_cost | <null> | 0.1 |
+---------------------------+------------+---------------+

查询 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 表示子查询的执行次数。

(2)怎么转换?

还是以前面的示例 SQL 为例:

SELECT * FROM city
WHERE city_id < 100 AND city_id IN (
SELECT city_id FROM address
)

在查询准备阶段,还没有确定子查询的执行策略之前,就会把主查询 where 条件中的 IN 条件字段和子查询 select 子句中的字段组成新条件,并附加到子查询的 where 条件中。

也就是把 city 表的 city_id 字段和 address 表的 city_id 字段组成新条件,附加到子查询中,看起来就像是这样的 select 语句:

SELECT * FROM city
WHERE city_id < 100 AND EXISTS (
SELECT city_id FROM address
WHERE city.city_id = address.city_id
)

那么问题来了,如果查询优化阶段决定 IN 子查询不转换为相关子查询,附加到子查询 where 条件中的新条件怎么办?

这个好办,再删掉就是了。

在构造的时候,新条件会被打上标记,表示这个条件是 IN 子查询转换为相关子查询时新构造的。

有了这个标记,就能知道要删除子查询 where 条件中的那个条件了。

4、执行流程

不管是 IN 子查询转换来的,还是我们纯手工打造的相关子查询,到了执行阶段,流程就一样了。

还是以前面的示例 SQL 1 为例,来介绍相关子查询的主要执行流程:

SELECT * FROM city
WHERE city_id < 100 AND EXISTS (
SELECT city_id FROM address
WHERE city.city_id = address.city_id
)

步骤 1,主查询从 city 表读取一条记录。

步骤 2,判断主查询记录是否匹配 where 条件。

因为 city_id < 100 在前,先判断主查询记录是否满足这个条件。

如果满足,则执行子查询,否则,回到步骤 1。

假设主查询读取到 city 表的 city_id 字段值为 8,此时,要执行的子查询就是这样的了:

SELECT city_id FROM address
WHERE address.city_id = 8

如果执行子查询查到了记录,说明主查询记录满足 city_id < 100 和 EXISTS 子查询两个条件,把主查询记录返回给客户端,否则,回到步骤 1。

重复执行步骤 1 ~ 2,直到读完主查询 city 表中满足 city_id < 100 的所有记录,执行流程结束。

通过 optimizer trace 也可以验证主查询每读取一条满足 city_id < 100 的记录,EXISTS 子查询都要执行一次,如下:

{
"join_execution": {
"select#": 1,
"steps": [
{
// 主查询一共有 99 满足 where 条件的记录
// steps 中有 99 个 subselect_execution
"subselect_execution": {
"select#": 2,
"steps": [
{
"join_execution": {
"select#": 2,
"steps": []
}
}
]
}
// 此处省略 98 个 subselect_execution
// ......
}
]
}
}

以下是 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 可以看到两种执行策略的成本。
  • 简单介绍了相关子查询的执行流程。

本文转载自微信公众号「一树一溪」,可以通过以下二维码关注。转载本文请联系一树一溪公众号。

责任编辑:姜华 来源: 一树一溪
相关推荐

2020-10-15 06:56:51

MySQL排序

2021-04-23 10:31:18

MySQLRole数据库

2021-07-30 10:33:57

MySQL触发器数据

2021-03-01 18:37:15

MySQL存储数据

2022-11-21 08:21:04

MySQL子查询

2023-09-22 17:36:37

2021-01-28 22:31:33

分组密码算法

2020-05-22 08:16:07

PONGPONXG-PON

2018-06-07 13:17:12

契约测试单元测试API测试

2022-08-08 08:25:21

Javajar 文件

2021-01-29 08:32:21

数据结构数组

2023-07-06 13:56:14

微软Skype

2021-08-04 09:32:05

Typescript 技巧Partial

2023-05-15 08:38:58

模板方法模式

2018-11-29 09:13:47

CPU中断控制器

2019-02-13 14:15:59

Linux版本Fedora

2022-11-01 08:46:20

责任链模式对象

2021-02-06 08:34:49

函数memoize文档

2022-02-21 15:01:45

MySQL共享锁独占锁

2020-08-12 08:34:16

开发安全We
点赞
收藏

51CTO技术栈公众号