在企业中慢SQL问题进场会遇到#xff0c;尤其像我们这种ERP行业。

最近经常遇到技术开发跑来问我慢SQL优化相关工作所以干脆出几篇SQL相关优化技术月报我这里就以公司mysql一致的5.7版本来说明下。

成熟的公司企业都会有晚上的慢SQL监控和预警机制。

不需要我们技术人员过多关注慢SQL的产生和收集自然会有管理人员通知下来。

一般来说慢SQL监控通常都是利用slowlog来实现的这个比较简单

mysql

默认是关闭slowlog的不记录管理语句也不记录不使用索引进行查找的查询毕竟这也是一个额外的损耗。

最小值和默认值long_query_time分别为

show

那么发现了慢SQL之后怎么去定位问题在mysql官网文档中性能问题诊断分析有提供分析方式。

1、慢SQL诊断SHOW

profile语句提供的分析信息相当的数据但是需要注意的是在未来的mysql中会弃用当前语句功能使用性能模式performance_schema来替换从8.0版本文档中确实没有看到这个语句了但是听别说依旧可以使用这个先不管了反正目前看来mysql5.7在23年10月还在更新维护那就没什么好说的。

profiles

其他内容就不多说了简单玩意默认size是15我这里调成了最大100。

2、已知执行SQL诊断性能

如果现在你已经知道慢SQL是哪个了就可以通过profiling来进行诊断。

profiles来显示发送到服务器的最新语句的列表除了他自己。

profile

SWITCHES显示自愿和非自愿上下文切换的计数CPU显示用户和系统CPU使用时间IPC显示发送和接收的消息计数MEMORY目前尚未实施PAGE

FAULTS显示主要和次要页面错误的计数SOURCE显示源代码中函数的名称以及函数所在文件的名称和行号SWAPS显示交换计数

profile

19可以显示在各个阶段CPU的消耗。

具体的使用可以根据需要来定。

对于show

profile的结果比较重要这是我们诊断SQL问题的关键。

返回内容比较多都是SQL整个执行过程我们也不需要关注所有的内容

System

确认是由于哪个锁引起的通常是因为MySQL或InnoDB内核级的锁引起的。

建议如果耗时较大再关注即可一般情况下都还

Sending

select并将数据发送到客户端。

由于在此状态期间发生的操作往往会执行大量磁盘访问读取因此它通常是给定查询生命周期中运行时间最长的状态。

建议一般当前步骤耗时久就是SQL本身的效能问题可以通过做响应的优化手段比如索引优化提高检索效率、分页控制数据量等等。

Sorting

by、groupby都会有这样的步骤产生如果当前阶段耗时久可以考虑做一些索引优化来避免sort动作或者进行数据量控制。

Sending

by、groupby都会有这样的步骤产生如果当前阶段耗时久可以考虑做一些索引优化来避免sort动作或者进行数据量控制

Creating

创建临时表。

先拷贝数据到临时表用完后再删除临时表。

消耗内存数据来回拷贝删除消耗时间。

建议比如groupby或者一些子查询会产生当前步骤可以通过优化索引来避免

converting

建议优化索引或着数据量优化可以调整max_heap_table_size

Copying

建议优化索引可以调整tmp_table_size参数增大内存临时表大小

上面列举一些常见内容项详细的可以查看官网中资料processlistMySQL

MySQL

处理一般线程state官网还介绍了缓存、I/O线程状态等等。

虽然内容是show

到这里基本上就可以大致有个慢SQL诊断结果了如果SQL本身需要优化就可以做响应的执行进化分析过程。

3、线上问题分析定位

这样可以知道当前执行中的SQL当前自行过程中的状态注意这个时实时的所以可以通过多次观察来看耗时的步骤比如当前SQL在sending

client持续时间很久说明数据量很大导致传输给客户端效率慢。

connection