MySQL超大备份文件导入速度衰减，是否可进一步优化？

这种速度下降是正常现象，核心原因是导入后期MySQL的后台开销急剧增加：

• 前期导入的是空表（仅主键或无索引），写入以顺序IO为主，速度快；
• 数据量累积后，MySQL需要频繁维护二级索引，每次插入都要更新索引树，随机IO占比飙升；
• 事务日志（redo log、undo log）不断膨胀，刷盘频率提高，IO资源被占满；
• 内存缓存（buffer pool）被填满后，频繁触发页淘汰，进一步拖慢写入效率。

1. 调整MySQL核心配置 #

• 临时删除二级索引：导入前先删除所有非主键索引，完成导入后再批量重建。批量建索引比逐条插入时维护索引效率高数倍。
• 优化事务日志参数：
  • 增大innodb_log_file_size（建议设为4G-8G，需重启MySQL），减少日志切换和刷盘次数；
  • 临时设innodb_flush_log_at_trx_commit=2（牺牲部分事务安全性换取导入速度，完成后改回1）；
  • 临时关闭innodb_doublewrite=0（导入后恢复默认值）。
• 优化缓存与IO：
  • 调大innodb_buffer_pool_size（建议设为服务器内存的70%-80%），减少磁盘读写次数；
  • 设置innodb_flush_method=O_DIRECT，避免操作系统缓存与InnoDB缓存的双重缓冲。

2. 优化导入流程与备份文件 #

• 移除--insert-ignore参数：该参数会让MySQL每次插入前检查唯一性约束，数据量越大开销越高，确认备份无重复数据后直接删除该参数。
• 拆分大文件并行导入：将700GB备份按表或行数拆分为多个小文件，并行导入（注意避免同时导入有外键关联的表）。可用split命令拆分：

  split -l 100000 /file.sql backup_part_
  

  然后多终端并行执行：

  pv backup_part_aa | mysql -u USER DB &
  pv backup_part_ab | mysql -u USER DB &
  

• 临时禁用外键约束：导入前执行SET FOREIGN_KEY_CHECKS=0;，完成后执行SET FOREIGN_KEY_CHECKS=1;，避免外键检查拖慢插入。

3. 硬件与系统层面优化 #

• 确保目标服务器使用SSD磁盘：机械硬盘的随机IO性能远低于SSD，会成为明显瓶颈；
• 临时关闭swap分区：避免内存不足时触发swap交换，拖慢IO效率；
• 调整磁盘调度算法：SSD设为none或deadline，机械硬盘设为deadline：

  echo deadline > /sys/block/sda/queue/scheduler
  

4. 其他细节优化 #

• 导入期间关闭慢查询日志、通用日志，减少日志写入开销；
• 确保导入时无其他业务操作占用服务器资源，比如定时任务、查询请求；
• 若使用MySQL 8.0+，可尝试将备份转成CSV格式，用LOAD DATA INFILE替代INSERT语句，批量导入速度远高于普通SQL插入。

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