# tract分析优化器执行计划 MySQL5.6提供了对SQL的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划。 ## 1.打开trace 打开trace ， 设置格式为 JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。 ~~~ SET optimizer_trace="enabled=on",end_markers_in_json=on; set optimizer_trace_max_mem_size=1000000; ~~~ **示例1：** ~~~ mysql> select * from city_innodb; +---------+-----------+------------+ | city_id | city_name | country_id | +---------+-----------+------------+ | 1 | Xian | 10 | | 2 | NewYork | 2 | | 3 | BeiJing | 10 | +---------+-----------+------------+ 3 rows in set (0.00 sec) mysql> select * from information_schema.optimizer_trace\G; *************************** 1. row *************************** QUERY: select * from city_innodb TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `city_innodb`.`city_id` AS `city_id`,`city_innodb`.`city_name` AS `city_name`,`city_innodb`.`country_id` AS `country_id` from `city_innodb`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`city_innodb`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`city_innodb`", "table_scan": { "rows": 3, "cost": 0.25 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`city_innodb`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 3, "access_type": "scan", "resulting_rows": 3, "cost": 0.55, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 3, "cost_for_plan": 0.55, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`city_innodb`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`city_innodb`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.00 sec) ERROR: No query specified ~~~