Java

MySQL 里 order by 为什么会慢？从过滤、候选集到分页方式一条线看清

`order by` 变慢，常常不是排序单点出了问题，而是过滤路径不够收敛、索引顺序接不上、候选集太大，再叠上分页和回表，把成本一层层抬了起来。

  • MySQL
  • order by
  • SQL优化
  • 数据库
14 分钟阅读

很多人第一次排查 order by 慢，会下意识把问题理解成：SQL 只是多写了一句排序，为什么接口时间会差这么多。

但线上真正让人头疼的，通常不是“排序这个动作本身有多贵”，而是数据库为了拿到一份有序结果，前面已经多做了很多事。

我后来越来越少把 order by 问题拆成几个平铺的根因去背。更贴近现场的方式，往往是顺着一条具体查询往下看：

  • 它先是怎么过滤的
  • 过滤完手里还剩多少候选记录
  • 排序能不能顺着同一条索引做完
  • 如果不能，还要不要额外排序、回表、跳过大量分页数据

只要把这条链看清，order by 为什么慢，通常就不会停留在一句很空的“可能是 filesort”。

先看一条最典型的排序查询

比如后台订单列表有这样一条 SQL：

select id, order_no, amount, create_time
from orders
where status = 'PAID'
order by create_time desc
limit 100000, 20;

这类查询在项目里很常见：

  • 先筛状态
  • 再按时间倒序
  • 后台翻到很后面的页

很多人第一反应是去看：create_time 上有没有索引。

但这只是很小的一部分。真正影响这条 SQL 成本的，其实是一整条链：

  1. status = 'PAID' 能先筛掉多少数据
  2. 排序能不能接在过滤之后直接顺着索引走
  3. 如果接不上，是不是要对大量候选集额外排序
  4. limit 100000, 20 又是不是逼着数据库先处理前面很多无效记录

所以我更愿意把 order by 慢理解成：为了拿到最后那 20 条有序结果，数据库前面到底绕了多大一圈。

第一步，先别急着盯排序，先看过滤是不是已经把局面拉大了

还是这条 SQL：

where status = 'PAID'
order by create_time desc
limit 100000, 20;

如果 status = 'PAID' 在这张表里占比本来就很高，比如 60%、70%，那它其实没有帮你缩小多少范围。数据库手里还是会留下很大一批候选记录。

这时候你后面再谈排序，成本自然轻不下来。

所以排查排序慢时，我通常不会先问“排序字段有没有索引”，而会先问：

进入排序阶段之前，候选集到底已经被压到多小了？

这一步特别重要，因为很多 SQL 的真正问题根本不是排序机制有多差，而是排序之前留下的数据太多。

如果 EXPLAIN 里你看到的是：

rows: 180000

而最终页面只展示 20 条，那你就要很警惕了。数据库为了给你这 20 条结果，前面可能已经拿着十几万条候选数据在做后续处理。

第二步，再看过滤和排序能不能接成同一条索引路径

这是 order by 优化里最容易被误解的一层。

很多人会说：

  • status 上有索引
  • create_time 上也有索引
  • 那过滤和排序不都照顾到了吗

真实执行时，事情没这么理想。

数据库真正喜欢的是：有一条索引，能把过滤和排序连成一段连续路径。

比如这条 SQL：

select id, order_no, amount, create_time
from orders
where status = 'PAID'
order by create_time desc
limit 20;

如果你只有：

idx_status(status)
idx_create_time(create_time)

那优化器往往还是得做取舍：

  • 先按 status 找，再额外排序
  • 或者沿着 create_time 的顺序找，再回头筛状态

无论哪一种，通常都不是最省的路径。

而如果有的是：

idx_status_ctime(status, create_time)

情况就完全不同了。数据库更有机会先用 status 把入口收住，再顺着 create_time 的顺序往后取。这样排序就不再是一道独立重活，而更像是在同一条索引路径上的顺手输出。

所以很多 order by 慢，表面在说排序，底层其实在说一件更具体的事：

过滤路径和排序路径没有接起来。

第三步，如果接不上，就要面对 Using filesort 背后的真实含义

很多人一看到 Using filesort 就很紧张，但又说不清它到底意味着什么。

我更习惯把它理解成一句很朴素的话：

数据库没法直接顺着现成顺序把结果吐出来，所以得自己再排一遍。

这里最重要的不是术语，而是它出现在什么上下文里。

如果执行计划像这样：

key: idx_status
rows: 180000
Extra: Using where; Using filesort

那你其实已经可以把现场复盘出来了：

  • 先沿着 status 找出很多记录
  • 这些记录还没有天然按 create_time 排好
  • 所以得额外排序
  • 候选集又不小，这个排序就会变得很重

这也是为什么 Using filesort 不能孤立看。它值不值得紧张，往往取决于前面那批候选集到底有多大。

候选集很小，额外排序未必有多痛；候选集很大，它就会立刻变成主成本之一。

第四步，很多人以为只剩排序了，其实深分页又把成本继续放大了

还是这条 SQL：

limit 100000, 20

这类写法的问题，不只是“我要第 100001 条到第 100020 条”。对数据库来说，更现实的含义是：

  • 前面那一大段数据它不能假装不存在
  • 它通常还是得先找到、排序、或者至少沿顺序跳过
  • 最后才轮到你真正看到的那 20 条

所以后台列表经常会出现一种很典型的现象：

  • 前几页很快
  • 页码越深越慢
  • 数据量一上来，某些筛选条件下更明显

这时候如果你只把问题归到 order by，其实会有点失真。因为真实成本已经变成：

  • 过滤不够收敛
  • 排序没法直接承接
  • 深分页又逼着数据库处理大量会被跳过的数据

这三件事叠在一起，RT 很容易一下子上去。

第五步，回表和 select * 往往是最后那一层隐形加重

还有一类很常见的误判是：已经把过滤和排序想明白了，却没意识到查询字段本身还在抬成本。

比如：

select *
from orders
where status = 'PAID'
order by create_time desc
limit 20;

即使你后面补了 (status, create_time) 联合索引，这条 SQL 也未必就立刻很漂亮。因为只要是 select *，数据库往往还得：

  • 先顺着索引找到候选记录
  • 再回表拿完整字段

如果记录本身很宽，或者候选量比你以为的大，这一步依然可能不轻。

所以有些排序 SQL 的真实画像不是“排序慢”，而是：

  • 过滤路径一般
  • 排序成本不低
  • 回表又把整体代价继续往上抬了一截

这也是为什么很多列表查询优化到后面，会顺手做两件事：

  • 尽量少查无关字段
  • 能形成覆盖索引的地方尽量形成覆盖索引

它们不一定改变排序的本质，但很经常能把整条查询压下来。

把这条现场往后收一下

如果你真在现场里盯这条 SQL，通常不用把它拆成一张标准化检查表。大多数时候，跑一遍 EXPLAIN，再把执行链按前后顺序接起来，问题就已经八九不离十了。

像前面这条订单查询，我一般只会连续确认几件事：先看 status='PAID' 到底筛掉了多少数据；再看排序是不是能接在这段过滤后面；如果接不上，就接受它要额外排序；最后再问一句，limit 100000,20 这种分页是不是又把前面成本继续放大了。

这样看下来，你会发现它不是某一个知识点答错了，而是整条路径都不够短。

再把最开始那条 SQL 还原一遍

回到这条例子：

select id, order_no, amount, create_time
from orders
where status = 'PAID'
order by create_time desc
limit 100000, 20;

假设你看到的执行计划是：

key: idx_status
rows: 180000
Extra: Using where; Using filesort

那这条查询为什么慢，其实已经可以顺下来了：

  1. status 先筛了一下，但筛得不够狠，候选集还是很大
  2. 排序没法直接顺着索引完成，所以出现 Using filesort
  3. 深分页又要求数据库处理并跳过前面大量结果
  4. 如果查的字段再多一点，后面还会叠回表成本

你会发现，问题从来都不只是“排序字段没有单独索引”。

真正的主线是：过滤路径、候选集规模、排序承接方式、分页方式，这几件事一起决定了 order by 的成本。

最后总结

order by 慢，很多时候不是一个孤立的排序问题，而是一条查询链在几个地方同时变重了：

  • 过滤条件没有先把候选集压小
  • 排序字段没法接在过滤路径后面顺着索引走
  • 于是出现额外排序
  • 深分页又把前面的成本继续放大
  • 回表和取字段方式再补上一刀

所以我现在看这类 SQL，最关心的不是“排序慢的几个原因”这种平铺答案，而是更具体的一个问题：

为了拿到最后那一点有序结果，数据库前面到底做了多少本可以少做的事？

把这个问题想清楚，order by 的优化方向通常就会自然很多。

如果你要顺着这个问题继续查