深入 MySQL 查询优化器：Optimizer Trace 分析

一、前言

我们在日常维护数据库的时候,如果遇到慢语句查询的时候,我们一般会怎么做?执行EXPLAIN去查看它的执行计划?
是的。我们经常会这么做,然后看到执行计划展示给我们的一些信息,告诉我们MySQL是如何执行语句的。

但是执行计划往往只给我们带来了最基础的分析信息,比如是否有使用索引,还有一些其他供我们分析的信息,比如使用了临时表、排序等等。我们能从这些信息里面找一些优化点,这样就足够了吗?

看看这张图里的执行计划,我们可以提很多问题:为什么t1表上明明使用了索引在Extra列中还是能看到filesort?如果possible_keys列中有多个索引的话,优化器是基于什么选定使用的索引?这些问题,并不能非常直观地从执行计划中看出来更多的信息,这个时候,我们可以开启OPTIMIZER_TRACE,基于OPTIMIZER_TRACE捕获的信息,去做更细致的追踪分析。一起来看看吧~

二、参数详解

optimizer_trace

  • enabled:启用/禁用optimizer_trace功能
  • one_line:决定了跟踪信息的存储方式,为on表示使用单行存储,否则以json树的标准展示形式存储

optimizer_trace_features

该变量中存储了跟踪信息中可控的打印项,可以通过调整该变量,控制在INFORMATION_SCHEMA.OPTIMIZER_TRACE表中的trace列需要打印的JSON项和不需要打印的JSON项。默认打开该参数下的所有项。

  • greedy_search: 对于有N个表的join操作,可能产生N的阶乘的查询计划路径。如果禁用,则不跟踪贪婪搜索
  • range_optimizer: range优化,如果禁用,则不会跟踪范围优化器
  • dynamic_range: dynamic range optimizer,如果关闭该选项的话,只有第一次调用JOIN_TAB::SQL_SELECT才被跟踪
  • repeated_subselect :子查询,如果关闭的话,只有第一次调用Item_subselect才被跟踪

optimizer_trace_max_mem_size

  • optimizer_trace内存的大小,如果跟踪信息超过这个大小,信息将会被截断

optimizer_trace_limit

  • optimizer_trace_limit,约束跟踪信息存储的个数

**optimizer_trace_offset **

  • optimizer_trace_offset,约束偏移量

三、Optimizer Trace

在Optimizer Trace的输出中,主要分为三个部分:

  • join_preparation SQL的准备阶段
  • join_optimization SQL优化阶段
  • join_execution SQL执行阶段

join_preparation

第一部分是完成SQL的准备工作。在这个阶段,SQL语句会被格式化输出,通配符*会被具体字段代替,但不会进行等价改写动作。如上图中传入的SQL语句是”select * from dept”的结果。在完成了语句的补充、格式化后,准备阶段结束并进入下一阶段。

join_optimization

第二部分,是完成SQL语句的逻辑与物理优化的过程,这其中的优化步骤比较多

在展开具体内容之前,先解释下”select #”的问题。在输出中经常会看到有”select#:N”的字样,它表示当前跟踪的结构体是属于第几个SELECT。

如果语句中使用多个SELECT语句拼接 如UNION 或者有嵌套子查询中有SELECT,会产生多个序号。

condition_processing

这一部分是完成对条件语句的优化,包括对WHERE子句或HAVING子句的优化。在后面的”condition”部分就标识出是对哪类子句的优化,如下图就是对WHERE子句的优化。

在后面是三个优化过程,每步都写明了转换类型 transformation ,明确转换做的事情,以及转换之后的结果语句 resulting_condition 。这三个转换分别是:

  • equality_propagation 等值条件句转换
  • constant_propagation 常量条件句转换
  • trivial_condition_removal 无效条件移除的转换

substitute_generated_columns

用于替换虚拟生成列

table_dependencies

这部分是要找出表之间的相互依赖关系。如查询中存在多个表且之间是有依赖关系,会影响优化行为。这部分信息更多是提示作用,没有实质优化动作。

在具体字段含义上:

  • table:涉及的表名 如果有别名,也会显示出来
  • row_may_be_null:列是否允许为NULL,这里并不是指表中的列属性是否允许为NULL,而是指JOIN操作之后的列是否为NULL。比如说原始语句中如果使用了LEFT JOIN,那么后一张表的row_may_be_null则会显示为true。
  • map_bit:表的映射编号,从0开始递增。
  • depends_on_map_bits:依赖的映射表,这里主要是在使用STRAIGHT_JOIN进行强制连接顺序或者是LEFT JOIN/RIGHT JOIN有顺序差别时,会在depends_on_map_bits中列出前置表的map_bit。
ref_optimizer_key_uses

列出了所有可用的ref类型的索引,在图中显示可过滤的字段

rows_estimation

在这一阶段会评估各种扫描方式的成本,包括全表扫描机索引扫描的代价估算。这一段以表对象作为结构体进行展开

potential_range_indexes

该阶段会列出表中所有的索引并分析其是否可用,并且还会列出索引中可用的列字段;如果不可用,则列出不可用的原因

setup_range_conditions

如果有可下推的条件,则带条件考虑范围查询

group_index_range

评估在使用了GROUP BY或者是DISTINCT的时候是否有适合的索引可用

skip_scan_range

8.0新增,是否使用了skip scan

在 MySQL 中,skip_scan_range 是优化器跟踪输出中的一个元素,它表示优化器使用了跳过扫描 Skip Scan 访问方法。跳过扫描是一种范围访问方法,它在查询过程中跳过不满足条件的行,从而减少需要扫描的行数,提高查询效率

analyzing_range_alternatives

分析各索引使用成本,包括range_scan_alternatives range扫描分析 、analyzing_roworder_intersect index merge分析 两个阶段,分别针对不同的情况进行执行代价的分析,从中选择出更优的执行计划

在有了上述对比之后,在下面chosen_range_access_summary给出这部分的最终结论,使用了 index : customer_id

considered_execution_plans

对比实际的不同路径的成本。如果是多表关联,且有存在执行顺序(如left/right join或straight_join来强制指定顺序),则在plan_prefix部分会有前置条件;否则,就按照所有可能性评估

attaching_conditions_to_tables

这一步是在上面的基础上,尽量通过绑定条件到对应表上来获取更好的数据筛选。如果能做ICP(索引条件推入)则更佳

finalizing_table_conditions

refine_plan

最后的优化后的结果,如果只是展示对应的表对象没有其他字段,则说明之前已经确定的执行计划已经是最优的结果

join_execution

在SQL在阶段,大部分都是空白的

只有当语句中包含有排序等操作时,才会在此部分显示

附件

1
2
3
4
5
6
CREATE TABLE t1 (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20) DEFAULT NULL,
id1 INT DEFAULT NULL,
PRIMARY KEY ( id )
) ENGINE InnoDB AUTO_INCREMENT 9 DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO t1 (name, id1) VALUES
('Alice', 101),
('Bob', 102),
('Charlie', 103),
('David', 104),
('Eve', 105),
('Frank', 106),
('George', 107),
('Hannah', 108),
('Ivan', 109),
('Jack', 110),
('Kevin', 111);
1
2
3
4
5
6
7
8
9
10
11
INSERT INTO orders (order_date, amount, customer_id) VALUES
('2023-01-01', 100.00, 1),
('2023-01-02', 200.50, 2),
('2023-01-03', 300.75, 3),
('2023-01-04', 400.00, 4),
('2023-01-05', 500.00, 5),
('2023-01-06', 600.25, 6),
('2023-01-07', 700.50, 7),
('2023-01-08', 800.00, 8),
('2023-01-09', 900.75, 9),
('2023-01-10', 1000.00, 10);
1
2
3
4
5
6
7
8
9
10
11
SELECT
t1.id AS customer_id,
t1.name AS customer_name,
GROUP_CONCAT(o.amount ORDER BY o.amount SEPARATOR ',') AS grouped_amount, -- 使用 GROUP_CONCAT() 函数并确保它在 SELECT 列表中
COUNT(o.order_id) AS order_count
FROM
(SELECT * FROM t1 WHERE id 1) AS t1
JOIN
orders AS o ON t1.id o.customer_id
GROUP BY
amount; 
1
2
3
4
5
6
7
8
9
10
11
SELECT
t1.id AS customer_id,
t1.name AS customer_name,
GROUP_CONCAT(DISTINCT o.amount ORDER BY o.amount SEPARATOR ',') AS grouped_amount, -- 使用 GROUP_CONCAT() 函数并确保它在 SELECT 列表中,添加 DISTINCT 以去重
COUNT(o.order_id) AS order_count
FROM
(SELECT * FROM t1 WHERE id 1) AS t1
JOIN
orders AS o ON t1.id o.customer_id
GROUP BY
amount;
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
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
QUERY | SELECT
t1.id AS customer_id,
t1.name AS customer_name,
GROUP_CONCAT(DISTINCT o.amount ORDER BY o.amount SEPARATOR ',') AS grouped_amount, -- 使用 GROUP_CONCAT() 函数并确保它在 SELECT 列表中,添加 DISTINCT 以去重
COUNT(o.order_id) AS order_count
FROM
(SELECT * FROM t1 WHERE id 1) AS t1 -- 子查询,先从 t1 表中选出 id 为 1 的记录
JOIN
orders AS o ON t1.id o.customer_id -- 将子查询的结果与 orders 表进行连接
GROUP BY
amount
TRACE | {
steps : [
{
join_preparation : {
select# : 1,
steps : [
{
join_preparation : {
select# : 2,
steps : [
{
expanded_query : /* select#2 */ select t1 . id AS id , t1 . name AS name , t1 . id1 AS id1 from t1 where ( t1 . id 1)
}
]
}
},
{
derived : {
table : t1 ,
select# : 2,
merged : true
}
},
{
expanded_query : /* select#1 */ select t1 . id AS customer_id , t1 . name AS customer_name ,group_concat(distinct o . amount order by o . amount ASC separator ',') AS grouped_amount ,count( o . order_id ) AS order_count from (<constant table> join ( t1 ) on(( t1 . id 1)) join orders o on(( t1 . id o . customer_id ))) group by o . amount
},
{
transformations_to_nested_joins : {
transformations : [
JOIN_condition_to_WHERE ,
parenthesis_removal
],
expanded_query : /* select#1 */ select t1 . id AS customer_id , t1 . name AS customer_name ,group_concat(distinct o . amount order by o . amount ASC separator ',') AS grouped_amount ,count( o . order_id ) AS order_count from t1 join orders o where (( t1 . id o . customer_id ) and ( t1 . id 1)) group by o . amount
}
},
{
functional_dependencies_of_GROUP_columns : {
all_columns_of_table_map_bits : [
0
],
columns : [
demo3.o.amount ,
demo3.t1.id
]
}
}
]
}
},
{
join_optimization : {
select# : 1,
steps : [
{
condition_processing : {
condition : WHERE ,
original_condition : (( t1 . id o . customer_id ) and ( t1 . id 1)) ,
steps : [
{
transformation : equality_propagation ,
resulting_condition : (multiple equal(1, t1 . id , o . customer_id ))
},
{
transformation : constant_propagation ,
resulting_condition : (multiple equal(1, t1 . id , o . customer_id ))
},
{
transformation : trivial_condition_removal ,
resulting_condition : multiple equal(1, t1 . id , o . customer_id )
}
]
}
},
{
substitute_generated_columns : {
}
},
{
table_dependencies : [
{
table : t1 ,
row_may_be_null : false,
map_bit : 0,
depends_on_map_bits : [
]
},
{
table : orders o ,
row_may_be_null : false,
map_bit : 1,
depends_on_map_bits : [
]
}
]
},
{
ref_optimizer_key_uses : [
{
table : t1 ,
field : id ,
equals : 1 ,
null_rejecting : true
},
{
table : orders o ,
field : customer_id ,
equals : 1 ,
null_rejecting : true
}
]
},
{
rows_estimation : [
{
table : t1 ,
rows : 1,
cost : 1,
table_type : const ,
empty : false
},
{
table : orders o ,
range_analysis : {
table_scan : {
rows : 2,
cost : 2.55
},
potential_range_indexes : [
{
index : PRIMARY ,
usable : false,
cause : not_applicable
},
{
index : customer_id ,
usable : true,
key_parts : [
customer_id ,
order_id
]
}
],
setup_range_conditions : [
],
group_index_range : {
chosen : false,
cause : not_single_table
},
skip_scan_range : {
chosen : false,
cause : not_single_table
},
analyzing_range_alternatives : {
range_scan_alternatives : [
{
index : customer_id ,
ranges : [
customer_id 1
],
index_dives_for_eq_ranges : true,
rowid_ordered : true,
using_mrr : false,
index_only : false,
in_memory : 1,
rows : 1,
cost : 0.61,
chosen : true
}
],
analyzing_roworder_intersect : {
usable : false,
cause : too_few_roworder_scans
}
},
chosen_range_access_summary : {
range_access_plan : {
type : range_scan ,
index : customer_id ,
rows : 1,
ranges : [
customer_id 1
]
},
rows_for_plan : 1,
cost_for_plan : 0.61,
chosen : true
}
}
}
]
},
{
considered_execution_plans : [
{
plan_prefix : [
t1
],
table : orders o ,
best_access_path : {
considered_access_paths : [
{
access_type : ref ,
index : customer_id ,
rows : 1,
cost : 0.35,
chosen : true
},
{
access_type : range ,
range_details : {
used_index : customer_id
},
chosen : false,
cause : heuristic_index_cheaper
}
]
},
condition_filtering_pct : 100,
rows_for_plan : 1,
cost_for_plan : 0.35,
chosen : true
}
]
},
{
attaching_conditions_to_tables : {
original_condition : ( o . customer_id 1) ,
attached_conditions_computation : [
],
attached_conditions_summary : [
{
table : orders o ,
attached : ( o . customer_id 1)
}
]
}
},
{
optimizing_distinct_group_by_order_by : {
simplifying_group_by : {
original_clause : o . amount ,
items : [
{
item : o . amount
}
],
resulting_clause_is_simple : true,
resulting_clause : o . amount
}
}
},
{
finalizing_table_conditions : [
{
table : orders o ,
original_table_condition : ( o . customer_id 1) ,
final_table_condition : null
}
]
},
{
refine_plan : [
{
table : orders o
}
]
},
{
considering_tmp_tables : [
{
creating_tmp_table : {
tmp_table_info : {
table : intermediate_tmp_table ,
columns : 1,
row_length : 6,
key_length : 0,
unique_constraint : false,
makes_grouped_rows : false,
cannot_insert_duplicates : false,
location : TempTable
}
}
},
{
adding_sort_to_table : o
}
]
}
]
}
},
{
join_execution : {
select# : 1,
steps : [
{
sorting_table : o ,
filesort_information : [
{
direction : asc ,
expression : o . amount
}
],
filesort_priority_queue_optimization : {
usable : false,
cause : not applicable (no LIMIT)
},
filesort_execution : [
],
filesort_summary : {
memory_available : 262144,
key_size : 5,
row_size : 18,
max_rows_per_buffer : 15,
num_rows_estimate : 15,
num_rows_found : 1,
num_initial_chunks_spilled_to_disk : 0,
peak_memory_used : 32776,
sort_algorithm : none ,
unpacked_addon_fields : skip_heuristic ,
sort_mode : <fixed_sort_key, additional_fields>
}
}
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE | 0
INSUFFICIENT_PRIVILEGES | 0