MySQL 海量資料遷移實戰：6 億單表分區並行遷移

單表 6 億行、按月分區的 MySQL 遷移全記錄

背景：單表 6 億+ 記錄，InnoDB，按月 PARTITION BY RANGE。需遷移並新增 date 欄位。PC 測試環境 8G 內存、4 核。

踩坑演進

1. 分頁 LIMIT：SELECT * LIMIT 10000 OFFSET ... 隨偏移量線性變慢，不可用
2. 分區剪枝：WHERE created_at BETWEEN '2012-01-01' AND '2012-01-31' 自動命中單分區，EXPLAIN PARTITIONS 驗證
3. 引擎差異：MyISAM 導入 4h/月，InnoDB 32h/月（8 倍差）

InnoDB 寫入優化關鍵參數

[mysqld]
innodb_buffer_pool_size = 6G        # 物理內存 70-80%
innodb_flush_log_at_trx_commit = 2  # 1→2：不每次刷盤，僅刷 OS cache
innodb_log_file_size = 512M         # 大事務減少 checkpoint
innodb_log_buffer_size = 16M        # 峰值寫入緩衝

調優後 InnoDB 導入效能追平 MyISAM（~3.5h/月）。

並行遷移方案

單線程仍慢 → Shell 多進程按**天**並行導出導入：

# 導出單日（按分區剪枝自動命中）
SELECT col1, col2, ..., DATE_FORMAT(created_at, '%Y.%m.%d') AS new_date
INTO OUTFILE '/data/sinawb20120724/111101.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM TB_SINA_STATUS
WHERE created_at >= '2011-11-01 00:00:00' AND created_at < '2011-11-02 00:00:00';

# 並行導入（多進程同時跑）
LOAD DATA LOCAL INFILE '/data/.../111101.txt'
IGNORE INTO TABLE NEW_TB_SINA_STATUS
CHARACTER SET UTF8
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n';

效能對比

關鍵工具指令

SET profiling = 1;
SHOW PROFILES;
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;
SHOW STATUS LIKE 'Handler%';
EXPLAIN PARTITIONS SELECT ...;

遷移清單

• [ ] 目標表預建分區結構、索引
• [ ] 調整 InnoDB 寫入參數、重啟生效
• [ ] 編寫 Shell 並行導出導入腳本（按分區/天）
• [ ] 驗證行數、抽樣核對關鍵欄位
• [ ] 切換讀寫流量、觀察監控
• [ ] 遷移後恢復 innodb_flush_log_at_trx_commit = 1