mysql对索引的选择简述；INFORMATION_SCHEMA.OPTIMIZER_TRACE使用简述

概述

在业务中经常会优化一些mysql的慢查询，通常都是使用explain去查看分析，检查扫描行数和索引的命中情况；

但是在具体索引的选择上，explain结果中并没有直接展示出来；
此时可以开启mysql的追踪优化器Trace功能；

我看了下众多的参数，觉得查看的时候其中几项是必看的
join_preparation是首先要关注的，我们的写的sql语句会经过这一步重新编排后给mysql执行；
其次，join_optimization中的rows_estimation也是重要的，其中展示了对索引选择的分析；
最后considered_execution_plans中展示了对实际选择的执行计划的分析；
其他的可以按需查看

-- 建议和业务sql一起执行
-- 在当前session中打开追踪优化器
SET session optimizer_trace="enabled=on"
-- json太长，在右括号后面加上注释
,end_markers_in_json=on; 

我的mysql版本是8.0.32

mysql的追踪优化器Trace Demo

SET session optimizer_trace="enabled=on"; -- 在当前session中打开追踪优化器
 
SET session optimizer_trace_max_mem_size = 1048576; -- 设置为1MB，根据实际情况调整

-- 和业务查询一起执行，查询mysql的索引比较
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;

截图中可以看到mysql在比较最合适的索引的依据

INFORMATION_SCHEMA.OPTIMIZER_TRACE使用简述

查询结果一般是4个参数

1. QUERY：查询语句
2. TRACE：QUERY字段对应语句的跟踪信息
3. MISSING_BYTES_BEYOND_MAX_MEM_SIZE：跟踪信息过长时，被截断的跟踪信息的字节数。
4. INSUFFICIENT_PRIVILEGES：执行跟踪语句的用户是否有查看对象的权限。当不具有权限时，该列信息为1且TRACE字段为空，一般在调用带有SQL SECURITY DEFINER的视图或者是存储过程的情况下，会出现此问题。

重要内容在trace中；

join_preparation

预优化细节如下

condition：优化对象类型;WHERE条件句或者是HAVING条件句
original_condition：优化前的原始语句
steps：主要包括三步分别是quality_propagation（等值条件句转换），constant_propagation（常量条件句转换）和trivial_condition_removal（无效条件移除的转换）

join_optimization

sql具体优化阶段的执行过程

condition_processing

主要对WHERE条件进行优化处理

condition

优化对象类型。WHERE条件句或者是HAVING条件句

original_condition

优化前的原始语句

steps

主要包括三步，分别是equality_propagation（等值条件句转换），constant_propagation（常量条件句转换），trivial_condition_removal（无效条件移除的转换）
transformation：转换类型句
resulting_condition：转换之后的结果输出

substitute_generated_columns`

用于替换虚拟生成列

table_dependencies

分析表之间的依赖关系

ref_optimizer_key_uses

列出所有可用的ref类型的索引,如果使用了组合索引的多个部分，则会列出多个元素，每个元素中会列出ref使用的索引及对应值

rows_estimation

估算需要扫描的记录数
rows_estimation中
1-table_scan：如果全表扫描的话，需要扫描多少行（row，123），以及需要的代价（cost，123）
2-potential_range_indexes：列出表中所有的索引并分析其是否可用。如果不可用的话，会列出不可用的原因是什么；如果可用会列出索引中可用的字段；
3-setup_range_conditions：如果有可下推的条件，则带条件考虑范围查询
4-group_index_range：当使用了GROUP BY或DISTINCT时，是否有合适的索引可用。当未使用时，会显示chosen=false, cause=not_group_by_or_distinct；如使用了,但是多表查询时，会显示chosen=false，cause =not_single_table。其他情况下会尝试分析可用的索引（potential_group_range_indexes）并计算对应的扫描行数及其所需代价
5-skip_scan_range：是否使用了skip scan;简单的来说就是当索引存储是有序的，此时条件符合的话会直接跳过不符合的数据段

6-analyzing_range_alternatives：分析各个索引的使用成本
6-1-range_scan_alternatives：range扫描分析
index：索引名
6-1-1-ranges：range扫描的条件范围
6-1-2-index_dives_for_eq_ranges：是否使用了index dive，该值会被参数eq_range_index_dive_limit变量值影响。
6-1-3-rowid_ordered：该range扫描的结果集是否根据PK值进行排序
6-1-4-using_mrr：是否使用了mrr
6-1-5-index_only：表示是否使用了覆盖索引
6-1-6-rows：扫描的行数
6-1-7-cost：索引的使用成本
6-1-8-chosen：表示是否使用了该索引

7-analyzing_roworder_intersect：分析是否使用了索引合并（index merge），如果未使用，会在cause中展示原因；如果使用了索引合并，会在该部分展示索引合并的代价。
8-chosen_range_access_summary：在前一个步骤中分析了各类索引使用的方法及代价，得出了一定的中间结果之后，在summary阶段汇总前一阶段的中间结果确认最后的方案
8-1-range_access_plan：range扫描最终选择的执行计划。
8-1-1-type：展示执行计划的type，如果使用了索引合并，则会显示index_roworder_intersect
8-1-2-index：索引名
8-1-3-rows：扫描的行数
8-1-4-ranges：range扫描的条件范围
8-1-5-rows_for_plan：该执行计划的扫描行数
8-1-6-cost_for_plan：该执行计划的执行代价
8-1-7-chosen：是否选择该执行计划

considered_execution_plans

负责对比各可行计划的开销，并选择相对最优的执行计划
1-plan_prefix：当前计划的前置执行计划。
2-table：涉及的表名，如果有别名，也会展示出来
3-best_access_path：通过对比considered_access_paths，选择一个最优的访问路径
3-1-considered_access_paths：当前考虑的访问路径
3-2-access_type：使用索引的方式，可参考explain中的type字段
3-3-index：索引
3-4-rows_to_scan：行数
3-5-cost：开销
3-5-chosen：是否选用这种执行路径
4-condition_filtering_pct：类似于explain的filtered列，是一个估算值
5-rows_for_plan：执行计划最终的扫描行数，由considered_access_paths.rows X condition_filtering_pct计算获得。
6-cost_for_plan：执行计划的代价，由considered_access_paths.cost相加获得
7-chosen：是否选择了该执行计划

attaching_conditions_to_tables

基于considered_execution_plans中选择的执行计划，改造原有where条件，并针对表增加适当的附加条件，以便于单表数据的筛选;
简单来说就是对单表的数据做提前过滤

original_condition：原始的条件语句
attached_conditions_computation：使用启发式算法计算已使用的索引，如果已使用的索引的访问类型是ref，则计算用range能否使用组合索引中更多的列，如果可以，则用range的方式替换ref
attached_conditions_summary：附加之后的情况汇总
table：表名
attached：附加的条件或原语句中能直接下推给单表筛选的条件。

finalizing_table_conditions

最终的、经过优化后的表条件

refine_plan

table：表名及别名

join_execution

join_execution段落展示了执行阶段的执行过程