MySQL学习专题

MySQL 分页:用稳定排序比较 OFFSET 与游标

用 MySQL 8.4 合成数据比较 OFFSET 与二元游标返回的同一页,说明唯一排序、主键空洞和跨请求并发修改的边界。

简明结论

游标分页根据上一页最后的排序值继续查找,不能把行数偏移量直接当主键;排序值重复时应增加唯一键作为第二排序条件。

主键有删除空洞时,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,结果集合仍可能改变;需要先定义能否接受这种变化,再考虑不可变排序、快照或其他产品方案。不要为了展示一致性而在生产长期持有本例事务。

若使用“先查窄列主键、再关联取宽行”的延迟关联方式,也仍有扫描偏移成本,并应在外层明确排序;它与游标分页解决的问题并不完全相同。

保留历史原文出处;本站新增了带重复排序值的数据和结果一致性验证。没有进行并发写入实验,也没有用小表结果推断生产性能。

继续阅读联合索引和事务快照。