记一次千万级订单表分页查询优化：

记一次千万级订单表分页查询优化从 28 秒到 0.01 秒背景电商系统delivery_orders表存放配送订单累计数据量 1200 万行。运营后台有一个订单列表页支持按商家 ID 筛选、按下单时间倒序翻页。上线初期没什么问题随着数据量增长运营反馈翻到后面几页就卡死了。问题复现-- 运营翻到第 500 页时实际执行的 SQLSELECT*FROMdelivery_ordersWHEREseller_id1001ORDERBYcreated_atDESCLIMIT10000,10;执行时间28.3 秒。而翻第 1 页只需要 0.03 秒第 10 页 0.2 秒越往后越慢到第 1000 页直接超时。排查过程第一步EXPLAIN 看执行计划EXPLAINSELECT*FROMdelivery_ordersWHEREseller_id1001ORDERBYcreated_atDESCLIMIT10000,10;结果type: ref key: idx_seller_id rows: 487623 Extra: Using filesort索引是走了但rows 487623扫了将近 50 万行。更关键的是Using filesort说明排序没走索引。第二步理解 LIMIT offset 的本质很多人以为LIMIT 10000, 10是跳过前 10000 条取 10 条实际上 MySQL 的执行过程是扫描满足WHERE seller_id 1001的所有行按created_at DESC排序取出前 10010 行丢掉前 10000 行返回最后 10 行也就是说offset 越大做的无用功越多。翻到第 1000 页就要取出 100010 行再丢掉 100000 行。第三步确认索引设计问题SHOWINDEXFROMdelivery_orders;现有索引idx_seller_id(seller_id)idx_created_at(created_at)两个单列索引MySQL 只能用其中一个排序和筛选没法同时走索引所以出现了Using filesort。解决方案方案一建联合索引治本ALTERTABLEdelivery_ordersADDINDEXidx_seller_created(seller_id,created_at);联合索引让筛选和排序都走同一个索引消除 filesort。但 LIMIT offset 大的问题还没解决继续优化。方案二子查询定位 ID再回表取数据-- 优化后的 SQLSELECT*FROMdelivery_ordersWHEREidIN(SELECTidFROMdelivery_ordersWHEREseller_id1001ORDERBYcreated_atDESCLIMIT10000,10);子查询只查id走覆盖索引不需要回表速度极快外层再用id IN精确回表取完整数据只回表 10 次。执行时间0.8 秒。有改善但还不够。方案三游标分页彻底解决游标分页的思路是不用 offset而是记住上一页最后一条记录的游标值下一页从游标位置开始取。-- 第一页SELECTid,seller_id,created_at,statusFROMdelivery_ordersWHEREseller_id1001ORDERBYcreated_atDESCLIMIT10;-- 假设第一页最后一条的 created_at 2024-03-01 10:00:00id 98765-- 第二页用游标替代 offsetSELECTid,seller_id,created_at,statusFROMdelivery_ordersWHEREseller_id1001AND(created_at2024-03-01 10:00:00OR(created_at2024-03-01 10:00:00ANDid98765))ORDERBYcreated_atDESCLIMIT10;这样每次查询都只扫描真正需要的行不管翻到第几页执行时间都是固定的。配合idx_seller_created联合索引执行时间0.01 秒。最终方案落地-- 1. 建联合索引ALTERTABLEdelivery_ordersADDINDEXidx_seller_created(seller_id,created_at);-- 2. 后端接口改为游标分页接收参数-- seller_id, last_created_at, last_id, page_size-- 3. 对外仍然支持跳页需求的场景限制最大页数-- 超过 100 页引导用户缩小筛选条件前端改动将页码改为加载更多或下一页交互产品上线后运营反馈正常列表页无论翻多少页响应都在 50ms 以内。总结方案第500页耗时适用场景原始 LIMIT offset28.3s不适用子查询优化0.8s数据量不太大时可用游标分页0.01s推荐大数据量必选分页慢的根本原因不是索引是 LIMIT offset 的机制问题。大 offset 场景下无论索引多完善都会慢只有换掉分页方式才能真正解决。