批处理与分页
批处理是把多条结构相同的写入攒成一批交给数据库,减少逐条发送的往返;分页是限定一次读取多少数据。两者都是常见的性能手段,但都有容易混淆的边界:一批语句执行完不等于已经提交,换成游标分页也不等于结果不重不漏。
批次大小和事务大小是两回事;Offset 分页越往后越慢,Keyset 分页从上一页末尾继续。这些优化最后都要在目标数据库和真实数据规模下验证。文中的示例基于 JDBC、MySQL 8.0 和 Hibernate ORM 5.6/6.2。
1. 面试核心
先记住这几条,再读正文- JDBC batch 只是把多组参数交给驱动批量执行,整批共同成功或回滚要靠事务保证。
executeBatch()失败后,要结合更新计数和事务状态决定重试范围。卡 · 批大小与事务范围 - JPA 分批写入时,先
flush()把待写变化同步到数据库,再clear()解除实体管理;顺序反过来会丢掉尚未同步的修改。卡 · MyBatis 与 JPA 的本地状态 hibernate.jdbc.batch_size=50只把单批语句上限设为 50,什么时候提交仍由 commit 决定。卡 · Hibernate 批次与提交频率- 深 Offset 分页仍要扫描并跳过前面的记录。覆盖索引能减少回表,但消除不了偏移扫描。卡 · Offset 分页成本
- Keyset 分页依赖唯一且稳定的排序;游标只记录位置,不冻结数据,精确导出还需要固定快照或业务版本。卡 · Keyset 分页
2. JDBC 批处理
多行数据用同一种 SQL 结构写入时,可以先把每行的参数收集起来,再一次交给驱动执行,省去逐条发送语句的开销:
try (PreparedStatement statement = connection.prepareStatement(SQL)) {
for (OrderRow row : rows) {
statement.setLong(1, row.id());
statement.setBigDecimal(2, row.amount());
statement.addBatch();
}
int[] counts = statement.executeBatch();
}
PreparedStatement 保存带占位符的 SQL,每次 addBatch() 把当前这组参数加进批次。循环结束后,executeBatch() 把攒下的命令交给驱动执行,返回的 counts 是每条命令的更新计数。
驱动拿到批次后怎么发,取决于数据库协议和驱动配置:可能合并发送,也可能改写成一条多值语句。实际往返了几次、单批最多支持多少条、生成的主键怎样返回,都要针对你用的驱动验证,可以看 JDBC 日志和数据库指标确认批处理真的发生了。
3. 批大小与事务范围
批大小决定一次交给驱动多少条语句,提交频率决定多少次写入属于同一个事务,这是两个独立的旋钮。比如导入一百万行,可以每 500 条执行一次批次,再按一致性要求决定多久提交一次。
两个旋钮各有代价:
- 批次太小,减少网络往返的收益有限。
- 批次太大,参数占用的内存增加,一旦失败,定位和重试的成本也更高。
- 事务太大,锁持有时间变长、日志保留量增加,回滚范围也随之扩大。
批处理失败时会抛出 BatchUpdateException,它带着更新计数。驱动可以在失败处停下,也可以继续执行剩下的命令,所以重试范围要结合事务状态来定(下一节展开)。如果写入涉及唯一约束,先要保证幂等,也就是同一笔业务重复执行仍能得到正确结果,然后再考虑增大批量。
4. 批处理结果与事务示例
更新计数告诉你哪些命令执行过,事务状态才决定哪些结果真正提交了。需要整批一起成功时,可以像下面这样,在新获取的独立连接上关闭自动提交,执行完再提交:
try (Connection connection = dataSource.getConnection()) {
connection.setAutoCommit(false);
try {
// 使用该连接 addBatch / executeBatch,核对更新计数。
connection.commit();
} catch (SQLException e) {
try { connection.rollback(); }
catch (SQLException rollbackFailure) { e.addSuppressed(rollbackFailure); }
throw e;
}
}
批量执行成功就 commit(),遇到 SQLException 就尝试 rollback();如果回滚也失败,回滚异常会作为 suppressed 异常附在原异常上,不会把原因盖掉。如果连接由 Spring 管理,就不要这样手动提交,而是让内部代码沿用 Spring 的事务边界。
读更新计数(update counts)时,要区分三种值:
- 非负数:该命令成功,数值是影响的行数。
SUCCESS_NO_INFO=-2:执行成功,但驱动没有给出精确行数。EXECUTE_FAILED=-3:该命令失败。只有驱动在失败后继续执行剩余命令时才会出现。
如果驱动在失败处中止,数组里只有中止前那些成功命令的结果。所以负数各有含义,不能统一当成「影响了零行」。
决定重试之前,还要确认 autoCommit 配置和事务有没有提交。自动提交模式下,失败前的命令可能已经各自提交;如果在提交阶段断线,调用方根本无法直接知道最终结果。这时只能按业务唯一键去数据库核对,看哪些操作已经生效。只有确认没有提交、并且已经回滚,才能重试整个业务单元。
5. MyBatis 与 JPA 的本地状态
用框架批量写入时,除了数据库那边的执行,内存里还压着待执行的语句和被跟踪的实体,控制批大小时要把这些本地状态一起管住。
MyBatis 的 ExecutorType.BATCH 会把更新延迟到刷新批次时才执行,所以最终影响行数要从刷新后的结果里读。执行时机和返回值见 MyBatis。
JPA 的持久化上下文会保留受管实体,也就是框架正在跟踪变化的对象。导入一万条数据时,如果一直不清理,上下文里就会攒下一万个实体。所以要按块处理:先 flush() 把待写变化同步到数据库,再 clear() 解除管理。顺序不能反,先 clear() 的话,还没同步的修改就直接丢了。语句最终能不能合批,还取决于主键策略、实体关系和 Hibernate 的批处理配置。
反过来,用批量 DML 直接改数据库时,已经加载的实体不会跟着变,仍然是旧值,需要在明确的边界上清理或刷新。处理顺序见 JPA。
6. Hibernate 批次与提交频率
Hibernate 的批处理配置只决定语句怎样交给驱动,事务仍由外层的提交边界决定。Hibernate ORM 5.6/6.2 默认 hibernate.jdbc.batch_size=0,设成正数才会为有状态 Session 启用 JDBC 批处理:
hibernate.jdbc.batch_size=50
hibernate.order_inserts=true
hibernate.order_updates=true
batch_size 把单批交给驱动的语句上限设为 50。两个 order_* 配置会调整插入和更新的执行顺序,让相同的 SQL 排在一起,更容易进入同一批次;排序本身也有成本,要按实际负载验证。另外,使用 IDENTITY 主键策略时,Hibernate 5.6/6.2 会关闭 JDBC 插入合批,因为每插一行都要立刻拿回数据库生成的主键。
每 50 条调用一次 flush()、clear(),限制的是上下文里的实体数量。如果一万条数据只在最后 commit 一次,它们仍然是一个大事务。想缩短事务就要分批 commit,同时接受一个事实:已经提交的批次是独立成功的,后面的批次失败时,需要单独设计恢复方式。
7. Offset 分页成本
Offset 分页先跳过指定数量的记录,再返回一页,所以页码越靠后,要跳过的记录越多:
select id, created_at
from orders
order by created_at desc, id desc
limit 20 offset 1000000;
这条 MySQL 8.0 查询只返回 20 行,却要先沿排序结果跳过前一百万行。有匹配排序的索引,可以省掉排序;有覆盖索引(索引里包含查询需要的全部列),可以省掉回表取列。但扫描并跳过大量索引项这一步,怎么都省不掉。
Offset 还有正确性问题:翻页期间有人插入或删除数据,后面的记录位置就会整体移动,相邻两页可能出现重复或遗漏。后台管理页面偶尔跳到某一页,用 Offset 没问题;持续下拉或顺序批量扫描,更适合从上一页最后一行接着查。
8. Keyset 分页
Keyset 分页用上一页最后一条记录的排序值定位下一页,这组值叫游标。查询直接从游标之后开始,不用从头跳过大量记录:
select id, created_at
from orders
where created_at < :lastCreatedAt
or (created_at = :lastCreatedAt and id < :lastId)
order by created_at desc, id desc
limit 20;
created_at 可能重复,如果只按时间排序,同一时刻的多条记录分在两页时,就分不清哪些已经返回过。所以示例在后面补上主键 id,让组合排序唯一。排序键还要稳定,记录才会一直待在游标的同一侧。建索引时,过滤条件和排序顺序要一起考虑。
游标里要保存或编码这些定位信息:
- 上一页末行的排序值,例如
created_at和id。 - 翻页方向,以及和方向匹配的比较规则。
- 查询条件或它的摘要,让游标只对当前这次查询有效。
服务端要校验这些参数,也可以给游标签名,防止客户端篡改。筛选条件变了,就从第一页重新开始。
Keyset 适合拿着上一页游标连续往后翻。它不擅长任意跳页和精确统计总页数,这些都要额外查询,所以选哪种分页方式,要看页面是否需要这些能力。
9. 游标与一致性边界
游标解决的是从哪里接着读,至于读出来的结果完不完整,还取决于边界条件和数据变化。上一节的查询要求 created_at 和 id 都非空,并且组合排序唯一。倒序翻页时下一页用严格小于;如果游标只记时间,同一时刻还没返回的记录就会被漏掉。
上一节的条件也可以写成更短的元组比较(行构造器比较),把两个字段作为一组和游标比较:
where (created_at, id) < (:lastCreatedAt, :lastId)
两种写法语义相同,但 MySQL 对元组比较的优化有限,官方文档也建议改写成展开的形式。在 MySQL 8.4 上对 30 万行的表实测:展开写法走 (created_at, id) 索引的范围扫描,只读了 20 个索引项;元组写法变成从索引末尾倒序扫描、逐行过滤,读了 17 万多个索引项才凑够 20 行。越往后翻,要跳过的索引项越多,又回到了深 Offset 的问题。所以在 MySQL 上优先用展开写法,换数据库或升级版本时再用执行计划确认。
Keyset 能避免前面插入数据导致的位置移动,但它不是数据库快照。记录的排序字段被更新,就可能跨过游标;删除也会改变后续结果,所以重复和遗漏仍然可能出现。
需要精确导出时,就得固定事务快照、冻结数据集,或者按固定的业务版本读取。按单调递增主键扫描时,还可以在开始时记下当前最大 ID 作为截止点,只限制本次扫描不读后来新增的行。已有行的内容仍然可能被修改,这个截止点管不到。
10. 关联分页的结果规模
关联分页先要想清楚:一页的单位是父对象,还是 join 展开以后的结果行。一对多关联会让一个父对象占好几行,直接加 LIMIT 截的是展开后的行,结果是每页的父对象数量忽多忽少,最后一个父对象的子集合还可能不完整。
做法是分两步:先分页查出本页的父 ID,再抓取这些父对象的关联数据,并恢复第一步的顺序,这样每页父对象的数量就稳定了。两次读取之间数据会不会变,取决于事务隔离级别或业务版本,具体过程见 N+1 查询。
11. 目标数据库的访问测试
数据访问测试要验证的是最终 SQL 在目标数据库上怎样执行,所以要用目标数据库来测。H2 可以测少量通用逻辑,但 MySQL 8.0 的方言、锁、索引、时区和执行计划,只能在同版本上验证。关键的 Repository 或 Mapper 测试,可以用 Testcontainers 启动一个隔离的数据库容器。
测试至少要覆盖这些正确性和执行行为:
- 参数绑定是否正确,包括空值、枚举和时间类型。
- 唯一约束、外键,以及失败后事务回滚的结果。
- 动态 SQL、排序字段白名单和分页边界。
- 关联查询的次数(发现 N+1),以及写入是否真的走了批处理。
- 并发更新、锁等待和超时后的处理结果。
- 数据库迁移脚本能否兼容已有数据。
有一类问题自动回滚的测试发现不了:测试执行到回滚就结束了,提交之后的行为根本没跑到。需要验证提交后的缓存、事件或数据可见性时,要显式 commit,再开一个新事务读取检查。有些约束数据库是在提交时才检查的,这类测试也要走到真实提交。
12. 性能与结果验证
性能验证要同时证明两件事:执行更快了,结果仍然正确,而且数据规模要接近实际使用。为了能和其他方案比较,记录下面这些内容:
- 批大小、行宽、索引、驱动配置和提交频率,保证不同测试的条件可比。
- 吞吐、P99、重试次数和数据库资源消耗,判断加速是不是只是把成本转移到了别处。
- 影响行数、重复键、遗漏记录和返回顺序,以及并发修改后的分页结果。
只有性能和结果都达标,才换用新的批处理或分页方案。
13. 面试题卡片
14. 参考资料
- JDBC:PreparedStatement
- MyBatis:Java API
- Hibernate ORM 5.6 User Guide:Batching
- Hibernate ORM 6.2 User Guide:Batching
- Testcontainers for Java:Database Containers
- JDBC:BatchUpdateException
- MySQL 8.0:Row Constructor Optimization
- JDBC 21:Statement.executeBatch
- MySQL 8.0:LIMIT Query Optimization
来源:Coding 101 原文 · 免费阅读,引用请保留出处。内容使用说明