本文摘要百万行订单表上LIMIT 1000000, 20要丢弃前百万行页码越深耗时越高。延迟分页用覆盖索引先取 id 再回表游标分页改写id last_id消除偏移扫描。一、问题与结论MySQL 8.0.x InnoDB 的订单库上管理端跳到第 100 万条附近的一页时接口生成SELECT*FROMt_ordersWHEREuser_id100ANDstatus1ORDERBYidLIMIT1000000,20;即便命中idx_user_status_idLIMIT offset, n仍要定位并丢弃前 1,000,000 行。SHOW PROFILE的时间分布里绝大部分落在Sending data即百万行从存储引擎读出再丢弃与网络或序列化无关。offset 的语义决定数据库必须数过去。延迟分页降低被丢弃行的单行成本游标分页直接不再数。标题里的 3 秒与 40ms 是量级示意不是基准结论绝对值受硬件、数据分布、缓冲池命中和并发影响请自行计时。深分页在管理后台、报表导出和对账接口里很常见。二、排查与选择依据先定三件事ORDER BY的列有没有可用索引、SELECT *是否真需要宽行、接口是否必须支持跳页。它们分别决定排序成本、回表成本和方案上限。判断看EXPLAIN FORMATJSON的访问方式与扫描行数估算不要只看总耗时——缓冲池命中会让两种写法的差距在测试环境里消失。丢弃行的真实代价LIMIT offset, n要求引擎先读出前 offset 行再丢弃。InnoDB 中若SELECT *需要amount、created_at这类非索引列被丢弃的每行都要从二级索引回表到聚簇索引百万次回表是随机 I/O 的累积这才是延迟分页真正省掉的部分。覆盖索引让丢弃行只在索引 B 树上顺序扫过不回表。替代方案与取舍方案选择条件代价边界延迟关联内层SELECT id外层 join 回表必须跳页ORDER BY列能被覆盖索引包含两步之间可能不一致内层仍扫 offset 行ORDER BY无索引时 filesort 不消失游标keysetid last_id只需上一页、下一页或加载更多前端保存游标不能跳页排序键被更新或行被删除时会漏行预计算位置表row_num映射跳页是硬需求读远多于写每次写入维护映射写放大数据变更后要重建或增量刷新窗口函数ROW_NUMBER()排序复杂且总行数不大仍需为全量行计算行号大偏移时不省时间仅 8.0不该用延迟分页的场景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.x8.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))ENGINEInnoDBDEFAULTCHARSETutf8mb4;CREATETABLEt_digit(nTINYINTNOTNULLPRIMARYKEY)ENGINEInnoDB;INSERTINTOt_digitVALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);-- 执行两遍凑约 200 万行INSERTINTOt_orders(user_id,status,amount)SELECTCASEWHENr0.62THEN100ELSE1CAST(r*4999ASUNSIGNED)END,CASEWHENr0.62THEN1ELSECAST(r*37ASUNSIGNED)%4END,ROUND(r*1000,2)FROM(SELECT((a.nb.n*10c.n*100d.n*1000e.n*10000f.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 原始 LIMITEXPLAINFORMATJSONSELECT*FROMt_ordersWHEREuser_id100ANDstatus1ORDERBYidLIMIT1000000,20;-- B 延迟关联EXPLAINFORMATJSONSELECTo.*FROM(SELECTidFROMt_ordersWHEREuser_id100ANDstatus1ORDERBYidLIMIT1000000,20)tmpJOINt_orders oONo.idtmp.id;-- C 游标SETlast_id1000000;EXPLAINFORMATJSONSELECT*FROMt_ordersWHEREuser_id100ANDstatus1ANDidlast_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与接口侧的分页深度分布一旦某个页码区间回到百万量级说明覆盖索引失效或语句被改回原始形式。思考排序键在业务流程中会被更新时是接受游标漏行换取稳定翻页还是改用带单调序号的事件游标跳页需求不可取消时页码映射表按写入条数重建与按时间增量刷新哪种失效边界更容易被业务接受参考资料MySQL 8.0 参考手册 · SELECT StatementMySQL 8.0 参考手册 · EXPLAIN StatementMySQL 8.0 参考手册 · EXPLAIN Output FormatMySQL 8.0 参考手册 · Optimization and IndexesMySQL 8.0 参考手册 · Consistent Nonlocking Reads