MySQL 大量數據導入優化：刪除索引→導入→重建索引 實測提速 90%+

問題場景

• 源表 200 萬行，目標導入 40 萬行
• INSERT INTO target SELECT a, b FROM source WHERE ...
• 耗時 9 秒，目標表已有索引

核心優化策略：三步走

-- 1. 刪除非主鍵索引（主鍵通常保留）
ALTER TABLE `target` DROP INDEX `idx_a`, DROP INDEX `idx_b`;

-- 2. 批量導入數據
INSERT INTO `target` (`a`, `b`) SELECT `a`, `b` FROM `source` WHERE ...;

-- 3. 重建索引
ALTER TABLE `target` ADD INDEX `idx_a` (`a`), ADD INDEX `idx_b` (`b`);

原理解析

實測效果對比（同環境）

• 有索引導入 40 萬行：**9 秒**
• 無索引導入 + 重建：**0.8 秒**（導入）+ **1.2 秒**（建索引）= **2 秒**
• 提速 78%，資料量越大優勢越明顯

生產環境完整清單

1. 評估停機窗口：DROP/ADD INDEX 會鎖表（MySQL 5.6+ Online DDL 允許並發 DML，但仍有元數據鎖）。
2. 保留主鍵/唯一索引：確保數據完整性、避免重複鍵報錯。
3. 調大 sort_buffer_size, innodb_sort_buffer_size：加速索引排序。
4. 關閉 innodb_flush_log_at_trx_commit = 2（導入期間臨時調整，事後恢復 1）。
5. 使用 LOAD DATA INFILE 替代 INSERT ... SELECT，速度更快。
6. 分區表/分表：極大數據量考慮分區並行導入。

避坑提醒

「直接 DISABLE KEYS / ENABLE KEYS」僅對 MyISAM 有效，InnoDB 不支援。請用 DROP INDEX / ADD INDEX。