mysql中group by用法（MySQLgroupby是如何使用索引的）
mysql中group by用法（MySQLgroupby是如何使用索引的）这两种方式的区别是，松索引扫描是在扫描的时候就进行聚合计算。而紧索引扫描会先进行一次范围扫描，然后聚合计算结果。MySQL会使用两种方式来做group by的优化：感兴趣的同学可以查看原文档：https://dev.mysql.com/doc/refman/8.0/en/group-by-optimization.html文档中第一段就指出，对于group by，大多数传统的方式都是扫描全表，然后建临时表来进行group操作和聚合操作。在一些case下，MySQL做得更好一些，会使用索引来避免创建临时表。这上来就自夸一波我是没想到的，不过夸的是自己，骂的是谁咱就不清楚了~接着指出了group by使用索引的前置条件，是所有的group by中的列，必须在同一个索引里面，但这只是前提条件，最终是不是使用索引还会受where条件和聚合函数的影响，这个我们在下面详细说。
最近的文章都是用的自己拍的照片。住的地方附近有个小花园，今天下了点雨，下午去花园散步的时候觉得上面的露珠很漂亮，就顺手拍了一张。
现在的年轻人生活都太忙碌，很少有时间去仔细观察大自然的美。但有时候停下来看看，可能内心会平静一些，聚焦当下，烦恼会少一点，快乐会多一点。
前两天项目上有一个查询的需求，需要用到group by某个字段后，聚合查询另一个字段的累加和（SUM聚合），同时where条件和group条件还有点不一样。评估数据量后使用的单表，后期数据量可能会不断增大，所以需要评估一下性能上的影响。这块我之前是不太了解的，对于这种查询，有没有使用到索引，该如何建索引？
于是我查了一下资料，发现MySQL官方文档里就有比较详尽的解释，自己也在docker里建了一个表实际操作了一下，里面的规则还是有点复杂的。所以写篇文章好好介绍一下这个问题。
感兴趣的同学可以查看原文档：https://dev.mysql.com/doc/refman/8.0/en/group-by-optimization.html
MySQL对group by的优化文档中第一段就指出，对于group by，大多数传统的方式都是扫描全表，然后建临时表来进行group操作和聚合操作。在一些case下，MySQL做得更好一些，会使用索引来避免创建临时表。这上来就自夸一波我是没想到的，不过夸的是自己，骂的是谁咱就不清楚了~
接着指出了group by使用索引的前置条件，是所有的group by中的列，必须在同一个索引里面，但这只是前提条件，最终是不是使用索引还会受where条件和聚合函数的影响，这个我们在下面详细说。
MySQL会使用两种方式来做group by的优化：
- 松索引扫描(Loose Index Scan)
- 紧索引扫描(Tight Index Scan)
这两种方式的区别是，松索引扫描是在扫描的时候就进行聚合计算。而紧索引扫描会先进行一次范围扫描，然后聚合计算结果。
Loose Index Scan当索引本身就能够覆盖到所有group的列的时候，MySQL就会使用松索引扫描。为什么叫松索引扫描呢？因为这种扫描方式只考虑索引中的部分key，而不是全部key。
如果使用了松索引扫描，你的查询语句explain会在Extra中显示：Using index for group-by (scanning)
松散扫描有以下限制：
- 查询必须是单表，跨表不行
- group by的列必须符合索引的最左匹配原则
- select中，只有MIN和MAX聚合函数能用，里面的列必须在索引中，并且必须紧邻在group by列的后面
- 除了MIN和MAX函数的参数外，查询中的列如果不在group by中，那where条件中这一列只能是“等于一个常量”，而如果在group by的列中，可以使用大于和小于等条件。
- 字符串的前缀索引不适用，比如c1 varchar(20)，索引c1(10)就不行。
我们假设有一个表t1，有c1 c2 c3 c4列。有一个索引idx(c1 c2 c3)。那下面这些查询就可以使用松索引扫描：
SELECT c1 c2 FROM t1 GROUP BY c1 c2;
SELECT DISTINCT c1 c2 FROM t1;
SELECT c1 MIN(c2) FROM t1 GROUP BY c1;
SELECT c1 c2 FROM t1 WHERE c1 < const GROUP BY c1 c2;
SELECT MAX(c3) MIN(c3) c1 c2 FROM t1 WHERE c2 > const GROUP BY c1 c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1 c2;
SELECT c1 c2 FROM t1 WHERE c3 = const GROUP BY c1 c2;
但下面这几种就不行：
# 限制3，只有MIN和MAX能用
SELECT c1 SUM(c2) FROM t1 GROUP BY c1;
# 限制2，gropu by里面不符合最左匹配
SELECT c1 c2 FROM t1 GROUP BY c2 c3;
# 限制4，c3不在group by的列中，且没有等于一个常量
SELECT c1 c3 FROM t1 GROUP BY c1 c2;
除了group by以外，松索引扫描还用于这几种情况的聚合函数：
- AVG DISTINCT SUM DISTINCT COUNT DISTINCT
- 在查询中没有GROUP BY 和 DISTINCT
- 仍然有上面说的那些限制
比如下面两个sql是可以用松索引扫描的：
SELECT COUNT(DISTINCT c1) SUM(DISTINCT c1) FROM t1;
SELECT COUNT(DISTINCT c1 c2) COUNT(DISTINCT c2 c1) FROM t1;Tight Index Scan
紧索引扫描会根据查询条件来决定是全索引扫描还是范围索引扫描。当条件不满足松散索引扫描时，尤其是不满足索引的最左匹配原则时，如果查询条件里面，有一些条件能够“填充”索引中的"缝隙"，MySQL也能够利用索引排好序的能力，直接完成group。
同样是上面的表t1，下面这两种sql虽然不能使用松索引扫描，但可以使用紧索引扫描，避免全表扫描和创建临时表。
- 虽然group by c1 c3不符合索引的最左匹配规则，但因为c2等于了一个常量，相当于填充了“缝隙”，所以也有效。
SELECT c1 c2 c3 FROM t1 WHERE c2 = 'a' GROUP BY c1 c3;
- 虽然group by c2 c3没有从c1开始，同样不符合索引的最左匹配规则，但c1等于了一个常量，也填充了“缝隙”。
SELECT c1 c2 c3 FROM t1 WHERE c1 = 'a' GROUP BY c2 c3;结论
再回到我自己遇到的问题。我大概是有一个表，sql是这样：
SELECT c1 SUM(c2)
FROM t1
WHERE c1 in(1 2)
GROUP BY c1
可以添加索引c1 c2。但由于松索引扫描不支持SUM函数，所以这个SQL应该是走不了松索引扫描的。通过explain结果，我推测它仍然可以使用索引来加快查询速度，但最后计算sum的时候，应该还是要创建临时表（但不确定会不会回查记录表）。
疑惑松索引扫描的性能是最高的，核心原理在于不扫描整棵索引树，只扫描必要的部分。其实除了group by以外，在正常的查询中，MySQL也可能使用松索引扫描来提升性能，是否使用松索引扫描，MySQL会根据数据的分布情况来测算，优化器来做最后的决定。
感兴趣的同学可以看这篇文章：https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#range-access-skip-scan
我目前主要有两个疑惑。
第一个疑惑是，在实验中，我建了跟文章中一样的表和索引，但EXPLAIN却发现并不是所有的SQL都使用了松索引扫描，有很多SQL的Extra显示的是Using where; Using index。我猜测是因为自己数据量不够大（只有几条数据）或者数据类型不一样（文章是字符串类型，自己建的是数字类型）导致的，MySQL优化器也会根据数据量去智能选择，这块有读者朋友有相关的文章或者实验可以留言讨论一下。
第二个疑惑是，为什么只支持MIN，MAX，而不支持SUM、AVG函数？理论上来说他们的计算成本，所需空间应该都是差不多的。这块我在网上也没找到很好的答案，有线索的大佬也可以交流下。
求个支持
我是Yasin，一个坚持技术原创的博主，我的微信公众号是：编了个程
都看到这儿了，如果觉得我的文章写得还行，不妨支持一下。
文章会首发到公众号，阅读体验最佳，欢迎大家关注。
你的每一个转发、关注、点赞、评论都是对我最大的支持！
还有学习资源、和一线互联网公司内推哦
