点击↑上方↑蓝色“编了个程”关注我~

e4ea5ee1ab460c758c6dd269f4ff963f.png

这是Yasin的第 64 篇原创文章

b9293af5a63f0d16955a814d84cd542c.png

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

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

97417c6c3795657a8b4797b1b670fccd.png

前两天项目上有一个查询的需求,需要用到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)

<-

松散扫描有以下限制:

  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会根据数据的分布情况来测算,优化器来做最后的决定。

->

感兴趣的同学可以看这篇文章: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函数?理论上来说他们的计算成本,所需空间应该都是差不多的。这块我在网上也没找到很好的答案,有线索的大佬也可以交流下。

568038d804b9883327f97f3a513ca8d8.png

关于作者

我是Yasin,一个爱写博客的技术人

微信公众号:编了个程(blgcheng)

个人网站:https://yasinshaw.com

欢迎关注这个公众号ff183658d3923bdef6b25ca36b9ca6ed.png

4c944039480cb9121909a58f62efd6a5.png

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐