你有没有遇到过这种情况列表页第一页秒开翻到几十页开始明显卡顿到一百多页直接超时。我上次排查一个后台订单列表的线上慢查询从慢查询日志里捞出来的罪魁祸首就是一条看起来再普通不过的SQLSELECT id, order_no, user_id, amount, create_time FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 20;这条SQL写得很规范where条件有索引order by字段也有索引但就是慢。当时第一反应是服务器性能问题后来把执行计划、扫描行数、耗时放在一起看才意识到LIMIT OFFSET这种分页方式在深翻页场景下代价是线性增长的而且增长得比想象中还快。这篇文章我不打算只给结论而是把这个“为什么慢”从头到尾捋一遍先讲LIMIT OFFSET在MySQL里的执行逻辑再用实测数据量化offset变大之后的性能变化最后给出三种我在生产环境验证过的分页方案以及排序字段相关的几个坑。1. 从一条“走了索引但就是慢”的SQL说起1.1 业务背景与SQL长什么样线上后台系统里经常会有这类需求订单管理页面每页显示20条按创建时间倒序排列并且筛选出某个状态的数据。用户一页一页翻翻到第5001页的时候前端等待时间已经超过2秒再往后直接超时。慢查询日志里反复出现的就是上面那条SQL。这里LIMIT 100000, 20的含义是跳过前100000条只取第100001到第100020条。如果每页20条那它就是第5001页的数据。对很多运营后台来说翻到这一页不算特别离谱但数据库已经扛不住了。1.2 EXPLAIN看不出问题的真相先看执行计划这个SQL的EXPLAIN结果大致如下列值typerefkeyidx_statusrows120000ExtraUsing filesort第一眼看上去不算太差没有全表扫描type是refrows估算12万行也不算天文数字。但仔细看有两个隐患一是status字段的区分度不高会过滤出大量候选行二是排序字段create_time没有和status在同一个索引里所以Extra出现了Using filesort。真正让我确定问题所在的是用SHOW STATUS LIKE Innodb_rows_read在执行前后各取一次值。两次差值显示这个查询让InnoDB实际读取了大约100020行。这正是LIMIT 100000加上LIMIT 20的结果。也就是说MySQL并没有“直接跳到第100000行”而是把前100000行挨个读了一遍全部丢弃只留下最后20行。之后我做了个验证把LIMIT 100000, 20改成LIMIT 0, 20同样的where和order by条件响应时间立刻从接近2秒掉到几十毫秒。问题一下锁定了就是OFFSET太深导致的。1.3 分页慢不等于SQL写错了排这类问题的时候我习惯先把“分页慢”拆成两种场景第一页就慢问题多半出在where条件是否用了合适的索引、表数据量是否过大、查询字段是否需要回表这些地方。第一页快、越翻越慢几乎可以断定是LIMIT OFFSET里的offset在作祟因为每次翻页都要把之前所有页的数据重新扫一遍。判断方法很简单把LIMIT里的offset临时改成0其他条件不变如果速度立刻恢复正常那就不用怀疑别的东西了。真正要解决的是“深翻页”这个动作本身的代价而不是质疑SQL写错了。2. LIMIT OFFSET的底层逻辑数过offset行才开始取2.1 执行器层和存储引擎层的分工要理解LIMIT OFFSET为什么慢得先分清MySQL内部的两层结构。存储引擎层也就是InnoDB负责从索引或数据页里取行返回给server层server层负责where过滤、order by排序、limit截断这些操作。一条带LIMIT OFFSET的查询InnoDB会把满足条件的行一条条返回。server层拿到这些行之后不是直接取第offset1行而是从第一条开始数数到第offset行之后才把后续的limit行返回给客户端。那些被数过的行全部被丢弃。这就是LIMIT OFFSET复杂度是O(offsetlimit)的根本原因它必须把前面offset条全部读出来数一遍而不是“跳到”第offset条。你可以想象在一本没有页码的书里找内容每翻到新的一页都要从头数起。2.2 “数行”的成本为什么不能忽略如果只读主键id每条记录可能就十几个字节扫100万次也不至于太痛苦。但真实业务里SELECT *意味着每条记录都要完整读出来。InnoDB的一行记录可能几百字节二级索引定位之后还要回表读数据页这中间的IO和CPU成本是实实在在的。更关键的是数据页的读取方式。InnoDB以16KB的页为基本单位扫过的那些行往往散落在很多数据页里。最坏情况下每页只取一条记录就放弃导致大范围的随机回表。这种随机读比全表顺序扫描还要难受因为顺序扫描能利用预读机制而随机回表只能一块一块地从磁盘往buffer pool里搬。所以深分页慢的本质不是数据量多而是“读出来很多又全扔了”。在buffer pool不够大、热数据命不中的时候这个问题会被进一步放大。2.3 排序会把问题进一步放大如果order by字段没有可以利用的索引MySQL会把满足条件的行全部放入sort buffer排序排完序再执行limit截断。这意味着即使offset只有10000只要符合条件的总行数有200万它也可能先给200万行做排序。深分页场景下filesort是慢查询的重灾区。一个典型的例子是订单表按照创建时间排序但where条件只用了状态索引create_time没有和状态组成联合索引。结果就是先按状态捞出几十万行再把这几十万行全部sort一下然后丢弃前100000行最后返回20行。整个过程耗费的时间和磁盘临时空间都很大。如果order by字段有索引虽然可以省掉filesort但MySQL依然要从索引的某个位置开始向下扫描一边扫一边数数到offsetlimit才停。索引只能帮你“从某个符合条件的起点顺序走”不能帮你“从第offset个位置直接开始”。这是很多人的认知误区以为加了索引就能解决一切分页问题实际上索引只解决了定位和排序解决不了“数行”本身。3. 实测OFFSET从0涨到100万性能是怎么恶化的3.1 测试环境与数据准备为了让结论可复现我建了一张订单表并插入了200万行测试数据表结构如下CREATE TABLE t_orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL, create_time DATETIME NOT NULL, KEY idx_create_time (create_time) ) ENGINEInnoDB;故意没有在status上建索引是为了单独观察offset的作用。机器是普通的笔记本MySQL版本8.0.33innodb_buffer_pool_size设了1G。数据用存储过程循环插入200万行分布在两年的创建时间里。这个测试场景里SELECT * ORDER BY create_time DESC LIMIT offset, 20会走idx_create_time反向扫描每扫一条索引记录都要回表拿完整行数据正好模拟了线上最常见的深分页情况。3.2 四组数据的对照结果测试SQL很简单SELECT * FROM t_orders ORDER BY create_time DESC LIMIT {offset}, 20;多次执行取中位数结果如下offset对应页码(每页20条)响应时间Innodb_rows_read0第1页0.002s2010000第501页0.045s10020100000第5001页0.38s1000201000000第50001页2.9s1000020再用同样offset测试延迟关联和书签分页的耗时offset传统LIMIT OFFSET延迟关联书签分页100000.045s0.012s0.003s1000000.38s0.06s0.003s10000002.9s0.42s0.003s补充说明一下Innodb_rows_read这个变量按会话累计每次SQL执行前后各查一次差值就是本次查询InnoDB层实际读取的行数。这个指标比EXPLAIN的rows估算值靠谱得多排查分页问题的时候我非常推荐用这个方式观察。3.3 结果解读深分页真正消耗的是什么从上面的数据能看出三件事。第一传统LIMIT OFFSET的响应时间和Innodb_rows_read基本随offset线性增长。offset从0涨到100万读取行数从20涨到100万响应时间也从2毫秒涨到接近3秒。这个趋势非常稳定不存在说翻到一定程度突然变快的情况。第二延迟关联虽然也要在二级索引上扫同样多的行但是Innodb_rows_read这个数值在这条SQL上并不完全等同于回表次数。延迟关联的巧妙之处在于子查询只返回主键id走的是覆盖索引不需要回表外层JOIN只对20个id回表。所以响应时间从2.9秒降到0.42秒是因为把100万次随机回表压缩成了20次。第三书签分页完全不扫offset行所以翻到多深都稳定在毫秒级。这个结果对于深翻页场景是决定性的与其优化LIMIT OFFSET本身不如换一种不分页的取数逻辑。4. 三种在生产环境验证过的分页优化4.1 延迟关联把回表次数从十万降到二十延迟关联也叫延迟连接核心思路是先查主键id再拿主键id回表查完整数据。改造后的SQL长这样SELECT o.* FROM ( SELECT id FROM t_orders ORDER BY create_time DESC LIMIT 100000, 20 ) t JOIN t_orders o ON o.id t.id ORDER BY o.create_time DESC;执行的逻辑是子查询里只要id和create_time这两个字段都包含在idx_create_time索引里所以整个过程不需要回表。MySQL在二级索引上完成排序和offset计数最终只得到20个主键id。外层JOIN再按主键去查原表主键查询走聚簇索引一次一个页快速且精准。这个方案适合任意跳页、数据量大、又要SELECT *的场景。它的成本仍然和offset线性相关但把最昂贵的回表次数降到了最低。如果查询还带where条件可以把过滤字段和排序字段建成联合索引让子查询在索引上同时完成过滤、排序和offset计数。有一点需要注意MySQL优化器对派生表的处理在不同版本有差异执行计划里可能出现Derived或者Materialize。我的经验是加上LIMIT之后派生表基本会物化整体逻辑不变但上线前必须EXPLAIN确认子查询走了覆盖索引否则优化可能失效。4.2 书签分页翻得越深优势越明显书签分页也叫keyset分页、seek method。核心思想是记住上一页最后一条记录排序字段的值下一页直接从那个值之后开始取。上一页返回的最后一条记录如果create_time是2024-12-01 12:00:00id是12345那么下一页的SQL就是SELECT * FROM t_orders WHERE create_time 2024-12-01 12:00:00 ORDER BY create_time DESC, id DESC LIMIT 20;因为create_time上有索引这个where条件能直接定位到上一页末尾附近从这个点开始往后再扫20条即可。整个查询的扫描行数大约就是20和已经翻过多少页完全无关。这也是它为什么能在offset达到100万时还稳定在毫秒级。使用书签分页有三个前提业务形态是“下一页”而不是“跳到任意页”。排序字段有重复值时必须加一个唯一字段如id作为次级排序否则会漏数据或者重复。每一页请求都要把上一页最后一条的排序字段值传回后端。书签分页最适合App的列表流、瀑布流和“加载更多”场景。缺点是无法直接跳页用户想从第1页直接跳到第10000页就做不到了。但现实里基本没有用户会认真翻到那么深这个限制通常靠产品设计就能规避。4.3 任意跳页的妥协方案如果业务实在要任意跳页比如管理后台要求输入页码跳转我的建议分三步处理。第一步限制最大页码。翻到超过100页或者200页的时候提示用户使用筛选条件缩小范围。绝大多数后台系统这么做完全够用还能顺便减少无效的数据扫描。第二步把传统分页换成延迟关联。牺牲一点SQL复杂度换来可接受的性能。第三步针对超大offset的访问做缓存。热门页码的结果缓存到Redis设置短过期时间没必要每次都让数据库硬扛。同时要明白任何SQL层面的优化都只是缓解不是根治。深分页的根子在于“按偏移量找数据”这个模型。如果产品上允许用“加载更多”代替页码那是成本最低、效果最好的方案。5. 排序字段的分页陷阱案例和索引设计原则5.1 同样的SQL换个排序列为什么慢十倍曾经有个查询where条件过滤之后只剩几千行order by一个没有索引的金额字段LIMIT 200, 20。照理说几千行不算大但实际执行要1秒多。原因就是filesort几千行全部进sort buffer排序排序完成后再丢弃前200行。这种问题不是深分页本身造成的而是排序字段没有可用索引。即使offset不大filesort也可能把性能拖垮。解决办法很简单尽量让order by字段进入某个索引无论是单列索引还是联合索引只要排序能用上索引顺序Extra里就不会出现Using filesort。如果SQL是WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 20建议建联合索引(status, create_time)。这样where条件走最左匹配排序也能直接利用索引顺序一举两得。但要注意联合索引只解决“不用额外排序”没有解决“要数过offset行”。深翻页照样慢只是把慢的背景从“排序加数行”简化成了“数行”这已经能让延迟关联方案的收益更明显。5.2 排序字段重复值引发的分页混乱还有一个隐藏很深的坑排序字段区分度低。比如order by create_time如果同一秒有几百条订单LIMIT OFFSET翻页时可能出现同一条记录在两页都出现或者某条记录永远翻不到。原因不复杂order by没有把顺序完全确定下来MySQL在排序值相同时返回的顺序是不保证稳定的。每次执行可能因为并发插入、buffer pool状态不同导致相同排序值的记录顺序发生变化分页就会错乱。解决方法是把主键加入排序写成ORDER BY create_time DESC, id DESC;这样每条记录的顺序是100%确定的。如果使用书签分页边界条件也要同步带上这两个字段比如WHERE create_time ... OR (create_time ... AND id ...)才能保证页与页之间无缝衔接。5.3 分页场景下索引设计的三条实用规则第一过滤字段和排序字段尽量放进同一个联合索引。最典型的场景是WHERE status ? ORDER BY create_time联合索引(status, create_time)能让过滤和排序同时走索引。第二深分页要刻意设计覆盖索引。延迟关联的子查询需要覆盖id和排序字段所以像(status, create_time)这种联合索引天然就覆盖了主键id和排序字段效果很好。第三不要迷信单列索引。单独给status建索引区分度不高时反而会扫描很多行单独给create_time建索引where过滤又要回到主表过滤。分页性能优化往往是索引设计、查询改写和产品形态三件事一起做只调整其中一样效果都很有限。最后再分享一个我自己的判断标准数据量在十万级以内用户最多翻十几页用传统LIMIT OFFSET完全没问题别没事找事换方案数据量到百万千万级翻页深度又不受控早点把分页改成书签模式如果产品逻辑动不了至少把延迟关联用上。分页性能优化不是炫技而是把每一层成本看清楚以后选择最符合业务形态的那种做法。