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

 

CleverCode的同事最近給我推薦了一個分析mysql中sql語句的工具profiling，發現這個工具非常不錯，能夠很准確的分析出查詢的過程中sql語句具體的時間花在了哪裡。CleverCode在這裡總結一下，分享給大家。

【 CleverCode在csdn博客中的原創作品，請勿轉載，原創地址：http://blog.csdn.net/clevercode/article/details/46310835】

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” 來獲取。參考地址：http://dev.mysql.com/doc/refman/5.6/en/show-profile.html。

 

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