做一个基础的企业网站多少钱,免费wap自助建站火星建站,asp有哪些网站,深圳市地图1 show profile 分析慢查询
有时需要确定 SQL 到底慢在哪个环节#xff0c;此时 explain 可能不好确定。在 MySQL 数据库中#xff0c;通过 profile#xff0c;能够更清楚地了解 SQL 执行过程的资源使用情况#xff0c;能让我们知道到底慢在哪个环节。 知识扩展#xff1…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 的每个状态及耗时时间。
1.1 确定是否支持 profile
我们进行第一步用下面命令来判断当前 MySQL 是否支持 profile
mysql select have_profiling;------------------
| have_profiling |
------------------
| YES |
------------------1 row in set, 1 warning (0.00 sec)从上面结果中可以看出是YES表示支持profile的。
1.2 查看 profiling 是否关闭的
进行第二步用下面命令判断 profiling 参数是否关闭默认 profiling 是关闭的
mysql select profiling;-------------
| profiling |
-------------
| 0 |
-------------1 row in set, 1 warning (0.00 sec)结果显示为 0表示 profiling 参数状态是关闭的。
1.3 通过 set 开启 profile
mysql set profiling1;Query OK, 0 rows affected, 1 warning (0.00 sec)Tipsset 时没加 global只对当前 session 有效。
1.4 执行 SQL 语句
mysql select * from t1 where b1000;1.5 确定 SQL 的 query id
通过 show profiles 语句确定执行过的 SQL 的 query id
mysql show profiles;
-----------------------------------------------------
| Query_ID | Duration | Query |
-----------------------------------------------------
| 1 | 0.00063825 | select * from t1 where b1000 |
-----------------------------------------------------
1 row in set, 1 warning (0.00 sec)1.6 查询 SQL 执行详情
通过 show profile for query 可看到执行过的 SQL 每个状态和消耗时间
mysql show profile for query 1;
--------------------------------
| Status | Duration |
--------------------------------
| starting | 0.000115 |
| checking permissions | 0.000013 |
| Opening tables | 0.000027 |
| init | 0.000035 |
| System lock | 0.000017 |
| optimizing | 0.000016 |
| statistics | 0.000025 |
| preparing | 0.000020 |
| executing | 0.000006 |
| Sending data | 0.000294 |
| end | 0.000009 |
| query end | 0.000012 |
| closing tables | 0.000011 |
| freeing items | 0.000024 |
| cleaning up | 0.000016 |
--------------------------------
15 rows in set, 1 warning (0.00 sec)通过以上结果可以确定 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 分析下执行计划 通过上面执行计划中 key 这个字段可以看出该语句使用的是 b 字段的索引 idx_b。实际表 t1 中a、b 两个字段都有索引为什么条件中有这两个索引字段却偏偏选了 b 字段的索引呢这时就可以使用 trace 进行分析。大致步骤如下
mysql set session optimizer_traceenabledon,end_markers_in_jsonon;
/* optimizer_traceenabledon 表示开启 traceend_markers_in_jsonon 表示 JSON 输出开启结束标记 */
Query OK, 0 rows affected (0.00 sec)mysql select * from t1 where a 900 and b 910 order by a;
------------------
| id | a | b |
------------------
| 1 | 1 | 1 |
| 2 | 2 | 2 |......| 1000 | 1000 | 1000 |
------------------
1000 rows in set (0.00 sec)mysql SELECT * FROM information_schema.OPTIMIZER_TRACE\G
*************************** 1. row ***************************
QUERY: select * from t1 where a 900 and b 910 order by a --SQL语句
TRACE: {steps: [{join_preparation: { --SQL准备阶段select#: 1,steps: [{expanded_query: /* select#1 */ select t1.id AS id,t1.a AS a,t1.b AS b,t1.create_time AS create_time,t1.update_time AS update_time from t1 where ((t1.a 900) and (t1.b 910)) order by t1.a}] /* steps */} /* join_preparation */},{join_optimization: { --SQL优化阶段select#: 1,steps: [{condition_processing: { --条件处理condition: WHERE,original_condition: ((t1.a 900) and (t1.b 910)), --原始条件steps: [{transformation: equality_propagation,resulting_condition: ((t1.a 900) and (t1.b 910)) --等值传递转换},{transformation: constant_propagation,resulting_condition: ((t1.a 900) and (t1.b 910)) --常量传递转换},{transformation: trivial_condition_removal,resulting_condition: ((t1.a 900) and (t1.b 910)) --去除没有的条件后的结构}] /* steps */} /* condition_processing */},{substitute_generated_columns: {} /* substitute_generated_columns */ --替换虚拟生成列},{table_dependencies: [ --表依赖详情{table: t1,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: t1,range_analysis: {table_scan: {rows: 1000, --扫描行数cost: 207.1 --成本} /* table_scan */,potential_range_indexes: [ --分析可能使用的索引{index: PRIMARY,usable: false, --为false说明主键索引不可用cause: not_applicable},{index: idx_a, --可能使用索引idx_ausable: true,key_parts: [a,id] /* key_parts */},{index: idx_b, --可能使用索引idx_busable: 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 */,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, --是否使用mrrindex_only: false, --是否使用覆盖索引rows: 100, --使用该索引获取的记录数cost: 121.01, --使用该索引的成本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,rows: 90,cost: 109.01,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,rows: 90,ranges: [910 b] /* ranges */} /* range_access_plan */,rows_for_plan: 90,cost_for_plan: 109.01,chosen: true} /* chosen_range_access_summary */} /* range_analysis */}] /* rows_estimation */},{considered_execution_plans: [ --考虑的执行计划{plan_prefix: [] /* plan_prefix */,table: t1,best_access_path: { --最优的访问路径considered_access_paths: [ --决定的访问路径{rows_to_scan: 90, --扫描的行数access_type: range, --访问类型为rangerange_details: {used_index: idx_b --使用的索引为idx_b} /* range_details */,resulting_rows: 90, --结果行数cost: 127.01, --成本chosen: true, --确定选择use_tmp_table: true}] /* considered_access_paths */} /* best_access_path */,condition_filtering_pct: 100,rows_for_plan: 90,cost_for_plan: 127.01,sort_cost: 90,new_cost_for_plan: 217.01,chosen: true}] /* considered_execution_plans */},{attaching_conditions_to_tables: { --尝试添加一些其他的查询条件original_condition: ((t1.a 900) and (t1.b 910)),attached_conditions_computation: [] /* attached_conditions_computation */,attached_conditions_summary: [{table: t1,attached: ((t1.a 900) and (t1.b 910))}] /* attached_conditions_summary */} /* attaching_conditions_to_tables */},{clause_processing: {clause: ORDER BY,original_clause: t1.a,items: [{item: t1.a}] /* items */,resulting_clause_is_simple: true,resulting_clause: t1.a} /* clause_processing */},{reconsidering_access_paths_for_index_ordering: {clause: ORDER BY,index_order_summary: {table: t1,index_provides_order: false,order_direction: undefined,index: idx_b,plan_changed: false} /* index_order_summary */} /* reconsidering_access_paths_for_index_ordering */},{refine_plan: [ --改进的执行计划{table: t1,pushed_index_condition: (t1.b 910),table_condition_attached: (t1.a 900)}] /* refine_plan */}] /* steps */} /* join_optimization */},{join_execution: { --SQL执行阶段select#: 1,steps: [{filesort_information: [{direction: asc,table: t1,field: 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: { --排序详情rows: 90,examined_rows: 90, --参与排序的行数number_of_tmp_files: 0, --排序过程中使用的临时文件数sort_buffer_size: 115056,sort_mode: sort_key, additional_fields --排序模式详解请看下方知识扩展} /* filesort_summary */}] /* steps */} /* join_execution */}] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 --该字段表示分析过程丢弃的文本字节大小本例为0说明没丢弃任何文本INSUFFICIENT_PRIVILEGES: 0 --查看trace的权限是否不足0表示有权限查看trace详情
1 row in set (0.00 sec)
------------------------------------------------
------------------------------------------------mysql set session optimizer_traceenabledoff;
/* 及时关闭trace */这里对上方的执行字段详细描述一下
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 中进行排序。 知识扩展 知识点一MySQL 常见排序模式 sort_key, rowid 双路排序又叫回表排序模式是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID然后在 sort buffer 中进行排序排序完后需要再次取回其它需要的字段 sort_key, additional_fields 单路排序是一次性取出满足条件行的所有字段然后在sort buffer中进行排序 sort_key, packed_additional_fields 打包数据排序模式将 char 和 varchar 字段存到 sort buffer 中时更加紧缩。 三种排序模式比较 第二种模式相对第一种模式避免了二次回表可以理解为用空间换时间。由于 sort buffer 有限如果需要查询的数据比较大的话会增加磁盘排序时间效率可能比第一种方式更低。 MySQL 提供了一个参数max_length_for_sort_data当“排序的键值对大小” max_length_for_sort_data 时MySQL 认为磁盘外部排序的 IO 效率不如回表的效率会选择第一种排序模式否则会选择第二种模式。 第三种模式主要解决变长字符数据存储空间浪费的问题。 知识点二优化器在估计符合条件的行数时有两个选择 index diverdive 到 index 中利用索引完成元组数的估算特点是速度慢但可以得到精确的值index statistics使用索引的统计数值进行估算特点是速度快但是值不一定准确。 3 总结
今天我们分享了 show profile 和 trace 的使用方法我们来对比一下三种分析 SQL 方法的特点
explain获取 MySQL 中 SQL 语句的执行计划比如语句是否使用了关联查询、是否使用了索引、扫描行数等profile可以清楚了解到SQL到底慢在哪个环节trace查看优化器如何选择执行计划获取每个可能的索引选择的代价。
三种方法各有其适用场景
