10-Mysql内核查询成本计算实战-05

连接查询EXPLAIN输出连接成本

连接查询在输出成本时和单表查询稍有不同，如下：

explain format=json SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2
ON s1.order_no= s2.order_note WHERE s1.expire_time> '2021-03-22 18:28:28'
AND s1.expire_time<= '2021-03-22 18:35:09' AND s2.expire_time> '2021-03-22
18:35:09' AND s2.expire_time<= '2021-03-22 18:35:59'\G
1
2
3
4

EXPLAIN: {
"query_block": {
"select_id": 1,# 整个查询语句只有1个SELECT关键字，该关键字对应的id号为1
"cost_info": {
"query_cost": "840.51" # 整个查询的执行成本
},
"nested_loop": [ # 几个表之间采用嵌套循环连接算法执行
{
"table": {
"table_name": "s2", # s2表是驱动表
"access_type": "range", # 访问方法为range
"possible_keys": [
"idx_expire_time"
],
"key": "idx_expire_time",
"used_key_parts": [
"expire_time"
],
"key_length": "5",
"rows_examined_per_scan": 321, # 查询s2表大致需要扫描321条记录
"rows_produced_per_join": 321, # 驱动表s2的扇出是321
"filtered": "100.00", # condition filtering代表的百分比
"index_condition": "((`mysqladv`.`s2`.`expire_time` > '2021-03-22
18:35:09') and (`mysqladv`.`s2`.`expire_time` <= '2021-03-22 18:35:59'))",
"cost_info": {
"read_cost": "386.21",
"eval_cost": "64.20",
"prefix_cost": "450.41", # 查询s1表总共的成本，read_cost + eval_cost
"data_read_per_join": "152K" # 读取的数据量
},
"used_columns": [
"id",
"order_no",
"order_note",
"insert_time",
"expire_duration",
"expire_time",
"order_status"
]
}
},
{
"table": {
"table_name": "s1", # s1表是被驱动表
"access_type": "ref",
"possible_keys": [
"idx_order_no",
"idx_expire_time"
],
"key": "idx_order_no",
"used_key_parts": [
"order_no"
],
"key_length": "152",
"ref": [
"mysqladv.s2.order_note"
],
"rows_examined_per_scan": 1, # 查询一次s1表大致需要扫描1条记录
"rows_produced_per_join": 16, # 被驱动表s2的扇出是16（由于没有多余的表进行连
接，所以这个值无用）
"filtered": "4.94", # condition filtering代表的百分比
"index_condition": "(`mysqladv`.`s1`.`order_no` =
`mysqladv`.`s2`.`order_note`)",
"cost_info": {
"read_cost": "325.08",
"eval_cost": "3.21",
"prefix_cost": "840.51", # 单次查询s2、多次查询s1表总共的成本
"data_read_per_join": "7K"
},
"used_columns": [
"id",
"order_no",
"order_note",
"insert_time",
"expire_duration",
"expire_time",
"order_status"
],
"attached_condition": "((`mysqladv`.`s1`.`expire_time` > '2021-03-
22 18:28:28') and (`mysqladv`.`s1`.`expire_time` <= '2021-03-22 18:35:09'))"
}
}
]
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

我们使用#后边跟随注释的形式为大家解释了EXPLAIN FORMAT=JSON语句的输出内容，s2表的"cost_info"中prefix_cost就是单独查询s2表的成本。

对于s1表的"cost_info"中，由于s1表是被驱动表，所以可能被读取多次，这里的read_cost和eval_cost是访问多次s2表后累加起来的值，而s1表中的prefix_cost的值代表的是整个连接查询预计的成本。看完了上面的执行计划的输出，可能大家有疑惑，驱动表S2的查询成本为450.41，总查询成本为840.51，也就是说对被驱动表S1的查询成本也就是390左右，看起来用S1做驱动表好像更省一点。真的这样吗？我们把SQL语句改造一下，将INNER JOIN 替换为

STRAIGHT_JOIN：
explain format=json SELECT * FROM order_exp AS s1 STRAIGHT_JOIN order_exp2 AS
s2 ON s1.order_no= s2.order_note WHERE s1.expire_time> '2021-03-22 18:28:28'
AND s1.expire_time<= '2021-03-22 18:35:09' AND s2.expire_time> '2021-03-22
18:35:09' AND s2.expire_time<= '2021-03-22 18:35:59'\G
1
2
3
4
5

大家可以自行看看所需要的成本是多少，并自行解释一下原因。

多表连接的成本分析

首先要考虑一下多表连接时可能产生出多少种连接顺序：
对于两表连接，比如表A和表B连接只有 AB、BA这两种连接顺序。其实相当于2 × 1 = 2种连接顺序。
对于三表连接，比如表A、表B、表C进行连接有ABC、ACB、BAC、BCA、CAB、CBA这么6种连接顺序。其实相当于3 × 2 × 1 = 6种连接顺序。
对于四表连接的话，则会有4 × 3 × 2 × 1 = 24种连接顺序。
对于n表连接的话，则有 n × (n-1) × (n-2) × ··· × 1种连接顺序，就是n的阶乘种连接顺序，也就是n!。
有n个表进行连接，MySQL查询优化器要每一种连接顺序的成本都计算一遍么？那就有n!种连接顺序。其实真的是要都算一遍，不过MySQL用了很多办法减少计算非常多种连接顺序的成本的方法：
提前结束某种顺序的成本评估
MySQL在计算各种链接顺序的成本之前，会维护一个全局的变量，这个变量表示当前最小的连接查询成本。如果在分析某个连接顺序的成本时，该成本已经超过当前最小的连接查询成本，那就压根儿不对该连接顺序继续往下分析了。比方说A、B、C三个表进行连接，已经得到连接顺序ABC是当前的最小连接成本，比方说10.0，在计算连接顺序BCA时，发现B和C的连接成本就已经大于10.0时，就不再继续往后分析BCA这个连接顺序的成本了。
系统变量optimizer_search_depth

为了防止无穷无尽的分析各种连接顺序的成本，MySQL提出了optimizer_search_depth系统变量，如果连接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否则只对与optimizer_search_depth值相同数量的表进行穷举分析。很显然，该值越大，成本分析的越精确，越容易得到好的执行计划，但是消耗的时间也就越长，否则得到不是很好的执行计划，但可以省掉很多分析连接成本的时间。

根据某些规则压根儿就不考虑某些连接顺序
即使是有上边两条规则的限制，但是分析多个表不同连接顺序成本花费的时间还是会很长，所以MySQL干脆提出了一些所谓的启发式规则（就是根据以往经验指定的一些规则），凡是不满足这些规则的连接顺序压根儿就不分析，这样可以极大的减少需要分析的连接顺序的数量，但是也可能造成错失最优的执行计划。他们提供了一个系统变量optimizer_prune_level来控制到底是不是用这些启发式规则。不过按照《阿里最新Java编程规范泰山版》中《(二) 索引规约》中的说法：

当出现超过三个表的join时，就应该考虑改写SQL语句了，因为从我们上面的多表关联成本分析可以知道，就算是不考虑多表关联时需要查询的巨大记录条数，就算是几个表的关联成本计算也是个很耗费时间的过程。