SQL 中 LIMIT / OFFSET 的真实成本

LIMIT本身几乎不耗资源，真正昂贵的是OFFSET，它强制数据库逐行扫描并丢弃指定行数，导致I/O和CPU开销随OFFSET+LIMIT线性增长，优化应绕开OFFSET改用游标分页等方案。

sql 中 LIMIT 本身几乎不耗资源，真正昂贵的是 OFFSET——它强制数据库“数着跳过”指定行数，哪怕这些行最终被丢弃。

OFFSET 的执行过程就是重复无用扫描

以 select * FROM orders ORDER BY id LIMIT 20 OFFSET 100000 为例：

• mysql/postgresql 必须先按 id 排序（走索引或临时文件）
• 然后从排序结果中，逐行计数：第1行、第2行……直到第100000行
• 这100000行全部被读取、解包、校验，再全部丢弃
• 最后才取接下来的20行返回

相当于让数据库干了100020份活，只交出20份成果。偏移越大，浪费越严重。

性能衰减不是线性，而是阶梯式恶化

实测中常见现象：

• OFFSET 100：约 1ms，基本感知不到
• OFFSET 10000：升至 100ms+，已触发慢查询告警
• OFFSET 100000：常突破 1.5s，可能引发连接超时
• OFFSET 1000000：极易触发磁盘临时表、内存溢出或被主动 kill

根本原因在于：扫描行数 = OFFSET + LIMIT，而 I/O 和 CPU 开销与之强正相关。

为什么加索引有时也不管用？

即使 ORDER BY id 字段有主键索引，问题仍存在：

• 索引能加速排序和定位，但无法跳过“计数跳行”逻辑
• 若查询含 SELECT *，数据库仍需对跳过的每一行回表取完整数据（尤其在非覆盖场景）
• 当 WHERE 条件未命中索引最左前缀，排序可能退化为 filesort，OFFSET 成本雪上加霜

真正的优化方向是绕开 OFFSET

不靠“跳多少”，而靠“从哪开始”：

• 游标分页：用上一页末条记录的 id 或 created_at 值做条件，如 WHERE id > 123456 ORDER BY id LIMIT 20
• 延迟关联：子查询先精准捞出 ID 列表，外层再 JOIN 取全字段，减少回表量
• 覆盖索引：把 SELECT 所有字段都纳入联合索引，避免回表，压缩单次扫描代价
• 业务妥协：禁用任意页跳转，改用“下一页”按钮；总数显示模糊化（如“10万+”）；高频分页预生成快照