做后端开发的同学，大概率都遇到过MySQL分页的坑——浅分页时查询秒回，一旦翻到几十页、上百页之后，接口就开始卡顿，甚至直接超时。之前在做电商商品列表接口时，就因为没处理好深度分页，线上出现过多次接口超时告警，排查后才发现是分页SQL的问题。今天就结合实际开发经验，聊聊MySQL深度分页的本质问题，以及那些能直接落地的优化方案，都是经过生产环境验证的干货。
先说说最开始踩的坑。当时商品列表接口用的是最常规的分页写法，也就是LIMIT offset, size，代码里封装了通用分页逻辑，前端传页码和每页条数，后端计算offset后拼SQL。上线初期数据量少，翻个十几页都没问题，可随着商品数据涨到几十万条，用户翻到第500页之后，接口响应时间直接从几十毫秒飙升到几百甚至上千毫秒，监控面板上全是红色告警。
一、深度分页为什么会慢？
一开始以为是索引没建对，排查后发现索引是正常的，后来翻了MySQL官方文档，再结合执行计划分析，才搞懂了核心原因。我们常用的LIMIT offset, size写法，比如LIMIT 100000, 20，MySQL并不是直接跳过前10万条数据取后面20条，而是会从表的第一条数据开始，逐行扫描，一共读取100020条数据，然后丢弃前10万条，只返回最后20条。
这就意味着，offset越大，MySQL需要扫描的行数就越多，磁盘IO和内存消耗都会急剧增加，查询效率自然呈指数级下降。如果分页SQL还没命中索引，触发全表扫描，那情况会更糟，直接导致整个数据库实例压力飙升，影响其他接口。
二、实战优化方案：从易到难落地
针对深度分页问题，没有万能方案，只能结合业务场景选择最合适的。下面按优先级排序，分享几个实际项目中用过的优化方案，从改造成本低到高逐步说明。
方案一：书签分页（最优解，90%场景适用）
这是我目前在项目中用得最多的方案，改造成本低，性能提升明显，核心思路是放弃offset偏移，用上一页最后一条数据的主键或唯一索引作为“书签”，让MySQL直接通过索引定位到书签位置，再往后查询指定条数，彻底避免扫描无用数据。
比如之前的商品列表，用主键id排序，原来的低效写法是：
-- 第5001页，每页20条，offset=100000 SELECT id, name, price, category FROM goods WHERE category=1 ORDER BY id LIMIT 100000, 20;优化后，让前端传递上一页最后一条数据的id，比如上一页最后一条id是100000，新的SQL写法是：
SELECT id, name, price, category FROM goods WHERE category=1 AND id > 100000 ORDER BY id LIMIT 20;这样MySQL会直接通过主键索引定位到id=100000的位置，再往后取20条数据，只扫描20条记录，不管数据量多大，查询速度都能稳定在毫秒级。
这里有个需要注意的点：如果排序字段不是主键，而是普通字段（比如create_time），且存在重复值，直接用该字段作为书签会导致分页重复或漏数据。这时需要用“排序字段+主键”的组合作为锚点，保证唯一性。
比如按创建时间倒序分页，优化写法如下：
-- 上一页最后一条数据：create_time='2026-01-15 18:30:00'，id=100000 SELECT id, name, price, create_time FROM goods WHERE category=1 AND (create_time < '2026-01-15 18:30:00' OR (create_time = '2026-01-15 18:30:00' AND id < 100000)) ORDER BY create_time DESC, id DESC LIMIT 20;这种组合锚点的方式，能完美解决排序字段重复导致的分页异常问题，也是生产环境中处理非主键排序分页的标准写法。
方案二：子查询/JOIN优化（兼容跳页需求）
书签分页的缺点是不支持直接跳转到指定页码，而很多业务场景（比如后台管理系统的分页组件）必须有页码选择功能，这时就需要用子查询或JOIN来优化。
核心思路是：先通过索引查询出需要的主键ID，再通过主键关联查询全字段数据。因为主键是聚簇索引，查询主键的速度极快，子查询只扫描主键字段的offset+size条数据，而不是全字段，能大幅降低IO开销。
原来的低效写法优化前：
SELECT * FROM goods WHERE category=1 ORDER BY id LIMIT 100000, 20;用JOIN优化后的写法（性能更优，MySQL推荐）：
SELECT g.* FROM goods g JOIN (SELECT id FROM goods WHERE category=1 ORDER BY id LIMIT 100000, 20) t ON g.id = t.id ORDER BY g.id;这种方案能完美兼容跳页需求，不需要改造前端分页组件，性能比原生LIMIT提升10~100倍，offset越大，优化效果越明显。需要注意的是，子查询中的排序字段必须建立索引，否则子查询依然会全表扫描，优化失效。
方案三：业务层限制（最简单的兜底方案）
其实很多ToC业务场景中，用户根本不会翻到第100页之后。比如电商商品列表，用户通常只看前10页，翻到后面的概率极低。针对这种场景，最简单的优化方式就是在业务层限制最大页码。
我们当时在商品列表接口中做了限制：最多只能翻到第50页，超过50页就提示“暂无更多数据”，同时引导用户通过筛选条件（比如价格区间、销量排序）缩小查询范围。这种方式零开发成本，零性能损耗，直接从根源解决问题，适合大多数ToC业务。
方案四：特殊场景兜底（海量数据/批量导出）
如果遇到千万级数据的深度分页，或者需要批量导出海量数据的场景，上面的方案可能不够用，这时可以考虑预生成分段ID或使用游标分页。
预生成分段ID的思路是：在数据表中新增segment_id字段，按主键分段（比如每1000条数据为一个分段），建立segment_id索引。分页时先按segment_id定位分段，再在分段内分页，避免大范围扫描。这种方式适合数据更新频率低的场景，性能极致，但需要预处理数据。
游标分页则适合批量数据导出、离线任务等不需要跳页的场景，通过MySQL游标逐行读取数据，避免一次性加载大量数据到内存，不会有offset的性能问题，但业务改造成本较高，只适合后端离线任务。
三、优化必守原则（缺一不可）
不管用哪种优化方案，以下两个原则必须遵守，否则所有优化都会失效：
1. 分页SQL必须命中索引：WHERE筛选条件+ORDER BY排序条件，必须建立对应的单列索引或联合索引，否则MySQL会触发全表扫描+文件排序，性能依然极差。比如上面的商品列表，需要建立category+id的联合索引，才能让分页SQL高效执行。
2. 避免使用SELECT *：只查询需要的字段，减少数据传输和内存开销。如果表中有text、blob等大字段，SELECT *会导致性能严重损耗，甚至拖慢整个数据库。
四、总结
MySQL深度分页的核心矛盾，本质是offset导致的无效扫描。优化的核心思路就是：能不用offset就不用，优先用书签分页；必须用offset就减少扫描数据量，用子查询/JOIN优化；业务上能限制分页深度就限制，从根源规避问题。
在实际开发中，不需要追求最复杂的方案，而是要结合业务场景选择最合适的。大部分场景下，书签分页+索引优化就能满足需求，改造成本低，性能又稳定。希望这篇实战总结能帮到大家，避开MySQL深度分页的坑。
