为了防止全表查询,我们通常会在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.全字段排序
通常情况下,这个语句执行流程如下所示
- 初始化sort_buffer,将name,price,category_id三个字段放入。
- 从索引中找到满足条件的主键ID,获取整行数据,取name,price,category_id三个字段的值存入sort_buffer中。
- 对sort_buffer中的数据按照字段name做快速排序。
- 按照排序结果去前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就认为单行太大,要换一个算法。
执行流程:
- 初始化sort_buffer,确定放入两个字段,即name和id;
- 从索引name找到满足条件的主键id,获取整行数据,去name,id这两个字段,存入sort_buffer中。
- 对sort_buffer中的数据按照name进行排序;
- 遍历排序结果,去前10行,并按照主键id值回原表取name,price,category_id三个字段值返回给客户端。