MySQL中实现大数据量的快速插入

一、SQL语句优化​

1. ​批量插入代替单条插入​

• ​单条插入会频繁触发事务提交和日志写入，效率极低。
• ​批量插入通过合并多条数据为一条SQL语句，减少网络传输和SQL解析开销。

-- 低效写法：逐条插入
INSERT INTO table (col1, col2) VALUES (1, 'a');
INSERT INTO table (col1, col2) VALUES (2, 'b');-- 高效写法：批量插入
INSERT INTO table (col1, col2) VALUES 
(1, 'a'), (2, 'b'), (3, 'c'), ...;

• ​建议单次插入数据量​：控制在 500~2000 行（避免超出 max_allowed_packet）。

2. ​禁用自动提交（Autocommit）​​

• 默认情况下，每条插入都会自动提交事务，导致频繁的磁盘I/O。
• ​手动控制事务，将多个插入操作合并为一个事务提交：

START TRANSACTION;
INSERT INTO table ...;
INSERT INTO table ...;
...
COMMIT;

• ​注意​：事务过大可能导致 undo log 膨胀，需根据内存调整事务批次（如每 1万~10万 行提交一次）。

3. ​**使用 LOAD DATA INFILE**​

• 从文件直接导入数据，比 INSERT 快 ​20倍以上，跳过了SQL解析和事务开销。

LOAD DATA LOCAL INFILE '/path/data.csv' 
INTO TABLE table
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

• ​适用场景​：从CSV或文本文件导入数据。

4. ​禁用索引和约束​

• 插入前禁用索引（尤其是唯一索引和全文索引），插入完成后重建：

-- 禁用索引
ALTER TABLE table DISABLE KEYS;
-- 插入数据...
-- 重建索引
ALTER TABLE table ENABLE KEYS;

• ​禁用外键检查​：

SET FOREIGN_KEY_CHECKS = 0;
-- 插入数据...
SET FOREIGN_KEY_CHECKS = 1;

---

​二、参数配置优化​

1. ​InnoDB引擎参数调整​

• ​**innodb_flush_log_at_trx_commit**​：
  • 默认值为 1（每次事务提交都刷盘），改为 0 或 2 可减少磁盘I/O。
  • 0：每秒刷盘（可能丢失1秒数据）。
  • 2：提交时写入OS缓存，不强制刷盘。
• ​**innodb_buffer_pool_size**​：
  • 增大缓冲池大小（通常设为物理内存的 70%~80%），提高数据缓存命中率。
• ​**innodb_autoinc_lock_mode**​：
  • 设为 2（交叉模式），减少自增锁竞争（需MySQL 8.0+）。

2. ​调整网络和包大小​

• ​**max_allowed_packet**​：
  • 增大允许的数据包大小（默认 4MB），避免批量插入被截断。
• ​**bulk_insert_buffer_size**​：
  • 增大批量插入缓冲区大小（默认 8MB）。

3. ​其他参数​

• ​**back_log**​：增大连接队列长度，应对高并发插入。
• ​**innodb_doublewrite**​：关闭双写机制（牺牲数据安全换取性能）。

---

​三、存储引擎选择​

1. ​MyISAM引擎​

• ​优点​：插入速度比InnoDB快（无事务和行级锁开销）。
• ​缺点​：不支持事务和崩溃恢复，适合只读或允许数据丢失的场景。

2. ​InnoDB引擎​

• ​优点​：支持事务和行级锁，适合高并发写入。
• ​优化技巧​：
  • 使用 innodb_file_per_table 避免表空间碎片。
  • 主键使用自增整数（避免随机写入导致的页分裂）。

---

​四、硬件和架构优化​

1. ​使用SSD硬盘​

• 替换机械硬盘为SSD，提升I/O吞吐量。

2. ​分库分表​

• 将单表拆分为多个子表（如按时间或ID范围），减少单表压力。
• 使用中间件（如ShardingSphere）或分区表（PARTITION BY）。

3. ​读写分离​

• 主库负责写入，从库负责查询，降低主库压力。

4. ​异步写入​

• 将数据先写入消息队列（如Kafka），再由消费者批量插入数据库。

---

​五、代码层面优化​

1. ​多线程并行插入​

• 将数据分片，通过多线程并发插入不同分片。
• ​注意​：需确保线程间无主键冲突。

2. ​预处理语句（Prepared Statements）​​

• 复用SQL模板，减少解析开销：

// Java示例
String sql = "INSERT INTO table (col1, col2) VALUES (?, ?)";
PreparedStatement ps = conn.prepareStatement(sql);
for (Data data : list) {ps.setInt(1, data.getCol1());ps.setString(2, data.getCol2());ps.addBatch();
}
ps.executeBatch();

---

​六、性能对比示例​

优化方法	插入10万条耗时（秒）
逐条插入（默认）	120
批量插入（1000行/次）	5
LOAD DATA INFILE	1.5

---

​总结​

• ​核心思路​：减少磁盘I/O、降低锁竞争、合并操作。
• ​推荐步骤​：
  1. 优先使用 LOAD DATA INFILE 或批量插入。
  2. 调整事务提交策略和InnoDB参数。
  3. 优化表结构（禁用非必要索引）。
  4. 根据硬件和场景选择存储引擎。
  5. 在架构层面分库分表或异步写入。

通过上述方法，可在MySQL中实现每秒数万甚至数十万条的高效插入。