vlambda博客
学习文章列表

 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 */ }