萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

MySQL 批量插入優化實戰:MyISAM/InnoDB 引擎專屬加速策略

影響插入速度的核心因素

  • 索引維護開銷 (B+樹分裂、二級索引寫入)
  • 唯一性檢查 (唯一索引/主鍵衝突檢測)
  • 外鍵檢查 (InnoDB)
  • 事務日志刷盤 (Redo Log、Binlog、雙 1 安全模式)
  • 單條 INSERT 語句開銷 (網絡往返、解析、優化、執行)

MyISAM 引擎優化策略

1. 禁用索引 → 批量導入 → 重建索引


-- 非空表批量導入前
ALTER TABLE `tbl_name` DISABLE KEYS;
-- 導入數據 (LOAD DATA / 批量 INSERT)
LOAD DATA INFILE '/path/data.txt' INTO TABLE `tbl_name` ...;
-- 或
INSERT INTO `tbl_name` VALUES (...), (...), ...;
-- 導入完成後
ALTER TABLE `tbl_name` ENABLE KEYS;

原理: MyISAM 批量導入後統一排序建索引,比逐行維護 B+樹快數量級。
注意: 空表導入無需此操作 (MyISAM 導入後才建索引);InnoDB **不支持** DISABLE KEYS。

2. 禁用唯一性檢查


SET SESSION UNIQUE_CHECKS = 0;
-- 批量導入
SET SESSION UNIQUE_CHECKS = 1;

3. 使用 LOAD DATA INFILE (最快)


LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
INTO TABLE `tbl_name`
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(col1, col2, col3, ...);

前提: secure_file_priv 允許路徑、文件在服務器本地、權限正確。
速度: 比 INSERT 快 10-20 倍。

InnoDB 引擎優化策略

1. 禁用唯一性檢查 (同 MyISAM)


SET SESSION UNIQUE_CHECKS = 0;
-- 導入
SET SESSION UNIQUE_CHECKS = 1;

2. 禁用外鍵檢查


SET SESSION FOREIGN_KEY_CHECKS = 0;
-- 導入
SET SESSION FOREIGN_KEY_CHECKS = 1;

3. 禁用自動提交 (批量提交)


SET SESSION AUTOCOMMIT = 0;
-- 批量 INSERT (建議每 1000-5000 行 COMMIT 一次)
INSERT INTO `tbl_name` VALUES (...), (...), ...;  -- 批次 1
COMMIT;
INSERT INTO `tbl_name` VALUES (...), (...), ...;  -- 批次 2
COMMIT;
-- ...
SET SESSION AUTOCOMMIT = 1;

原理: 減少 Redo Log fsync 次數、Binlog 組提交、鎖持有時間。

4. 批量 INSERT 多值語法 (應用層組裝)


-- 應用層拼接 (單條 SQL 最大 max_allowed_packet,默認 64MB)
INSERT INTO `tbl_name` (col1, col2, col3) VALUES 
(?, ?, ?), (?, ?, ?), ... (?, ?, ?);  -- 500-2000 行/批

對比: 1 萬條單 INSERT ≈ 30 秒;批量 1000 行/條 ≈ 0.5 秒 (提升 60 倍)。

5. LOAD DATA INFILE (InnoDB 同樣最快)


SET SESSION UNIQUE_CHECKS=0, FOREIGN_KEY_CHECKS=0, AUTOCOMMIT=0;
LOAD DATA INFILE ... INTO TABLE `tbl_name` ...;
COMMIT;
SET SESSION UNIQUE_CHECKS=1, FOREIGN_KEY_CHECKS=1, AUTOCOMMIT=1;

大表分批導入模板 (應用層控制)


# Python 伪代碼
batch_size = 2000
rows = [...]  # 數據源生成器

conn = get_connection()
cursor = conn.cursor()
cursor.execute("SET SESSION UNIQUE_CHECKS=0, FOREIGN_KEY_CHECKS=0, AUTOCOMMIT=0")

sql = "INSERT INTO `tbl_name` (col1, col2, col3) VALUES " + ",".join(["(%s,%s,%s)"] * batch_size)

for i in range(0, len(rows), batch_size):
    batch = rows[i:i+batch_size]
    flat = [val for row in batch for val in row]
    cursor.execute(sql, flat)
    if (i // batch_size) % 10 == 0:
        conn.commit()  # 每 10 批提交一次,平衡事務大小與恢復點

conn.commit()
cursor.execute("SET SESSION UNIQUE_CHECKS=1, FOREIGN_KEY_CHECKS=1, AUTOCOMMIT=1")

系統級參數調優 (臨時調大,導入後恢復)

參數導入時建議值說明
innodb_buffer_pool_size物理內存 80%+盡量缓住所有數據/索引頁
innodb_log_file_size4 GB減少 Checkpoint 刷盤頻率
innodb_flush_log_at_trx_commit2事務提交不強制 fsync,僅寫 OS 緩存 (風險:掉電丟 1 秒)
sync_binlog0Binlog 不強制 fsync (風險:掉電丟 Binlog)
innodb_doublewriteOFF關閉雙寫緩衝 (風險:頁損壞),極速導入可用
innodb_change_buffer_max_size50加大 Change Buffer 合併二級索引寫入

⚠️ 警告: 生產核心庫**嚴禁**調整雙 1 / 雙寫 / Redo 安全參數!僅限可重建的導入環境、測試環境、數據倉庫 ETL。

驗證導入效果


-- 行數核對
SELECT COUNT(*) FROM `tbl_name`;

-- 關鍵指標抽樣
SELECT MIN(id), MAX(id), COUNT(DISTINCT col_a) FROM `tbl_name`;

-- 索引碎片檢查
ANALYZE TABLE `tbl_name`;
SHOW TABLE STATUS LIKE 'tbl_name'\G  -- Data_free 應極小
copyright © 萬盛學電腦網 all rights reserved