show profile剖析单条查询:
在服务器上执行的所有语句,都会测量其耗费的时间和其他一些查询执行状态变更相关的数据。show profile是由Jeremy Cole捐献给MySQL社区版本的。默认的是关闭的,但是会话级别可以开启这个功能。开启它可以让MySQL收集在执行语句的时候所使用的资源。为了统计分析,把profiling设为1打开收集
show profile配置
#查看show profile开关mysql>show variables like "profiling";
![5c54f28aff297c7a51531f71fc5dfa09.png](https://img-blog.csdnimg.cn/img_convert/5c54f28aff297c7a51531f71fc5dfa09.png)
默认情况下都是关闭的,这是mysql默认的配置,开启会消耗mysql系统的资源,一般情况下不建议开启,除非系统检索到有很慢的sql语句且需要使用show profile去分析的时候,可临时打开使用。
#开启show profile开关(临时打开,mysql重启后自动关闭),如需要永生效,需要修改mysql配置文件mysql> set profiling=on;
![6948053aa110dde3ef502f00a8570e8f.png](https://img-blog.csdnimg.cn/img_convert/6948053aa110dde3ef502f00a8570e8f.png)
#开始测试,我们随便执行几个sql语句,查看show profile收集的情况进行详细查看语句执行的生命周期和细节mysql>show profile;
![e8957ab938150801951ab57d5f6bc150.png](https://img-blog.csdnimg.cn/img_convert/e8957ab938150801951ab57d5f6bc150.png)
#查询query_id=2的运行结果,根据自己需要查询的id查询相应的结果,根据查#询结果查看耗时步骤进行优化。mysql>show profile for query 2;
![1bccb77e9f438b51a54fb0cc6cbd389d.png](https://img-blog.csdnimg.cn/img_convert/1bccb77e9f438b51a54fb0cc6cbd389d.png)
- 结论:消耗时间最多的是“发送数据(Sending data)",这个状态代表的原因非常多,可能是各种不同的服务器活动,包括在关联时搜索匹配的行记录等,这部分很难说能优化节省多少消耗的时间。另外也要注意到“结果排序(Sortingresult)"花费的时间占比非常低,所以这部分是不值得去优化的。这是一个比较典型的问题,所以一般我们都不建议用户在“优化排序缓冲区(tuning sort buffer)"或者类似的活动,上花时间。尽管剖析报告能帮助我们定位到哪些活动花费了最多的时间,但并不会告诉我们为什么会这样。要弄清楚为什么复制数据到临时表要花费这么多时间,就需要深入下去,继续剖析这一步的子任务。
#注意:诊断sql可以在show profile后加上指定的诊断参数mysql>show profile cpu,block,io for query id;#type类型:ALL-显示所有的开销信息BLOCK I0-显示块IO相关开销CONTEXT SWITCHES-上下文切换相关开销CPU-显示CPU相关开销信息IPC-显示发送和接收相关开销信息MEMQRY_显示内存相关开销信息PAGE FAULTS-显示页面错误相关开销信息SWAPS-显示交换次数相关开销的信息Source -显示和Source_ function, Source_ file, Source_ line相关的开销信息
sql诊断出现以下4种情况特别危险
- converting HEAP to MyISAM 查询结果太大,内存不够用了,开始往磁盘上搬了
- creating tmp table 创建临时表,把查询的数据拷贝到临时表,用完后还要删除
- coping to tmp table on disk 把内存临时表复制到磁盘,非常危险.......
- lock 锁表
总结:在执行sql诊断的时候如果发现以上4中情况出现其中任何一个都相当危险,如果4个同时出现,那这个哥们写的sql语句可以说是惊天地泣鬼神!