在 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 的排序方式:

  • Using index:覆盖索引
  • Using filesort:使用外部排序
  • 没有 Using index 和 Using filesort:使用联合索引

参考

  • 极客时间《MySQL实战45讲》:“order by” 是怎么工作的?