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=1000000;
执行sql语句
select * from xuehao;
最后检查infomation_schema.optimizer_trace就可以知道mysql是如何执行sql的
mysql> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************QUERY: select * from xuehaoTRACE: { "steps": [{ "join_preparation": { "select#": 1,"steps": [{ "expanded_query": "/* select#1 */ select `xuehao`.`id` AS `id` from `xuehao`"}] /* steps */} /* join_preparation */},{ "join_optimization": { "select#": 1,"steps": [{ "table_dependencies": [{ "table": "`xuehao`","row_may_be_null": false,"map_bit": 0,"depends_on_map_bits": [] /* depends_on_map_bits */}] /* table_dependencies */},{ "rows_estimation": [{ "table": "`xuehao`","table_scan": { "rows": 8,"cost": 2} /* table_scan */}] /* rows_estimation */},{ "considered_execution_plans": [{ "plan_prefix": [] /* plan_prefix */,"table": "`xuehao`","best_access_path": { "considered_access_paths": [{ "access_type": "scan","rows": 8,"cost": 3.6137,"chosen": true}] /* considered_access_paths */} /* best_access_path */,"cost_for_plan": 3.6137,"rows_for_plan": 8,"chosen": true}] /* considered_execution_plans */},{ "attaching_conditions_to_tables": { "original_condition": null,"attached_conditions_computation": [] /* attached_conditions_computation */,"attached_conditions_summary": [{ "table": "`xuehao`","attached": null}] /* attached_conditions_summary */} /* attaching_conditions_to_tables */},{ "refine_plan": [{ "table": "`xuehao`","access_type": "table_scan"}] /* refine_plan */}] /* steps */} /* join_optimization */},{ "join_execution": { "select#": 1,"steps": [] /* steps */} /* join_execution */}] /* steps */}MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0INSUFFICIENT_PRIVILEGES: 01 row in set (0.01 sec)