最近的文章都是用的自己拍的照片。住的地方附近有个小花园，今天下了点雨，下午去花园散步的时候觉得上面的露珠很漂亮，就顺手拍了一张。

现在的年轻人生活都太忙碌，很少有时间去仔细观察大自然的美。但有时候停下来看看，可能内心会平静一些，聚焦当下，烦恼会少一点，快乐会多一点。



by会用索引吗 group mysql group by 使用索引_java

前两天项目上有一个查询的需求，需要用到group by某个字段后，聚合查询另一个字段的累加和（SUM聚合），同时where条件和group条件还有点不一样。评估数据量后使用的单表，后期数据量可能会不断增大，所以需要评估一下性能上的影响。这块我之前是不太了解的，对于这种查询，有没有使用到索引，该如何建索引？

于是我查了一下资料，发现MySQL官方文档里就有比较详尽的解释，自己也在docker里建了一个表实际操作了一下，里面的规则还是有点复杂的。所以写篇文章好好介绍一下这个问题。

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)

松散扫描有以下限制：

  1. 查询必须是单表，跨表不行
  2. group by的列必须符合索引的最左匹配原则
  3. select中，只有MIN和MAX聚合函数能用，里面的列必须在索引中，并且必须紧邻在group by列的后面
  4. 除了MIN和MAX函数的参数外，查询中的列如果不在group by中，那where条件中这一列只能是“等于一个常量”，而如果在group by的列中，可以使用大于和小于等条件。
  5. 字符串的前缀索引不适用，比如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虽然不能使用松索引扫描，但可以使用紧索引扫描，避免全表扫描和创建临时表。

  1. 虽然group by c1,c3不符合索引的最左匹配规则，但因为c2等于了一个常量，相当于填充了“缝隙”，所以也有效。
SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
  1. 虽然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会根据数据的分布情况来测算，优化器来做最后的决定。

我目前主要有两个疑惑。

第一个疑惑是，在实验中，我建了跟文章中一样的表和索引，但EXPLAIN却发现并不是所有的SQL都使用了松索引扫描，有很多SQL的Extra显示的是Using where; Using index。我猜测是因为自己数据量不够大（只有几条数据）或者数据类型不一样（文章是字符串类型，自己建的是数字类型）导致的，MySQL优化器也会根据数据量去智能选择，这块有读者朋友有相关的文章或者实验可以留言讨论一下。

第二个疑惑是，为什么只支持MIN，MAX，而不支持SUM、AVG函数？理论上来说他们的计算成本，所需空间应该都是差不多的。这块我在网上也没找到很好的答案，有线索的大佬也可以交流下。