在开发联调环境里一切响应飞快数据量一上来曾经“能用”的查询瞬间变成性能瓶颈这类问题里最具代表性的就是分页查询。很多系统的线上故障不是被复杂统计拖垮的而是被一条简单的LIMIT 100000, 20拖垮的——这看起来不可思议却很常见。尤其是早年用MySQL搭起来的业务库单表几百万行到几千万行数据一旦涉及深分页那条曾经“还行”的SQL就像换了一台机器在跑。我早些年在一家做电商中台的公司销量表在各平台大促后轻轻松松突破千万级。后台订单管理页每翻一页接口就要卡两三秒客服一边骂系统一边清缓存导致问题雪上加霜。后来仔细排查才发现罪魁祸首几乎都是同一个深分页查询。这篇文章不打算讲那些教科书式的理论而是把我这几年在真实业务里踩过的坑、验证过的方案和优化后的数据变化结合常见业务场景重新梳理一遍。我会从“为什么慢”的根源讲起再给出行之有效的几种优化方案最后通过一个模拟项目的优化过程把这些手段串起来。如果你手头正好也有一个即将面临数据量增长的系统或者正在为慢分页接口发愁这篇文章应该能提供方向。1. 先把问题拆明白分页查询到底慢在哪里1.1 不仅仅是“取数据”慢而是“丢弃数据”慢很多人一提到分页慢第一反应是数据量大、索引没建好。但其实LIMIT 100000, 20这类深分页查询慢本质上是MySQL在做一种非常“笨”的操作先找到前100020条满足条件的记录再丢掉前100000条最后只把20条返回给客户端。整个过程中真正有用的只有最后那20条前面那10万条几乎全是为了“翻到这一页”而做的铺垫。在没有合适索引辅助的情况下这个“找前100020条”的操作会让InnoDB存储引擎全表扫描。即便有索引如果查询条件和索引匹配度不高依然会产生大量随机IO。随机IO对磁盘来说是最“伤”的操作因为每一次跳转都需要寻道时间。机械硬盘的寻道时间在毫秒级即使换成SSD随机读相比顺序读也有明显差距。这里可以拿一个简单的场景来理解想象你在一个有几百万本书的图书馆里管理员需要从第一本书开始数数到第100001本才把后面的20本拿给你。这个管理员再勤快也没办法跳过前面那10万本直接取最后的20本。1.2 MySQL的“取-排-丢”三步曲当一条带ORDER BY和LIMIT的SQL被执行时MySQL内部大致会经历下面几个过程根据查询条件筛选出满足条件的全部记录。如果条件能命中索引这个过程会快一些如果没有合适的索引就是全表扫描每一条记录都要过一遍。对筛选出的记录按ORDER BY字段排序。如果排序字段能用索引直接提供顺序就可以省去显式排序否则MySQL需要借助临时文件来做归并排序或堆排序。从排序结果中取出偏移量之后的所有记录再把偏移量之前的记录丢弃。这个步骤完全是浪费但因为SQL语义如此MySQL必须老老实实从头开始数。这有点像是工厂流水线商品从传送带一头进入走到指定位置才把需要的几个拿下来其他所有通过的商品都得“经过”一遍当通过成本很高时最后的“丢弃”动作就显得格外昂贵。1.3 覆盖索引能解决什么不能解决什么为了减少回表造成的随机IO很多人会想到利用覆盖索引——也就是让索引中包含查询需要的所有字段查询时直接从索引树里取数据避免再到聚簇索引主键索引里回查一次完整行记录。这个思路本身是正确的对查询效率的提升也往往是肉眼可见的。但覆盖索引对深分页问题只是“缓解”不是“根治”。它能让“找到前100020条记录”这一步走得更快因为索引树的扫描通常比全表扫描代价小很多可MySQL仍然需要从第一行开始数仍然需要丢掉前100000条只是把丢数据前的准备工作压缩了一下。光靠覆盖索引一个手段或者建几个普通索引解决不了海量数据下深分页的根本矛盾。真正要做的是换一种思路让自己尽量少做“无用功”。2. 两种行之有效的基础优化方案2.1 方案一延迟关联——先把主键找出来再回表取数据延迟关联Deferred Join的思路很直接分页的时候第一步先用覆盖索引查出当前页需要的主键ID第二步再用这些ID去关联完整表取出详细字段。这种方式能有效避免MySQL在深分页时对大字段进行无效回表。用一段SQL来说明。假设场景是一张订单表orders字段包括主键id、用户IDuser_id、下单时间order_time、订单金额amount、支付状态pay_status等。订单量已经有三千万行客服后台需要按时间倒序查看订单。原始写法可能是这样的SELECT id, user_id, order_time, amount, pay_status FROM orders WHERE pay_status 1 ORDER BY order_time DESC LIMIT 100000, 20;如果order_time上有索引看起来好像没问题但MySQL要先按索引过滤出所有pay_status 1的记录这是一次索引范围扫描然后按order_time排序如果顺序不一致还需要临时排序再跳过10万条回表取出100020条完整记录最后只留20条。整个过程笨重且浪费。优化成延迟关联的写法是这样的SELECT o.id, o.user_id, o.order_time, o.amount, o.pay_status FROM ( SELECT id FROM orders WHERE pay_status 1 ORDER BY order_time DESC LIMIT 100000, 20 ) AS tmp JOIN orders AS o ON tmp.id o.id ORDER BY o.order_time DESC;内层查询只查id搭配(pay_status, order_time, id)这样的复合索引可以完全走覆盖索引不需要回表。内层返回的只有20个主键ID外层再拿这20个ID去聚簇索引里直接取值。虽然外层的一次回表是不可避免的但回表次数从10万次降到了20次这个差距就是性能提升的最大来源。在我模拟的测试环境里三千万行数据下这条SQL的耗时从原来的1.2秒左右降到了100毫秒以内。效果立竿见影。2.2 方案二游标分页——不做深分页改记位置如果说延迟关联是在“不改变查询形态”的前提下做优化那么游标分页Keyset Pagination则是彻底改变了查询形态。它的核心思想很简单每一页请求都携带上一页最后一条记录的某个唯一标识然后用这个标识作为过滤条件把确定不需要的数据直接跳过。最典型的游标分页写法如下SELECT id, user_id, order_time, amount, pay_status FROM orders WHERE pay_status 1 AND order_time 2024-06-01 12:00:00 ORDER BY order_time DESC LIMIT 20;上一页返回的最后一条记录的order_time是2024-06-01 12:00:00下一页查询直接就从这个时间点以前开始取。因为order_time上有索引MySQL只需要定位到“书架”的指定位置然后顺着索引顺序往后取20条完全不需要从头数。但这里有一个重要前提排序字段必须是唯一且有序的。如果只用order_time排序当两条记录具有完全相同的order_time时翻页就会出现问题——上一页没取完的数据在下一页可能会被跳过或者重复出现。最稳妥的方式是使用“唯一字段 业务排序字段”的组合游标。例如按(order_time, id)复合排序那么下一页的过滤条件就要带上两个字段WHERE pay_status 1 AND (order_time 2024-06-01 12:00:00 OR (order_time 2024-06-01 12:00:00 AND id 100320)) ORDER BY order_time DESC, id DESC LIMIT 20;这种写法把排序字段组成了一个“联合游标”只要(order_time, id)上有复合索引MySQL就能准确定位到上一页的结束位置从这个位置开始继续向后取数据。2.3 两种方案怎么选延迟关联适合改动范围小、不想动前端交互逻辑的项目。它保留了页码跳转的能力用户仍然可以点击第100页、第1000页。缺点在于如果你真的翻到第100万页延迟关联也只能把损耗降低没办法完全消除“从头数”的开销所以它更适合中等深度的分页场景。游标分页是真正意义上的“无限翻页”方案不管往后翻多少页耗时都基本稳定。它的代价也很明显用户无法直接跳转到指定页数交互上比较适合“加载更多”“上一页/下一页”这类模式而不适合传统页码导航。现实业务里电商订单管理、内容流、搜索结果这类场景大多可以接受游标分页。像传统的表格型报表、后台列表页往往更依赖页码跳转这时候可以先用延迟关联过渡再配合后续章节提到的其他手段。提示游标分页还有一个问题在真实系统中经常被忽略——跨页数据变动。如果上一页返回了10条数据这期间新增了一条记录下一页的“位置”会整体下移可能出现重复数据。业务上如果对数据一致性要求极高需要考虑快照读或加锁方案。3. 当数据量再上一个台阶索引设计、分表分区与多级缓存3.1 联合索引的正确设计与“魔鬼细节”很多时候分页慢不是方案不对而是索引根本就没建对。比如WHERE pay_status 1 ORDER BY order_time DESC这条查询最合适的索引应该是(pay_status, order_time)而不是单独在order_time上建索引。在联合索引中MySQL会先按pay_status定位到所有需要的数据范围再在这个范围内用order_time排序如果两者组合成了索引的第二列那么排序就不需要额外做文件排序顺序读取就能直接返回结果。这是“索引即排序”的关键。单独在order_time上建索引MySQL需要先扫一遍索引找到所有满足pay_status 1的记录再回表确认各自的状态最后还得做一次排序。这里又多了回表又多了临时排序代价自然高。这条经验几乎适用于所有分页场景凡是查询条件里有等值条件又有排序字段优先考虑把等值字段放前面、排序字段放后面的联合索引。3.2 分表分区解决“单表太大”的极端场景表数据量过了几千万还能忍但如果到了几亿、几十亿的规模单表怎么都跑不动了。这种量级下分表或分区就成了必然选项。分区表能把一张大表在逻辑上拆成多个物理存储区域查询时可以根据分区键直接裁剪掉大量无用数据。比如按order_time做RANGE分区只查某一个月的数据时MySQL可以直接跳过其他月份的分区文件扫描量大幅下降。但分区方案也有自己的坑。如果分区键和业务查询条件不匹配比如只按order_time分区却经常按user_id查询MySQL可能无法有效裁剪分区最终一次查询扫遍所有分区文件反而比单表还慢。设计分区前一定要先统计线上的真实查询模式。分表是更彻底的做法。比如按用户ID哈希分成64张表把订单分别路由到对应分表里。传统业务的订单查询通常是按用户维度发起的所以按用户分表在架构上最自然。但分表带来的问题也不少跨表翻页变得困难排序必须在应用层做归并count需要聚合所有分表的统计结果运维复杂度也跟着指数级上升。除非数据量真的到了非分不可的地步我通常建议先做索引优化和读写分离把分表作为最后手段。3.3 缓存介入用Redis挡住最热的深分页页尾在一些实际项目里访问热度的分布极度不均匀。比如订单列表页用户通常翻前几页翻到20页以后的流量就很少了。最底部的页尾数据虽然偶尔有人访问但一旦触发就是一次深分页扫描成本非常高。对这类场景可以用Redis做多级缓存。比如把前N页的查询结果缓存起来热点请求直接走缓存针对最深的页尾设置一个失效时间较长的缓存因为它的数据变更频率反而比首页低很多。对我来说比较实用的一种做法是热点页缓存失效但深页缓存保留配合后台异步更新。也就是说当有人请求第100页时先查Redis如果没有就执行SQL并把结果写入缓存设置一个适中的过期时间。首页数据因为变化频繁缓存很容易失效但如果第100页的数据在业务上没有频繁变动这个缓存实际上能存活很久后续请求全部由Redis扛住数据库的深分页压力几乎为零。当年做订单管理后台时我用这种方法把数据库深分页的压力从原来的每天上百万次降到了几千次接口平均响应时间从1.5秒降到150毫秒以内。3.4 从方案对比看取舍在某模拟项目里原始查询耗时1.2秒优化后的几种方案对比如下方案实现复杂度平均耗时模拟环境适用场景不加优化的原始查询无约1200ms小数据量、低并发延迟关联低约80ms保留页码跳转的传统列表游标分页中稳定在50ms以内“加载更多”类交互覆盖索引 延迟关联低约60ms大多数中小型系统缓存介入中命中缓存时低于10ms访问热度集中、深页低频没有银弹。每一条优化路径都有它的适用边界需要结合业务形态去选择。4. 方案选型的思考与决策清单前面讲了一些具体手段但在真实项目中让人头疼的往往不是“不会写优化SQL”而是“不知道该往哪个方向投入”。我在实际做技术方案选型时一般会按下面的顺序梳理出一条决策路径数据量到底有多大是百万级、千万级还是亿级以上。百万级很多时候几条普通索引就能给出很可观的性能改善千万级适合用深分页优化、缓存和读写分离亿级以上大概率要走到分表、归档或者搜索引擎方案。查询模式是什么样的用户能不能接受“加载更多”的交互是否需要跳页查询条件的组合是固定的还是随意的这决定了你适合走游标分页、延迟关联还是全文检索引擎。数据库的QPS压力有多大如果数据库本身已经接近瓶颈做再多SQL优化也顶不住这时优先考虑缓存层和读写分离把读流量从主库剥离。团队能接受的维护成本是多少引入Elasticsearch或ClickHouse会带来新的运维压力但也能换来极大的查询灵活性分表能扛数据量但会让所有业务查询都变得复杂。这些年我见过太多团队一上来就分表结果业务代码里到处都在拼表名查询逻辑乱成一团也见过有的系统明明数据量只有几百万却因为一个联合索引没建对而天天出故障。技术选型要适度不是什么数据量都要上重型武器。注意在做分页优化前先做一次慢查询日志分析。很多时候慢SQL可能是缺了索引、、 统计信息过期或者查出的数据本身就是几万条大结果集。先排除这些低垂果实再投入重武器改造效率会高得多。针对常见的两类系统形态我给出的选择倾向是系统形态推荐路径理由传统后台管理系统联合索引 延迟关联 深页缓存需要页码跳转改造影响小高并发C端信息流游标分页 Redis缓存 必要时引入搜索引擎用户接受“加载更多”且查询条件复杂多变5. 分页优化工具集主键分页、归档表和COUNT优化5.1 主键分页的另类写法与风险有一种老开发者喜欢用的“奇技淫巧”直接把上一页最后一条记录的主键当成查询条件继续往下取。SELECT id, user_id, order_time, amount, pay_status FROM orders WHERE id 100000 AND pay_status 1 ORDER BY id ASC LIMIT 20;这种写法对“按主键自增顺序翻页”的场景极其高效因为主键索引天然有序MySQL可以直接定位到id 100000的位置向后取20条完事。问题是一旦业务排序不再是主键自增顺序这招就不灵了。如果需求是按order_time倒序但主键ID是自增的那么新旧数据主键顺序和业务顺序未必一致用主键翻页就会出现明显的逻辑错误。主键分页适合后台按创建顺序浏览、按月归档数据扫描等场景但不能把它当成通用方案。5.2 归档表大数据量的终极兜底手段很多业务数据是“热数据”和“冷数据”混杂的。三年前的订单已经完全没有查询热度但它们依然躺在那张大表里拖着每一查询的扫描成本。对这种数据定期把冷数据迁移到历史归档表是性价比非常高的优化。比如订单系统可以保留最近6个月的数据在热表超过6个月的按月迁移到归档表。热表体积降低索引效率提高深分页问题也会大幅缓解。应用层可以做一个透明路由先查热表热表没有再去归档表补数据。用户面对的还是同一套接口但数据库压力已经小了一个量级。归档表最大的坑是数据一致性。批量迁移过程中要确保不漏数据、不重复数据建议用分批小事务迁移并记录每一批的迁移位点中途失败可以续跑。5.3 别忽略COUNT(*)的成本很多分页接口除了返回当前页数据还会返回“总记录数”用来渲染分页页码。这个COUNT(*)在千万级大表上往往比分页查询本身还要昂贵。对InnoDB来说COUNT(*)需要对满足条件的行做一次完整统计扫描无法走索引快速跳过。通用的优化手段有几种不显示真实总数。用“返回前N页 是否还有更多”代替“第X页/共Y页”前后端配合改交互。这是游标分页最常见的处理方式。用估算值。如果保留页码导航可以用EXPLAIN里的估算行数或者定时统计总行数缓存到Redis接受一定的偏差。用额外的计数表。业务上如果确实需要精确总数可以维护一个计数器实时更新但这又引入了分布式一致性问题只在特定场景下使用。在这里我推荐对绝大多数业务场景执行“能不做COUNT就不做COUNT”的原则。如果硬要做也要做到“高频查询从缓存取低频查询直接查库”。6. 当SQL优化到尽头搜索引擎与OLAP分析引擎的降维打击6.1 为什么要“降维”处理有些查询场景已经不是优化SQL能解决的了。例如后台需要支持基于任意组合条件的“亿万行订单模糊搜索”或者需要跨多个维度实时分析。这种场景如果用MySQL硬扛不断在前端加筛选条件索引再多也扛不住——因为条件组合无穷多规划的索引可能根本覆盖不到某个组合。这时候应该考虑引入搜索引擎或OLAP分析引擎让MySQL只负责自己的主业务逼迫复杂查询转移到专门的组件上。一个业务典型场景可以这么设计订单数据通过异步任务同步到专用搜索引擎后台所有搜索、筛选、分页都由搜索引擎处理MySQL继续承载订单创建、状态更新这类写事务和高频主键查询。搜索接口的响应时间在百毫秒以内数据量涨到数亿也不怕。这里多加一句切忌把搜索引擎当作万能组件。它的数据一致性相对弱写入延迟大运维复杂度也高。只要原有的几百毫秒分页可以接受就没必要为“听起来更酷”买单。6.2 两类常用引擎选型对比引擎擅长的场景不适合的场景Elasticsearch全文搜索、复杂组合筛选、聚合分析、海量日志强事务、超高频点查ClickHouse海量数据分析、实时聚合、大数据量扫描点查、频繁更新我个人的选择习惯是以检索为主、需要毫秒级响应就选Elasticsearch以分析为主、需要批量扫描和聚合就选ClickHouse。两者也经常协同使用只是一般情况下没有那个必要。6.3 一个虚拟业务的落地复盘用一个模拟项目来说明。某跨平台订单中心后台原始订单数据保存在MySQL中超过三千万行。客服需要按订单号、手机号、订单状态、时间段等条件组合查询经常翻到几十页以后。当时的改造路径比较明确MySQL侧先做基础索引优化把(pay_status, order_time, id)联合索引加上前端交互从“页码跳转”改为“加载更多”后端改为游标分页配合状态字段排序高频热页数据加Redis缓存深页数据保留缓存兜底。后续为了支持公式化复杂筛选又引入了搜索引擎把后台筛选查询全部迁出MySQL。结果之后客服后台查询从平均2秒降到150毫秒以内数据库慢查询量从每天几百条降到几乎清零。这套改造只花了两周时间效果却持续稳定了很长时间。这种循序渐进、按需引入的改造步骤是我最推荐的做法。一上来就上Elasticsearch未必能解决SQL层面的基础健康问题反而会把系统复杂度带上去。7. 常见问题与排查技巧实录优化做多了总会碰到各种奇怪的问题。我把几个高频问题整理成速查表方便一线开发排查。现象可能原因排查思路索引加了却不生效查询条件写法不匹配索引最左前缀检查WHERE条件是否包含联合索引的第一列分页接口变慢但SQL单跑很快接口代码里多了循环查询、序列化开销大开启慢查询日志把真实接口SQL抓出来对比执行计划游标分页出现重复/缺失数据排序字段不唯一或排序字段顺序与索引不匹配检查排序字段是否包含唯一主键必要时改为复合排序翻到很靠后的页时仍然慢延迟关联没能完全消除深分页扫描成本考虑切换游标分页或增加深页缓存COUNT(*)和大分页组合导致接口超时两者相加的代价太高去掉精确COUNT改用估算值或“是否还有更多”分页排序字段类型不一致导致索引失效字段隐式转换或字符集排序规则不一致保持查询字段和索引字段类型、排序规则完全一致排查这类问题时我最常用的工具组合是EXPLAIN ANALYZEMySQL 8.0performance_schema。先用EXPLAIN看执行计划确认是否走了索引、有没有Using filesort再用EXPLAIN ANALYZE看实际执行时间占比定位耗时最大的阶段。不要让慢查询停留在“感觉慢”的层面直接用数据说话。7.1 典型问题一为什么索引建了还是不生效有一次某同学在业务群反馈订单表上建了(pay_status, order_time)联合索引可查询还是走全表扫描。我把他的SQL拿过来一看发现他是这样写的SELECT * FROM orders WHERE pay_status IN (0, 1, 2) ORDER BY order_time DESC LIMIT 20;这里的问题在于IN (0, 1, 2)几乎等于无过滤条件索引的第一列区分度极低MySQL优化器发现扫描整个联合索引比回表还贵干脆选择全表扫描。解决方式是pay_status的过滤条件不要包含接近全量的值或者是把order_time作为联合索引第一列把pay_status放后面。这提醒我们在设计联合索引时优先考虑过滤性强的等值条件。7.2 典型问题二按时间范围分页为什么还需要额外加索引还有一位同学说他自己用了游标分页每天都按order_time翻订单可查询到第5页就几秒钟。我看了一下他的表结构order_time上是有普通索引的但查询条件是:WHERE user_id 12345 AND order_time 2024-06-01 12:00:00 ORDER BY order_time DESC LIMIT 20;单独在order_time建索引不代表MySQL能利用它完成定位。这里user_id 12345是过滤条件order_time ...是范围条件。MySQL要同时满足这两点最好的结构是(user_id, order_time)复合索引。如果只有order_time单列索引MySQL必须先按索引扫描所有小于这个时间的记录再在内存中过滤user_id过程中数据量一大自然就慢。这也是深分页场景中最容易踩的坑——“有索引”不等于“索引有效”。7.3 典型问题三深分页时LIMIT偏移量很大但总是很快有时候深分页查询并不慢这是因为条件本身很有过滤性比如user_id 12345的订单量只有几百条翻到第100页也就几千条自然快。要准确判断慢不慢不能单看页码数字更要看“偏移量对应的扫描量”。如果页面序号已经到几千页但过滤条件仍然精确到某一个用户或某一种状态那扫描量其实并不大。真正会出问题的是“全量条件下的深度分页”例如不带用户过滤条件、全订单列表按时间排序拉到很后面几页。7.4 典型问题四数据膨胀后索引失效需要重建数据量上来后某些老索引会失效或变得低效。最常见的情况是当初建索引时数据量小区分度足够后续数据膨胀某个字段的区分度下降比如pay_status字段0和1占了99%导致优化器判定走索引还不如全表扫描。遇到这种情况不能只靠优化SQL。把慢查询日志中执行计划不稳定、时而走索引时而不走索引的SQL挑出来分析字段区分度变化考虑拆索引或重新设计查询条件。有些表每隔一段时间做一次索引重建和统计信息更新是有必要的。8. 沿着这些思路往前走一步多年和分页查询打交道我个人最深的体会是分页优化的本质不是想方设法让MySQL执行得更快而是想办法让它少干活。延迟关联减少回表次数游标分页减少扫描行数缓存减少数据库访问次数归档表减少扫描总体积——每一条路径的底层逻辑都是“减少无效工作”。如果你正在做一个新系统一开始就要把分页交互设计好。能接受“加载更多”就用游标分页从一开始就别引入页码跳转必须保留页码导航的预留深页查询的缓存策略和索引设计。很多团队是在业务已经跑了一年、数据量翻了20倍之后才开始优化到那时每一次试错都要用线上故障来买单。我始终认为SQL优化能力是一线开发者的基本功即便搜索引擎和分布式架构再流行MySQL依然是绝大多数中小企业的数据底座。你把上面的这些方案亲手在本地环境测一遍把执行计划、耗时数据记录下来形成自己的调优手记远比看十篇文章有用。做一个能扛住亿级数据的分页接口是一件能获得极大满足感的事情。尤其是看到原来三秒才出的页面变成毫秒级响应时你会觉得这一切折腾都值得。 
长河君 · 2026/10/21 18:00:37 · 阅读 1.2k MySQL深分页性能优化：从LIMIT慢查询到延迟关联与游标分页
在开发联调环境里一切响应飞快数据量一上来曾经“能用”的查询瞬间变成性能瓶颈这类问题里最具代表性的就是分页查询。很多系统的线上故障不是被复杂统计拖垮的而是被一条简单的LIMIT 100000, 20拖垮的——这看起来不可思议却很常见。尤其是早年用MySQL搭起来的业务库单表几百万行到几千万行数据一旦涉及深分页那条曾经“还行”的SQL就像换了一台机器在跑。我早些年在一家做电商中台的公司销量表在各平台大促后轻轻松松突破千万级。后台订单管理页每翻一页接口就要卡两三秒客服一边骂系统一边清缓存导致问题雪上加霜。后来仔细排查才发现罪魁祸首几乎都是同一个深分页查询。这篇文章不打算讲那些教科书式的理论而是把我这几年在真实业务里踩过的坑、验证过的方案和优化后的数据变化结合常见业务场景重新梳理一遍。我会从“为什么慢”的根源讲起再给出行之有效的几种优化方案最后通过一个模拟项目的优化过程把这些手段串起来。如果你手头正好也有一个即将面临数据量增长的系统或者正在为慢分页接口发愁这篇文章应该能提供方向。1. 先把问题拆明白分页查询到底慢在哪里1.1 不仅仅是“取数据”慢而是“丢弃数据”慢很多人一提到分页慢第一反应是数据量大、索引没建好。但其实LIMIT 100000, 20这类深分页查询慢本质上是MySQL在做一种非常“笨”的操作先找到前100020条满足条件的记录再丢掉前100000条最后只把20条返回给客户端。整个过程中真正有用的只有最后那20条前面那10万条几乎全是为了“翻到这一页”而做的铺垫。在没有合适索引辅助的情况下这个“找前100020条”的操作会让InnoDB存储引擎全表扫描。即便有索引如果查询条件和索引匹配度不高依然会产生大量随机IO。随机IO对磁盘来说是最“伤”的操作因为每一次跳转都需要寻道时间。机械硬盘的寻道时间在毫秒级即使换成SSD随机读相比顺序读也有明显差距。这里可以拿一个简单的场景来理解想象你在一个有几百万本书的图书馆里管理员需要从第一本书开始数数到第100001本才把后面的20本拿给你。这个管理员再勤快也没办法跳过前面那10万本直接取最后的20本。1.2 MySQL的“取-排-丢”三步曲当一条带ORDER BY和LIMIT的SQL被执行时MySQL内部大致会经历下面几个过程根据查询条件筛选出满足条件的全部记录。如果条件能命中索引这个过程会快一些如果没有合适的索引就是全表扫描每一条记录都要过一遍。对筛选出的记录按ORDER BY字段排序。如果排序字段能用索引直接提供顺序就可以省去显式排序否则MySQL需要借助临时文件来做归并排序或堆排序。从排序结果中取出偏移量之后的所有记录再把偏移量之前的记录丢弃。这个步骤完全是浪费但因为SQL语义如此MySQL必须老老实实从头开始数。这有点像是工厂流水线商品从传送带一头进入走到指定位置才把需要的几个拿下来其他所有通过的商品都得“经过”一遍当通过成本很高时最后的“丢弃”动作就显得格外昂贵。1.3 覆盖索引能解决什么不能解决什么为了减少回表造成的随机IO很多人会想到利用覆盖索引——也就是让索引中包含查询需要的所有字段查询时直接从索引树里取数据避免再到聚簇索引主键索引里回查一次完整行记录。这个思路本身是正确的对查询效率的提升也往往是肉眼可见的。但覆盖索引对深分页问题只是“缓解”不是“根治”。它能让“找到前100020条记录”这一步走得更快因为索引树的扫描通常比全表扫描代价小很多可MySQL仍然需要从第一行开始数仍然需要丢掉前100000条只是把丢数据前的准备工作压缩了一下。光靠覆盖索引一个手段或者建几个普通索引解决不了海量数据下深分页的根本矛盾。真正要做的是换一种思路让自己尽量少做“无用功”。2. 两种行之有效的基础优化方案2.1 方案一延迟关联——先把主键找出来再回表取数据延迟关联Deferred Join的思路很直接分页的时候第一步先用覆盖索引查出当前页需要的主键ID第二步再用这些ID去关联完整表取出详细字段。这种方式能有效避免MySQL在深分页时对大字段进行无效回表。用一段SQL来说明。假设场景是一张订单表orders字段包括主键id、用户IDuser_id、下单时间order_time、订单金额amount、支付状态pay_status等。订单量已经有三千万行客服后台需要按时间倒序查看订单。原始写法可能是这样的SELECT id, user_id, order_time, amount, pay_status FROM orders WHERE pay_status 1 ORDER BY order_time DESC LIMIT 100000, 20;如果order_time上有索引看起来好像没问题但MySQL要先按索引过滤出所有pay_status 1的记录这是一次索引范围扫描然后按order_time排序如果顺序不一致还需要临时排序再跳过10万条回表取出100020条完整记录最后只留20条。整个过程笨重且浪费。优化成延迟关联的写法是这样的SELECT o.id, o.user_id, o.order_time, o.amount, o.pay_status FROM ( SELECT id FROM orders WHERE pay_status 1 ORDER BY order_time DESC LIMIT 100000, 20 ) AS tmp JOIN orders AS o ON tmp.id o.id ORDER BY o.order_time DESC;内层查询只查id搭配(pay_status, order_time, id)这样的复合索引可以完全走覆盖索引不需要回表。内层返回的只有20个主键ID外层再拿这20个ID去聚簇索引里直接取值。虽然外层的一次回表是不可避免的但回表次数从10万次降到了20次这个差距就是性能提升的最大来源。在我模拟的测试环境里三千万行数据下这条SQL的耗时从原来的1.2秒左右降到了100毫秒以内。效果立竿见影。2.2 方案二游标分页——不做深分页改记位置如果说延迟关联是在“不改变查询形态”的前提下做优化那么游标分页Keyset Pagination则是彻底改变了查询形态。它的核心思想很简单每一页请求都携带上一页最后一条记录的某个唯一标识然后用这个标识作为过滤条件把确定不需要的数据直接跳过。最典型的游标分页写法如下SELECT id, user_id, order_time, amount, pay_status FROM orders WHERE pay_status 1 AND order_time 2024-06-01 12:00:00 ORDER BY order_time DESC LIMIT 20;上一页返回的最后一条记录的order_time是2024-06-01 12:00:00下一页查询直接就从这个时间点以前开始取。因为order_time上有索引MySQL只需要定位到“书架”的指定位置然后顺着索引顺序往后取20条完全不需要从头数。但这里有一个重要前提排序字段必须是唯一且有序的。如果只用order_time排序当两条记录具有完全相同的order_time时翻页就会出现问题——上一页没取完的数据在下一页可能会被跳过或者重复出现。最稳妥的方式是使用“唯一字段 业务排序字段”的组合游标。例如按(order_time, id)复合排序那么下一页的过滤条件就要带上两个字段WHERE pay_status 1 AND (order_time 2024-06-01 12:00:00 OR (order_time 2024-06-01 12:00:00 AND id 100320)) ORDER BY order_time DESC, id DESC LIMIT 20;这种写法把排序字段组成了一个“联合游标”只要(order_time, id)上有复合索引MySQL就能准确定位到上一页的结束位置从这个位置开始继续向后取数据。2.3 两种方案怎么选延迟关联适合改动范围小、不想动前端交互逻辑的项目。它保留了页码跳转的能力用户仍然可以点击第100页、第1000页。缺点在于如果你真的翻到第100万页延迟关联也只能把损耗降低没办法完全消除“从头数”的开销所以它更适合中等深度的分页场景。游标分页是真正意义上的“无限翻页”方案不管往后翻多少页耗时都基本稳定。它的代价也很明显用户无法直接跳转到指定页数交互上比较适合“加载更多”“上一页/下一页”这类模式而不适合传统页码导航。现实业务里电商订单管理、内容流、搜索结果这类场景大多可以接受游标分页。像传统的表格型报表、后台列表页往往更依赖页码跳转这时候可以先用延迟关联过渡再配合后续章节提到的其他手段。提示游标分页还有一个问题在真实系统中经常被忽略——跨页数据变动。如果上一页返回了10条数据这期间新增了一条记录下一页的“位置”会整体下移可能出现重复数据。业务上如果对数据一致性要求极高需要考虑快照读或加锁方案。3. 当数据量再上一个台阶索引设计、分表分区与多级缓存3.1 联合索引的正确设计与“魔鬼细节”很多时候分页慢不是方案不对而是索引根本就没建对。比如WHERE pay_status 1 ORDER BY order_time DESC这条查询最合适的索引应该是(pay_status, order_time)而不是单独在order_time上建索引。在联合索引中MySQL会先按pay_status定位到所有需要的数据范围再在这个范围内用order_time排序如果两者组合成了索引的第二列那么排序就不需要额外做文件排序顺序读取就能直接返回结果。这是“索引即排序”的关键。单独在order_time上建索引MySQL需要先扫一遍索引找到所有满足pay_status 1的记录再回表确认各自的状态最后还得做一次排序。这里又多了回表又多了临时排序代价自然高。这条经验几乎适用于所有分页场景凡是查询条件里有等值条件又有排序字段优先考虑把等值字段放前面、排序字段放后面的联合索引。3.2 分表分区解决“单表太大”的极端场景表数据量过了几千万还能忍但如果到了几亿、几十亿的规模单表怎么都跑不动了。这种量级下分表或分区就成了必然选项。分区表能把一张大表在逻辑上拆成多个物理存储区域查询时可以根据分区键直接裁剪掉大量无用数据。比如按order_time做RANGE分区只查某一个月的数据时MySQL可以直接跳过其他月份的分区文件扫描量大幅下降。但分区方案也有自己的坑。如果分区键和业务查询条件不匹配比如只按order_time分区却经常按user_id查询MySQL可能无法有效裁剪分区最终一次查询扫遍所有分区文件反而比单表还慢。设计分区前一定要先统计线上的真实查询模式。分表是更彻底的做法。比如按用户ID哈希分成64张表把订单分别路由到对应分表里。传统业务的订单查询通常是按用户维度发起的所以按用户分表在架构上最自然。但分表带来的问题也不少跨表翻页变得困难排序必须在应用层做归并count需要聚合所有分表的统计结果运维复杂度也跟着指数级上升。除非数据量真的到了非分不可的地步我通常建议先做索引优化和读写分离把分表作为最后手段。3.3 缓存介入用Redis挡住最热的深分页页尾在一些实际项目里访问热度的分布极度不均匀。比如订单列表页用户通常翻前几页翻到20页以后的流量就很少了。最底部的页尾数据虽然偶尔有人访问但一旦触发就是一次深分页扫描成本非常高。对这类场景可以用Redis做多级缓存。比如把前N页的查询结果缓存起来热点请求直接走缓存针对最深的页尾设置一个失效时间较长的缓存因为它的数据变更频率反而比首页低很多。对我来说比较实用的一种做法是热点页缓存失效但深页缓存保留配合后台异步更新。也就是说当有人请求第100页时先查Redis如果没有就执行SQL并把结果写入缓存设置一个适中的过期时间。首页数据因为变化频繁缓存很容易失效但如果第100页的数据在业务上没有频繁变动这个缓存实际上能存活很久后续请求全部由Redis扛住数据库的深分页压力几乎为零。当年做订单管理后台时我用这种方法把数据库深分页的压力从原来的每天上百万次降到了几千次接口平均响应时间从1.5秒降到150毫秒以内。3.4 从方案对比看取舍在某模拟项目里原始查询耗时1.2秒优化后的几种方案对比如下方案实现复杂度平均耗时模拟环境适用场景不加优化的原始查询无约1200ms小数据量、低并发延迟关联低约80ms保留页码跳转的传统列表游标分页中稳定在50ms以内“加载更多”类交互覆盖索引 延迟关联低约60ms大多数中小型系统缓存介入中命中缓存时低于10ms访问热度集中、深页低频没有银弹。每一条优化路径都有它的适用边界需要结合业务形态去选择。4. 方案选型的思考与决策清单前面讲了一些具体手段但在真实项目中让人头疼的往往不是“不会写优化SQL”而是“不知道该往哪个方向投入”。我在实际做技术方案选型时一般会按下面的顺序梳理出一条决策路径数据量到底有多大是百万级、千万级还是亿级以上。百万级很多时候几条普通索引就能给出很可观的性能改善千万级适合用深分页优化、缓存和读写分离亿级以上大概率要走到分表、归档或者搜索引擎方案。查询模式是什么样的用户能不能接受“加载更多”的交互是否需要跳页查询条件的组合是固定的还是随意的这决定了你适合走游标分页、延迟关联还是全文检索引擎。数据库的QPS压力有多大如果数据库本身已经接近瓶颈做再多SQL优化也顶不住这时优先考虑缓存层和读写分离把读流量从主库剥离。团队能接受的维护成本是多少引入Elasticsearch或ClickHouse会带来新的运维压力但也能换来极大的查询灵活性分表能扛数据量但会让所有业务查询都变得复杂。这些年我见过太多团队一上来就分表结果业务代码里到处都在拼表名查询逻辑乱成一团也见过有的系统明明数据量只有几百万却因为一个联合索引没建对而天天出故障。技术选型要适度不是什么数据量都要上重型武器。注意在做分页优化前先做一次慢查询日志分析。很多时候慢SQL可能是缺了索引、、 统计信息过期或者查出的数据本身就是几万条大结果集。先排除这些低垂果实再投入重武器改造效率会高得多。针对常见的两类系统形态我给出的选择倾向是系统形态推荐路径理由传统后台管理系统联合索引 延迟关联 深页缓存需要页码跳转改造影响小高并发C端信息流游标分页 Redis缓存 必要时引入搜索引擎用户接受“加载更多”且查询条件复杂多变5. 分页优化工具集主键分页、归档表和COUNT优化5.1 主键分页的另类写法与风险有一种老开发者喜欢用的“奇技淫巧”直接把上一页最后一条记录的主键当成查询条件继续往下取。SELECT id, user_id, order_time, amount, pay_status FROM orders WHERE id 100000 AND pay_status 1 ORDER BY id ASC LIMIT 20;这种写法对“按主键自增顺序翻页”的场景极其高效因为主键索引天然有序MySQL可以直接定位到id 100000的位置向后取20条完事。问题是一旦业务排序不再是主键自增顺序这招就不灵了。如果需求是按order_time倒序但主键ID是自增的那么新旧数据主键顺序和业务顺序未必一致用主键翻页就会出现明显的逻辑错误。主键分页适合后台按创建顺序浏览、按月归档数据扫描等场景但不能把它当成通用方案。5.2 归档表大数据量的终极兜底手段很多业务数据是“热数据”和“冷数据”混杂的。三年前的订单已经完全没有查询热度但它们依然躺在那张大表里拖着每一查询的扫描成本。对这种数据定期把冷数据迁移到历史归档表是性价比非常高的优化。比如订单系统可以保留最近6个月的数据在热表超过6个月的按月迁移到归档表。热表体积降低索引效率提高深分页问题也会大幅缓解。应用层可以做一个透明路由先查热表热表没有再去归档表补数据。用户面对的还是同一套接口但数据库压力已经小了一个量级。归档表最大的坑是数据一致性。批量迁移过程中要确保不漏数据、不重复数据建议用分批小事务迁移并记录每一批的迁移位点中途失败可以续跑。5.3 别忽略COUNT(*)的成本很多分页接口除了返回当前页数据还会返回“总记录数”用来渲染分页页码。这个COUNT(*)在千万级大表上往往比分页查询本身还要昂贵。对InnoDB来说COUNT(*)需要对满足条件的行做一次完整统计扫描无法走索引快速跳过。通用的优化手段有几种不显示真实总数。用“返回前N页 是否还有更多”代替“第X页/共Y页”前后端配合改交互。这是游标分页最常见的处理方式。用估算值。如果保留页码导航可以用EXPLAIN里的估算行数或者定时统计总行数缓存到Redis接受一定的偏差。用额外的计数表。业务上如果确实需要精确总数可以维护一个计数器实时更新但这又引入了分布式一致性问题只在特定场景下使用。在这里我推荐对绝大多数业务场景执行“能不做COUNT就不做COUNT”的原则。如果硬要做也要做到“高频查询从缓存取低频查询直接查库”。6. 当SQL优化到尽头搜索引擎与OLAP分析引擎的降维打击6.1 为什么要“降维”处理有些查询场景已经不是优化SQL能解决的了。例如后台需要支持基于任意组合条件的“亿万行订单模糊搜索”或者需要跨多个维度实时分析。这种场景如果用MySQL硬扛不断在前端加筛选条件索引再多也扛不住——因为条件组合无穷多规划的索引可能根本覆盖不到某个组合。这时候应该考虑引入搜索引擎或OLAP分析引擎让MySQL只负责自己的主业务逼迫复杂查询转移到专门的组件上。一个业务典型场景可以这么设计订单数据通过异步任务同步到专用搜索引擎后台所有搜索、筛选、分页都由搜索引擎处理MySQL继续承载订单创建、状态更新这类写事务和高频主键查询。搜索接口的响应时间在百毫秒以内数据量涨到数亿也不怕。这里多加一句切忌把搜索引擎当作万能组件。它的数据一致性相对弱写入延迟大运维复杂度也高。只要原有的几百毫秒分页可以接受就没必要为“听起来更酷”买单。6.2 两类常用引擎选型对比引擎擅长的场景不适合的场景Elasticsearch全文搜索、复杂组合筛选、聚合分析、海量日志强事务、超高频点查ClickHouse海量数据分析、实时聚合、大数据量扫描点查、频繁更新我个人的选择习惯是以检索为主、需要毫秒级响应就选Elasticsearch以分析为主、需要批量扫描和聚合就选ClickHouse。两者也经常协同使用只是一般情况下没有那个必要。6.3 一个虚拟业务的落地复盘用一个模拟项目来说明。某跨平台订单中心后台原始订单数据保存在MySQL中超过三千万行。客服需要按订单号、手机号、订单状态、时间段等条件组合查询经常翻到几十页以后。当时的改造路径比较明确MySQL侧先做基础索引优化把(pay_status, order_time, id)联合索引加上前端交互从“页码跳转”改为“加载更多”后端改为游标分页配合状态字段排序高频热页数据加Redis缓存深页数据保留缓存兜底。后续为了支持公式化复杂筛选又引入了搜索引擎把后台筛选查询全部迁出MySQL。结果之后客服后台查询从平均2秒降到150毫秒以内数据库慢查询量从每天几百条降到几乎清零。这套改造只花了两周时间效果却持续稳定了很长时间。这种循序渐进、按需引入的改造步骤是我最推荐的做法。一上来就上Elasticsearch未必能解决SQL层面的基础健康问题反而会把系统复杂度带上去。7. 常见问题与排查技巧实录优化做多了总会碰到各种奇怪的问题。我把几个高频问题整理成速查表方便一线开发排查。现象可能原因排查思路索引加了却不生效查询条件写法不匹配索引最左前缀检查WHERE条件是否包含联合索引的第一列分页接口变慢但SQL单跑很快接口代码里多了循环查询、序列化开销大开启慢查询日志把真实接口SQL抓出来对比执行计划游标分页出现重复/缺失数据排序字段不唯一或排序字段顺序与索引不匹配检查排序字段是否包含唯一主键必要时改为复合排序翻到很靠后的页时仍然慢延迟关联没能完全消除深分页扫描成本考虑切换游标分页或增加深页缓存COUNT(*)和大分页组合导致接口超时两者相加的代价太高去掉精确COUNT改用估算值或“是否还有更多”分页排序字段类型不一致导致索引失效字段隐式转换或字符集排序规则不一致保持查询字段和索引字段类型、排序规则完全一致排查这类问题时我最常用的工具组合是EXPLAIN ANALYZEMySQL 8.0performance_schema。先用EXPLAIN看执行计划确认是否走了索引、有没有Using filesort再用EXPLAIN ANALYZE看实际执行时间占比定位耗时最大的阶段。不要让慢查询停留在“感觉慢”的层面直接用数据说话。7.1 典型问题一为什么索引建了还是不生效有一次某同学在业务群反馈订单表上建了(pay_status, order_time)联合索引可查询还是走全表扫描。我把他的SQL拿过来一看发现他是这样写的SELECT * FROM orders WHERE pay_status IN (0, 1, 2) ORDER BY order_time DESC LIMIT 20;这里的问题在于IN (0, 1, 2)几乎等于无过滤条件索引的第一列区分度极低MySQL优化器发现扫描整个联合索引比回表还贵干脆选择全表扫描。解决方式是pay_status的过滤条件不要包含接近全量的值或者是把order_time作为联合索引第一列把pay_status放后面。这提醒我们在设计联合索引时优先考虑过滤性强的等值条件。7.2 典型问题二按时间范围分页为什么还需要额外加索引还有一位同学说他自己用了游标分页每天都按order_time翻订单可查询到第5页就几秒钟。我看了一下他的表结构order_time上是有普通索引的但查询条件是:WHERE user_id 12345 AND order_time 2024-06-01 12:00:00 ORDER BY order_time DESC LIMIT 20;单独在order_time建索引不代表MySQL能利用它完成定位。这里user_id 12345是过滤条件order_time ...是范围条件。MySQL要同时满足这两点最好的结构是(user_id, order_time)复合索引。如果只有order_time单列索引MySQL必须先按索引扫描所有小于这个时间的记录再在内存中过滤user_id过程中数据量一大自然就慢。这也是深分页场景中最容易踩的坑——“有索引”不等于“索引有效”。7.3 典型问题三深分页时LIMIT偏移量很大但总是很快有时候深分页查询并不慢这是因为条件本身很有过滤性比如user_id 12345的订单量只有几百条翻到第100页也就几千条自然快。要准确判断慢不慢不能单看页码数字更要看“偏移量对应的扫描量”。如果页面序号已经到几千页但过滤条件仍然精确到某一个用户或某一种状态那扫描量其实并不大。真正会出问题的是“全量条件下的深度分页”例如不带用户过滤条件、全订单列表按时间排序拉到很后面几页。7.4 典型问题四数据膨胀后索引失效需要重建数据量上来后某些老索引会失效或变得低效。最常见的情况是当初建索引时数据量小区分度足够后续数据膨胀某个字段的区分度下降比如pay_status字段0和1占了99%导致优化器判定走索引还不如全表扫描。遇到这种情况不能只靠优化SQL。把慢查询日志中执行计划不稳定、时而走索引时而不走索引的SQL挑出来分析字段区分度变化考虑拆索引或重新设计查询条件。有些表每隔一段时间做一次索引重建和统计信息更新是有必要的。8. 沿着这些思路往前走一步多年和分页查询打交道我个人最深的体会是分页优化的本质不是想方设法让MySQL执行得更快而是想办法让它少干活。延迟关联减少回表次数游标分页减少扫描行数缓存减少数据库访问次数归档表减少扫描总体积——每一条路径的底层逻辑都是“减少无效工作”。如果你正在做一个新系统一开始就要把分页交互设计好。能接受“加载更多”就用游标分页从一开始就别引入页码跳转必须保留页码导航的预留深页查询的缓存策略和索引设计。很多团队是在业务已经跑了一年、数据量翻了20倍之后才开始优化到那时每一次试错都要用线上故障来买单。我始终认为SQL优化能力是一线开发者的基本功即便搜索引擎和分布式架构再流行MySQL依然是绝大多数中小企业的数据底座。你把上面的这些方案亲手在本地环境测一遍把执行计划、耗时数据记录下来形成自己的调优手记远比看十篇文章有用。做一个能扛住亿级数据的分页接口是一件能获得极大满足感的事情。尤其是看到原来三秒才出的页面变成毫秒级响应时你会觉得这一切折腾都值得。