[面试题] MySQL批量数据插入性能优化实战指南

在处理海量数据入库的场景下，单条循环插入（INSERT INTO ...）的效率极其低下。这就像你计划派送1000个包裹，却雇佣了1000名快递员，让他们每人执行一次“敲门-签收-登记-离开”的完整流程，其时间与资源浪费是惊人的。

核心策略一：批量插入 (Batch Insert) 优化思路是将多个值组合在一个INSERT语句中一并提交，变多次网络交互和事务处理为一次。

INSERT INTO table_name (column1, column2) VALUES
(v1, v2),
(v3, v4),
(v5, v6),
...;

这种方式好比用一辆大货车，将成千上万个包裹打包成几个大箱子一次性运送，效率得到质的提升。通常，单次批量提交的数据量建议在500至1000条左右，太少优化效果不明显，太多则可能导致单次事务过大，对内存和日志造成压力。

核心策略二：调整事务与日志设置 将数据插入想象成向一个管理严格的仓库（数据库/中间件）送货。每次送货，仓库管理员都要执行一套完整的流程：检查货物、登记入库、更新库存清单（索引）、并确保每一步都安全录像（写日志）。对于大批量送货，我们可以协商临时调整规则以提升效率。

1. 禁用/延迟索引更新：索引如同仓库的货物检索目录。在插入前临时禁用非唯一索引，或在插入完成后重建索引，可以避免边插入边维护索引带来的巨大开销。
2. 合并事务：将多条INSERT语句放在同一个事务中，可以大幅减少事务提交产生的日志刷新（fsync）次数。
3. 调整日志写入策略（需谨慎）：以MySQL InnoDB为例，参数innodb_flush_log_at_trx_commit控制日志刷新到磁盘的策略。
   • 默认值为1：每次事务提交都写入磁盘，最安全，但性能最低。
   • 设置为0：每秒写入一次日志并刷新到磁盘。这相当于将“实时监控录像”改为“每分钟定时拍照”，性能提升显著，但若数据库崩溃，最多可能丢失1秒的数据。
   • 设置为2：每次事务提交后仅写入操作系统缓存，每秒刷新一次到磁盘。在保证操作系统不崩溃的前提下，比0更安全一些。

核心策略三：使用专有加载工具 如果源数据已经是规整的文本文件（如CSV），强烈建议使用数据库原生的批量加载命令，如MySQL的LOAD DATA INFILE。

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

这个命令相当于让货车从仓库专用装卸通道直接进入，跳过了SQL解析、优化等层层关卡，直接将数据文件内容载入表中，速度可比普通INSERT快一个数量级以上。

重要注意事项 上述性能优化手段，尤其是调整事务日志持久化级别，是以牺牲一定的数据安全性或完整性为代价的。因此，在生产环境中应用时必须遵循以下原则：

1. 严格在测试环境验证：任何参数修改前，必须在与生产环境类似的测试库进行充分验证。
2. 操作前备份：在执行大批量数据操作前，务必对目标表或数据库进行备份。
3. 操作后恢复与检查：批量操作完成后，应立即将修改过的数据库参数恢复为安全配置，并校验数据的完整性和一致性。
4. 选择合适时机：此类操作应安排在业务低峰期进行，最大限度降低对线上服务的影响。