学习小结||MySQL的“order by”是怎样工作的？

为了防止全表查询，我们通常会在order by字段上面加上索引，这里我以菜品表（dish）为例，并且在给name字段加了索引。

 这时，你的SQL语句可以这么写

select name,price,category_id 
from dish 
where category_id = 11 
order by name desc limit 10

使用explain命令查看语句执行情况

Extra这个字段中的“Using filesort”表示的就是需要排序，MySQL就会给每个线程分配一块内存用于排序，称为sort_buffer。

MySQL对于排序有两种不同的算法。

1.全字段排序

通常情况下，这个语句执行流程如下所示

1. 初始化sort_buffer,将name，price，category_id三个字段放入。
2. 从索引中找到满足条件的主键ID，获取整行数据，取name，price，category_id三个字段的值存入sort_buffer中。
3. 对sort_buffer中的数据按照字段name做快速排序。
4. 按照排序结果去前10行数据返回给客户端。

“按照name排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size.

sort_buffer_size，就是MySQL为排序开辟的内存（sort_buffer）的大小。如果排序的数量小于sort_buffer_size，排序就在内存中完成，如果排序数量太大，内存放不下，则会利用磁盘临时文件辅助排序。

2.rowid排序

对于全字段排序算法过程中，有一个问题，就是如果查询要返回的字段很多的话，那么sort_buffer里面要存放的字段数太多，这样内存里能同时放下的行数很少，要分成很多临时文件，排序性能会很差。

我们修改一个参数，让MySQL采用另外一种算法。

SET max_length_for_sort_data =16;

max_length_for_sort_data，是MySQL中专门控制用于排序的行数据的长度的一个参数。它的意思是如果单行长度超过这个值，MySQL就认为单行太大，要换一个算法。

执行流程：

1. 初始化sort_buffer，确定放入两个字段，即name和id；
2. 从索引name找到满足条件的主键id，获取整行数据，去name，id这两个字段，存入sort_buffer中。
3. 对sort_buffer中的数据按照name进行排序；
4. 遍历排序结果，去前10行，并按照主键id值回原表取name，price，category_id三个字段值返回给客户端。