MySQL 8中提升InnoDB的LOAD DATA INFILE导入速度方案咨询

问题背景 #

使用MyISAM引擎10余年，其适配我的业务场景：每日一次无并发写入、高频SELECT查询。现在为使用分区功能切换到InnoDB，但LOAD DATA INFILE导入速度极慢，急需优化。

表特性：

• 860列（无法规范化，所有列均用于生成查询结果集）
• 4个索引，主键由Cell和Time两列组成
• 每日需导入约80000行数据，3年累计近1亿行，目前未实现分区

测试基准：向空表导入51000行数据，MyISAM仅需12秒，InnoDB最优耗时12分钟（慢60倍），完全无法接受。

现有实验结果 #

以下为不同参数/工具的测试数据：

1. 导入耗时17分09秒（默认参数）

innodb_flush_log_at_trx_commit=1 /* 默认值 */
innodb_buffer_pool_size=0.125 GB /* 默认值 */

2. 导入耗时12分04秒

innodb_flush_log_at_trx_commit=1 /* 默认值 */
innodb_buffer_pool_size=7 GB /* 总内存8GB */

3. 导入耗时11分59秒

innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=7 GB /* 总内存8GB */

4. 导入耗时11分47秒

innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=7 GB /* 总内存8GB */

5. 导入耗时10分59秒（导入前删除所有索引）

innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=7 GB /* 总内存8GB */

6. 导入耗时03分15秒（最快但不可用，导入前删除所有索引+主键）

innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=7 GB /* 总内存8GB */

7. 导入耗时14分钟（使用MySQL Shell的util.import_table，4线程并行，innodb_flush_log_at_trx_commit=1/2结果一致）
8. 导入耗时13分20秒（开启autocommit=0）

SET autocommit=0;
Load data infile '//Import_tools//process_hua//export1.csv' into table testdb.h_cell_inno FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
COMMIT;

补充实验 #

• 仅保留60列时：MyISAM导入耗时2秒，InnoDB耗时50秒（参数：innodb_flush_log_at_trx_commit=2、innodb_buffer_pool_size=6 GB）
• 重复导入对比：MyISAM后续导入51000行耗时8-10秒；InnoDB首次导入12分钟，第二次导入相同数据耗时超26分钟

进一步优化建议 #

1. 索引策略优化 #

• 删除重复索引：表中PRIMARY KEY (Cell,Time)与KEY Index1 (Cell,Time)完全重复，直接删除Index1，减少索引维护开销
• 导入时仅保留主键：删除所有二级索引，导入完成后再重建二级索引。虽然重建需要额外时间，但整体导入+重建的总耗时大概率低于带索引导入
• 预处理CSV顺序：将CSV按主键Cell升序、Time升序排序后导入，避免InnoDB插入时的页分裂，大幅提升写入效率

2. InnoDB参数临时调整（导入后恢复默认值） #

• 增大重做日志文件：设置innodb_log_file_size=2GB（需重启服务），减少检查点刷新频率，降低IO开销
• 开启innodb_flush_method=O_DIRECT：绕过操作系统缓存，避免双重缓存，提升写入效率
• 关闭双写缓冲：设置innodb_doublewrite=0，减少一次IO操作（注意：此操作降低崩溃恢复能力，仅适合无并发的导入场景）
• 关闭唯一性检查：设置unique_checks=0，避免导入时的主键唯一性检查开销
• 调整事务日志刷写：保持innodb_flush_log_at_trx_commit=2，平衡性能与数据安全性

3. 导入命令与流程优化 #

• 使用LOCAL选项：开启local_infile=1后，执行LOAD DATA LOCAL INFILE，减少服务器端IO交互
• 分批导入+提交：将CSV拆分为多个小文件（如每个5000行），每次导入一个文件后执行COMMIT，避免单次事务过大导致的日志刷写压力
• 处理重复数据：导入前清理重复数据，或使用INSERT ... ON DUPLICATE KEY UPDATE Time=Time（无实际更新），减少主键冲突的处理开销

4. 硬件层面优化 #

• 分离IO负载：将InnoDB数据文件与日志文件放在不同的SSD磁盘上
• 提升磁盘性能：更换为更高IOPS的SSD设备

数据库替换考量 #

若上述优化仍无法达到可接受的速度，可考虑切换到PostgreSQL：

• PostgreSQL的COPY命令在大数据量导入时性能优异，配合UNLOGGED TABLE（导入时设置为无日志表，完成后转为普通表）可大幅提升速度
• 支持成熟的分区表功能，查询优化器对分区表的适配性良好，能保持高频SELECT的性能
• 完全支持ACID特性，满足数据一致性需求

注意：迁移时需处理数据类型兼容、索引重建、查询语句适配等问题，建议先做小范围测试验证导入速度与查询性能。

内容的提问来源于stack exchange，提问作者Ivaylo