总的来说,在MySQL中的命令有两种排序实现方式,一种是利用有序索引获取有序数据,另一种则是通过相应的排序算法,将取得的数据在内存中进行排序。
下面将通过实例分析两种排序实现方式及实现图解:
假设有表A和B两个表结构分别如下:
1,利用有序索引进行排序,实际上就是当我们查询的命令条件和查询的执行计划中所利用的索引 的索引键(或前面几个索引键)完全一致,且索引访问方式为响了,裁判或者指数的时候,MySQL 可以利用索引顺序而直接取得已经排好序的数据。这种方式的顺序由基本上可以说是最优的排序方式了,因为MySQL 不需要进行实际的排序操作。
假设我们在表A和B上执行如下SQL:
我们通过执行计划可以看的出,MySQL实际上并没有进行实际的排序操作,实际上其整个执行过程如下图所示:
2,通过相应的排序算法,将取得的数据在内存中进行排序方式,MySQL比需要将数据在内存中进行排序,所使用的内存区域也就是我们通过 sort_buffer_size系统变量所设置的排序区。这个排序区是每个线程独享的,所以说可能在同一时刻在MySQL 中可能存在多个排序缓冲区内存区域。
第二种方式在MySQL查询优化器所给出的执行计划(通过解释命令查看)中被称为 filesort。在这种方式中,主要是由于没有可以利用的有序索引取得有序的数据,MySQL只能通过将取得的数据在内存中进行排序然后再将数据返回给 客户端。在MySQL中filesort 的实现算法实际上是有两种的,一种是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息,然后在排序缓冲区 中进行排序。另外一种是一次性取出满足条件行的所有字段,然后在排序缓冲区中进行排序。
在MySQL4.1版本之前只有第一种排序算法,第二种算法是从 MySQL4.1开始的改进算法,主要目的是为了减少第一次算法中需要两次访问表数据的IO操作,将两次变成了一次,但相应也会耗用更多的 排序缓冲区空间。当然,MySQL4.1开始的以后所有版本同时也支持第一种算法,MySQL主要通过比较我们所设定的系统参数 max_length_for_sort_data的大小和查询语句所取出的字段类型大小总和来判定需要使用哪一种排序算法。如果 max_length_for_sort_data更大,则使用第二种优化后的算法,反之使用第一种算法,所以如果希望秩序 操作的效率尽可能的高,一定要主义max_length_for_sort_data 参数的设置。曾经就有同事的数据库出现大量的排序等待,造成系统负载很高,而且响应时间变得很长,最后查出正是因为MySQL 使用了传统的第一种排序算法而导致,在加大了max_length_for_sort_data 参数值之后,系统负载马上得到了大的缓解,响应也快了很多。
我们再看看MySQL需要使用filesort实现排序的实例。
假设我们改变一下我们的查询,换成通过A.c2来排的序,再看看情况:
MySQL从表中取出了符合条件的数据,由于取得的数据并不满足订单的条件,所以MySQL进行了filesort操作,其整个执行过程如下图所示:
在MySQL中,filesort操作还有一个比较奇怪的限制,那就是其数据源必须是来源于一个 表,所以,如果我们的排序数据如果是两个(或者更多个)表通过加入所得出的,那么MySQL 必须通过先创建一个临时表(临时表),然后再将此临时表的数据进行排序,如下例所示:
rk这个执行计划的输出还是有点奇怪的,不知道为什么,MySQL查询优化器将使用临时“过程显示在第一行对桌子的操作中,难道只是为让执行计划的输出少一行?
实际执行过程应该是如下图所示:
