前言
平常用 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
操作方法
set optimizer_trace="enabled=on"; -- 开启trace查看优化器的结果
set end_markers_in_json=on; -- 增加注释
#sql query#; -- sql 执行语句
select * from information_schema.optimizer_trace \G; -- 查看分析结果执行结果包含内容
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方式做一些追踪。
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 |
+----+-------------+-------+------------+-------+----------------------------+---------+---------+-------+--------+----------+-------------+优化器分析
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 说明
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 说明
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 说明
original_condition：原始的条件语句
attached_conditions_computation：使用启发式算法计算已使用的索引，如果已使用的索引的访问
类型是ref，则计算用range能否使用组合索引中更多的列，如果可以，则用range的方式替换ref。
attached_conditions_summary：附加之后的情况汇总
table：表名
attached：附加的条件或原语句中能直接下推给单表筛选的条件。finalizing_table_conditions 说明
最终的、经过优化后的表条件
{
"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 */
}