数据库慢查询语句的收集和分析方案

2023-04-02 08:36| 来源: 网络整理| 查看: 265

概述mysql慢sql收集与分析2.1. 慢sql收集1. 启用慢并记录到表中2. 先不记录不用索引的sql3. 修改时间阀值，建议由10秒改为3秒。4. 根据表做各种维度分析5. 按sql前100个字符相同做统计分析2.2. mysql慢查询分析sql server性能监控3.1. 工具介绍3.2. 使用工具收集慢sql3.3. sql server慢sql性能分析

概述

在日常业务节点测试及性能测试中，我们需要观察数据端执行的sql，对于sql执行中查询数量过多（超过2万），执行时间过长（超过3秒）的sql，一般就意味着有性能问题出现了。需要我们测试人员和研发人员尽早发现和介入分析。优化方案上，主要考虑添加合理的索引、优化sql语句结构及调整业务代码逻辑等手段，避免掉这些查询结果集过多和执行时间过长的sql，从而改善业务节点的操作效率，降低服务器资源的使用，提高产品的稳定性。本文档以mysql和sql server为例，说明了如何收集及分析，供参考。

mysql慢sql收集与分析

2.1. 慢sql收集

mysql默认开启慢日志是存入日志文件，由于云mysql环境慢日志文件不方便取出及分析慢日志文件需要专门的工具（如mysqldumpslow）才能做统计分析，操作较为繁琐。实际开发中建议修改mysql数据库设置，将慢日志存入表mysql.slow_log。

1. 启用慢并记录到表中

show variables like ‘slowquery_log’set global slow_query_log=1show variables like ‘log_output’ ##默认是FILEset global logoutput=‘TABLE’ ##修改为存在表中：

2. 先不记录不用索引的sql

show variables like ‘%log_queries_not_using_indexes%’set global log_queries_not_using_indexes = 0

3. 修改时间阀值，建议由10秒改为3秒。

show variables like ‘long_query_time’set global long_query_time = 3

4. 根据表做各种维度分析

select * from mysql.slow_log where start_time >’2020-07-01 00:00:00’select * from mysql.slow_log where sql_text like ‘%imp_iot_alarm_logszg%’

示例：开发环境及日常测试环境，关注query_time时间过长的sql及rows_sent超过2万行的sql语句，并进行判定是否合理及是否要介入优化？

5. 按sql前100个字符相同做统计分析

对于压测环境或线上环境，可考虑在开启慢日志一定时间（如一天）后，做统计分析，找出执行次数频率高且总时间长的sql进行分析优化。SELECT SUBSTRING(sql_text,1,100),COUNT(*) AS COUNT,SUM(query_time) AS ‘秒’ Frommysql.slow_logGROUP BY SUBSTRING(sql_text,1,100)ORDER BY SUM(query_time) DESC

select * from mysql.slow_log where sql_text like ‘%imp_iot_alarm_logszg%’

2.2. mysql慢查询分析

explain用来分析mysql语句的执行计划，通过执行计划可从侧面反映出sql的执行计划，从而明确是否sql在执行时，走到了不合理的执行路径？**explain1. table：显示这一行的数据是关于哪张表的。2. type：这是最重要的列，显示连接使用了何种类型。从最好到最差的连接类型为：null、const、eq_ref、ref、range、index和ALL。3. possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。4. key：实例使用的索引。如果为null，则没有使用索引。5. key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好。6. ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。7. rows：mysql认为必须检查的用来返回请求数据的行数。rows越小越好。8. extra：在什么方式下找到了所需记录,出现using filesort或using temporary表明效率低下,only index用到了索引,where used用到了where 过滤条件,impossible where 没用到索引。Using filesort： mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。Using temporary：mysql需要创建一个临时表来存储结果，这通常发生在对不同的列进行Order by上，而不是group by上。

sql server性能监控

3.1. 工具介绍

SQL Profiler是一个图形界面的sql收集及重放工具，其作用如下：1. 图形化监视SQL Server查询2. 在后台收集查询信息3. 分析性能4. 诊断像死锁之类的问题5. 调试T-SQL语句6. 模拟重放SQL Server活动7. Http请求信息，包括耗时及请求上下行数据重点关注的事件：关注的数据列：

3.2. 使用工具收集慢sql 在sql server查询分析器，点[工具]，选[SQL Server Profiler]节点。打开收集工具后，选[使用模板]为[Turing]（性能调优模板）。2. 设置收集列和事件。 3. 根据实际情况，设置DatabaseName、CPU、Duration过滤。4. 开始收集时将滚动sql设置改为不滚动，避免跟踪工具卡死白屏。跟踪一定时间后，将跟踪结果另存为跟踪文件。 3.3. sql server慢sql性能分析使用脚本对保存的跟踪文件做统计分析：DROP TABLE zgaDROP TABLE zgbGOSELECT * INTO zga FROM fntrace_gettable(‘d:\zg.trc’,default) WHERE eventclass IN (10,12)GOSELECT ROW_NUMBER() over ( order by starttime ASC) as rownumber , * INTO zgb FROM zgaGOSELECT SUBSTRING(CAST(textdata AS NVARCHAR(MAX)),1,100),COUNT(*) AS COUNT,SUM(Duration)/1000 AS ‘毫秒’ FromzgbWHERE eventclass IN (10,12)GROUP BY SUBSTRING(CAST(textdata AS NVARCHAR(MAX)),1,100)—order by duration desc_ORDER BY SUM(Duration) DESCGOselect * from zgb where textdata like ‘%%’ order by duration desc

示例：对收集到的慢sql，根据执行次数、合计时间消排序，找出消耗资源高的top n sql进行分析优化。示例：单个sql性能分析1. 主要优化是关注sql在执行时的cpu、io消耗和具体的执行计划是否合理。开启cpu和io统计：set statistics time onset statistics io on执行sql，则可在[消息]结果中看到io和cpu消耗。

2. 根据情况，选择[显示估计的执行计划]或后面的[显示实际的执行计划]去评估sql的执行消耗：3. 执行计划结果如下，根据实际情况，查看消耗占比高的执行路径，进行sql优化，如添加合理索引、改写sql结构等。

【本文地址】

公司简介

联系我们