文章目录
1、SQL分析、调优和排查的大体流程
- DBA或者运维人员通过监控系统监测到运行缓慢的SQL
- 在测试环境中,运行待优化的SQL语句,并开启慢查询日志,抓取运行缓慢的SQL语句
- 通过explain分析SQL的执行计划,通过explain的结果再结合相应的业务基本上可以定位并解决运行缓慢的问题
- 假如通过explain还是无法定位sql运行慢的问题,则需要分析sql在数据传输、网络、连接是否是死锁等需要使用比explain更加细粒度的查询和排查show profile;
- 假如通过explain和show profile还是无法解决的话,则需要配合DBA对服务器上的各种参数进行调优
2、show profile是什么
是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。
利用show profile可以查看SQL的执行周期
该SHOW PROFILE和SHOW PROFILES语句被取消,并将在未来的MySQL版本中删除
默认情况下,参数处于关闭状态,并保存最近15次的运行结果
3、show profile分析步骤
(1)是否支持,看看当前的MySQL版本是否支持
(2)开启功能,默认是关闭,使用前需要开启
(3)运行SQL
select * from emp inner join dept on emp.deptid=dept.id;
select * from dept where id=9999;
select * from dept;
select * from emp group by id%10 limit 150000;
(4)查看结果,show profiles
(5)诊断SQL,show profile cpu, block io for query 上一步前面的问题SQL数字号码
show profile参数说明
主要的参数类型是CPU和 BLOCK IO.
参数类型 | 参数说明 |
---|---|
ALL | 显示所有的开销信息 |
BLOCK IO | 显示块IO相关开销 |
CONTEXT SWITCHES | 上下文切换相关开销 |
CPU | 显示CPU相关开销信息 |
IPC | 显示发送和接收相关开销信息 |
MEMORY | 显示内存相关开销信息 |
PAGE FAULTS | 显示页面错误相关开销信息 |
SOURCE | 显示和Source_function,Source_file,Source_line相关的开销信息 |
SWAPS | 显示交换次数相关开销信息 |
由上图可知,SQL的生命周期中有如此多的过程,不可能全部记住,那么当出现哪几种情况,说明该SQL语句一定存在性能问题呢?
我们分析一下运行时间6.98s的SQL语句的声明周期跟上面的SQL语句有什么不同之处,如下图所示:
(6)日常开发需要注意的结论(以下情况必优化)
在日常开发中通过show profile出现以下4种情况之一,就说明该SQL存在严重的问题,必须进行SQL优化操作。
类型 | 说明 |
---|---|
converting HEAP to MyISAM | 查询结果太大,内存都不够用了,往磁盘上搬了 |
Creating tmp table | 创建临时表 1. 拷贝数据到临时表 2. 用完再删除 |
Copying to tmp table on disk | 把内存中临时表复制到磁盘,危险操作 |
locked | 加锁 |