SHOW PROFILE

SHOW PROFILE是MySQL的一個效能分析命令，可以跟蹤SQL各種資源消耗。使用格式如下：

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type: {
    ALL                     顯示所有資訊
  | BLOCK IO                顯示阻塞的輸入輸出次數
  | CONTEXT SWITCHES				顯示自願及非自願的上下文切換次數
  | CPU											顯示使用者與系統CPU使用時間
  | IPC											顯示訊息傳送與接收的次數
  | MEMORY									顯示記憶體相關的開銷，目前未實現此功能
  | PAGE FAULTS							顯示頁錯誤相關開銷資訊
  | SOURCE									列出相應操作對應的函式名及其在原始碼中的位置(行)
  | SWAPS										顯示swap交換次數
}

預設情況下，SHOW PROFILE只展示Status和Duration兩列，如果想展示更多資訊，可指定type。

使用步驟如下：

使用如下命令，檢視是否支援SHOW PROFILE功能，yes標誌支援。從MySQL 5.0.37開始，MySQL支援SHOW PROFILE。
```
select @@have_profiling;
```
檢視當前是否啟用了SHOW PROFILE，0表示未啟用，1表示已啟用
```
select @@profiling;
```
使用如下命令為當前會話開啟或關閉效能分析，設成1表示開啟，0表示關閉
```
set profiling = 1;
```
使用SHOW PROFILES命令，可為最近傳送的SQL語句做一個概要的效能分析。展示的條目數目由profiling_history_size會話變數控制，該變數的預設值為15。最大值為100。將值設定為0具有禁用分析的實際效果。
```
-- 預設展示15條
show profiles

-- 使用profiling_history_size調整展示的條目數
set profiling_history_size = 100;
```

使用show profile分析指定查詢：

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)

-- 預設情況下，只展示Status和Duration兩列，如果想展示更多資訊，可指定type。
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 PROFILE功能：
```
set profiling = 0;
```

TIPS

MySQL官方文件宣告SHOW PROFILE已被廢棄，並建議使用Performance Schema作為替代品。

在某些系統上，效能分析只有部分功能可用。比如，部分功能在Windows系統下無效（show profile使用了getrusage()這個API，而在Windows上將會返回false，因為Windows不支援這個API）；此外，效能分析是程式級的，而不是執行緒級的，這意味著其他執行緒的活動可能會影響到你看到的計時資訊。

INFORMATION_SCHEMA.PROFILING

INFORMATION_SCHEMA.PROFILING用來做效能分析。它的內容對應SHOW PROFILE和SHOW PROFILES 語句產生的資訊。除非設定了 set profiling = 1; ，否則該表不會有任何資料。該表包括以下欄位：

QUERY_ID：語句的唯一標識
SEQ：一個序號，展示具有相同QUERY_ID值的行的顯示順序
STATE：分析狀態
DURATION：在這個狀態下持續了多久（秒）
CPU_USER，CPU_SYSTEM：使用者和系統CPU使用情況（秒）
CONTEXT_VOLUNTARY，CONTEXT_INVOLUNTARY：發生了多少自願和非自願的上下文轉換
BLOCK_OPS_IN，BLOCK_OPS_OUT：塊輸入和輸出操作的數量
MESSAGES_SENT，MESSAGES_RECEIVED：傳送和接收的訊息數
PAGE_FAULTS_MAJOR，PAGE_FAULTS_MINOR：主要和次要的頁錯誤資訊
SWAPS：發生了多少SWAP
SOURCE_FUNCTION，SOURCE_FILE，SOURCE_LINE：當前狀態是在原始碼的哪裡執行的

TIPS
SHOW PROFILE本質上使用的也是INFORMATION_SCHEMA.PROFILING表；

INFORMATION_SCHEMA.PROFILING表已被廢棄，在未來可能會被刪除。未來將可使用Performance Schema替代，詳見
下面兩個SQL是等價的：
SHOW PROFILE FOR QUERY 2;

SELECT STATE, FORMAT(DURATION, 6) AS DURATION
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = 2 ORDER BY SEQ;

PERFORMANCE_SCHEMA

PERFORMANCE_SCHEMA是MySQL建議的效能分析方式，未來SHOW PROFILE、INFORMATION_SCHEMA.PROFILING都會廢棄。據筆者研究，PERFORMANCE_SCHEMA在MySQL 5.6引入，因此，在MySQL 5.6及更高版本才能使用。可使用SHOW VARIABLES LIKE 'performance_schema'; 檢視啟用情況，MySQL 5.7開始預設啟用。

下面來用PERFORMANCE_SCHEMA去實現SHOW PROFILE類似的效果：

檢視是否開啟效能監控

mysql> SELECT * FROM performance_schema.setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| %    | %    | %    | YES     | YES     |
+------+------+------+---------+---------+

預設是開啟的。

你也可以執行類似如下的SQL語句，只監控指定使用者執行的SQL：

mysql> UPDATE performance_schema.setup_actors
       SET ENABLED = 'NO', HISTORY = 'NO'
       WHERE HOST = '%' AND USER = '%';

mysql> INSERT INTO performance_schema.setup_actors
       (HOST,USER,ROLE,ENABLED,HISTORY)
       VALUES('localhost','test_user','%','YES','YES');

這樣，就只會監控localhost機器上test_user使用者傳送過來的SQL。其他主機、其他使用者發過來的SQL統統不監控。

執行如下SQL語句，開啟相關監控項：

mysql> UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES', TIMED = 'YES'
       WHERE NAME LIKE '%statement/%';

mysql> UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES', TIMED = 'YES'
       WHERE NAME LIKE '%stage/%';
       
mysql> UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
       WHERE NAME LIKE '%events_statements_%';

mysql> UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
       WHERE NAME LIKE '%events_stages_%';

使用開啟監控的使用者，執行SQL語句，比如：

mysql> SELECT * FROM employees.employees WHERE emp_no = 10001;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
+--------+------------+------------+-----------+--------+------------+

執行如下SQL，獲得語句的EVENT_ID。

mysql> SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
       FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%10001%';
+----------+----------+--------------------------------------------------------+
| event_id | duration | sql_text                                               |
+----------+----------+--------------------------------------------------------+
|       31 | 0.028310 | SELECT * FROM employees.employees WHERE emp_no = 10001 |
+----------+----------+--------------------------------------------------------+

這一步類似於 SHOW PROFILES。

執行如下SQL語句做效能分析，這樣就可以知道這條語句各種階段的資訊了。

mysql> SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
       FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=31;
+--------------------------------+----------+
| Stage                          | Duration |
+--------------------------------+----------+
| stage/sql/starting             | 0.000080 |
| stage/sql/checking permissions | 0.000005 |
| stage/sql/Opening tables       | 0.027759 |
| stage/sql/init                 | 0.000052 |
| stage/sql/System lock          | 0.000009 |
| stage/sql/optimizing           | 0.000006 |
| stage/sql/statistics           | 0.000082 |
| stage/sql/preparing            | 0.000008 |
| stage/sql/executing            | 0.000000 |
| stage/sql/Sending data         | 0.000017 |
| stage/sql/end                  | 0.000001 |
| stage/sql/query end            | 0.000004 |
| stage/sql/closing tables       | 0.000006 |
| stage/sql/freeing items        | 0.000272 |
| stage/sql/cleaning up          | 0.000001 |
+--------------------------------+----------+

參考文件

學習更多：
慕課網為大家準備了一門按演進思路進行講授的課程：希望能夠幫助大家在演進的過程跟隨千萬級專案從0到100全過程高效成長。

MySQL SQL效能分析

SHOW PROFILE

INFORMATION_SCHEMA.PROFILING

PERFORMANCE_SCHEMA

參考文件

相關文章