回顾一下,我们之前所说过的,如果想要进行SQL查询的数据调优、排查。第一步,一定要让出现的问题重现啊(运营工程师或DBA他们从监控系统里面,收到了爆炸,系统变慢了,大家都知道,重要的核心系统都会有另外一套辅助的系统来监控,这种监控系统,比如说现在这个系统慢与每一个模块平均时间,可能5秒钟就能执行完,但是已经长达20秒了,这个时候就要判断为什么慢了。有很多种原因:1.有可能是程序的内存泄漏,也许是死锁,也许是网络,也许是SQL写的烂。假设是SQL的问题,那么我们需要把有问题的SQL抓出来)
执行过程:收到问题,诊断SQL
开启慢查询日志,抓出执行的慢的SQL
使用explain分析(基本上可以找到为题所在,但是如果还是没有摆平,我们的SQL在传输、网络、连接、死锁,需要进一步细粒度的查询和排查的时候就需要使用show profile)
show profile(还是解决的一般般)
配合DBA 到my.cnf配置文件中对各种性能的参数调优和修改(基本上不需要我们来修改)
show profile
是什么:是mysql 提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。(默认情况下,参数处于关闭状态,并保存最近15次的运行结果)
分析步骤:
1.是否支持,看看当前的MySQL版本是否支持:
show variables like 'profiling'; 或show variables like 'profiling%';
2.开启功能,默认是关闭,使用前需要开启: set profiling = on;
3.运行sql
随便运行几条SQL,以便于show prifiles的日志分析。
4.查询结果
show profiles;show profiles结果
5.诊断SQL
show profile cpu,block io for query 上一步前面的问题SQL数字号码
参数说明:不只是可以查看cpu和block io
show profile cpu,block io for query 3;show prpfile 分析
从图中可以看到开始,打开表,加载,关闭表,释放资源、记录日志,清理的你工作,在这完全可以看到一条SQL的完整生命周期。
如上图看到的各种执行结果状态以后,怎么知道哪些参数有毛病,哪些参数没毛病呢?往下走
6.日常开发需要注意的结论
如果show profile ... for query id;出现了如下四个,则必须优化这条sql。converting HEAP to MyISAM 查询结果太大, 内存都不够用了网磁盘上搬了
Creating tmp table 创建临时表拷贝数据到临时表:假设要查询两百万数据,刚好匹配的条件有一百万,恰巧要把这一百万的数据拷贝到临时表,然后再把数据推送给用户,最后再把临时表删掉,这个时候就是导致SQL变慢的罪魁祸首
用完再删除Copying to tmp table on disk 把内存中临时表复制到磁盘,危险!!!
locked
case
上图show profiles结果第8条执行了两秒多,下面分析来看一下结果
如红色框中标注,此时创建了临时表,这个过程执行了2.58秒,这个时候如果把这个化验单提交给DBA或者技术大牛的时候,他们肯定会带你好好玩。而且还出现了sort排序,创建临时表、拷贝到临时表、删除临时表。相当于一个对象创建使用回收。这三部加重了数据库使用的负担。