order by除了加索引，我们还可以怎么优化？

Myth丶恋晨 2024-02-19 10:02 10阅读 0赞

大家好，我是小趴菜，在平常业务中，排序是一个十分常见的需求，但是有些时候，因为一些操作导致我们排序的效率特别的低下，这时候我们通常会给排序的字段添加索引来解决，除了给排序字段添加索引，我们是否还有其它的优化手段呢？

### order by原理 ###

既然要优化，那么我们就必须要明白order by的底层原理是怎么样的

复制代码

`select * from t_user where id > 10 order by age;`

*  1：MySql会根据where条件查询出所有符合条件的记录
 *  2：将查询的结果集放入sort\_buffer中(排序内存)
 *  3：对中间结果集按照ORDER BY字段排序
 *  4：回表生成完整的结果集(如果需要)

以上就是我们在执行上述SQL要执行的步骤，也是ORDER BY的实现原理,那么我们要优化的地方有以下几点

### 优化点一：中间结果集 ###

MySql首先会按照where条件查询出所有符合条件的记录，然后将结果集放入sort\_buffer中，但是这里要注意的是，sort\_buffer是有大小限制的，如果你的中间结果集大于这个sort\_buffer的大小，那么这个中间集就不会放入内存中进行排序，而是放在硬盘中了。

我们都知道在内存中排序比在硬盘中排序效率是高出特别多的，我们可以使用 \*show variables like '%sort\_buffer\_size%';\*来查看sort\_buffer的大小

![image.png][]

所以当你的内存足够大的时候，我们可以适当调大sort\_buffer的大小，让数据尽可能的在内存中排序，而不是进入到硬盘中

### 优化点二： 回表生成完整结果集 ###

我们看最后一步，如果需要ORDER BY需要回表生成结果集，什么是回表呢？就是拿到这条记录的主键ID，到主键索引中找到整条记录。

比如说，我们一张表有一百多个字段，这时候我们需要把这一百多个字段都查询出来的，那么这时候，中间结果集可能不会有所有的字段，而是*排序字段+主键*，举个例子，如下，假设我们t\_user表有一百多个字段

复制代码

`select * from t_user where id > 10 order by age;`

这时候首先会按照where条件查询出中间结果集，但是这时候的中间结果集可能不会有所有的字段，而是只有*age+id*二个字段，等排序结束以后，再拿主键回表查询整条记录，所以就造成了回表。

当要查询的字段小于这个阈值的时候，中间结果集就把所有字段都查出来，后续也就不需要回表了。但是当大于这个阈值的时候，就需要回表了

![image.png][image.png 1]

但是不能无脑的把这个值调大，来避免回表，还记得优化点一吗？？ 如果你中间结果集太大，就不会在内存中进行排序，而是在硬盘中排序了。

### 总结 ###

除了以上几点，我们最好是能使用到覆盖索引，这样就不用生成中间结果集中，可以直接输出结果了

[image.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/c91c8716999c46daa9251aa6a6aba7c3.png
[image.png 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/a14104cb45b54171bd316ac7dabdf02b.png

order by除了加索引，我们还可以怎么优化？

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 order by除了加索引，我们还可以怎么优化？

相关除了缓存，性能优化还可以这么搞？

相关 MySQL索引系列--索引的优化--ORDER BY

相关 mysql对于order by 索引的自我优化

相关 order by关键字优化

相关 MySql ORDER BY 索引优化

相关 MySQL Order By索引优化

相关 order by与索引

相关 order by怎么使用索引

相关 MySQL--索引Order By优化、Group By优化