MyBatis 动态 SQL 与批量插入优化:从循环单条到一条 SQL 插万行-开发者社区

MyBatis 动态 SQL 与批量插入优化:从循环单条到一条 SQL 插万行

用 MyBatis 写查询,条件一多就开始拼字符串,SQL 里全是1=1这种脏东西;做批量插入时又习惯性 for 循环调 mapper,一万条数据插了半分钟。这两个痛点其实都有干净的解法。这篇把动态 SQL 的四个核心标签讲透,再一步步把批量插入从「每秒几百条」优化到「秒级万行」,代码都能直接跑。

动态 SQL:告别`where 1=1`

先看最原始的拼接写法,很多老项目里到处都是:

<!-- 反面教材:靠 1=1 兜底,SQL 脏且易错 --><selectid="search"resultType="User">select * from user where 1=1<iftest="name != null">and name = #{name}</if><iftest="status != null">and status = #{status}</if></select>

where 1=1是为了解决「第一个条件前面要不要加 and」的问题,但它让 SQL 变脏,还可能让优化器误判。MyBatis 的<where>标签专门解决这个:它会自动加上 where,并智能去掉紧跟其后的第一个 and/or。

<selectid="search"resultType="User">select * from user<where><iftest="name != null and name != ''">and name = #{name}</if><iftest="status != null">and status = #{status}</if></where></select>

如果所有条件都不成立,<where>连 where 关键字都不会输出,SQL 依然合法。同理,更新语句用<set>标签自动处理末尾多余的逗号:

<updateid="update">update user<set><iftest="name != null">name = #{name},</if><iftest="status != null">status = #{status},</if></set>where id = #{id}</update>

<set>会自动去掉最后一个字段后面多余的逗号,你只管每行都写逗号就行。

foreach:IN 查询与批量的基础

<foreach>用来遍历集合,最常见的是IN查询:

<selectid="findByIds"resultType="User">select * from user where id in<foreachcollection="ids"item="id"open="("separator=","close=")">#{id}</foreach></select>

open/close是首尾包裹,separator是元素分隔符。生成的 SQL 是where id in (?, ?, ?)。

坑点一:集合为空会生成in ()语法错误。调用前一定要判空:

if(ids==null||ids.isEmpty()){returnCollections.emptyList();// 空集合直接返回,别进 SQL}

坑点二:IN 列表别太长。几千个 id 塞进 IN 会让 SQL 巨长、优化器变慢,MySQL 还有max_allowed_packet限制。超过几百个建议分批查。

批量插入:从 for 循环到一条 SQL

最慢的写法:for 循环单条插入

// 反面教材:一万次网络往返,极慢for(Useru:users){userMapper.insert(u);// 每条一次 SQL、一次网络往返}

一万条就是一万次数据库往返,每次都有网络和 SQL 解析开销,慢到无法接受。

用 foreach 拼成一条 INSERT

把多行 values 拼进一条 SQL,一次往返插完:

<insertid="batchInsert">insert into user (name, status) values<foreachcollection="users"item="u"separator=",">(#{u.name}, #{u.status})</foreach></insert>

对应 mapper 接口:

intbatchInsert(@Param("users")List<User>users);

生成的 SQL 形如insert into user (name, status) values (?,?),(?,?),(?,?)...。一万条从几十秒降到几百毫秒,这是最立竿见影的一步。

但这个方案有上限:拼出来的 SQL 长度受 MySQLmax_allowed_packet(默认 4MB/16MB)限制,几万行、字段又多时会超包报错。所以要分批,每批 500~1000 条:

publicvoidbatchInsertInChunks(List<User>users){intbatchSize=1000;for(inti=0;i<users.size();i+=batchSize){List<User>chunk=users.subList(i,Math.min(i+batchSize,users.size()));userMapper.batchInsert(chunk);// 每批一条 SQL}}

数据量极大时:用 ExecutorType.BATCH

当数据量到十万级、或者要批量 update/delete 时,更稳的是 MyBatis 的批处理执行器。它复用同一个PreparedStatement,把多次操作攒到一起 flush,底层走 JDBC 的addBatch:

publicvoidbatchInsertWithExecutor(List<User>users,SqlSessionFactoryfactory){// 关键:openSession(ExecutorType.BATCH) 开启批处理模式try(SqlSessionsession=factory.openSession(ExecutorType.BATCH)){UserMappermapper=session.getMapper(UserMapper.class);inti=0;for(Useru:users){mapper.insert(u);// 这里仍是单条 insert 语句,但不会立即执行if(++i%1000==0){session.flushStatements();// 每 1000 条 flush 一次,避免攒太多爆内存}}session.flushStatements();// 提交剩余的session.commit();}}

它和 foreach 拼 SQL 的区别:foreach 是「一条超长 SQL」,BATCH 是「很多条短 SQL 一次性发送」。BATCH 模式对超大数据量更友好,不受单条 SQL 长度限制,还能统一用于 insert/update/delete。

用 MySQL 时还要在连接串加上rewriteBatchedStatements=true,否则 JDBC 的 addBatch 对 insert 不会真正合并,优化打折:

jdbc:mysql://host:3306/db?rewriteBatchedStatements=true

加了这个参数,驱动会把多条 insert 重写成一条多值 insert,批处理才真正生效——这是最容易被忽略、却决定性能的一行。

三种方案怎么选

几十到几百条:foreach 拼一条 INSERT,简单直接。
几千到几万条:foreach + 分批(每批 1000),兼顾速度和 packet 限制。
十万级以上、或要批量 update:ExecutorType.BATCH+rewriteBatchedStatements=true,最稳。

小结

动态 SQL 用<where>替代where 1=1,用<set>处理更新的逗号,SQL 干净不易错。
<foreach>做 IN 查询,务必判空集合(避免in ())、控制列表长度。
批量插入三级优化:for 单条(最慢)→ foreach 拼一条 SQL(快,但受 packet 限制要分批)→ExecutorType.BATCH(超大数据量最稳)。
MySQL 用批处理必须在连接串加rewriteBatchedStatements=true,否则 addBatch 不合并,白优化。

一句话记忆点:别在 Java for 循环里调 insert;小批量 foreach 拼 SQL,大批量 BATCH 执行器加 rewriteBatchedStatements。