MySQL中的排序

在编写SQL 语句时常常会用到 order by 进行排序，那么排序过程是什么样的？为什么有些排序执行比较快，有些排序执行很慢？又该如何去优化？

索引排序

索引排序指的是在通过索引查询时就完成了排序，从而不需要再单独进行排序，效率高。索引排序是通过联合索引实现的。因为联合索引是从最左边的列开始起按大小顺序进行排序，如下图。

比如现在查询条件是 where sex=1 order by name，那么查询过程就是会找到满足 sex=1 的记录，而符合这条的所有记录一定是按照 name 排序的，所以也不需要额外进行排序。而如果是 where sex >1 order by name，那么根据 sex>1 得到的记录 sex 值并不是固定值，所以得到的记录是按照 sex，其次才是 name 进行排列的。也就没有实现索引排列。

 

额外排序

额外排序就是需要额外进行排序，也就是在 explain 的 extra 列中出现 using filesort。可以分别两种方式来看待。

按执行位置划分

1、Sort_Buffer

MySQL 为每个线程各维护了一块内存区域 sort_buffer ，用于进行排序。sort_buffer 的大小可以通过 sort_buffer_size 来设置。如果加载的记录字段总长度（可能是全字段也可能是 rowid排序的字段）小于 sort_buffer_size 便使用 sort_buffer 排序；如果超过则使用 sort_buffer + 临时文件进行排序。

2、Sort_Buffer + 临时文件

MySQL 会使用临时文件搭配 Sort_Buffer 进行排序。主要是使用归并算法来得出最终排序后的结果。

临时文件种类：

临时表种类由参数 tmp_table_size 与临时表大小决定，如果内存临时表大小超过 tmp_table_size ，那么就会转成磁盘临时表。因为磁盘临时表在磁盘上，所以使用内存临时表的效率是大于磁盘临时表的。

1、内存临时表　　

2、磁盘临时表　　　　磁盘临时表默认使用的是 InnoDB，如果想要切换执行引擎，可以修改参数 internal_tmp_disk_storage_engine。 

 

按执行方式划分

执行方式是由 max_length_for_sort_data 参数与用于排序的单条记录字段长度决定的，如果用于排序的单条记录字段长度 <= max_length_for_sort_data ，就使用全字段排序；反之则使用 rowid 排序。

1、全字段排序

全字段排序就是将查询的所有字段全部加载进来进行排序。

优点：查询快，执行过程简单

缺点：需要的空间大。

例子（不考虑临时文件）：select city,name,age from t where city='杭州' order by name limit 1000  ;    city 有索引

1、初始化 sort_buffer，确定放入两个字段，即 name 和 id；
2、从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
3、到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
4、从索引 city 取下一个记录的主键 id；
5、重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；
6、对 sort_buffer 中的数据按照字段 name 进行排序；
7、遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

2、rowid 排序

rowid 表示位置信息，如果整张表有主键那么 rowid 就是主键，如果没有主键就会自动创建一个 6 字节的唯一标识。所以 rowid 排序就表示只加载用于排序的字段以及 rowid ，然后进行排序，然后根据排序好的 rowid 去表中回表查询所要的结果。

缺点：会产生更多次数的回表查询，查询可能会慢一些。 

优点：所需的空间更小。

例子（不考虑临时文件）：select city,name,age from t where city='杭州' order by name limit 1000  ;    city 有索引

1、初始化 sort_buffer，确定放入两个字段，即 name 和 id；
2、从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
3、到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
4、从索引 city 取下一个记录的主键 id；
5、重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；
6、对 sort_buffer 中的数据按照字段 name 进行排序；
7、遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

 

执行案例分析

rand() 执行

 select word from words order by rand() limit 3;  　　表数据有10000行  　　　　SQL是从10000行记录中随机获取3条记录返回。

分析： 这里查询的字段只有一个，所以使用全字段查询。 加上记录数过多，但是单条记录的字段长度不长，所以会使用 sort_buffer + 内存临时表。所以总结来看这条语句会使用 全字段查询 + sort_buffer + 内存临时表 来排序。

执行过程：

1、从缓冲池依次读取记录，每次读取后都调用 rand() 函数生成一个 0-1 的数存入内存临时表，W 是 word 值，R 是 rand() 生成的随机数。到这扫描了 10000 行。

2、初始化 sort_buffer，从内存临时表中将 rowid（这张表自动生成的） 以及 排序数据 R 存入 sort_buffer。到这因为要遍历内存临时表所以又扫描了 10000 行。

3、在 sort_buffer 中根据 R 排好序，然后选择前三个记录的 rowid 逐条去内存临时表中查到 word 值返回。到这因为取了三个数据去内存临时表去查找所以又扫描了 3 行。总共 20003 行。

 

rand() 优化

通过上面的例子可以看出当要从表中随机获取几条记录使用 rand() 函数是非常消耗资源的，同时触发了 Using temporary（使用了临时表） 和 Using filesort（使用了额外排序）。并且进行了 20003 行记录的扫描，非常消耗资源。所以我们可以自己去计算一个随机值，避免使用 rand() 函数。

查询随机的一条记录：

1、取得整个表的行数，并记为 C。　
2、取得 Y = floor(C * rand())。floor 函数在这里的作用，就是取整数部分。 　　　
3、再用 limit Y,1 取得一行。　　　　　　　　

select count(*) into @C from t;
set @Y = floor(@C * rand());
set @sql = concat("select * from t limit ", @Y, ",1");
prepare stmt from @sql;
execute stmt;
DEALLOCATE prepare stmt;

 

如果查询多条，只要将第二步执行多次，然后依次执行就可以了。

使用这样的方式就可以避免 MySQL 去使用临时表以及 filesort 排序，提高执行效率。

 

优先队列排序算法

在 5.6 中对排序算法进行一些优化，之前使用的是搭配临时表使用 归并排序算法，这种方式会对所有的记录都进行排序，消耗了不必要的资源例如有一个 20000 行记录的表，执行  select word from words order by rand() limit 3; 

因为这条语句只取三条记录，对这剩余的 19993 行进行排序比较浪费CPU资源且耗时。所以在 5.6 中提出了使用 优先队列排序算法。还是以这个例子为例，因为查询的字段只有一个，且查询的行数很多，所以还是使用 全字段查询 + sort_buffer + 内存临时表 。

过程：先读取前三行记录并为其分别通过 rand() 函数为其设置一个0-1的随机数，取这三条记录的 rowid、随机数组成一个堆，然后依次设置随机数并与当前堆中的随机数比较。如果这个随机数比堆中某个记录的随机数小，就替换，然后移除，如果没有小的就直接移除，取下一个。最后根据堆中的 rowid 去临时表中读取对应的 word 值返回。

失效场景：因为要拿指定的记录数的排序数据以及rowid去挨个比较，所以如果需要返回的记录数过多，导致所有的字段长度超过了设置的 sort_buffer_size ，那么此算法就会失效。

 

索引排序案例

问题：有 (city,name) 联合索引，select * from t where city in (“杭州”," 苏州 ") order by name limit 100; 这个 SQL 语句是否需要排序？有什么方案可以避免排序？

答案：需要排序。因为city 的条件有两个，总体上来看就是以 city优先进行排序的。可以优化成下面三步：

1、执行 select * from t where city=“杭州” order by name limit 100; 这个语句是不需要排序的，客户端用一个长度为 100 的内存数组 A 保存结果。
2、执行 select * from t where city=“苏州” order by name limit 100; 用相同的方法，假设结果被存进了内存数组 B。
3、现在 A 和 B 是两个有序数组，然后你可以用归并排序的思想，得到 name 最小的前 100 值，就是我们需要的结果了。

 

如果将 " limit 100" 改成 " limit 10000,100 "。可以优化成下面三步：

1、select id,name from t where city="杭州" order by name limit 10100;
2、select id,name from t where city="苏州" order by name limit 10100。
3、用归并排序的方法取得按 name 顺序第 10001~10100 的 name、id 的值，然后拿着这 100 个 id 到数据库中去查出所有记录。

 

优化总结

优化总体上就是围绕 “尽量不使用额外排序，避免使用临时表” 的原则。

1、尽量使用索引完成排序，如果该查询语句执行的频率比较高，可以为其创建一个联合索引。而如果使用的频率很低，那么就不需要去创建，因为索引的维护需要成本。

2、如果需要额外去排序，那么可以适当调整 sort_buffer_size（sort_buffer） 和 tmp_table_size（内存临时表） ，使排序只在内存中执行。

3、如果 sort_buffer 空间设置足够大，也可以适当调整 max_length_for_sort_data 的值，使用全字段排序。

4、对于一些比较耗时的函数可以自定义算法去实现，避免计算过程在 MySQL 中实现。