尧图网站建设 
做后台管理系统的时候你是不是也遇到过这种场景列表页一开始挺流畅翻到第50页、第100页就开始转圈翻到最后一页直接卡到怀疑人生。产品说“不就换个页码嘛能有多慢”DBA甩过来一条慢查询日志罪魁祸首就是那条平平无奇的LIMIT 1000000, 20。这一章我们就来把MySQL分页性能这件事彻底掰开揉碎看看深分页到底慢在哪有哪些立竿见影的优化手段以及不同业务场景下该怎么选方案。这一章的内容适合谁看刚把SQL写利索的初级开发被线上慢查询逼着做优化的中级工程师以及想给团队定一套分页规范的负责人。我会先从MySQL执行分页的内部逻辑讲起再给覆盖索引、延迟关联、游标分页这些常用方案做对比最后用一个百万级订单列表的真实优化案例把流程串起来。1. 先搞清楚分页慢在哪LIMIT OFFSET 的执行逻辑1.1 一条分页 SQL 在服务端是怎么跑的很多同学对分页的理解停留在“MySQL会先跳过前面的行再返回需要的行”这个理解方向没错但漏掉了最关键的细节MySQL的InnoDB引擎在真正执行LIMIT offset, size时并没有跳过能力它只能老老实实把前offsetsize条记录都查出来然后丢弃前面offset条。举个例子SELECT * FROM orders ORDER BY id DESC LIMIT 1000000, 20这条SQL服务端实际干的事是根据索引或全表扫描找到符合条件的第一行然后沿着链表或索引顺序一路数到第1000020行最后只返回第1000001到第1000020这20条。这不是我拍脑袋说的你可以用执行计划验证。在MySQL 8.0里EXPLAIN的结果中这一行通常会显示rows扫描行数接近100万。也就是说你只想看20条数据数据库却为你跑了100万行这中间的时间和I/O成本全被“深分页”吃掉了。1.2 三个隐藏成本扫描行数、回表次数、排序临时表深分页慢不是某一个环节的锅而是三个成本叠加的结果。第一个成本是扫描行数膨胀。offset越大MySQL需要顺序扫描的行越多。有人可能会说“我有索引啊”但索引只能帮你快速定位到第一条符合条件的记录定位之后从第一条到目标偏移量之间的每一行你都得一个一个数过去。这就像你在书里查内容目录能帮你翻到那一章但要从章首页数到第1000段还是得一页一页翻。第二个成本是回表。如果查询语句要返回的字段不全在索引里MySQL每扫描到一行符合条件的记录都要拿着主键去聚簇索引里再取一次完整行数据。这个动作叫回表本质是一次随机I/O。深分页时这个动作会被重复执行几十万甚至上百万次性能自然就崩了。为什么很多分页优化方案都强调“先取主键再回表”就是要把昂贵的随机I/O次数降下来。第三个成本是排序临时表。只要查询里带ORDER BY而排序字段又没有走上合适的索引MySQL就得动用filesort。数据量小的时候可能在内存里排一旦sort_buffer_size装不下就会创建临时表甚至落盘到磁盘临时文件。深分页场景下你需要排序的往往不只是当前页的20行而是满足条件的所有行这个成本会随着数据总量增长而飙升。2. 分页优化的核心思路减少扫描和回表2.1 覆盖索引是什么怎么帮分页提速理解了深分页的成本构成优化思路就清晰了尽量让MySQL在扫描阶段少干活在回表阶段少跑路。覆盖索引就是同时解决这两个问题的利器。所谓覆盖索引是指查询所需的全部字段都在同一个索引中MySQL可以直接从索引树拿到所有数据不需要再回表。判断方法很简单看执行计划中Extra列有没有出现Using index。比如订单表orders(id, user_id, order_no, amount, status, create_time)主键是id业务上经常按create_time分页查询。如果你的分页SQL写成SELECT id, order_no, amount FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 20;这条SQL要返回id, order_no, amount三个字段如果只建了(status, create_time)这个索引那扫描索引后还得回表取order_no和amount。但如果你把索引改成覆盖式的ALTER TABLE orders ADD INDEX idx_status_time (status, create_time, order_no, amount);那么只要索引树里有全部查询字段InnoDB扫描索引就能直接返回结果连回表都省了。实际效果有多明显我之前在一个日增量几万行的业务表上做过测试覆盖索引能让深分页查询从900毫秒降到100毫秒左右代价是写入时索引维护成本会增加一些。这里要提醒一句覆盖索引不是字段加得越多越好。索引本质是一棵B树字段越多树越高、页越大写入时的分裂和维护开销也越高。一般只把查询频率高、字段长度不大比如int、bigint、短字符串的列加进去那些大字段如text、长varchar就不要塞进索引了。2.2 延迟关联先取主键再回表覆盖索引的思路是“让索引包揽所有字段”但现实里你往往需要返回一大堆字段不可能全塞进索引。这时候更通用的方案是延迟关联deferred join也叫延迟回表。延迟关联的核心思路是先把分页需要的“最小主键集合”查出来再用这些主键去关联原表取完整数据。还是拿订单表举例SELECT o.id, o.order_no, o.amount, o.status, o.create_time FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 20 ) t ON o.id t.id ORDER BY o.create_time DESC;这个SQL的执行逻辑分两步子查询里只查id这个查询可以走(status, create_time, id)索引完成排序和分页定位外层再根据拿到的20个主键回表取完整行。这样回表的次数被压缩到了20次和原来一次回表几十万次完全不是一个量级。可能有同学要问子查询外层又排序了一次不是白干了吗其实当返回结果集只有20行时这一层排序的开销可以忽略不计。你甚至可以不用ORDER BY因为子查询已经按create_time排好了外层直接按id关联后保持顺序即可。不过为了语义清晰和防止优化器行为变化我一般还是会写上。延迟关联的适用面非常广几乎所有深分页场景都能套用。唯一需要注意的是如果分页条件是复合的比如WHERE status 1 AND user_id 10086那么子查询里也要带上同样的过滤条件索引设计也要围绕过滤和排序字段展开。3. 几种典型优化方案实操对比3.1 方案一限制最大页码从业务层面消灭深分页这是最朴实也最有效的方案直接不允许访问太深的页码。很多真实产品里用户根本不会翻到第10万页所谓深分页带来的痛苦往往是自己系统的分页组件设计不合理。我之前维护过一个后台日志查询系统运营人员确实会为了找一条历史记录不停地翻页。后来我们做了三个改动最大页码限制为200页超出后提示“请使用筛选条件缩小范围”默认搜索条件加上时间范围保证结果集在一个合理量级提供按ID区间、按时间点跳转的高级查询能力。改完之后线上慢查询直接消失了大半。这个方案的优点是零成本、见效快缺点是治标不治本一旦业务确实需要翻到很深的页比如to B系统里用户明确要求“我要看第5000条以后的数据”它就不够用了。但它应该成为每个团队默认启用的底线策略因为99%的业务场景里用户想看的一定是“最近的数据”而不是“最中间的数据”。3.2 方案二游标分页Keyset Paging用条件代替偏移量如果业务真的需要跨过大量数据又不想性能崩掉那就把“翻页”的概念从“偏移量”变成“游标”。游标分页的思想是不用LIMIT offset, size而是记住上一页最后一条记录的位置下一页用WHERE条件直接定位。假设列表按create_time DESC, id DESC排序上一页最后一条记录的create_time是2025-06-01 12:00:00id是998877那么下一页就是SELECT id, order_no, amount, create_time FROM orders WHERE create_time 2025-06-01 12:00:00 OR (create_time 2025-06-01 12:00:00 AND id 998877) ORDER BY create_time DESC, id DESC LIMIT 20;这条SQL走了(create_time, id)联合索引后可以直接定位到游标位置然后往下扫20条。无论整体数据量是1万还是1亿查询速度都稳定在毫秒级因为扫描行数始终只有20行左右。这里的OR条件看起来有点绕本质是为了处理“排序字段不唯一”的情况。如果只用create_time ...那同一秒内有多条数据时就会漏数据或重复。所以标准的游标分页会同时带上排序字段和主键形成一个严格的总序。游标分页的局限也很明显用户不能随意跳页只能一页一页往前或往后翻。这在移动端“下拉加载更多”的场景里完全够用但在传统PC后台那种“直接输入页码跳转”的场景里就不合适了。所以它更适合信息流、日志列表、交易流水等滚动加载型业务。3.3 方案三用子查询或JOIN实现延迟关联前面提到的延迟关联本质上也是深分页场景下常用的优化手段。这里再给一个更简洁的变体直接用子查询SELECT * FROM orders WHERE id ( SELECT id FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 1 ) ORDER BY create_time DESC LIMIT 20;这个写法的思路是先用子查询拿到第100001条记录的主键然后外层用主键定位从那里往后取20条。它的效率比原始的LIMIT 100000,20高很多因为子查询里只扫描了索引没有回表但要注意它有个小坑如果第100001条和第100002条之间恰好有被逻辑删除或过滤掉的数据外层直接WHERE id ...可能会把本不该展示的数据带出来或者数量不对。所以这个写法只适合过滤条件稳定、数据物理上连续可控的场景。相比之下用INNER JOIN的延迟关联更严谨因为子查询里完整保留了过滤和排序条件外层基于主键取数不会受到中间数据变化的影响。两种方式我都用过项目里我会优先选JOIN版本子查询版本只在我能确认数据特征时才用。3.4 方案四汇总表和缓存用空间换时间还有一种思路不太常用但在特定场景里非常有效把分页要用的结果集提前算好存成汇总表或者放进Redis缓存。比如业务方要求“按标签查询文章列表并分页”每次查询都要把几十万篇文章按标签过滤再排序。与其让MySQL每次实时算不如写一个定时任务把当前标签下的文章ID列表按排序规则序列化后写入Redis的zset每页直接ZREVRANGE取值。这样分页的耗时就变成了内存操作性能天花板极高。汇总表和缓存的缺点是数据实时性差、架构复杂还需要处理增量更新和缓存失效。我一般只在两种情况下推荐一是查询结果集非常固定数据变化不频繁二是并发量高到MySQL已经扛不住必须把热点查询挪出数据库。3.5 方案对比什么时候选哪个方案核心原理适合场景缺点限制最大页码业务约束后台管理列表无法满足真实深翻需求游标分页条件定位滚动加载、信息流、流水不支持任意跳页延迟关联先取主键再回表任意跳页且数据量大SQL略复杂汇总表/缓存预计算固定结果集、高并发实时性差、维护成本高我在实际选型时一般遵循一个原则能限制页码就限制页码不能限制就用游标分页必须跳页就用延迟关联前面都不合适再用缓存。方案没有绝对的好只有适不适合当前业务。4. ORDER BY 排序与分页的联动优化4.1 filesort 对分页的影响分页查询十有八九带着ORDER BY排序是容易被低估的瓶颈。MySQL的排序分为两种情况走索引排序和filesort。走索引排序时数据本来就在B树上有序扫描到哪就是哪分页效率很高filesort则要把满足条件的行先取出来放进排序缓冲区排好序再执行分页逻辑。对深分页来说filesort的杀伤力是双倍的一方面要排序的数据是“全部符合条件的数据”不是当前页的20条另一方面排序结果很可能存到磁盘临时表产生额外的I/O。我见过最夸张的一个例子ORDER BY update_time DESC LIMIT 200000, 10执行计划显示Using temporary; Using filesort一条查询跑了6秒多。后来加上联合索引让排序走索引时间直接降到30毫秒。判断你的SQL是否走了filesort最简单的方法是EXPLAIN看Extra列。如果出现Using filesort就要检查排序字段是否跟表上的索引前缀匹配。这里不展开B树的全部原理但有一条核心规则要记住排序字段必须是索引列且排序方向ASC/DESC尽量与索引定义一致。MySQL 8.0支持降序索引这在以前是没有的如果你的排序大部分是DESC可以考虑显式建降序索引。4.2 多列排序时如何设计索引实际业务里的排序往往不止一个字段比如“按创建时间倒序时间相同按ID倒序”。这种情况下索引设计需要遵循联合索引的“最左前缀”原则。对于ORDER BY create_time DESC, id DESC建立一个(create_time, id)联合索引就非常合适因为索引可以同时支撑时间排序和同时间内的ID逆序。设计时要特别注意一个坑ORDER BY字段的顺序必须和索引列顺序一致否则索引会直接失效退回filesort。比如索引是(create_time, id)但你的SQL写成ORDER BY id DESC, create_time DESC优化器就无法利用这个索引做排序。还有一种常见场景是“过滤排序”组合比如WHERE status 1 ORDER BY create_time DESC。这时索引应该优先把过滤字段放前面排序字段放后面即(status, create_time)。原因在于先用等值条件缩小范围再在范围内走索引排序是最理想的执行路径。如果你把排序列放前面过滤时会扫描的范围就太大得不偿失。我在实践中还发现多列排序的分页优化最好把主键也放进排序条件里比如ORDER BY create_time DESC, id DESC。因为仅用create_time排序时同一时间戳下可能有大量数据分页边界不稳定页与页之间可能出现重复或遗漏。用“业务排序字段主键”构成唯一顺序游标分页和延迟关联的边界才能真正锁死。5. 实战一个百万级订单列表的优化全过程5.1 原始SQL与执行计划为了让大家把前面的方案串起来我模拟一个真实的优化案例。假设订单表orders有120万行数据结构简化如下CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, order_no VARCHAR(32) NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL, create_time DATETIME NOT NULL, KEY idx_status_time (status, create_time) ) ENGINEInnoDB;后台需求是按状态筛选订单按创建时间倒序分页展示。最初的SQL是SELECT id, order_no, amount, status, create_time FROM orders WHERE status 1 ORDER BY create_time DESC, id DESC LIMIT 200000, 20;执行计划大致如下使用idx_status_time索引定位status 1的范围但ORDER BY中的id DESC不包含在索引里所以Extra列显示Using filesort扫描行数在20万以上。线上实测耗时约1.8秒翻到第50万条以后基本超过3秒。这里有个细节值得说为什么已经走了索引还慢因为idx_status_time(status, create_time)只能保证create_time有序而SQL要求(create_time, id)双重排序导致MySQL必须在扫描status 1的所有记录后重新做一次完整排序。也就是说索引虽然帮忙缩小了范围但没帮忙解决排序瓶颈转移到了filesort上。5.2 优化后的延迟关联 SQL针对这个场景我做了两步优化。第一步把索引改成(status, create_time, id)让排序完整走索引ALTER TABLE orders DROP INDEX idx_status_time, ADD INDEX idx_status_time_id (status, create_time, id);第二步改成延迟关联先只取主键再关联完整数据SELECT o.id, o.order_no, o.amount, o.status, o.create_time FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY create_time DESC, id DESC LIMIT 200000, 20 ) t ON o.id t.id ORDER BY o.create_time DESC, o.id DESC;优化后执行计划的Extra列不再出现Using filesort子查询只扫描索引且不回表外层仅对20条主键做回表。线上实测同样翻到第20万条耗时从1.8秒降到了120毫秒左右。如果你不想改表结构也可以在SQL层面直接做延迟关联把索引留给原来的(status, create_time)但那样里层的ORDER BY create_time DESC, id DESC依然会有filesort风险。所以我的经验是延迟关联主要解决回表问题索引设计主要解决排序问题两个要配合着来只做一个效果会打折扣。5.3 参数调优与配置SQL层面的优化做完后如果排序数据量还是很大可以检查几个MySQL参数。sort_buffer_size是每个线程用于排序的内存缓冲区大小默认值通常为256KB。如果排序数据超过这个值MySQL会使用磁盘临时文件导致额外的I/O。把它调到2MB到4MB通常能覆盖大多数场景。注意这是每线程分配的内存并发高的时候不要调太大否则内存会撑不住。max_length_for_sort_data是MySQL决定使用哪种排序算法的阈值。如果单行数据长度超过这个值MySQL会采用“先排序主键排序列再回表取完整行”的算法读表次数会上升。默认1024字节一般够用但如果你在排序时带了一堆大字段这个参数就需要关注。tmp_table_size和max_heap_table_size共同决定内部临时表的大小上限。当ORDER BY的中间结果超过这个值临时表会从内存转为磁盘。我在高并发场景下建议这两个值不要超过64MB否则内存临时表被撑爆的风险会变大。这些参数的具体调法要结合服务器的内存和并发线程数不能抄一个值就上。我的基本思路是先用执行计划确认排序方式再通过SHOW STATUS LIKE Sort_merge_passes观察磁盘排序次数如果这个值一直在涨就说明sort_buffer_size不够了。5.4 结果对比指标优化前优化后翻到第20万页附近耗时约1.8秒约120毫秒执行计划排序方式Using filesortUsing index回表次数约20万次20次扫描行数20万20万索引扫描无回表接近20这个案例给我的最大启发是分页性能优化不是某一条SQL的魔法而是“索引设计查询改写参数配置”的组合拳。只改SQL不改索引filesort依然在只改索引不改SQL回表依然多参数调优则解决最末端的内存与磁盘开销问题。6. 常见问题与排查技巧实录6.1 为什么加了索引还是慢这是我被问得最多的问题“我明明给排序字段建了索引为什么分页查询还是慢”要回答这个问题不能只看索引是否存在要看执行计划是否真的用上了。常见的原因有三个。一是索引建了但没被选中因为优化器认为全表扫描更“划算”特别是在数据量不大或者索引选择性很差的时候。解决办法是用FORCE INDEX临时验证或者调整索引列的顺序。二是索引中字段和查询里的过滤/排序对不上比如查询用WHERE a 1 ORDER BY b但索引是(b, a)最左前缀匹配不上。三是查询返回的字段太多必须回表即使索引扫描很快回表次数一多就把时间吃掉。这时候就该上延迟关联或者覆盖索引。排查工具很简单EXPLAIN ANALYZEMySQL 8.0.18可以直接看到每一步的耗时和扫描行数。我强烈建议把这个命令作为分页问题的第一步体检工具远比拿秒表掐时间靠谱。6.2 分页总数 COUNT(*) 也很慢怎么办分页查询通常还需要返回总条数也就是SELECT COUNT(*)。很多人费尽心思优化了LIMIT查询结果COUNT(*)反而成了新的瓶颈。COUNT(*)慢的根本原因是InnoDB必须扫描满足条件的所有行而不是像MyISAM那样直接读总数。如果表很大、过滤条件很多这个操作的成本可能比分页本身还高。我的处理思路分三层。第一层如果业务允许“总数不需要绝对精确”就用SQL_CALC_FOUND_ROWS的替代品——直接把总数缓存到Redis定时更新或者只在第一页查询时计算后续页复用。第二层如果要精确计数确保过滤字段有合适的索引让计数尽可能走索引扫描而不是全表。第三层实在不行就把计数结果落到统计表里用异步任务维护。记住一个原则在高并发系统里别让数据库每次翻页都实时算全量总数。6.3 多个排序字段导致索引失效上一节说过ORDER BY字段顺序和索引列顺序不一致会让索引失效。但在真实项目中更隐蔽的问题是排序方向不一致。比如索引是(create_time ASC, id ASC)而你的查询是ORDER BY create_time DESC, id ASC。MySQL从8.0开始支持降序索引但如果你没建降序索引优化器可能只能对create_time做反向扫描然后发现id方向又不匹配只好退回filesort。这种“一半匹配一半不匹配”的状态最难查执行计划也不会直接告诉你哪里断了需要自己把SQL里的排序条件逐字段跟索引做对照。规避办法就一条让排序字段的升降序和索引定义保持一致。如果你常用DESC可以考虑把索引改成(create_time DESC, id DESC)。不过要注意降序索引在写入时维护成本略高不是业务真的有逆序排序需求不值得全表都建。6.4 哪些场景适合分库分表后分页数据量到了一定级别单表已经扛不住很多人会做分库分表。但分库分表后分页成了一个更棘手的问题每个分片只能算出自己内部的偏移量全局偏移量怎么算最粗暴的做法是把所有分片的数据都取出来在应用层合并、排序、重新分页。这个方案在一个分片数据量不大时是可接受的但一旦数据总量上亿应用层排序的内存和CPU开销会非常夸张。更实用的方案有两个一是仍然借助游标分页只要全局排序规则清晰每页查询带上上一页的游标条件分发给各个分片并行查询再在应用层merge性能通常不错二是引入搜索引擎或OLAP引擎把分页查询的活交给ES、ClickHouse这类天生适合大数据分析的组件MySQL只负责OLTP写入和单条查询。我的建议是不要在分库分表之后还硬撑着用LIMIT offset做全局跳页那是拿数据库的死穴硬碰。先看业务能不能改成游标式翻页不行就认怂引入外部存储。7. 一些我踩过坑之后的个人心得分页优化这个主题看起来知识点就那么几个但真正落地的时候坑往往不在SQL本身而在业务和技术的交叉点上。我见过最典型的翻车现场是技术团队花了一整周把深分页优化得漂漂亮亮结果产品一上新需求排序规则多了两个字段索引全部失效性能一夜回到解放前。所以我现在做分页优化第一步永远是反问产品经理“这个列表真的需要跳页吗排序规则将来会不会变”如果排序规则确实会变我会倾向于用游标分页而不是依赖固定索引的延迟关联因为游标分页对索引的依赖更轻排序字段变化时只需要改条件。还有一个小技巧是写分页SQL时尽量把主键放进排序条件。之前维护过一个交易流水列表业务上按create_time排序结果同一秒内有几百条记录分页时用户看到的数据一直在跳。加上id DESC作为次级排序后边界问题彻底消失。这个习惯成本极低收益却很实在。最后再分享一个排查顺序遇到分页慢别急着改SQL先EXPLAIN ANALYZE看一眼瓶颈在这三件事的哪一件——多扫描的行、多回表的次数、多花的排序内存。对症下药之后你会发现优化思路其实特别直白减少MySQL不得不做的无用功而已。 MySQL深分页优化实战：覆盖索引、延迟关联与游标分页
做后台管理系统的时候你是不是也遇到过这种场景列表页一开始挺流畅翻到第50页、第100页就开始转圈翻到最后一页直接卡到怀疑人生。产品说“不就换个页码嘛能有多慢”DBA甩过来一条慢查询日志罪魁祸首就是那条平平无奇的LIMIT 1000000, 20。这一章我们就来把MySQL分页性能这件事彻底掰开揉碎看看深分页到底慢在哪有哪些立竿见影的优化手段以及不同业务场景下该怎么选方案。这一章的内容适合谁看刚把SQL写利索的初级开发被线上慢查询逼着做优化的中级工程师以及想给团队定一套分页规范的负责人。我会先从MySQL执行分页的内部逻辑讲起再给覆盖索引、延迟关联、游标分页这些常用方案做对比最后用一个百万级订单列表的真实优化案例把流程串起来。1. 先搞清楚分页慢在哪LIMIT OFFSET 的执行逻辑1.1 一条分页 SQL 在服务端是怎么跑的很多同学对分页的理解停留在“MySQL会先跳过前面的行再返回需要的行”这个理解方向没错但漏掉了最关键的细节MySQL的InnoDB引擎在真正执行LIMIT offset, size时并没有跳过能力它只能老老实实把前offsetsize条记录都查出来然后丢弃前面offset条。举个例子SELECT * FROM orders ORDER BY id DESC LIMIT 1000000, 20这条SQL服务端实际干的事是根据索引或全表扫描找到符合条件的第一行然后沿着链表或索引顺序一路数到第1000020行最后只返回第1000001到第1000020这20条。这不是我拍脑袋说的你可以用执行计划验证。在MySQL 8.0里EXPLAIN的结果中这一行通常会显示rows扫描行数接近100万。也就是说你只想看20条数据数据库却为你跑了100万行这中间的时间和I/O成本全被“深分页”吃掉了。1.2 三个隐藏成本扫描行数、回表次数、排序临时表深分页慢不是某一个环节的锅而是三个成本叠加的结果。第一个成本是扫描行数膨胀。offset越大MySQL需要顺序扫描的行越多。有人可能会说“我有索引啊”但索引只能帮你快速定位到第一条符合条件的记录定位之后从第一条到目标偏移量之间的每一行你都得一个一个数过去。这就像你在书里查内容目录能帮你翻到那一章但要从章首页数到第1000段还是得一页一页翻。第二个成本是回表。如果查询语句要返回的字段不全在索引里MySQL每扫描到一行符合条件的记录都要拿着主键去聚簇索引里再取一次完整行数据。这个动作叫回表本质是一次随机I/O。深分页时这个动作会被重复执行几十万甚至上百万次性能自然就崩了。为什么很多分页优化方案都强调“先取主键再回表”就是要把昂贵的随机I/O次数降下来。第三个成本是排序临时表。只要查询里带ORDER BY而排序字段又没有走上合适的索引MySQL就得动用filesort。数据量小的时候可能在内存里排一旦sort_buffer_size装不下就会创建临时表甚至落盘到磁盘临时文件。深分页场景下你需要排序的往往不只是当前页的20行而是满足条件的所有行这个成本会随着数据总量增长而飙升。2. 分页优化的核心思路减少扫描和回表2.1 覆盖索引是什么怎么帮分页提速理解了深分页的成本构成优化思路就清晰了尽量让MySQL在扫描阶段少干活在回表阶段少跑路。覆盖索引就是同时解决这两个问题的利器。所谓覆盖索引是指查询所需的全部字段都在同一个索引中MySQL可以直接从索引树拿到所有数据不需要再回表。判断方法很简单看执行计划中Extra列有没有出现Using index。比如订单表orders(id, user_id, order_no, amount, status, create_time)主键是id业务上经常按create_time分页查询。如果你的分页SQL写成SELECT id, order_no, amount FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 20;这条SQL要返回id, order_no, amount三个字段如果只建了(status, create_time)这个索引那扫描索引后还得回表取order_no和amount。但如果你把索引改成覆盖式的ALTER TABLE orders ADD INDEX idx_status_time (status, create_time, order_no, amount);那么只要索引树里有全部查询字段InnoDB扫描索引就能直接返回结果连回表都省了。实际效果有多明显我之前在一个日增量几万行的业务表上做过测试覆盖索引能让深分页查询从900毫秒降到100毫秒左右代价是写入时索引维护成本会增加一些。这里要提醒一句覆盖索引不是字段加得越多越好。索引本质是一棵B树字段越多树越高、页越大写入时的分裂和维护开销也越高。一般只把查询频率高、字段长度不大比如int、bigint、短字符串的列加进去那些大字段如text、长varchar就不要塞进索引了。2.2 延迟关联先取主键再回表覆盖索引的思路是“让索引包揽所有字段”但现实里你往往需要返回一大堆字段不可能全塞进索引。这时候更通用的方案是延迟关联deferred join也叫延迟回表。延迟关联的核心思路是先把分页需要的“最小主键集合”查出来再用这些主键去关联原表取完整数据。还是拿订单表举例SELECT o.id, o.order_no, o.amount, o.status, o.create_time FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 20 ) t ON o.id t.id ORDER BY o.create_time DESC;这个SQL的执行逻辑分两步子查询里只查id这个查询可以走(status, create_time, id)索引完成排序和分页定位外层再根据拿到的20个主键回表取完整行。这样回表的次数被压缩到了20次和原来一次回表几十万次完全不是一个量级。可能有同学要问子查询外层又排序了一次不是白干了吗其实当返回结果集只有20行时这一层排序的开销可以忽略不计。你甚至可以不用ORDER BY因为子查询已经按create_time排好了外层直接按id关联后保持顺序即可。不过为了语义清晰和防止优化器行为变化我一般还是会写上。延迟关联的适用面非常广几乎所有深分页场景都能套用。唯一需要注意的是如果分页条件是复合的比如WHERE status 1 AND user_id 10086那么子查询里也要带上同样的过滤条件索引设计也要围绕过滤和排序字段展开。3. 几种典型优化方案实操对比3.1 方案一限制最大页码从业务层面消灭深分页这是最朴实也最有效的方案直接不允许访问太深的页码。很多真实产品里用户根本不会翻到第10万页所谓深分页带来的痛苦往往是自己系统的分页组件设计不合理。我之前维护过一个后台日志查询系统运营人员确实会为了找一条历史记录不停地翻页。后来我们做了三个改动最大页码限制为200页超出后提示“请使用筛选条件缩小范围”默认搜索条件加上时间范围保证结果集在一个合理量级提供按ID区间、按时间点跳转的高级查询能力。改完之后线上慢查询直接消失了大半。这个方案的优点是零成本、见效快缺点是治标不治本一旦业务确实需要翻到很深的页比如to B系统里用户明确要求“我要看第5000条以后的数据”它就不够用了。但它应该成为每个团队默认启用的底线策略因为99%的业务场景里用户想看的一定是“最近的数据”而不是“最中间的数据”。3.2 方案二游标分页Keyset Paging用条件代替偏移量如果业务真的需要跨过大量数据又不想性能崩掉那就把“翻页”的概念从“偏移量”变成“游标”。游标分页的思想是不用LIMIT offset, size而是记住上一页最后一条记录的位置下一页用WHERE条件直接定位。假设列表按create_time DESC, id DESC排序上一页最后一条记录的create_time是2025-06-01 12:00:00id是998877那么下一页就是SELECT id, order_no, amount, create_time FROM orders WHERE create_time 2025-06-01 12:00:00 OR (create_time 2025-06-01 12:00:00 AND id 998877) ORDER BY create_time DESC, id DESC LIMIT 20;这条SQL走了(create_time, id)联合索引后可以直接定位到游标位置然后往下扫20条。无论整体数据量是1万还是1亿查询速度都稳定在毫秒级因为扫描行数始终只有20行左右。这里的OR条件看起来有点绕本质是为了处理“排序字段不唯一”的情况。如果只用create_time ...那同一秒内有多条数据时就会漏数据或重复。所以标准的游标分页会同时带上排序字段和主键形成一个严格的总序。游标分页的局限也很明显用户不能随意跳页只能一页一页往前或往后翻。这在移动端“下拉加载更多”的场景里完全够用但在传统PC后台那种“直接输入页码跳转”的场景里就不合适了。所以它更适合信息流、日志列表、交易流水等滚动加载型业务。3.3 方案三用子查询或JOIN实现延迟关联前面提到的延迟关联本质上也是深分页场景下常用的优化手段。这里再给一个更简洁的变体直接用子查询SELECT * FROM orders WHERE id ( SELECT id FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 1 ) ORDER BY create_time DESC LIMIT 20;这个写法的思路是先用子查询拿到第100001条记录的主键然后外层用主键定位从那里往后取20条。它的效率比原始的LIMIT 100000,20高很多因为子查询里只扫描了索引没有回表但要注意它有个小坑如果第100001条和第100002条之间恰好有被逻辑删除或过滤掉的数据外层直接WHERE id ...可能会把本不该展示的数据带出来或者数量不对。所以这个写法只适合过滤条件稳定、数据物理上连续可控的场景。相比之下用INNER JOIN的延迟关联更严谨因为子查询里完整保留了过滤和排序条件外层基于主键取数不会受到中间数据变化的影响。两种方式我都用过项目里我会优先选JOIN版本子查询版本只在我能确认数据特征时才用。3.4 方案四汇总表和缓存用空间换时间还有一种思路不太常用但在特定场景里非常有效把分页要用的结果集提前算好存成汇总表或者放进Redis缓存。比如业务方要求“按标签查询文章列表并分页”每次查询都要把几十万篇文章按标签过滤再排序。与其让MySQL每次实时算不如写一个定时任务把当前标签下的文章ID列表按排序规则序列化后写入Redis的zset每页直接ZREVRANGE取值。这样分页的耗时就变成了内存操作性能天花板极高。汇总表和缓存的缺点是数据实时性差、架构复杂还需要处理增量更新和缓存失效。我一般只在两种情况下推荐一是查询结果集非常固定数据变化不频繁二是并发量高到MySQL已经扛不住必须把热点查询挪出数据库。3.5 方案对比什么时候选哪个方案核心原理适合场景缺点限制最大页码业务约束后台管理列表无法满足真实深翻需求游标分页条件定位滚动加载、信息流、流水不支持任意跳页延迟关联先取主键再回表任意跳页且数据量大SQL略复杂汇总表/缓存预计算固定结果集、高并发实时性差、维护成本高我在实际选型时一般遵循一个原则能限制页码就限制页码不能限制就用游标分页必须跳页就用延迟关联前面都不合适再用缓存。方案没有绝对的好只有适不适合当前业务。4. ORDER BY 排序与分页的联动优化4.1 filesort 对分页的影响分页查询十有八九带着ORDER BY排序是容易被低估的瓶颈。MySQL的排序分为两种情况走索引排序和filesort。走索引排序时数据本来就在B树上有序扫描到哪就是哪分页效率很高filesort则要把满足条件的行先取出来放进排序缓冲区排好序再执行分页逻辑。对深分页来说filesort的杀伤力是双倍的一方面要排序的数据是“全部符合条件的数据”不是当前页的20条另一方面排序结果很可能存到磁盘临时表产生额外的I/O。我见过最夸张的一个例子ORDER BY update_time DESC LIMIT 200000, 10执行计划显示Using temporary; Using filesort一条查询跑了6秒多。后来加上联合索引让排序走索引时间直接降到30毫秒。判断你的SQL是否走了filesort最简单的方法是EXPLAIN看Extra列。如果出现Using filesort就要检查排序字段是否跟表上的索引前缀匹配。这里不展开B树的全部原理但有一条核心规则要记住排序字段必须是索引列且排序方向ASC/DESC尽量与索引定义一致。MySQL 8.0支持降序索引这在以前是没有的如果你的排序大部分是DESC可以考虑显式建降序索引。4.2 多列排序时如何设计索引实际业务里的排序往往不止一个字段比如“按创建时间倒序时间相同按ID倒序”。这种情况下索引设计需要遵循联合索引的“最左前缀”原则。对于ORDER BY create_time DESC, id DESC建立一个(create_time, id)联合索引就非常合适因为索引可以同时支撑时间排序和同时间内的ID逆序。设计时要特别注意一个坑ORDER BY字段的顺序必须和索引列顺序一致否则索引会直接失效退回filesort。比如索引是(create_time, id)但你的SQL写成ORDER BY id DESC, create_time DESC优化器就无法利用这个索引做排序。还有一种常见场景是“过滤排序”组合比如WHERE status 1 ORDER BY create_time DESC。这时索引应该优先把过滤字段放前面排序字段放后面即(status, create_time)。原因在于先用等值条件缩小范围再在范围内走索引排序是最理想的执行路径。如果你把排序列放前面过滤时会扫描的范围就太大得不偿失。我在实践中还发现多列排序的分页优化最好把主键也放进排序条件里比如ORDER BY create_time DESC, id DESC。因为仅用create_time排序时同一时间戳下可能有大量数据分页边界不稳定页与页之间可能出现重复或遗漏。用“业务排序字段主键”构成唯一顺序游标分页和延迟关联的边界才能真正锁死。5. 实战一个百万级订单列表的优化全过程5.1 原始SQL与执行计划为了让大家把前面的方案串起来我模拟一个真实的优化案例。假设订单表orders有120万行数据结构简化如下CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, order_no VARCHAR(32) NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL, create_time DATETIME NOT NULL, KEY idx_status_time (status, create_time) ) ENGINEInnoDB;后台需求是按状态筛选订单按创建时间倒序分页展示。最初的SQL是SELECT id, order_no, amount, status, create_time FROM orders WHERE status 1 ORDER BY create_time DESC, id DESC LIMIT 200000, 20;执行计划大致如下使用idx_status_time索引定位status 1的范围但ORDER BY中的id DESC不包含在索引里所以Extra列显示Using filesort扫描行数在20万以上。线上实测耗时约1.8秒翻到第50万条以后基本超过3秒。这里有个细节值得说为什么已经走了索引还慢因为idx_status_time(status, create_time)只能保证create_time有序而SQL要求(create_time, id)双重排序导致MySQL必须在扫描status 1的所有记录后重新做一次完整排序。也就是说索引虽然帮忙缩小了范围但没帮忙解决排序瓶颈转移到了filesort上。5.2 优化后的延迟关联 SQL针对这个场景我做了两步优化。第一步把索引改成(status, create_time, id)让排序完整走索引ALTER TABLE orders DROP INDEX idx_status_time, ADD INDEX idx_status_time_id (status, create_time, id);第二步改成延迟关联先只取主键再关联完整数据SELECT o.id, o.order_no, o.amount, o.status, o.create_time FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY create_time DESC, id DESC LIMIT 200000, 20 ) t ON o.id t.id ORDER BY o.create_time DESC, o.id DESC;优化后执行计划的Extra列不再出现Using filesort子查询只扫描索引且不回表外层仅对20条主键做回表。线上实测同样翻到第20万条耗时从1.8秒降到了120毫秒左右。如果你不想改表结构也可以在SQL层面直接做延迟关联把索引留给原来的(status, create_time)但那样里层的ORDER BY create_time DESC, id DESC依然会有filesort风险。所以我的经验是延迟关联主要解决回表问题索引设计主要解决排序问题两个要配合着来只做一个效果会打折扣。5.3 参数调优与配置SQL层面的优化做完后如果排序数据量还是很大可以检查几个MySQL参数。sort_buffer_size是每个线程用于排序的内存缓冲区大小默认值通常为256KB。如果排序数据超过这个值MySQL会使用磁盘临时文件导致额外的I/O。把它调到2MB到4MB通常能覆盖大多数场景。注意这是每线程分配的内存并发高的时候不要调太大否则内存会撑不住。max_length_for_sort_data是MySQL决定使用哪种排序算法的阈值。如果单行数据长度超过这个值MySQL会采用“先排序主键排序列再回表取完整行”的算法读表次数会上升。默认1024字节一般够用但如果你在排序时带了一堆大字段这个参数就需要关注。tmp_table_size和max_heap_table_size共同决定内部临时表的大小上限。当ORDER BY的中间结果超过这个值临时表会从内存转为磁盘。我在高并发场景下建议这两个值不要超过64MB否则内存临时表被撑爆的风险会变大。这些参数的具体调法要结合服务器的内存和并发线程数不能抄一个值就上。我的基本思路是先用执行计划确认排序方式再通过SHOW STATUS LIKE Sort_merge_passes观察磁盘排序次数如果这个值一直在涨就说明sort_buffer_size不够了。5.4 结果对比指标优化前优化后翻到第20万页附近耗时约1.8秒约120毫秒执行计划排序方式Using filesortUsing index回表次数约20万次20次扫描行数20万20万索引扫描无回表接近20这个案例给我的最大启发是分页性能优化不是某一条SQL的魔法而是“索引设计查询改写参数配置”的组合拳。只改SQL不改索引filesort依然在只改索引不改SQL回表依然多参数调优则解决最末端的内存与磁盘开销问题。6. 常见问题与排查技巧实录6.1 为什么加了索引还是慢这是我被问得最多的问题“我明明给排序字段建了索引为什么分页查询还是慢”要回答这个问题不能只看索引是否存在要看执行计划是否真的用上了。常见的原因有三个。一是索引建了但没被选中因为优化器认为全表扫描更“划算”特别是在数据量不大或者索引选择性很差的时候。解决办法是用FORCE INDEX临时验证或者调整索引列的顺序。二是索引中字段和查询里的过滤/排序对不上比如查询用WHERE a 1 ORDER BY b但索引是(b, a)最左前缀匹配不上。三是查询返回的字段太多必须回表即使索引扫描很快回表次数一多就把时间吃掉。这时候就该上延迟关联或者覆盖索引。排查工具很简单EXPLAIN ANALYZEMySQL 8.0.18可以直接看到每一步的耗时和扫描行数。我强烈建议把这个命令作为分页问题的第一步体检工具远比拿秒表掐时间靠谱。6.2 分页总数 COUNT(*) 也很慢怎么办分页查询通常还需要返回总条数也就是SELECT COUNT(*)。很多人费尽心思优化了LIMIT查询结果COUNT(*)反而成了新的瓶颈。COUNT(*)慢的根本原因是InnoDB必须扫描满足条件的所有行而不是像MyISAM那样直接读总数。如果表很大、过滤条件很多这个操作的成本可能比分页本身还高。我的处理思路分三层。第一层如果业务允许“总数不需要绝对精确”就用SQL_CALC_FOUND_ROWS的替代品——直接把总数缓存到Redis定时更新或者只在第一页查询时计算后续页复用。第二层如果要精确计数确保过滤字段有合适的索引让计数尽可能走索引扫描而不是全表。第三层实在不行就把计数结果落到统计表里用异步任务维护。记住一个原则在高并发系统里别让数据库每次翻页都实时算全量总数。6.3 多个排序字段导致索引失效上一节说过ORDER BY字段顺序和索引列顺序不一致会让索引失效。但在真实项目中更隐蔽的问题是排序方向不一致。比如索引是(create_time ASC, id ASC)而你的查询是ORDER BY create_time DESC, id ASC。MySQL从8.0开始支持降序索引但如果你没建降序索引优化器可能只能对create_time做反向扫描然后发现id方向又不匹配只好退回filesort。这种“一半匹配一半不匹配”的状态最难查执行计划也不会直接告诉你哪里断了需要自己把SQL里的排序条件逐字段跟索引做对照。规避办法就一条让排序字段的升降序和索引定义保持一致。如果你常用DESC可以考虑把索引改成(create_time DESC, id DESC)。不过要注意降序索引在写入时维护成本略高不是业务真的有逆序排序需求不值得全表都建。6.4 哪些场景适合分库分表后分页数据量到了一定级别单表已经扛不住很多人会做分库分表。但分库分表后分页成了一个更棘手的问题每个分片只能算出自己内部的偏移量全局偏移量怎么算最粗暴的做法是把所有分片的数据都取出来在应用层合并、排序、重新分页。这个方案在一个分片数据量不大时是可接受的但一旦数据总量上亿应用层排序的内存和CPU开销会非常夸张。更实用的方案有两个一是仍然借助游标分页只要全局排序规则清晰每页查询带上上一页的游标条件分发给各个分片并行查询再在应用层merge性能通常不错二是引入搜索引擎或OLAP引擎把分页查询的活交给ES、ClickHouse这类天生适合大数据分析的组件MySQL只负责OLTP写入和单条查询。我的建议是不要在分库分表之后还硬撑着用LIMIT offset做全局跳页那是拿数据库的死穴硬碰。先看业务能不能改成游标式翻页不行就认怂引入外部存储。7. 一些我踩过坑之后的个人心得分页优化这个主题看起来知识点就那么几个但真正落地的时候坑往往不在SQL本身而在业务和技术的交叉点上。我见过最典型的翻车现场是技术团队花了一整周把深分页优化得漂漂亮亮结果产品一上新需求排序规则多了两个字段索引全部失效性能一夜回到解放前。所以我现在做分页优化第一步永远是反问产品经理“这个列表真的需要跳页吗排序规则将来会不会变”如果排序规则确实会变我会倾向于用游标分页而不是依赖固定索引的延迟关联因为游标分页对索引的依赖更轻排序字段变化时只需要改条件。还有一个小技巧是写分页SQL时尽量把主键放进排序条件。之前维护过一个交易流水列表业务上按create_time排序结果同一秒内有几百条记录分页时用户看到的数据一直在跳。加上id DESC作为次级排序后边界问题彻底消失。这个习惯成本极低收益却很实在。最后再分享一个排查顺序遇到分页慢别急着改SQL先EXPLAIN ANALYZE看一眼瓶颈在这三件事的哪一件——多扫描的行、多回表的次数、多花的排序内存。对症下药之后你会发现优化思路其实特别直白减少MySQL不得不做的无用功而已。