企业官网建设流程全解析

商品列表翻页翻到OOM？分页调优全场景指南

去年双11压测的时候，我们压商品列表接口，压到QPS200的时候，商品库从库直接OOM挂了，查了半天发现是模拟用户翻页到第200页的时候触发的。开发写的分页SQL是标准的SELECT * FROM goods ORDER BY create_time DESC LIMIT 20000,20，压测的时候几百个并发翻深页，每个SQL都要回表扫2万行数据，内存里堆了上千万条记录，直接把从库内存撑爆了。我们当时紧急改了分页逻辑，压到QPS2000数据库CPU才20%，顺顺利利扛过了双11。分页可以说是所有业务系统用的最多的功能，从C端的信息流、商品列表，到后台的订单管理、数据导出，都要用到分页，但90%的开发者写分页只会写个LIMIT offset,size，根本没意识到深分页的性能风险，直到数据量涨到百万、千万级，翻页翻慢了、把库搞挂了才想着优化。今天我把所有分页场景的优化方案、踩坑经验全部分享给你，从C端信息流到后台管理系统，从MySQL到ES，所有场景的最优解都给你整理好了。

分页查询设计与深度性能调优实战‌

一、先搞懂：你的分页为什么越翻越慢

很多人觉得分页不就是LIMIT一下吗，能有多慢？但你肯定遇到过：列表第一页秒开，翻到第10页要等1秒，翻到第100页要等5秒，翻到几百页直接超时。要搞懂这个问题，你得先明白MySQL执行LIMIT offset,size的时候到底做了什么。

InnoDB的聚簇索引和二级索引结构我们之前讲过，当你执行SELECT * FROM goods ORDER BY create_time DESC LIMIT 20000,20的时候，MySQL并不是直接跳过20000条拿到后面的20条，它会先沿着create_time的二级索引扫描20020条记录，每一条都做回表操作，去聚簇索引把完整的行数据查出来，然后把前20000条记录全部扔掉，只返回最后20条。offset越大，MySQL需要扫描和回表的记录就越多，性能自然线性下降。如果你的SQL是SELECT *，需要回表所有扫描到的行，offset到10万的时候，要回表10万次，随机IO的开销会大到不可接受。

我在1000万行的商品表上做过测试，同样是查20条数据，不同offset下的性能差距非常夸张：

表格

offset偏移量 SQL执行时间 扫描行数 回表次数

0（第一页） 3毫秒 20 20

1000 12毫秒 1020 1020

10000 85毫秒 10020 10020

100000 920毫秒 100020 100020

1000000 8.7秒 1000020 1000020

你看，offset到100万的时候，执行时间从3毫秒涨到了8.7秒，性能差了近3000倍，要是有几个并发同时翻深页，数据库直接就被打满了，这就是为什么很多系统翻页翻深了就超时、甚至OOM的根本原因。更坑的是，大部分后台系统的分页都带多条件筛选、排序，索引用不好的话，扫描行数会更多，性能会更差。

二、分页最常踩的5个坑，每个都能搞出线上故障

我前前后后见过几十次分页相关的线上故障，90%都是下面这5个坑，很多坑甚至是资深开发也会踩的。

1、没有固定ORDER BY字段，分页数据跳页、重复

很多人写分页的时候不写ORDER BY，觉得MySQL默认会按主键顺序返回，省事儿。实际上InnoDB在有删除、插入、页分裂的时候，主键顺序并不一定是你预期的，尤其是并发写入的时候，两次查询之间有数据插入/删除，会导致同一页的数据重复出现，或者跳页：第一页看到的商品，翻到第二页又出现一次，或者有的商品直接消失了，运营和用户都会来找bug。

写分页SQL的第一铁则：‌必须有明确的、全局唯一的排序字段‌，哪怕你就是要按主键排序，也得显式写ORDER BY id，而且排序字段必须有索引，不然会触发全表文件排序，慢到离谱。

sql

-- 反例：无order by分页，可能重复/跳页

SELECT * FROM goods LIMIT 0,20;

-- 正确写法：显式按主键/唯一字段排序，保证顺序稳定

SELECT * FROM goods ORDER BY id DESC LIMIT 0,20;

2、ORDER BY字段不唯一，导致分页数据错乱

很多人写分页按create_time排序，觉得按创建时间排序很合理，但是create_time不是唯一字段，同一秒可能有几十个商品同时创建，排序的时候相同create_time的数据顺序是不确定的，并发插入数据的时候，翻页同样会出现数据重复、丢失的问题。

正确的做法是排序字段加上主键作为兜底排序键，比如ORDER BY create_time DESC, id DESC，这样哪怕create_time一样，也会按id二次排序，保证全局顺序唯一，不会出现翻页错乱的问题。

3、深分页+SELECT *，回表开销爆炸

就是我们开头讲的OOM事故的根源，SELECT 会查所有字段，导致二级索引扫描完必须回表查聚簇索引，offset越大回表越多，性能直线下降。如果只查索引上有的字段，走覆盖索引不需要回表，哪怕offset到10万也会快很多，但业务场景一般都需要查完整字段，这个时候就必须用其他分页方案替代大offset。

4、先查列表再count，两次查询都慢

几乎所有分页接口都会返回总条数total给前端做分页器，很多人的实现是先查一页列表数据，再跑一遍一样的WHERE条件做COUNT()算总条数，相当于同样的过滤条件扫了两遍表，列表本身就慢，count还慢，接口响应时间直接翻倍。而且很多人写count的时候也带ORDER BY，完全没必要，count根本不需要排序，白白浪费排序开销。

sql

-- 反例：count带order by，多余的排序开销

SELECT COUNT(*) FROM goods WHERE category_id = 1 ORDER BY create_time DESC;

-- 正确写法：count去掉order by，不需要排序

SELECT COUNT(*) FROM goods WHERE category_id = 1;

5、一次性查全量数据在内存里分页

还有人为了避免深分页问题，一次性把所有符合条件的数据ID全查出来，放到内存里，然后在代码里做分页，觉得这样数据库压力小。数据量小的时候没问题，如果符合条件的数据有几十万、上百万条，一次全查出来放到JVM内存里，分分钟把应用服务器的内存打满，引发Full GC甚至OOM，比数据库深分页还危险。

三、不同场景下的分页最优解，直接抄作业

分页没有万能方案，不同的业务场景要用不同的实现方式，我把所有常见场景的最优解法都整理好了，你直接对照自己的业务场景用就行。

1、C端信息流/上拉加载场景：游标分页（书签分页）

C端的商品列表、朋友圈、短视频信息流这类场景，用户只会一直往下滑加载更多，根本不需要跳转到指定页码，也不需要看总页数，这种场景最优解就是游标分页（也叫seek method、书签分页），性能比offset分页好几百倍，不管滑多少页都是毫秒级。

游标分页的逻辑非常简单：第一次查询第一页的时候，拿到本页最后一条记录的排序字段值（比如最后一条的id和create_time），下一页查询的时候，把这个值当游标传过来，WHERE条件过滤掉游标之前的所有数据，直接取后面20条，完全不需要offset，也就没有扫描大量数据的问题。

sql

-- 第一页查询，拿最后一条数据的create_time=1719800000, id=123456作为下一页游标

SELECT id, goods_name, price, create_time

FROM goods

WHERE category_id = 1

ORDER BY create_time DESC, id DESC

LIMIT 20;

-- 下一页查询，用上一页的游标过滤，不需要offset

SELECT id, goods_name, price, create_time

FROM goods

WHERE category_id = 1 AND (create_time < 上一页最后create_time OR (create_time = 上一页最后create_time AND id < 上一页最后id))

ORDER BY create_time DESC, id DESC

LIMIT 20;

这种写法能完美利用联合索引(category_id, create_time, id)，每次查询都是从索引的游标位置开始往后扫20条，不管翻多少页，扫描行数永远是20行，不需要回表多余的数据，哪怕翻到第一百万页，执行时间也和第一页一样是几毫秒，性能非常稳定，完全不会有深分页问题。抖音、淘宝的信息流全是用的这种分页方案。

游标分页的唯一缺点就是不支持跳转到指定页码，只能一页一页往后翻，刚好完美匹配C端上拉加载的场景，是C端分页的首选方案。

2、后台管理系统跳页场景：延迟关联分页

后台管理系统一般需要支持跳转到任意页码，显示总页数，没法用游标分页，这种场景的最优方案是延迟关联（也叫书签分页）：先通过覆盖索引查到当前页需要的主键ID，再通过主键ID关联查完整的行数据，把回表的范围从20000+20条缩小到20条，性能提升几十上百倍。

sql

-- 反例：深分页回表20020行，执行时间920毫秒

SELECT * FROM goods WHERE category_id = 1 ORDER BY create_time DESC LIMIT 20000,20;

-- 优化后：延迟关联，先查20个ID，再关联查详情，执行时间28毫秒

SELECT g.*

FROM goods g

INNER JOIN (

-- 子查询走覆盖索引，不需要回表，只查主键ID，哪怕offset2万也很快

SELECT id FROM goods

WHERE category_id = 1

ORDER BY create_time DESC, id DESC

LIMIT 20000, 20

) t ON g.id = t.id;

为什么能快这么多？因为子查询只查id，走联合索引(category_id, create_time, id)的覆盖索引，不需要回表，索引树本身很小，扫描20020个id非常快；拿到20个主键id之后，再去聚簇索引查完整行，只需要回表20次，随机IO的开销减少了1000倍，性能提升非常明显。我们压测的时候，offset到10万条，这种写法的执行时间也才100毫秒左右，完全能满足后台系统的需求。

如果需要算总条数，可以单独做count优化：如果筛选条件固定，可以把count结果缓存到Redis，或者维护到计数表里，不用每次都实时count，或者给MySQL加上并行查询参数，count的速度也能提升好几倍。

3、多条件复杂筛选场景：ES搜索引擎分页

如果是商品搜索、订单搜索这类支持多维度筛选、关键词搜索、任意维度排序的场景，MySQL本身就不擅长做这件事，不管你怎么优化分页都快不起来，直接把数据同步到Elasticsearch，用ES做分页就好。

ES有两种分页方案：深度翻页（from+size）适合100页以内的浅分页，和MySQL的offset逻辑类似；如果要支持很深的翻页或者上拉加载，用ES的search_after游标分页，原理和MySQL的游标分页一样，性能非常稳定，千万级数据分页都是毫秒级返回。我们当时把所有商品搜索、订单搜索的分页从MySQL移到ES之后，接口响应时间从平均300毫秒降到了20毫秒，还支持了全文检索、多维度筛选，体验好了很多。

4、超大数据量导出/遍历场景：游标分批遍历

如果是做数据导出、全量数据同步这类需要遍历整张表所有数据的场景，不要用offset分页，越往后越慢，还会产生长事务。最优方案是用主键游标分批查，每次从上一次查到的最大id开始，往后查1000条，直到查完为止，不管表有多大，每一批查询都是毫秒级，也不会有深分页问题。

java

// 全量遍历导出数据示例

long lastId = 0;

int batchSize = 1000;

while (true) {

// 每次查id大于lastId的1000条，走主键索引，永远只扫1000条

List list = goodsMapper.selectList(

"SELECT * FROM goods WHERE id > #{lastId} ORDER BY id LIMIT #{batchSize}",

lastId, batchSize

);

if (CollectionUtils.isEmpty(list)) break;

// 处理这批数据，写文件/同步到其他系统

exportData(list);

// 更新游标为当前批最后一条的id

lastId = list.get(list.size() - 1).getId();

}

这种方案我们用来导过上亿行的订单表，每批1000条，全程数据库CPU不超过20%，比offset分页快几十倍，也不会出现OOM、长事务的问题。

四、真实优化案例：商品列表从3秒到20毫秒全流程

当时双11压测出问题的商品列表接口，SQL就是最基础的深分页写法：

sql

-- 优化前SQL，offset=20000的时候执行时间3.2秒，并发高了就OOM

SELECT * FROM goods

WHERE category_id = 1 AND is_online = 1

ORDER BY create_time DESC

LIMIT 20000, 20;

我们按照场景优化：C端用户用游标分页，后台管理系统用延迟关联分页，同时建了对应的联合索引(is_online, category_id, create_time, id)覆盖子查询，C端接口直接改成游标分页，后台接口用延迟关联。

sql

-- C端优化后游标分页，执行时间5毫秒

SELECT id, goods_name, price, original_price, pic_url

FROM goods

WHERE is_online = 1 AND category_id = 1

AND (create_time < ? OR (create_time = ? AND id < ?))

ORDER BY create_time DESC, id DESC

LIMIT 20;

-- 后台优化后延迟关联分页，执行时间22毫秒

SELECT g.* FROM goods g

INNER JOIN (

SELECT id FROM goods

WHERE is_online = 1 AND category_id = 1

ORDER BY create_time DESC, id DESC

LIMIT 20000, 20

) t ON g.id = t.id;

同时我们把总条数count做了缓存，商品上下架的时候更新缓存里的总数，不用每次列表查询都count。改完之后压测，QPS从200打挂，升到2000的时候数据库CPU才20%，双11峰值的时候接口平均响应时间18毫秒，没有一次超时，也再没出现过OOM的问题。

五、分页设计的6条红线，执行了就不会出故障

这些年踩了无数分页的坑之后，我们组定了6条分页的强制规范，所有分页代码必须遵守，之后再也没出过分页相关的线上故障：

1、所有分页必须带ORDER BY，且排序字段必须加全局唯一的主键兜底，禁止无排序分页，避免数据重复、跳页。

2、C端信息流、上拉加载场景必须用游标分页，禁止用大offset分页，保证任意翻页性能稳定。

3、后台跳页场景用延迟关联分页，单页size不超过100条，禁止一次查上千条数据放到内存。

4、分页查询禁止SELECT *，列表页只查需要展示的字段，详情查单独接口，减少回表和网络传输开销。

5、多条件搜索、全模糊查询场景直接走ES，禁止在MySQL上做复杂搜索分页。

6、全量数据遍历、导出场景必须用主键游标分批查询，每批不超过1000条，禁止用深分页一次性拉取大量数据。

7、分页count尽量做缓存或者预计算，避免每次查询实时count大表，不需要显示精确总条数的场景可以不返回total，减少数据库开销。

很多人觉得分页是个特别简单的功能，不就是个LIMIT吗，没什么技术含量，实际上分页是最考验数据库优化功底的细节之一，用错了方案，在数据量和并发上来之后，会成为整个系统最大的性能瓶颈。分页优化的本质逻辑其实非常简单：永远不要扫描多余的数据，不要回表多余的行，让数据库每次只查需要返回的那几十条数据，自然就快了。不需要什么高深的技术，也不需要升级配置，把这些细节做到位，哪怕是千万级数据表的分页，也能做到毫秒级响应。

💡注意：本文所介绍的软件及功能均基于公开信息整理，仅供用户参考。在使用任何软件时，请务必遵守相关法律法规及软件使用协议。同时，本文不涉及任何商业推广或引流行为，仅为用户提供一个了解和使用该工具的渠道。

你在生活中时遇到了哪些问题？你是如何解决的？欢迎在评论区分享你的经验和心得！

希望这篇文章能够满足您的需求，如果您有任何修改意见或需要进一步的帮助，请随时告诉我！

感谢各位支持，可以关注我的个人主页，找到你所需要的宝贝。

博文入口：山峰哥-CSDN博客复制到【浏览器】打开即可,宝贝入口：常用软件宝贝：精品文件

作者郑重声明，本文内容为本人原创文章，纯净无利益纠葛，如有不妥之处，请及时联系修改或删除。诚邀各位读者秉持理性态度交流，共筑和谐讨论氛围～