经常有朋友问我“我这张表才几百万行怎么分页翻到后面就卡成狗”其实答案并不复杂——MySQL 的深分页代价是被LIMIT的OFFSET机制吃掉的越往后翻数据库要丢掉的行就越多查询自然越来越慢。这篇文章是 MySQL 学习系列的第十二篇专门拆解分页性能优化这件事。文中我会从LIMIT的执行原理讲起用具体的 SQL 和 EXPLAIN 分析带你理解“深分页为什么慢”然后给出覆盖索引、延迟关联、游标分页、范围分页等一套可以直接抄作业的优化方案最后整理我在实际业务中踩过的坑和排查技巧。这套内容适合正在做后端开发、维护线上 MySQL 的同学也适合准备面试时想把这部分讲透的人。1. 分页为什么会越翻越慢LIMIT 的底层执行逻辑1.1 一条深分页 SQL 到底做了什么很多人对分页慢的认知停留在“数据多了所以慢”但真正的原因比这具体得多。先看一条最常见的分页语句SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 20;这条 SQL 的意图很清楚把订单表按创建时间倒序排好跳过前 100 万条返回第 1000001 到 1000020 条。但问题是MySQL 在执行这条语句时并没有“直接从第 1000001 条开始取”的能力它的真实执行步骤是从orders表里找到所有满足WHERE条件的行按照ORDER BY create_time DESC做一次完整排序如果数据量大排序甚至要落磁盘临时文件排序结束后从第一条开始数数到第 1000000 条时停下来丢掉前面这 100 万条记录只把第 1000001 到 1000020 条返回给客户端。也就是说你每翻一页数据库都要把前 N 页的数据完整地查询、排序、然后丢弃。OFFSET越大白白丢弃的行就越多耗时自然呈线性甚至超线性增长。我用一个生活化的类比帮你理解想象你在图书馆要找一本排在书架第 100 万本位置的书但图书馆的管理系统没有“按位置直接索引”的能力它只能从第一本开始一本一本数过去数完前 99 万本之后才能拿给你。如果你要找第 200 万本它就要从头再数一遍而不是记住上次数到哪儿了。数据库的LIMIT就是这样一位“每次从头数”的图书管理员。1.2 用 EXPLAIN 看清慢在哪一步下面这条 SQL 是我在一张约 500 万行的订单表上实际跑的EXPLAIN SELECT * FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 200000, 20;执行计划的关键信息如下列值解读typeALL全表扫描没有走任何索引keyNULL没有可用的索引rows4975120预估扫描接近全表所有行ExtraUsing where; Using filesort先过滤再排序排序无法用索引这里的Using filesort是最刺眼的一个标记。它表示排序操作无法利用索引的有序性MySQL 需要额外分配排序缓冲区甚至把中间结果写到磁盘临时文件里。数据量一大这个代价会迅速膨胀。哪怕我把status字段单独加上索引让type变成ref、ref变成走索引过滤只要ORDER BY create_time这个排序还是不能走索引Using filesort就依然存在深分页的性能问题依旧没有解决。1.3 用一个量化实验证明“翻得越深越慢”我在本地用 500 万行测试数据做了一个简单的压测表结构和线上订单表基本一致查询条件也相同只有LIMIT的偏移量不同。记录单次查询耗时如下OFFSET耗时约扫描行数00.05 秒20100000.28 秒100201000001.52 秒1000205000006.83 秒500020100000012.96 秒1000020数据非常直观OFFSET 每扩大一个数量级耗时也差不多扩大一个数量级。这还是在有索引的情况下测的如果把排序和过滤都做成全表扫描耗时会更难看。所以任何分页优化方案核心都是围绕“如何减少数据库实际扫描并丢弃的行数”来做文章。2. 优化前先搭好三层思路从业务到 SQL 再到架构2.1 先问业务用户真的需要翻到第 100 页之后吗很多人一上来就扎进 SQL 优化但我建议你先做一件更省力的事——确认深分页这个需求本身是否合理。我见过太多业务方在页面底部放一个“跳到第 500 页”的输入框产品经理解释说“用户可以用它快速定位数据”但实际上这个功能上线一年使用率几乎为零。从业务角度你可以考虑三种替代策略限制最大翻页数比如只允许翻前 100 页超过后提示用户使用搜索或筛选条件缩小范围用“加载更多”按钮代替页码分页每次基于上次返回的最后一条记录往后取而不是基于偏移量把“翻页”改成“滚动加载”这种模式天然适合游标分页不需要支持随机跳页。在我经手的项目里80% 以上的深分页慢问题都能通过业务层限制解决而且是一行代码都不用改就能让用户感知到“系统变快了”。这不是逃避问题而是避免用数据库的昂贵操作去服务一个伪需求。2.2 SQL 层优化让排序和回表尽可能退场确认了业务确实需要分页后再动手调 SQL。这一层有三条主线第一条是让ORDER BY走索引。如果排序字段本身是索引的一部分MySQL 可以直接按索引顺序读取数据省掉Using filesort。比如对(status, create_time)建联合索引WHERE status 1 ORDER BY create_time DESC就能通过索引既完成过滤又完成排序。第二条是减少回表次数。SELECT *会把每一行都回表取全字段如果这部分数据量很大I/O 代价就很高。优化办法是用“覆盖索引”先查出主键 ID再用主键去关联原表取完整数据也就是常说的延迟关联。第三条是避免不必要的全表扫描。查询条件里如果有范围条件、函数操作、隐式类型转换都可能导致索引失效。优化前先看执行计划确认过滤是用上了索引的。2.3 架构层优化缓存、汇总表和搜索引擎什么时候上如果业务不能限制深分页SQL 层该做的也都做了但仍然扛不住那就需要考虑架构层面的手段把高频访问的前几页数据缓存到 Redis 或其他内存存储里注意缓存粒度要细化到“页 查询条件”而不是盲目缓存全表对统计类、流水类非实时数据提前建汇总表用定时任务把数据加工好查询时直接读汇总结果数据量到了一定级别后把全文检索、复杂多维筛选交给 Elasticsearch 这类搜索引擎MySQL 只负责存储原始数据。架构层方案的成本通常比 SQL 层高一到两个数量级涉及到额外组件、数据一致性、运维复杂度。所以我的建议是能 SQL 解决就不上架构别为了“显得专业”而引入不必要的中间件。3. 一次完整的索引与分页改造实录3.1 模拟一个真实业务场景假设我们有一个订单查询页面运营人员需要按“订单创建时间倒序”查看某个状态下的订单列表数据量约 200 万行。表结构如下CREATE TABLE orders ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(64) NOT NULL, user_id bigint NOT NULL, status tinyint NOT NULL DEFAULT 0, amount decimal(10,2) NOT NULL DEFAULT 0.00, create_time datetime NOT NULL, update_time datetime NOT NULL, PRIMARY KEY (id), KEY idx_status (status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;原始查询语句是SELECT * FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 200000, 20;通过EXPLAIN能看到执行计划里type是ref走idx_status过滤了状态但Extra列仍有Using filesort。原因是idx_status只包含status字段无法支撑按create_time排序MySQL 需要先拿到 20 万行再排序、再丢弃前 199980 行。这个状态下的耗时在 3 秒左右线上用户反馈明显卡顿。3.2 方案一联合索引让排序直接走索引第一步改造也是最基础的一步——把单列索引idx_status升级为联合索引idx_status_create_timeALTER TABLE orders ADD INDEX idx_status_create_time (status, create_time);这个索引的设计遵循了最左前缀原则WHERE status 1命中第一列ORDER BY create_time DESC命中第二列的有序性。改完后再看执行计划EXPLAIN SELECT * FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 200000, 20;列值解读typeref通过索引等值匹配 statuskeyidx_status_create_time命中新联合索引rows大约 20 万只需扫描 status1 范围内大约 20 万行ExtraUsing index condition已无 Using filesort这时候排序本身已经不需要额外开销了但注意rows还是有大约 20 万——因为OFFSET仍然要求 MySQL 一路扫过前 20 万条索引记录。这一步优化后耗时显著下降但距离“根治”还有距离。3.3 方案二延迟关联改写减少回表和列传输联合索引解决了排序问题但SELECT *的回表依旧昂贵。把所有满足条件的id找到后每一行都要通过主键再回表查询一次完整数据。20 万行回表不是小数目而且网络传输和临时结果集也会占用资源。更好的写法是先用覆盖索引查出主键 ID再通过主键关联取完整行SELECT o.* FROM ( SELECT id FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 200000, 20 ) t JOIN orders o ON t.id o.id;在这个子查询里idx_status_create_time索引包含了status、create_time、id三个字段查询所需的全部数据都在索引页里不需要回表。这个状态下的扫描和排序都只涉及索引代价小很多。外层再用JOIN回原表取 20 行完整数据回表次数从 20 万次压缩到了 20 次。这套写法在 MySQL 5.6 以后的版本都有良好支持适用性很广。我实测这一步之后相同场景下的单次查询耗时从 3 秒左右降到了 0.3 秒以内。3.4 方案三游标分页彻底干掉 OFFSET联合索引和延迟关联把性能优化到了“还能接受”的程度但如果业务要翻到第 500 页、第 1000 页前者依然会越来越慢。要彻底解决问题得换个思路——不要告诉数据库“跳过多少行”而是告诉它“从哪一行开始往后取”。这就是游标分页也叫 keyset pagination.实现方式很简单上一页返回结果里的最后一条记录包含了下一页需要的“游标值”。比如按create_time倒序分页当前页最后一条的create_time是2024-06-01 12:00:00下一页的查询可以写成SELECT * FROM orders WHERE status 1 AND create_time 2024-06-01 12:00:00 ORDER BY create_time DESC LIMIT 20;这条 SQL 不需要跳过任何行因为它直接从create_time小于游标值的第一条记录开始取。只要(status, create_time)联合索引在MySQL 就能精确定位到游标位置取完 20 条立刻结束。不管翻到多深单次查询的代价都基本恒定。需要注意一个细节当create_time存在重复值时单纯用create_time ?会漏数据。这时候要把主键id一起加入游标条件形成“复合游标”SELECT * FROM orders WHERE status 1 AND (create_time 2024-06-01 12:00:00 OR (create_time 2024-06-01 12:00:00 AND id 123456)) ORDER BY create_time DESC, id DESC LIMIT 20;这里的id 123456保证了即使多条记录创建时间相同也能通过主键唯一确定排序位置不会重复也不会遗漏。游标分页的缺陷也很明显——它不支持随机跳页用户不能直接从第 1 页跳到第 100 页。但对于“加载更多”“滚动加载”“上一页/下一页”这类场景它是最优解。3.5 方案四范围分页适用于静态或流水型数据还有一种思路是把“翻页”改造成“按范围扫描”。如果某个查询场景的数据是近似静态的比如历史流水、归档日志或者业务方可以接受按时间范围分段那么可以用范围条件代替偏移量。SELECT * FROM orders WHERE status 1 AND create_time BETWEEN 2024-05-01 00:00:00 AND 2024-05-31 23:59:59 ORDER BY create_time DESC;这种写法本质上已经不算分页了而是在按业务维度“切分数据”每一页对应一个独立的时间片段。它的好处是每个片段之间完全独立天然适合并行查询和缓存预热而且不会随着页码增大而变慢。缺点是需要业务方接受“按时间段浏览数据”的交互方式对强随机翻页需求的场景不适用。我把这几种方案放在一起对比方案核心思路适用场景主要缺点普通 LIMIT 联合索引索引排序减少 filesort浅分页前 100 页深分页仍随 OFFSET 变慢延迟关联先索引查 ID 再回表中深度分页无法彻底解决 OFFSET 问题游标分页基于上次位置继续取滚动加载/上一页下一页不支持随机跳页范围分页按业务范围切分静态流水、归档数据依赖业务接受分段浏览4. 分页优化中的常见坑与排查技巧4.1 为什么加了索引还是没走排序字段的顺序很关键一个很常见的场景表里已经建了(status, create_time)联合索引但EXPLAIN里依然出现Using filesort。我排查过不少这类问题原因基本都是排序方向不一致或漏了排序字段。先看方向不一致的例子SELECT * FROM orders WHERE status 1 ORDER BY create_time ASC LIMIT 20;如果索引定义是(status, create_time DESC)而查询要求ASC索引就无法直接提供升序排列MySQL 会回调Using filesort。反过来也一样。解决办法是让查询的排序方向与索引一致或者在设计索引时就考虑好主要查询的排序方向。再看漏排序字段的例子SELECT * FROM orders WHERE status 1 ORDER BY create_time DESC, amount DESC LIMIT 20;索引只包含create_time没有amount所以即使前两个字段能利用索引多一个排序字段索引就用不上了。遇到这种场景要么把amount也加进索引要么调整查询逻辑避免多字段排序。还有一种隐蔽的坑是隐式类型转换。比如status是tinyint但查询里写了WHERE status 1MySQL 可能会把字段做隐式转换导致索引失效。排查时可以用SHOW WARNINGS查看 MySQL 实际执行的语句。4.2 COUNT(*) 为什么在大表分页里很要命很多分页接口除了返回当前页数据还会返回总页数于是执行了一条这样的查询SELECT COUNT(*) FROM orders WHERE status 1;这条语句看起来简单但在数据量大时可能比分页查询本身更慢。InnoDB 引擎不做精确计数它必须一条一条遍历满足status 1的记录来统计数量。如果条件匹配的行有几十万上百万这个 COUNT 就是一次全表扫描级的操作。在分页性能优化里COUNT 常常被忽略但它往往才是拖垮整个接口的元凶。三种优化思路用近似值代替精确值很多统计类页面的“总页数”并不要求精确到个位可以用EXPLAIN的rows字段或采样估算展示“约 XX 条”维护计数缓存对非实时的业务维护一个独立的计数表通过定时任务或异步更新去掉 COUNT如果前端改成滚动加载模式根本不需要总页数这是最省事的方案。4.3 缓存深分页数据的三个大坑有人会想既然分页慢那我干脆把每一页的查询结果缓存到 Rediskey 设计成page:orders:status_1:page_200这样第二次访问就直接走缓存了。这个思路本身没错但有几个问题要留意。第一个是空间成本。深分页的页数呈几何级增长假设每页缓存 20 条完整订单数据缓存 1000 页可能就吃掉几十 MB 甚至上百 MB 内存。换来的是极低概率被访问的热点数据性价比很低。第二个是数据一致性。订单状态、金额会变缓存过期时间设计不好用户看到的就是脏数据。如果设置较短的过期时间缓存命中率又上不来。第三个是缓存穿透。某个非常深的页码如果没人访问过第一次请求还是会打到数据库该慢还是慢。在大促或运营活动期间用户集中访问深分页这种穿透会被放大。所以我对分页缓存的建议是只缓存前几页的高频数据深分页一定在外层做业务限制或改造为游标分页不要指望用缓存来解决深分页的根源问题。4.4 分页性能问题排查速查表现象可能原因排查方向翻页越来越慢OFFSET 过大导致扫描行数增加EXPLAIN 看 rows确认深分页场景是否被业务接受排序字段没走索引索引顺序或方向不匹配检查联合索引定义与 ORDER BY 字段顺序、方向回表次数过多SELECT * 导致大量随机 I/O用覆盖索引延迟关联改写查询条件过滤慢索引失效或缺少复合索引SHOW WARNINGS、EXPLAIN type 字段接口整体慢但数据查询快多为 COUNT 或序列化开销过大单独压测 COUNT 语句考虑去掉或异步化深分页始终无法根治业务需要随机跳页限制翻页深度或引入搜索引擎4.5 一个快速验证优化效果的技巧我在实际项目中养成了一个习惯优化完分页语句后不是光看执行时间而是用EXPLAIN ANALYZE看真实的执行过程和行数消耗。MySQL 8.0 之后这个工具非常顺手它会把每一步操作的实际耗时、扫描行数都打印出来EXPLAIN ANALYZE SELECT * FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 200000, 20;输出结果里可以看到类似这样的信息- Limit: 20 row(s) (cost... rows20) - Nested loop inner join (cost... rows...) - Filter: (orders.status 1) (cost... rows...) - Index scan on orders using idx_status_create_time ... - Single-row index lookup on orders using PRIMARY ...通过对比改造前后的rows和actual time就能清楚地知道优化到底减少了多少无谓扫描。这套方法比单纯看“快了多少毫秒”更扎实因为它告诉你快的原因是什么。最后再分享一点个人习惯我在设计数据查询接口时默认就会想清楚交互模式如果是用户持续下拉浏览的列表直接采用游标分页根本不留给深分页出现的机会如果是后台管理系统的分页表格第一版就先和产品对齐“最多翻到第 100 页”的限制。大多数情况下这些前置设计能省掉后面大量的性能急救工作。如果真遇到必须支持随机跳页且数据量很大的场景我个人的底线是联合索引加延迟关联把响应时间压到可接受范围再配合对 COUNT 的降级和前端交互的限制。分页优化没有银弹但它一定有规律可循——先看懂数据是怎么被丢弃的再去决定用什么手段让数据库少做点无用功。