在mysql5.6中提供对sql的跟踪命令trace,通过trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划,能够帮助我们更好的理解优化器的行为。
使用方式,首先打开trace,设置格式为json,设置trace的最大使用内存大小,避免解析过程中因为默认内存过小而不能够完整显示。
set optimizer_trace=’enabled=on’,end_markers_in_json=on;
set optimizer_trace_max_mem_size=100000;
在分析完sql执行过程之后,您可以关闭trace,否则会影响性能
set session optimizer_trace="enabled=off"; #关闭
接下来执行您要跟踪的sql,比如:
select * from history where open in (11.6500,10.1,10.333) and date ='2001-12-18';
然后查询sql跟踪的结果:
select * from information_schema.optimizer_trace;
您可以将其中的json复制出来,使用json查看效果更好。下边是对执行结果的解释:
{
"steps": [
{
#准备阶段,将sql进行格式化。补全省略的字段
"join_preparation": {
"select#": 1,
"steps": [
{
"IN_uses_bip": true
},
{
"expanded_query": "/* select#1 */ select `history`.`date` AS `date`,`history`.`code` AS `code`,`history`.`open` AS `open`,`history`.`high` AS `high`,`history`.`low` AS `low`,`history`.`close` AS `close`,`history`.`preclose` AS `preclose`,`history`.`volume` AS `volume`,`history`.`amount` AS `amount`,`history`.`adjustflag` AS `adjustflag`,`history`.`turn` AS `turn`,`history`.`tradestatus` AS `tradestatus`,`history`.`pctChg` AS `pctChg`,`history`.`isST` AS `isST` from `history` where ((`history`.`open` in (11.6500,10.1,10.333)) and (`history`.`date` = '2001-12-18')) limit 0,500"
}
] /* steps */
} /* join_preparation */
},
{
#优化阶段
"join_optimization": {
"select#": 1,
"steps": [
{
#处理where条件优化
"condition_processing": {
#优化的阶段为where
"condition": "WHERE",
#优化前的语句
"original_condition": "((`history`.`open` in (11.6500,10.1,10.333)) and (`history`.`date` = '2001-12-18'))",
"steps": [
{
#等值条件转化
"transformation": "equality_propagation",
#类型转化之后的语句
"resulting_condition": "((`history`.`open` in (11.6500,10.1,10.333)) and (`history`.`date` = '2001-12-18'))"
},
{
#常量条件转化
"transformation": "constant_propagation",
#转化之后
"resulting_condition": "((`history`.`open` in (11.6500,10.1,10.333)) and (`history`.`date` = '2001-12-18'))"
},
{
#无效条件的移除
"transformation": "trivial_condition_removal",
"resulting_condition": "((`history`.`open` in (11.6500,10.1,10.333)) and (`history`.`date` = '2001-12-18'))"
}
] /* steps */
} /* condition_processing */
},
{
#用于替换虚拟的生成列
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
#表的依赖
"table_dependencies": [
{
#表名
"table": "`history`",
#join操作之后行是否可为null,如果是left join,则后一张表的row_may_be_null会显示为true
"row_may_be_null": false,
#表的映射编号,从0开始递增
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
#列出所有可用的ref类型的索引,如果使用了组合索引,该列会有多个元素
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
#估算需要扫描的记录数
"rows_estimation": [
{
"table": "`history`",
#全表扫描的话,需要扫描多少行,cost为代价
"table_scan": {
"rows": 4540,
"cost": 24.25
} /* table_scan */
#这里除此之外还会有potential_range_indexs字段,里边会列出可用的索引,或者不可索引的原因
#setup_range_conditions表示是否具有推理的情况,看看有没有可用的索引
#group_index_range当使用group by或者distinct的时候是否有合适的索引可以使用。
#skip_scan_range是否使用了skip scan,mysql8的新特性
#analyzing_range_alternatives各个索引使用的成本,rowid_ordered是否按pk进行排序,using_mrr是否使用mrr,index_only是否使用了覆盖索引,rows扫描的行数,cost索引的使用成本,chosen是否使用了该索引
#analyzing_roworder_intersect是否使用了合并索引
#chosen_range_access_summary分析各类索引使用的方法和代价,得出一个中间结果之后,在summary阶段汇总前一阶段的结果确认最终的方案
#
}
] /* rows_estimation */
},
{
#负责对比各可行计划的开销,并选择相对最优的执行计划
"considered_execution_plans": [
{
#前置计划
"plan_prefix": [
] /* plan_prefix */,
#表名
"table": "`history`",
#最优路径
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 4540,
#explain的type字段
"access_type": "scan",
"resulting_rows": 4540,
"cost": 478.25,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
#expalin的filtered列,是一个估算值
"condition_filtering_pct": 100,
#执行计划的代价
"rows_for_plan": 4540,
"cost_for_plan": 478.25,
#是否选择
"chosen": true
}
] /* considered_execution_plans */
},
{
#对上述计划执行改造原有的where条件,并针对适当的附加条件便于数据的筛选
"attaching_conditions_to_tables": {
"original_condition": "((`history`.`open` in (11.6500,10.1,10.333)) and (`history`.`date` = '2001-12-18'))",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
#汇总的情况
"attached_conditions_summary": [
{
"table": "`history`",
"attached": "((`history`.`open` in (11.6500,10.1,10.333)) and (`history`.`date` = '2001-12-18'))"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
#最终的经过优化后的表条件
"finalizing_table_conditions": [
{
"table": "`history`",
"original_table_condition": "((`history`.`open` in (11.6500,10.1,10.333)) and (`history`.`date` = '2001-12-18'))",
"final_table_condition ": "((`history`.`open` in (11.6500,10.1,10.333)) and (`history`.`date` = '2001-12-18'))"
}
] /* finalizing_table_conditions */
},
{
#改善计划
"refine_plan": [
{
"table": "`history`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
#展示执行阶段的执行过程
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}