MySQL 從庫延遲排查實戰：單線程複製 CPU 100% 的元兇——隱式主鍵導致的頁分裂風暴

現場症狀

• 從庫無業務讀寫，但 mysqld 單核 CPU 100%。
• 主庫慢查詢無寫入類 SQL。
• 經典 單線程複製（Single-threaded Replication）瓶頸：SQL 線程單核跑滿。

排查步驟

1. 解析 Relay Log 熱點表

# 利用 parsebinlog 工具（同 GitHub 專案）
./parsebinlog /u1/mysql/logs/mysql-bin.000806
...
Table xx_db.xxtable:
  Type DELETE: 101,246
  Type INSERT: 103,265
...

單位時間內同一表 DELETE+INSERT > 20 萬行，極大寫入壓力集中在 xx_db.xxtable。

2. 從庫 InnoDB 狀態確認

mysql> SHOW ENGINE INNODB STATUS\G
...
---TRANSACTION ..., ACTIVE 3 sec fetching rows
TABLE LOCK table xx_db.xxtable trx id ... lock mode IX
RECORD LOCKS space id 1002 page no 1975 index `AK_movieid` ...
RECORD LOCKS space id 1002 page no 6965 index `GEN_CLUST_INDEX` ...
... (大量 GEN_CLUST_INDEX 記錄鎖)
TOO MANY LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS

關鍵發現：

• 表鎖 IX + 大量 GEN_CLUST_INDEX 記錄鎖 → 無顯式主鍵，InnoDB 生成 6 字節隱式主鍵（GEN_CLUST_INDEX）。
• 隱式主鍵單調遞增，但 DELETE+INSERT 混合導致 頁分裂、碎片、鎖競爭，單線程應用極慢。

根因分析

解決方案：加顯式自增主鍵

ALTER TABLE xx_db.xxtable ADD COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

• 顯式主鍵同樣單調遞增，但 聚簇索引鍵值可控、可預期、便於分區/分表。
• 更關鍵：消除 GEN_CLUST_INDEX 內部機制帶來的額外頁分裂與鎖開銷。

效果驗證

• 執行 DDL 後（Online DDL，無鎖），從庫 CPU 從 100% 降至 3% 左右。
• 複製延遲歸零，持續觀察 24h 無復發。

延伸建議

1. 所有表必須顯式定義主鍵：納入 Schema Review 清單，CI 阻攔無 PK 建表。
2. 升級 5.7+ 開啟 MTS（多線程複製）：slave_parallel_type=LOGICAL_CLOCK，從根本上並行化 SQL 應用。
3. 監控 Handler_read_rnd_next / Innodb_buffer_pool_wait_free：早期發現隱式主鍵熱點。