小知识，大挑战！本文正在参与“ 程序员必备小知识 ”创作活动
作者的其他平台：
| CSDN：blog.csdn.net/qq_4115394…
| 知乎：www.zhihu.com/people/1024…
| GitHub：github.com/JiangXia-10…
| 公众号：1024笔记
本文大概5123字，读完共需13分钟
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优化的其他方法、步骤。
最后如果觉得本文写得不错，就推荐给更多的人呗。
今日推荐
几个必须掌握的SQL优化技巧(一)：查看SQL语句的执行频率
