MySQL 創建索引三大演進：Copy Table、Inplace、Online DDL 原理深度解析與 Bug 避坑

MySQL 創建索引三大演進：Copy Table、Inplace、Online DDL 原理深度解析與 Bug 避坑

MySQL 對 ADD INDEX 經歷了 Copy Table → Inplace → Online 三代演進。本文基于 InnoDB 源碼流程（sql_table.cc、handler0alter.cc、row0merge.cc、row0log.cc），深度剖析三種方式的處理流程、鎖行為、版本限制、Online DDL 的 Row Log 重放機制、Unique 索引衝突處理、已知 Bug 復現與規避，並補充 posix_fadvise/fallocate 文件操作優化技巧。

一、三種方式對比

二、Inplace Add Index 流程與關鍵問題

mysql_alter_table() → check_if_incompatible_data() → add_index()
  → row_merge_create_index() → row_merge_build_indexes()
    → row_merge_read_clustered_index() -- 跳過刪除項
    → row_merge_sort() → row_merge_insert_index_tuples()
  → wait_while_table_is_used() -- 等待只讀事務結束
  → final_add_index()

三大疑問：

1. 索引字典維護 trx_id 作用？→ 標識創建事務 ID，舊事務（ReadView 早於此 ID）嘗試用新索引快照讀時直接報錯 ERROR 1412: Table definition has changed。
2. 遍歷聚簇索引為何跳過刪除項？→ 新索引無版本信息，舊事務無法正確 MVCC，直接禁止使用。
3. 為何等待只讀事務結束？→ 保證這些事務不會使用到無版本信息的新索引。

三、Online Add Index 核心流程

mysql_alter_table() → check_if_supported_inplace_alter()
  → prepare_inplace_alter_table() → dict_stats_wait_bg_to_stop_using_tables()
  → row_log_allocate() -- 分配 Row Log（默認 1MB Block，由 innodb_sort_buffer_size 控制）
  → dict_index_set_online_status(index, ONLINE_INDEX_CREATION) -- 標記狀態，DML 寫入 Row Log
  → inplace_alter_table() → row_merge_build_index() -- 讀聚簇索引、排序、插入新索引
  → row_log_apply() -- 重放 Row Log 關鍵階段
    → rw_lock_x_lock(new_index) -- 鎖新索引樹
    → row_log_apply_ops() -- 分 Block 重放
      -- 中間 Block：無需鎖，新 DML 寫入最後 Block
      -- 最後一個 Block：持有鎖重放完成，新索引與聚簇索引達到一致
    → dict_index_set_online_status(index, ONLINE_INDEX_COMPLETE) -- 標記完成，清理 Row Log
  → commit_inplace_alter_table()

Row Log 重放鎖優化：僅跨 Block 切換時短暫鎖，最後一個 Block 持鎖完成。低峰期影響可接受。

四、Online DDL 關鍵特性與限制

• 支持 Unique 索引：維護 row_merge_dup_t 記錄衝突，重放後檢測衝突失敗則回滾。
• 更嚴格的事務限制：聚簇索引遍歷結束後，將新索引 trx_id 設為 Row Log 中最大事務 ID，所有較早事務（含創建過程中啟動的）均不可用新索引。
• 重複記錄去重：遍歷階段已寫入新索引的記錄，重放 Row Log 時檢測存在則跳過/轉換操作類型。

五、Online Unique Index Bug 復現（5.6 早期版本）

CREATE TABLE t1(a INT PRIMARY KEY, b INT, c CHAR(250)) ENGINE=InnoDB;
-- 插入大量數據，max(a)=196591
UPDATE t1 SET b=a; -- b 無重複
SESSION 1: ALTER TABLE t1 ADD UNIQUE INDEX idx_t1_b(b); -- Online 建索引
SESSION 2: INSERT INTO t1(b,c) VALUES(196592,'b');
         UPDATE t1 SET b=196589 WHERE a=196582; -- 製造 b=196589 重複
         DELETE FROM t1 WHERE a=262127;
-- 索引創建成功，但表中存在兩行 b=196589，違反唯一約束！

結論：Row Log 重放邏輯未覆蓋所有邊界情況，MySQL 5.6 穩定前慎用 Online Unique Index。

六、源碼級優化技巧借鑒

• posix_fadvise(fd, 0, 0, POSIX_FADV_DONTNEED)：丟棄文件 Cache 中的 clean blocks，保護 Buffer Cache，適合大文件順序讀寫場景。
• fallocate(fd, FALLOC_FL_PUNCH_HOLE, offset, len)：釋放文件中間空洞空間，實現稀疏文件真正釋放磁盤。