ORDER BY 两个字段慢 1.8 秒 排序索引该怎么补

订单列表接口 1.8 秒,EXPLAIN 的 Extra 是 Using where; Using filesort。status 上有单列索引,shop_id 也有单列索引,两个都用上了,排序还是没走索引。

第一反应是加联合索引 (shop_id, status, Created_at)。加完 EXPLAIN 还是 filesort,因为查询写的是 status IN (1, 2),两个值没法共用一段有序索引。

接着试调 sort_buffer_size,从 256KB 加到 4MB。1.8 秒降到 1.5 秒,磁盘临时文件没了,但 40 万行的排序量摆在那,收益有限,还多吃内存。

真正解决是把 IN 拆成两个等值分支,各自走 (shop_id, status, created_at),排序由索引顺序直接给出,再合并取前 20 条。

(SELECT id, status, created_at FROM orders
 WHERE shop_id = 42 AND status = 1
 ORDER BY created_at DESC LIMIT 20)
UNION ALL
(SELECT id, status, created_at FROM orders
 WHERE shop_id = 42 AND status = 2
 ORDER BY created_at DESC LIMIT 20)
ORDER BY created_at DESC LIMIT 20;

单条分支只扫 20 行就停,两个分支合起来 40 行。执行时间 60ms,Handler_read_next 从 38 万降到 47。

拆之前要确认状态枚举固定。这张表有 7 个状态,如果按配置动态生成 UNION 分支,SQL 会越拼越长,超过 5 个分支就该换别的写法。

也考虑过冗余一个排序时间字段,把排序变成范围扫描。改动大,写入时还要维护,最后没做。

确认方式:EXPLAIN 的 Extra 里 filesort 消失,type 是 ref;再看 SHOW SESSION STATUS LIKE 'Sort%',Sort_scan 归零,两个对上才算真改掉。

您可以还会对下面的文章感兴趣: