Mysql使用profiling分析慢sql语句的原因

   

         CleverCode的同事最近给我推荐了一个分析mysql中sql语句的工具profiling，发现这个工具非常不错，能够很准确的分析出查询的过程中sql语句具体的时间花在了哪里。CleverCode在这里总结一下，分享给大家。

        【 CleverCode在csdn博客中的原创作品，请勿转载，原创地址：Mysql使用profiling分析慢sql语句的原因_CleverCode的博客-CSDN博客】

1 简介    

    MySQL 的 Query Profiler 是一个使用非常方便的 Query 诊断分析工具，通过该工具可以获取一条Query 在整个执行过程中多种资源的消耗情况，如 CPU，IO，IPC，SWAP 等，以及发生的 PAGE FAULTS，CONTEXT SWITCHE 等等，同时还能得到该 Query 执行过程中 MySQL 所调用的各个函数在源文件中的位置。
    
    MySQL5.0.37版本以上支持PROFILING调试功能，让您可以了解SQL语句消耗资源的详细信息。因为它需要调用系统的getrusage()函数，所以只是在Linux/Unix类平台上才能使用，而不能在Windows平台上使用。而且，PROFILING是针对处理进程(process)而不是线程(thread)的，服务器上的其他应用，可能会影响您的调试结果，因此，这个工具适合开发过程中的调试，如果要在生产环境中调试使用，则要注意它的局限性。

2 操作

2.1 查看是否已经启用profile，默认是关闭的。

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 | 
+-------------+
1 row in set (0.00 sec)

2.2 启用profiling。变量profiling是用户变量，每次都得重新启用。

mysql> set profiling = 1;  
Query OK, 0 rows affected (0.00 sec)


mysql> select @@profiling;  
+-------------+
| @@profiling |
+-------------+
|           1 | 
+-------------+
1 row in set (0.00 sec)

2.3 执行以下语句。为避免之前已经把 SQL 存放在 QCACHE 中， 建议在执行 SQL 时， 强制 SELECT 语句不进行 QCACHE 检测。这样可以提交分析的准确性。

mysql> use db_user;
mysql> select sql_no_cache count(*) from system_user;
mysql> update system_user set username = 'CleverCode' where id = 10;
mysql> select sql_no_cache count(*) from system_user where age > 20;
...... 

2.4 使用show profile查询最近一条语句的执行信息。（分析：select sql_no_cache count(*) from system_user where age > 20）

mysql> show profile;

2.5 使用show profiles。查看在服务器上执行语句的列表。(查询id，花费时间，语句) 。

mysql> show profiles; 

2.6 使用show profile查询制定ID的执行信息。这里分析ID为6的语句。（分析：select sql_no_cache count(*) from system_user where age > 20）。

mysql> show profile for query 6;

2.7 获取 CPU 和 Block IO 的消耗。

mysql>  show profile block io,cpu for query 6;

2.8 获取其他信息。都可以通过执行 “SHOW PROFILE *** FOR QUERY n” 来获取。参考地址：MySQL :: MySQL 5.6 Reference Manual :: 13.7.5.31 SHOW PROFILE Statement。

mysql> show profile all for query 6;
mysql> show profile cpu,block io,memory,swaps,context switches,source for query 6;

ALL displays all information

BLOCK IO displays counts for block input and output operations
 

CONTEXT SWITCHES displays counts for voluntary and involuntary context switches

CPU displays user and system CPU usage times

IPC displays counts for messages sent and received

MEMORY is not currently implemented

PAGE FAULTS displays counts for major and minor page faults

SOURCE displays the names of functions from the source code, together with the name and line number of the file in which the function occurs


SWAPS displays swap counts

技术交流

CleverCode是一名架构师，技术交流，咨询问题，请加CleverCode创建的qq群(架构师俱乐部)：517133582。加群和腾讯，阿里，百度，新浪等公司的架构师交流。【架构师俱乐部】宗旨：帮助你成长为架构师！