背景

一條 SQL 語句的執行完，每個模塊耗時，不同資源(CPU/IO/IPC/SWAP)消耗情況我該如何知道呢？別慌俺有 - MySQL profiling

1. SQL語句執行前 - 開啟profiling

-- profiling (0-關閉 1-開啟)
-- 或者：show variables like 'profiling';
mysql> select @@profiling;-- 開啟
mysql> SET profiling = 1;
-- 關閉
mysql> SET profiling = 0;

2. 執行某個SQL語句

3. 執行分析

3.1 顯示當前會話產生的所有profiles
mysql> show profiles;
說明：每次最多顯示最近15條profiles
Query_ID: 每條SQL語句ID編號
Duration：SQL語句執行時長
Query：具體的SQL語句

3.2 查看最近一條SQL執行情況
mysql> show profile;

3.3 查看指定query id的執行情況
mysql> show profile for query xxx_query_id;

1. Status: 顯示了SQL執行的一個完整的生命周期，涉及到各個階段。
2. Duration: 表示SQL在這個階段的耗時。

3.4 執行資源(CPU/IO/IPC/SWAP)分析
mysql> show profile CPU, BLOCK IO for query xxx_query_id;
說明：show profile不指定type時默認只顯示 Status 和 Duration
ALL：顯示所有的開銷信息。
BLOCK IO：顯示塊存儲設備輸入和輸出的次數，即從硬盤讀取和寫入數據的次數。只有當數據量大于內存可用量時，才會借助硬盤進行內存交換（Swap），才會產生硬盤讀取和寫入。
CONTEXT SWITCHES：上下文切換開銷。
CPU：顯示CPU開銷信息。
IPC：顯示發送和接收開銷信息。
MEMORY：顯示內存開銷信息。
PAGE FAULTS：顯示頁面錯誤開銷信息。
SOURCE：顯示和Source_function，Source_file， Source_line相關的開銷信息。
SWAPS：顯示交換次數開銷信息。

注意事項：

1. profile統計的數據依賴于底層system call調用，某些操作系統上不支持調用時值會返回NULL
2. profiling統計的信息是進程級別而非線程級別，統計信息時刻在變更。
3. Profiling信息還可以通過INFORMATION_SCHEMA PROFILING table查詢
   SELECT STATE, FORMAT(DURATION, 6) AS DURATION FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 2 ORDER BY SEQ;
4. 從profiling分析結果Status中得到優化方向
   • converting HEAP to MyISAM: 查詢結果太大，內存不夠用
   • Creating tmp table: 創建臨時表。先拷貝數據到臨時表，用完后再刪除臨時表
   • Copying to tmp table on disk: 把內存中臨時表復制到磁盤上
   • locked: 發生了死鎖行為

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
}

參考文檔

https://dev.mysql.com/doc/refman/8.0/en/show-profile.html

profiling Statue : https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html