MySQL 分页越查越慢Limit Offset 优化方案汇总分页是开发中最常见的需求之一但大多数人在一开始都写过这样的代码SELECT * FROM orders ORDER BY id LIMIT 100000, 20;这条 SQL 在小数据量时没问题一旦偏移量变大性能就会急剧下降。原因很简单MySQL 需要跳过前面 100000 行才能读取后面的 20 行——这 100000 行全部被扫描并丢弃了。下面汇总 5 种经过验证的优化方案从简单到复杂覆盖不同场景。方案一子查询延迟关联最常用原理 先用覆盖索引快速定位起始 ID再关联回原表获取完整数据避免回表扫描大量无用行。-- 原始写法慢 SELECT * FROM orders ORDER BY id LIMIT 100000, 20; -- 优化后快 SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY id LIMIT 100000, 20 ) AS tmp ON o.id tmp.id;适用场景 任何基于自增主键排序的分页偏移量较大时效果显著。性能提升 偏移 10 万行时通常能快10~50 倍。方案二游标分页推荐用于无限滚动原理 记住上一页最后一条记录的 ID下一页直接用WHERE id last_id代替LIMIT OFFSET。-- 第一页 SELECT * FROM orders ORDER BY id LIMIT 20; -- 第二页传入上一页最后一个 id 1000 SELECT * FROM orders WHERE id 1000 ORDER BY id LIMIT 20; -- 第三页传入上一页最后一个 id 1020 SELECT * FROM orders WHERE id 1020 ORDER BY id LIMIT 20;优点无论翻多少页速度恒定不需要计算偏移量缺点只能实现“下一页”不能跳转到任意页码依赖连续递增的主键如果有删除操作会有空洞但不影响功能适用场景 移动端列表、Feed 流、评论加载等“无限滚动”场景。方案三利用 BETWEEN 或 跳过偏移适合已知主键范围原理 如果知道当前页的起始主键值直接用范围查询代替 LIMIT OFFSET。-- 假设每页 20 条第 5001 页的起始 id 是 100000 SELECT * FROM orders WHERE id 100000 AND id 100020 ORDER BY id;优点 极快只需扫描目标范围内的数据。缺点需要前端传回起始 ID或后端计算好主键不能有跳跃太大的空洞否则页数不准适用场景 后台管理系统的固定页码列表配合缓存记录每页起始 ID。方案四禁用 COUNT(*)改为估算总数原理 很多分页组件需要显示总页数而COUNT(*)在大表上非常慢。如果业务允许近似值可以用SHOW TABLE STATUS或EXPLAIN估算。-- 精确但慢全表扫描 SELECT COUNT(*) FROM orders WHERE status 1; -- 估算行数毫秒级 SHOW TABLE STATUS LIKE orders; -- 或者 EXPLAIN SELECT * FROM orders WHERE status 1;注意SHOW TABLE STATUS返回的是采样估算值误差可能在 30% 以内。EXPLAIN的rows字段也是估算值。适用场景 搜索列表、资讯列表等不需要精确总数的页面。方案五分区表 并行查询终极方案原理 将大表按时间或主键范围分区查询时只扫描相关分区甚至可以用多线程并行查询。-- 按月份分区 CREATE TABLE orders ( id BIGINT NOT NULL, created_at DATETIME NOT NULL, ... ) PARTITION BY RANGE (TO_DAYS(created_at)) ( PARTITION p202401 VALUES LESS THAN (TO_DAYS(2024-02-01)), PARTITION p202402 VALUES LESS THAN (TO_DAYS(2024-03-01)), PARTITION p202403 VALUES LESS THAN (TO_DAYS(2024-04-01)) ); -- 查询时自动只扫相关分区 SELECT * FROM orders WHERE created_at 2024-02-01 AND created_at 2024-03-01;适用场景 超大数据表千万级以上且有时间维度查询需求。方案对比速查表方案实现难度性能提升适用场景局限性子查询延迟关联⭐⭐高任意分页偏移量大时需要合适的索引游标分页⭐极高无限滚动、翻页按钮不能跳页BETWEEN 范围查询⭐极高固定页码、已知ID范围依赖主键连续性禁用 COUNT(*)⭐中等不需要精确总数失去精确分页信息分区表⭐⭐⭐⭐极高超大规模数据维护成本高实际选型建议场景一后台管理系统传统分页使用方案一子查询延迟关联如果数据量特别大百万级以上考虑方案四禁用 COUNT 或缓存总数场景二移动端/Web 无限滚动使用方案二游标分页配合last_seen_id参数传给前端场景三实时数据流如日志查询使用方案三BETWEEN 范围查询结合时间戳或自增 ID 做游标场景四超大规模数据千万级以上使用方案五分区表同时配合游标分页或子查询一句话总结别再用 LIMIT OFFSET 翻大页了。用游标代替偏移量用子查询延迟关联代替直接回表用估算代替精确 COUNT这三种技巧能解决 90% 的分页性能问题。
