做数据导入的时候我最怕听到的一句话就是“就几万条数据怎么跑了半小时还没跑完”在MySQL上做批量插入很多同学第一反应是写个循环一条INSERT接一条地怼进去。我早些年也是这么干的直到被线上一个跑了将近一个小时的导入任务教育了才老老实实把“MySQL 批量插入”这件事从头到尾摸了一遍。这篇内容不是什么源码级剖析而是我在实际项目中反复调优、踩坑之后沉淀下来的实战方法。你可以直接照着改也可以先理解原理再按自己的场景调整。无论你是刚接触MySQL的新手还是已经在写存储过程、处理大数据导入的开发者这篇文章的目标只有一条让你的批量插入不再成为整个数据链路的瓶颈。1. 为什么业务代码里的批量插入经常不生效从网络往返说起1.1 一次INSERT到底有多少往返开销先说个容易被忽略的事实你写一个INSERT语句执行成功哪怕数据只有一行客户端和服务端之间也不是“把SQL发过去、等结果回来”这么简单。一次典型的单条插入流程包括SQL文本打包、网络传输、服务端解析、权限检查、执行计划生成、事务与日志处理、结果回包。这个过程中网络往返round trip的时间往往比执行本身还稳定——本地测试大概在0.5到1毫秒跨机房环境下轻松超过几毫秒。我见过一个真实的接口循环往MySQL里插入3万条数据每条记录大概200字节。按每条1毫秒的网络往返算光等待网络就是30秒这还没算SQL解析、索引维护、事务提交这些额外消耗。更麻烦的是默认自动提交模式下每条INSERT都自带一个事务每次都要走一次redo log刷盘这个开销相当可观。1.2 很多框架的“批量”是伪批量很多人觉得自己已经用了批量插入因为代码里写了addBatch()或者MyBatis里用了foreach拼SQL。但实测下来没什么提速效果为什么大概率是下面两种情况程序里其实是for循环逐条执行INSERT表面封装了批量接口实际还是单条网络往返用了addBatch()但JDBC连接串里没开rewriteBatchedStatementstrueMySQL驱动会“贴心地”帮你把批量拆成逐条发送服务端收到的还是一条一条的SQL。还有一类是MyBatis的foreach拼大SQL这种方式本身有效——它确实把多条记录合并成一条SQL了但它不是真正的JDBC批次而是靠拼文本生成的巨型SQL。一旦数据量大很容易撞上max_allowed_packet的上限后面我会专门讲这个参数。1.3 真正的批量INSERT长什么样核心思路很简单把多条记录合并到一条INSERT语句里一次网络往返搞定。INSERT INTO t_user (name, age, city) VALUES (张三, 25, 杭州), (李四, 30, 上海), (王五, 28, 北京);这样网络往返次数从N次降到1次SQL解析也只有一次。实测在普通SSD机器上单条插入1万行和批量合并后插入1万行差距可以达到十倍以上。那么一次拼接多少条合适我通常控制在500到1000条一批。太小了网络往返仍然多太大了容易吃满max_allowed_packet。一条批量SQL的估算体积大约是“单行平均字节数 × 批量行数 SQL模板本身的长度”建议控制在max_allowed_packet的80%以内留出余量。提示批量插入不是行数越多越好。我见过有人一次拼5万条VALUES结果超过包大小限制直接被MySQL拒绝报错后整个批次全部回滚白跑一趟。2. MySQL服务端能扛多少参数配置是批量导入的第一道天花板2.1 max_allowed_packet批量插入的物理上限max_allowed_packet决定了MySQL最大能接收的单个数据包大小默认在MySQL 8.0里是64MB5.7时代是4MB。批量插入拼SQL时如果整体尺寸超过这个值会直接报错。这个参数要同时改服务端和客户端两边MySQL JDBC驱动侧也有一个maxAllowedPacket属性如果小于服务端配置客户端发送前就会先失败。我个人习惯在测试环境先跑一个极小批次然后逐步扩大批次卡在哪个值报错就回退一档。比如SET GLOBAL max_allowed_packet 128 * 1024 * 1024; SET SESSION max_allowed_packet 128 * 1024 * 1024;如果你用的是云数据库或者有专门的DBA团队记得让他们改配置后重启或热更新否则你这边改了会话级也没用。2.2 innodb_buffer_pool_size批量插入能不能扛住看它批量插入的核心瓶颈不只是网络还在于InnoDB要把这些数据页加载到内存再通过后台进程把脏页刷到磁盘。innodb_buffer_pool_size如果偏小批量导入会频繁触发刷盘导致写入速度忽快忽慢甚至出现“一开始飞快越跑越慢”的曲线。这个参数是全局的通常建议设为物理内存的60%到75%。比如16GB内存的实例设个10GB到12GB比较合理。要注意的是这个参数调整后一般需要重启MySQL才能完全生效8.0里可以用ALTER INSTANCE SET做在线调整但仍有诸多限制。导入前如果条件允许至少确认一下当前配置SHOW VARIABLES LIKE innodb_buffer_pool_size;2.3 事务刷盘参数批量导入期间的“临时暴力优化”这是优化效果最直观的一组参数。默认情况下innodb_flush_log_at_trx_commit1代表每次事务提交都要把redo log刷到磁盘保证任何时刻宕机数据都不丢。代价是每次提交都有一次磁盘fsync。批量导入时可以临时把它改成0或2。2只把日志写到操作系统缓存不强制刷盘速度明显提升但数据库异常断电可能丢一秒内的数据0由后台线程控制刷盘性能最好但崩溃时丢失窗口更大。类似的还有sync_binlog默认是1表示每次事务提交都同步binlog到磁盘。导入期间可以临时设置为0让MySQL自己决定什么时候刷。这些参数只建议在导入窗口内设为“性能模式”导入完成后必须恢复默认值。我还是那句话生产环境务必确认自己能承担极端情况下丢失少量数据的风险否则别动。我自己在非核心分析库上用过核心交易库从来没这么干过。优化的会话级设置可以这样SET SESSION autocommit 0; SET SESSION innodb_flush_log_at_trx_commit 0; SET SESSION sync_binlog 0; SET SESSION unique_checks 0; SET SESSION foreign_key_checks 0;unique_checks0的意思是导入期间暂时不做唯一性约束检查需要你提前保证数据里没有重复的唯一键否则会出现脏数据。foreign_key_checks0同理导入完成后必须重新校验外键关系。2.4 一个最常见的误操作ALTER TABLE ... DISABLE KEYS 在InnoDB上无效很多人从MyISAM时代带过来的习惯导入数据前先执行ALTER TABLE t DISABLE KEYS导完再ENABLE KEYS。这个命令在InnoDB上完全无效InnoDB不支持这样禁用二级索引的维护。InnoDB的做法是什么如果你有大量二级索引且导入的数据规模远大于已有数据最快的办法是先DROP INDEX导入完成后再重新CREATE INDEX。听起来反直觉但我实际测过保留索引逐条插入和维护索引的代价往往比“先删索引、导完再建”要高得多。当然如果你导入的是增量小数据几千行以内保留索引反而更方便这个要按数据量权衡。3. JDBC与MyBatis的批处理差异同样的SQL差距在驱动和会话3.1 JDBC开启rewriteBatchedStatements后发生了什么Java后端连MySQL做批量插入最常见的坑就是连接串里少了rewriteBatchedStatementstrue。没有这个参数时MySQL驱动收到addBatch()后会一条一条发送SQL和你单条循环没有本质区别。打开这个参数后驱动才会把同一条预处理SQL的多组参数值拼成多行VALUES真正在服务端执行批量插入。推荐的生产级批量插入连接串长这样jdbc:mysql://127.0.0.1:3306/test_db? useUnicodetruecharacterEncodingutf8mb4 rewriteBatchedStatementstrue useServerPrepStmtstruecachePrepStmtstrueuseServerPrepStmtstrue会在服务端创建预处理语句cachePrepStmtstrue会缓存预处理语句避免反复创建。这两项配合批量插入效果更佳。对应代码模式如下try (Connection conn DriverManager.getConnection(url, username, password); PreparedStatement ps conn.prepareStatement( INSERT INTO t_user (name, age, city) VALUES (?, ?, ?))) { conn.setAutoCommit(false); int batchSize 500; for (int i 0; i userList.size(); i) { User u userList.get(i); ps.setString(1, u.getName()); ps.setInt(2, u.getAge()); ps.setString(3, u.getCity()); ps.addBatch(); if ((i 1) % batchSize 0) { ps.executeBatch(); conn.commit(); } } ps.executeBatch(); conn.commit(); }注意setAutoCommit(false)和手动commit()。我见过有人批量接口开了但没关自动提交结果还是每条一个事务性能并没有本质提升。批量插入的核心收益是“攒批少提交”两者缺一不可。3.2 MyBatis里三种“批量”方式的实测区别MyBatis阵营常见的批量插入有三种写法效果差别很大方式原理适用规模风险foreach拼VALUES生成一条巨型INSERT SQL几百到几千条拼太大会超max_allowed_packetExecutorType.BATCH复用一条预处理SQL并攒批执行几万条以上动态SQL多时无法真正复用手写JDBCbatch配合rewriteBatchedStatements不限需要控制事务提交粒度foreach写法示例insert idbatchInsert parameterTypelist INSERT INTO t_user (name, age, city) VALUES foreach collectionlist itemu separator, (#{u.name}, #{u.age}, #{u.city}) /foreach /insert这个写法前面说过本质是拼SQL不是真正的服务器端批量。所以如果你用MyBatis同时数据量在几万行以上建议改用ExecutorType.BATCHSqlSession session sqlSessionFactory.openSession(ExecutorType.BATCH); try { UserMapper mapper session.getMapper(UserMapper.class); for (int i 0; i userList.size(); i) { mapper.insert(userList.get(i)); if ((i 1) % 500 0) { session.flushStatements(); session.commit(); } } session.commit(); } finally { session.close(); }这里有个问题我在项目里踩过BATCH模式下动态SQL里有if这类条件片段时每条记录的SQL结构可能不同驱动需要重新解析批效果会大打折扣。所以批量导入场景我强烈建议先查出来需要插入的数据然后统一走固定的INSERT模板别在批量里搞花里胡哨的动态条件。另外BATCH模式下MyBatis默认不回填自增主键因为批量执行时驱动不会逐条返回生成键。如果你后续逻辑需要每行的主键要么自己事先分配好要么用SELECT LAST_INSERT_ID()这类手段别指望批量接口自动帮你把主键set回实体里。3.3 连接池配置对批量导入的影响批量导入通常伴随着高并发写入连接池太小会导致线程互相等待连接池太大又可能把数据库连接数打满。以HikariCP为例我的经验是导入任务如果是单线程跑maximumPoolSize设5到10就够如果是并行分片导入按“分片线程数少量备用连接”来设比如8个分片线程就设10到12个连接。还有个细节连接池和max_allowed_packet是联动的。如果应用程序连接池用的连接长时间保持而数据库参数在导入前被调整过旧连接可能仍然沿用旧的会话参数。所以批量导入前最好重启应用或确保连接池能自动重建连接否则你改了服务端配置实际执行还是老样子。4. 预处理语句的真正价值不只是防SQL注入4.1 服务端预处理为什么快提到PreparedStatement大部分人的第一反应是防SQL注入第二反应是代码好写。但在批量插入场景它还有一个被低估的作用减少SQL解析开销。MySQL执行一条SQL文本要先做词法分析、语法分析、生成执行计划。虽然MySQL 8.0有查询缓存相关的优化实际上8.0已经移除查询缓存但解析和优化仍然有成本。服务端预处理通过COM_STMT_PREPARE预先把SQL模板解析好后面每次执行只需要COM_STMT_EXECUTE传参数省去了重复解析的环节。批量插入时我们用同一个INSERT模板配上不同参数正是服务端预处理最擅长的工作。所以JDBC连接串里的useServerPrepStmtstrue和cachePrepStmtstrue不是锦上添花是实打实的性能因素。4.2 预处理与BATCH的协同逻辑一套配合很默契的流程是这样的JDBC驱动发送PREPARE语句服务端生成预处理对象并缓存业务代码多次setString/setInt/setXxx每次addBatch()往客户端攒一组参数攒到批次大小后executeBatch()触发驱动把整批参数发送给服务端服务端拿着预处理模板整批参数直接走批量执行路径。所以我在生产环境推荐开启这三个参数rewriteBatchedStatementstrue、useServerPrepStmtstrue、cachePrepStmtstrue。尤其是第一条没开的批量只是心理安慰。但注意一个边界如果批量SQL本身每次都不一样比如动态拼接了不同的表名、不同的字段集合预处理就失去了模板复用的价值还可能因为预处理缓存累积导致服务端内存压力。这时不如退回到普通拼接SQL。批量导入最忌讳把“复用模板”和“动态拼SQL”混在一起用。4.3 占位符的安全与转义问题批量插入用预处理占位符还有一层好处参数值里有单引号、反斜杠、换行符这类特殊字符时驱动会帮你处理好不用自己在SQL文本里手工转义。如果是foreach拼SQL你反而要小心字段值里带单引号导致SQL语法错误。我遇到过一个case从CSV导入用户备注字段里面既有双引号又有换行符用foreach拼SQL时直接把一条记录拆成了两行报错不说数据还错了。后来改用PreparedStatement占位符问题一次解决。千万不要觉得“批量插入就是拼字符串”数据清洁度和SQL模板的稳定性在这种场景里同样重要。5. 导入大表时的锁竞争与Binlog放大效应5.1 InnoDB插入时的锁超卖问题先放一边插入锁才是关键批量插入时InnoDB并不是无锁一路狂写。每个插入操作都会涉及插入意向锁Insert Intention Lock二级索引也要加锁维护。数据量大时如果多个写事务并发操作同一范围的主键或唯一键还会出现锁等待和死锁。自增列还有一个专门的innodb_autoinc_lock_mode参数0每次插入都持有表级自增锁保证严格连续但并发差1普通插入使用互斥锁批量申请性能较好默认值2交错模式插入交错分配自增值并发最高但自增值不连续。批量导入场景如果你不需要自增ID严格连续可以对会话临时设置innodb_autoinc_lock_mode2提升性能。这个参数是全局的MySQL 8.0里可以在配置文件中改但要真正生效需要重启。这里有个矛盾重启代价大而且生产实例不是你说了算。所以我的做法是能在建表前设计好批量插入方案就直接用自然主键或业务主键省得让自增锁成为瓶颈。5.2 批量INSERT与Binlog的放大关系很多人对binlog的理解是“主从同步用的日志”但在大规模导入时binlog会实实在在拖慢写入。在binlog_formatROW格式下MySQL会把每一行数据的变更记成事件。你一条批量INSERT插了1000行binlog里就会记录1000行对应的变更事件日志量随数据量线性放大。如果你同时还有主从复制从库也要重放这些日志压力全部在链路上放大。有一个思路是在导入前确认这个实例不是复制链路的关键节点然后临时关掉SQL级binlog记录SET SESSION SQL_LOG_BIN 0;这个操作非常危险一旦实例意外宕机或切换主从数据就不一致了之后怎么补救都是麻烦。我只有在独立分析库、无任何从库依赖、且允许重建数据的场景下才用过。生产环境的常规项目我强烈不建议碰这个开关。更稳妥的做法是接受binlog开销在评估导入时间时预留充足余量。5.3 触发器与外键导入时别忽略的“隐形参与者”批量导入时如果目标表上有触发器无论你插多少行触发器都会逐行执行。这会让你的导入时间直接翻倍甚至更多。导入前先检查一下SHOW TRIGGERS LIKE t_user;如果触发器只服务于业务写入比如记录审计日志大规模初始化数据时可以先把触发器删掉或停用导完数据再恢复。外键也一样foreign_key_checks0只是部分缓解了子表扫描校验但如果有复杂的级联操作性能影响仍然不小。批量导入前的表结构清理和调参一样重要甚至更重要。6. 配套的批量删除与批量更新导入流程里的两次清扫6.1 清空旧数据的正确方式TRUNCATE与DELETE的取舍导入前要清空目标表这个步骤看起来不起眼却决定了整个导入任务的总时长。TRUNCATE TABLE是DDL操作直接重建表结构并释放数据页不逐行删除速度极快但同时不可回滚DELETE FROM是DML操作逐行删要记redo、binlog还可能因为大事务拖垮实例。所以我的规则很简单整表替换场景用TRUNCATE需要清理符合特定条件的部分数据用分批DELETE需要回滚保障宁可慢一点用DELETE并保证事务可控。TRUNCATE在InnoDB上的一个实际表现需要注意它会隐式提交事务并且速度虽快但做表结构重建时对内存和锁资源也有要求。大表TRUNCATE后如果紧接着大批量插入建议先等缓冲池状态稳定一点别一口气跑到CPU飙升。6.2 分批删除一次DELETE一百万行的教训有一次我需要清理一个月前的流水数据数据量大概800万行。一开始图省事直接一行DELETE FROM flow_log WHERE create_time 2024-01-01结果锁表时间太久业务侧直接报警。后来我改成循环分批删除每批5000行重试三次总耗时反而更短而且对在线业务几乎无感。老生常谈的批处理逻辑如下DELETE FROM flow_log WHERE create_time 2024-01-01 LIMIT 5000;配合应用层循环while (影响行数 0): 执行上述DELETE语句 每次删除后sleep 50ms避免持续占用IO 如果超时或死锁稍等后重试这里有个细节LIMIT配合DELETE在MySQL里是允许的效果是每次最多删除指定行数。但如果没有合适的索引即使每次只删5000行扫描范围仍然很大必须确保WHERE条件能走索引否则分批删比一次性删更慢。6.3 批量UPDATE的惯用技巧用CASE WHEN省网络往返批量导入不只有INSERT还有一个高频场景是“数据已存在覆盖更新”。如果逐条UPDATE又会回到网络往返的老问题。常用做法是把多条更新合并到一条SQL里用CASE WHEN做行级映射UPDATE t_user SET age CASE name WHEN 张三 THEN 26 WHEN 李四 THEN 31 WHEN 王五 THEN 29 END, city CASE name WHEN 张三 THEN 苏州 WHEN 李四 THEN 南京 WHEN 王五 THEN 深圳 END WHERE name IN (张三, 李四, 王五);这个写法把N次UPDATE变成一次收益和批量INSERT一致。但要注意WHERE name IN (...)列出的行数不能太大我一般控制在500行以内同时name字段必须有唯一索引否则CASE WHEN的映射可能出现多行更新结果不符合预期。6.4 导入流程的事务边界设计整条导入链路通常是清空旧表或删除旧数据→ 批量插入新数据 → 校验数据 → 重建索引或归档。一个常见错误是把“清空全量导入”包进同一个事务。这样做的本意是保证要么不动要么全体替换逻辑上很好看。但数据量大时一个事务里的undo log会膨胀得极其夸张事务耗时长还会让间隙锁、插入意向锁范围变大最后不仅没保住一致性反而把导入拖垮。我的做法是拆成多个独立步骤每个步骤单独提交清空数据算一个事务每500到1000条插入算一个事务校验阶段只读不加锁。如果中途失败用记录表或文件标记当前进度下次从断点继续。这是一种工程化的妥协强一致性的代价是性能和可用性批量导入这类离线型任务更适合“可重试”而不是“绝对串行”。7. LOAD DATA INFILE当批量INSERT还不够快时的终极方案7.1 为什么LOAD DATA能比INSERT快这么多如果数据已经躺在文件里CSV、TSV、定宽文本或者你做的是跨库迁移、数据倾卸那么LOAD DATA INFILE是比批量INSERT更合适的选择。MySQL官方文档说它可以达到普通INSERT语句几十倍的速度我实测也基本符合这个量级。原理很简单普通INSERT需要经过完整的SQL解析层、权限检查、执行计划生成而LOAD DATA INFILE直接把文件内容按约定格式解析后交给存储引擎做批量加载中间省掉了大量SQL文本解析和网络协议开销。它甚至比“拼一条大SQL批量INSERT”更快因为后者仍然要服务端解析一轮SQL文本。7.2 LOAD DATA INFILE的实用配置基本用法LOAD DATA INFILE /data/user.csv INTO TABLE t_user CHARACTER SET utf8mb4 FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (name, age, city);这里几个关键配置项FIELDS TERMINATED BY字段分隔符CSV就是逗号TSV就是制表符ENCLOSED BY字段包裹符通常处理带引号的字符串LINES TERMINATED BY行分隔符注意Windows下的CRLF要写\r\nIGNORE 1 LINES跳过首行表头(name, age, city)按列名映射文件字段列可以少选也可以乱序。如果文件在客户端而不是数据库服务器上需要加LOCAL关键字LOAD DATA LOCAL INFILE /local/path/user.csv INTO TABLE ...LOCAL版本意味着文件从客户端上传到服务端MySQL 8.0默认local_infile是关闭的需要SET GLOBAL local_infile 1并且客户端连接串也要加allowLoadLocalInfiletrue。这个开关有安全隐患用户可能借此读取服务器本地文件非必要不开启开了也要收窄文件目录权限。提示LOAD DATA默认还会触发外键检查、唯一键检查、binlog日志记录。导入前和批量INSERT一样先SET SESSION foreign_key_checks0; SET SESSION unique_checks0;能明显缩短时间。7.3 文件清洗与字符集是LOAD DATA最容易翻车的地方用LOAD DATA导入中文数据时很容易出现乱码。核心原因是源文件编码和表字符集不一致。我建议所有文件统一转成UTF-8导入时显式指定CHARACTER SET utf8mb4不要依赖数据库默认字符集。文件里有NULL值时用\N表示MySQL的LOAD DATA约定或者导入前先做预处理替换成空字符串。另外如果CSV字段里本身包含逗号和换行符ENCLOSED BY 能解决大部分问题但如果字段里还有双引号就需要转义处理。这类文件清洗脚本我通常用Python先跑一遍搞定编码和转义再交付给LOAD DATA不要指望MySQL能容错。8. 实测数据对比不同批量大小、不同配置下的耗时曲线8.1 测试条件与方法为了给一个直观的参考我在自己的开发机上跑过一组对比测试。环境MySQL 8.08核CPU16GB内存SSD盘InnoDB引擎目标表无大字段表里有主键和一个普通二级索引。数据量100万行每行大约200字节。测试方式分别覆盖单条INSERT、批量500条、批量1000条、开启rewriteBatchedStatements后的批量、调优参数后的批量、以及LOAD DATA INFILE。耗时记录从服务端日志角度取运行时间去掉代码编译和JVM启动等因素干扰。8.2 耗时结果表方案百万行耗时说明单条INSERT自动提交约270秒每条网络往返事务提交慢出了天际JDBC批量500条未开rewrite约230秒驱动逐条发提升极其有限JDBC批量500条开rewrite约38秒网络往返大幅减少核心优化开始生效JDBC批量1000条开rewrite手动每500条提交约31秒批次与提交粒度更匹配调参后关外键检查、unique_checks0innodb_flush_log_at_trx_commit0约19秒减少了大量约束检查与日志刷盘LOAD DATA INFILE同样调参约7秒绕开SQL解析层效率天花板最高同一批数据在不同环境下的绝对时间会有差异但趋势是一致的逐条插入是最慢的开不开rewriteBatchedStatements差别可能小差别大的是你是否理解了驱动到底做了什么而参数调优、关闭约束、LOAD DATA带来的倍数差距无论如何都值得去做。8.3 完整导入链路的最佳姿势根据上面这些实测和踩坑我把一套“最快路径”总结成操作顺序你遇到大数据量导入时可以直接照这个思路走第一步设计表结构确认主键、唯一键、索引。导入前评估是否需要临时删除二级索引。第二步执行会话级参数调整autocommit0innodb_flush_log_at_trx_commit0sync_binlog0foreign_key_checks0unique_checks0。第三步如果数据在文件里优先用LOAD DATA INFILE如果数据在应用内存里用JDBC批量rewriteBatchedStatementstrue每500到1000条提交一次。第四步导入完成后立即恢复参数重建之前删掉的索引校验行数和数据完整性。第五步切流前做一次主从或时间点备份确保数据可用归档。这个链路跑下来我见过的导入任务从“小时级”降到“分钟级”是常态个别任务从“跑不完”到“十几分钟搞定”也真实发生过。最后再分享一个我自己的习惯批量导入上线之前我一定会拿生产环境的数据量级缩样先跑一轮压测注意不是用测试数据随便跑跑而是用真实字段分布、真实行数和真实索引结构。因为很多性能问题在数据量小的时候完全不存在一旦数据量上来参数、锁、binlog、缓冲池全都会暴露问题。提前跑一遍比你上线后半夜被报警叫醒要舒服得多。
MySQL批量插入性能优化：从INSERT循环到LOAD DATA的实战指南
做数据导入的时候我最怕听到的一句话就是“就几万条数据怎么跑了半小时还没跑完”在MySQL上做批量插入很多同学第一反应是写个循环一条INSERT接一条地怼进去。我早些年也是这么干的直到被线上一个跑了将近一个小时的导入任务教育了才老老实实把“MySQL 批量插入”这件事从头到尾摸了一遍。这篇内容不是什么源码级剖析而是我在实际项目中反复调优、踩坑之后沉淀下来的实战方法。你可以直接照着改也可以先理解原理再按自己的场景调整。无论你是刚接触MySQL的新手还是已经在写存储过程、处理大数据导入的开发者这篇文章的目标只有一条让你的批量插入不再成为整个数据链路的瓶颈。1. 为什么业务代码里的批量插入经常不生效从网络往返说起1.1 一次INSERT到底有多少往返开销先说个容易被忽略的事实你写一个INSERT语句执行成功哪怕数据只有一行客户端和服务端之间也不是“把SQL发过去、等结果回来”这么简单。一次典型的单条插入流程包括SQL文本打包、网络传输、服务端解析、权限检查、执行计划生成、事务与日志处理、结果回包。这个过程中网络往返round trip的时间往往比执行本身还稳定——本地测试大概在0.5到1毫秒跨机房环境下轻松超过几毫秒。我见过一个真实的接口循环往MySQL里插入3万条数据每条记录大概200字节。按每条1毫秒的网络往返算光等待网络就是30秒这还没算SQL解析、索引维护、事务提交这些额外消耗。更麻烦的是默认自动提交模式下每条INSERT都自带一个事务每次都要走一次redo log刷盘这个开销相当可观。1.2 很多框架的“批量”是伪批量很多人觉得自己已经用了批量插入因为代码里写了addBatch()或者MyBatis里用了foreach拼SQL。但实测下来没什么提速效果为什么大概率是下面两种情况程序里其实是for循环逐条执行INSERT表面封装了批量接口实际还是单条网络往返用了addBatch()但JDBC连接串里没开rewriteBatchedStatementstrueMySQL驱动会“贴心地”帮你把批量拆成逐条发送服务端收到的还是一条一条的SQL。还有一类是MyBatis的foreach拼大SQL这种方式本身有效——它确实把多条记录合并成一条SQL了但它不是真正的JDBC批次而是靠拼文本生成的巨型SQL。一旦数据量大很容易撞上max_allowed_packet的上限后面我会专门讲这个参数。1.3 真正的批量INSERT长什么样核心思路很简单把多条记录合并到一条INSERT语句里一次网络往返搞定。INSERT INTO t_user (name, age, city) VALUES (张三, 25, 杭州), (李四, 30, 上海), (王五, 28, 北京);这样网络往返次数从N次降到1次SQL解析也只有一次。实测在普通SSD机器上单条插入1万行和批量合并后插入1万行差距可以达到十倍以上。那么一次拼接多少条合适我通常控制在500到1000条一批。太小了网络往返仍然多太大了容易吃满max_allowed_packet。一条批量SQL的估算体积大约是“单行平均字节数 × 批量行数 SQL模板本身的长度”建议控制在max_allowed_packet的80%以内留出余量。提示批量插入不是行数越多越好。我见过有人一次拼5万条VALUES结果超过包大小限制直接被MySQL拒绝报错后整个批次全部回滚白跑一趟。2. MySQL服务端能扛多少参数配置是批量导入的第一道天花板2.1 max_allowed_packet批量插入的物理上限max_allowed_packet决定了MySQL最大能接收的单个数据包大小默认在MySQL 8.0里是64MB5.7时代是4MB。批量插入拼SQL时如果整体尺寸超过这个值会直接报错。这个参数要同时改服务端和客户端两边MySQL JDBC驱动侧也有一个maxAllowedPacket属性如果小于服务端配置客户端发送前就会先失败。我个人习惯在测试环境先跑一个极小批次然后逐步扩大批次卡在哪个值报错就回退一档。比如SET GLOBAL max_allowed_packet 128 * 1024 * 1024; SET SESSION max_allowed_packet 128 * 1024 * 1024;如果你用的是云数据库或者有专门的DBA团队记得让他们改配置后重启或热更新否则你这边改了会话级也没用。2.2 innodb_buffer_pool_size批量插入能不能扛住看它批量插入的核心瓶颈不只是网络还在于InnoDB要把这些数据页加载到内存再通过后台进程把脏页刷到磁盘。innodb_buffer_pool_size如果偏小批量导入会频繁触发刷盘导致写入速度忽快忽慢甚至出现“一开始飞快越跑越慢”的曲线。这个参数是全局的通常建议设为物理内存的60%到75%。比如16GB内存的实例设个10GB到12GB比较合理。要注意的是这个参数调整后一般需要重启MySQL才能完全生效8.0里可以用ALTER INSTANCE SET做在线调整但仍有诸多限制。导入前如果条件允许至少确认一下当前配置SHOW VARIABLES LIKE innodb_buffer_pool_size;2.3 事务刷盘参数批量导入期间的“临时暴力优化”这是优化效果最直观的一组参数。默认情况下innodb_flush_log_at_trx_commit1代表每次事务提交都要把redo log刷到磁盘保证任何时刻宕机数据都不丢。代价是每次提交都有一次磁盘fsync。批量导入时可以临时把它改成0或2。2只把日志写到操作系统缓存不强制刷盘速度明显提升但数据库异常断电可能丢一秒内的数据0由后台线程控制刷盘性能最好但崩溃时丢失窗口更大。类似的还有sync_binlog默认是1表示每次事务提交都同步binlog到磁盘。导入期间可以临时设置为0让MySQL自己决定什么时候刷。这些参数只建议在导入窗口内设为“性能模式”导入完成后必须恢复默认值。我还是那句话生产环境务必确认自己能承担极端情况下丢失少量数据的风险否则别动。我自己在非核心分析库上用过核心交易库从来没这么干过。优化的会话级设置可以这样SET SESSION autocommit 0; SET SESSION innodb_flush_log_at_trx_commit 0; SET SESSION sync_binlog 0; SET SESSION unique_checks 0; SET SESSION foreign_key_checks 0;unique_checks0的意思是导入期间暂时不做唯一性约束检查需要你提前保证数据里没有重复的唯一键否则会出现脏数据。foreign_key_checks0同理导入完成后必须重新校验外键关系。2.4 一个最常见的误操作ALTER TABLE ... DISABLE KEYS 在InnoDB上无效很多人从MyISAM时代带过来的习惯导入数据前先执行ALTER TABLE t DISABLE KEYS导完再ENABLE KEYS。这个命令在InnoDB上完全无效InnoDB不支持这样禁用二级索引的维护。InnoDB的做法是什么如果你有大量二级索引且导入的数据规模远大于已有数据最快的办法是先DROP INDEX导入完成后再重新CREATE INDEX。听起来反直觉但我实际测过保留索引逐条插入和维护索引的代价往往比“先删索引、导完再建”要高得多。当然如果你导入的是增量小数据几千行以内保留索引反而更方便这个要按数据量权衡。3. JDBC与MyBatis的批处理差异同样的SQL差距在驱动和会话3.1 JDBC开启rewriteBatchedStatements后发生了什么Java后端连MySQL做批量插入最常见的坑就是连接串里少了rewriteBatchedStatementstrue。没有这个参数时MySQL驱动收到addBatch()后会一条一条发送SQL和你单条循环没有本质区别。打开这个参数后驱动才会把同一条预处理SQL的多组参数值拼成多行VALUES真正在服务端执行批量插入。推荐的生产级批量插入连接串长这样jdbc:mysql://127.0.0.1:3306/test_db? useUnicodetruecharacterEncodingutf8mb4 rewriteBatchedStatementstrue useServerPrepStmtstruecachePrepStmtstrueuseServerPrepStmtstrue会在服务端创建预处理语句cachePrepStmtstrue会缓存预处理语句避免反复创建。这两项配合批量插入效果更佳。对应代码模式如下try (Connection conn DriverManager.getConnection(url, username, password); PreparedStatement ps conn.prepareStatement( INSERT INTO t_user (name, age, city) VALUES (?, ?, ?))) { conn.setAutoCommit(false); int batchSize 500; for (int i 0; i userList.size(); i) { User u userList.get(i); ps.setString(1, u.getName()); ps.setInt(2, u.getAge()); ps.setString(3, u.getCity()); ps.addBatch(); if ((i 1) % batchSize 0) { ps.executeBatch(); conn.commit(); } } ps.executeBatch(); conn.commit(); }注意setAutoCommit(false)和手动commit()。我见过有人批量接口开了但没关自动提交结果还是每条一个事务性能并没有本质提升。批量插入的核心收益是“攒批少提交”两者缺一不可。3.2 MyBatis里三种“批量”方式的实测区别MyBatis阵营常见的批量插入有三种写法效果差别很大方式原理适用规模风险foreach拼VALUES生成一条巨型INSERT SQL几百到几千条拼太大会超max_allowed_packetExecutorType.BATCH复用一条预处理SQL并攒批执行几万条以上动态SQL多时无法真正复用手写JDBCbatch配合rewriteBatchedStatements不限需要控制事务提交粒度foreach写法示例insert idbatchInsert parameterTypelist INSERT INTO t_user (name, age, city) VALUES foreach collectionlist itemu separator, (#{u.name}, #{u.age}, #{u.city}) /foreach /insert这个写法前面说过本质是拼SQL不是真正的服务器端批量。所以如果你用MyBatis同时数据量在几万行以上建议改用ExecutorType.BATCHSqlSession session sqlSessionFactory.openSession(ExecutorType.BATCH); try { UserMapper mapper session.getMapper(UserMapper.class); for (int i 0; i userList.size(); i) { mapper.insert(userList.get(i)); if ((i 1) % 500 0) { session.flushStatements(); session.commit(); } } session.commit(); } finally { session.close(); }这里有个问题我在项目里踩过BATCH模式下动态SQL里有if这类条件片段时每条记录的SQL结构可能不同驱动需要重新解析批效果会大打折扣。所以批量导入场景我强烈建议先查出来需要插入的数据然后统一走固定的INSERT模板别在批量里搞花里胡哨的动态条件。另外BATCH模式下MyBatis默认不回填自增主键因为批量执行时驱动不会逐条返回生成键。如果你后续逻辑需要每行的主键要么自己事先分配好要么用SELECT LAST_INSERT_ID()这类手段别指望批量接口自动帮你把主键set回实体里。3.3 连接池配置对批量导入的影响批量导入通常伴随着高并发写入连接池太小会导致线程互相等待连接池太大又可能把数据库连接数打满。以HikariCP为例我的经验是导入任务如果是单线程跑maximumPoolSize设5到10就够如果是并行分片导入按“分片线程数少量备用连接”来设比如8个分片线程就设10到12个连接。还有个细节连接池和max_allowed_packet是联动的。如果应用程序连接池用的连接长时间保持而数据库参数在导入前被调整过旧连接可能仍然沿用旧的会话参数。所以批量导入前最好重启应用或确保连接池能自动重建连接否则你改了服务端配置实际执行还是老样子。4. 预处理语句的真正价值不只是防SQL注入4.1 服务端预处理为什么快提到PreparedStatement大部分人的第一反应是防SQL注入第二反应是代码好写。但在批量插入场景它还有一个被低估的作用减少SQL解析开销。MySQL执行一条SQL文本要先做词法分析、语法分析、生成执行计划。虽然MySQL 8.0有查询缓存相关的优化实际上8.0已经移除查询缓存但解析和优化仍然有成本。服务端预处理通过COM_STMT_PREPARE预先把SQL模板解析好后面每次执行只需要COM_STMT_EXECUTE传参数省去了重复解析的环节。批量插入时我们用同一个INSERT模板配上不同参数正是服务端预处理最擅长的工作。所以JDBC连接串里的useServerPrepStmtstrue和cachePrepStmtstrue不是锦上添花是实打实的性能因素。4.2 预处理与BATCH的协同逻辑一套配合很默契的流程是这样的JDBC驱动发送PREPARE语句服务端生成预处理对象并缓存业务代码多次setString/setInt/setXxx每次addBatch()往客户端攒一组参数攒到批次大小后executeBatch()触发驱动把整批参数发送给服务端服务端拿着预处理模板整批参数直接走批量执行路径。所以我在生产环境推荐开启这三个参数rewriteBatchedStatementstrue、useServerPrepStmtstrue、cachePrepStmtstrue。尤其是第一条没开的批量只是心理安慰。但注意一个边界如果批量SQL本身每次都不一样比如动态拼接了不同的表名、不同的字段集合预处理就失去了模板复用的价值还可能因为预处理缓存累积导致服务端内存压力。这时不如退回到普通拼接SQL。批量导入最忌讳把“复用模板”和“动态拼SQL”混在一起用。4.3 占位符的安全与转义问题批量插入用预处理占位符还有一层好处参数值里有单引号、反斜杠、换行符这类特殊字符时驱动会帮你处理好不用自己在SQL文本里手工转义。如果是foreach拼SQL你反而要小心字段值里带单引号导致SQL语法错误。我遇到过一个case从CSV导入用户备注字段里面既有双引号又有换行符用foreach拼SQL时直接把一条记录拆成了两行报错不说数据还错了。后来改用PreparedStatement占位符问题一次解决。千万不要觉得“批量插入就是拼字符串”数据清洁度和SQL模板的稳定性在这种场景里同样重要。5. 导入大表时的锁竞争与Binlog放大效应5.1 InnoDB插入时的锁超卖问题先放一边插入锁才是关键批量插入时InnoDB并不是无锁一路狂写。每个插入操作都会涉及插入意向锁Insert Intention Lock二级索引也要加锁维护。数据量大时如果多个写事务并发操作同一范围的主键或唯一键还会出现锁等待和死锁。自增列还有一个专门的innodb_autoinc_lock_mode参数0每次插入都持有表级自增锁保证严格连续但并发差1普通插入使用互斥锁批量申请性能较好默认值2交错模式插入交错分配自增值并发最高但自增值不连续。批量导入场景如果你不需要自增ID严格连续可以对会话临时设置innodb_autoinc_lock_mode2提升性能。这个参数是全局的MySQL 8.0里可以在配置文件中改但要真正生效需要重启。这里有个矛盾重启代价大而且生产实例不是你说了算。所以我的做法是能在建表前设计好批量插入方案就直接用自然主键或业务主键省得让自增锁成为瓶颈。5.2 批量INSERT与Binlog的放大关系很多人对binlog的理解是“主从同步用的日志”但在大规模导入时binlog会实实在在拖慢写入。在binlog_formatROW格式下MySQL会把每一行数据的变更记成事件。你一条批量INSERT插了1000行binlog里就会记录1000行对应的变更事件日志量随数据量线性放大。如果你同时还有主从复制从库也要重放这些日志压力全部在链路上放大。有一个思路是在导入前确认这个实例不是复制链路的关键节点然后临时关掉SQL级binlog记录SET SESSION SQL_LOG_BIN 0;这个操作非常危险一旦实例意外宕机或切换主从数据就不一致了之后怎么补救都是麻烦。我只有在独立分析库、无任何从库依赖、且允许重建数据的场景下才用过。生产环境的常规项目我强烈不建议碰这个开关。更稳妥的做法是接受binlog开销在评估导入时间时预留充足余量。5.3 触发器与外键导入时别忽略的“隐形参与者”批量导入时如果目标表上有触发器无论你插多少行触发器都会逐行执行。这会让你的导入时间直接翻倍甚至更多。导入前先检查一下SHOW TRIGGERS LIKE t_user;如果触发器只服务于业务写入比如记录审计日志大规模初始化数据时可以先把触发器删掉或停用导完数据再恢复。外键也一样foreign_key_checks0只是部分缓解了子表扫描校验但如果有复杂的级联操作性能影响仍然不小。批量导入前的表结构清理和调参一样重要甚至更重要。6. 配套的批量删除与批量更新导入流程里的两次清扫6.1 清空旧数据的正确方式TRUNCATE与DELETE的取舍导入前要清空目标表这个步骤看起来不起眼却决定了整个导入任务的总时长。TRUNCATE TABLE是DDL操作直接重建表结构并释放数据页不逐行删除速度极快但同时不可回滚DELETE FROM是DML操作逐行删要记redo、binlog还可能因为大事务拖垮实例。所以我的规则很简单整表替换场景用TRUNCATE需要清理符合特定条件的部分数据用分批DELETE需要回滚保障宁可慢一点用DELETE并保证事务可控。TRUNCATE在InnoDB上的一个实际表现需要注意它会隐式提交事务并且速度虽快但做表结构重建时对内存和锁资源也有要求。大表TRUNCATE后如果紧接着大批量插入建议先等缓冲池状态稳定一点别一口气跑到CPU飙升。6.2 分批删除一次DELETE一百万行的教训有一次我需要清理一个月前的流水数据数据量大概800万行。一开始图省事直接一行DELETE FROM flow_log WHERE create_time 2024-01-01结果锁表时间太久业务侧直接报警。后来我改成循环分批删除每批5000行重试三次总耗时反而更短而且对在线业务几乎无感。老生常谈的批处理逻辑如下DELETE FROM flow_log WHERE create_time 2024-01-01 LIMIT 5000;配合应用层循环while (影响行数 0): 执行上述DELETE语句 每次删除后sleep 50ms避免持续占用IO 如果超时或死锁稍等后重试这里有个细节LIMIT配合DELETE在MySQL里是允许的效果是每次最多删除指定行数。但如果没有合适的索引即使每次只删5000行扫描范围仍然很大必须确保WHERE条件能走索引否则分批删比一次性删更慢。6.3 批量UPDATE的惯用技巧用CASE WHEN省网络往返批量导入不只有INSERT还有一个高频场景是“数据已存在覆盖更新”。如果逐条UPDATE又会回到网络往返的老问题。常用做法是把多条更新合并到一条SQL里用CASE WHEN做行级映射UPDATE t_user SET age CASE name WHEN 张三 THEN 26 WHEN 李四 THEN 31 WHEN 王五 THEN 29 END, city CASE name WHEN 张三 THEN 苏州 WHEN 李四 THEN 南京 WHEN 王五 THEN 深圳 END WHERE name IN (张三, 李四, 王五);这个写法把N次UPDATE变成一次收益和批量INSERT一致。但要注意WHERE name IN (...)列出的行数不能太大我一般控制在500行以内同时name字段必须有唯一索引否则CASE WHEN的映射可能出现多行更新结果不符合预期。6.4 导入流程的事务边界设计整条导入链路通常是清空旧表或删除旧数据→ 批量插入新数据 → 校验数据 → 重建索引或归档。一个常见错误是把“清空全量导入”包进同一个事务。这样做的本意是保证要么不动要么全体替换逻辑上很好看。但数据量大时一个事务里的undo log会膨胀得极其夸张事务耗时长还会让间隙锁、插入意向锁范围变大最后不仅没保住一致性反而把导入拖垮。我的做法是拆成多个独立步骤每个步骤单独提交清空数据算一个事务每500到1000条插入算一个事务校验阶段只读不加锁。如果中途失败用记录表或文件标记当前进度下次从断点继续。这是一种工程化的妥协强一致性的代价是性能和可用性批量导入这类离线型任务更适合“可重试”而不是“绝对串行”。7. LOAD DATA INFILE当批量INSERT还不够快时的终极方案7.1 为什么LOAD DATA能比INSERT快这么多如果数据已经躺在文件里CSV、TSV、定宽文本或者你做的是跨库迁移、数据倾卸那么LOAD DATA INFILE是比批量INSERT更合适的选择。MySQL官方文档说它可以达到普通INSERT语句几十倍的速度我实测也基本符合这个量级。原理很简单普通INSERT需要经过完整的SQL解析层、权限检查、执行计划生成而LOAD DATA INFILE直接把文件内容按约定格式解析后交给存储引擎做批量加载中间省掉了大量SQL文本解析和网络协议开销。它甚至比“拼一条大SQL批量INSERT”更快因为后者仍然要服务端解析一轮SQL文本。7.2 LOAD DATA INFILE的实用配置基本用法LOAD DATA INFILE /data/user.csv INTO TABLE t_user CHARACTER SET utf8mb4 FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (name, age, city);这里几个关键配置项FIELDS TERMINATED BY字段分隔符CSV就是逗号TSV就是制表符ENCLOSED BY字段包裹符通常处理带引号的字符串LINES TERMINATED BY行分隔符注意Windows下的CRLF要写\r\nIGNORE 1 LINES跳过首行表头(name, age, city)按列名映射文件字段列可以少选也可以乱序。如果文件在客户端而不是数据库服务器上需要加LOCAL关键字LOAD DATA LOCAL INFILE /local/path/user.csv INTO TABLE ...LOCAL版本意味着文件从客户端上传到服务端MySQL 8.0默认local_infile是关闭的需要SET GLOBAL local_infile 1并且客户端连接串也要加allowLoadLocalInfiletrue。这个开关有安全隐患用户可能借此读取服务器本地文件非必要不开启开了也要收窄文件目录权限。提示LOAD DATA默认还会触发外键检查、唯一键检查、binlog日志记录。导入前和批量INSERT一样先SET SESSION foreign_key_checks0; SET SESSION unique_checks0;能明显缩短时间。7.3 文件清洗与字符集是LOAD DATA最容易翻车的地方用LOAD DATA导入中文数据时很容易出现乱码。核心原因是源文件编码和表字符集不一致。我建议所有文件统一转成UTF-8导入时显式指定CHARACTER SET utf8mb4不要依赖数据库默认字符集。文件里有NULL值时用\N表示MySQL的LOAD DATA约定或者导入前先做预处理替换成空字符串。另外如果CSV字段里本身包含逗号和换行符ENCLOSED BY 能解决大部分问题但如果字段里还有双引号就需要转义处理。这类文件清洗脚本我通常用Python先跑一遍搞定编码和转义再交付给LOAD DATA不要指望MySQL能容错。8. 实测数据对比不同批量大小、不同配置下的耗时曲线8.1 测试条件与方法为了给一个直观的参考我在自己的开发机上跑过一组对比测试。环境MySQL 8.08核CPU16GB内存SSD盘InnoDB引擎目标表无大字段表里有主键和一个普通二级索引。数据量100万行每行大约200字节。测试方式分别覆盖单条INSERT、批量500条、批量1000条、开启rewriteBatchedStatements后的批量、调优参数后的批量、以及LOAD DATA INFILE。耗时记录从服务端日志角度取运行时间去掉代码编译和JVM启动等因素干扰。8.2 耗时结果表方案百万行耗时说明单条INSERT自动提交约270秒每条网络往返事务提交慢出了天际JDBC批量500条未开rewrite约230秒驱动逐条发提升极其有限JDBC批量500条开rewrite约38秒网络往返大幅减少核心优化开始生效JDBC批量1000条开rewrite手动每500条提交约31秒批次与提交粒度更匹配调参后关外键检查、unique_checks0innodb_flush_log_at_trx_commit0约19秒减少了大量约束检查与日志刷盘LOAD DATA INFILE同样调参约7秒绕开SQL解析层效率天花板最高同一批数据在不同环境下的绝对时间会有差异但趋势是一致的逐条插入是最慢的开不开rewriteBatchedStatements差别可能小差别大的是你是否理解了驱动到底做了什么而参数调优、关闭约束、LOAD DATA带来的倍数差距无论如何都值得去做。8.3 完整导入链路的最佳姿势根据上面这些实测和踩坑我把一套“最快路径”总结成操作顺序你遇到大数据量导入时可以直接照这个思路走第一步设计表结构确认主键、唯一键、索引。导入前评估是否需要临时删除二级索引。第二步执行会话级参数调整autocommit0innodb_flush_log_at_trx_commit0sync_binlog0foreign_key_checks0unique_checks0。第三步如果数据在文件里优先用LOAD DATA INFILE如果数据在应用内存里用JDBC批量rewriteBatchedStatementstrue每500到1000条提交一次。第四步导入完成后立即恢复参数重建之前删掉的索引校验行数和数据完整性。第五步切流前做一次主从或时间点备份确保数据可用归档。这个链路跑下来我见过的导入任务从“小时级”降到“分钟级”是常态个别任务从“跑不完”到“十几分钟搞定”也真实发生过。最后再分享一个我自己的习惯批量导入上线之前我一定会拿生产环境的数据量级缩样先跑一轮压测注意不是用测试数据随便跑跑而是用真实字段分布、真实行数和真实索引结构。因为很多性能问题在数据量小的时候完全不存在一旦数据量上来参数、锁、binlog、缓冲池全都会暴露问题。提前跑一遍比你上线后半夜被报警叫醒要舒服得多。
