OFFSET 100000 触发全表扫描是因为MySQL优化器基于成本模型误判“跳过10万行再取数据”比索引回表更优；应改用WHERE+主键seek分页，如WHERE id > 上一页最大id，避免偏移量依赖。

为什么 OFFSET 100000 会触发全表扫描

MySQL 在执行 LIMIT offset, size 时，即使有索引，也必须先定位到第 offset + 1 行——它不会跳过前 N 行，而是逐行计数。当 OFFSET 达到 10 万级，优化器往往放弃使用索引的 range scan，转而走全表扫描（type: ALL）或索引全扫描（type: index），因为“读 10 万行再扔掉”比“用索引反复回表”更“划算”（实际更慢）。这不是 bug，是优化器基于成本模型的误判。

用 WHERE + 主键/唯一索引实现 seek 分页

核心思路：不依赖行号偏移，改用上一页最后一条记录的主键值作为下一页查询起点。这要求排序字段必须有唯一性约束（或组合唯一），否则可能漏数据或重复。

• 必须按 ORDER BY id ASC（或带唯一性的字段）排序，且 id 是索引列
• 第一页查：SELECT * FROM t ORDER BY id ASC LIMIT 20
• 第二页查（假设第一页最后 id 是 10523）：SELECT * FROM t WHERE id > 10523 ORDER BY id ASC LIMIT 20
• 不能写 WHERE id >= ...，否则会重复返回 10523 这条
• 如果排序字段不是主键（如 created_at），需确保该列+主键组合唯一，例如：WHERE created_at > '2025-01-01' OR (created_at = '2025-01-01' AND id > 10523)

如何安全地支持「任意页跳转」（非连续翻页）

seek 模式天然不支持直接跳转到第 500 页，但可通过「覆盖索引 + 子查询定位锚点」折中实现，代价是多一次索引扫描：

SELECT * FROM t 
WHERE id > (
  SELECT id FROM t 
  ORDER BY id 
  LIMIT 99999, 1
) 
ORDER BY id 
LIMIT 20;

注意：LIMIT 99999, 1 这个子查询仍会扫描 10 万行，但它只返回一个整数（id），不回表、不传输行数据，比外层全扫快得多。适用于低频跳转场景；高频随机页建议预生成页码映射或改用搜索引擎。

容易忽略的边界与陷阱

实际落地时这几个点常被绕过：

• 排序字段允许 NULL？MySQL 中 NULL 在 ORDER BY 里默认排最前，WHERE field > ? 会自动过滤掉 NULL，导致首页缺数据——统一设为 NOT NULL 或在查询中显式处理
• 并发写入导致新记录插入到已查区间？比如按时间分页时，新插入一条 created_at 在上一页范围内的记录，下一页就可能跳过它。这是 seek 模式的固有取舍：换来了性能，放弃了强一致性翻页
• 复合排序时没覆盖所有条件：例如 ORDER BY status,

  

  id，则 WHERE 条件必须写成 WHERE (status = 'active' AND id > 10523) OR status > 'active'，漏掉任一路径都会丢数据

真正难的不是写出第一条 seek 查询，而是把业务里所有分页入口、搜索结果页、后台导出逻辑全部对齐到同一套锚点规则，并守住排序字段的唯一性契约。