主键有删除空洞时,id > 页码 * 每页条数 能替代 OFFSET 吗?不能。偏移量数的是行,主键数值不代表行的位置;非唯一排序列还需要稳定的补充排序。
准备独立实验库
在可丢弃的 MySQL 8.4 实例里首次运行以下建库脚本。不要在业务库中执行;其他文章已创建同名实验库时可以复用同结构数据,不要重复建库。
CREATE DATABASE phase2_knowledge;
USE phase2_knowledge;
CREATE TABLE employee (
id INT PRIMARY KEY,
tenant INT NOT NULL,
age INT NOT NULL,
name VARCHAR(40) NOT NULL,
notes VARCHAR(200) NOT NULL,
KEY idx_tenant_age_name (tenant, age, name),
KEY idx_age_id (age, id)
) ENGINE=InnoDB;
INSERT INTO employee
WITH RECURSIVE seq AS (
SELECT 1 AS n UNION ALL SELECT n+1 FROM seq WHERE n < 1000
)
SELECT n, MOD(n,10), MOD(n,80), CONCAT('user',n), REPEAT('x',120) FROM seq;
ANALYZE TABLE employee;
在同一快照里比较结果
USE phase2_knowledge;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT 'OFFSET_PAGE';
SELECT id,age FROM employee ORDER BY age,id LIMIT 3 OFFSET 10;
SELECT age,id INTO @last_age,@last_id FROM employee ORDER BY age,id LIMIT 1 OFFSET 9;
SELECT 'CURSOR_PAGE';
SELECT id,age FROM employee
WHERE age>@last_age OR (age=@last_age AND id>@last_id)
ORDER BY age,id LIMIT 3;
COMMIT;
在这组固定数据里,OFFSET_PAGE 和 CURSOR_PAGE 均返回:
880 0
960 0
1 1
演示为了取得对照游标,先读取偏移 9 的行;真实翻页应从上一页响应中取得 (last_age,last_id),不应每一页又用 OFFSET 查游标。下一页只返回不小于该排序位置之后的记录,并继续使用同样的 ORDER BY age,id。
适用条件与代价
游标适合顺序向后浏览,不天然支持任意跳到第几页。匹配过滤和排序的索引需要按真实查询设计;本例已有 (age,id),但没有测量深分页耗时,因此不宣称具体提速。
同一事务快照使本例两次查询可以严格对照。真实网站跨请求期间可能插入、删除或修改 age,结果集合仍可能改变;需要先定义能否接受这种变化,再考虑不可变排序、快照或其他产品方案。不要为了展示一致性而在生产长期持有本例事务。
若使用“先查窄列主键、再关联取宽行”的延迟关联方式,也仍有扫描偏移成本,并应在外层明确排序;它与游标分页解决的问题并不完全相同。
保留历史原文出处;本站新增了带重复排序值的数据和结果一致性验证。没有进行并发写入实验,也没有用小表结果推断生产性能。