資料庫效能大揭秘：玩轉MySQL監控指標狀態變數

前言

在本文中，我們將深入探討MySQL資料庫的效能監控世界。透過了解並應用一系列常用的監控指標，我們能夠更精準地把握資料庫的執行狀況。這些指標，通常以狀態變數（status variables）的形式呈現，是洞察資料庫效能的關鍵。

接下來，我們將逐一解析與連線相關的監控變數，包括當前連線數、活躍連線數、快取執行緒數以及已建立的執行緒數。這些指標不僅幫助我們評估連線使用效率，還能預警潛在的效能問題。

此外，文章還將涵蓋查詢效率、臨時表使用、表快取效率、緩衝池狀態、日誌檔案操作、行鎖爭用、排序操作、查詢模式以及流量吞吐等多個方面的監控指標。透過對這些指標的深入分析，我們能夠構建一個全面而有效的資料庫效能監控體系。

透過本文，希望讀者能夠遊刃有餘地配置和最佳化MySQL監控方案，從而在資料庫效能問題出現時，能夠迅速定位並解決。

1. 連線相關

MySQL 預設的排程方式是每一個連線一個執行緒，既 one-thread-per-connection 所以本節涉及到的變數，基本可以視為連線。
Tips：one-thread-per-connection 適合於低併發長連線的環境，而在高併發或大量短連線環境下，大量建立和銷燬執行緒，以及執行緒上下文切換，會嚴重影響效能，如果遇到此類瓶頸，可以使用執行緒池（pool-of-threads）來最佳化。

1.1 連線執行緒

以下是連線執行緒涉及到的變數：

Variable Name	Variable Scope	Variable Meaning
Threads_connected	Global	當前連線（執行緒）數，該值等於 SHOW PROCESSLIST 的總數。
Threads_running	Global	當前處於活躍狀態的連線（執行緒）數，如果該值過大，會導致系統頻繁地切換上下文，CPU 使用率也會比較高。
Threads_cached	Global	Threads cache 快取的執行緒數。在建立新的連線時，會首先檢查 Threads cache 中是否有快取的執行緒。如果有則複用，如果沒有則建立新的執行緒。線上程池的場景中，會禁用 Threads cache 此時該值為 0。
Threads_created	Global	已建立的執行緒數。反應的是累加值，如果該值過大，說明 Threads cache 過小，可考慮適當增大 thread_cache_size 的值。

Tips：建議配置連線數使用率和活躍連線數使用率告警，連線數被佔滿會導致業務報錯，Threads_connected / max_connections 推薦閾值 85% 活躍連線數使用率過高，通常 CPU 使用率也會高，意味著系統很繁忙 Threads_running / max_connections 推薦閾值 50%。

1.2 連線異常

以下是連線異常相關的狀態變數：

Aborted_clients：客戶端已成功建立，但中途異常斷開連線的次數。常見原因有以下幾種。

• 客戶端程式斷開連線前，沒有呼叫 mysql_close() 方法。
• 客戶端連線的休眠時間超過 wait_timeout 的會話值，被伺服器主動斷開。
• 客戶端程式在資料傳輸時突然斷開。
• 資料包的大小超過 max_allowed_packet 的限制。

對於中途斷開的連線，錯誤日誌（log_error_verbosity = 3）中通常會有如下資訊：
[Note] Aborted connection 184618 to db: 'xxx' user: 'xxx' host: 'xxxx' (Got an error reading communication packets)

Aborted_connects：連線 MySQL 服務端失敗的次數。常見的原因有以下幾種。

• 客戶端賬號密碼不準確。
• 沒有指定庫的訪問許可權。
• 連線包中沒有包含正確的資訊。
• 超過 connect_timeout 服務端沒有收到客戶端的連線包。

show status where Variable_name in ('Threads_connected', 'Threads_running', 'Threads_cached', 'Threads_created');

1.3 最大連線數

以下是連線數相關的狀態變數：

Variable Name	Variable Scope	Variable Meaning
Max_used_connections	Global	資料庫歷史最大的連線數。
Max_used_connections_time	Global	連線數達到歷史最大的時間。
Connection_errors_max_connections	Global	連線數佔滿後，應用有新的連線後返回 Too many connections 錯誤，該值也會隨之增大。

Tips：MySQL 中的最大連線數由引數 max_connections 控制，預設是 151。當連線數達到 max_connections 的限制，業務會返回報錯 Too many connections 狀態變數 Connection_errors_max_connections 也會隨之增大。建議基於 Threads_connected / max_connections 做好連線數使用率監控，如果大於 85% 則觸發告警。

show status where Variable_name in ('Max_used_connections', 'Max_used_connections_time', 'Connection_errors_max_connections');

2、Com 相關

統計操作執行的次數。以下狀態變數在監控中使用較多，可以反應資料庫的繁忙程度。

Variable Name	Variable Scope	Variable Meaning
Com_insert	Both	insert 語句執行次數。
Com_select	Both	select 語句執行次數。
Com_update	Both	update 語句執行次數。
Com_delete	Both	delete 語句執行次數。
Com_commit	Both	commit 語句執行次數。
Com_rollback	Both	rollback 語句執行次數。
Com_replace	Both	replace 語句執行次數。

Tips：此類變數可以使用 flush status 命令歸零，重新累加統計。每秒執行事務的次數 TPS 可透過 Com_commit + Com_rollback 每秒增量來計算。

這裡只列出了部分常見操作，完整的可以使用下方 SQL 檢視。

show status like 'Com%';

3、臨時表相關

MySQL 在執行 order by、group by 查詢時，通常會建立一個或兩個臨時表，當臨時表較小時，可以放到記憶體中，較大時則會存在於磁碟上。可以透過以下 3 個變數監控臨時檔案使用情況。

Variable Name	Variable Scope	Variable Meaning
Created_tmp_disk_tables	Both	MySQL 內部臨時錶轉化為磁碟表的數量。
Created_tmp_files	Global	MySQL 建立臨時檔案的數量。
Created_tmp_tables	Both	MySQL 建立在記憶體臨時表的數量。

Tips：理論上來講使用臨時表無法避免，但是肯定是越少越好，並且磁碟臨時表需要保持在一個很小的值，經驗值 Created_tmp_disk_tables / Created_tmp_tables 小於 20%。

show status like 'Created%';

4、Table Cache 相關

為了提升表的訪問效率，表在使用完畢後，不會立即關閉，而是會快取在 Table Cache 中，可透過以下 6 個變數監控 Table Cache 使用情況。

Variable Name	Variable Scope	Variable Meaning
Open_tables	Both	當前開啟表的數量。
Open_table_definitions	Global	當前快取的 frm 檔案的數量。
Opened_tables	Both	開啟過的表的數量。
Open_table_definitions	Global	快取過的 frm 檔案的數量。
Table_open_cache_hits	Both	Table Cache 的命中次數。
Table_open_cache_misses	Both	Table Cache 沒有命中的次數。
Table_open_cache_overflows	Both	表快取被刪除的次數。

當 MySQL 要訪問一張表的時候，首先會檢查該表的檔案描述符是否在 Table Cache 中，如果存在則直接使用，並增大 Table_open_cache_hits 的值，如果不存在，則開啟表，並增大 Opened_tables 和 Table_open_cache_misses 的值。然後將表快取在 Table Cache 中。

當 Table Cache 達到了 table_open_cache 的限制，此時分兩種場景：

1. 快取中存在未使用的表： 會使用 LRU 演算法淘汰掉未使用的表，並在 Table Cache 中刪除，同時會增大 Table_open_cache_overflows 的值。

2. 快取中的表都在使用： 會臨時擴容 Table Cache， 一旦檢測出未使用的表，則觸發清理，從而保持在 table_open_cache 之下。

Tips：如果觀測 Opened_tables 大於 table_open_cache 且在持續增大，意味著 table_open_cache 相對較小，此時可適當調大引數。

show status where Variable_name in ('Open_tables', 'Open_table_definitions', 'Opened_tables', 'Open_table_definitions', 'Table_open_cache_hits', 'Table_open_cache_misses', 'Table_open_cache_overflows');

5. 緩衝池相關

對於 innodb 表引擎來說，使用者資料和索引及系統後設資料，都是以頁的形式儲存在表空間中，表空間是 innodb 對檔案系統上一個或多個物理檔案的抽象，也就是說資料到底還是儲存在磁碟中的。但是磁碟的速度要比記憶體慢太多，速度跟不上 CPU 的計算速度，所以 innodb 引擎需要訪問某個頁的資料時，就會把完整的頁全部載入的記憶體中（頁大小預設 16 k）即使訪問一個頁的一行資料，也需要先把完整的頁載入的記憶體中，Innodb 所有讀寫操作都是在記憶體中完成的，完成讀寫後 innodb 並不會立刻釋放掉，而是先快取起來，後面如果有請求需要用到這張頁的話，就可以直接從記憶體讀取，可以省去磁碟 IO 的開銷。

MySQL 緩衝池也使用 LRU 演算法進行排程，本質是讓熱資料頁在快取中長時間保留，提高查詢訪問效率，但是快取是有限的，LRU 的作用就是減少重複資料頁載入頻率。

以下是緩衝池中資料頁面的相關變數：

Variable Name	Variable Scope	Variable Meaning
innodb_buffer_pool_pages_data	Global	緩衝池中資料頁的數量，包括乾淨頁和髒頁。
innodb_buffer_pool_bytes_data	Global	資料頁的大小，單位是位元組。
innodb_buffer_pool_pages_dirty	Global	髒頁的數量。
innodb_buffer_pool_bytes_dirty	Global	髒頁的大小，單位是位元組。
innodb_buffer_pool_pages_free	Global	空閒頁的數量。
innodb_buffer_pool_pages_misc	Global	用於管理開銷而分配的頁的數量，比如行鎖、自適應雜湊索引等。
innodb_buffer_pool_pages_total	Global	頁的總數量。
innodb_buffer_pool_pages_flushed	Global	髒頁被刷盤的次數。
innodb_buffer_pool_wait_free	Global	等待空閒頁的次數。

show status 
where 
  Variable_name in (
    'innodb_buffer_pool_pages_data', 
    'innodb_buffer_pool_bytes_data', 
    'innodb_buffer_pool_pages_dirty', 
    'innodb_buffer_pool_bytes_dirty', 
    'innodb_buffer_pool_pages_free', 
    'innodb_buffer_pool_pages_misc', 
    'innodb_buffer_pool_pages_total', 
    'innodb_buffer_pool_pages_flushed', 
    'innodb_buffer_pool_wait_free'
  );
	```
	
	如果有大表全表掃描的 SQL 執行的時候需要將整張表都載入到 buffer pool 中，導致 buffer pool 中的熱點資料被置換出去，這種情況叫做快取汙染，可以透過快取命中率來監控此類情況。
● Innodb_buffer_pool_read_requests：邏輯讀的數量，既快取讀。
● Innodb_buffer_pool_reads：物理讀的數量，既磁碟讀。

Innodb 快取命中率 = (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests
OLTP 型業務，快取命中率應大於 95%，如果命中率低，則需要調大 innodb_buffer_pool_size 及排查是否有全表掃描 SQL。
另外，透過下方 SQL 可以觀測 Innodb 刪除、插入、讀取、更改的行數。
show status like 'innodb_rows%';

# 6. Redo log 相關
為了取得更好的讀寫效能，InnoDB 會將資料快取在記憶體中（InnoDB Buffer Pool）對磁碟資料的修改也會落後於記憶體，這時如果程序或機器崩潰，會導致記憶體資料丟失，為了保證資料庫本身的一致性和永續性，InnoDB 維護了 REDO LOG。

修改 Page 之前需要先將修改的內容記錄到 REDO 中，並保證 REDO LOG 早於對應的 Page 落盤，也就是常說的 WAL（Write Ahead Log）日誌優先寫，Redo Log 的寫入是順序 IO，可以獲得更高的 IOPS 從而提升資料庫的寫入效能。

當故障發生導致記憶體資料丟失後，InnoDB 會在重啟時，透過重放 REDO，將 Page 恢復到崩潰前的狀態。

以下是 Redo log 相關的狀態變數：

| Variable Name | Variable Scope | Variable Meaning  |
|  -  |  -  |  -  |
|Innodb_log_waits|Global|因 redo buffer 過小，導致 redo log buffer 刷盤的次數。|
|Innodb_log_write_requests| Global  |寫 redo log buffer 的次數。|
|Innodb_log_writes|  Global  |寫 redo log 次數。|
|Innodb_os_log_fsyncs|  Global  |對 redo log 呼叫 fsync 操作的次數。|
|Innodb_os_log_pending_fsyncs| Global  |fsync 操作等待的次數。|
|Innodb_os_log_pending_writes|  Global |寫 redo log 等待次數。|
|Innodb_os_log_written|   Global  |redo log 的寫入量，單位是位元組。|

透過以上狀態變數可以看出資料庫的寫入情況，如果 Innodb_log_waits 持續增大，需要確認 redo log 檔案和 buffer 相關配置是否合適。另外不能透過 Innodb_os_log_written 來反映 redo 的寫入量，因為 redo log 基本儲存單位是 block 512 bytes 小於基本儲存單位的寫入也會以基本單位來計算。
要評估 Redo log 寫入量可參考下方文件。

推薦閱讀：[How to calculate a good InnoDB log file size](https://www.percona.com/blog/how-to-calculate-a-good-innodb-log-file-size/)

# 7. 行鎖相關
資料庫的核心方向就是高併發，整體業務場景大多是 讀-讀、讀-寫、寫-寫，三類併發場景，看似容易融合到業務場景後也比較複雜。透過鎖機制主要可以幫助我們解決 寫-寫 和 讀-讀 場景下的併發安全問題，所以鎖爭用和鎖等待也是經常遇到的情況，
可透過下方狀態變數了解資料庫中的行鎖資訊：

● Innodb_row_lock_current_waits：當前正在等待行鎖的運算元。
● Innodb_row_lock_time：獲取行鎖花費的總時間，單位毫秒。
● Innodb_row_lock_time_avg：獲取行鎖花費的平均時間，單位毫秒。
● Innodb_row_lock_time_max：獲取行鎖花費的最大時間，單位毫秒。

下面我們來做一個實驗：

root@mysql 14:38: [(none)]>show status like '%Innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 33165 |
| Innodb_row_lock_time_avg | 16582 |
| Innodb_row_lock_time_max | 28845 |
| Innodb_row_lock_waits | 2 |
+-------------------------------+-------+

| Session 1 | Session 2 | 
|  -  |  -  |  
|Begin;|  |
|delete from score where id = 5;|  |
|  |update score set number = 66 where id = 5; -- 等待行鎖|

root@mysql 14:41: [test]>show status like '%Innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 1 |
| Innodb_row_lock_time | 33165 |
| Innodb_row_lock_time_avg | 11055 |
| Innodb_row_lock_time_max | 28845 |
| Innodb_row_lock_waits | 3 |
+-------------------------------+-------+

此時可以發現 Innodb_row_lock_waits 和 Innodb_row_lock_current_waits 都增長了，time 相關的變數需要等事務結束後才會進行計算。
Tips：Innodb_row_lock_current_waits 可以反映當前資料庫行鎖的情況，不過不是很準確，有 bug：https://bugs.mysql.com/bug.php?id=71520

# 8. 排序相關
MySQL 中如果有涉及到排序的操作（ORDER BY、GROUP BY、DISTINCT）操作時，如果無法使用索引，則會使用檔案排序。執行計劃中的 Extra 列會顯示 Using filesort。

MySQL 會為需要 filesort 的會話分配單獨排序的快取區（sort buffer）排序快取區是需要時才分配，且按需分配，最大限制由 sort_buffer_size 控制，預設是 256KB。如果需要排序的記錄較少，既 sort buffer 夠用，那麼在記憶體中排序也是非常快的。如果需要排序的記錄非常多，MySQL 會分批處理，每一批首先會在排序快取區中排序，排序後的結果會儲存在臨時檔案中。每個排序快取區對應一個臨時檔案中的一個 block。處理完畢後，最後再對臨時檔案中的 block 進行歸併排序，相比直接在記憶體中排序需要消耗額外的 IO 和 CPU 計算資源。

以下是排序相關的狀態變數：
● Sort_merge_passes：反映的是 sort buffer 不夠用，使用臨時檔案歸併排序的次數。
● Sort_range：對索引範圍掃描的結果進行排序的次數。
● Sort_rows：排序的記錄數。
● Sort_scan：對全表掃描的結果進行排序的次數。

show status like '%Sort%';

Tips：需要關注 Sort_merge_passes 的值，如果持續增大，說明有行數較大的排序操作，需要定位 SQL 判斷是否調大 sort buffer。

# 9. 查詢相關
以下是查詢相關的狀態變數：

| Variable Name | Variable Scope | Variable Meaning  |
|  -  |  -  |  -  |
|Select_scan|Both|全表掃描的次數，如果是關聯查詢，指的是驅動表執行了全表掃描。|
|Select_full_join| Both |同樣是全表掃描，不過只包含關聯場景，驅動表全表掃描的次數。|
|Select_range|  Both |範圍查詢次數。如果是關聯查詢，指的是驅動表執行了範圍查詢。|
|Select_full_range_join|  Both  |同樣是範圍查詢，不過只包含關聯場景，驅動表全表掃描的次數。|
|Select_range_check| Both |常用於非等值的關聯查詢中。|
|Slow_queries|  Both |慢查詢的數量，無論是否開啟了慢查詢，只要 SQL 執行耗時大於 long_query_time 該值就會增加。|

show status
where
Variable_name in (
'Select_scan',
'Select_full_join',
'Select_range',
'Select_full_range_join',
'Select_range_check',
'Select_range_check',
'Slow_queries'
);
```

Tips：Select_scan 可以反映資料庫是否存在全表掃描的 SQL，從 Slow_queries 可以看出儲存中慢 SQL 的數量，建議為這兩個狀態變數配置監控。

10. 流量相關

以下是流量吞吐相關的狀態變數：
● bytes_received：從客戶端接收的流量大小，單位是位元組。
● bytes_sent：傳送給客戶端端流量大小，單位是位元組。

show status 
where 
  Variable_name in (
    'bytes_received', 
    'bytes_sent'
  );
	```
	
	Tips：資料庫的流量吞吐，可以幫助我們瞭解資料庫的負載狀況和併發處理能力。建議為其每秒增量配置監控。
《行業指標體系白皮書》下載地址：https://www.dtstack.com/resources/1057?src=szsm

《數棧產品白皮書》下載地址：https://www.dtstack.com/resources/1004?src=szsm

《資料治理行業實踐白皮書》下載地址：https://www.dtstack.com/resources/1001?src=szsm

想了解或諮詢更多有關大資料產品、行業解決方案、客戶案例的朋友，瀏覽袋鼠雲官網：https://www.dtstack.com/?src=szbky