快速学会分析SQL执行效率（下）
当你做成功一件事，千万不要等待着享受荣誉，应该再做那些需要的事。——巴斯德
在上一节我们学习了定位慢SQL及使用explain分析慢SQL，我们也提到了分析慢SQL还有showprofile和trace等方法，本节就重点补充学习这两种方法。
1、show profile分析慢查询
有时需要确定SQL到底慢在哪个环节，此时explain可能不好确定。在MySQL数据库中，通过profile，能够更清楚地了解SQL执行过程的资源使用情况，能让我们知道到底慢在哪个环节。
知识扩展：
可以通过配置参数 profiling = 1 来启用 SQL 分析。该参数可以在全局和 session 级别来设置。对于全局级别则作用于整个MySQL 实例，而 session 级别仅影响当前 session 。该参数开启后，后续执行的SQL 语句都将记录其资源开销，如 IO、上下文切换、CPU、Memory等等。根据这些开销进一步分析当前SQL 从而进行优化与调整。
下面我们来讲一下如何使用 profile 分析慢查询，大致步骤是：确定这个 MySQL 版本是否支持 profile；确定 profile是否关闭；开启 profile；执行 SQL；查看执行完 SQL 的 query id；通过 query id 查看 SQL 的每个状态及耗时时间。
下面是建表语句：
CREATE DATABASE muke;/* 创建测试使用的database，名为muke */
use muke;/* 使用muke这个database */
drop table if exists t1;/* 如果表t1存在则删除表t1 */
/* 创建表t1 */
CREATE TABLE `t1` (
`id` int(11) NOT NULL auto_increment,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
PRIMARY KEY (`id`),
KEY `idx_a` (`a`),
KEY `idx_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
drop procedure if exists insert_t1; /* 如果存在存储过程insert_t1，则删除 */
delimiter ;;
create procedure insert_t1()
begin
/* 创建存储过程insert_t1 */
declare i int; /* 声明变量i */
set i=1; /* 设置i的初始值为1 */
while(i<=1000)do /* 对满足i<=1000的值进行while循环 */
insert into t1(a,b) values(i, i); /* 写入表t1中a、b两个字段，值都为i当前的值 */
set i=i+1; /* 将i加1 */
end while;
end;;
delimiter ; /* 创建批量写入1000条数据到表t1的存储过程insert_t1 */
call insert_t1(); /* 运行存储过程insert_t1 */
drop table if exists t2; /* 如果表t2存在则删除表t2 */
create table t2 like t1; /* 创建表t2，表结构与t1一致 */
insert into t2 select * from t1; /* 将表t1的数据导入到t2 */
1.1 确定是否支持 profile
我们进行第一步，用下面命令来判断当前 MySQL 是否支持 profile：
select @@have_profiling;
从上面结果中可以看出是YES，表示支持profile的。
1.2 查看 profiling 是否关闭的
进行第二步，用下面命令判断 profiling 参数是否关闭（默认 profiling 是关闭的）：
select @@profiling;
我之前开启了所以是1，默认的结果是为 0，表示 profiling 参数状态是关闭的。
1.3 通过 set 开启 profile
set profiling=1;
Tips：set 时没加 global，只对当前 session 有效。
1.4 执行 SQL 语句
select * from t1 where b=1000;
1.5 确定 SQL 的 query id
通过 show profiles 语句确定执行过的 SQL 的 query id：
show profiles;
1.6 查询 SQL 执行详情
通过 show profile for query 可看到执行过的 SQL 每个状态和消耗时间：
show profile for query 1;
通过以上结果，可以确定 SQL 执行过程具体在哪个过程耗时比较久，从而更好地进行 SQL 优化与调整。
2 trace 分析 SQL 优化器
从前面学到了 explain 可以查看 SQL 执行计划，但是无法知道它为什么做这个决策，如果想确定多种索引方案之间是如何选择的或者排序时选择的是哪种排序模式，有什么好的办法吗？
从 MySQL 5.6 开始，可以使用 trace 查看优化器如何选择执行计划。
通过trace，能够进一步了解为什么优化器选择A执行计划而不是选择B执行计划，或者知道某个排序使用的排序模式，帮助我们更好地理解优化器行为。
如果需要使用，先开启 trace，设置格式为 JSON，再执行需要分析的 SQL，最后查看 trace 分析结果（在information_schema.OPTIMIZER_TRACE 中）。
开启该功能，会对 MySQL 性能有所影响，因此只建议分析问题时临时开启。
下面一起来看下 trace 的使用方法。使用讲解 explain 时创建的表t1做实验。
首先构造如下 SQL (表示取出表 t1 中 a 的值大于 900 并且 b 的值大于 910 的数据，然后按照 a 字段排序)：
select * from t1 where a >900 and b > 910 order by a;
我们首先用 explain 分析下执行计划：
explain select * from t1 where a >900 and b > 910 order by a;
通过上面执行计划中 key 这个字段可以看出，该语句使用的是 b 字段的索引 idx_b。实际表 t1 中，a、b 两个字段都有索引，为什么条件中有这两个索引字段却偏偏选了 b 字段的索引呢？这时就可以使用 trace 进行分析。大致步骤如下：
set session optimizer_trace="enabled=on",end_markers_in_json=on;
optimizer_trace=“enabled=on” 表示开启 trace；end_markers_in_json=on 表示 JSON 输出开启结束标记
一起执行就可以看到
select * from t1 where a >900 and b > 910 order by a;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
下面使用注释说明:
{
"steps": [
{
"join_preparation": { //sql准备阶段
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `t2`.`id` AS `id`,`t2`.`a` AS `a`,`t2`.`b` AS `b`,`t2`.`create_time` AS `create_time`,`t2`.`update_time` AS `update_time` from `t2` where ((`t2`.`a` > 900) and (`t2`.`b` > 910)) order by `t2`.`a`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { //SQL优化阶段
"select#": 1,
"steps": [
{
"condition_processing": { //条件
"condition": "WHERE",
"original_condition": "((`t2`.`a` > 900) and (`t2`.`b` > 910))", //原始条件
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`t2`.`a` > 900) and (`t2`.`b` > 910))" //等值传递转换
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`t2`.`a` > 900) and (`t2`.`b` > 910))" //常量传递转换
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`t2`.`a` > 900) and (`t2`.`b` > 910))" //去除没有的条件的结构
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [ //表依赖情况
{
"table": "`t2`",
"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": "`t2`",
"range_analysis": {
"table_scan": {
"rows": 1000, //扫描行数
"cost": 105.225 //时间成本
} /* table_scan */,
"potential_range_indexes": [ //分析可能使用的索引
{
"index": "PRIMARY",
"usable": false, //为false说明不可以主键索引
"cause": "not_applicable"
},
{
"index": "idx_a", //可能使用索引idx_a
"usable": true,
"key_parts": [
"a",
"id"
] /* key_parts */
},
{
"index": "idx_b", //可能使用索引idx_b
"usable": true,
"key_parts": [
"b",
"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_a",
"usable": false,
"cause": "query_references_nonkey_column"
},
{
"index": "idx_b",
"usable": false,
"cause": "query_references_nonkey_column"
}
] /* potential_skip_scan_indexes */
} /* skip_scan_range */,
"analyzing_range_alternatives": { //分析各索引的索引成本
"range_scan_alternatives": [
{
"index": "idx_a", //使用idx_a的成本
"ranges": [
"900 < a" //使用索引idx_a的范围
] /* ranges */,
"index_dives_for_eq_ranges": true,//使用了index dive
"rowid_ordered": false, //使用该索引获取的记录是否按照主键排序
"using_mrr": false, //表示没有使用多范围读取
"index_only": false, //没有使用覆盖索引
"in_memory": 1, //为1。表示执行计划使用内存临时表存储数据。
"rows": 100, //该索引扫描记录
"cost": 73.135, //该索引的时间成本
"chosen": true //可能选择该索引
},
{
"index": "idx_b", //使用索引idx_b的成本
"ranges": [
"910 < b"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"in_memory": 1,
"rows": 90, //扫描行数比idx_a索引更少
"cost": 65.885, //时间成本比idx_a更低
"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_b", //索引idx_b
"rows": 90,
"ranges": [
"910 < b"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 90,
"cost_for_plan": 65.885,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [ //考虑的执行计划
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`t2`",
"best_access_path": { //最优的访问路径
"considered_access_paths": [ //决定的访问路径
{
"rows_to_scan": 90, //扫描行数
"access_type": "range", //访问类型，为rang范围查询
"range_details": {
"used_index": "idx_b" //使用idx_b索引
} /* range_details */,
"resulting_rows": 90, //结果行数
"cost": 74.885, //时间成本
"chosen": true, //选择该索引
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 90,
"cost_for_plan": 74.885,
"sort_cost": 90,
"new_cost_for_plan": 164.885,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`t2`.`a` > 900) and (`t2`.`b` > 910))",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`t2`",
"attached": "((`t2`.`a` > 900) and (`t2`.`b` > 910))"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"optimizing_distinct_group_by_order_by": {
"simplifying_order_by": {
"original_clause": "`t2`.`a`",
"items": [
{
"item": "`t2`.`a`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`t2`.`a`"
} /* simplifying_order_by */
} /* optimizing_distinct_group_by_order_by */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [
] /* steps */,
"index_order_summary": {
"table": "`t2`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "idx_b",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"finalizing_table_conditions": [
{
"table": "`t2`",
"original_table_condition": "((`t2`.`a` > 900) and (`t2`.`b` > 910))",
"final_table_condition ": "((`t2`.`a` > 900) and (`t2`.`b` > 910))"
}
] /* finalizing_table_conditions */
},
{
"refine_plan": [
{
"table": "`t2`",
"pushed_index_condition": "(`t2`.`b` > 910)",
"table_condition_attached": "(`t2`.`a` > 900)"
}
] /* refine_plan */
},
{
"considering_tmp_tables": [
{
"adding_sort_to_table": "t2"
} /* filesort */
] /* considering_tmp_tables */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { -----//SQL执行阶段
"select#": 1,
"steps": [
{
"sorting_table": "t2",
"filesort_information": [
{
"direction": "asc",
"expression": "`t2`.`a`"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false, --------//未使用优先队列优化排序
"cause": "not applicable (no LIMIT)" ----------//没有使用limit
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": { ------------//排序情况
"memory_available": 262144,
"key_size": 9,
"row_size": 32,
"max_rows_per_buffer": 90,
"num_rows_estimate": 90,
"num_rows_found": 90,
"num_initial_chunks_spilled_to_disk": 0,
"peak_memory_used": 33520,
"sort_algorithm": "std::sort",
"unpacked_addon_fields": "skip_heuristic",
"sort_mode": "<fixed_sort_key, additional_fields>"
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
这里对上方的执行字段详细描述一下：
TRACE 字段中整个文本大致分为三个过程。
-
准备阶段：对应文本中的 join_preparation
-
优化阶段：对应文本中的 join_optimization
-
执行阶段：对应文本中的 join_execution
使用时，重点关注优化阶段和执行阶段。
由此例可以看出：
-
在 trace 结果的 analyzing_range_alternatives 这一项可以看到：使用索引 idx_a 的成本为 121.01，使用索引idx_b 的成本为 109.01，显然使用索引 idx_b 的成本要低些，因此优化器选择了 idx_b 索引；
-
在 trace 结果的 filesort_summary 这一项可以看到：排序模式为<sort_key, additional_fields>，表示使用的是单路排序，即一次性取出满足条件行的所有字段，然后在 sort buffer 中进行排序。
3 、总结
今天我们分享了 show profile 和 trace 的使用方法，我们来对比一下三种分析 SQL 方法的特点：
explain：获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等；
profile：可以清楚了解到SQL到底慢在哪个环节；
trace：查看优化器如何选择执行计划，获取每个可能的索引选择的代价。
三种方法各有其适用场景，如果你有其它分析 SQL 的工具，欢迎在留言区分享。
4、问题
在工作中，遇到慢查询你是如何去分析优化的？
986
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
