1. Keyset（游标）—— 性能王者#

1
-- 单一排序键
2
SELECT * FROM t_order
3
WHERE id < :last_id
4
ORDER BY id DESC
5
LIMIT 10;
6

7
-- 复合排序键（如按 created_at 排序）必须带主键 tie-breaker
8
SELECT * FROM post
9
WHERE (created_on, id) < ('2024-10-02 21:00:00', 4951)
10
ORDER BY created_on DESC, id DESC
11
LIMIT 50;

Vlad Mihalcea 实测：100 万行表，最后一页 OFFSET 要 1+ 秒，Keyset 仅 0.279 ms。

限制：无法直接跳到第 N 页、必须有稳定排序键。适合”加载更多”、信息流、消息列表。

2. Deferred Join（延迟关联）—— 最务实的折中#

阿里《Java 开发手册》强制要求的优化方式：

1
-- 不行：OFFSET 大时极慢
2
SELECT * FROM people ORDER BY birthday, id LIMIT 20 OFFSET 450000;
3
-- 600-700 ms
4

5
-- 优化：先在覆盖索引上 OFFSET，再回表 20 次
6
SELECT * FROM people
7
INNER JOIN (
8
  SELECT id FROM people
9
  ORDER BY birthday, id
10
  LIMIT 20 OFFSET 450000
11
) AS p2 USING (id)
12
ORDER BY birthday, id;
13
-- 200 ms（再加 birthday 索引可降到 60 ms）

核心原理：让 MySQL 在只含主键的覆盖索引上完成 OFFSET 跳过，避免对 100 万行做回表。最终只回表 20 次。

3. 子查询定位起点#

1
SELECT * FROM t_order
2
WHERE id >= ( SELECT id FROM t_order ORDER BY id LIMIT 1000000, 1 )
3
ORDER BY id LIMIT 10;

效果接近延迟关联，但复杂过滤场景容易产生临时表，生产建议优先选 INNER JOIN 延迟关联。

4. 覆盖索引（加速器，常和上面组合）#

让查询所需字段都包含在索引里，二级索引直接出结果，完全不回表。是上面所有方案的共同加速手段。

不用动的（保持 OFFSET 即可）#

数据量天然小、后台必须支持跳页：

• @e:/go_project/caelor/server/service/user/user.go 用户列表
• @e:/go_project/caelor/server/service/apisvc/api.go API 列表
• @e:/go_project/caelor/server/service/dict/dict.go 字典
• 角色、菜单、部门、配置类

重点关注的（建议 Deferred Join 或 Keyset）#

会持续增长、容易堆到几十万到上千万行：

• @e:/go_project/caelor/server/service/logsvc/log.go —— sys_operation_log / sys_login_log，最危险，日志类天生大表，深翻页 OFFSET 必崩
• @e:/go_project/caelor/server/service/cmdb/host.go —— 如果接入 agent 心跳/监控历史会很大
• @e:/go_project/caelor/server/service/ai/ —— AI 对话消息流（按对话内分页消息），天然适合 Keyset（“加载更多历史消息”是单向的）

落地优先级建议#

P0（高优先级，性价比最高）：日志列表改为延迟关联

1
-- 当前（@logsvc/log.go:60）
2
SELECT * FROM sys_operation_log WHERE ... ORDER BY id DESC LIMIT 10 OFFSET 1000000;
3

4
-- 改成
5
SELECT t.* FROM sys_operation_log t
6
INNER JOIN (
7
  SELECT id FROM sys_operation_log
8
  WHERE ...                          -- 同样的过滤
9
  ORDER BY id DESC
10
  LIMIT 10 OFFSET 1000000
11
) k ON t.id = k.id
12
ORDER BY t.id DESC;

API 完全不变，只动 service 层 SQL。需要为常用过滤字段加联合索引（如 (username, id)、(status, id)）让子查询走覆盖索引。

P1：AI 消息历史改 Keyset，因为加载历史本来就是单向滚动：“WHERE conversation_id = ? AND id < :last_id ORDER BY id DESC LIMIT 20”，无需 COUNT(*)，性能拉满。

P2：通用基础设施层面，可在 @e:/go_project/caelor/server/model/request/base_request.go 之外新增 KeysetRequest{ LastID, PageSize }，作为可选模板（不替换 PageRequest），让新业务自行选用。

1. 翻页错位（cursor 真正解决的痛点）#

1
用户在第 1 页（最新 20 条）→ 此时来了 5 条新消息 →
2
用户滑动加载第 2 页（OFFSET 20 LIMIT 20）→
3
新插入的 5 条把原第 1 页末尾的 5 条挤到了第 21-25 位
4
→ 用户在第 2 页又看到了第 1 页末尾那 5 条（重复！）
5
→ 而真正的"第 21-25 条"被挤到 26-30，被跳过（漏读！）

这个问题对表格不致命（用户能容忍管理后台偶尔重复），对滚动信息流是灾难（IM 重复消息、订单列表丢单）。

cursor 用 WHERE id < last_id 锚定绝对位置，新数据插在哪都不影响”已加载过的内容”，天然免疫翻页错位。

2. 性能（次要，但叠加问题）#

亿级数据下 OFFSET 1000000 会把数据库打挂，cursor 是 O(log N) 索引 seek。