MySQL 海量資料遷移實戰：6 億行單表、分區並行、InnoDB 調優全記錄

專案背景

• 單表 6 億+ 行，按月分區（Partition by RANGE on datetime）。
• 需遷移至新表並新增 DATE 型別欄位（原 DATETIME）。
• 目標：最小停機、資料零損失、可驗證一致性。

遷移演進歷程

階段 1：朴素分頁 SELECT + INSERT（極慢）

SELECT * FROM tb WHERE id > 40000 AND id < 50001
INTO OUTFILE '/tmp/data.txt';
LOAD DATA INFILE '/tmp/data.txt' INTO TABLE new_tb;

問題：OFFSET 大時全表掃描，單批 1 萬行耗時不可接受。

階段 2：利用分區裁剪

-- 確認分區裁剪生效
EXPLAIN PARTITIONS SELECT ... FROM tb WHERE ir_created_at >= '2011-11-01' AND ir_created_at < '2011-11-02';
-- 僅掃描 p201111 分區

按月匯出/匯入，配合 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\\\' 標準格式。

階段 3：引擎對比——MyISAM vs InnoDB 驚人差距

關鍵調優參數（InnoDB 寫入加速核心四件套）

[mysqld]
# 1. 緩衝池：專用機設 70-80% 記憶體
innodb_buffer_pool_size = 16G

# 2. 重做日誌檔案：大檔案減少檢查點，提升寫入
innodb_log_file_size = 1G
innodb_log_files_in_group = 4

# 3. 重做日誌緩衝：大批量寫入時避免頻繁刷盤
innodb_log_buffer_size = 64M

# 4. 提交刷盤策略：2 = 寫入 OS 快取，每秒刷盤（極大提升寫入）
innodb_flush_log_at_trx_commit = 2

# 5. 繞過 OS 快取，直接 I/O（Linux/RAID/SSD 必開）
innodb_flush_method = O_DIRECT

# 6. 並行 I/O 執行緒（MySQL 5.6+）
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 20000
innodb_io_capacity_max = 40000

階段 4：並行分區遷移（Shell 多進程）

#!/bin/bash
# migrate_parallel.sh - 按天並行遷移
PARTITION_DATES=("2011-11-01" "2011-11-02" ... "2012-12-31")
MAX_JOBS=4

for DATE in "${PARTITION_DATES[@]}"; do
    # 控制併發數
    while [ $(jobs -r | wc -l) -ge $MAX_JOBS ]; do sleep 5; done

    (
        OUT_DIR="/home/mysql/data/sinawb/${DATE//-/}"
        mkdir -p "$OUT_DIR"
        OUTFILE="${OUT_DIR}/${DATE}.txt"

        # 1. 匯出（按天分區裁剪）
        mysql -uroot -p${PASS} -e "
            SELECT col1, col2, ..., DATE_FORMAT(ir_created_at,'%Y.%m.%d')
            FROM TB_SINA_STATUS
            WHERE ir_created_at >= '${DATE} 00:00:00'
              AND ir_created_at < DATE_ADD('${DATE}', INTERVAL 1 DAY)
            INTO OUTFILE '${OUTFILE}'
            FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\\\'
            LINES TERMINATED BY '\n';
        "

        # 2. 匯入
        mysql -uroot -p${PASS} -e "
            LOAD DATA LOCAL INFILE '${OUTFILE}'
            IGNORE INTO TABLE NEW_TB_SINA_STATUS
            CHARACTER SET UTF8
            FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\\\'
            LINES TERMINATED BY '\n';
        "
        echo "[$(date)] Partition ${DATE} done"
    ) &
done
wait
echo "All partitions migrated"

效能分析工具箱

-- 1. Profiling 開啟與查看
SET profiling = 1;
-- 執行目標 SQL
SHOW PROFILES;
SHOW PROFILE CPU, BLOCK IO FOR Query 1;

-- 2. 即時狀態
SHOW STATUS LIKE 'Innodb%';
SHOW PROCESSLIST;

-- 3. 執行計畫
EXPLAIN PARTITIONS SELECT ...;
EXPLAIN ANALYZE SELECT ...;  -- MySQL 8.0.18+

遷移驗證清單

1. 行數一致：SELECT COUNT(*) FROM old_tb PARTITION (p...) vs new_tb 同條件。
2. 抽樣核對：CHECKSUM TABLE 或應用層 MD5 比對關鍵欄位。
3. 新欄位正確：DATE(ir_created_at) = new_date_col 全表驗證。
4. 索引/約束完整：SHOW CREATE TABLE 雙向比對。
5. 應用端測試：灰度切流 1% 流量跑新表，監控錯誤率與延遲。

經驗總結

• 分區表遷移必須利用分區裁剪，避免全表掃描。
• InnoDB 寫入效能高度依賴四大參數，調優後可匹敵 MyISAM。
• 並行度 MAX_JOBS 視 CPU 核心、磁碟 IOPS、innodb_io_capacity 調整，通常 4-8 為宜。
• LOAD DATA ... IGNORE 可跳過主鍵衝突，配合冪等重跑機制保證最終一致性。
• 全程記錄開始/結束時間、行數、錯誤日誌，建立可追溯遷移報告。