MySQL 主鍵缺失導致從庫 Hang 死深度剖析：Row 模式全表掃描的災難與急救方案

MySQL 主鍵缺失導致從庫 Hang 死深度剖析：Row 模式全表掃描的災難與急救方案

故障現象

• 線上頻繁出現 Slave 延遲 (Seconds_Behind_Master 持續增長)
• SHOW SLAVE STATUS\G 顯示 Slave_SQL_Running: Yes 但實際卡住
• SHOW OPEN TABLES 顯示特定表長期 In_use=1

Slave_IO_State: Waiting for master to send event
Slave_SQL_Running: Yes
Seconds_Behind_Master: 1339  -- 持續增長
Relay_Log_Space: 29409335    -- 大量積壓

根因分析：Row 模式 + 無主鍵 + 大量刪除/更新 = 全表掃描風暴

1. Binlog Row 模式下的行級事件

無主鍵表執行 UPDATE dmpush_message_temp SET stat=1; (全表更新 22 萬行)，Binlog 記錄 **每行一條事件**：

### UPDATE qianyi.dmpush_message_temp
### WHERE
### @1='fb5c72c9...'  -- 所有列值 (無主鍵)
### @2=133
### @3='20121012220000'
### @4='0'
### SET
### @4='1'

解析 Binlog 發現：**229,226 條 UPDATE 事件** (約等於表行數)。

2. 從庫重放邏輯

• SQL 執行緒逐條執行事件
• **無主鍵 → 無法定位行 → 每條事件觸發全表掃描**
• 22 萬次全表掃描 × 22 萬行 = **500 億行級掃描**，IOPS 耗盡，從庫凍結

3. 對比：有主鍵時

加上 id INT AUTO_INCREMENT PRIMARY KEY 後，同樣全表更新：

### UPDATE qianyi.dmpush_message_temp
### WHERE
### @1='fb5c72c9...'
### @5=1   -- 主鍵值
### SET
### @4='1'

事件攜帶主鍵 @5，從庫可直接 **主鍵定位 (O(1))**，無全表掃描，秒級完成。

緊急急救方案：繞過故障事件追趕主庫

前提： Binlog 為 Row 模式，故障表無主鍵，主庫已產生大量全表掃描事件。

步驟 1：強制停止從庫 (SQL 執行緒已卡死，需 Kill 進程)

mysql> STOP SLAVE;  -- 可能卡住，需 Ctrl+C 強制中斷
# 作業系統層面 Kill
ps -ef | grep 3006
kill -9  

步驟 2：跳過故障表的 Relay Log 事件

1. 配置檔加入 skip-slave-start，啟動 MySQL (不自動啟動複製)
2. 從庫**清空故障表資料** (結構保留)：
   SET SQL_LOG_BIN=0; TRUNCATE TABLE qianyi.dmpush_message_temp;
3. 啟動複製：START SLAVE;
4. 從庫執行到該表事件會報 **Error 1032 (Can't find record)**，編寫腳本自動跳過：
   sh /tmp/skip.sh 3006 dmpush_message_temp

步驟 3：從主庫同步最新資料覆蓋

待 Seconds_Behind_Master = 0 後：

-- 主庫：鎖表導出
LOCK TABLES qianyi.dmpush_message_temp READ;
CREATE TABLE a2 LIKE qianyi.dmpush_message_temp;
LOCK TABLES a2 WRITE, qianyi.dmpush_message_temp READ;
INSERT INTO a2 SELECT * FROM qianyi.dmpush_message_temp;
UNLOCK TABLES;

-- 從庫：覆蓋導入
SET SQL_LOG_BIN=0;
DROP TABLE qianyi.dmpush_message_temp;
CREATE TABLE qianyi.dmpush_message_temp LIKE a2;
INSERT INTO qianyi.dmpush_message_temp SELECT * FROM a2;

步驟 4：通知業務方添加主鍵 (根治)

ALTER TABLE dmpush_message_temp ADD COLUMN id INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id);

快速定位卡住表的技巧

mysql> SHOW OPEN TABLES;
+----------+-----------------------+--------+-------------+
| Database | Table                 | In_use | Name_locked |
+----------+-----------------------+--------+-------------+
| qianyi   | dmpush_message_temp   | 1      | 0           |  -- 即為罪魁禍首
| qianyi   | test                  | 0      | 0           |
+----------+-----------------------+--------+-------------+

主鍵設計鐵律 (InnoDB)

1. 每張表必須有顯式主鍵，無業務主鍵則用 BIGINT AUTO_INCREMENT 代理鍵
2. 優先自增整數 (INT/BIGINT)：
   • 插入效能最佳 (尾部追加，無頁分裂)
   • 二級索引存主鍵值，主鍵越小 → 二級索引越小 → 記憶體命中率越高
   • 減少頁碎片，空間利用率高
3. 嚴禁 UUID/MD5/長字串作主鍵：隨機寫入導致嚴重頁分裂、索引膨脹、緩存污染
4. 業務唯一鍵改為 Unique Index，主鍵專職叢集索引組織

避坑檢查清單

• ☐ 所有表是否有 PRIMARY KEY？
• ☐ 主鍵是否為 AUTO_INCREMENT BIGINT？
• ☐ Binlog 是否為 ROW 模式 (推薦)？
• ☐ 是否有大批量 UPDATE/DELETE 無 WHERE 條件或無索引條件？
• ☐ 監控是否覆蓋 Seconds_Behind_Master、Slave_SQL_Running_State？

**主鍵是 InnoDB 的生命線**。缺失主鍵在 Row 複製下會引發從庫全表掃描風暴，輕則延遲、重則凍結。建表即加主鍵，無例外。