trace分析SQL
MySQL5.6开始支持使用trace查看优化器如何选择执行计划。 通过trace，能够进一步了解为什么优化器选择A执行计划而不是选择B执行计划，或者知道某个排序使用的排序模式，帮助我们更好地理解优化器行为。
如果需要使用，先开启 trace，设置格式为 JSON，再执行需要分析的 SQL，最后查看 trace 分析结果（在 information_schema.OPTIMIZER_TRACE 中）。
开启该功能，会对 MySQL 性能有所影响，因此只建议分析问题时临时开启。
使用步骤
步骤如下：
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
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;
/* optimizer_trace="enabled=on" 表示开启 trace；end_markers_in_json=on 表示 JSON 输出开启结束标记 */
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where a >900 and b > 910 order by a;
+------+------+------+
| id | a | b |
+------+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
......
| 1000 | 1000 | 1000 |
+------+------+------+
1000 rows in set (0.00 sec)
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G
*************************** 1. row ***************************
QUERY: select * from t1 where a >900 and b > 910 order by a --SQL语句
TRACE: {
"steps": [
{
"join_preparation": { --SQL准备阶段
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `t1`.`id` AS `id`,`t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`create_time` AS `create_time`,`t1`.`update_time` AS `update_time` from `t1` where ((`t1`.`a` > 900) and (`t1`.`b` > 910)) order by `t1`.`a`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { --SQL优化阶段
"select#": 1,
"steps": [
{
"condition_processing": { --条件处理
"condition": "WHERE",
"original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))", --原始条件
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))" --等值传递转换
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))" --常量传递转换
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))" --去除没有的条件后的结构
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */ --替换虚拟生成列
},
{
"table_dependencies": [ --表依赖详情
{
"table": "`t1`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [ --预估表的访问成本
{
"table": "`t1`",
"range_analysis": {
"table_scan": {
"rows": 1000, --扫描行数
"cost": 207.1 --成本
} /* table_scan */,
"potential_range_indexes": [ --分析可能使用的索引
{
"index": "PRIMARY",
"usable": false, --为false，说明主键索引不可用
"cause": "not_applicable"
},
{
"index": "idx_a", --可能使用索引idx_a
"usable": true,
"key_parts": [
"a",
"id"
] /* key_parts */
},
{
"index": "idx_b", --可能使用索引idx_b
"usable": true,
"key_parts": [
"b",
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": { --分析各索引的成本
"range_scan_alternatives": [
{
"index": "idx_a", --使用索引idx_a的成本
"ranges": [
"900 < a" --使用索引idx_a的范围
] /* ranges */,
"index_dives_for_eq_ranges": true, --是否使用index dive（详细描述请看下方的知识扩展）
"rowid_ordered": false, --使用该索引获取的记录是否按照主键排序
"using_mrr": false, --是否使用mrr
"index_only": false, --是否使用覆盖索引
"rows": 100, --使用该索引获取的记录数
"cost": 121.01, --使用该索引的成本
"chosen": true --可能选择该索引
},
{
"index": "idx_b", --使用索引idx_b的成本
"ranges": [
"910 < b"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 90,
"cost": 109.01,
"chosen": true --也可能选择该索引
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": { --分析使用索引合并的成本
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": { --确认最优方法
"range_access_plan": {
"type": "range_scan",
"index": "idx_b",
"rows": 90,
"ranges": [
"910 < b"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 90,
"cost_for_plan": 109.01,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [ --考虑的执行计划
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`t1`",
"best_access_path": { --最优的访问路径
"considered_access_paths": [ --决定的访问路径
{
"rows_to_scan": 90, --扫描的行数
"access_type": "range", --访问类型：为range
"range_details": {
"used_index": "idx_b" --使用的索引为：idx_b
} /* range_details */,
"resulting_rows": 90, --结果行数
"cost": 127.01, --成本
"chosen": true, --确定选择
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 90,
"cost_for_plan": 127.01,
"sort_cost": 90,
"new_cost_for_plan": 217.01,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": { --尝试添加一些其他的查询条件
"original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`t1`",
"attached": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`t1`.`a`",
"items": [
{
"item": "`t1`.`a`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`t1`.`a`"
} /* clause_processing */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"index_order_summary": {
"table": "`t1`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "idx_b",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [ --改进的执行计划
{
"table": "`t1`",
"pushed_index_condition": "(`t1`.`b` > 910)",
"table_condition_attached": "(`t1`.`a` > 900)"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { --SQL执行阶段
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`t1`",
"field": "a"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false, --未使用优先队列优化排序
"cause": "not applicable (no LIMIT)" --未使用优先队列排序的原因是没有limit
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": { --排序详情
"rows": 90,
"examined_rows": 90, --参与排序的行数
"number_of_tmp_files": 0, --排序过程中使用的临时文件数
"sort_buffer_size": 115056,
"sort_mode": "<sort_key, additional_fields>" --排序模式（详解请看下方知识扩展）
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 --该字段表示分析过程丢弃的文本字节大小，本例为0，说明没丢弃任何文本
INSUFFICIENT_PRIVILEGES: 0 --查看trace的权限是否不足，0表示有权限查看trace详情
1 row in set (0.00 sec)
------------------------------------------------
------------------------------------------------
mysql> set session optimizer_trace="enabled=off";
/* 及时关闭trace */
TRACE 字段中整个文本大致分为三个过程。
- 准备阶段：对应文本中的 join_preparation
- 优化阶段：对应文本中的 join_optimization
- 执行阶段：对应文本中的 join_execution
总结
三种分析 SQL 方法的特点：
- explain：获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等；
- profile：可以清楚了解到SQL到底慢在哪个环节；
- trace：查看优化器如何选择执行计划，获取每个可能的索引选择的代价。
