本文摘要深分页 offset 达九十万时单页常耗数秒页码越深越慢。延迟关联先在索引挑出 id再回表 20 行回表次数从 offsetN 降到 N。一、问题与结论orders表约 100 万行SELECT * FROM orders ORDER BY created_at DESC LIMIT 900000, 20走的是idx_created (created_at)offset90 万与offset1 万的响应差着量级量级判断需按第四节命令实测。慢的不是缺索引MySQL 为了取最后 20 行必须先把前 90 万行逐条回表取完整列再把它们全部丢弃。结论先行改写后回表从offset20次降到 20 次代价是 SQL 多一层派生表且被跳过的索引条目仍要顺序读offset到千万级时收益明显缩水。业务只要允许顺序翻页游标分页才是与页码无关的解法。二、排查与选择依据判断一条深分页值不值得改写看EXPLAIN的type、rows、Extra三处Extra出现Using index只读二级索引 B 树零回表SELECT *不出现它说明每行都要回表取非索引列。Extra出现Using filesortORDER BY列没走上索引排序延迟关联的收益基本消失。外层typeeq_ref且rows20回表只发生在最终 20 行说明改写生效。派生表rows估算仍接近全表符合预期offset扫描没有消失。索引选择上InnoDB 二级索引记录是(索引列, 主键)idx_created已能覆盖SELECT id不需要为延迟关联额外建索引。若列表只查created_at, user_id, status, amount这类少量列直接建INDEX(created_at, user_id, status, amount)走覆盖索引更简单但索引变宽会放大写入与页分裂成本remark这类大字段不能进索引。另有两个常见前置判断COUNT(*)需要扫描整棵索引树深分页接口若每页都带总数统计成本往往高于翻页本身可缓存总数、只在第一页统计或用“是否还有下一页”替代业务若只需上一页/下一页优先游标分页。替代方案与取舍方案选择条件代价边界延迟关联必须跳页offset在十万到百万级仍要顺序扫offset条索引SQL 多一层派生表offset超千万收益有限ORDER BY无索引时无效游标分页只需顺序翻页排序键唯一或有复合游标不能跳页要保存上一页末尾游标值created_at有重复时须用(created_at, id)复合游标否则漏行覆盖索引SELECT 列少且固定索引宽、写放大列多或含大字段时不可行ORDER BY列无可用索引、offset长期在千万级、SQL 带GROUP BY或聚合时改写只增加复杂度不该用延迟关联。三、关键原理InnoDB 聚簇索引叶子节点是完整行二级索引叶子是(索引列, 主键)。SELECT *在二级索引上拿到主键后必须回到聚簇索引取其余列这一次随机查找就是回表深分页里前offset条各回表一次后被丢弃成本是O(offsetN)次回表。延迟关联的子查询SELECT id FROM orders ORDER BY created_at DESC LIMIT 900000, 20只取idid已在idx_created中扫描全程Using index回表降到O(20)次。但 LIMIT 只能“读到offsetN就停”被跳过的索引条目仍要顺序读成本从随机回表为主变成顺序扫描为主O(offset)并未消失。MySQL 8.0 的derived_merge不会合并含LIMIT的派生表改写不会被优化器拆掉仍建议用EXPLAIN确认实际计划。四、可运行示例环境MySQL 8.0.x、InnoDB、默认配置。用digits表交叉连接生成 100 万行created_at每 3 行共用同一秒便于验证游标去重。DROPTABLEIFEXISTSdigits;CREATETABLEdigits(dTINYINTPRIMARYKEY)ENGINEInnoDB;INSERTINTOdigitsVALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);DROPTABLEIFEXISTSorders;CREATETABLEorders(idBIGINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,user_idINTUNSIGNEDNOTNULL,statusTINYINTNOTNULL,amountDECIMAL(10,2)NOTNULL,created_atDATETIMENOTNULL,remarkVARCHAR(200),INDEXidx_created(created_at))ENGINEInnoDB;INSERTINTOorders(user_id,status,amount,created_at,remark)SELECTseq%100000,seq%5,(seq%10000)/100,DATE_ADD(2024-01-01 00:00:00,INTERVAL(seqDIV3)SECOND),CONCAT(r-,seq)FROM(SELECTa.db.d*10c.d*100e.d*1000f.d*10000g.d*100000ASseqFROMdigits a,digits b,digits c,digits e,digits f,digits g)t;ANALYZETABLEorders;方式 A直接深分页。EXPLAINSELECT*FROMordersORDERBYcreated_atDESCLIMIT900000,20;预期输出关键列typeindex keyidx_created rows≈1000000 Extra: 不出现 Using index实际输出执行后比对type、key、rows、Extra四项Extra没有Using index就意味着每行都回表。方式 B延迟关联。EXPLAINSELECTt.*FROMorders tJOIN(SELECTidFROMordersORDERBYcreated_atDESCLIMIT900000,20)tmpONt.idtmp.id;预期输出derived2 typeindex keyidx_created rows≈1000000 Extra: Using index orders t typeeq_ref keyPRIMARY rows20 Extra: 无 Using filesort实际输出派生表一侧出现Using index、外层rows20即改写生效8.0.18 起可用EXPLAIN ANALYZE看到实际循环次数与耗时。计时对比timemysql-uroot-p-Dtest-eSELECT * FROM orders ORDER BY created_at DESC LIMIT 900000, 20timemysql-uroot-p-Dtest-eSELECT t.* FROM orders t JOIN (SELECT id FROM orders ORDER BY created_at DESC LIMIT 900000, 20) tmp ON t.id tmp.id预期输出两条命令real时间的差值即收益常见量级是延迟关联进入两位数毫秒、原写法为秒级量级预期未在本文环境中实测。实际输出同一机器、同一数据各跑 5 次取中位数避免缓冲池冷热差异。游标分页顺序翻页已知上一页末行(2024-01-10 08:00:00, 500000)SELECTid,user_id,status,amount,created_atFROMordersWHERE(created_at,id)(2024-01-10 08:00:00,500000)ORDERBYcreated_atDESC,idDESCLIMIT20;失败处理ORDER BY列无索引时延迟关联反而更慢。DROPINDEXidx_createdONorders;EXPLAINSELECTt.*FROMorders tJOIN(SELECTidFROMordersORDERBYcreated_atDESCLIMIT900000,20)tmpONt.idtmp.id;预期输出派生表typeALLExtra: Using filesort; Using temporary。原因子查询无法用索引完成排序全表扫描加 filesort 的成本远大于省下的回表多一层 JOIN 只是额外开销。修复补回idx_created或按上文改用游标分页若游标查询出现Using filesort显式补(created_at, id)复合索引。五、验证结果与边界读数方式EXPLAIN只给估算收益要用同一数据下两条计时命令的差值衡量EXPLAIN ANALYZE输出实际循环次数可直接看到派生表扫过多少索引条目、外层回表多少次。上文耗时为量级预期未在本文环境中实测。代价与边界子查询仍要顺序读offset条索引条目offset千万级时通常只能从“秒级”降到“亚秒级”。每页统计COUNT(*)时统计成本常高于分页本身用缓存总数、只统计第一页或改“是否还有下一页”。ORDER BY无索引、SQL 带聚合或GROUP BY、列表要查大字段时延迟关联不适用。offset长期超千万且必须跳页时预计算页码映射或搜索系统的search_after更合适。思考列表页返回的总条数是牺牲统计实时性换取体验还是收敛成“是否还有下一页”产品是否真需要任意跳页还是能把交互收敛为顺序翻页换得与页码无关的响应参考资料MySQL 8.0 Reference Manual — EXPLAIN Output FormatMySQL 8.0 Reference Manual — LIMIT OptimizationMySQL 8.0 Reference Manual — InnoDB Clustered and Secondary IndexesMySQL 8.0 Reference Manual — Optimizer Hints