---

mysql排序实现原理

全字段排序

当我们执行sql语句中的order by来排序时,mysql会分配一个叫做sort buffer的内存空间.进行排序时,可能涉及到回表的动作,从主键索引中拿出需要使用的行数据放入sort buffer,然后再根据行中的某个字段排序,最终返回结果.如果分配的内存不足以放下这些结果,那么就需要磁盘的临时文件来辅助排序,否则可以直接在内存中完成排序.运行流程为:

1. 从主键索引中取出要返回的行数据
2. 根据返回的行数据中的字段进行排序
3. sort buffer足够,在内存中完成排序,否则使用临时文件辅助排序
4. 将结果返回

如果内存足够,使用全字段排序更好,减少一次回表,也就减少了对磁盘的访问

rowid排序

另外mysql提供了一个叫做max_length_for_sort_data的参数,如果我们从行中所需要的数据总长度大于这个参数设置的值,那么一开始可以先取出排序所需要的字段,排序之后,再通过id去主键索引里面拿到行数据,通过这种方式,运行流程变成了:

1. 从主键索引中取出要排序的字段和主键id
2. 根据返回的排序字段进行排序
3. sort buffer足够,在内存中完成排序,否则使用临时文件辅助排序
4. 使用主键id再到主键索引中拿到行数据
5. 将结果返回

如果内存不够,使用rowid排序更好,但会增加对磁盘的访问

利用索引覆盖

在某些场景,根据具体的排序需求,可以直接建立索引并依靠索引的有序性来直接返回结果,这样就可以直接在索引层面上返回结果,无需依赖sort_buffer或者临时文件,这种排序方式在explain语句中的体现是使用了order by,但不会看到using file sort关键字,甚至可以不回到主键索引查询,直接通过辅助索引就可以返回所有数据,这种方式称为索引覆盖,在explain的extra中会出现using index.

参考资料

极客时间mysql45讲 16节 order by是如何工作的