前言

線上千萬級的大表在新增字段的時候，一定要小心，我見過太多團隊在千萬級大表上執行DDL時翻車的案例。

很容易影響到正常用戶的使用。

本文將深入剖析大表加字段的核心難點，并給出可落地的解決方案。

希望對你會有所幫助。

1.為什么大表加字段如此危險？

核心問題：MySQL的DDL操作會鎖表。

當執行ALTER TABLE ADD COLUMN時：

1. MySQL 5.6之前：全程鎖表（阻塞所有讀寫）
2. MySQL 5.6+：僅支持部分操作的Online DDL

通過實驗驗證鎖表現象：

-- 會話1：執行DDL操作
ALTER TABLE user ADD COLUMN age INT;
-- 會話2：嘗試查詢（被阻塞）
SELECT * FROM user WHERE id=1; -- 等待DDL完成

鎖表時間計算公式：

鎖表時間 ≈ 表數據量 / 磁盤IO速度

對于1000萬行、單行1KB的表，機械磁盤（100MB/s）需要100秒的不可用時間！

如果在一個高并發的系統中，這個問題簡直無法忍受。

那么，我們要如何解決問題呢？

2.原生Online DDL方案

在MySQL 5.6+版本中可以使用原生Online DDL的語法。

例如：

ALTER TABLE user 
ADD COLUMN age INT,
ALGORITHM=INPLACE, 
LOCK=NONE;

實現原理：

致命缺陷：

1. 仍可能觸發表鎖（如添加全文索引）
2. 磁盤空間需雙倍（實測500GB表需要1TB空閑空間）
3. 主從延遲風險（從庫單線程回放）

3.停機維護方案

適用場景：

• 允許停服時間（如凌晨3點）
• 數據量小于100GB（減少導入時間）
• 有完整回滾預案

4.使用PT-OSC工具方案

Percona Toolkit的pt-online-schema-change這個是我比較推薦的工具。

工作原理：

操作步驟：

# 安裝工具
sudo yum install percona-toolkit
# 執行遷移（添加age字段）
pt-online-schema-change \
--alter "ADD COLUMN age INT" \
D=test,t=user \
--execute

5.邏輯遷移 + 雙寫方案

還有一個金融級安全的方案是：邏輯遷移 + 雙寫方案。

適用場景：

• 字段變更伴隨業務邏輯修改（如字段類型變更）
• 要求零數據丟失的金融場景
• 超10億行數據的表

實施步驟：

1. 創建新表結構

-- 創建包含新字段的副本表
CREATE TABLE user_new (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50),
    -- 新增字段
    age INT DEFAULT 0,
    -- 增加原表索引
    KEY idx_name(name)
) ENGINE=InnoDB;

2. 雙寫邏輯實現（Java示例）

// 數據寫入服務
public class UserService {
    @Transactional
    public void addUser(User user) {
        // 寫入原表
        userOldDAO.insert(user);
        // 寫入新表（包含age字段）
        userNewDAO.insert(convertToNew(user));
    }
    
    private UserNew convertToNew(User old) {
        UserNew userNew = new UserNew();
        userNew.setId(old.getId());
        userNew.setName(old.getName());
        // 新字段處理（從其他系統獲取或默認值）
        userNew.setAge(getAgeFromCache(old.getId()));
        return userNew;
    }
}

3. 數據遷移（分批處理）

-- 分批遷移腳本
SET @start_id = 0;
WHILE EXISTS(SELECT 1 FROM user WHERE id > @start_id) DO
    INSERT INTO user_new (id, name, age)
    SELECT id, name, 
        COALESCE(age_cache, 0) -- 從緩存獲取默認值
    FROM user
    WHERE id > @start_id
    ORDER BY id
    LIMIT 10000;
    
    SET @start_id = (SELECT MAX(id) FROM user_new);
    COMMIT;
    -- 暫停100ms避免IO過載
    SELECT SLEEP(0.1); 
END WHILE;

4. 灰度切換流程

這套方案適合10億上的表新增字段，不過操作起來比較麻煩，改動有點大。

6.使用gh-ost方案

gh-ost（GitHub's Online Schema Transmogrifier）是GitHub開源的一種無觸發器的MySQL在線表結構變更方案。

專為解決大表DDL（如新增字段、索引變更、表引擎轉換）時鎖表阻塞、主庫負載高等問題而設計。

其核心是通過異步解析binlog，替代觸發器同步增量數據，顯著降低對線上業務的影響。

與傳統方案對比

• 觸發器方案（如pt-osc）：
  在源表上創建INSERT/UPDATE/DELETE觸發器，在同一事務內將變更同步到影子表。
  痛點：

  • 觸發器加重主庫CPU和鎖競爭，高并發時性能下降30%以上
  • 無法暫停，失敗需重頭開始
  • 外鍵約束支持復雜

• gh-ost方案：

  • 偽裝為從庫：直連主庫或從庫，拉取ROW格式的binlog，解析DML事件（INSERT/UPDATE/DELETE）
  • 異步應用：將增量數據通過獨立連接應用到影子表（如REPLACE INTO處理INSERT事件），與主庫事務解耦
  • 優先級控制：binlog應用優先級 > 全量數據拷貝，確保數據強一致

關鍵流程：

• 全量拷貝：按主鍵分塊（chunk-size控制）執行INSERT IGNORE INTO _table_gho SELECT ...，避免重復插入
• 增量同步：
  • INSERT → REPLACE INTO
  • UPDATE → 全行覆蓋更新
  • DELETE → DELETE
• 原子切換（Cut-over）：
  1. 短暫鎖源表（毫秒級）
  2. 執行原子RENAME：RENAME TABLE source TO _source_del, _source_gho TO source
  3. 清理舊表（_source_del）

典型命令示例：

gh-ost \
--alter="ADD COLUMN age INT NOT NULL DEFAULT 0 COMMENT '用戶年齡'" \
--host=主庫IP --port=3306 --user=gh_user --password=xxx \
--database=test --table=user \
--chunk-size=2000 \       # 增大批次減少事務數
--max-load=Threads_running=80 \ 
--critical-load=Threads_running=200 \
--cut-over-lock-timeout-seconds=5 \  # 超時重試
--execute \               # 實際執行
--allow-on-master         # 直連主庫模式

2. 監控與優化建議

• 進度跟蹤：

echo status | nc -U /tmp/gh-ost.sock  # 查看實時進度

• 延遲控制：
  • 設置--max-lag-millis=1500，超閾值自動暫停
  • 從庫延遲過高時切換為直連主庫模式
• 切換安全：
  使用--postpone-cut-over-flag-file人工控制切換時機

7.分區表滑動窗口方案

適用場景：

• 按時間分區的日志型大表
• 需要頻繁變更結構的監控表

核心原理：
通過分區表特性，僅修改最新分區結構。

操作步驟：

修改分區定義：

-- 原分區表定義
CREATE TABLE logs (
    id BIGINT,
    log_time DATETIME,
    content TEXT
) PARTITION BY RANGE (TO_DAYS(log_time)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01'))
);
-- 添加新字段（僅影響新分區）
ALTER TABLE logs ADD COLUMN log_level VARCHAR(10) DEFAULT 'INFO';

創建新分區（自動應用新結構）：

-- 創建包含新字段的分區
ALTER TABLE logs REORGANIZE PARTITION p202302 INTO (
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01'))
);

歷史數據處理：

-- 僅對最近分區做數據初始化
UPDATE logs PARTITION (p202302) 
SET log_level = parse_log_level(content);

8.千萬級表操作注意事項

1. 主鍵必須存在（無主鍵將全表掃描）
2. 磁盤空間監控（至少預留1.5倍表空間）
3. 復制延遲控制

SHOW SLAVE STATUS; 
-- 確保Seconds_Behind_Master < 10

4. 灰度驗證步驟：

   • 先在從庫執行
   • 檢查數據一致性
   • 低峰期切主庫

5. 字段屬性選擇：

   • 避免NOT NULL（導致全表更新）
   • 優先使用ENUM代替VARCHAR
   • 默認值用NULL而非空字符串

9.各方案對比

以下是針對千萬級MySQL表新增字段的6種方案的對比。

總結

1. 常規場景（<1億行）：

   • 首選 Online DDL（ALGORITHM=INSTANT，MySQL 8.0秒級加字段）
   • 備選 PT-OSC（兼容低版本MySQL）

2. 高并發大表（>1億行）：

   • 必選 gh-ost（無觸發器設計，對寫入影響<5%）

3. 金融核心表：

   • 雙寫方案 是唯一選擇（需2-4周開發周期）

4. 日志型表：

   • 分區滑動窗口 最優（僅影響新分區）

5. 緊急故障處理：

   • 超百億級表異常時，考慮 停機維護 + 回滾預案

給大家一些建議：

• 加字段前優先使用 JSON字段預擴展（ALTER TABLE user ADD COLUMN metadata JSON）
• 萬億級表建議 分庫分表 而非直接DDL
• 所有方案執行前必須 全量備份（mysqldump + binlog）
• 流量監測（Prometheus+Granfa實時監控QPS）

在千萬級系統的戰場上，一次草率的ALTER操作可能就是壓垮駱駝的最后一根稻草。

?轉自https://www.cnblogs.com/12lisu/p/19008591