17370845950

OFFSET越大查询越慢，因MySQL需扫描并跳过前N行；游标分页用排序字段值过滤替代偏移，要求字段唯一且有索引，避免错位、跳行，但不支持随机跳页。

为什么 OFFSET 越大，查询越慢

因为 MySQL 在执行 LIMIT 10000, 20 时，仍需扫描前 10000 行数据（即使不返回），再跳过它们，最后取 20 行。数据量大、OFFSET 高时，I/O 和 CPU 开销陡增，索引也未必能完全规避全扫描。

常见现象：SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 20 耗时从几毫秒飙升到数秒，甚至触发慢查询日志。

• 即使 id 有主键索引，MySQL 仍需“找到第 100001 条记录”，本质是线性偏移
• 如果 ORDER BY 字段存在重复值（如多个相同 created_at），OFFSET 结果还可能错位或漏数据
• 并发写入下，OFFSET 分页会出现“跳行”或“重复”——因为新插入的记录会改变原有行序

用游标分页替代 OFFSET（推荐用于高偏移场景）

游标分页基于上一页最后一条记录的排序字段值做条件过滤，避免物理偏移。它要求排序字段唯一且有索引（如主键 id 或带唯一约束的 created_at + id）。

例如：上一页最后一条记录 id = 12345，下一页查询写成：

SELECT * FROM orders WHERE id < 12345 ORDER BY id DESC LIMIT 20;

关键点：

• 必须用 或 >（取决于排序方向），不能用 != 或 NOT IN
• WHERE 条件和 ORDER BY 字段要一致，且该字段必须有高效索引
• 首次请求需明确起始值（如 WHERE id ），或用最大值兜底
• 不支持“跳转到第 N 页”，只支持“下一页/上一页”，但对 Feed 流、日志列表等场景更稳定

LIMIT 与 OFFSET 的合法语法边界

MySQL 中 LIMIT 支持两种写法：LIMIT row_count 或 LIMIT offset, row_count。注意：

• OFFSET 必须是非负整数，不能是变量表达式（如 LIMIT 20 OFFSET @off 中 @off 可为变量，但值必须 ≥ 0）
• 若 row_count 为 0，结果恒为空集，但语句仍合法（可用于占位或兼容逻辑）
• LIMIT 18446744073709551615 是 MySQL 中表示“无上限”的特殊大整数，慎用——它会强制全表扫描
• 在存储过程中使用动态 OFFSET 时，务必校验输入：防止 SQL 注入或负数传入导致语法错误

复合排序 + LIMIT 分页的陷阱

当 ORDER BY 包含多个字段（如 ORDER BY status ASC, updated_at DESC），仅靠一个字段做游标不可靠——因为 status 相同的记录，updated_at 可能重复，导致游标值不唯一。

正确做法是把排序字段全部纳入游标条件：

SELECT * FROM tasks 
WHERE (status, updated_at) < (1, '2024-05-20 10:30:00') 
ORDER BY status ASC, updated_at DESC 
LIMIT 20;

注意：

• 括号内元组比较要求 MySQL 8.0+，低版本需拆解为 status
• 所有参与排序的字段都必须有联合索引，否则无法高效走索引
• 时间字段建议用 DATETIME(3) 或更高精度，减少重复概率

游标分页不是银弹——它依赖排序字段的稳定性与唯一性。OFFSET 分页在页码靠前、数据静态或管理后台场景中依然可用，但只要用户能翻到几千页，就该警惕性能断崖。