MySQL 排序机制

Apr 21, 2019

在 MySQL 中经常使用 Order by 对数据进行排序，其实排序这个行为是比较消耗 IO 的过程，有时候需要回表多次才可以完成排序，所以在任何时候都需要对排序的原理要心知肚明。

在 MySQL 中排序按照是否使用外部存储可以分为，内存排序和外部排序两种。根据排序所需的字段可以分成 rowid 排序和全字段排序两种。

在 MySQL 执行排序的时候会分配一块内存 sort_buffer，MySQL 把需要排序的字段放入这个 sort_buffer 中，让，后在 sort_buffer 执行排序的过程，如果 sort_buffer 大小不够，就要使用外部存储。

一般来说 sort_buffer_size 的大小取决于排序的是使用快排（内存排序）还是归并排序（外部排序）。

以上的排序过程都是使用全字段进行排序的，但是如果 sort_buffer 不足以存放所有排序字段，那么这时候就需要用到 rowid 排序。

对于 rowid 排序支取 id 和需要排序的字段放入 sort_buffer 中，在 sort_buffer 开始对字段进行排序。根据排序完成后的 id 再回表找到其他字段组合成结果集返回。

对于 rowid 排序和全字段排序最大差别在于多一次回表的过程，这也是一次 io 消耗过程（不一定是随机读过程）。同时扫描次数 rowid 会多余全字段排序 n 行，这个 n 就是第二次回表过程根据 id 找到的行的数量。

对于 MySQL 来说如果内存够，就要多利用内存，尽量减少磁盘访问，只有分配的内存不够用的时候才会使用 rowid 排序。

那么，这个过程如何优化？我们可以发现优化的地方有两个，一个是排序所消耗的时间，一个是回表再次读取的时间。所以优化就可以根据这两个来。

对于回表这个操作经常和数据量有关系没有什么好办法，一种比较常用的方法就是建立复合索引以减少排序所耗费的时间。如果再 order by 时候字段满足最左匹配原则，那么这时候第一次从表加载到 sort_buffer 中本身就有序的，那么这时候可以直接当做结果返回了，就不需要排序了。

最后，使用 explain 可以分析 SQL 的排序方式：

参考