企业官网建设流程全解析

MySQL查询的原始返回顺序与limit分页优化

聊到MySQL分页，很多人第一反应就是“SELECT ... LIMIT offset, size”，然后就没下文了。真正在线上踩过坑的人会明白，分页这件事远没有看起来那么简单。尤其是当你知道MySQL查询其实没有“默认顺序”这种东西的时候，你会开始重新审视你写的每一句SQL：为什么这里返回的顺序时对时错？为什么用户反馈翻页翻着翻着出现了重复数据？为什么数据量一上来，最后一页的接口要跑好几秒？

这篇文章我想老老实实把两件事讲透：第一，MySQL查询的原始返回顺序到底是怎么来的，能不能依赖；第二，基于limit的分页在真实业务里有哪些坑，以及主流的优化方案分别适用什么场景。我会尽量用我在实际项目里遇到过的现象和排查过程来说，不堆理论，只讲能用的东西。内容适合正在写业务SQL、被深分页困扰的开发者，也适合准备面试想系统梳理这块知识的朋友。

1. 原始返回顺序：MySQL真的没有“默认排序”吗

1.1 为什么“不写ORDER BY”的顺序是不可靠的

很多初学者会有一个错觉：我查出来的数据，每次返回的顺序都一样，那这个顺序是不是就是数据的“默认顺序”？答案是不一定。MySQL在没有ORDER BY的情况下，返回顺序取决于存储引擎怎么把数据读出来，而这个读取路径受很多因素影响：表的数据量、索引的使用情况、执行计划里选择了哪个索引、缓冲区是否命中、并发插入的物理位置等等。

我举个例子。假设你有一张用户表，主键是自增id，你执行：

SELECT id, username FROM users WHERE status = 1;

如果status字段上没有索引，MySQL大概率会走主键索引的全扫描，数据按主键顺序读出来，这时候看起来“像”是按id排序。但是一旦你在status上建了索引，执行计划可能改为先通过二级索引找到满足条件的记录，再去主键索引回表取其他字段。此时返回顺序就变成二级索引的叶子节点顺序，而二级索引的顺序是由索引键决定的，和主键id顺序没有任何关系。

更麻烦的场景是使用了文件排序或者临时表。比如查询里有GROUP BY、DISTINCT、UNION这类操作，MySQL可能先把结果放进临时表，再吐给你，顺序完全取决于临时表的实现方式。所以你会发现同一个SQL，数据量小的时候顺序“挺正常”，数据量大了之后顺序就变了。这不是玄学，是执行计划和数据分布变了。

1.2 InnoDB存储引擎下的读取路径与顺序真相

我们日常用得最多的就是InnoDB引擎。InnoDB的数据存储结构是B+树，聚簇索引的叶子节点保存了整行数据，二级索引的叶子节点保存了索引键和主键值。查询返回的记录，实际上是存储引擎沿着B+树扫描得到的结果。

在没有ORDER BY的情况下，MySQL优化器会选择它认为“代价最低”的路径来获取数据。常见的有几种：

• 全表扫描：直接从聚簇索引的第一个叶子节点开始，顺序读完所有叶子节点，这时候输出顺序近似等于主键顺序。
• 索引范围扫描：如果WHERE条件能用到二级索引，InnoDB先扫描二级索引，找到所有满足条件的记录，然后通过主键回表获取完整行。此时输出顺序是二级索引键的顺序。
• 索引覆盖扫描：如果查询的列全部在索引里，不需要回表，直接按二级索引叶子节点顺序输出。
• 多表连接：驱动表的扫描顺序会直接影响最终结果集的顺序。

一张表经过长时间增删改之后，聚簇索引的叶子节点也会发生页分裂，物理顺序和逻辑顺序会逐渐不一致。因此即使每次都走主键索引全扫描，直接返回的顺序也可能在不同时间点、不同数据量下不一样。

这里有个关键认知必须建立：SQL的返回顺序，只有显式ORDER BY才能保证。其他任何情况下，MySQL都没有义务给你一个稳定的顺序。你把这条规则记牢，后续所有的分页问题就都好理解了。

1.3 没有稳定顺序时，分页会出现什么灾难

假设你没有ORDER BY，只写：

SELECT id, username FROM users LIMIT 20;

这条SQL第一次执行返回的是前20条，第二次执行可能还是这20条，因为数据没有变化，执行计划也没有变化。但一旦表里发生了一行插入、一行删除，或者某个统计信息更新导致执行计划变化，第二次执行返回的20条就可能和第一次完全不同。对分页来说，后果就是第一页和第二页出现重复记录，或者某些记录永远查不到。

我在一个用户列表功能里遇到过这种情况。运营同事反馈说，翻到第三页的时候，有一条数据在第二页已经看过了，再往后翻又出现了一遍。排查到最后才发现，列表查询压根没写ORDER BY，只是靠LIMIT控制条数。后来补上了按主键排序才彻底解决。这不是个例，很多“页面上数据乱跳”的Bug，根因都是这个。

所以分页优化的前提条件，永远是“先有稳定排序，再谈性能优化”。顺序都不稳定，优化再快也没有意义。

2. LIMIT深分页的性能诅咒：为什么越翻越慢

2.1 LIMIT offset, size的工作原理：被跳过的数据不是免费的

LIMIT分页的写法有两种等价形式：

SELECT * FROM users ORDER BY id LIMIT 20; SELECT * FROM users ORDER BY id LIMIT 0, 20;

第二种写法里的0是偏移量，20是返回的行数。很多人的误解在于，认为MySQL从第0行开始数，数到第20条，然后取出来，跳过前面的0条，所以很快。但实际执行过程完全不是这样。

MySQL处理LIMIT offset, size时，需要先把前offset条符合条件的记录完整读取出来，然后逐一丢弃，只保留最后size条返回给客户端。假如查询是LIMIT 1000000, 20，MySQL要读取1000020行数据到内存里，再把前面的1000000行扔掉。这期间还要算上回表的代价、排序的代价、网络传输的代价。页数越深，offset越大，读取的行数越多，查询自然就越慢。

你可以做一个简单实验，在百万级数据的表上做如下对比查询：

SELECT id, title FROM articles ORDER BY id LIMIT 20; SELECT id, title FROM articles ORDER BY id LIMIT 100000, 20;

你会发现后者耗时通常是前者的几十倍甚至上百倍。这就是深分页的性能诅咒。

2.2 深分页慢的三个真正瓶颈

深分页很慢，表面上是“数据量大”，但仔细拆解，瓶颈其实是三个：

第一，回表次数太多。二级索引里只存索引键和主键值，你要查的完整行数据在聚簇索引上。MySQL需要拿着二级索引里的每条主键，再回聚簇索引查一次。offset越大，需要回表的记录就越多，100万条记录回表100万次，这个代价非常可观。

第二，排序代价被放大。如果ORDER BY的字段不是索引能覆盖的，MySQL要用filesort对参与排序的所有数据排序。如果顺序无所谓，可能还稍微好一点；但业务上分页通常要ORDER BY，一旦排序的不是索引字段，数据的读取、排序、丢弃流程全走一遍，深分页自然慢。

第三，无效的数据传输。MySQL要把offset+size条记录全都从存储引擎层读出来，即使其中offset条是被丢弃的，这个读取过程依然发生。这些被丢弃的数据全部浪费了I/O和CPU，越深越亏。

2.3 业务侧感知到的“故障”：接口超时、内存压力、数据库QPS飙升

性能问题最终会传导到业务侧。线上常见的现象是：列表接口前几页响应速度正常，从第几十页开始响应时间指数级上升，最后直接超时。

与此同时，数据库服务器的监控上能看到两个明显的异常指标。第一个是逻辑读激增，因为深分页要扫描和丢弃大量记录，缓冲池的命中率下降，磁盘I/O升高。第二个是临时表和排序缓冲的消耗变大，如果排序数据量超过sort_buffer_size，MySQL会使用磁盘临时文件来排序，进一步拉高磁盘I/O。

我碰到过一个比较极端的案例。一个后台管理系统的订单列表，默认按创建时间倒序分页，数据量在几百万级别。运营人员习惯性地往后翻页，翻到第200页（每页20条，offset约4000）时，单条查询已经需要3到5秒。这个接口是后台高频接口，多个人同时操作时，数据库的负载直接被打满。后来我们把分页方式改成了“基于游标”的方案，同样数据量下，不管翻到多深，单次查询耗时都稳定在几十毫秒以内。

所以深分页不只是一个SQL写法问题，它会直接变成数据库稳定性的问题。你不优化，它迟早会以一场故障的形式来找你。

3. 分页优化实操：从索引设计到改写方案

3.1 方案一：覆盖索引 + 延迟关联

深分页慢的一个大原因是回表次数太多。如果能避免回表，性能就能大幅提升。覆盖索引加延迟关联就是干这个事的。

核心思路分两步。第一步，先在二级索引上查出当前页需要的主键ID。因为这一步只访问二级索引，不需要回表，扫描速度远快于全行回表。第二步，再用这些主键ID去关联原表，取出完整数据。

比如原查询是：

SELECT id, title, content FROM articles ORDER BY created_at DESC LIMIT 100000, 20;

如果直接在created_at上建索引，执行计划还是需要读取100020条整行数据并丢弃前100000条。改写之后：

SELECT a.id, a.title, a.content FROM articles a INNER JOIN ( SELECT id FROM articles ORDER BY created_at DESC LIMIT 100000, 20 ) tmp ON a.id = tmp.id ORDER BY a.created_at DESC;

内层子查询只查id列，且created_at上有索引，可以走索引覆盖扫描，避免回表。拿到20个id后再回表查完整数据。实际测试下来，同样深度的分页，这个写法通常能把查询时间从秒级降到百毫秒级，前提是你建的索引能覆盖子查询的所有过滤和排序字段。

这个方案的优点是对现有SQL改动最小，不需要业务层调整查询参数。缺点是它只是减少了回表代价，依然要读取并丢弃offset条主键记录，所以offset特别大的时候，提升有限，但已经能解决很多中等规模的问题。

3.2 方案二：基于主键或唯一键的游标分页

游标分页是应对深分页最彻底的手段。它的核心思路是：不告诉数据库“给我偏移多少条之后的数据”，而是告诉数据库“给我从某一条记录之后的数据”。由于MySQL可以直接通过索引定位到游标位置，然后顺序向后扫描size条，全程不需要丢弃任何记录，所以无论翻到多深，性能都恒定。

以按id正序分页为例。前端每次请求需要带上上一页最后一条记录的id，通常叫lastId，服务端SQL写成：

SELECT id, title, content FROM articles WHERE id > #{lastId} ORDER BY id ASC LIMIT 20;

第一页查询时lastId传0或者不传，之后每页都从上一页最后一条id继续往大的方向查。这个方案有几个明显的特征：

• 翻页是“单向”的，只能下一页，不能直接从第1页跳到第100页。
• 排序字段必须是索引，且游标字段和排序字段一致，才能直接走索引定位。
• 如果按创建时间created_at倒序分页，游标需要同时携带created_at和id，用复合条件定位。

倒序分页的写法是这样：

SELECT id, title, content FROM articles WHERE (created_at < #{lastCreatedAt}) OR (created_at = #{lastCreatedAt} AND id < #{lastId}) ORDER BY created_at DESC, id DESC LIMIT 20;

为了避免created_at重复导致数据错乱，通常会带上主键id作为次级排序条件。这个方案的实际效果，我用一个百万级数据的表验证过，单次查询稳定在10到30毫秒，而且不随页码加深而恶化。它是目前我认为最适合大型列表场景的分页方式。

当然它也有明显的限制：不支持随机跳页。用户想看第50页，你没办法直接定位到第50页的起点。如果业务上必须支持任意跳页，比如后台系统经常要快速跳转，那游标分页就不太合适了。这种情况建议结合下文提到的“区间限定优化”或者改用搜索引擎一类的方案。

3.3 方案三：用表连接做时间轴分页

有些业务场景是按时间倒序看列表的，比如动态流、订单记录、操作日志。这类列表天然适合用时间游标分页。实现方式可以做得比较巧妙，直接用上一页的最大时间戳或者最小时间戳作为下一次查询的边界。

一个典型的写法是：

SELECT id, title, created_at FROM articles WHERE created_at < #{lastTime} ORDER BY created_at DESC LIMIT 20;

这个方案比“主键游标”写法更简单，因为通常只需要一个时间字段就能实现游标定位。但需要注意一个问题：如果created_at在业务上是秒级精度，同秒内插入的数据可能非常多，且这些数据之间的顺序是随机的。这时候只拿created_at做游标很容易漏数据或者重复数据。

我的建议是升级为双字段游标，用created_at加id一起组成游标条件。这样即使created_at相同，id也能保证唯一顺序。SQL写法参考上一节展示的复合条件。另一个细节是，时间字段必须建索引，否则每次查询都全表扫一遍，性能无从谈起。

时间轴分页还有个附带好处：它天然适合“下拉加载更多”的移动端体验，不需要页码控件。每次上滑请求时带上当前列表最后一条的时间戳，后端通过参数判断是首次加载还是加载更多。这种交互模式下，游标分页几乎就是最优解。

3.4 方案四：限制最大翻页深度，从业务层面规避深分页

有些场景不适合用游标分页，比如后台管理表格必须要页码跳转。这时候可以考虑一个粗暴但非常有效的策略：限制允许访问的最大offset。

比如在业务代码里判断：当请求的offset超过10000或页数超过500时，直接拒绝查询，引导用户使用筛选条件缩小数据范围，或者改用其他方式导出。

这个思路看起来不够“技术”，但实际非常实用。我见到不少公司就是这么干的：列表接口允许前500页自由翻页，超过之后提示用户使用搜索或筛选功能。原因也很简单，用户根本不会真的去看几千页的数据，排在前面的数据往往才是用户关注的。与其为了极少数深翻页场景拖垮数据库，不如引导用户用更合理的路径获取数据。

实施的时候建议在网关层或者业务服务层做统一拦截，而不是在数据库里处理。判断逻辑很简单：分页参数里的页码乘以每页条数如果超过阈值，直接返回业务错误码。这个阈值可以根据表数据量和查询耗时来定，一般在几百到几千之间。

4. 分页排序字段的索引选择与SQL写法细节

4.1 为什么ORDER BY字段必须进索引

分页查询往往伴随着排序需求，而排序字段能不能用到索引，直接决定了查询是否高效。MySQL使用索引排序的前提是，ORDER BY里的字段顺序必须和索引列顺序完全匹配，且排序方向和索引扫描方向一致。

举例来说，你有索引idx_create_time(created_at)，那么：

SELECT * FROM articles ORDER BY created_at ASC LIMIT 20;

这个SQL可以走索引顺序扫描，直接取前20条，非常快。但如果写的是：

SELECT * FROM articles ORDER BY created_at DESC LIMIT 20;

MySQL可以从索引末尾倒着扫，依然高效。两种方向都能用索引。

问题出在排序字段和索引字段不匹配的情况。比如你有复合索引idx_status_created(status, created_at)，查询是：

SELECT * FROM articles WHERE status = 1 ORDER BY created_at LIMIT 20;

由于索引第二个字段是created_at，且第一个字段status有等值条件，这个ORDER BY可以命中索引。但如果换成了：

SELECT * FROM articles WHERE status = 1 ORDER BY id LIMIT 20;

索引的第一列挡住了id的排序，MySQL大概率会先把所有status=1的记录取出来，再在内存中排序，这就产生了filesort。

4.2 索引设计时要覆盖分页排序的几种组合

根据我自己的实践经验，分页场景下的索引设计要围绕排序字段、过滤字段和游标字段来搭建。常见的有这几种组合：

单排序字段场景：只需要每次查询都ORDER BY同一个字段，就在这个字段上建单列索引即可。

过滤加排序场景：有一个等值过滤条件和一个排序字段，优先建复合索引，过滤字段放前面，排序字段放后面。比如“查某个分类下的文章，按发布时间倒序”，建(status, created_at)这样的复合索引最合适。

排序字段不唯一场景：如果排序字段可能重复，比如按状态排序，状态只有几个值，这时候单纯按状态排序会导致同一状态内部顺序不稳定。要在复合索引的末尾加上主键id，同时ORDER BY后面也补上id，这样才能保证全局顺序稳定。

举个例子：

CREATE INDEX idx_status_created_id ON articles(status, created_at, id); SELECT id, title FROM articles WHERE status = 1 ORDER BY created_at DESC, id DESC LIMIT 20;

这里id放最后，不影响status和created_at的索引匹配，同时又保证了同一时间戳下顺序稳定。

4.3 一个容易忽略的坑：隐式类型转换让索引失效

分页查询如果排序字段是字符串类型，或者WHERE条件里的字段在数据库里是varchar，而传入的参数是数字，MySQL会发生隐式类型转换，这时候索引可能失效，原本的索引排序会退化为全表扫描加文件排序。

举个例子：

SELECT id, title FROM articles WHERE order_no = 123456789 ORDER BY created_at LIMIT 20;

如果order_no在表里是varchar类型，那么这个查询会先把order_no转换成数字再比较，导致该字段上即使有索引也不一定能用上。排序字段created_at如果也在索引里，可能因为前置条件没走索引，整个查询直接变了执行计划。

解决办法很简单：参数传入层的类型要和表字段类型保持一致。你可以在SQL里显式转成字符串，比如把123456789改成'123456789'，或者在ORM层面把参数类型约束好。这个坑很隐蔽，排查起来耗时，但只要记住了，以后写SQL时多看一眼字段类型就能避免。

5. 深分页优化方案的选型对比与适用场景

5.1 四种方案横向对比

为了让你在真实业务里能快速选型，我把上面提到的主流方案拉了一个对比表。对比的维度包括性能、跳页支持、改动成本和适用场景。

这张表是我在实际选型时经常参照的。如果数据量在百万以内，跳页是刚需，我会优先考虑覆盖索引加延迟关联，改动小、见效快。如果数据量上千万，且产品可以接受只做“下一页”，主键游标分页几乎是唯一稳妥的选择。如果分页控件无论如何都改不掉，那就加上最大深度限制，从源头防止SQL打到数据库上。

5.2 混合使用：服务端兜底 + SQL优化双管齐下

在实际系统里，我并不建议只依赖一种方案。更稳妥的做法是组合：SQL层面用覆盖索引降低单条查询代价，接口层面同时用“offset限制”做兜底，防止极端的深分页请求消耗过多数据库资源。

举个例子。一个新闻资讯列表接口，每页20条，允许按时间倒序排。我在实现时会做三件事：

第一，给时间字段和主键建立合适的复合索引。第二，SQL里使用游标分页，把上一页最后一条资讯的发布时间和id作为参数传入，保证单次查询永远只扫描20条数据。第三，在服务端判断如果请求参数里没有游标字段，就拒绝服务，避免有人绕过前端直接用旧的offset参数去压接口。

这样即使将来数据量从百万涨到千万，接口性能也能维持稳定。分页优化不是写一条SQL就完事，它需要SQL、索引、接口协议、产品交互一起配合。

5.3 什么情况下应该引入其他存储组件

不要迷信任何一张表都能靠MySQL优化扛住所有分页需求。当业务需要的分页条件特别复杂，比如多维度任意组合过滤、全文检索、地理位置排序，MySQL的索引就显得捉襟见肘了。

这时候常见的选择是引入Elasticsearch一类的检索引擎。把需要复杂分页查询的数据同步到搜索引擎里，由它来处理过滤、排序和分页，MySQL只负责存数据和提供主键查询。搜索引擎内部的分页机制虽然也会面临类似深分页的性能问题，但在分布式架构下可以通过scroll、search_after等方式做游标查询，能力上限比单机MySQL高很多。

不过引入新组件意味着运维成本和系统复杂度上升。我在项目里通常遵守一条原则：单表数据量在千万级别以下、查询条件简单的情况下，优先在MySQL里解决分页；只有当组合过滤条件多到索引设计无法覆盖时，才考虑搜索引擎。不要为了炫技把一个简单的分页功能做成微服务加搜索集群，这是典型的过度设计。

6. 实战问题排查：从慢SQL到分页接口优化的完整路径

6.1 第一步：用EXPLAIN定位分页查询的执行计划

遇到分页查询慢，第一步永远是打开慢查询日志，把慢SQL捞出来，然后执行EXPLAIN。我这边看到的EXPLAIN结果里，最需要关注的几个字段是type、key、rows和Extra。

type字段如果是ALL，代表全表扫描，多半是索引没建好或者查询条件没法命中索引。key字段如果是NULL，说明根本没有可用索引。rows字段估算的扫描行数会直接告诉你这条分页查询到底扫了多少行，深分页问题在这里暴露得特别明显，你明明只要20条，它却显示要扫描几十万行。Extra里如果出现Using filesort，说明排序没有利用索引，需要调整索引设计；如果出现Using temporary，说明查询中出现了隐式的临时表使用，这通常是分组或去重引起的。

对比一下优化前后的EXPLAIN结果，能很直观地看到扫描行数从几十万降到几十条的变化。这个验证过程也是后续判断优化是否成功的重要依据。

6.2 第二步：压测不同深度的分页请求，找出临界点

优化做完之后，不要只看一两条SQL的耗时，要做分页深度维度上的压测。我的习惯是写一个小脚本，分别请求第1页、第10页、第100页、第500页、第1000页，记录各自的响应时间和数据库的扫描行数。

这样做的目的是找到当前方案的临界点：哪个页码开始性能明显劣化。如果用了游标分页，理论上所有页码的耗时都应该接近；如果用的是覆盖索引加延迟关联，可能到特定的offset后依然会劣化，这时候你就能根据压测数据决定是不是要加深度限制。

压测时不要忘了把数据量调整到接近线上规模，不然结果没有参考价值。我曾经在测试环境里数据量只有10万条，怎么测都很快，上线前才发现生产已经500万条了，性能完全不是一个量级。压测一定要贴近真实环境，最好直接压测从生产备份出来的脱敏数据。

6.3 第三步：结合业务交互确定最终方案

分页优化不是纯粹的技术问题，它最终要服务业务交互。我接触到比较典型的几个交互形态是：

• 传统的页码跳转式分页，带总页数，常见于后台管理系统。这种只能用offset分页或者配合深分页优化手段，并且建议加最大页数限制。
• 加载更多的“瀑布流”式分页，常见于资讯App。这种最适合游标分页，接口不需要返回总页数，只需要返回下一页的游标。
• 无限滚动加缓存分页，用户只看前几页，产品有实时性要求。这种情况直接优化前几页的SQL就行，深分页不用重点考虑。

我的建议是早一点和产品沟通清楚用户行为。很多时候产品经理并不知道“第100页的数据没人看”，你提出来之后，他可能欣然接受“最多加载前100页”的产品策略。技术方案也就从被动优化变成了主动定义边界，事情反而简单了。

最后再分享一个我在实际项目里反复用到的经验：任何分页优化，动手之前先把“是否稳定排序”定死。很多分页Bug和性能问题，初看是性能问题，深挖都出在没有稳定排序或者排序字段不适合走索引上。先补上正确的ORDER BY，再考虑limit怎么优化，顺序反了，后面全是坑。另外，游标分页虽然好用，但一定要做兼容处理：第一页请求没有游标参数时的默认逻辑、游标对应数据被删除时是否跳过、游标参数传错时是报错还是兜底查询，这些边界情况想清楚了，方案才算真正落地。