【MySQL】如何使用SQL Profiler 效能分析器

楊奇龍發表於2012-11-03
MySql
 MySQL5.0.37版本以上支援profiling,透過使用profiling 功能可以檢視到sql語句消耗資源的更詳細的資訊。通常我們使用explain 檢視執行計劃，結合profile 功能我們可以定位sql 執行過程中的瓶頸到底出現在哪裡？
一 語法：
show profile 的語法格式如下： 
SHOW PROFILE [type [, type] … ] 
   [FOR QUERY n] 
   [LIMIT row_count [OFFSET offset]] 
type: 
 ALL      - displays all information
 BLOCK IO - displays counts for block input and output Operations
 CONTEXT SWITCHES - displays counts for voluntary and involuntary context switches
 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
二 如何使用
預設情況下profiling 為0 ，
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)
開啟profiling,則設定該值為1
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)
查詢一些sql 
mysql> SELECT t0.id AS id1,  t0.frozen AS frozen6, t0.gmt_create AS gmt_create9, t0.gmt_update AS gmt_update10 
    -> FROM tabname t0 WHERE t0.uid = '46061' LIMIT 1;
+------+---------+---------------------+---------------------+
| id1  | frozen6 | gmt_create9         | gmt_update10        |
+------+---------+---------------------+---------------------+
| 7922 |    0.00 | 2012-10-29 22:45:09 | 2012-10-31 15:23:24 |
+------+---------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> show profiles;
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                                                                                   |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|        1 | 0.00048900 | SELECT t0.id AS id1,  t0.frozen AS frozen6, t0.gmt_create AS gmt_create9, t0.gmt_update AS gmt_update10 
FROM tabname t0 WHERE t0.uid = '46061' LIMIT 1                    |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
Query_ID --- 是執行sql 的編號，如果你執行了7個 ，show profiles的時候，ID 會顯示1---7
Duration -----sql 的執行時間
Query      -----具體執行的sql 語句。
mysql> show profile for query 1;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000141 |
| Opening tables     | 0.000032 |
| System lock        | 0.000009 |
| Table lock         | 0.000013 |
| init               | 0.000031 |
| optimizing         | 0.000015 |
| statistics         | 0.000074 |
| preparing          | 0.000020 |
| executing          | 0.000006 |
| Sending data       | 0.000088 |
| end                | 0.000007 |
| query end          | 0.000006 |
| freeing items      | 0.000035 |
| logging slow query | 0.000006 |
| cleaning up        | 0.000006 |
+--------------------+----------+
15 rows in set (0.00 sec)
executing,sending data都是很快的，因為該sql 的執行計劃很正確了！
檢視執行該sql所花費的 block io,cpu資料：
mysql> show profile BLOCK IO ,cpu for query 4;
+--------------------+----------+----------+------------+--------------+---------------+
| Status             | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------+----------+----------+------------+--------------+---------------+
| starting           | 0.000141 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables     | 0.000032 | 0.000000 |   0.000000 |            0 |             0 |
| System lock        | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |
| Table lock         | 0.000013 | 0.000000 |   0.000000 |            0 |             0 |
| init               | 0.000031 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing         | 0.000015 | 0.000000 |   0.000000 |            0 |             0 |
| statistics         | 0.000074 | 0.000000 |   0.000000 |            0 |             0 |
| preparing          | 0.000020 | 0.000000 |   0.000000 |            0 |             0 |
| executing          | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data       | 0.000088 | 0.000000 |   0.000000 |            0 |             0 |
| end                | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |
| query end          | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items      | 0.000035 | 0.000000 |   0.000000 |            0 |             0 |
| logging slow query | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up        | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
+--------------------+----------+----------+------------+--------------+---------------+
15 rows in set (0.00 sec)

當然也可以使用
show profile SOURCE,MEMORY,CONTEXT SWITCHES for query N; 檢視其它資訊!
當sql 的效能出現問題之後，除了定位sql的執行計劃之外，可以選擇使用 profile 來對sql的瓶頸進行定位！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-748258/，如需轉載，請註明出處，否則將追究法律責任。

相關文章