第500页耗时12秒：深分页拖垮 MySQL，换游标分页降到8毫秒

类型：数据库类大小：136.4M语言：中文评分：10.0
标签：

立即下载

上周有个客户找过来，说后台的商品列表"翻到第几页就卡住了"。他们表里大概 1200 万条商品记录，页码用的是最常见的那种写法：LIMIT 49980, 20。前几十页都是秒开，翻到第 2500 页的时候，接口直接跑到了 12 秒，前端转圈把浏览器都拖死了。更麻烦的是，那个接口有几十个人同时在用，一旦有人翻到很后面，整台机器的负载就跟着抖起来。

如果你也在做带分页的业务，尤其是数据量上百万之后还在用 OFFSET，那么选一台 SSD 存储充足、带宽稳定的机器是第一步：StarryDNS（VPS 与独立服务器，含大存储 SSD 方案）。当然，光换机器不解决查询写法的问题，真正要动刀的地方在 SQL 那一行。

数据库深分页查询时磁盘扫描的开销

先搞清楚：OFFSET 到底做了什么

很多人以为 LIMIT 500000, 20 的意思是"跳到第 500000 行，取 20 行"。数据库实际做的不是跳转，而是老老实实把前面 50 万行读出来，然后扔掉。

MySQL 拿到这条语句后，会走索引定位到第一行，然后顺着索引（或聚簇索引）一条一条往下走，走到第 500000 条的时候才停下来开始收集要返回的 20 条。这中间 499980 条记录的读取、页的解析、行的还原，全都是实打实的 CPU 和 IO 消耗，但最后只留下 20 条给你。所以你会看到一个很典型的现象：返回的行数一样多，耗时却随着 offset 线性上涨。

我在客户那台机器上用 EXPLAIN 抓过现场执行计划，Extra 那一栏赫然写着 Using filesort，rows 估算值直接飙到了 50 万。分页越往后，扫描量越大，慢是必然的，不是数据库"变笨了"。

终端里执行深分页查询卡住的现场

三种解法，从最容易的开始

第一种：把 OFFSET 换成游标（keyset pagination）。这是根治方案，思路也很朴素既然"跳到第 N 行"要靠扫描，那就不跳，改成"从我上次看到的最后一条继续往后取"。

假设列表按 id 倒序排，原来要写：

SELECT * FROM products ORDER BY id DESC LIMIT 49980, 20;

改成游标写法，让前端把上一页最后一条的 id 带回来：

SELECT * FROM products WHERE id < 3984712 ORDER BY id DESC LIMIT 20;

因为 id 是主键，这一句可以直接在索引上定位，走的是范围扫描，扫多少行就取多少行，不存在任何浪费。我拿客户那 1200 万行的表实测，同样的第 2500 页，游标版本返回时间是 8 毫秒，OFFSET 版本是 12100 毫秒，差了一千五百倍。

代价当然有：没法直接跳到第 N 页，页码栏得改成"上一页/下一页"或者无限滚动，另外排序字段必须唯一并且能建索引。如果排序字段是 created_at 这种有重复值的，得凑成 WHERE (created_at, id) < (?, ?) 这种复合条件，不然数据会在翻页过程中重复或者漏掉。

第二种：延迟关联（deferred join）。如果业务上必须支持跳页，退而求其次的做法是让数据库先只在索引上取主键，再回表取整行。

SELECT p.* FROM products p JOIN ( SELECT id FROM products WHERE category_id = 7 ORDER BY id DESC LIMIT 49980, 20 ) t ON p.id = t.id;

子查询在覆盖索引里跑，只碰索引不回表，省掉大量随机 IO。实测这个表上延迟关联版本是 900 毫秒，虽然还是慢，但比 12 秒好了一个数量级。

第三种：给排序字段建联合索引。如果排序是 category_id = ? ORDER BY created_at DESC 这种组合，那索引必须是 (category_id, created_at)，单独给 created_at 建索引等于没建。MySQL 8.0 可以用降序索引，PG 也能直接写 CREATE INDEX ... (created_at DESC)，都能避免额外排序。

游标分页在索引上连续读取的路径示意

三个容易踩的细节

第一，count 也别用 LIMIT 撑。很多分页组件会额外查一个总数 SELECT COUNT(*) FROM products WHERE category_id = 7，1200 万行的表上这一句就要 1 秒多。真要显示总数，可以用估算值（EXPLAIN 的 rows 或 information_schema 的统计），或者干脆只显示"还有更多"这种模糊表述。

第二，别忘了加业务过滤条件。上面所有讨论都假设只有排序条件，如果还有 status = 1、tenant_id = ? 这类过滤，深分页的扫描量会成倍放大，因为要跳过的不再是连续的 50 万行，而是满足条件的行。组合索引里过滤列的位置同样很关键。

第三，深分页在读写分离架构下会不一致。从库延迟还没追上的时候，你在主库翻到第 100 页，再从库翻到第 100 页，数据对不上是必然的。做游标分页反而能缓解这个问题，因为它是"基于上次看到的点继续"，天然带着一个一致性锚点。

服务器机柜与分页偏移抽象示意

如果机器本身也不够快

还有一种情况是机器确实扛不住：数据量和查询量都上去了，光改 SQL 不够。这时候可以考虑把跑分页查询的这套 MySQL 单独拆到一台 存储强、内存大、带宽稳 的机器上，比如 HighEndNetwork（SSD 云服务器，含大流量与共享 2Gbps 端口方案）这类定位，把分页库和主业务库隔离，避免一个慢查询把整台机器的 IO 队列占满。

我的最终方案是：接口改成游标分页，前端去掉页码跳转，数据库加联合索引，把分页查询挪到一台 SSD 机器上。上线后那个卡了半年的接口，p99 从 9 秒降到了 40 毫秒，客户那边再没找过来过。

深分页不是什么玄学问题，它就是"用扫描换跳跃"的一个约定俗成的坏习惯。数据量小的时候无所谓，等到几百万行的时候，你付的每一毫秒都会以磁盘扫描的形式还回来。