之前我介紹過msyql查詢最佳化explain檢查命令的使用，explain主要是檢查sql語句的基本效能，sql是否優秀，但不能檢視具體的涉及硬體資源的開銷，今天要介紹的這個profiling工具可以更細節的檢視資源的開銷，比較詳細。
首先這款效能檢查工具是針對每個session生效的，session結束了就要重要發起查詢檢測。
預設是關閉的，需要手動開啟：
SET profiling = 1;
開啟之後，發往mysql伺服器的語句可以透過SHOW PROFILES顯示出來，預設顯示15條，最大設定為100，透過設定變數profiling_history_size實現，設定為0將會禁用profiling。
語法
SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type:
ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS
關於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
-
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
使用示例
檢視有沒有啟用profiling
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
開啟profiling
mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)
執行要分析的SQL語句
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE T1 (id INT);
Query OK, 0 rows affected (0.01 sec)
檢查所有抓取到的分析語句效能指標
mysql> SHOW PROFILES;
+----------+----------+--------------------------+
| Query_ID | Duration | Query |
+----------+----------+--------------------------+
| 0 | 0.000088 | SET PROFILING = 1 |
| 1 | 0.000136 | DROP TABLE IF EXISTS t1 |
| 2 | 0.011947 | CREATE TABLE t1 (id INT) |
+----------+----------+--------------------------+
3 rows in set (0.00 sec)
顯示單個分析語句效能指標，指最近執行次數最多的那一條
mysql> SHOW PROFILE;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| checking permissions | 0.000040 |
| creating table | 0.000056 |
| After create | 0.011363 |
| query end | 0.000375 |
| freeing items | 0.000089 |
| logging slow query | 0.000019 |
| cleaning up | 0.000005 |
+----------------------+----------+
7 rows in set (0.00 sec)
具體檢視某條分析語句的效能
mysql> SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| query end | 0.000107 |
| freeing items | 0.000008 |
| logging slow query | 0.000015 |
| cleaning up | 0.000006 |
+--------------------+----------+
4 rows in set (0.00 sec)
你也可以檢視CPU或者其他資源消耗資訊
mysql> SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000040 | 0.000038 | 0.000002 |
| creating table | 0.000056 | 0.000028 | 0.000028 |
| After create | 0.011363 | 0.000217 | 0.001571 |
| query end | 0.000375 | 0.000013 | 0.000028 |
| freeing items | 0.000089 | 0.000010 | 0.000014 |
| logging slow query | 0.000019 | 0.000009 | 0.000010 |
| cleaning up | 0.000005 | 0.000003 | 0.000002 |
+----------------------+----------+----------+------------+
7 rows in set (0.00 sec)
其他使用方式
也可以透過查表的方式檢視分析語句的效能，所有show能看到的都會記錄在INFORMATION_SCHEMA表中，比如：
SELECT STATE, FORMAT(DURATION, 6) AS DURATION FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 2 ORDER BY SEQ;
SHOW與INFORMATION_SCHEMA對應關係表：
| INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
| QUERY_ID | Query_ID |
|
| SEQ |
|
|
| STATE | Status |
|
| DURATION | Duration |
|
| CPU_USER | CPU_user |
|
| CPU_SYSTEM | CPU_system |
|
| CONTEXT_VOLUNTARY | Context_voluntary |
|
| CONTEXT_INVOLUNTARY | Context_involuntary |
|
| BLOCK_OPS_IN | Block_ops_in |
|
| BLOCK_OPS_OUT | Block_ops_out |
|
| MESSAGES_SENT | Messages_sent |
|
| MESSAGES_RECEIVED | Messages_received |
|
| PAGE_FAULTS_MAJOR | Page_faults_major |
|
| PAGE_FAULTS_MINOR | Page_faults_minor |
|
| SWAPS | Swaps |
|
| SOURCE_FUNCTION | Source_function |
|
| SOURCE_FILE | Source_file |
|
| SOURCE_LINE | Source_line |
|
注意
INFORMATION_SCHEMA這個表的使用方式已經在mysql5.7.2已經標記廢除了，在未來的版本將會徹底刪除掉，SHOW的使用方式在未來的版本也會替代掉，替代使用方式為MySQL Performance Schema，具體的參考官網的使用：https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html
以上profiling所有介紹翻譯來源於官網，原版可以參考：https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
