【Java踩坑笔记】43_LIMIT大偏移量慢查询，你的分页写法过时了

43 | LIMIT 大偏移量慢查询你的分页写法过时了摘要LIMIT 100000, 10要先读 100000 行再丢弃即使有索引也很慢。延迟关联WHERE id lastId是更好的分页方案。一、问题现象-- ❌ 慢查询偏移量 100000SELECT*FROMordersORDERBYidLIMIT100000,10;-- 执行时间约 500ms100万行数据原因MySQL 必须读取前 100000 行然后丢弃只返回最后 10 行。二、踩坑现场场景前端分页用户跳到最后一页// ❌ 前端传 pageNum10000后端直接 LIMITpublicListOrdergetOrders(intpageNum,intpageSize){intoffset(pageNum-1)*pageSize;returnorderMapper.selectByPage(offset,pageSize);// SQL: LIMIT #{offset}, #{pageSize}}现象pageNum 越大查询越慢。三、原理解析3.1 LIMIT 偏移量的执行过程LIMIT 100000, 10 的执行 1. 按 ORDER BY 字段排序或用索引 2. 读取前 100000 行不返回给客户端 3. 读取接下来 10 行 4. 返回这 10 行问题步骤 2 即使有索引也要遍历100000 行。3.2 为什么索引帮不了偏移量索引可以加速WHERE过滤但LIMIT 100000, 10是跳过前 100000 行索引无法避免这个跳过动作。四、正确写法4.1 延迟关联推荐兼容深分页-- ✅ 先通过索引拿 ID再关联拿数据SELECTo.*FROMorders oINNERJOIN(SELECTidFROMordersORDERBYidLIMIT100000,10)AStmpONo.idtmp.id;原理子查询只读取id字段覆盖索引比SELECT *快很多。4.2 记住上一页最后一条的 ID最优但不支持跳页-- ✅ WHERE id lastId走主键索引O(1)SELECT*FROMordersWHEREid100000ORDERBYidLIMIT10;// ✅ 前端/App 翻页用这个方案publicListOrdergetNextPage(LonglastId,intpageSize){returnorderMapper.selectByIdGreaterThan(lastId,pageSize);}4.3 MyBatis 实现延迟关联!-- ✅ MyBatis 实现延迟关联 --selectidselectByPageOptimizedresultTypeOrderSELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE create_time #{startTime} ORDER BY id LIMIT #{offset}, #{pageSize} ) tmp ON o.id tmp.id/select五、最佳实践✅ 分页查询的 4 条规范不支持深分页产品层面限制最大 pageNum如最多 100 页能用WHERE id lastId就用它最快必须用偏移量时用延迟关联优化ORDER BY的字段必须有索引 EXPLAIN 验证EXPLAINSELECT*FROMordersORDERBYidLIMIT100000,10;-- 看 rows 列如果是 100010说明扫描了 10万行六、小结LIMIT 100000, 10要先读 10万行再丢弃即使有索引也慢最优方案WHERE id lastId LIMIT 10走主键索引兼容跳页用延迟关联INNER JOIN子查询只查 ID产品层面限制最大分页深度从根源解决问题下一篇预告数据库字段用保留字MyBatis 生成的 SQL 直接报错—— 用反引号包裹字段名但这个坑不止这一个。更多资料一线大厂Java面试题合集