你以为 ORDER BY 很快？我用一次血案告诉你什么叫Too Young

事情是这样的，那天线上报警说接口超时，我胸有成竹地打开监控——毕竟我写的SQL，那叫一个优雅。

然后我看到了这个：

SELECT * FROM orders 
WHERE status = 'paid' 
ORDER BY created_at DESC 
LIMIT 100

熟悉吗？太熟悉了。谁没写过这样的SQL呢？

然后我看了眼执行计划：

id | type | key          | rows   | Extra
---|------|--------------|--------|------------------
 1 | ref  | idx_status   | 15234  | Using filesort

一万五千行，做了个filesort。嗯，还行吧。

直到有一天，运营告诉我："老板说要把这个报表改成查全量数据"。

我心想，小问题，不就改个LIMIT吗？

SELECT * FROM orders 
WHERE status = 'paid' 
ORDER BY created_at DESC 
LIMIT 10000000

上线。

报警了。

那次P0故障的根因，就藏在这个看起来无比正常的SQL里。

MySQL的排序到底是怎么工作的？

很多人以为 ORDER BY 就是数据库找个地方排一下就完了。太天真了。

MySQL的排序分为两种模式：

1. 索引排序：当你 ORDER BY 的字段刚好有索引，且查询条件能利用上这个索引的有序性时，MySQL直接按顺序读就行了，零额外开销。
2. Filesort：当没有索引可用，或者索引的有序性被破坏时，MySQL只能把数据先读出来，在内存（或者磁盘，如果数据量大的话）里排序。

问题来了：Filesort是用什么算法排序的？

MySQL 5.6之前用的是两次传输排序：先读出所有需要排序的字段到客户端_sort_buffer，排序，然后再回表取SELECT的其他字段。简单粗暴。

MySQL 5.7优化成了单次传输排序：先回表查出所有字段，存入_sort_buffer，然后排序。虽然减少了一次数据传输，但内存压力更大了。

不管哪种算法，核心问题都是：数据量越大，排序越慢，而且这个慢是指数级的。

你的索引可能白建了

回到开头那个SQL：

SELECT * FROM orders 
WHERE status = 'paid' 
ORDER BY created_at DESC

有人可能会问：我建了 idx_status_created (status, created_at) 联合索引啊，应该走索引排序吧？

来，看个例子：

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  status VARCHAR(20),
  created_at DATETIME,
  amount DECIMAL(10,2),
  customer_name VARCHAR(100),
  ...
  INDEX idx_status_created (status, created_at)
);

EXPLAIN SELECT * FROM orders 
WHERE status = 'paid' 
ORDER BY created_at DESC;

执行计划告诉你：Using filesort。

为什么？

因为SELECT *把其他字段也查出来了，而这些字段不在联合索引里。MySQL发现与其"索引有序但要回表N次"，不如"全表扫描+filesort"。这就是MySQL optimizer的判断。

解决方案是什么？

用覆盖索引：

CREATE INDEX idx_status_created_covering 
ON orders (status, created_at, amount, customer_name, ...);

但问题是，你的SELECT *是要查多少字段？全放进去索引会变得很大，插入性能下降，而且MySQL单索引长度有限制（767字节，InnoDB）。

所以很多时候，这不是索引的问题，是SQL写法的问题。

分页偏移量的陷阱

我见过最离谱的代码是这样的：

-- 用户要求：查看第100页，每页20条
SELECT * FROM orders 
WHERE status = 'paid' 
ORDER BY created_at DESC 
LIMIT 2000, 20;

你知道MySQL要做什么吗？它要：

1. 扫描2020行数据
2. 排序
3. 返回第2000到2020行

也就是说，用户想看第100页，但MySQL要把前100页的数据都扫描一遍。

更可怕的是，当用户翻到第1000页时：

LIMIT 20000, 20

MySQL扫描20020行，只为了返回20条数据。990%的IO都是浪费的。

这就是经典的"深度分页"问题。

解决方案：延迟关联。

-- 先查ID，再关联
SELECT orders.* FROM orders 
INNER JOIN (
  SELECT id FROM orders 
  WHERE status = 'paid' 
  ORDER BY created_at DESC 
  LIMIT 20000, 20
) AS t USING(id)

这样，外层查询只返回20条完整的订单记录，而子查询虽然也要扫描2020行，但sort buffer里只存了id整数，内存压力小得多。

如果你的表有几十GB，这个优化能把查询时间从几十秒降到几十毫秒。

实战优化方案

1. 游标分页（Keyset Pagination）

LIMIT offset, count 的问题在于offset越大，扫描越多。更好的方式是：

-- 第一页
SELECT * FROM orders 
WHERE status = 'paid' 
ORDER BY created_at DESC 
LIMIT 20;

-- 记住最后一行的created_at
-- 下一页
SELECT * FROM orders 
WHERE status = 'paid' 
AND created_at < '2024-01-15 10:30:00'
ORDER BY created_at DESC 
LIMIT 20;

不管翻到第几页，始终只扫描20行。这是最优解，但需要前端配合改分页逻辑。

2. 倒序索引

MySQL支持在建索引时指定DESC：

CREATE INDEX idx_status_created_desc 
ON orders (status, created_at DESC);

对于MySQL 8.0+，这种降序索引能让ORDER BY DESC直接走索引，不用filesort再排一次。

3. 预排序+缓存

如果你的查询是报表类的，实时性要求不高，可以：

1. 用cron任务定时把排序结果写入一张新表
2. 查询直接从新表读
3. 牺牲一点实时性，换取查询性能的质变

听起来不优雅？但生产环境里，这可能是唯一可行的方案。

那些年我踩过的坑

讲讲那次P0故障的后续。

问题定位后，我第一时间加了索引：

ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

线上执行，锁表了。

对，MySQL 5.6及之前的版本，ADD INDEX会锁表。虽然5.7+支持Online DDL，但在生产环境执行大表索引，依然是个技术活+运气活。

最终解决方案是：用pt-online-schema-change工具，凌晨3点无流量时执行的。整整跑了4个小时。

所以我现在的习惯是：在上线前就用EXPLAIN检查所有涉及排序的SQL，永远不要等到报警了再优化。

总结

ORDER BY看着简单，但这里面的水很深：

• 索引列顺序决定了你能不能利用索引排序
• SELECT * 是性能杀手，特别是在有排序的时候
• 深度分页是性能黑洞，游标分页才是正解
• 大表加索引要谨慎，pt-osc或gh-ost是你的好朋友
• 监控和报警很重要，但提前做code review更重要

最后送大家一句话：你以为的简单，可能只是你还没遇到流量。

共勉。