MySql学习笔记（六） - 使用trace分析Sql

 set optimizer_trace=’enabled=on’,end_markers_in_json=on;
 set optimizer_trace_max_mem_size=100000;

set session optimizer_trace="enabled=off"; #关闭

select * from history where open in (11.6500,10.1,10.333) and date ='2001-12-18';

select * from information_schema.optimizer_trace;

{
   "steps": [
     {
        #准备阶段，将sql进行格式化。补全省略的字段
       "join_preparation": {
         "select#": 1,
         "steps": [
           {
             "IN_uses_bisection": 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 */
 }