如何提升MySQL批量插入性能？Windows环境索引插入过慢求助

针对你在Windows 10 + MySQL 5.7环境下遇到的带索引插入数据极慢的问题，结合你的32GB内存配置（完全足够支撑高效导入）和操作场景，我整理了几个实用的优化方案，帮你大幅提升导入效率：

先解决找不到配置文件的问题 #

在Windows版本的MySQL 5.7中，配置文件默认是**my.ini**而非my.cnf，默认存放路径是C:\ProgramData\MySQL\MySQL Server 5.7\my.ini——注意ProgramData是隐藏文件夹，需要在文件夹选项中开启「显示隐藏的文件、文件夹和驱动器」才能看到。

核心优化方案（按优先级排序） #

1. 先删除索引，导入后重建（最有效！） #

你提到表必须保留索引，但带着索引逐行插入数据的效率远低于先删索引、导入完成后再重建索引。InnoDB维护二级索引是逐行更新，而批量重建索引是通过排序批量构建，效率差几个数量级：

• 导入前执行：

  DROP INDEX 2017_index ON 2017;
  

• 导入完成后执行：

  CREATE INDEX 2017_index ON 2017(MOB_NO, ACT_DATE);
  

  哪怕算上重建索引的时间，总耗时也会比带着索引导入少很多（比如从22小时缩短到几十分钟）。

2. 调整InnoDB缓冲池大小 #

你的32GB内存完全可以给InnoDB分配更多缓冲池，减少磁盘IO：
在my.ini的[mysqld]段添加或修改：

innodb_buffer_pool_size = 16G  # 建议设为物理内存的50%-70%，比如16G或20G

修改后重启MySQL服务，这样导入时大部分数据和索引操作都能在内存中完成，大幅提升速度。

3. 优化InnoDB日志相关参数 #

增大重做日志文件并调整刷盘策略，减少磁盘IO瓶颈：

• 增大日志文件大小（修改后需要重启MySQL，且要先删除旧日志）：

  innodb_log_file_size = 1G  # 建议设为1G-2G，最大不超过4G
  

  操作步骤：停MySQL服务 → 删除C:\ProgramData\MySQL\MySQL Server 5.7\data下的ib_logfile0和ib_logfile1 → 重启服务。
• 临时调整日志刷盘策略（导入完成后改回1保证数据安全性）：

  SET GLOBAL innodb_flush_log_at_trx_commit = 2;
  

  这个参数设为2时，日志每秒刷一次到磁盘，而非每次事务提交都刷，能大幅提升写入速度。

4. 修正LOAD DATA语句的语法错误 #

你的导入语句里有个明显的语法问题：INTO TABLE 2017COLUMNS应该是INTO TABLE 2017 COLUMNS（表名和COLUMNS之间要有空格），修正后的完整语句：

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
LOAD DATA LOCAL INFILE 'D:/base/test/2017/2017.txt' 
INTO TABLE 2017 
COLUMNS TERMINATED BY '|';
COMMIT;

另外，如果文本文件的字段顺序和表结构完全一致，可以正常导入；如果不一致，建议明确指定列名，比如：

LOAD DATA LOCAL INFILE 'D:/base/test/2017/2017.txt' 
INTO TABLE 2017 (MOB_NO, CAF_SLNO, CNAME, ACT_DATE)
COLUMNS TERMINATED BY '|';

5. 其他辅助优化 #

• 如果你用的是机械硬盘，尽量把MySQL的数据文件和日志文件放在不同的物理磁盘上，减少IO竞争；如果是SSD，这个影响不大。
• 导入期间关闭Toad的实时监控功能（比如自动刷新表数据、统计信息），避免占用额外资源。
• 开启连续自增锁模式，适合批量导入：

  SET GLOBAL innodb_autoinc_lock_mode = 2;
  

内容的提问来源于stack exchange，提问作者raghavendra prasad gudipalli