前言

朋友们，日常开发是不是总遇 MySQL 的糟心事？不排序秒出结果，一加 ORDER BY 就卡顿，反复改 SQL、调索引也找不准慢查根源，越调越懵？

其实问题核心就在没选对排序方式、没找对慢查症结！我专门深耕梳理了 MySQL 的核心排序机制，整理了 4 种常用排序方式，还附上慢排序定位方法和落地优化技巧，从根源教你搞定 ORDER BY 慢查，干货可直接套用，一起来看看！

MySQL常用的排序方式索引排序

索引本身就排好序的 B+Tree（如下图），索引排序就是直接利用B+Tree有序性进行查询，无需额外排序。这是最高效的一种方式，也是最推荐的。

索引排序使用的前提是 排序字段要匹配上索引，所以我们可以建立相应索引。

优先队列排序

也叫堆排序，适应于 带有 limit 的排序查询（order by *** limit N），这种排序仅需在内存中维护N个元素的堆即可（如下图），比较次数较少效能很高。

堆排序对 sort_buffer 的需求极低（通常仅需几KB到几十KB），远小于sort_buffer_size默认值（256KB），因此几乎不会触发磁盘临时文件。

如果排序查询中有 limit，则优先使用堆排序（想想也是，有limit则查询条数一般都很少则需要一点内存就够了）。

快速排序

对于没有 limit 的排序查询，如果 sort_buffer 够用则使用快速排序。所有扫描的数据都放到内存参与排序，数据量大，但是在内存中进行的（filesort中的内存排序）效能也挺高。

快速排序对 sort_buffer 的需求很大（具体取决于排序的数据量大小），sort_buffer 一旦不够就会转变为下面的归并排序，可以使用 sort_buffer_size 设置足够的内存，但为了避免内存溢出也不能设置太大（具体参数设置见 MySQL优化参数设置）。

Explain的Extra里出现 Using filesort，有可能是快速排序也有可能是归并排序。

归并排序

对于没有 limit 的排序查询，如果 sort_buffer 不够则使用归并排序（如下图）。这种排序是在磁盘上进行的，效能比较低。

归并排序使用磁盘（filesort中的磁盘排序），是使用的MySQL的临时目录（tmpdir），为了提升效能建议将临时目录指向一个 SSD盘。

归并排序使用磁盘效能较低，建议尽量避免这种排序方式。

查询究竟使用了哪种排序

下面通过一个满SQL分析一个SQL究竟使用了哪种排序，找出慢的根源。

select c from t1 where a = 3 and b > 1 order by b limit 300,10

上面的SQL有排序比较慢，什么原因呢？下面使用 explain analyze 和 optimizer_trace 两种方式进行分析：

1、分析explain analyze 结果

A、通过解析公式（从上到下、深度优先，详细见 分析SQL执行细节）我们得出这个SQL的执行步骤：先使用 a=3 对表 t1 进行索引扫描（肯定是用字段a建立了二级索引），扫描行数为16384；再用 t1.b>1 对这些数据进行过滤，过滤了16384行；再进行排序，参与排序的有310行；最后再对排序好的数据取前10行。

B、Sort行中，rows为310，远远小于下面的16384行，说明使用了优先队列排序；既然使用了优先队列排序则说明肯定是在内存里做的，说明sort_buffer_size 够用；参与排序行数也不多，还是在内存里做的，说明慢的原因不在排序这里。

C、进一步分析，导致慢的只能是扫描16384 行并过滤了....

得到上述结论，为优化这个SQL提供了方向，怎么减少扫描行数呢？ 用(a,b) 建立联合索引是个办法。

2、分析optimizer_trace结果

A、num_initial_chunks_spilled_to_disk=0，说明参与排序的数据块都没有溢出到磁盘，完全使用了内存。

B、using_priority_queue 说明使用了优先队列排序（堆排序）。

总结

A、排序算法效能排序：索引排序 > 优先队列 > 快速排序 > 归并排序。

B、我们要尽量使用索引排序（让EXPLAIN 里 Using filesort 消失），这样效能最好。

C、优先队列排序 和 快速排序都使用sort_buffer，故如果并发不高可以把 sort_buffer_size 适当加大（但要保证 sort_buffer_size × 并发 的值别太大避免超过服务器可用内存引起OOM）。

D、为了减少 sort_buffer 使用量，我们应该在排序不分只查询排序需要的字段。

E、 MySQL临时目录（tmpdir）使用 SSD，提高归并排序效能。

F、大分页 LIMIT 100000,10 可先“书签”定位，减少排序行数，见 分页排序查询优化技巧 。

G、Explain 的 Extra 里出现 Using filesort，不一定是使用了磁盘的归并排序，也有可能是快速排序。

朋友们，您对MySQL排序慢还有什么解决方案？欢迎评论区交流！