MySQL 的批量插入这个话题几乎每个用过 MySQL 的人都会碰到——数据量一大逐条 insert 的速度慢到让人怀疑数据库是不是卡死了。我自己就经历过一次某次业务需要把近百万行数据灌进一张表最开始用最简单的方式跑预估要一个多小时后来换了批量插入的写法四十秒出头就搞定了。这中间差的不是数据库性能而是导入方式本身。这篇文章就围绕批量插入展开把原理、参数、实操步骤和坑一次讲清楚。内容主要针对使用 MySQL 5.7 / 8.0 的开发者无论你是做后端接口、数据迁移还是大数据预处理只要涉及“往 MySQL 里灌数据”这篇文章都值得看完。1. 为什么你写的插入语句越跑越慢很多人一开始觉得MySQL 插入慢是因为“数据太多了”其实只对了一半。数据量大的确会让耗时变长但更关键的问题在于插入方式——逐条插入时每一行数据都要走一遍完整的“客户端到服务器”往返流程这里面每一步都有开销叠加起来就变成了性能黑洞。1.1 最容易被忽略的“提交开销”先说事务提交。MySQL 默认开启 autocommit也就是说你每执行一条 insert 语句它都会立刻开启一个隐式事务执行完再提交。一次提交意味着什么在 InnoDB 引擎下提交要把事务日志redo log刷到磁盘这个操作叫 fsync。你可以把 fsync 理解成“签合同盖章”每次提交都要盖一次章物理磁盘的响应时间是固定的几万次插入就有几万次盖章时间就是这么堆出来的。再算一笔账假设单次 fsync 平均耗时 1 到 2 毫秒这个数字对机械硬盘或高负载环境来说非常保守。但如果你插入 10 万行数据逐条提交就是 10 万次 fsync单纯等待磁盘 I/O 就花了 100 到 200 秒。所以数据还没轮到真正执行“插入”动作时间就没了大半。1.2 索引维护的成本比想象中高第二笔开销是索引维护。表上的每一个二级索引在插入一条新记录时都要同步更新。如果你在一个有 5 个索引的表上插入数据相当于每行数据要写 1 次主键索引加 5 次二级索引。更麻烦的是索引页如果放不下新数据还要触发页分裂和页合并这个过程会把随机 I/O 放大。批量插入的意义不只是减少了网络往返还让 MySQL 有机会顺序写入数据和索引这个区别在高并发写入场景下非常明显。还有个隐藏问题批量插入的命令通常很大MySQL 会把这一个大语句当成一个事务执行这样 InnoDB 可以在内存里积累足够的日志后再一次性刷盘磁盘 I/O 次数大幅减少。这一点后面会详细展开。2. 批量插入的几种主流方案与选型2.1 多值 INSERTVALUES 拼接的适用边界多值 INSERT 是最常用的方式就是把多条记录拼进一条 SQL 里INSERT INTO user (name, age, email) VALUES (张三, 25, zhangsanexample.com), (李四, 30, lisiexample.com), (王五, 28, wangwuexample.com);这种方案适合每次插入几百到几千行。它最大的好处是减少 SQL 语句数量也就减少了网络往返和解析次数。但要注意一条 SQL 不能无限制地拼接。MySQL 参数max_allowed_packet控制了单个包的最大大小默认通常是 16MB 或 64MB8.0 默认 64MB。如果拼出来的 SQL 超过这个限制会直接报错。另一个限制是参数占位符数量。如果用 PreparedStatement 配合?占位符MySQL 对语句中的参数数量上限是 65535 个包括客户端和服务端的协议层限制。举个例子如果你一行的数据有 10 个字段那么一条语句里最多只能放大约 6500 行再多就会报“Prepared statement contains too many placeholders”。所以批量插入的“批”不是越大越好而是要在三个限制之间找平衡max_allowed_packet参数值决定了一条 SQL 的包大小上限65535 个占位符限制使用 PreparedStatement 时InnoDB 单事务的合理大小推荐 1 万到 5 万行作为一个批次。建议每一次批量插入控制在 2000 到 5000 行。这个区间既能充分利用批量优势又不容易触发各种限制。2.2 事务分批提交的正确姿势既然 autocommit 是性能杀手那么最直接的优化就是关闭自动提交手动控制事务每 N 行提交一次。SET autocommit 0; INSERT INTO user (name, age, email) VALUES (张三, 25, zhangsanexample.com); INSERT INTO user (name, age, email) VALUES (李四, 30, lisiexample.com); -- ... 执行 N 行 COMMIT;这种方式适合你不想改 SQL、只是把现有逐条插入代码包在事务里的场景。改造成本最低效果立竿见影一般能把插入速度提升 5 到 10 倍。但事务也不能开太大。一个事务插入几十万行意味着 InnoDB 的 undo log 和 redo log 都会膨胀事务提交时一次性刷盘的压力很大极端情况下会影响整个实例的性能。更危险的是如果一个事务太长长时间占用行锁其他会话的写入会被阻塞甚至引发锁等待超时。我的经验是批次大小控制在 1 万到 5 万行之间比较稳妥。具体数字取决于你的行宽和服务器磁盘性能行越宽批次越小。你可以先用 1 万行跑一次观察耗时和服务器负载再逐步调整。2.3 JDBC 的 rewriteBatchedStatements 参数如果你用 Java 的 JDBC 连接 MySQL还有一个特别容易被忽视的配置rewriteBatchedStatements。默认情况下JDBC 的addBatch()和executeBatch()并不会真正把多条 insert 合并发送而是逐条发给 MySQL 执行。你以为自己在“批量插入”实际上 MySQL 收到的还是单条语句性能提升非常有限只是帮你省了客户端到服务器的往返而已。把连接串改成这样jdbc:mysql://localhost:3306/test?rewriteBatchedStatementstrue加上这个参数后MySQL 驱动会把同一事务里的多条 INSERT 语句重写成多值 INSERT批量效果才能真正发挥出来。这里有一个小细节值得说rewriteBatchedStatements默认是关闭的很多教程不会提。我见过生产环境里项目代码写得没问题但性能就是上不去排查到最后发现是这参数没开。如果你用 JDBC 做批量插入务必确认这个配置。2.4 LOAD DATA INFILE最快的导入方式如果你要导入的数据在文件里比如 CSV那LOAD DATA INFILE是 MySQL 里最快的导入方式没有之一。LOAD DATA INFILE /tmp/user_data.csv INTO TABLE user FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (name, age, email);这个语句能跑多快我实测过百万行数据在普通 SSD 上大概 10 到 20 秒就能完成导入远快于任何多值 INSERT。它的原理和批量 INSERT 类似但 MySQL 内部做了更多优化包括直接走存储引擎层的批量写入路径减少 SQL 层的解析和调用开销。如果你的数据源不是文件也可以先在程序里把内存中的数据整理成临时 CSV 文件再通过LOAD DATA INFILE导入。需要注意LOAD DATA INFILE受local_infile参数控制服务端和客户端都要开启才能生效。MySQL 8.0 默认关闭了这个功能需要手动打开这个后面讲参数时会提到。2.5 方案对比和选型建议不同类型的数据导入需求适合不同的方案。我做了一张表方便你直接对照选择方案适合场景速度注意事项单条 INSERT 循环数据量小、并发写入场景最慢不适合大规模导入多值 INSERTVALUES 拼接几百到几千行的批量写入较快受 max_allowed_packet 和占位符数量限制事务分批 多条 INSERT已有逐条插入代码、需最小化改造中等偏上事务不宜过大注意锁等待JDBC rewriteBatchedStatementsJava 后端批量写入快一定记得开启参数LOAD DATA INFILE从文件导入大规模数据最快需开启 local_infile适合离线/迁移场景技术选型没有银弹。如果你只是给一个小工具写数据多值 INSERT 就够了但如果你在做数据迁移、初始化、ETL 一类的事情别犹豫直接上 LOAD DATA INFILE再配合合理的批次提交能把导入时间压缩到原来的几十分之一。3. MySQL 批量插入需要调的几个核心参数很多人在批量插入性能不佳时第一反应是加索引、加内存或者换机器但其实有几个 MySQL 参数本身就专为“写入性能”设计只是默认值太保守没发挥出来。3.1 max_allowed_packet决定单条 SQL 能多大这个参数前面已经提到过是批量插入最容易踩的坑。默认值在 MySQL 5.7 是 4MB8.0 是 64MB。如果你把几千行拼成一条 SQL很容易超过默认限制。查看当前值SHOW VARIABLES LIKE max_allowed_packet;修改方式重启后失效需要写入配置文件持久化SET GLOBAL max_allowed_packet 128 * 1024 * 1024;建议设置为 64MB 到 128MB。请注意这个参数的单位是字节别直接写128那是 128 字节。3.2 innodb_flush_log_at_trx_commit事务提交时怎么刷盘这个参数和事务提交的 fsync 开销直接相关它有 3 个可选值1默认每次事务提交都把 redo log 刷到磁盘最安全最慢2每次提交只把日志写入操作系统缓存每秒刷一次盘0每秒刷一次盘事务提交时不主动刷。如果你在做大批量数据导入并且可以接受“导入过程中如果宕机可能丢最近 1 秒数据”的风险可以临时设为2或0导入完成后立刻改回1。这是一个典型的“性能换安全”取舍。业务线上的常规写入必须用1但导入场景可以放宽实用价值非常高。3.3 bulk_insert_buffer_size针对 MyISAM 的参数如果你的表引擎是 MyISAM虽然现在很少见批量插入时可以用bulk_insert_buffer_size来加速。InnoDB 对这个参数是无视的不用去管。顺便说一句对于需要大量插入的场景表引擎选 InnoDB 是惯例不要为了“批量插入快”去用 MyISAM数据完整性和事务能力才是长期要考虑的东西。3.4 local_infileLOAD DATA 的开关上面提到LOAD DATA INFILE时说过MySQL 8.0 默认关掉了local_infile。如果你的导入命令提示“The used command is not allowed with this MySQL version”就要检查这个变量SHOW VARIABLES LIKE local_infile;修改方式SET GLOBAL local_infile ON;注意如果使用客户端命令行还需要在连接时加上--local-infile1参数否则服务端虽然开了客户端还是不会发起本地文件读取。3.5 innodb_buffer_pool_size写入的前提是把内存放大这个参数不直接控制“插入速度”但影响很大。InnoDB 的数据页和索引页都在 buffer pool 里插入的数据要先进内存再慢慢刷到磁盘。如果 buffer pool 太小页面频繁被替换出去写入性能会雪崩。一般经验值是物理内存的 60% 到 70%。如果你有 16GB 内存分配给 MySQL 的 buffer pool 建议 10GB 左右。如果 MySQL 只分配了 128MB几百 MB 的数据可能就把内存打爆了随之而来的就是大量磁盘 I/O性能会变得非常糟糕。调参之前先看看系统的内存资源到底给了 MySQL 多少空间。很多批量插入慢的问题其实根源在于 buffer pool 配置过低而不是 SQL 写法有问题。4. 实战过程一次把导入耗时压到原来的 1/80下面用一次实际经历复盘整个优化过程这样比单讲理论更有参考价值。背景是一个数据迁移需求把一张旧系统导出的 CSV 文件含 83 万行、每行 14 个字段导入到 MySQL 8.0 的一张新表中。4.1 环境与表结构服务器4 核 CPU、16GB 内存、SSD 磁盘MySQL8.0.28默认配置未做过多调整表结构一个主键 IDBIGINT 自增两个普通索引一个唯一索引目标最短时间内完成导入不阻塞其他业务。4.2 第一版Python 逐条插入最开始我用 Python 逐条执行 INSERT代码很简单import pymysql conn pymysql.connect(hostlocalhost, userroot, password..., databasetest) cursor conn.cursor() with open(user_data.csv, r, encodingutf-8) as f: reader csv.reader(f) next(reader) # 跳过表头 for row in reader: cursor.execute( INSERT INTO user (name, age, email, address, phone, status) VALUES (%s, %s, %s, %s, %s, %s), row ) conn.commit()跑起来之后速度大约是每秒 300 行左右整个导入预估需要 40 多分钟。这个结果非常典型——不是脚本写得不对而是逐条插入的模式本身就是效率最差的。顺带解释一个现象这里每秒 300 行看着还行但 80 多万行乘起来就是 40 多分钟。而且这种模式下CPU 大量消耗在 MySQL 解析 SQL、锁管理、日志写入上纯粹就是浪费。4.3 第二版改成多值 INSERT 分批提交很快我改成了多值 INSERT 的方案每批 2000 行手动控制事务batch_size 2000 rows [] with open(user_data.csv, r, encodingutf-8) as f: reader csv.reader(f) next(reader) for row in reader: rows.append(row) if len(rows) batch_size: insert_many(rows) rows.clear() if rows: insert_many(rows) def insert_many(batch): sql INSERT INTO user (name, age, email, address, phone, status) VALUES , .join([(%s, %s, %s, %s, %s, %s)] * len(batch)) cursor.executemany(sql, batch) conn.commit()这里用executemany配合多值 SQL 模板实际效果比execute循环强了非常多。第二次跑完整个导入耗时从 40 分钟降到了 3 分半钟左右。每秒能处理约 4000 行提升 13 倍。这一段改动没有任何高深技巧核心就是“一条 SQL 多行数据”加“手动提交”。如果你还没有用多值 INSERT 做批量插入强烈建议从这一步开始优化收益非常明显。4.4 第三版换成 LOAD DATA INFILE下一步我直接把 CSV 文件交给 MySQL 自己处理用LOAD DATA INFILE替代程序组装 SQLLOAD DATA INFILE /tmp/user_data.csv INTO TABLE user CHARACTER SET utf8mb4 FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (name, age, email, address, phone, status);同时临时把innodb_flush_log_at_trx_commit设为0导入完成后再改回来。这一版的耗时降到了 44 秒。你没看错从最初 40 多分钟降到 44 秒提升了 55 倍以上比我预期还好一点。整个优化过程我给一个清晰的对照表阶段方案耗时说明第一版逐条 INSERT约 45 分钟每秒约 300 行第二版多值 INSERT每批 2000 行约 3 分 30 秒每秒约 4000 行第三版LOAD DATA INFILE 临时调参约 44 秒每秒约 1.9 万行这个案例非常有代表性优化 SQL 写法是第一步收益最大换更底层的导入机制是第二步进一步突破瓶颈。两者不冲突可以叠加使用。4.5 一个需要提醒的地方导入完成后别忘了把改过的参数恢复正常尤其是innodb_flush_log_at_trx_commit。如果导入后表上有其他业务在写入0或2的设置会导致数据安全风险。这个习惯非常重要否则很容易出现“优化一时爽宕机两行泪”的尴尬局面。在生产环境做这种临时调参之前最好在维护窗口执行并先在测试环境验证一遍完整流程。5. 常见错误、报错与解决办法批量插入的报错种类其实不多但每个都很典型。我整理了几个高频问题附带排查思路和解决办法基本都是我在实际项目里踩过的坑。5.1 Packets larger than max_allowed_packet are not allowed这是最常见的报错。原因是单条 SQL 语句太大超过了max_allowed_packet。解决办法有两个见效最快的是调大参数SET GLOBAL max_allowed_packet 128 * 1024 * 1024;同时缩小每条 SQL 的批大小。就算你把参数调到了 1GB我也不建议把几万行拼成一条 SQL因为这么大的语句在 MySQL 解析和执行时内存占用非常夸张还可能出现主从复制延迟问题。遇到这个报错时先看参数和批大小哪个低调哪个。5.2 Prepared statement contains too many placeholders这个报错的本质是占位符数量超限。MySQL 协议限制了单个 PreparedStatement 中参数的最大数量为 65535。如果你的批量插入语句是动态拼?的字段多、行数多就会触发。解决办法很直接把每批的行数减少。10 个字段的情况下每批 6000 行就是 6 万个参数接近上限保险起见每批控制在 4000 到 5000 行。如果你的业务字段特别多比如 20 个字段每批就只能放 3000 行左右。这个限制不是 MySQL 配置能解决的是协议层写死的只能靠调批大小来规避。5.3 Deadlock found when trying to get lock批量插入时死锁并不少见尤其在多个会话同时往同一张表插数据的情况下。InnoDB 需要加锁来检查唯一索引冲突锁的顺序不同就可能死锁。排查思路查看错误日志确认哪些会话产生了死锁检查是否存在多个线程操作同一批数据范围检查唯一索引冲突是否频繁。解决办法是让批次数据和事务边界尽量有序比如多个导入任务做好分片避免不同会话同时插入相同范围的数据。更稳妥的办法是插入失败后重试整个事务InnoDB 的死锁检测一般会自动回滚其中一个事务业务端捕捉到重试即可。5.4 Duplicate entry 导致整个批量失败如果批量插入时出现了主键或唯一键冲突默认情况下整个语句都会失败之前插入的数据也会回滚。这在数据迁移中很常见CSV 里有重复数据导入直接失败。解决办法有几个方向第一清洗数据在导入前先查重去掉重复行这是最推荐的方式。第二利用INSERT IGNORE跳过冲突行INSERT IGNORE INTO user (name, age, email) VALUES (张三, 25, zhangsanexample.com);第三使用ON DUPLICATE KEY UPDATE做“存在则更新”INSERT INTO user (name, age, email, status) VALUES (张三, 25, zhangsanexample.com, 1), (李四, 30, lisiexample.com, 2) ON DUPLICATE KEY UPDATE status VALUES(status);这个语法适合“重复数据要做幂等更新”的场景比如每天导入当天的增量数据有重复就更新状态。注意MySQL 8.0.20 起推荐使用别名语法AS new ON DUPLICATE KEY UPDATE status new.status老语法虽然还能用但新项目建议用新写法INSERT INTO user (name, age, email, status) VALUES (张三, 25, zhangsanexample.com, 1) AS new ON DUPLICATE KEY UPDATE status new.status;5.5 LOAD DATA 导入中文乱码CSV 文件是 GBK 编码但表是 utf8mb4直接导入就乱码。解决办法是在LOAD DATA语句里指定CHARACTER SET或者在导入前先统一转换编码。LOAD DATA INFILE /tmp/user_data.csv INTO TABLE user CHARACTER SET utf8mb4 FIELDS TERMINATED BY , ...MySQL 会按照目标表的字符集转换读取的文件编码。最常见的问题就是字符编码不统一导致导入后查询乱码或报错。千万别忽略这一步格式化一个百万行的 CSV 很简单修复百万行乱码数据很痛苦。6. 索引、唯一键和批量插入的相互作用这一节聊一个很多人忽略的问题索引不是越多越好批量插入时尤其如此。虽然“索引越多插入越慢”是常识但具体怎么影响性能很多人不清楚。简单说每增加一个二级索引批量插入时要多更新一颗 B 树。索引页的写入如果超出缓存还会产生随机 I/O拖慢整体速度。这就是为什么在导入大量数据时有的人先删掉非必要索引导入完成后再重新建索引整体时间反而更短。这个操作有没有必要看情况。如果你的数据量在百万行以内并且导入目标是空表直接带索引插入通常问题不大耗时多一半左右可以接受。但如果数据量达到千万级或者目标表已经有几百万行数据那么“先删索引再导入、最后重建”的方案会有明显收益。因为重建索引时 MySQL 可以从头构建 B 树批量创建比逐行插入时的逐页维护效率高得多。建议这样操作导出表结构记录所有索引定义导入前先删除非必要索引保留主键等数据完全导入后再用ALTER TABLE ADD INDEX重建索引。这里要特别注意唯一索引不能随便删。如果业务要求数据不能重复必须在导入时确保数据已经清洗干净否则导入后再加唯一索引时才发现大量重复数据麻烦就大了。批量导入时我的经验是业务上用到的主要查询索引导入后再建没问题唯一性约束不要依赖“最后建索引”来兜底导入前就要清洗数据导入完成建索引后务必执行ANALYZE TABLE更新统计信息避免优化器因统计信息过旧选错执行计划。另外一个细节批量插入大量数据后表的碎片可能比较严重。必要时执行OPTIMIZE TABLE回收碎片空间但这会让表锁一段时间生产环境要避开业务高峰。7. 高并发环境下的批量插入策略批量插入不一定都是“一次性导入”很多场景是系统持续不断地批量写数据。比如接收上游系统推送的数据攒一批后批量入库。这种场景下除了 SQL 写法还要考虑并发策略。7.1 控制并发写线程数量别以为开越多的线程写入就越快。MySQL 的写入瓶颈往往在磁盘 I/O而不是 CPU。我遇到过团队用 20 个线程并发批量插入结果不仅没变快反而把 InnoDB 的锁竞争和日志写入拖垮了整体吞吐反而下降。比较稳妥的做法单表并发写线程数控制在 2 到 4 个多表写入可以适当放宽但单表不要过度并发如果一张表有多个索引并发写入的索引竞争会更严重需要下调并发数。从 CPU 使用率来看如果 MySQL 的 CPU 已经飙升到 80% 以上而磁盘 I/O 还没跑满说明大部分消耗花在了 SQL 解析和锁管理上这时候并发数不仅没帮助反而有害。7.2 写队列和批次的配合在应用层做“攒批”策略时不要严格等批次满了才写入这样突发流量下延迟太高。我常用的方式是双条件触发数据量达到阈值或时间达到阈值满足一个就写入。比如最简单的伪代码逻辑if (buffer.size() 2000 || System.currentTimeMillis() - lastFlushTime 1000) { batchInsert(buffer); buffer.clear(); lastFlushTime System.currentTimeMillis(); }这样能在“批量效率”和“数据及时性”之间取得平衡。8. 几条有意思的实测经验分享几个我实测得出的结论不一定适合所有环境但大概率有参考价值。第一批量插入单批次的行数在 2000 到 10000 之间时性能差异通常不大。真正拉开差距的是“是否用了批量写”而不是“每一批写了多少”。所以别纠结于把批次调到最优选一个中间值长期使用就行。第二LOAD DATA INFILE也受目标表索引影响。带 5 个索引的百万行表导入时间会比只有主键的表多出四五倍。如果你对导入速度有极致要求还是老老实实用“先删索引、导入、再加索引”的组合打法。第三MySQL 8.0 在批量插入上的整体性能比 5.7 有明显提升尤其是多值 INSERT 和并发写入的表现。有条件的话新项目直接上 MySQL 8.0不只是因为性能还因为参数和语法也都更新了。第四使用 Python 的executemany的时候注意它是否真的把数据合并发送了。executemany在不同驱动底层实现不同pymysql有时会把多条请求拼接发送有时逐条执行。我用下来借助pymysql时配合多值 SQL 模板最稳不要只依赖executemany的默认行为。9. 排查性能问题时的思考顺序最后给一个排查写入性能问题的顺序是我自己总结出来的非常好用先看网络往返再看事务提交频率再看 SQL 解析和日志开销最后看索引维护和磁盘 I/O。具体到操作上确认是否用上了批量写入手段确认 autocommit 是否关了事务是否合理分批确认max_allowed_packet和local_infile参数没问题确认目标表的索引数量是否合理结合SHOW ENGINE INNODB STATUS查看锁等待和日志刷写情况。这套检查下来大部分写入性能问题都能定位到根因。如果都检查完了还是慢那就要把眼光放到服务器层面——磁盘类型、raid 配置、网络 IO、CPU 调度这些都有可能成为瓶颈。批量插入优化不是靠某一个技巧它是一整套逻辑的叠加减少交互次数、控制事务大小、调整关键参数、合理设计索引、选对导入工具。每一步累积下来导入时间从几十分钟压到几十秒并不是什么难事。数据库优化的本质就是搞清楚每一秒的时间都花在了哪里然后想办法把它省下来。批量插入只是其中一个缩影理解了背后这套思考方式以后碰到的其他性能问题也都能顺藤摸瓜找到答案。
MySQL批量插入优化指南：从逐条INSERT到LOAD DATA INFILE
MySQL 的批量插入这个话题几乎每个用过 MySQL 的人都会碰到——数据量一大逐条 insert 的速度慢到让人怀疑数据库是不是卡死了。我自己就经历过一次某次业务需要把近百万行数据灌进一张表最开始用最简单的方式跑预估要一个多小时后来换了批量插入的写法四十秒出头就搞定了。这中间差的不是数据库性能而是导入方式本身。这篇文章就围绕批量插入展开把原理、参数、实操步骤和坑一次讲清楚。内容主要针对使用 MySQL 5.7 / 8.0 的开发者无论你是做后端接口、数据迁移还是大数据预处理只要涉及“往 MySQL 里灌数据”这篇文章都值得看完。1. 为什么你写的插入语句越跑越慢很多人一开始觉得MySQL 插入慢是因为“数据太多了”其实只对了一半。数据量大的确会让耗时变长但更关键的问题在于插入方式——逐条插入时每一行数据都要走一遍完整的“客户端到服务器”往返流程这里面每一步都有开销叠加起来就变成了性能黑洞。1.1 最容易被忽略的“提交开销”先说事务提交。MySQL 默认开启 autocommit也就是说你每执行一条 insert 语句它都会立刻开启一个隐式事务执行完再提交。一次提交意味着什么在 InnoDB 引擎下提交要把事务日志redo log刷到磁盘这个操作叫 fsync。你可以把 fsync 理解成“签合同盖章”每次提交都要盖一次章物理磁盘的响应时间是固定的几万次插入就有几万次盖章时间就是这么堆出来的。再算一笔账假设单次 fsync 平均耗时 1 到 2 毫秒这个数字对机械硬盘或高负载环境来说非常保守。但如果你插入 10 万行数据逐条提交就是 10 万次 fsync单纯等待磁盘 I/O 就花了 100 到 200 秒。所以数据还没轮到真正执行“插入”动作时间就没了大半。1.2 索引维护的成本比想象中高第二笔开销是索引维护。表上的每一个二级索引在插入一条新记录时都要同步更新。如果你在一个有 5 个索引的表上插入数据相当于每行数据要写 1 次主键索引加 5 次二级索引。更麻烦的是索引页如果放不下新数据还要触发页分裂和页合并这个过程会把随机 I/O 放大。批量插入的意义不只是减少了网络往返还让 MySQL 有机会顺序写入数据和索引这个区别在高并发写入场景下非常明显。还有个隐藏问题批量插入的命令通常很大MySQL 会把这一个大语句当成一个事务执行这样 InnoDB 可以在内存里积累足够的日志后再一次性刷盘磁盘 I/O 次数大幅减少。这一点后面会详细展开。2. 批量插入的几种主流方案与选型2.1 多值 INSERTVALUES 拼接的适用边界多值 INSERT 是最常用的方式就是把多条记录拼进一条 SQL 里INSERT INTO user (name, age, email) VALUES (张三, 25, zhangsanexample.com), (李四, 30, lisiexample.com), (王五, 28, wangwuexample.com);这种方案适合每次插入几百到几千行。它最大的好处是减少 SQL 语句数量也就减少了网络往返和解析次数。但要注意一条 SQL 不能无限制地拼接。MySQL 参数max_allowed_packet控制了单个包的最大大小默认通常是 16MB 或 64MB8.0 默认 64MB。如果拼出来的 SQL 超过这个限制会直接报错。另一个限制是参数占位符数量。如果用 PreparedStatement 配合?占位符MySQL 对语句中的参数数量上限是 65535 个包括客户端和服务端的协议层限制。举个例子如果你一行的数据有 10 个字段那么一条语句里最多只能放大约 6500 行再多就会报“Prepared statement contains too many placeholders”。所以批量插入的“批”不是越大越好而是要在三个限制之间找平衡max_allowed_packet参数值决定了一条 SQL 的包大小上限65535 个占位符限制使用 PreparedStatement 时InnoDB 单事务的合理大小推荐 1 万到 5 万行作为一个批次。建议每一次批量插入控制在 2000 到 5000 行。这个区间既能充分利用批量优势又不容易触发各种限制。2.2 事务分批提交的正确姿势既然 autocommit 是性能杀手那么最直接的优化就是关闭自动提交手动控制事务每 N 行提交一次。SET autocommit 0; INSERT INTO user (name, age, email) VALUES (张三, 25, zhangsanexample.com); INSERT INTO user (name, age, email) VALUES (李四, 30, lisiexample.com); -- ... 执行 N 行 COMMIT;这种方式适合你不想改 SQL、只是把现有逐条插入代码包在事务里的场景。改造成本最低效果立竿见影一般能把插入速度提升 5 到 10 倍。但事务也不能开太大。一个事务插入几十万行意味着 InnoDB 的 undo log 和 redo log 都会膨胀事务提交时一次性刷盘的压力很大极端情况下会影响整个实例的性能。更危险的是如果一个事务太长长时间占用行锁其他会话的写入会被阻塞甚至引发锁等待超时。我的经验是批次大小控制在 1 万到 5 万行之间比较稳妥。具体数字取决于你的行宽和服务器磁盘性能行越宽批次越小。你可以先用 1 万行跑一次观察耗时和服务器负载再逐步调整。2.3 JDBC 的 rewriteBatchedStatements 参数如果你用 Java 的 JDBC 连接 MySQL还有一个特别容易被忽视的配置rewriteBatchedStatements。默认情况下JDBC 的addBatch()和executeBatch()并不会真正把多条 insert 合并发送而是逐条发给 MySQL 执行。你以为自己在“批量插入”实际上 MySQL 收到的还是单条语句性能提升非常有限只是帮你省了客户端到服务器的往返而已。把连接串改成这样jdbc:mysql://localhost:3306/test?rewriteBatchedStatementstrue加上这个参数后MySQL 驱动会把同一事务里的多条 INSERT 语句重写成多值 INSERT批量效果才能真正发挥出来。这里有一个小细节值得说rewriteBatchedStatements默认是关闭的很多教程不会提。我见过生产环境里项目代码写得没问题但性能就是上不去排查到最后发现是这参数没开。如果你用 JDBC 做批量插入务必确认这个配置。2.4 LOAD DATA INFILE最快的导入方式如果你要导入的数据在文件里比如 CSV那LOAD DATA INFILE是 MySQL 里最快的导入方式没有之一。LOAD DATA INFILE /tmp/user_data.csv INTO TABLE user FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (name, age, email);这个语句能跑多快我实测过百万行数据在普通 SSD 上大概 10 到 20 秒就能完成导入远快于任何多值 INSERT。它的原理和批量 INSERT 类似但 MySQL 内部做了更多优化包括直接走存储引擎层的批量写入路径减少 SQL 层的解析和调用开销。如果你的数据源不是文件也可以先在程序里把内存中的数据整理成临时 CSV 文件再通过LOAD DATA INFILE导入。需要注意LOAD DATA INFILE受local_infile参数控制服务端和客户端都要开启才能生效。MySQL 8.0 默认关闭了这个功能需要手动打开这个后面讲参数时会提到。2.5 方案对比和选型建议不同类型的数据导入需求适合不同的方案。我做了一张表方便你直接对照选择方案适合场景速度注意事项单条 INSERT 循环数据量小、并发写入场景最慢不适合大规模导入多值 INSERTVALUES 拼接几百到几千行的批量写入较快受 max_allowed_packet 和占位符数量限制事务分批 多条 INSERT已有逐条插入代码、需最小化改造中等偏上事务不宜过大注意锁等待JDBC rewriteBatchedStatementsJava 后端批量写入快一定记得开启参数LOAD DATA INFILE从文件导入大规模数据最快需开启 local_infile适合离线/迁移场景技术选型没有银弹。如果你只是给一个小工具写数据多值 INSERT 就够了但如果你在做数据迁移、初始化、ETL 一类的事情别犹豫直接上 LOAD DATA INFILE再配合合理的批次提交能把导入时间压缩到原来的几十分之一。3. MySQL 批量插入需要调的几个核心参数很多人在批量插入性能不佳时第一反应是加索引、加内存或者换机器但其实有几个 MySQL 参数本身就专为“写入性能”设计只是默认值太保守没发挥出来。3.1 max_allowed_packet决定单条 SQL 能多大这个参数前面已经提到过是批量插入最容易踩的坑。默认值在 MySQL 5.7 是 4MB8.0 是 64MB。如果你把几千行拼成一条 SQL很容易超过默认限制。查看当前值SHOW VARIABLES LIKE max_allowed_packet;修改方式重启后失效需要写入配置文件持久化SET GLOBAL max_allowed_packet 128 * 1024 * 1024;建议设置为 64MB 到 128MB。请注意这个参数的单位是字节别直接写128那是 128 字节。3.2 innodb_flush_log_at_trx_commit事务提交时怎么刷盘这个参数和事务提交的 fsync 开销直接相关它有 3 个可选值1默认每次事务提交都把 redo log 刷到磁盘最安全最慢2每次提交只把日志写入操作系统缓存每秒刷一次盘0每秒刷一次盘事务提交时不主动刷。如果你在做大批量数据导入并且可以接受“导入过程中如果宕机可能丢最近 1 秒数据”的风险可以临时设为2或0导入完成后立刻改回1。这是一个典型的“性能换安全”取舍。业务线上的常规写入必须用1但导入场景可以放宽实用价值非常高。3.3 bulk_insert_buffer_size针对 MyISAM 的参数如果你的表引擎是 MyISAM虽然现在很少见批量插入时可以用bulk_insert_buffer_size来加速。InnoDB 对这个参数是无视的不用去管。顺便说一句对于需要大量插入的场景表引擎选 InnoDB 是惯例不要为了“批量插入快”去用 MyISAM数据完整性和事务能力才是长期要考虑的东西。3.4 local_infileLOAD DATA 的开关上面提到LOAD DATA INFILE时说过MySQL 8.0 默认关掉了local_infile。如果你的导入命令提示“The used command is not allowed with this MySQL version”就要检查这个变量SHOW VARIABLES LIKE local_infile;修改方式SET GLOBAL local_infile ON;注意如果使用客户端命令行还需要在连接时加上--local-infile1参数否则服务端虽然开了客户端还是不会发起本地文件读取。3.5 innodb_buffer_pool_size写入的前提是把内存放大这个参数不直接控制“插入速度”但影响很大。InnoDB 的数据页和索引页都在 buffer pool 里插入的数据要先进内存再慢慢刷到磁盘。如果 buffer pool 太小页面频繁被替换出去写入性能会雪崩。一般经验值是物理内存的 60% 到 70%。如果你有 16GB 内存分配给 MySQL 的 buffer pool 建议 10GB 左右。如果 MySQL 只分配了 128MB几百 MB 的数据可能就把内存打爆了随之而来的就是大量磁盘 I/O性能会变得非常糟糕。调参之前先看看系统的内存资源到底给了 MySQL 多少空间。很多批量插入慢的问题其实根源在于 buffer pool 配置过低而不是 SQL 写法有问题。4. 实战过程一次把导入耗时压到原来的 1/80下面用一次实际经历复盘整个优化过程这样比单讲理论更有参考价值。背景是一个数据迁移需求把一张旧系统导出的 CSV 文件含 83 万行、每行 14 个字段导入到 MySQL 8.0 的一张新表中。4.1 环境与表结构服务器4 核 CPU、16GB 内存、SSD 磁盘MySQL8.0.28默认配置未做过多调整表结构一个主键 IDBIGINT 自增两个普通索引一个唯一索引目标最短时间内完成导入不阻塞其他业务。4.2 第一版Python 逐条插入最开始我用 Python 逐条执行 INSERT代码很简单import pymysql conn pymysql.connect(hostlocalhost, userroot, password..., databasetest) cursor conn.cursor() with open(user_data.csv, r, encodingutf-8) as f: reader csv.reader(f) next(reader) # 跳过表头 for row in reader: cursor.execute( INSERT INTO user (name, age, email, address, phone, status) VALUES (%s, %s, %s, %s, %s, %s), row ) conn.commit()跑起来之后速度大约是每秒 300 行左右整个导入预估需要 40 多分钟。这个结果非常典型——不是脚本写得不对而是逐条插入的模式本身就是效率最差的。顺带解释一个现象这里每秒 300 行看着还行但 80 多万行乘起来就是 40 多分钟。而且这种模式下CPU 大量消耗在 MySQL 解析 SQL、锁管理、日志写入上纯粹就是浪费。4.3 第二版改成多值 INSERT 分批提交很快我改成了多值 INSERT 的方案每批 2000 行手动控制事务batch_size 2000 rows [] with open(user_data.csv, r, encodingutf-8) as f: reader csv.reader(f) next(reader) for row in reader: rows.append(row) if len(rows) batch_size: insert_many(rows) rows.clear() if rows: insert_many(rows) def insert_many(batch): sql INSERT INTO user (name, age, email, address, phone, status) VALUES , .join([(%s, %s, %s, %s, %s, %s)] * len(batch)) cursor.executemany(sql, batch) conn.commit()这里用executemany配合多值 SQL 模板实际效果比execute循环强了非常多。第二次跑完整个导入耗时从 40 分钟降到了 3 分半钟左右。每秒能处理约 4000 行提升 13 倍。这一段改动没有任何高深技巧核心就是“一条 SQL 多行数据”加“手动提交”。如果你还没有用多值 INSERT 做批量插入强烈建议从这一步开始优化收益非常明显。4.4 第三版换成 LOAD DATA INFILE下一步我直接把 CSV 文件交给 MySQL 自己处理用LOAD DATA INFILE替代程序组装 SQLLOAD DATA INFILE /tmp/user_data.csv INTO TABLE user CHARACTER SET utf8mb4 FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (name, age, email, address, phone, status);同时临时把innodb_flush_log_at_trx_commit设为0导入完成后再改回来。这一版的耗时降到了 44 秒。你没看错从最初 40 多分钟降到 44 秒提升了 55 倍以上比我预期还好一点。整个优化过程我给一个清晰的对照表阶段方案耗时说明第一版逐条 INSERT约 45 分钟每秒约 300 行第二版多值 INSERT每批 2000 行约 3 分 30 秒每秒约 4000 行第三版LOAD DATA INFILE 临时调参约 44 秒每秒约 1.9 万行这个案例非常有代表性优化 SQL 写法是第一步收益最大换更底层的导入机制是第二步进一步突破瓶颈。两者不冲突可以叠加使用。4.5 一个需要提醒的地方导入完成后别忘了把改过的参数恢复正常尤其是innodb_flush_log_at_trx_commit。如果导入后表上有其他业务在写入0或2的设置会导致数据安全风险。这个习惯非常重要否则很容易出现“优化一时爽宕机两行泪”的尴尬局面。在生产环境做这种临时调参之前最好在维护窗口执行并先在测试环境验证一遍完整流程。5. 常见错误、报错与解决办法批量插入的报错种类其实不多但每个都很典型。我整理了几个高频问题附带排查思路和解决办法基本都是我在实际项目里踩过的坑。5.1 Packets larger than max_allowed_packet are not allowed这是最常见的报错。原因是单条 SQL 语句太大超过了max_allowed_packet。解决办法有两个见效最快的是调大参数SET GLOBAL max_allowed_packet 128 * 1024 * 1024;同时缩小每条 SQL 的批大小。就算你把参数调到了 1GB我也不建议把几万行拼成一条 SQL因为这么大的语句在 MySQL 解析和执行时内存占用非常夸张还可能出现主从复制延迟问题。遇到这个报错时先看参数和批大小哪个低调哪个。5.2 Prepared statement contains too many placeholders这个报错的本质是占位符数量超限。MySQL 协议限制了单个 PreparedStatement 中参数的最大数量为 65535。如果你的批量插入语句是动态拼?的字段多、行数多就会触发。解决办法很直接把每批的行数减少。10 个字段的情况下每批 6000 行就是 6 万个参数接近上限保险起见每批控制在 4000 到 5000 行。如果你的业务字段特别多比如 20 个字段每批就只能放 3000 行左右。这个限制不是 MySQL 配置能解决的是协议层写死的只能靠调批大小来规避。5.3 Deadlock found when trying to get lock批量插入时死锁并不少见尤其在多个会话同时往同一张表插数据的情况下。InnoDB 需要加锁来检查唯一索引冲突锁的顺序不同就可能死锁。排查思路查看错误日志确认哪些会话产生了死锁检查是否存在多个线程操作同一批数据范围检查唯一索引冲突是否频繁。解决办法是让批次数据和事务边界尽量有序比如多个导入任务做好分片避免不同会话同时插入相同范围的数据。更稳妥的办法是插入失败后重试整个事务InnoDB 的死锁检测一般会自动回滚其中一个事务业务端捕捉到重试即可。5.4 Duplicate entry 导致整个批量失败如果批量插入时出现了主键或唯一键冲突默认情况下整个语句都会失败之前插入的数据也会回滚。这在数据迁移中很常见CSV 里有重复数据导入直接失败。解决办法有几个方向第一清洗数据在导入前先查重去掉重复行这是最推荐的方式。第二利用INSERT IGNORE跳过冲突行INSERT IGNORE INTO user (name, age, email) VALUES (张三, 25, zhangsanexample.com);第三使用ON DUPLICATE KEY UPDATE做“存在则更新”INSERT INTO user (name, age, email, status) VALUES (张三, 25, zhangsanexample.com, 1), (李四, 30, lisiexample.com, 2) ON DUPLICATE KEY UPDATE status VALUES(status);这个语法适合“重复数据要做幂等更新”的场景比如每天导入当天的增量数据有重复就更新状态。注意MySQL 8.0.20 起推荐使用别名语法AS new ON DUPLICATE KEY UPDATE status new.status老语法虽然还能用但新项目建议用新写法INSERT INTO user (name, age, email, status) VALUES (张三, 25, zhangsanexample.com, 1) AS new ON DUPLICATE KEY UPDATE status new.status;5.5 LOAD DATA 导入中文乱码CSV 文件是 GBK 编码但表是 utf8mb4直接导入就乱码。解决办法是在LOAD DATA语句里指定CHARACTER SET或者在导入前先统一转换编码。LOAD DATA INFILE /tmp/user_data.csv INTO TABLE user CHARACTER SET utf8mb4 FIELDS TERMINATED BY , ...MySQL 会按照目标表的字符集转换读取的文件编码。最常见的问题就是字符编码不统一导致导入后查询乱码或报错。千万别忽略这一步格式化一个百万行的 CSV 很简单修复百万行乱码数据很痛苦。6. 索引、唯一键和批量插入的相互作用这一节聊一个很多人忽略的问题索引不是越多越好批量插入时尤其如此。虽然“索引越多插入越慢”是常识但具体怎么影响性能很多人不清楚。简单说每增加一个二级索引批量插入时要多更新一颗 B 树。索引页的写入如果超出缓存还会产生随机 I/O拖慢整体速度。这就是为什么在导入大量数据时有的人先删掉非必要索引导入完成后再重新建索引整体时间反而更短。这个操作有没有必要看情况。如果你的数据量在百万行以内并且导入目标是空表直接带索引插入通常问题不大耗时多一半左右可以接受。但如果数据量达到千万级或者目标表已经有几百万行数据那么“先删索引再导入、最后重建”的方案会有明显收益。因为重建索引时 MySQL 可以从头构建 B 树批量创建比逐行插入时的逐页维护效率高得多。建议这样操作导出表结构记录所有索引定义导入前先删除非必要索引保留主键等数据完全导入后再用ALTER TABLE ADD INDEX重建索引。这里要特别注意唯一索引不能随便删。如果业务要求数据不能重复必须在导入时确保数据已经清洗干净否则导入后再加唯一索引时才发现大量重复数据麻烦就大了。批量导入时我的经验是业务上用到的主要查询索引导入后再建没问题唯一性约束不要依赖“最后建索引”来兜底导入前就要清洗数据导入完成建索引后务必执行ANALYZE TABLE更新统计信息避免优化器因统计信息过旧选错执行计划。另外一个细节批量插入大量数据后表的碎片可能比较严重。必要时执行OPTIMIZE TABLE回收碎片空间但这会让表锁一段时间生产环境要避开业务高峰。7. 高并发环境下的批量插入策略批量插入不一定都是“一次性导入”很多场景是系统持续不断地批量写数据。比如接收上游系统推送的数据攒一批后批量入库。这种场景下除了 SQL 写法还要考虑并发策略。7.1 控制并发写线程数量别以为开越多的线程写入就越快。MySQL 的写入瓶颈往往在磁盘 I/O而不是 CPU。我遇到过团队用 20 个线程并发批量插入结果不仅没变快反而把 InnoDB 的锁竞争和日志写入拖垮了整体吞吐反而下降。比较稳妥的做法单表并发写线程数控制在 2 到 4 个多表写入可以适当放宽但单表不要过度并发如果一张表有多个索引并发写入的索引竞争会更严重需要下调并发数。从 CPU 使用率来看如果 MySQL 的 CPU 已经飙升到 80% 以上而磁盘 I/O 还没跑满说明大部分消耗花在了 SQL 解析和锁管理上这时候并发数不仅没帮助反而有害。7.2 写队列和批次的配合在应用层做“攒批”策略时不要严格等批次满了才写入这样突发流量下延迟太高。我常用的方式是双条件触发数据量达到阈值或时间达到阈值满足一个就写入。比如最简单的伪代码逻辑if (buffer.size() 2000 || System.currentTimeMillis() - lastFlushTime 1000) { batchInsert(buffer); buffer.clear(); lastFlushTime System.currentTimeMillis(); }这样能在“批量效率”和“数据及时性”之间取得平衡。8. 几条有意思的实测经验分享几个我实测得出的结论不一定适合所有环境但大概率有参考价值。第一批量插入单批次的行数在 2000 到 10000 之间时性能差异通常不大。真正拉开差距的是“是否用了批量写”而不是“每一批写了多少”。所以别纠结于把批次调到最优选一个中间值长期使用就行。第二LOAD DATA INFILE也受目标表索引影响。带 5 个索引的百万行表导入时间会比只有主键的表多出四五倍。如果你对导入速度有极致要求还是老老实实用“先删索引、导入、再加索引”的组合打法。第三MySQL 8.0 在批量插入上的整体性能比 5.7 有明显提升尤其是多值 INSERT 和并发写入的表现。有条件的话新项目直接上 MySQL 8.0不只是因为性能还因为参数和语法也都更新了。第四使用 Python 的executemany的时候注意它是否真的把数据合并发送了。executemany在不同驱动底层实现不同pymysql有时会把多条请求拼接发送有时逐条执行。我用下来借助pymysql时配合多值 SQL 模板最稳不要只依赖executemany的默认行为。9. 排查性能问题时的思考顺序最后给一个排查写入性能问题的顺序是我自己总结出来的非常好用先看网络往返再看事务提交频率再看 SQL 解析和日志开销最后看索引维护和磁盘 I/O。具体到操作上确认是否用上了批量写入手段确认 autocommit 是否关了事务是否合理分批确认max_allowed_packet和local_infile参数没问题确认目标表的索引数量是否合理结合SHOW ENGINE INNODB STATUS查看锁等待和日志刷写情况。这套检查下来大部分写入性能问题都能定位到根因。如果都检查完了还是慢那就要把眼光放到服务器层面——磁盘类型、raid 配置、网络 IO、CPU 调度这些都有可能成为瓶颈。批量插入优化不是靠某一个技巧它是一整套逻辑的叠加减少交互次数、控制事务大小、调整关键参数、合理设计索引、选对导入工具。每一步累积下来导入时间从几十分钟压到几十秒并不是什么难事。数据库优化的本质就是搞清楚每一秒的时间都花在了哪里然后想办法把它省下来。批量插入只是其中一个缩影理解了背后这套思考方式以后碰到的其他性能问题也都能顺藤摸瓜找到答案。
