1 前言
在应用的开发过程中，由于开发初期的数据量一般都比较小，所以开发过程中一般都比较注重功能上的实现，但是当完成了一个应用或者系统之后，随着生产数据量的急剧增长，那么之前的很多sql语句的写法就会显现出一定的性能问题，对生产的影响也会越来越大，这些不恰当的sql语句就会成为整个系统性能的瓶颈，为了追求系统的极致性能，必须要对它们进行优化。
2 正文
上一篇文章：几个必须掌握的SQL优化技巧(三)：Explain分析执行计划介绍了如何使用explain命令对sql的执行计划进行分析，包括在Select语句执行过程中表如何连接和连接的顺序，从而后续对其进行优化。具体的命令格式如下
explain + select语句 复制代码
在MySQL体系架构这篇文章介绍过mysql的体系架构，里面说到mysql体系中有一层叫做优化器--optimizer，MYSQL5.6开始提供了对SQL的跟踪trace，通过trace文件能够进一步了解为什么optimizer如何选择执行计划的，为什么会选择A计划，而不是B计划。
今天这篇文章将会介绍如何使用trace工具分析优化器的执行计划。
1、打开trace，设置格式为json格式，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示：
set optimier_trace="enabled=on",end_markers_in_JSON=on; set optimier_trace_max_mem_size=1000000 复制代码
结果如下：
2、然后执行一条sql语句，比如：
select * from payment where id=1 复制代码
3、最后，在系统表information_schema.optimizer_trace中查询优化器的执行计划就可以知道mysql是如何执行mysql的：
select * from information_schema.optimizer_trace \G 复制代码
具体结果如下为json格式，具体步骤以及注释如下：
mysql> select *from information_schema.optimizer_trace \G *************************** 1. row *************************** // 具体的执行的sql语句 QUERY: select * from payment where id=1 // 优化器跟踪的步骤信息 TRACE: { // 具体步骤 "steps": [ { "join_preparation": { //预备工作 "select#": 1, "steps": [ { // 将 * 解析为具体字段 "expanded_query": "/* select#1 */ select `payment`.`amount` AS `amount` from `payment` where (`payment`.`customer_id` = 1)" } ] /* steps */ } /* join_preparation */ }, { //进行优化 "join_optimization": { "select#": 1, "steps": [ { //条件处理 "condition_processing": { "condition": "WHERE", "original_condition": "(`payment`.`customer_id` = 1)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(1, `payment`.`customer_id`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(1, `payment`.`customer_id`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(1, `payment`.`customer_id`)" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { //替换生成的列 } /* substitute_generated_columns */ }, { "table_dependencies": [ //表的依赖关系 { "table": "`payment`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ //使用键 { "table": "`payment`", "field": "customer_id", "equals": "1", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ //行判断 { "table": "`payment`", "range_analysis": { "table_scan": { "rows": 16125, "cost": 3324.1 } /* table_scan */, //扫描表 "potential_range_indexes": [ //潜在的范围索引 { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "idx_fk_staff_id", "usable": false, "cause": "not_applicable" }, { "index": "idx_fk_customer_id", "usable": true, "key_parts": [ "customer_id", "payment_id" ] /* key_parts */ }, { "index": "fk_payment_rental", "usable": false, "cause": "not_applicable" } ] /* potential_range_indexes */, "setup_range_conditions": [ //设置范围条件 ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "analyzing_range_alternatives": { //分析范围选项 "range_scan_alternatives": { "index": "idx_fk_customer_id", "ranges": [ "1 <= customer_id <= 1" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 32, "cost": 39.41, "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_fk_customer_id", "rows": 32, "ranges": [ "1 <= customer_id <= 1" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 32, "cost_for_plan": 39.41, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ //考虑执行计划 { "plan_prefix": [ ] /* plan_prefix */, "table": "`payment`", "best_access_path": { //最佳访问路径 "considered_access_paths": [ { "access_type": "ref", "index": "idx_fk_customer_id", "rows": 32, "cost": 38.4, "chosen": true }, { "access_type": "range", "range_details": { "used_index": "idx_fk_customer_id" } /* range_details */, "chosen": false, "cause": "heuristic_index_cheaper" } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100,//行过滤百分比 "rows_for_plan": 32, "cost_for_plan": 38.4, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { //将条件附加到表上 "original_condition": "(`payment`.`customer_id` = 1)", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ //附加条件概要 { "table": "`payment`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ //精简计划 { "table": "`payment`" } ] /* 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) 复制代码
3 总结
这篇文章主要介绍了如何使用trace工具跟踪mysql优化器的执行计划，根据执行语句结合优化器的执行计划，以及前面介绍的sql频率和mysql的执行计划等信息，有助于我们后面对select查询语句进行优化。
后面将会继续分享关于sql优化的其他方法、步骤。
