MySQL：在SQL里进行排序或者分组的时候，如何才能用到索引

最新推荐文章于 2023-07-24 19:21:33 发布

OceanStar的学习笔记

最新推荐文章于 2023-07-24 19:21:33 发布

阅读量2.7k

点赞数 2

原文链接：https://time.geekbang.org/

版权

数据库专栏收录该内容

289 篇文章 20 订阅

订阅专栏

本文详细介绍了如何在SQL查询中利用索引来优化ORDER BY和GROUP BY语句的性能。通过创建合适的联合索引，并确保ORDER BY字段顺序与索引顺序一致，可以避免磁盘排序和文件排序，显著提高查询速度。同时，对于GROUP BY，应确保分组字段与索引中的字段匹配，以利用索引进行快速分组。理解并正确应用这些原则，能够有效提升数据库系统的查询效率。

摘要由CSDN通过智能技术生成

当我们的SQL语句里使用order by语句进行排序的时候，如何才能用上索引？

没有索引时的情况：

通常而言，我们自己想象一下，假设有一个select * from table where xxx = xxx order by xxx这样的一个SQL语句，似乎应该是基于where语句通过索引快速筛选出来的一波数据，接着放在内存里，或者放在一个临时磁盘文件里，然后通过排序算法按照某一个字段走一个排序，最后把排序号的数据返回。
但是这么做的话速度会很慢，尤其是你要排序的数据量比较大的话，还不能用内存来排序，如果基于磁盘文件来排序，那在MySQL中有一个术语，叫做filesort，那速度是相当慢了。
通常我们尽量不要这么做，尤其是类似于select * from table order by xx1，xx2，xx3 limit 100这样的SQL语句，按照多个字段进行排序然后返回排名前100条数据。类似的语句常见于分页SQL语句里，可能需要对表里的数据进行一定的排序，然后走一个limit拿出来指定部分的数据。
如果是纯粹把一坨数据放到一个临时磁盘文件里，然后直接硬上各种排序算法在磁盘文件里搞一通排序，接着按照你指定的要求走limit语句拿到指定分页的数据，这简直会让SQL的速度慢到家了！

怎么优化？

通常而言，这种情况下，假设我们建立一个叫做INDEX(xx1, xx2, xx3)这样的一个联合索引，这个时候默认情况下在索引树里本身就是按照xx1、xx2、xx3三个字段的值去排序的，那么此时你再运行select * from table order by xx1，xx2，xx3 limit 100这样的SQL语句，就不需要在什么临时磁盘文件里排序了
因为它要求的不过按照xx1、xx2、xx3三个字段进行排序，在联合索引的索引树里已经排序好了。因此可以直接按照索引树里的顺序，把xx1，xx2、xx3三个字段按照从小到大的值获取前面100条就可以了，然后拿到100条数据的主键再去聚簇索引里回表查询剩余的所有的字段。

所以，在SQL语句里，应该尽量最好是按照联合索引的字段值的字段顺序去进行order by排序，这样就可以直接利用联合索引树里的数据有序性，到索引树里直接按照字段值的顺序去获取你需要的数据了。

但是这里有一些限定的规则：

因为联合索引里的字段值在索引树里都是从小到大依次排列的，所以你在order by里要不然就是每个字段后面什么都不加，直接就是order by xx1，xx2，xx3，要么就是都加DESC降序排列，就是order by xx1 DESC，xx2 DESC，xx3 DESC
如果都是升序排列，直接就从索引树里最下的开始读取一定条数就可以了；要是都是降序排列，就从索引树里最大的数据开始去读一定的条数就可以了；但是你不能order by语句里有的字段升序有的字段降序，那是不能用索引的
另外，要是order by语句里有的字段不在联合索引里，或者是你对order by语句里的字段用了复杂的函数，这些也不能使用索引区排序了

当我们的SQL语句里使用group by语句进行分组的时候，如何才能用上索引？

假设你要是走一个类似select count(*) from table group by xx的SQL语句，似乎看起来必须把你所有的数据放到一个临时磁盘文件里还有加上部分内存，然后按照指定字段的值分成一组一组的，接着对每一组执行一个聚合函数。这个性能也是极差的，因为毕竟涉及大量的磁盘交互。
因为在我们的索引树里默认都是按照指定的一些字段都排序好的，而且字段值相同的数据都是在一起的。假设要是走索引去执行分组后再聚合，那性能一定比临时磁盘文件去执行要好很多了
所以通常而言，对于group by后的字段，最好也是按照联合索引里的最左侧的字段开始，按照顺序排列开来。这样的话，就可以完美的运用上索引来直接提取一组一组的数据，然后针对每一组的数据执行聚合函数就可以了

group by和order by用上索引的原理和条件都是差不多的，本质都是在group by和order by之后的字段顺序和联合索引中的从最左侧开始的字段顺序一致，然后就可以充分利用索引树里已经完成排序的特性，快速的根据排序号的数据执行后继操作了。

这样就不再需要针对杂乱无章的数据利用临时磁盘文件加上部分内存数据结构进行耗时耗力的现场排序和分组。