优化数据库查询最容易被忽视却最耗性能的往往是排序和分页。我之前接手过一个后台的订单列表页数据量刚到300万行翻到200页之后接口就要卡十几秒前端一度以为后端挂了。查到最后问题既不在这台服务器的CPU也不在缓存设计而是出在一条再普通不过的SQL上SELECT * FROM orders WHERE status1 ORDER BY create_time DESC LIMIT 200000,20;。这篇文章不绕弯子就是把MySQL排序与分页这件事讲透——底层是怎么执行的为什么会有那么多坑以及真正能解决问题的优化路径。适合正在写业务代码、被慢查询逼疯的后端开发者也适合想系统理解MySQL执行原理的DBA和架构师。1. 一条慢查询暴露出的排序分页问题1.1 事故现场翻到第200页突然卡死那个列表页本身不复杂就是查订单。订单表大概长这样CREATE TABLE orders ( id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0, ... KEY idx_status (status), KEY idx_create_time (create_time) ) ENGINEInnoDB;业务要求默认展示status1已支付的订单按支付时间倒序每次20条。分页按钮从第1页到第∞页。前50页速度还行到第200页即LIMIT 3980,20的时候就开始明显变慢等翻到第10000页LIMIT 199980,20直接超时。一开始团队怀疑是网络或前端渲染问题后来我直接开慢查询日志把这条SQL捞了出来。放到数据库里单独执行耗时4.8秒。这个数字让我确定问题不在应用层在数据库层。1.2 执行计划里的两个危险信号对这条SQL执行EXPLAIN结果让我瞬间清醒id | select_type | table | type | key | rows | Extra 1 | SIMPLE | orders | ref | idx_status | 1523400| Using filesort两个危险信号第一key用的是idx_statusrows估算有152万行第二Extra里赫然写着Using filesort。第一个信号说明MySQL确实先用status1这个索引等值条件过滤出了152万行记录。第二个信号说明这152万行在内存或磁盘里被重新排序了。而LIMIT 200000,20的意思是从排序完的结果中跳过前20万行然后取20行。这个执行计划几乎把最坏的情况全占满了过滤结果集巨大、排序没有索引支撑、偏移量高达20万。1.3 为什么LIMIT 200000,20要读20万行很多刚从学校毕业的开发者会有一个天真的想法LIMIT 200000,20就是“只读20条”。大错特错。MySQL 的 LIMIT 语义是“偏移量 行数”它必须按照ORDER BY排序完成后从头数到第200000行再取第200001到第200020行。换句话说前面的20万条记录虽然不会返回给客户端但它们必须被扫描、排序、计数一样都不能少。偏移量越大耗费的资源就越多这是一个近似线性的增长关系。这里还叠加了另一个问题ORDER BY create_time DESC没有对应的索引。MySQL 的idx_create_time是单列索引但它和status过滤条件不是同一个索引无法被用来同时完成过滤和排序。所以数据库只能先把符合status1的152万行全部捞到内部缓冲做一次 filesort然后再做偏移。这个过程有点像一个快递分拣中心你要求把某个片区的150万件包裹先全部搬到传送带上按面单上的时间排序然后从第20万件开始只拿20件出来。大部分成本都花在了搬运和排序上。2. ORDER BY背后的排序机制索引排序与filesort的博弈2.1 走索引排序像查字典一样天然有序MySQL 的 ORDER BY 能不能快取决于它是否能“偷懒”地利用索引的有序性。InnoDB 的 B 树叶子节点本来就是按索引列排序的。如果排序字段与某个索引的列完全匹配并且过滤条件也匹配索引的前几列那么 MySQL 可以直接沿着索引的叶子节点顺序扫描得到的自然就是排好序的数据不需要额外的排序环节。执行计划里的体现就是 Extra 中不出现Using filesort有时候会看到Using index或单纯的Using index condition。举个例子如果有一个联合索引(status, create_time)那么执行SELECT id, user_id, status, create_time FROM orders WHERE status 1 ORDER BY create_time DESC;MySQL 会先通过status 1跳到索引的对应位置然后因为索引内create_time是有序排列的它可以按倒序扫描直接取出满足条件的数据行。整个过程不需要一处额外的排序复杂度大约和结果集行数成正比而不是和整个表的数据量成正比。这就像一本按城市分类、每个城市内部按姓名拼音排序的电话簿。你只需要翻到“北京”这个分区然后在分区内部按拼音顺序找就行根本不需要把整本电话簿抄一遍再排序。2.2 filesort的工作原理内存排序、磁盘归并与参数调优当不能用索引排序时MySQL 就会启用 filesort。名字叫“文件排序”其实首选是内存排序。它的执行分两步第一步MySQL 将需要排序的键值和行数据或者指向行数据的指针读入sort_buffer_size指定的内存区域在内存里做快速排序。第二步如果要排序的行数太多内存装不下MySQL 就会把中间结果分成多个“块”每个块先各自排序然后写入磁盘临时文件最后再做多路归并排序。这个过程非常耗时因为涉及大量磁盘读写。这里有几个参数值得注意sort_buffer_size单个排序操作可用的内存上限。设得太小会频繁触发磁盘临时文件设得太大虽然可能减少磁盘归并但每个连接都会保留这么大一块内存对高并发场景反而是灾难。max_length_for_sort_data排序行的最大字节数。如果查询涉及的所有列总长度超过该值MySQL 会退化为“双路排序”只把排序键和主键放进 sort buffer排完序再回表查询完整数据。这样内存使用更小但增加了一次回表操作。max_sort_length某个排序键列的最大字节数超出部分截断。我见过不少团队为了调优 filesort把sort_buffer_size从默认的 256KB 直接调到 64MB结果并发一上来内存直接被打爆。实际上当ORDER BY的结果集达到百万行级别再怎么调大内存都是治标不治本唯一正确的思路是想办法消除 filesort。2.3 哪些写法会让ORDER BY放弃索引在日常开发中即使建了索引也常常会因为写法的细节让索引派不上用场。我最常踩到的是这几类对排序列使用函数比如ORDER BY DATE(create_time)。索引存的是create_time的原始值不是DATE()后的值因此优化器没法用到索引的有序性。如果你确实需要按日期排序应该单独维护一个 date 字段。隐式类型转换。比如user_id是字符串类型条件写成WHERE user_id 123MySQL 会隐式地把字符串列转成数字来比较导致索引列上发生函数运算索引失效。混合排序方向。索引默认是 ASC升序如果查询要求ORDER BY create_time ASC, id DESC在 MySQL 8.0 之前B树索引无法优雅地支持一升一降的混合扫描大概率要 filesort。排序字段和 WHERE 过滤字段不在同一个索引的最左前缀中。比如WHERE status1 ORDER BY create_time单独建idx_status和idx_create_time即使两者各自都有索引也无法在同一个索引内同时完成过滤和排序。理解了这两条路再回头看那条慢查询解决方案就很清楚了要么让过滤和排序走同一个联合索引要么想办法减少排序和偏移的扫描量。3. 分页查询的三个隐蔽陷阱3.1 深分页的代价为什么会线性增长分页本身不难难的是“深分页”。我们做个简单测试在同一张300万行的表上执行同一形式的SQL只改变偏移量观察耗时变化。偏移量SQL写法耗时约0LIMIT 0,200.12s10000LIMIT 10000,200.65s50000LIMIT 50000,201.20s200000LIMIT 200000,204.80s数据很直观偏移量越大耗时越接近线性增长。原因前面已经说过LIMIT 的 offset 让 MySQL 必须扫描并丢弃前面的所有行。深分页的代价本质上不是“取20行”的代价而是“丢弃前面20万行”的代价。在真实业务中订单管理员会习惯性地翻到很后面的页去查看历史订单。如果不做限制或优化深分页就会成为数据库的定时炸弹。有的团队甚至因此把默认排序改成了不排序结果业务又要求必须有稳定的分页顺序反而引入了更难排查的乱序问题。3.2 偏移分页导致的数据重复与丢失如果你在翻页的过程中表里的数据发生了插入或删除那么基于 LIMIT 的偏移分页会出现“重复出现”或“突然缺少”的诡异现象。举个例子当前数据共100条按create_time DESC排序。第一页返回的是最新20条。就在你要翻第二页之前恰好有一条新的订单插入并且它的create_time是最新的。那么第二页的LIMIT 20,20重新排序后原来的第20条变成了第21条第二页本来是第21-40条现在变成了第22-41条。从用户视角看第一页最后一条和第二页第一条重合了。反过来如果翻页过程中删掉了第一条数据那么第二页的第一条就会直接顶到第一页的位置用户会以为数据“跳过去了”。这个问题不解决任何基于页码的分页都无法彻底避免。如果你做的是账户流水、订单列表这种对数据一致性要求很高的场景就要特别留意。要缓解可以给排序加一个绝对唯一的键作为最终排序条件比如ORDER BY create_time DESC, id DESC。这样除非同一毫秒内插入相同的create_time可能性很低否则每一行都有唯一的序列位置。但注意这能缓解重复却不能解决删除带来的空洞。要根治就得用下一小节提到的方式。3.3 排序字段不唯一引发的“幽灵排列”create_time的精度是秒在交易高峰期同一个秒内可能插入几十上百条记录。如果你只按create_time DESC排序那么这些记录谁在前谁在后MySQL 是不保证稳定的。同一个SQL反复执行可能两次返回顺序不一样。这就会导致分页出现一种特别让人费解的现象第1页看到了A记录翻到第2页又看到了A记录或者明明第1页显示A在最后第2页的列表里A却跑到最前面。用极客的话说这是排序的“非确定性”。解决思路很简单在排序中追加一个不重复的列。最稳妥的是主键SELECT ... FROM orders WHERE status 1 ORDER BY create_time DESC, id DESC;因为id是主键绝对唯一先按时间倒序、再按主键倒序每一行的位置就是唯一且稳定的。代价是排序的key变长了一点点但通常可以接受。4. 大翻页场景的三种优化方案及实测4.1 覆盖索引 延迟关联先定位主键再回表第一种优化方案的核心思路是把排序和分页限制在一个“瘦”索引上完成不要在这一步碰完整的行数据。刚才的慢SQL是SELECT *所有字段都要返回MySQL 必须访问聚簇索引获取完整记录。但如果我们改成分两步走SELECT * FROM orders INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY create_time DESC, id DESC LIMIT 200000, 20 ) tmp ON orders.id tmp.id ORDER BY orders.create_time DESC, orders.id DESC;子查询里只查id并分散在索引(status, create_time, id)上执行。status1用于定位索引前缀create_time DESC, id DESC用于索引排序整个子查询全程不需要回表。然后外层再根据这20个id去聚簇索引回表取完整数据。我给这个SQL建了一个联合索引ALTER TABLE orders ADD INDEX idx_status_create_time_id (status, create_time, id);再执行同样的查询耗时从4.8秒降到了0.3秒左右。原理很简单排序和偏移都发生在更小、更紧凑的索引树上扫描索引也比扫描完整行便宜得多。这个方案保留了“跳页”能力可以翻到任意页也不用改写业务逻辑的页码参数。4.2 游标分页Keyset Pagination放弃页码记住位置第二种方案更彻底不要页码只提供“上一页/下一页”。具体做法是把上一页最后一条记录的关键排序值作为下一页的查询起点。假设上一页最后一条记录的create_time 2024-01-15 10:30:00id 12345那么下一页的SQL可以写成SELECT * FROM orders WHERE status 1 AND (create_time, id) (2024-01-15 10:30:00, 12345) ORDER BY create_time DESC, id DESC LIMIT 20;这种写法的核心是利用索引直接定位到“上一页最后一条”的位置然后向后扫描20条。MySQL 会使用(status, create_time, id)联合索引先定位到status1的起点再沿着索引向后扫直到找到(create_time, id) (...)的边界之后返回20行。这个方案最大的好处是无论你翻到第几页查询成本都恒定绝不会出现深分页越翻越慢的现象。而且天然规避了插入/删除导致的数据重复或空洞问题因为查询条件是“严格小于上一页最后一条”上一页的那条记录根本不会重复出现。代价是不能跳页用户无法直接输入页码跳到第10000页。但想想现实中的业务真的有用户需要跳到第10000页吗大多数情况下“上一页/下一页”足够满足需求。如果你做的是IM聊天记录、操作日志、交易流水这种顺序浏览的场景游标分页通常是更优解。4.3 三种方案的性能对比和适用场景方案SQL核心跳页能力深分页性能数据稳定性适用场景朴素 LIMITLIMIT offset,size强差线性下降插入/删除会导致重复或空洞数据量小、很少翻深页覆盖索引 延迟关联子查询先取主键强中等偏优插入/删除问题依旧存在数据量大但有跳页需求游标分页用上页末尾值作为起点无跳页恒定开销稳定不受插入/删除影响顺序浏览、无限滚动、聊天记录我在实际项目中通常这样选择如果产品和运营坚持要页码那就用覆盖索引延迟关联如果产品接受“加载更多”或“上一页/下一页”就果断用游标分页。后者能少掉很多后续的坑。我还试过用窗口函数ROW_NUMBER()做分页写法很优雅SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY create_time DESC, id DESC) rn FROM orders WHERE status1 ) t WHERE rn BETWEEN 200001 AND 200020;但实测下来这个方案要先为152万行生成排序号临时表开销巨大深分页性能甚至不如朴素 LIMIT所以不推荐在真实业务里作为高并发分页方案。5. 排序分页场景下的索引设计铁律5.1 组合索引的字段顺序等值列优先排序列次之知道了排序和分页的原理最后也最关键的问题就是怎么建索引。我总结的第一条铁律是等值条件的列放在排序列前面。假设业务写的是SELECT * FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 20;索引应该建(status, create_time)而不是(create_time, status)。因为status1是等值过滤可以精确定位到索引的一个小区间然后在这个小区间里create_time已经天然有序。反过来建(create_time, status)虽然也能找到所有status1的行但这些行的create_time散落在整个索引中数据库无法利用索引完成排序还是得 filesort。如果查询里同时有范围条件比如SELECT * FROM orders WHERE status 1 AND create_time 2024-01-01 ORDER BY id DESC;此时排序字段变成了id而create_time是范围条件。最优的索引设计往往是(status, create_time, id)其中status等值定位create_time划定范围id在每一段范围内才是有序的。但要注意范围条件create_time ...会打断后面id的整体有序性导致id DESC无法借助索引——MySQL 只能对每一小段分别扫描后再归并大概率还是会 filesort。所以遇到这类SQL要么接受filesort并控制范围大小要么改写成游标分页用(status, create_time, id)组合直接排序。5.2 排序方向与混合DESC/ASC的处理在 MySQL 8.0 之前索引只支持 ASC升序所谓降序查询是通过反向扫描索引实现的。如果排序是ORDER BY create_time DESC, id DESC两个字段都反向优化器可以直接反向扫描一个升序索引(create_time, id)效果和降序索引一样。但如果写成ORDER BY create_time ASC, id DESC一个升序一个降序优化器就没那么顺手了往往会退化成 filesort。MySQL 8.0 引入了降序索引可以用如下方式显式创建ALTER TABLE orders ADD INDEX idx_status_ct_id_desc (status, create_time DESC, id DESC);这在混合方向排序、对性能有硬性要求的场景下很有用。不过要注意降序索引也会增加写入和存储的开销不要为每一个查询都盲目建。我通常只在高频QPS的排序查询上使用。5.3 用EXPLAIN校准优化结果的几个要点每建完一个索引都要用EXPLAIN验证优化是否真正生效。我会重点看三处key是否使用了预期的新索引。rows估算扫描行数是否显著下降。Extra是否还出现Using filesort或Using temporary。如果Extra里出现了Using index说明这是覆盖索引场景排序和查询都可以在索引内完成不用回表这是最完美的情况。如果看到Using index condition说明索引条件下推也不错。只要Using filesort消失或减少就说明排序问题已经缓解。另外EXPLAIN的rows是估算值不一定精确但可以用来做前后对比。正式上线前最好在真实数据量下多跑几次同时打开profiling或慢查询日志记录实际耗时。5.4 我的几条实战心得最后说几条我在实际业务里反复踩坑后才总结出来的心得第一SELECT *是排序分页优化的大敌。每次回表都要把完整行数据捞出来排序缓冲区更容易被打满filesort 更容易退化成磁盘归并。尽量只查询需要的列或者用延迟关联先取主键。第二如果一张表已经有多个排序查询的变体不要试图用一个索引覆盖所有情况。比如有的查询ORDER BY create_time DESC, id DESC有的查询ORDER BY amount DESC那就分别建(status, create_time, id)和(status, amount)两个索引让每条关键路径各取所需。第三实时分页和离线导出逻辑要分开。导出大量数据时不要走分页SQL直接全量扫并使用流式读取否则排序缓冲和内存会很难受。第四数据量达到千万级之后任何偏移式分页都自带天花板。这时候再优化索引也只是延缓问题聪明的做法是提前和产品沟通把交互改成“加载更多”或引入独立搜索引擎。但大多数系统在百万到千万这个量级通过覆盖索引延迟关联就能撑住不必动不动就上重型组件。我处理过的排序分页问题里真正需要动用大数据方案的不到十分之一。剩下九成都是老老实实把索引设计对了、把SQL写法改合理了问题就迎刃而解。这也是我写这篇文章的目的排序与分页的底层机制并不复杂但每一步选择——是走索引还是filesort是深分页还是游标分页——都会在数据量变大后产生天壤之别的性能差异。理解这些细节你就不需要被一条4.8秒的慢查询牵着鼻子走了。