本文摘要：百万行订单表上LIMIT 1000000, 20要丢弃前百万行，页码越深耗时越高。延迟分页用覆盖索引先取 id 再回表，游标分页改写id > @last_id消除偏移扫描。

一、问题与结论

MySQL 8.0.x + InnoDB 的订单库上，管理端跳到第 100 万条附近的一页时，接口生成：

SELECT*FROMt_ordersWHEREuser_id=100ANDstatus=1ORDERBYidLIMIT1000000,20;

即便命中idx_user_status_id，LIMIT offset, n仍要定位并丢弃前 1,000,000 行。SHOW PROFILE的时间分布里绝大部分落在Sending data，即百万行从存储引擎读出再丢弃，与网络或序列化无关。

offset 的语义决定数据库必须数过去。延迟分页降低被丢弃行的单行成本，游标分页直接不再数。标题里的 3 秒与 40ms 是量级示意，不是基准结论；绝对值受硬件、数据分布、缓冲池命中和并发影响，请自行计时。深分页在管理后台、报表导出和对账接口里很常见。

二、排查与选择依据

先定三件事：ORDER BY的列有没有可用索引、SELECT *是否真需要宽行、接口是否必须支持跳页。它们分别决定排序成本、回表成本和方案上限。判断看EXPLAIN FORMAT=JSON的访问方式与扫描行数估算，不要只看总耗时——缓冲池命中会让两种写法的差距在测试环境里消失。

丢弃行的真实代价

LIMIT offset, n要求引擎先读出前 offset 行再丢弃。InnoDB 中若SELECT *需要amount、created_at这类非索引列，被丢弃的每行都要从二级索引回表到聚簇索引；百万次回表是随机 I/O 的累积，这才是延迟分页真正省掉的部分。覆盖索引让丢弃行只在索引 B+ 树上顺序扫过，不回表。

替代方案与取舍

不该用延迟分页的场景：ORDER BY列无可用索引（filesort 不会因拆两步而消失）、写入远大于读取（预计算表写放大严重）、接口只有"加载更多"（游标更直接）。

三、关键原理

延迟关联把"跳过 offset"和"取整行"拆开：内层SELECT id在覆盖索引上顺序扫过被丢弃的行，外层只对 20 个id做主键查找。扫描行数没减少，变便宜的是每一行；行越宽、偏移越大，收益越明显。InnoDB 二级索引条目只含索引列与主键值，宽度远小于整行；被丢弃的百万行在索引里接近顺序读，回表是按主键的随机读，I/O 模式差别即延迟关联的收益来源。

游标分页改的是定位方式：id > @last_id触发 range 访问，扫描量恒为 20。代价是把"第几页"换成"从哪条继续"；排序键若不唯一或会被更新，>边界会漏行或重复。复合游标要写成created_at > @last_at OR (created_at = @last_at AND id > @last_id)，否则同一时间戳的行会被跳过。

四、可运行示例

环境：MySQL 8.0.x（8.4 行为一致），InnoDB，缓冲池建议 1G 以上。下面建表与造数可直接粘贴执行；INSERT ... SELECT跑两遍可得约 200 万行，其中约 62% 命中user_id = 100 AND status = 1，保证深分页有数据可返回。

DROPTABLEIFEXISTSt_orders;DROPTABLEIFEXISTSt_digit;CREATETABLEt_orders(idBIGINTUNSIGNEDNOTNULLAUTO_INCREMENT,user_idINTUNSIGNEDNOTNULL,statusTINYINTNOTNULLDEFAULT0,amountDECIMAL(10,2)NOTNULLDEFAULT0,created_atDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP,PRIMARYKEY(id),KEYidx_user_status_id(user_id,status,id))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;CREATETABLEt_digit(nTINYINTNOTNULLPRIMARYKEY)ENGINE=InnoDB;INSERTINTOt_digitVALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);-- 执行两遍，凑约 200 万行INSERTINTOt_orders(user_id,status,amount)SELECTCASEWHENr<0.62THEN100ELSE1+CAST(r*4999ASUNSIGNED)END,CASEWHENr<0.62THEN1ELSECAST(r*37ASUNSIGNED)%4END,ROUND(r*1000,2)FROM(SELECT((a.n+b.n*10+c.n*100+d.n*1000+e.n*10000+f.n*100000)*0.6180339887)%1ASrFROMt_digit a,t_digit b,t_digit c,t_digit d,t_digit e,t_digit f)t;ANALYZETABLEt_orders;

三种写法（@last_id取上一页最后一行的id）：

-- A 原始 LIMITEXPLAINFORMAT=JSONSELECT*FROMt_ordersWHEREuser_id=100ANDstatus=1ORDERBYidLIMIT1000000,20;-- B 延迟关联EXPLAINFORMAT=JSONSELECTo.*FROM(SELECTidFROMt_ordersWHEREuser_id=100ANDstatus=1ORDERBYidLIMIT1000000,20)tmpJOINt_orders oONo.id=tmp.id;-- C 游标SET@last_id=1000000;EXPLAINFORMAT=JSONSELECT*FROMt_ordersWHEREuser_id=100ANDstatus=1ANDid>@last_idORDERBYidLIMIT20;

预期输出：A 是索引扫描加百万行回表，rows_examined_per_scan随 offset 线性增长；B 的内层查询显示using_index: true，仅 20 行回表；C 是range访问，扫描量恒定在 20 行附近。以上为量级示意，未在本机实测。

实际输出：在mysql客户端用\timing或SHOW PROFILE把 A、B、C 各跑 5 次取中位数。若 B 与 A 差距不大，先看内层子查询是否using_index: true，再确认数据是否已被缓冲池全部缓存。

常见失败：内层子查询出现using_filesort。当ORDER BY id与索引(user_id, status, id)的前缀不匹配、且user_id、status未同时给等值条件时，索引无法提供 id 的有序性；补齐等值条件，或把索引改成(user_id, id)这类真正匹配排序键的组合。

五、验证结果与边界

边界一：游标会漏行。并发DELETE或把排序键换成会被更新的created_at后，> @last_id会跳过被删、改号的行。要不重不漏，改用不可变的单调游标（自增id或带序号的版本列）。

边界二：延迟关联的两步之间数据变更会让返回不足 20 行；放进同一事务能缓解，但快照持有时间变长。

边界三：ORDER BY列无索引时，子查询要 filesort 全部匹配行，延迟分页几乎不省时间；先建(created_at, id)这类复合索引。

边界四：游标只能顺序翻页。需要跳页时不要拼页码再反推游标，那等于把 OFFSET 换个写法再做一遍。

回滚方案：接口签名不用改。保留页码参数，页码小于阈值（如 1 万）时走 OFFSET，超过阈值走延迟关联，加载更多路径用游标；出问题可按页码区间灰度退回原始写法。回归监控看慢查询日志的Rows_examined与接口侧的分页深度分布，一旦某个页码区间回到百万量级，说明覆盖索引失效或语句被改回原始形式。

思考

1. 排序键在业务流程中会被更新时，是接受游标漏行换取稳定翻页，还是改用带单调序号的事件游标？
2. 跳页需求不可取消时，页码映射表按写入条数重建与按时间增量刷新，哪种失效边界更容易被业务接受？

参考资料

• MySQL 8.0 参考手册 · SELECT Statement
• MySQL 8.0 参考手册 · EXPLAIN Statement
• MySQL 8.0 参考手册 · EXPLAIN Output Format
• MySQL 8.0 参考手册 · Optimization and Indexes
• MySQL 8.0 参考手册 · Consistent Nonlocking Reads