Mysql之 optimizer_trace 相关总结

Mysql之 optimizer_trace 相关总结

MySQL官网介绍：https://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_OPT_TRACE.html

---

1. 简介

MySQL优化器可以生成Explain执行计划，通过执行计划查看sql是否使用了索引，使用了哪种索；
但是有些时候，你会发现为什么没想按照我们所想的思路执行：
为什么会使用这个索引 ？！
为什么没有使用添加的索引 ？！

于是，MySQL5.6版本之后开始引入 optimizer trace（优化器追踪），它可以查看优化器生成执行计划的整个过程，以及做出的各种决策，包括访问表的方法、各种开销计算、各种转换等等，帮助我们更好的去优化sql。

另外，optimizer_trace的开关默认是关闭的 ，开启trace工具会影响mysql性能，所以只适合临时分析sql使用，用完之后最好及时关闭。

---

2. 使用方法

1. 查看optimizer trace配置

show variables like '%optimizer_trace%';

查询结果：

查询结果字段说明：

• optimizer_trace： 主配置，enabled的on表示开启，off表示关闭，one_line表示是否展示成一行
• optimizer_trace_features： 表示优化器的可选特性，包括贪心搜索、范围优化等
• optimizer_trace_limit： 表示优化器追踪最大显示数目，默认是1条
• optimizer_trace_max_mem_size： 表示优化器追踪占用的最大容量
• optimizer_trace_offset： 表示显示的第一个优化器追踪的偏移量

---

2. 开启/关闭 optimizer trace

#开启trace
set session optimizer_trace="enabled=on",end_markers_in_json=on;
#关闭trace
set session optimizer_trace="enabled=off";

---

3. 执行需要进行分析的SQL语句

select * from test0816 where name > 'a' order by remark;

---

4. 使用optimizer trace查看优化器的选择过程

SELECT * FROM information_schema.OPTIMIZER_TRACE;

查询结果：

查询结果对应字段说明：

• QUERY： 表示我们执行的查询语句
• TRACE： 优化器生成执行计划的过程（重点关注）
• MISSING_BYTES_BEYOND_MAX_MEM_SIZE： 优化过程其余的信息会被显示在这一列
• INSUFFICIENT_PRIVILEGES： 表示是否有权限查看优化过程，0是，1否

---

5. 分析

trace的内容：

{"steps": [{"join_preparation": {"select#": 1,"steps": [{"expanded_query": "/* select#1 */ select `test0816`.`id` AS `id`,`test0816`.`name` AS `name`,`test0816`.`age` AS `age`,`test0816`.`remark` AS `remark`,`test0816`.`create_time` AS `create_time` from `test0816` where (`test0816`.`name` > 'a') order by `test0816`.`remark`"}] /* steps */} /* join_preparation */},{"join_optimization": {"select#": 1,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`test0816`.`name` > 'a')","steps": [{"transformation": "equality_propagation","resulting_condition": "(`test0816`.`name` > 'a')"},{"transformation": "constant_propagation","resulting_condition": "(`test0816`.`name` > 'a')"},{"transformation": "trivial_condition_removal","resulting_condition": "(`test0816`.`name` > 'a')"}] /* steps */} /* condition_processing */},{"substitute_generated_columns": {} /* substitute_generated_columns */},{"table_dependencies": [{"table": "`test0816`","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": "`test0816`","range_analysis": {"table_scan": {"rows": 3,"cost": 2.65} /* table_scan */,"potential_range_indexes": [{"index": "PRIMARY","usable": false,"cause": "not_applicable"},{"index": "idx_name_age_remark","usable": true,"key_parts": ["name","age","remark","id"] /* key_parts */}] /* potential_range_indexes */,"setup_range_conditions": [] /* setup_range_conditions */,"group_index_range": {"chosen": false,"cause": "not_group_by_or_distinct"} /* group_index_range */,"skip_scan_range": {"potential_skip_scan_indexes": [{"index": "idx_name_age_remark","usable": false,"cause": "query_references_nonkey_column"}] /* potential_skip_scan_indexes */} /* skip_scan_range */,"analyzing_range_alternatives": {"range_scan_alternatives": [{"index": "idx_name_age_remark","ranges": ["a < name"] /* ranges */,"index_dives_for_eq_ranges": true,"rowid_ordered": false,"using_mrr": false,"index_only": false,"in_memory": 1,"rows": 3,"cost": 1.31,"chosen": true}] /* range_scan_alternatives */,"analyzing_roworder_intersect": {"usable": false,"cause": "too_few_roworder_scans"} /* analyzing_roworder_intersect */} /* analyzing_range_alternatives */,"chosen_range_access_summary": {"range_access_plan": {"type": "range_scan","index": "idx_name_age_remark","rows": 3,"ranges": ["a < name"] /* ranges */} /* range_access_plan */,"rows_for_plan": 3,"cost_for_plan": 1.31,"chosen": true} /* chosen_range_access_summary */} /* range_analysis */}] /* rows_estimation */},{"considered_execution_plans": [{"plan_prefix": [] /* plan_prefix */,"table": "`test0816`","best_access_path": {"considered_access_paths": [{"rows_to_scan": 3,"access_type": "range","range_details": {"used_index": "idx_name_age_remark"} /* range_details */,"resulting_rows": 3,"cost": 1.61,"chosen": true,"use_tmp_table": true}] /* considered_access_paths */} /* best_access_path */,"condition_filtering_pct": 100,"rows_for_plan": 3,"cost_for_plan": 1.61,"sort_cost": 3,"new_cost_for_plan": 4.61,"chosen": true}] /* considered_execution_plans */},{"attaching_conditions_to_tables": {"original_condition": "(`test0816`.`name` > 'a')","attached_conditions_computation": [] /* attached_conditions_computation */,"attached_conditions_summary": [{"table": "`test0816`","attached": "(`test0816`.`name` > 'a')"}] /* attached_conditions_summary */} /* attaching_conditions_to_tables */},{"optimizing_distinct_group_by_order_by": {"simplifying_order_by": {"original_clause": "`test0816`.`remark`","items": [{"item": "`test0816`.`remark`"}] /* items */,"resulting_clause_is_simple": true,"resulting_clause": "`test0816`.`remark`"} /* simplifying_order_by */} /* optimizing_distinct_group_by_order_by */},{"reconsidering_access_paths_for_index_ordering": {"clause": "ORDER BY","steps": [] /* steps */,"index_order_summary": {"table": "`test0816`","index_provides_order": false,"order_direction": "undefined","index": "idx_name_age_remark","plan_changed": false} /* index_order_summary */} /* reconsidering_access_paths_for_index_ordering */},{"finalizing_table_conditions": [{"table": "`test0816`","original_table_condition": "(`test0816`.`name` > 'a')","final_table_condition   ": "(`test0816`.`name` > 'a')"}] /* finalizing_table_conditions */},{"refine_plan": [{"table": "`test0816`","pushed_index_condition": "(`test0816`.`name` > 'a')","table_condition_attached": null}] /* refine_plan */},{"considering_tmp_tables": [{"adding_sort_to_table": "test0816"} /* filesort */] /* considering_tmp_tables */}] /* steps */} /* join_optimization */},{"join_execution": {"select#": 1,"steps": [{"sorting_table": "test0816","filesort_information": [{"direction": "asc","expression": "`test0816`.`remark`"}] /* filesort_information */,"filesort_priority_queue_optimization": {"usable": false,"cause": "not applicable (no LIMIT)"} /* filesort_priority_queue_optimization */,"filesort_execution": [] /* filesort_execution */,"filesort_summary": {"memory_available": 262144,"key_size": 400,"row_size": 1091,"max_rows_per_buffer": 15,"num_rows_estimate": 15,"num_rows_found": 3,"num_initial_chunks_spilled_to_disk": 0,"peak_memory_used": 32800,"sort_algorithm": "std::sort","sort_mode": "<fixed_sort_key, packed_additional_fields>"} /* filesort_summary */}] /* steps */} /* join_execution */}] /* steps */
}

一共是3个阶段：

• join_preparation：sql准备阶段，sql格式化；
• join_optimization： sql分析优化阶段，是分析OPTIMIZER TRACE的重点。这段一般都比较长，分很多步，需要细看；
• join_execution： sql执行阶段；

其中的相关关键字解析：
//TODO

结论：全表扫描的成本低于索引扫描，所以MySQL最终选择全表扫描。