注：当前测试mysql版本：mysql5.7，编码utf8mb4

trace是MySQL5.6版本后提供的SQL跟踪工具，通过使用trace可以让我们明白optimizer（优化器）如何选择执行计划。

注意：开启trace工具会影响mysql性能，所以只适合临时分析sql使用，用完之后请立即关闭。

测试数据脚本：

[code]DROP TABLE IF EXISTS `t_student`;CREATE TABLE `t_student` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`std_name` varchar(30) NOT NULL,`age` tinyint(3) unsigned NOT NULL,`class_id` int(11) unsigned NOT NULL,`gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`id`),KEY `idx_std_age` (`age`),KEY `idx_std_name_age_class` (`std_name`,`age`,`class_id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=100766 DEFAULT CHARSET=utf8mb4;--添加测试数据的存储过程drop procedure if exists proc_insert_student;create procedure proc_insert_student()begindeclare i int;set i=1;while(i<=100000)doinsert into t_student(std_name,age,class_id) values(CONCAT(\'Li Lei\',i), (i mod 120)+1 ,(i mod 3)+1);set i=i+1;end while;end;-- 执行存储过程call proc_insert_student();

trace工具用法

一.查看trace开关状态，默认关闭的

[code]show variables like \'optimizer_trace\';

二.开启face

1.会话级别临时开启，只在当前会话生效。

[code]set session optimizer_trace=\"enabled=on\",end_markers_in_json=on;

2.永久开启（重启失效）

注意用完关闭

[code]set optimizer_trace=\"enabled=on\";

三.用法

在查询sql后加上固定sql，例：

[code]set session optimizer_trace=\"enabled=on\",end_markers_in_json=on;select * from t_student where std_name > \'a\' order by age;SELECT * FROM information_schema.OPTIMIZER_TRACE;

执行结果：

把TRACE列的json数据拷贝出来查看。

[code]{\"steps\": [{\"join_preparation\": {  -- 第一阶段：SQL准备阶段，格式化sql\"select#\": 1,\"steps\": [{\"expanded_query\": \"/* select#1 */ select `t_student`.`id` AS `id`,`t_student`.`std_name` AS `std_name`,`t_student`.`age` AS `age`,`t_student`.`class_id` AS `class_id`,`t_student`.`gmt_create` AS `gmt_create` from `t_student` where (`t_student`.`std_name` > \'a\') order by `t_student`.`age`\"}] /* steps */} /* join_preparation */},{\"join_optimization\": {  -- 第二阶段：SQL优化阶段\"select#\": 1,\"steps\": [{\"condition_processing\": {  -- 条件处理\"condition\": \"WHERE\",\"original_condition\": \"(`t_student`.`std_name` > \'a\')\",\"steps\": [{\"transformation\": \"equality_propagation\",\"resulting_condition\": \"(`t_student`.`std_name` > \'a\')\"},{\"transformation\": \"constant_propagation\",\"resulting_condition\": \"(`t_student`.`std_name` > \'a\')\"},{\"transformation\": \"trivial_condition_removal\",\"resulting_condition\": \"(`t_student`.`std_name` > \'a\')\"}] /* steps */} /* condition_processing */},{\"substitute_generated_columns\": {} /* substitute_generated_columns */},{\"table_dependencies\": [  -- 表依赖详情{\"table\": \"`t_student`\",\"row_may_be_null\": false,\"map_bit\": 0,\"depends_on_map_bits\": [] /* depends_on_map_bits */}] /* table_dependencies */},{\"ref_optimizer_key_uses\": [] /* ref_optimizer_key_uses */},{\"rows_estimation\": [  -- 预估表的访问成本{\"table\": \"`t_student`\",\"range_analysis\": {\"table_scan\": {   -- 全表扫描\"rows\": 100300,  -- 行数\"cost\": 20351  -- 查询消耗} /* table_scan */,\"potential_range_indexes\": [  -- 查询可能使用的索引{\"index\": \"PRIMARY\",  -- 主键索引\"usable\": false,  -- 未使用\"cause\": \"not_applicable\"  -- 原因：不适合},{\"index\": \"idx_std_age\",  -- age索引\"usable\": false,  -- 未使用\"cause\": \"not_applicable\"  -- 原因：不适合},{\"index\": \"idx_std_name_age_class\",  -- stdname,age,class的组合索引\"usable\": true,  -- 使用\"key_parts\": [\"std_name\",\"age\",\"class_id\",\"id\"] /* key_parts */}] /* potential_range_indexes */,\"setup_range_conditions\": [] /* setup_range_conditions */,\"group_index_range\": {  -- group 用到的索引\"chosen\": false,  -- 未使用\"cause\": \"not_group_by_or_distinct\"  -- 原因：未使用group by 或者 distinct} /* group_index_range */,\"analyzing_range_alternatives\": {   -- 分析各个索引使用成本\"range_scan_alternatives\": [{\"index\": \"idx_std_name_age_class\",\"ranges\": [\"a < std_name\"  -- 索引使用范围] /* ranges */,\"index_dives_for_eq_ranges\": true,\"rowid_ordered\": false,  -- 使用该索引获取的记录是否按照主键排序\"using_mrr\": false,\"index_only\": false,  -- 是否使用覆盖索引\"rows\": 50150,  -- 索引扫描行数\"cost\": 60181,   -- 索引使用成本\"chosen\": false,  -- 是否选择该索引：否\"cause\": \"cost\"  -- 原因：消耗}] /* range_scan_alternatives */,\"analyzing_roworder_intersect\": {  -- 分析使用索引合并的成本\"usable\": false,\"cause\": \"too_few_roworder_scans\"} /* analyzing_roworder_intersect */} /* analyzing_range_alternatives */} /* range_analysis */}] /* rows_estimation */},{\"considered_execution_plans\": [  -- 分析出的执行计划{\"plan_prefix\": [] /* plan_prefix */,\"table\": \"`t_student`\",\"best_access_path\": {  -- 最优访问路径\"considered_access_paths\": [  --分析出的最终访问路径{\"rows_to_scan\": 100300,\"access_type\": \"scan\",  -- 访问类型：为scan，全表扫描\"resulting_rows\": 100300,\"cost\": 20349,\"chosen\": true,  -- 确定选择\"use_tmp_table\": true}] /* considered_access_paths */} /* best_access_path */,\"condition_filtering_pct\": 100,\"rows_for_plan\": 100300,\"cost_for_plan\": 20349,\"sort_cost\": 100300,\"new_cost_for_plan\": 120649,\"chosen\": true}] /* considered_execution_plans */},{\"attaching_conditions_to_tables\": {   -- 为查询的表添加条件\"original_condition\": \"(`t_student`.`std_name` > \'a\')\",\"attached_conditions_computation\": [] /* attached_conditions_computation */,\"attached_conditions_summary\": [    -- 添加条件结果{\"table\": \"`t_student`\",\"attached\": \"(`t_student`.`std_name` > \'a\')\"}] /* attached_conditions_summary */} /* attaching_conditions_to_tables */},{\"clause_processing\": {   -- order by 处理\"clause\": \"ORDER BY\",\"original_clause\": \"`t_student`.`age`\",\"items\": [{\"item\": \"`t_student`.`age`\"}] /* items */,\"resulting_clause_is_simple\": true,\"resulting_clause\": \"`t_student`.`age`\"} /* clause_processing */},{\"reconsidering_access_paths_for_index_ordering\": {    -- 重构索引处理顺序\"clause\": \"ORDER BY\",\"steps\": [] /* steps */,\"index_order_summary\": {\"table\": \"`t_student`\",\"index_provides_order\": false,\"order_direction\": \"undefined\",\"index\": \"unknown\",\"plan_changed\": false} /* index_order_summary */} /* reconsidering_access_paths_for_index_ordering */},{\"refine_plan\": [{\"table\": \"`t_student`\"}] /* refine_plan */}] /* steps */} /* join_optimization */},{\"join_execution\": {    -- 第三阶段：SQL执行阶段\"select#\": 1,\"steps\": [{\"filesort_information\": [{\"direction\": \"asc\",\"table\": \"`t_student`\",\"field\": \"age\"}] /* filesort_information */,\"filesort_priority_queue_optimization\": {\"usable\": false,\"cause\": \"not applicable (no LIMIT)\"} /* filesort_priority_queue_optimization */,\"filesort_execution\": [] /* filesort_execution */,\"filesort_summary\": {\"rows\": 100000,\"examined_rows\": 100000,\"number_of_tmp_files\": 14,\"sort_buffer_size\": 262016,\"sort_mode\": \"<sort_key, packed_additional_fields>\"} /* filesort_summary */}] /* steps */} /* join_execution */}] /* steps */}