本节目标：给图书列表做出稳定分页，给批量导入选对写入方式，并用一次 JOIN 替换活动借阅列表里的逐条查询。
适用版本：JDK 25、PostgreSQL 17、PostgreSQL JDBC 42.7.14、HikariCP 7.1.0。
承接的问题
前两节把 schema 和索引定下来了，但数据访问层还有三个形状问题，只有数据量上来才会明显：
- 列表页：
GET /api/books?page=0&size=20&q=keyword要返回当前页和总数。page从 0 开始，size默认 20、上限 100。深翻页时，offset会越来越大。 - 批量导入：一次导入几千本书，如果逐条
insert，每条都提交一次事务，耗时会长到不可接受。 - 借阅详情：列出会员当前借阅后，如果每条借阅再查一次图书，100 条借阅就是 101 次查询。这就是 N+1。
这三个问题分别对应 LIMIT/OFFSET、JDBC batch 和 JOIN。它们的共同点是：单条数据时都「能跑」，数据量一大就暴露。
本节的所有耗时都在同一台机器、同一个 postgres:17-alpine 容器（端口 55433）里测得，数据在同一张表上，连续运行取中位数。它们只用于比较三种写法之间的相对关系，不是跨机器或跨环境的性能结论。
核心机制
LIMIT/OFFSET 与稳定分页
分页的算术很简单：
offset = page * size
limit = size
page = 0 表示第一页。size 要在入口处收敛到合法范围，不能让客户端传 size=100000 把整张表拉出来。BRIEF 的约定是默认 20、上限 100；越界时回退默认值并记录 WARN。仓储层可以再做一次防御，但真正的参数校验属于第 8 章的 Web 层。
public List<Book> findPage(String keyword, int offset, int limit) {
String sql = "select isbn, title, author, published_year from books"
+ " order by title, isbn limit ? offset ?";
try (Connection connection = dataSource.getConnection();
PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setInt(1, limit);
statement.setInt(2, offset);
try (ResultSet rs = statement.executeQuery()) {
List<Book> books = new ArrayList<>();
while (rs.next()) {
books.add(map(rs));
}
return books;
}
} catch (SQLException e) {
throw SqlErrors.translate(e);
}
}
order by title, isbn 不能省。没有 ORDER BY 时，数据库返回行的顺序取决于物理存储和计划，两次查询可能给出不同的「第一页」，翻页时会出现重复或遗漏。title 可能重复，所以再加 isbn 作为决胜列，让排序成为全序的。
总数用另一条查询：
public long count(String keyword) {
String sql = keyword == null || keyword.isBlank()
? "select count(*) from books"
: "select count(*) from books where title ilike ? or author ilike ?";
try (Connection connection = dataSource.getConnection();
PreparedStatement statement = connection.prepareStatement(sql)) {
if (keyword != null && !keyword.isBlank()) {
String pattern = likePattern(keyword);
statement.setString(1, pattern);
statement.setString(2, pattern);
}
try (ResultSet rs = statement.executeQuery()) {
rs.next();
return rs.getLong(1);
}
} catch (SQLException e) {
throw SqlErrors.translate(e);
}
}
列表和总数是两条独立的 SQL。它们之间可能有新书插入，导致 total 和当前页不完全对应；对图书列表这种场景，短暂漂移可以接受。如果业务要求强一致，可以把两条查询放进同一个只读事务（第 5 章），代价是事务期间持有连接更久。
深翻页的成本来自 OFFSET：数据库仍然要从头扫描并丢弃前面的行。本机用 11 万本图书、按主键 isbn 排序测量，执行计划如下：
-- limit 20 offset 0
Limit (cost=0.42..1.57 rows=20 width=41) (actual time=0.130..0.134 rows=20 loops=1)
Buffers: shared hit=4
-> Index Scan using books_pkey on books (actual time=0.129..0.131 rows=20 loops=1)
Execution Time: 0.177 ms
-- limit 20 offset 90000
Limit (cost=5174.60..5175.75 rows=20 width=41) (actual time=15.108..15.112 rows=20 loops=1)
Buffers: shared hit=1584
-> Index Scan using books_pkey on books (actual time=0.022..12.067 rows=90020 loops=1)
Execution Time: 15.133 ms
offset 90000 的索引扫描实际返回了 90020 行，前 90000 行被 Limit 丢掉；缓冲区块从 4 涨到 1584。Java 侧连续 30 次取中位数，趋势一致：
| 查询 | 中位耗时 |
|---|---|
limit 20 offset 0 | 2.0 ms |
limit 20 offset 50000 | 5.8 ms |
limit 20 offset 90000 | 8.5 ms |
keyset：where isbn > ? order by isbn limit 20 | 1.6 ms |
keyset 分页（也叫游标分页）不用 OFFSET，而是记住上一页最后一行的排序键，下一页从它之后开始：
select isbn, title, author, published_year
from books
where isbn > ?
order by isbn
limit 20;
计划里出现 Index Cond: isbn > ?，只读 4 个缓冲区块，执行时间 0.056 ms，和页码无关。代价是不能直接跳到第 N 页，只能「下一页」；游标要由服务端编码，客户端不能随意伪造。图书列表通常只需要「下一页」，适合 keyset；后台管理需要跳页时，可以保留 OFFSET 但对最大页数设限。
上面的测量按 isbn 排序，是为了单独观察 OFFSET 的代价。实际 findPage 按 title, isbn 排序，还需要额外的排序成本；如果要做标题的 keyset 分页，需要 (title, isbn) 的复合索引和对应的游标条件。
批量写：addBatch 与 reWriteBatchedInserts
逐条插入的代码最直观：
for (Book book : books) {
repository.insert(book);
}
如果连接是自动提交，每次 insert 都是一次独立事务，PostgreSQL 要为每一行做一次 WAL 刷盘。10000 行就是 10000 次提交，耗时主要花在提交上，不是插入本身。
JDBC 的 batch 把多条语句攒起来一起发送：
public int[] insertBatch(List<Book> books) {
String sql = "insert into books (isbn, title, author, published_year)"
+ " values (?, ?, ?, ?)";
try (Connection connection = dataSource.getConnection();
PreparedStatement statement = connection.prepareStatement(sql)) {
for (Book book : books) {
statement.setString(1, book.isbn());
statement.setString(2, book.title());
statement.setString(3, book.author());
statement.setInt(4, book.year());
statement.addBatch();
}
return statement.executeBatch();
} catch (SQLException e) {
throw SqlErrors.translate(e);
}
}
addBatch() 只是把当前参数加入队列，executeBatch() 才真正发送。PostgreSQL JDBC 还有一个选项 reWriteBatchedInserts=true，它把多条 INSERT 改写成一条多值 INSERT，进一步减少解析和往返：
jdbc:postgresql://127.0.0.1:55433/libraryd?reWriteBatchedInserts=true
本机对 10000 行做三种写法的对照，每种跑 5 次取中位数，同一容器、同一张表：
| 写法 | 中位耗时 | 相对单条 |
|---|---|---|
逐条 executeUpdate，自动提交 | 24553 ms | 1× |
addBatch + executeBatch | 313 ms | 约 1/78 |
addBatch + reWriteBatchedInserts=true | 92 ms | 约 1/267 |
逐条写的样本在 20.6 秒到 25.9 秒之间，batch 在 279 到 520 毫秒之间，rewrite 在 70 到 125 毫秒之间。差异主要来自提交次数和往返次数，不是 PostgreSQL 突然变快。这个对比只在当前环境成立；换成本地 SSD、远程数据库或不同驱动版本，倍数会变，但「逐条提交最慢」的方向不变。
batch 不等于事务。默认自动提交下，一次 executeBatch() 是一条语句，具有语句级原子性；如果导入需要跨多个 batch 全部成功或全部回滚，仍然要显式关闭自动提交，在服务层包一个事务（第 5 章）。另外，PostgreSQL 在事务里遇到唯一键冲突会让整个事务进入 aborted 状态，后续语句全部失败，直到 rollback。批量导入要么先清洗重复 ISBN，要么用 ON CONFLICT DO NOTHING，要么为每一批设计好失败后的重试边界。
batch 的大小也要控制。一次攒 10 万条会占用大量内存，失败时重试成本也高。常见做法是每 500 到 1000 条调用一次 executeBatch()，既摊薄往返，又能在失败时只重试一小段。executeBatch() 返回的 int[] 在 reWriteBatchedInserts 下可能包含 Statement.SUCCESS_NO_INFO（-2），不要假设每个元素都是 1。
N+1：一次 JOIN 换掉 100 次查询
先看典型的 N+1 写法：
List<Loan> loans = loanRepository.findActiveByMember(memberId);
for (Loan loan : loans) {
Book book = bookRepository.findByIsbn(loan.isbn()).orElseThrow();
// 组装 LoanDto
}
第一条查询拿到 N 条借阅，然后每条借阅再查一次图书，总共 1 + N 次数据库往返。N 等于 100 时，就是 101 次。单次查询可能只要 0.1 毫秒，但 100 次往返的累计延迟、连接占用和日志量都会被放大。
本机对 100 条活动借阅做 30 次测量，取中位数：
nplus1 median_us=128305
join median_us=1809
逐条查询约 128.3 毫秒，一次 JOIN 约 1.8 毫秒，差了约 70 倍。这个数字同样是本机单次环境的结果，但 N+1 的放大效应与机器无关：查询次数随列表长度线性增长。
JOIN 的 SQL 把借阅和图书一次取回：
select l.id, l.isbn as loan_isbn, l.member_id, l.borrowed_on, l.due_on,
l.returned_on, l.renew_count,
b.isbn as book_isbn, b.title, b.author, b.published_year
from loans l
join books b on b.isbn = l.isbn
where l.returned_on is null
order by l.due_on, l.id
limit ?;
映射时要注意两个 isbn 列：借阅表的 isbn 和图书表的 isbn 同名，必须用别名区分，否则 rs.getString("isbn") 会取到不确定的那一列。
另一种写法是先把 N 个 ISBN 收集起来，用 where isbn = any(?) 一次查完，再在内存里按 ISBN 分组。它适合「先查主表、再按主键批量补详情」的场景，往返次数从 N 降到 2；JOIN 更适合详情和主表一起过滤、一起排序的场景。两者都比 N+1 好，选择取决于查询形状。
在 libraryd 里落地
JdbcLoanRepository 增加一个面向读模型的查询方法：
public record LoanWithBook(Loan loan, Book book) {
}
public List<LoanWithBook> findActiveWithBooks(int limit) {
String sql = "select l.id, l.isbn as loan_isbn, l.member_id, l.borrowed_on,"
+ " l.due_on, l.returned_on, l.renew_count,"
+ " b.isbn as book_isbn, b.title, b.author, b.published_year"
+ " from loans l join books b on b.isbn = l.isbn"
+ " where l.returned_on is null order by l.due_on, l.id limit ?";
try (Connection connection = dataSource.getConnection();
PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setInt(1, limit);
try (ResultSet rs = statement.executeQuery()) {
List<LoanWithBook> rows = new ArrayList<>();
while (rs.next()) {
Loan loan = new Loan(
rs.getLong("id"),
rs.getString("loan_isbn"),
rs.getString("member_id"),
rs.getObject("borrowed_on", LocalDate.class),
rs.getObject("due_on", LocalDate.class),
rs.getObject("returned_on", LocalDate.class),
rs.getInt("renew_count"));
Book book = new Book(
rs.getString("book_isbn"),
rs.getString("title"),
rs.getString("author"),
rs.getInt("published_year"));
rows.add(new LoanWithBook(loan, book));
}
return rows;
}
} catch (SQLException e) {
throw SqlErrors.translate(e);
}
}
本机调用返回 100 行，第一行的借阅 ISBN 和书名能对应上：
joined rows=100 first=9780000001000 / Book 001000
这个方法没有放进 LoanRepository 端口，因为它是读模型，不是领域端口的一部分。它属于 persistence 的查询能力，供 Web 层或服务层组装 LoanDto 使用。等第 6 章定义 DTO 时，再把 LoanWithBook 转成 LoanDto，领域层仍然不知道 JOIN 的存在。
分页的入口逻辑最终会落在 Web 层：校验 page >= 0、1 <= size <= 100，越界回退默认值并记录 WARN；然后调用 findPage(q, page * size, size) 和 count(q)，组装成 PageResponse<BookDto>。仓储只接收已经合法的 offset 和 limit，不再重复解释 HTTP 参数。
常见错误与边界
「分页不带 ORDER BY。」 没有全序排序时，数据库可以按任意顺序返回行，翻页会出现重复和遗漏。ORDER BY 的列要能唯一确定顺序；不能唯一时补一个主键列。
「深分页只调大 LIMIT。」 OFFSET 的成本来自跳过行，不是返回行。页码越深，扫描越多。能接受「下一页」的界面用 keyset；必须跳页的后台要限制最大页数，或者用覆盖索引减少回表。
「每页都跑一次 count(*)。」 大表上的精确总数要扫描索引或表。图书列表可以接受轻微漂移时，缓存总数或使用估算；如果契约要求精确总数，就接受这条查询的成本，并把它放在和列表查询相同的超时预算里。
「批量导入不控制事务边界。」 逐条自动提交最慢；一次 batch 太大又会在失败时丢掉整批。按 500 到 1000 条分批，显式事务包住需要原子性的范围，失败后从批次边界重试。
「把 executeBatch() 的返回值当成行数。」 驱动可能返回 SUCCESS_NO_INFO（-2），尤其在重写多值 INSERT 时。需要精确行数时，用 executeUpdate() 或插入后统一 count。
「在循环里查详情。」 N+1 不一定是显眼的 for 循环，也可能藏在 DTO 映射、模板渲染或 Optional 链里。看到「列表 + 每条补详情」的形状，先想 JOIN 或 = any(?)，再考虑逐条查询。
小结
- 分页用
page * size计算offset，ORDER BY必须是全序，size要在入口收敛到 1–100。 OFFSET越深越慢；keyset 分页用排序键游标，成本与页码无关，代价是不能随机跳页。- 列表和总数是两条查询，允许短暂漂移；需要强一致时放进同一个只读事务。
- 逐条自动提交插入最慢，
addBatch大幅减少往返，reWriteBatchedInserts=true进一步把多条 INSERT 合成多值 INSERT。 - batch 不等于事务；导入的原子性要靠显式事务，批次大小要控制在可重试的范围内。
- N+1 用一次 JOIN 或
= any(?)批量查询解决；100 条借阅的本机对照是 128.3 ms 对 1.8 ms。 - 所有性能数字只在同机同容器内比较，不能直接搬到其他环境。
数据访问层的三条主线到这里齐了：schema 定义规则，索引匹配查询，分页与批量控制成本。下一章要处理一个更隐蔽的问题：多个仓储方法组合时，事务边界在哪里，连接怎样在方法之间传递。
阅读导航：上一节：4.2 索引与执行计划：读懂 EXPLAIN · 下一节：5.1 JDBC 事务：提交、回滚与事务边界 。
继续阅读
探索更多技术文章
浏览归档，发现更多关于系统设计、工具链和工程实践的内容。