MySQL 從庫延遲排查案例：單執行緒複寫 CPU 滿載與無主鍵表導致的鎖競爭

巡檢發現從庫無業務負載卻單核 CPU 100%，主庫慢查詢無寫入 SQL。經分析 Binlog 定位到高頻 DELETE/INSERT 的表 xx_db.xxtable，結合 SHOW ENGINE INNODB STATUS 發現大量 GEN_CLUST_INDEX 鎖與表級鎖，根因為該表缺少顯式主鍵。新增無業務意義的自增主鍵後，CPU 瞬間降至 3%，複寫延遲徹底消除。

現象與初步判斷

• 從庫單核 CPU 100%，Seconds_Behind_Master 持續增長。
• 主庫無明顯慢寫入 SQL，排除主庫壓力傳導。
• 判斷為單執行緒 SQL 執行緒（SQL Thread）處理 Binlog 事件過慢。

Binlog 統計分析

工具：parsebinlog（mysql-binlog-statistic）

git clone https://github.com/wubx/mysql-binlog-statistic.git
cd mysql-binlog-statistic/bin/
parsebinlog /u1/mysql/logs/mysql-bin.000806
...
Table xx_db.xxtable:
  Type DELETE opt: 101246
  Type INSERT opt: 103265
...

單 Binlog 內同一表超 10 萬次 DELETE + INSERT，極高頻寫入。

InnoDB 狀態確認

mysql> SHOW ENGINE INNODB STATUS\G
...
---TRANSACTION ..., ACTIVE 3 sec fetching rows
mysql tables in use 1, locked 1
3361 lock struct(s), heap size 407992, 477888 row lock(s)
TABLE LOCK table xx_db.xxtable trx id ... lock mode IX
RECORD LOCKS space id 1002 page no 1975 n bits 1120 index `AK_movieid` ...
RECORD LOCKS space id 1002 page no 6965 n bits 264 index `GEN_CLUST_INDEX` ...
... (TOO MANY LOCKS PRINTED)
...

關鍵發現

• GEN_CLUST_INDEX：InnoDB 為無主鍵表自動生成的 6 字節隱藏叢集索引（Row ID）。
• 大量 GEN_CLUST_INDEX 記錄鎖：隱藏主鍵按插入順序遞增，導致頁面分裂、鎖競爭劇烈。
• 表級鎖（lock mode IX）降低並發度。

解決方案：新增顯式主鍵

ALTER TABLE xx_db.xxtable ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;

效果：

• CPU 單核佔用從 100% 降至 3%。
• 複寫延遲瞬間歸零，持續觀察 24h 無復發。
• 顯式主鍵使叢集索引有序，避免頁面分裂，鎖粒度更細，並發度大幅提升。

最佳實務總結

1. 所有 InnoDB 表必須有顯式主鍵（推薦自增 BIGINT/UNSIGNED）。
2. 避免使用唯一非空次要索引替代主鍵（仍會生成 GEN_CLUST_INDEX）。
3. 複寫延遲排查步驟：Seconds_Behind_Master → SHOW SLAVE STATUS → Binlog 統計 → SHOW ENGINE INNODB STATUS 鎖分析 → 結合結構優化。
4. 高頻小批次寫入表，考慮批次提交或改用 LOAD DATA / 多值 INSERT 減少 Binlog 事件數。