分页、N+1 与并发锁：访问模式如何制造性能和正确性故障

分页、N+1 与并发锁：访问模式如何制造性能和正确性故障高清原图

单条 SQL 很快，接口却随着列表长度线性变慢；翻到后一页出现重复或漏项；两个请求都读到可用库存，最后一次更新覆盖前一次。三类现象都不是 ORM 品牌问题，而是访问模式没有表达稳定顺序、查询形状和并发不变量。

Jakarta Persistence 把关系数据访问提升为持久化上下文中的对象状态机。规范明确：同一持久化上下文内，同一持久化标识只有一个受管实例；new、managed、detached、removed 是状态，不是四种 Java 类型。同步发生在 flush，flush 与 commit 又不是同一个动作。可直接对照 Jakarta Persistence 3.2 规范 与 Spring Data JPA 参考。

分页：先稳定，再谈快

场景说明：分页慢有两类。第一类是没排序或排序不稳定，用户翻页看到重复或漏数据；第二类是深分页慢，offset 越大数据库跳过的行越多。

直接做法，普通后台列表先做到稳定排序和最大页码限制。

<select id="pageOrders" resultMap="OrderListRowMap">
  select id, order_no, status, amount, created_at
  from biz_order
  where tenant_id = #{tenantId}
  order by created_at desc, id desc
  limit #{pageSize} offset #{offset}
</select>

Service 层计算分页参数，限制 pageSize 和最大 offset。

int pageSize = Math.min(Math.max(request.pageSize(), 1), 100);
int pageNo = Math.max(request.pageNo(), 1);
int offset = (pageNo - 1) * pageSize;

if (offset > 10000) {
    throw new IllegalArgumentException("deep pagination is not allowed");
}

如果用了 PageHelper，要记住 startPage 只影响紧跟着的下一次 MyBatis 查询，中间不要夹别的查询。

PageHelper.startPage(pageNo, pageSize);
List<OrderListRow> rows = orderQueryMapper.listForBackoffice(query);
PageInfo<OrderListRow> page = PageInfo.of(rows);

PageHelper 之所以能做到“不改 Mapper 方法也分页”，不是魔法，而是 MyBatis 插件链给了它切入点。它在查询进入 Executor 时拿到 MappedStatement、参数和 BoundSql，根据数据库方言生成 count SQL 和分页 SQL，再继续执行原查询链路。Spring Boot 4 项目如果使用 PageHelper，也要选对应的 4.x starter，不要把老的 1.x 配置参数原样搬过来。

深分页改成游标翻页，适合时间线、消息、订单流水这类“向后翻”的场景。

<select id="scrollOrders" resultMap="OrderListRowMap">
  select id, order_no, status, amount, created_at
  from biz_order
  where tenant_id = #{tenantId}
    <if test="lastCreatedAt != null and lastId != null">
      and (
        created_at &lt; #{lastCreatedAt}
        or (created_at = #{lastCreatedAt} and id &lt; #{lastId})
      )
    </if>
  order by created_at desc, id desc
  limit #{pageSize}
</select>

验证结果：同一查询条件下连续翻三页，检查是否重复、漏数据、排序漂移。再用执行计划确认排序字段是否走索引。

EXPLAIN
select id, order_no, status, amount, created_at
from biz_order
where tenant_id = 1001
order by created_at desc, id desc
limit 20 offset 10000;

常见坑：

只按 created_at 排序，时间相同的记录顺序不稳定，要补 id。PageHelper 自动生成的 count SQL 在复杂联表上很重，列表快但总数慢。后台导出复用分页查询，一页一页深翻，越跑越慢。

生产建议：用户界面分页可以保留 limit offset，但要设上限；导出、对账、任务扫描优先用游标或按主键范围切片，不要用深分页扫全量。

慢 SQL 和 N+1：从现象追到 Mapper

场景说明：线上慢 SQL 不会告诉你“我是哪个 Mapper 写的”。如果只有数据库慢日志，没有应用 trace、Mapper id 和参数摘要，排障会变成猜谜。

先看一条典型链路：

直接做法，把慢 SQL 的四类信息打通：

应用侧：接口路径、traceId、用户或租户、Mapper 方法、耗时、返回行数。MyBatis 侧：MappedStatement id、SQL 摘要、参数脱敏摘要。数据库侧：慢日志、执行计划、锁等待、扫描行数。

连接池侧：active、idle、pending、timeout、leak 线索。

排查 N+1 时，先看日志里是否同一条 SQL 短时间重复出现很多次。

grep "Preparing:" application.log | sort | uniq -c | sort -nr | head

如果一条查询订单的 SQL 后面跟着几十条查询订单明细的 SQL，大概率是循环里逐条查。

错误写法：

List<OrderListRow> orders = orderMapper.listRecentOrders(query);
for (OrderListRow order : orders) {
    order.setItems(orderItemMapper.listByOrderId(order.getId()));
}

改成批量查询再内存分组：

List<OrderListRow> orders = orderMapper.listRecentOrders(query);
List<Long> orderIds = orders.stream().map(OrderListRow::getId).toList();
Map<Long, List<OrderItemRow>> itemMap = orderItemMapper.listByOrderIds(orderIds)
    .stream()
    .collect(Collectors.groupingBy(OrderItemRow::getOrderId));

orders.forEach(order -> order.setItems(itemMap.getOrDefault(order.getId(), List.of())));

验证结果：

curl "https://api.example.test/orders/recent"
grep "OrderItemMapper.listByOrderId" application.log
grep "OrderItemMapper.listByOrderIds" application.log

改造后应该看不到 N 次 listByOrderId，只看到一次 listByOrderIds。

常见坑：

resultMap 里用嵌套 select，数据量一大就触发 N+1。日志只打印 SQL，不打印 Mapper id，无法回到代码。慢 SQL 只看耗时，不看返回行数、扫描行数和锁等待。

生产建议：核心查询上线前必须留执行计划。出现慢 SQL 时，先定位 Mapper，再验证索引、条件选择性、排序、分页方式和返回行数，不要直接加缓存掩盖问题。

分页：OFFSET 越深，代价越明显

列表页最容易从小问题长成大问题。第一页很快，不代表第 5000 页也快。

常见写法：

SELECT id, order_no, status, total_amount, created_at
FROM user_order
WHERE user_id = 10001
  AND status = 20
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

这类深分页通常需要先跳过大量记录，再返回 20 条。即使有索引，也可能扫描很多行。

推荐使用游标分页，也叫 seek pagination。第一页：

SELECT id, order_no, status, total_amount, created_at
FROM user_order
WHERE user_id = 10001
  AND status = 20
ORDER BY created_at DESC, id DESC
LIMIT 20;

下一页使用上一页最后一条记录的 (created_at, id)：

SELECT id, order_no, status, total_amount, created_at
FROM user_order
WHERE user_id = 10001
  AND status = 20
  AND (
    created_at < '<EVENT_TIME>'
    OR (created_at = '<EVENT_TIME>' AND id < 987654321)
  )
ORDER BY created_at DESC, id DESC
LIMIT 20;

验证方式：

EXPLAIN
SELECT id, order_no, status, total_amount, created_at
FROM user_order
WHERE user_id = 10001
  AND status = 20
  AND (
    created_at < '<EVENT_TIME>'
    OR (created_at = '<EVENT_TIME>' AND id < 987654321)
  )
ORDER BY created_at DESC, id DESC
LIMIT 20;

常见坑：

只按 created_at 排序，同一毫秒多条数据时翻页不稳定。游标字段没有放进索引，分页还是慢。产品要求跳到任意页，但数据量很大。这个时候要提前沟通边界，核心链路用游标分页，后台低频查询可以保留页码但限制最大页数。

生产建议：对核心列表写清分页契约。比如最大每页 100 条，最多允许查询最近 180 天，深分页只支持游标，不允许前端无边界传 pageSize=10000。

锁等待和死锁：先看索引，再看资源顺序

锁问题经常表现成接口慢、偶发失败、线程堆积，日志里可能是：

Lock wait timeout exceeded; try restarting transaction
Deadlock found when trying to get lock; try restarting transaction

先看是否有事务长时间不提交：

SELECT *
FROM information_schema.INNODB_TRX\G

再看锁等待关系：

SELECT *
FROM performance_schema.data_locks\G

SELECT *
FROM performance_schema.data_lock_waits\G

必要时看 InnoDB 最近一次死锁信息：

SHOW ENGINE INNODB STATUS\G

常见原因：

更新条件没命中索引，锁范围扩大。事务里做远程调用或大循环，锁持有时间太长。多条业务路径更新相同资源，但访问顺序不一致。

大批量更新一次锁太多行，和在线请求互相阻塞。

定位时不要只看“有锁等待”。要把等待关系拆成三步：

data_lock_waits 看哪个事务在等、被哪个事务挡住。data_locks 看锁对象、锁模式、索引名和记录范围。INNODB_TRX 看事务开始时间、状态和正在执行的语句。

可以把三张信息表联起来做一次粗排：

SELECT
  r.trx_id AS waiting_trx_id,
  r.trx_started AS waiting_started,
  b.trx_id AS blocking_trx_id,
  b.trx_started AS blocking_started,
  dl.OBJECT_SCHEMA,
  dl.OBJECT_NAME,
  dl.INDEX_NAME,
  dl.LOCK_TYPE,
  dl.LOCK_MODE
FROM performance_schema.data_lock_waits w
JOIN information_schema.INNODB_TRX r
  ON w.REQUESTING_ENGINE_TRANSACTION_ID = r.trx_id
JOIN information_schema.INNODB_TRX b
  ON w.BLOCKING_ENGINE_TRANSACTION_ID = b.trx_id
JOIN performance_schema.data_locks dl
  ON w.REQUESTING_ENGINE_LOCK_ID = dl.ENGINE_LOCK_ID\G

字段名在不同版本上要以当前实例为准；如果联表不成功，就分开查，不要为了一个排查 SQL 卡住。关键是拿到“等待事务、阻塞事务、表、索引、锁模式、事务持续时间”这几个证据。

直接做法：

EXPLAIN
UPDATE user_order
SET status = 30
WHERE user_id = 10001
  AND status = 10
  AND created_at < '<EVENT_TIME>';

如果更新条件没有合适索引，先不要上线。更新也需要执行计划，尤其是批量更新。生产里建议把大更新拆成小批次：

UPDATE user_order
SET status = 30
WHERE status = 10
  AND created_at < '<EVENT_TIME>'
ORDER BY id
LIMIT 500;

执行后检查：

SELECT ROW_COUNT();

循环执行时要有间隔、最大次数和回滚策略。不要写一个无限循环脚本直接打生产库。

锁类型不要只背名字，要能从 SQL 形态判断风险：

SQL 形态	常见锁范围	典型风险	验证方式
唯一索引等值命中一行	主要是记录锁	并发更新同一行等待	data_locks 看唯一索引记录
普通索引范围查询并当前读	next-key lock，记录锁加间隙锁	阻止范围内插入，接口看起来像“插入也被查询挡住”	两会话做 FOR UPDATE 和 INSERT
条件没索引或索引选择差	扫描范围扩大，锁跟着扩大	小业务更新变成大范围阻塞	先 EXPLAIN，再看 LOCK_MODE 和 INDEX_NAME
向被 gap lock 覆盖的范围插入	insert intention lock 等待	新增请求卡住但阻塞者是范围查询或更新	SHOW ENGINE INNODB STATUS\G 看 insert intention waiting
READ COMMITTED 下范围更新	间隙锁通常减少，但并非所有场景都消失	幻读预期、复制口径和业务一致性变化	同一脚本分别在 RC/RR 跑

最小复现实验如下，先建索引：

ALTER TABLE user_order ADD INDEX idx_user_status_created (user_id, status, created_at);

会话 A：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

SELECT id
FROM user_order
WHERE user_id = 10001
  AND status = 10
  AND created_at BETWEEN '<EVENT_TIME>' AND '<EVENT_TIME>'
FOR UPDATE;

会话 B 尝试插入同一索引范围：

INSERT INTO user_order (
  order_no, user_id, status, total_amount, created_at, updated_at
) VALUES (
  'NO-LOCK-TEST-001', 10001, 10, 10.00,
  '<EVENT_TIME>', NOW()
);

如果 B 被阻塞，不要只看应用超时，马上在第三个会话查：

SELECT ENGINE_TRANSACTION_ID, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_NAME = 'user_order'\G

SELECT *
FROM performance_schema.data_lock_waits\G

然后把会话 A 改成 READ COMMITTED、把查询条件改成唯一索引等值、把索引删掉分别复现一次。你会看到锁范围随隔离级别、访问路径和条件形态变化。生产建议很简单：会加锁的读写必须先证明索引路径；需要范围锁时要把范围收窄；插入被阻塞时不要只盯 INSERT，要找前面哪个范围当前读拿住了 gap。

用两个状态模型拆掉框架错觉

下面两个 Java 17 程序只保留本篇最关键的状态与分支。它们不连接真实数据库，因此不能证明驱动或数据库的厂商行为；它们用来证明调用方必须维持的不变量，真实集成测试再负责验证 SQL、锁和网络。

javac --release 17 -Xlint:all -Werror examples/backend-development/data-access/pagination-nplus-locking/KeysetPaginationDemo.java examples/backend-development/data-access/pagination-nplus-locking/OptimisticLockDemo.java
java -cp examples/backend-development/data-access/pagination-nplus-locking KeysetPaginationDemo
java -cp examples/backend-development/data-access/pagination-nplus-locking OptimisticLockDemo

predicate=(updated_at,id)>(100,42) order=updated_at,id nextCursor=101:44
sqlPredicate=id=42 AND version=7 updatedRows=0 optimisticConflict=true

输出的价值在于固定中间状态，而不是展示 API 能运行。修改实现后，如果资源没有复位、冲突被误报为成功、缓存跨越了更新边界或调用身份发生变化，模型应先失败，随后真实数据库测试再给出厂商级证据。

从“SQL 慢”退回第一处状态偏差

把正确性做成跨层验收