mysql> set session optimizer_trace=’enabled=on';
| select * from sbtest1 order by k limit 3 | { “steps”: [ { “join_preparation”: { “select#”: 1, “steps”: [ { “expanded_query”: “/* select#1 */ select `sbtest1`.`id` AS `id`,`sbtest1`.`k` AS `k`,`sbtest1`.`c` AS `c`, `sbtest1`.`pad` AS `pad` from `sbtest1` order by `sbtest1`.`k` limit 3″ } ] } }, { “join_optimization”: { “select#”: 1, “steps”: [ { “table_dependencies”: [ { “table”: “`sbtest1`”, “row_may_be_null”: false, “map_bit”: 0, “depends_on_map_bits”: [ ] } ] }, { “rows_estimation”: [ { “table”: “`sbtest1`”, “table_scan”: { “rows”: 986400, “cost”: 13741 } } ] }, { “considered_execution_plans”: [ { “plan_prefix”: [ ], “table”: “`sbtest1`”, “best_access_path”: { “considered_access_paths”: [ { “access_type”: “scan”, “rows”: 986400, “cost”: 211021, “chosen”: true } ] }, “cost_for_plan”: 211021, “rows_for_plan”: 986400, “chosen”: true } ] }, { “attaching_conditions_to_tables”: { “original_condition”: null, “attached_conditions_computation”: [ ], “attached_conditions_summary”: [ { “table”: “`sbtest1`”, “attached”: null } ] } }, { “clause_processing”: { “clause”: “ORDER BY”, “original_clause”: “`sbtest1`.`k`”, “items”: [ { “item”: “`sbtest1`.`k`” } ], “resulting_clause_is_simple”: true, “resulting_clause”: “`sbtest1`.`k`” } }, { “refine_plan”: [ { “table”: “`sbtest1`”, “access_type”: “table_scan” } ] }, { “reconsidering_access_paths_for_index_ordering”: { “clause”: “ORDER BY”, “index_order_summary”: { “table”: “`sbtest1`”, “index_provides_order”: true, “order_direction”: “asc”, “index”: “k”, “plan_changed”: true, “access_type”: “index_scan” } } } ] } }, { “join_execution”: { “select#”: 1, “steps”: [ ] } } ] } | ############################################################### 主要分为三个部分 join_preparation:SQL的准备阶段,sql被格式化 对应函数 JOIN::prepare 例如 * 被扩展开来 join_optimization:SQL优化阶段 对应函数JOIN::optimize join_execution:SQL执行阶段 对应函数:JOIN::exec |
optimizer_trace有两个字段:
offset=-5,limit=5 将最近的5次trace打印出来
当offset小于0时,则会显示最新的-offset开始的limit个trace,也就是说,只显示新的trace
注意重设变量会导致trace被清空
mysql> show variables like ‘optimizer_trace_features';
原创文章,转载请注明:转载自Simple Life
文章的脚注信息由WordPress的wp-posturl插件自动生成