您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 大量資料快速插入實戰：InnoDB vs MyISAM、分區表、DISABLE KEYS 極速優化

編輯：MySQL 綜合教學

實驗背景

測試環境：相同硬體、相同資料量（200 萬/2000 萬行），對比不同存儲引擎與優化手段的插入效能。

方案一：InnoDB + 分區表 + 存儲過程單行插入

建表

CREATE DATABASE ecommerce;

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    birth TIMESTAMP,
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
) 
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (10000),
    PARTITION p1 VALUES LESS THAN (50000),
    PARTITION p2 VALUES LESS THAN (100000),
    PARTITION p3 VALUES LESS THAN (150000),
    PARTITION p4 VALUES LESS THAN MAXVALUE
) ENGINE=InnoDB;

存儲過程單行循環插入

CREATE PROCEDURE BatchInsert(IN init INT, IN loop_time INT)
BEGIN
    DECLARE Var INT DEFAULT 0;
    DECLARE ID INT DEFAULT init;
    WHILE Var < loop_time DO
        INSERT INTO employees(id,fname,lname,birth,hired,separated,job_code,store_id)
        VALUES(ID, CONCAT('chen',ID), CONCAT('haixiang',ID), NOW(), NOW(), NOW(), 1, ID);
        SET ID = ID + 1;
        SET Var = Var + 1;
    END WHILE;
END;

執行結果

CALL BatchInsert(30036, 200000);
-- 耗時：3小時 37分 8秒（20萬行）

分析：單行 INSERT + 存儲過程循環 = 大量網路往返 + 每行提交事務 + 索引維護開銷，效能極差。

方案二：MyISAM + 分區表 + 批量插入 + DISABLE KEYS

建表

CREATE TABLE customer (
    id INT NOT NULL,
    email VARCHAR(64) NOT NULL,
    name VARCHAR(32) NOT NULL,
    password VARCHAR(32) NOT NULL,
    phone VARCHAR(13),
    birth DATE,
    sex INT(1),
    avatar BLOB,
    address VARCHAR(64),
    regtime DATETIME,
    lastip VARCHAR(15),
    modifytime TIMESTAMP NOT NULL,
    PRIMARY KEY (id)
) ENGINE=MyISAM
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (100000),
    PARTITION p1 VALUES LESS THAN (500000),
    PARTITION p2 VALUES LESS THAN (1000000),
    PARTITION p3 VALUES LESS THAN (1500000),
    PARTITION p4 VALUES LESS THAN (2000000),
    PARTITION p5 VALUES LESS THAN MAXVALUE
);

關鍵優化：DISABLE/ENABLE KEYS

-- 1. 禁用非唯一索引更新
ALTER TABLE customer DISABLE KEYS;

-- 2. 批量插入（存儲過程或多行 VALUES）
CALL BatchInsertCustomer(1, 2000000);

-- 3. 重新建立索引（一次性排序建立，極快）
ALTER TABLE customer ENABLE KEYS;

執行結果

-- 耗時：8分 50秒（200萬行）
-- 效能提升：約 245 倍！

核心優化原理解析

優化點	原理	適用引擎
DISABLE KEYS	插入過程中不維護非唯一索引，資料載入完成後一次性排序建立 B-Tree，減少 90%+ 索引維護開銷	MyISAM（InnoDB 不支援）
批量 INSERT	單條 SQL 插入多行 `VALUES (...),(...),(...)`，減少網路往返與解析開銷	所有引擎
LOAD DATA INFILE	直接從檔案讀取，繞過 SQL 層，速度最快	所有引擎
分區表	資料分散寫入不同物理檔案，減少鎖競爭，利用並行 I/O	所有引擎
事務批次提交	InnoDB 每 1000~5000 行提交一次，平衡恢復性能與 Undo Log 空間	InnoDB

InnoDB 大量插入最佳實踐（不支援 DISABLE KEYS）

-- 1. 自動提交關閉，手動控制事務邊界
SET autocommit = 0;

-- 2. 批次大小 1000~5000 行
INSERT INTO t (cols) VALUES (...),(...),(...);  -- 批次 1
COMMIT;
INSERT INTO t (cols) VALUES (...),(...),(...);  -- 批次 2
COMMIT;
...

-- 3. 關閉唯一性檢查（若確定資料無重複）
SET unique_checks = 0;
-- 插入操作
SET unique_checks = 1;

-- 4. 關閉外鍵檢查
SET foreign_key_checks = 0;
-- 插入操作
SET foreign_key_checks = 1;

-- 5. 調大 InnoDB 緩衝池與 Redo Log
-- innodb_buffer_pool_size = 物理記憶體 70%
-- innodb_log_file_size = 1G~4G
-- innodb_flush_log_at_trx_commit = 2  （導入期間暫時犧牲 ACID 換速度）

引擎切換技巧

-- 導入階段用 MyISAM 極速載入
ALTER TABLE t ENGINE = MyISAM;
-- ... 大量插入 ...
-- 導入完成切回 InnoDB 獲得事務支援
ALTER TABLE t ENGINE = InnoDB;

注意：ALTER TABLE ... ENGINE 會重建表，大表耗時較長，建議離線維護窗口執行。

生產級導入工具推薦

mysqlpump：MySQL 5.7+ 內建，支援並行導出/導入、壓縮、進度顯示
mydumper/myloader：多線程、斷點續傳、壓縮、更快
LOAD DATA INFILE：原生最快，配合 LOCAL 參數跨主機導入
pt-archiver：Percona Toolkit，適合線上分批遷移/歸檔

總結

大量資料插入的核心公式：減少網路往返（批量/LOAD DATA）+ 減少索引維護（DISABLE KEYS/延遲建立）+ 減少事務開銷（批次提交）+ 利用並行 I/O（分區/多線程）。MyISAM + DISABLE KEYS 是極速導入的「核武器」，但缺乏事務；InnoDB 需組合多種參數調優才能逼近同級效能。生產環境建議：離線導入用 MyISAM→切回 InnoDB，線上遷移用 pt-archiver 或分批事務。