前言

平常用 explain 就可以sql语句的性能了。但是，如果你觉得分析的不够详尽，你想查看内部的拆解过程，那么你可以试试用mysql优化器进行追踪分析

相关链接

explain 详解：https://blog.mailjob.net/posts/183747545.html

skip_scan_range（MySQL 8.0的新特性）：https://blog.csdn.net/weixin_43970890/article/details/89494915

操作方法

1
2
3
4
set optimizer_trace="enabled=on"; -- 开启trace查看优化器的结果
set end_markers_in_json=on; -- 增加注释
#sql query#; -- sql 执行语句
select * from information_schema.optimizer_trace \G; -- 查看分析结果

执行结果包含内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
1.join_preparation ：准备阶段，包查询语句转换，转换成嵌套循环语句等

   expanded_query
   transformations_to_nested_joins

2.join_optimization ：优化阶段，包括以下主要阶段

  condition_processing ：处理where条件部分，主要包括等式处理、常量处理、多余条件处理
  table_dependencies ：表依赖检查
  ref_optimizer_key_uses ：评估可用的索引
  rows_estimation        ：评估访问单表的方式，及扫描的行数与代价
  considered_execution_plans ：评估最终可使用的执行计划
  condition_on_constant_tables :检查带常量表的条件
  attaching_conditions_to_tables ：将常量条件作用到表
  refine_plan 改进计划，不理解

3.join_execution  ：执行阶段

优化器内容结果分析

explain 分析

以下 left` `join语句，d表与s表关联，当where条件在d.deptid上时，s表无法走索引。因此通过开启trace方式做一些追踪。

1
2
3
4
5
6
7
root@(none) 09:20:20>explain SELECT * FROM SSS.DEPARTMENT d LEFT JOIN ppp.shop s ON d.DEPTID = s.DEPTID WHERE d.DEPTID = '00001111';
+----+-------------+-------+------------+-------+----------------------------+---------+---------+-------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys              | key     | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+----------------------------+---------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | d     | NULL       | const | PRIMARY,INDEX_DEPARTMENT_5 | PRIMARY | 130     | const |      1 |   100.00 | NULL        |
|  1 | SIMPLE      | s     | NULL       | ALL   | NULL                       | NULL    | NULL    | NULL  | 978629 |   100.00 | Using where |
+----+-------------+-------+------------+-------+----------------------------+---------+---------+-------+--------+----------+-------------+

优化器分析

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
root@(none) 09:39:58> select * from information_schema.optimizer_trace\G;
*************************** 1. row ***************************
                            QUERY: SELECT * FROM SSS.DEPARTMENT d LEFT JOIN ppp.shop s ON d.DEPTID = s.DEPTID WHERE d.DEPTID = '00001111'
                            TRACE: {
  "steps": [
   
  #准备阶段
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {  
          #expanded_query，解析查询语句，"*" 转换成字段，left join on 处转化成on((`SSS`.`d`.`Deptid` = convert(`ppp`.`s`.`Deptid` using utf8mb4))))
            "expanded_query": "/* select#1 */ select `SSS`.`d`.`Organid` AS `Organid`,。。。`s`.`Status` AS `Status`,`ppp`.`s`.`Stylecategoryid` AS `Stylecategoryid`,`ppp`.`s`.`Turnontime` AS `Turnontime` from (`SSS`.`department` `d` left join `ppp`.`shop` `s` on((`SSS`.`d`.`Deptid` = convert(`ppp`.`s`.`Deptid` using utf8mb4)))) where (`SSS`.`d`.`Deptid` = '00001111')"
          },
          {
          #转化成的nested join语句：
            "transformations_to_nested_joins": {
              "transformations": [
                "parenthesis_removal"
              ] /* transformations */,
              "expanded_query": "/* select#1 */ select `SSS`.`d`.`Organid`。。。 `SSS`.`d`.`Guidecode` AS `Guidecode`,`SSS`.`d`.`Createdate` AS `Createdate`,`SSS`.`d`.`Plateformuser` AS `Plateformuser`,`SSS`.`d`.`Plateformdept` AS `Plateformdept`,`SSS`.`d`.`Agentuser` AS `Agentuser`,`SSS`.`d`.`Agentdept` AS `Agentdept`,`SSS`.`d`.`Shopstatus` AS `Shopstatus`,`SSS`.`d`.`Deptshortname` AS `Deptshortname`,`SSS`.`d`.`Storetype` AS `Storetype`,`SSS`.`d`.`Depttype` AS `Depttype`,`ppp`.`s`.`Shopid` AS `Shopid`,`ppp`.`s`.`Objectid` AS `Objectid`,`ppp`.`s`.`Shopname` AS `Shopname`,`ppp`Tel`,`ppp`.`s`.`Introduce` AS `Introduce`,`ppp`.`s`.`Industry` AS `Industry`,`ppp`.`s`.`Address` AS `Address`,`ppp`.`s`.`Shop360image` AS `Shop360image`,`ppp`.`s`.`Domain` AS `Domain`,`ppp`.`s`.`Organid` AS `Organid`,`ppp`.`s`.`Deptid` AS `Deptid`,`ppp`.`s`.`Brandids` AS `Brandids`,`ppp`.`s`.`Extdata` AS `Extdata`,`ppp`.`s`.`Ranking` AS `Ranking`,`ppp`.`s`.`Isdelete` AS `Isdelete`,`ppp`.`s`.`District` AS `District`,`ppp`.`s`.`City` AS `City`,`ppp`.`s`.`Province` AS `Province`,`ppp`.`s`.`Phone` AS `Phone`,`ppp`.`s`.`Watermarkimage` AS `Watermarkimage`,`ppp`.`s`.`Drawingimage` AS `Drawingimage`,`ppp`.`s`.`Contactuser` AS `Contactuser`,`ppp`.`s`.`Panoloadingimage` AS `Panoloadingimage`,`ppp`.`s`.`Lngandlat` AS `Lngandlat`,`ppp`.`s`.`Createtime` AS `Createtime`,`ppp`.`s`.`Shoptype` AS `Shoptype`,`ppp`.`s`.`Status` AS `Status`,`ppp`.`s`.`Stylecategoryid` AS `Stylecategoryid`,`ppp`.`s`.`Turnontime` AS `Turnontime` from `SSS`.`department` `d` left join `ppp`.`shop` `s` on((`SSS`.`d`.`Deptid` = convert(`ppp`.`s`.`Deptid` using utf8mb4))) where (`SSS`.`d`.`Deptid` = '00001111')"
            } /* transformations_to_nested_joins */
          }
        ] /* steps */
      } /* join_preparation */
    },#准备阶段结束
     
     
    {
    #优化阶段：
      "join_optimization": {
        "select#": 1,
        "steps": [
          { 
          #处理where条件部分，化简条件：
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`SSS`.`d`.`Deptid` = '00001111')",---原始条件
              "steps": [
                {
                  "transformation": "equality_propagation", ----等式处理
                  "resulting_condition": "(`SSS`.`d`.`Deptid` = '00001111')"
                },
                {
                  "transformation": "constant_propagation",-----常量处理
                  "resulting_condition": "(`SSS`.`d`.`Deptid` = '00001111')"
                },
                {
                  "transformation": "trivial_condition_removal",----去除多余无关的条件处理
                  "resulting_condition": "(`SSS`.`d`.`Deptid` = '00001111')"
                }
              ] /* steps */
            } /* condition_processing */
          },#结束，因为这里已经够简化了，所以三次处理后都是同样的。
           
          {
          #替代产生的字段
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
           
          {
          #表依赖关系检查
            /*
            table：涉及的表名，如果有别名，也会展示出来
            row_may_be_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值。
            */
            "table_dependencies": [
              {
                "table": "`SSS`.`department` `d`", ------表d
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`ppp`.`shop` `s`", --------表s
                "row_may_be_null": true,
                "map_bit": 1,
                "depends_on_map_bits": [
                  0
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          }, #表依赖关系检查结束
           
           
          {#找出可使用索引的字段：
            "ref_optimizer_key_uses": [
              {
                "table": "`SSS`.`department` `d`",
                "field": "Deptid", ----------可用的是Deptid
                "equals": "'00001111'",
                "null_rejecting": false ---
              },
              {
                "table": "`SSS`.`department` `d`",
                "field": "Deptid",
                "equals": "'00001111'",
                "null_rejecting": false
              }
            ] /* ref_optimizer_key_uses */
          },
           
          {#评估每个表单表访问行数及相应代价。
            "rows_estimation": [
              {
                "table": "`SSS`.`department` `d`",
                "rows": 1,   ---返回1行
                "cost": 1,   ---代价为1
                "table_type": "const",  ---d表使用的方式是const，即根据主键索引获取
                "empty": false
              },
              {
                "table": "`ppp`.`shop` `s`",
                "table_scan": { -------s表直接使用全表扫描
                  "rows": 978662, ------扫描978662行
                  "cost": 8109    ------代价为8109
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
           
           
          {#评估执行计划，这里考虑两表连接(负责对比各可行计划的开销，并选择相对最优的执行计划)
            "considered_execution_plans": [
              {
                "plan_prefix": [------------------执行计划的前缀，这里是d表，因为是left join 我认为指的应该是驱动表的意思
                  "`SSS`.`department` `d`"
                ] /* plan_prefix */,
                "table": "`ppp`.`shop` `s`",
                "best_access_path": {-------最优访问路径
                  "considered_access_paths": [考虑的访问路径
                    {
                      "rows_to_scan": 978662,---扫描978662行
                      "access_type": "scan",--------全表扫描的方式
                      "resulting_rows": 978662,
                      "cost": 203841,----------使用代价
                      "chosen": true-------选中
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,条件过滤率100%，指的是这里与上一个表进行行过滤的行数
                "rows_for_plan": 978662,------执行计划的扫描行数978662
                "cost_for_plan": 203841,-------执行计划的cost203841
                "chosen": true---------选中
              }
            ] /* considered_execution_plans */
          },
           
           
          {#检查带常量表的条件
            "condition_on_constant_tables": "('00001111' = '00001111')",
            "condition_value": true
          },
           
           
          {   #将常量条件作用到表，这里主要是将d表的中的deptid条件作用到s表的deptid
            "attaching_conditions_to_tables": {
              "original_condition": "('00001111' = '00001111')",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`ppp`.`shop` `s`",
                  "attached": "<if>(is_not_null_compl(s), ('00001111' = convert(`ppp`.`s`.`Deptid` using utf8mb4)), true)"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
           
           
          { # 改善执行计划
            "refine_plan": [
              {
                "table": "`ppp`.`shop` `s`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
     
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
     
  ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
rows_estimation 说明
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
table：表名
range_analysis：
table_scan：如果全表扫描的话，需要扫描多少行（row，2838216），以及需要的代价（cost，
286799）
potential_range_indexes：列出表中所有的索引并分析其是否可用。如果不可用的话，会列出不
可用的原因是什么；如果可用会列出索引中可用的字段；
setup_range_conditions：如果有可下推的条件，则带条件考虑范围查询
group_index_range：当使用了GROUP BY或DISTINCT时，是否有合适的索引可用。当未使用GROUP
BY或DISTINCT时，会显示chosen=false, cause=not_group_by_or_distinct；如使用了GROUP BY或
DISTINCT，但是多表查询时，会显示chosen=false，cause =not_single_table。其他情况下会尝试
分析可用的索引（potential_group_range_indexes）并计算对应的扫描行数及其所需代价
skip_scan_range：是否使用了skip scan
TIPS skip_scan_range是MySQL 8.0的新特性，感兴趣的可详见
https://blog.csdn.net/weixin_43970890/article/details/89494915
analyzing_range_alternatives：分析各个索引的使用成本
range_scan_alternatives：range扫描分析
index：索引名
ranges：range扫描的条件范围
index_dives_for_eq_ranges：是否使用了index dive，该值会被参数
eq_range_index_dive_limit变量值影响。
rowid_ordered：该range扫描的结果集是否根据PK值进行排序
using_mrr：是否使用了mrr
index_only：表示是否使用了覆盖索引
rows：扫描的行数
cost：索引的使用成本
chosen：表示是否使用了该索引
analyzing_roworder_intersect：分析是否使用了索引合并（index merge），如果未使用，
会在cause中展示原因；如果使用了索引合并，会在该部分展示索引合并的代价。
chosen_range_access_summary：在前一个步骤中分析了各类索引使用的方法及代价，得出了
一定的中间结果之后，在summary阶段汇总前一阶段的中间结果确认最后的方案
range_access_plan：range扫描最终选择的执行计划。
type：展示执行计划的type，如果使用了索引合并，则会显示index_roworder_intersect
index：索引名
rows：扫描的行数
ranges：range扫描的条件范围
rows_for_plan：该执行计划的扫描行数
cost_for_plan：该执行计划的执行代价
chosen：是否选择该执行计划
considered_execution_plans 说明
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
plan_prefix：当前计划的前置执行计划。
table：涉及的表名，如果有别名，也会展示出来
best_access_path：通过对比considered_access_paths，选择一个最优的访问路径
considered_access_paths：当前考虑的访问路径
access_type：使用索引的方式，可参考explain中的type字段
index：索引
rows：行数
cost：开销
chosen：是否选用这种执行路径
condition_filtering_pct：类似于explain的filtered列，是一个估算值
rows_for_plan：执行计划最终的扫描行数，由considered_access_paths.rows X
condition_filtering_pct计算获得。
cost_for_plan：执行计划的代价，由considered_access_paths.cost相加获得
chosen：是否选择了该执行计划
attaching_conditions_to_tables
基于considered_execution_plans中选择的执行计划，改造原有where条件，并针对表增加适当的附
加条件，以便于单表数据的筛选。

**TIPS **

这部分条件的增加主要是为了便于ICP（索引条件下推），但ICP是否开启并不影响这部
分内容的构造。 ICP参考文档：https://www.cnblogs.com/Terry-Wu/p/9273177.html

attaching_conditions_to_tables 说明
1
2
3
4
5
6
original_condition：原始的条件语句
attached_conditions_computation：使用启发式算法计算已使用的索引，如果已使用的索引的访问
类型是ref，则计算用range能否使用组合索引中更多的列，如果可以，则用range的方式替换ref。
attached_conditions_summary：附加之后的情况汇总
table：表名
attached：附加的条件或原语句中能直接下推给单表筛选的条件。
finalizing_table_conditions 说明
1
2
3
4
5
6
7
8
9
10
11
12
最终的、经过优化后的表条件

{
    "finalizing_table_conditions": [
    {
    "table": "`salaries`",
    "original_table_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and
    (`salaries`.`from_date` = DATE'1986-06-26'))",
    "final_table_condition ": null
    }
    ] /* finalizing_table_conditions */
}