1. 为什么你的分页查询越翻越慢很多开发者第一次意识到分页有性能问题往往是在数据量涨到几十万、上百万行之后。测试环境里LIMIT 0, 20跑得飞快线上翻到第几百页时接口突然超时日志里那条 SQL 的耗时从几毫秒飙到几秒。更让人困惑的是明明建了索引EXPLAIN看起来也走了索引为什么还是慢这个问题的根源不在索引本身而在于LIMIT offset, size这个语法的工作方式。数据库执行LIMIT 1000000, 20时并不是直接跳到第 100 万行再取 20 行而是要先定位并丢弃前 100 万行然后才返回后面那 20 行。也就是说你翻的页越靠后被白白扫描和丢弃的行就越多代价线性增长。我见过一个很典型的场景某内容管理后台的文章列表单表 300 万行运营同事习惯按时间倒序翻到很后面去找历史文章。前 50 页响应 200ms 以内翻到第 500 页时接口直接 5 秒以上数据库 CPU 被打满。当时第一反应是加索引但时间字段上本来就有索引加了也没用——因为瓶颈是丢弃行这个动作不是找行这个动作。所以优化分页查询核心思路只有两条要么减少需要丢弃的行数要么干脆不丢弃。围绕这两条思路衍生出了延迟关联、游标分页、覆盖索引、预计算边界等一系列方案。下面我会把每种方案的原理、适用场景、实测效果和踩过的坑都拆开讲清楚你可以直接对照自己的业务场景选型。这篇文章适合所有写过LIMIT分页的后端开发者、DBA以及正在被慢查询困扰的运维同学。不需要你是数据库专家但需要你对索引、执行计划有最基本的了解。我会尽量用生活化的类比把原理讲透再给出可以直接抄的 SQL 和代码。2. 先搞清楚 LIMIT 到底在干什么2.1 一个类比图书馆找第 1000 本书假设图书馆的书按编号排好放在书架上你要找第 1000 号到第 1020 号这 21 本书。如果管理员不知道第 1000 号书具体在哪个位置他只能从第 1 号开始一本一本数数到第 1000 本然后拿后面 21 本给你。前面数过的 999 本他一本都没用上但时间全花在数数上了。LIMIT 1000, 20就是这个管理员。数据库不知道第 1000 行在磁盘的哪个位置它只能沿着索引或数据页顺序扫描扫一行、判断一行、丢掉一行直到数够 offset 指定的行数才开始真正返回数据。这就是偏移量越大越慢的本质。2.2 执行计划里能看到的真相拿一张典型的订单表举例表结构简化如下CREATE TABLE t_order ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, amount decimal(10,2) NOT NULL, status tinyint NOT NULL, created_at datetime NOT NULL, PRIMARY KEY (id), KEY idx_created_at (created_at) ) ENGINEInnoDB;执行EXPLAIN SELECT * FROM t_order ORDER BY created_at DESC LIMIT 1000000, 20;你会看到typeindexrows估算值接近百万级Extra里没有Using filesort因为走了索引排序但rows这个数字就是问题所在——它意味着引擎预计要扫描上百万行。这里有个容易被忽略的点即使ORDER BY的字段有索引SELECT *仍然可能导致回表。如果查询只需要id和created_at优化器可能走覆盖索引但一旦SELECT *每一行都要回主键索引取完整数据代价更高。所以延迟关联的核心就是先把定位和取数这两件事拆开。2.3 offset 和 size 的代价差异很多人以为LIMIT 1000000, 20和LIMIT 0, 20的差别只是多扫了 100 万行但实际代价比这更复杂。当 offset 很大时还会带来几个连锁反应Buffer Pool 污染大量冷数据页被读入内存把热数据挤出去影响其他查询。回表次数暴增如果走的是二级索引每扫一行都可能触发一次回表100 万次随机 IO 是灾难性的。排序开销如果ORDER BY字段没有合适索引还要额外做 filesort内存和临时文件双重压力。理解了这些你就能明白为什么单纯加索引解决不了深分页问题——索引能加速找但加速不了丢。3. 延迟关联把回表推迟到最后一步3.1 核心思路拆解延迟关联Deferred Join是我在实际项目里用得最多、收益最直接的一招。它的思路是先用覆盖索引快速拿到目标行的主键再用主键去关联原表取完整数据。这样那 100 万行的扫描只发生在覆盖索引上不回表代价大幅降低。还是上面的订单表原始慢查询SELECT * FROM t_order ORDER BY created_at DESC LIMIT 1000000, 20;改写成延迟关联SELECT o.* FROM t_order o INNER JOIN ( SELECT id FROM t_order ORDER BY created_at DESC LIMIT 1000000, 20 ) AS tmp ON o.id tmp.id;内层子查询只查id而id是主键created_at上有索引所以这个子查询可以走覆盖索引索引里包含created_at和主键id扫描 100 万行但不需要回表。外层再用这 20 个id去主键索引精确取数只有 20 次回表。3.2 实测效果与前提条件我在一张 500 万行的表上做过对比测试环境是单机 MySQL 8.0Buffer Pool 4GB方案翻到第 5 万页offset 100 万耗时原始LIMIT约 4.2 秒延迟关联约 0.35 秒游标分页约 0.01 秒延迟关联把耗时降了一个数量级但它有前提内层子查询必须能走覆盖索引。如果ORDER BY的字段没有索引或者查询条件复杂到无法用索引覆盖这招效果会打折。判断方法很简单对内层子查询单独跑一次EXPLAIN看Extra里有没有Using index。注意延迟关联的收益来自减少回表不是减少扫描。如果 offset 大到几百万扫描本身仍然是负担这时候要考虑游标分页。3.3 一个容易踩的坑子查询被优化器改写有些 MySQL 版本会把INNER JOIN子查询改写成半连接或物化导致执行计划和你预期的不一样。我遇到过一次内层子查询被物化成临时表反而更慢。解决办法是用EXPLAIN确认执行计划必要时加/* NO_MERGE(tmp) */之类的优化器提示MySQL 8.0 支持。或者把子查询结果先查出来再在应用层拼IN条件虽然多一次网络往返但执行计划可控。另外如果业务允许把SELECT *换成只查需要的字段配合覆盖索引效果会更好。很多列表页其实只需要展示标题、时间、状态几个字段没必要把大字段如content、description也拉出来。4. 游标分页让数据库永远只扫 20 行4.1 从第几页到从哪一行之后游标分页Cursor-based Pagination是解决深分页的终极方案它的思路是不告诉数据库跳过多少行而是告诉它从哪个位置之后开始取。就像看书时用书签而不是每次从第一页数到第 500 页。传统分页SELECT * FROM t_order ORDER BY created_at DESC LIMIT 1000000, 20;游标分页SELECT * FROM t_order WHERE created_at 2024-01-15 10:30:00 ORDER BY created_at DESC LIMIT 20;这里的2024-01-15 10:30:00就是上一页最后一条记录的created_at值也就是游标。数据库可以直接通过索引定位到这个位置然后往后取 20 行无论翻到多深扫描行数永远是 20 行左右。4.2 游标字段的选择与唯一性陷阱游标字段必须满足两个条件有索引、排序稳定。created_at有索引但如果同一秒有多条记录created_at不唯一翻页时可能漏数据或重复。解决办法是用复合游标SELECT * FROM t_order WHERE (created_at, id) (2024-01-15 10:30:00, 123456) ORDER BY created_at DESC, id DESC LIMIT 20;MySQL 支持行值比较配合(created_at, id)的联合索引可以精确定位。这个写法在 MySQL 5.7 及以上都支持实测走索引没问题。提示如果排序字段是id自增主键那最简单直接用WHERE id last_id ORDER BY id DESC LIMIT 20性能和稳定性都最好。4.3 游标分页的代价不能跳页游标分页最大的限制是无法直接跳到第 N 页因为每一页都依赖上一页的游标。这对无限滚动的信息流、聊天记录、日志列表完全没问题但对跳转到第 50 页的后台管理就不友好。我的处理经验是分场景C 端信息流一律用游标分页用户体验更好性能最优。B 端后台如果产品坚持要页码可以保留传统分页但限制最大页数比如只允许翻到前 100 页或者用延迟关联 预计算总数折中。导出场景用游标分页分批拉取避免一次性LIMIT大偏移。还有一个细节游标分页返回结果时要把最后一条记录的游标值一并返回给前端前端下次请求带上。这个字段通常叫next_cursor或last_id设计 API 时要提前约定好。5. 覆盖索引与预计算把代价转移到写入时5.1 覆盖索引让扫描不回表覆盖索引Covering Index不是一种独立的分页方案而是其他方案的加速器。它的意思是查询需要的所有字段都包含在索引里引擎不需要回表。比如列表页只需要id、title、created_at可以建联合索引ALTER TABLE t_article ADD INDEX idx_created_title (created_at, id, title);这样SELECT id, title FROM t_article ORDER BY created_at DESC LIMIT 1000000, 20就能完全走索引扫描 100 万行也不回表。代价是索引变大、写入变慢需要权衡。我的一般原则是列表页查询字段控制在 3 到 5 个以内且都是短字段才考虑覆盖索引。如果包含TEXT或VARCHAR(1000)这种大字段索引膨胀会非常严重得不偿失。5.2 预计算边界值用空间换时间还有一种思路是预计算。既然深分页慢是因为要数行那能不能提前把第 N 页从哪个 id 开始算好存起来可以。做法是建一张分页边界表CREATE TABLE t_page_boundary ( page_no int NOT NULL, start_id bigint NOT NULL, created_at datetime NOT NULL, PRIMARY KEY (page_no) );后台定时任务每隔一段时间扫描一次把每页的起始id和created_at记录下来。查询时先查边界表拿到起始位置再用游标分页的方式取数据。这样查询永远是索引定位代价恒定。这个方案的缺点是数据实时性差新插入的数据会导致边界偏移。适合数据更新不频繁、对实时性要求不高的场景比如历史归档列表、报表数据。如果数据频繁变动边界表需要频繁重建维护成本高。5.3 三种方案怎么选方案适用场景优点缺点延迟关联必须支持跳页、offset 中等改动小、兼容性好offset 极大时仍慢游标分页信息流、无限滚动性能恒定、最优不能跳页覆盖索引查询字段少且固定加速所有分页索引膨胀、写入变慢预计算边界数据稳定、实时性低查询极快维护复杂、实时性差实际项目里往往是组合使用游标分页 覆盖索引是黄金搭档后台管理用延迟关联 限制最大页数报表类用预计算。6. 那些文档不会告诉你的实战细节6.1 COUNT 查询才是隐藏的杀手很多人优化了列表查询却忽略了COUNT(*)。分页组件通常要显示总页数于是每次请求都跑一次SELECT COUNT(*) FROM t_order WHERE ...。在几百万行的表上这个 COUNT 可能比列表查询还慢。我的处理方式有几种近似总数用EXPLAIN的rows估算值或者SHOW TABLE STATUS的Rows误差可接受时直接用。缓存总数总数变化不频繁时缓存几分钟。不显示总数信息流场景直接去掉总数只保留加载更多。维护计数表用触发器或应用层维护一张计数表写入时更新。注意COUNT(*)在 InnoDB 里需要扫描索引COUNT(1)和COUNT(*)性能基本一样COUNT(字段)反而更慢要判断 NULL。别被网上一些说法误导。6.2 ORDER BY 和 WHERE 的索引顺序联合索引的顺序很关键。如果查询是WHERE status 1 ORDER BY created_at DESC索引应该建(status, created_at)而不是(created_at, status)。因为最左前缀原则status作为等值条件放前面created_at放后面才能同时用于过滤和排序。如果status是范围条件比如status 1那created_at就无法用索引排序了会触发 filesort。这时候要么调整业务逻辑要么接受排序开销。6.3 深分页在分库分表下的变形分库分表后LIMIT 1000000, 20会变成每个分片都查LIMIT 1000000, 20然后在中间件层归并排序。这会让问题放大 N 倍。常见解法是禁止深分页产品层面限制最大页数。二次查询法先在每个分片查LIMIT offset/N, size拿到最小游标后再回查。ES 等搜索引擎把列表查询交给搜索引擎用search_after做游标分页。这些方案各有取舍核心还是那句话能不丢行就不丢行能不下发大 offset 就不下发。6.4 监控与回归别优化完就不管了优化上线后一定要有监控。我习惯在慢查询日志里单独标记分页 SQL设置阈值告警。另外压测时要模拟翻到最后一页的场景而不是只测第一页。很多性能问题都是上线后用户翻到深处才暴露的。还有一点优化方案要写进团队规范。否则过几个月新来的同事又写了一个LIMIT 1000000, 20前面的工作白做。我们团队的做法是把分页查询封装成统一的工具方法强制走游标或延迟关联从源头杜绝。7. 一套可以直接落地的分页改造清单如果你现在手上就有一个慢分页要改可以按这个顺序推进定位打开慢查询日志找到具体 SQL用EXPLAIN看rows和Extra确认是扫描行数问题还是回表问题。判断场景这个列表能不能接受不能跳页能就用游标分页不能就延迟关联。补索引确认ORDER BY和WHERE字段有合适的联合索引顺序符合最左前缀。改 SQL按选定方案改写内层子查询单独EXPLAIN验证覆盖索引。处理 COUNT评估总数是否必须精确不必须就缓存或估算。改 API游标分页要返回next_cursor前端配合改造。压测模拟深分页场景对比优化前后耗时。监控加慢查询告警把规范写进代码审查清单。这套流程我在几个项目里跑过基本能把深分页耗时从秒级压到毫秒级。最关键的一步其实是第 2 步——选对方案比调优参数重要得多。选错了方向再怎么加索引都是事倍功半。最后分享一个我自己的习惯每次写完分页 SQL都会下意识问自己一句这行 SQL 翻到最后一页会怎样。这个问题帮我提前发现过好几次潜在的性能炸弹。数据库优化没有银弹但把原理搞清楚再结合业务场景做取舍大部分问题都能找到性价比最高的解法。