实习生疑问：为什么要在需要排序的字段上加索引呢？

作者：架构精进之路

2023-04-19
北京
本文字数：1946 字
阅读完需：约 6 分钟

hello，大家好，我是张张，「架构精进之路」公号作者。

众所周知，为了避免全表扫描，条件句中增加了索引，性能上对比一目了然。

组内实习生同学不禁疑问：为什么要在排序的字段上添加索引呢？

排序有好多种算法来实现，在 MySQL 中经常会带上一个 limit，表示从排序后的结果集中取前 100 条，或者取第 n 条到第 m 条。

要实现排序，我们需要先根据查询条件获取结果集，然后在内存中对这个结果集进行排序，如果结果集数量特别大，还需要将结果集写入到多个文件里，然后单独对每个文件里的数据进行排序，然后在文件之间进行归并，排序完成后在进行 limit 操作。

没错，这个就是 MySQL 实现排序的方式，前提是排序的字段没有索引。

建表操作

CREATE TABLE `person` (  `id` int(11) NOT NULL,  `city` varchar(16) NOT NULL,  `name` varchar(16) NOT NULL,  `age` int(11) NOT NULL,  `addr` varchar(128) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `city` (`city`)) ENGINE=InnoDB;

复制代码

查询示例

select city,name,age from person where city='南京' order by name limit 100  ;

复制代码

使用 explain 发现该语句会使用 city 索引，并且会有 filesort。

我们分析下该语句的执行流程：

1.初始化 sortbuffer，用来存放结果集；
2.找到 city 索引，定位到 city 等于南京的第一条记录，获取主键索引 ID；
3.根据 ID 去主键索引上找到对应记录，取出 city,name,age 字段放入 sortbuffer；
4.在 city 索引取下一个 city 等于南京的记录的主键 ID；
5.重复上面的步骤，直到所有 city 等于南京的记录都放入 sortbuffer；
6.对 sortbuffer 里的数据根据 name 做快速排序；
7.根据排序结果取前面 1000 条返回。

这里是查询 city，name，age 3 个字段，比较少，如果查询的字段较多，则多个列如果都放入 sortbuffer 将占有大量内存空间。另一个方案是只区出待排序的字段和主键放入 sortbuffer 这里是 name 和 id，排序完成后在根据 id 取出需要查询的字段返回，其实就是时间换取空间的做法，这里通过 max_length_for_sort_data 参数控制，是否采用后面的方案进行排序。

另外如果 sortbuffer 里的条数很多，同样会占有大量的内存空间，可以通过参数 sort_buffer_size 来控制是否需要借助文件进行排序，这里会把 sortbuffer 里的数据放入多个文件里，用归并排序的思路最终输出一个大的文件。

关于 sortbuffer，官方文档：dev.mysql.com/doc/refman/…

以上方案主要是 name 字段没有加上索引，如果 name 字段上有索引，由于索引在构建的时候已经是有序的了，所以就不需要进行额外的排序流程只需要在查询的时候查出指定的条数就可以了，这将大大提升查询速度。我们现在加一个 city 和 name 的联合索引。

alter table person add index city_user(city, name);

复制代码

使用 explain 发现该语句会使用 city_user 索引，并且没有了 filesort。

这样查询过程如下：

1.根据 city,name 联合索引定位到 city 等于武汉的第一条记录，获取主键索引 ID
2.根据 ID 去主键索引上找到对应记录，取出 city,name,age 字段作为结果集返回
3.继续重复以上步骤直到 city 不等于武汉，或者条数大于 1000

由于联合所以在构建索引的时候，在 city 等于武汉的索引节点中的数据已经是根据 name 进行排序了的，所以这里只需要直接查询就可，另外这里如果加上 city, name, age 的联合索引，则可以用到索引覆盖，不行到主键索引上进行回表。

其实，Order By 有两种排序方法：

Backward index scan：使用索引扫描。索引本身就是有序的，所以不需要再次进行排序
using filessort：在内存中排序，占用 CPU 资源。如果查询结果太大还会产生临时文件，到磁盘中进行排序，这时候会进行大量 IO 操作性能较差

其实这个 SQL 是分三步来执行的：

where 得到数据；
排序处理数据首先看执行计划是不是用到索引，如果用到了就可以直接获得索引的顺序，从而避免再次排序，如果没用到就做排序（using filessort）；
返回数据。

总结

最后，简单总结一下：

Order By 语句跟 WHERE 语句中都用了索引字段，Order By 中的索引才会生效
Order By 中使用索引可避免重新排序导致 CPU 资源浪费

我们在有排序操作的时候，最好能够让排序字段上建有索引，另外由于查询第一百万条开始的一百条记录，需要过滤掉前面一百万条记录，即使用到索引也很慢，所以可以根据 ID 来进行区分，分页遍历的时候每次缓存上一次查询结果最后一条记录的 id ，下一次查询加上 id > xxxx limit 0,1000 这样可以避免前期扫描到的结果被过滤掉的情况。

希望今天的讲解对大家有所帮助，谢谢！

Thanks for reading!