注册 登录
  • 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

MySQL查询优化:GROUP BY

MySQL SQL 彭东稳 13638次浏览 已收录 0个评论

一、GROUP BY Optimization

GROUP BY操作在没有合适的索引可用的时候,通常先扫描整个表提取数据并创建一个临时表,然后按照GROUP BY指定的列进行排序。在这个临时表里面,对于每一个group的数据行来说是连续在一起的。完成排序之后,就可以发现所有的GROUPS,并可以执行聚集函数(aggregate function)。可以看到,在没有使用索引的时候,需要创建临时表和排序。在执行计划中通常可以看到“Using temporary; Using filesort”。

GROUP BY实际上也同样可以使用排序操作,而且与ORDER BY相比,GROUP BY主要只是多了排序之后的分组操作。当然,如果在分组的时候还使用了其他的一些聚合函数,那么还需要一些聚合函数的计算。所以,在GROUP BY的实现过程中,与ORDER BY一样也可以利用到索引。

MySQL建立的索引(B+Tree)通常是有序的,如果通过读取索引就完成group by操作,那么就可避免创建临时表和排序。因而使用索引进行group by的最重要的前提条件是所有group by的参照列(分组依据的列)来自于同一个索引,且索引按照顺序存储所有的keys(即BTREE index,而HASH index没有顺序的概念)。

MySQ有三种索引扫描方式完成GROUP BY操作,分别是松散索引扫描和紧凑索引扫描以及临时表实现GROUP BY。在松散索引扫描方式下,分组操作和范围预测(如果有的话)一起执行完成的。在紧凑索引扫描方式下,先对索引执行范围扫描(range scan),再对结果元组进行分组。

1、松散索引扫描(Loose Index Scan)

何谓松散索引扫描实现GROUP BY呢?实际上就是当MySQL完全利用索引扫描来实现GROUP BY的时候,并不需要扫描所有满足条件的索引键即可完成操作得出结果。

松散索引扫描相当于Oracle中的跳跃索引扫描(skip index scan),就是不需要连续的扫描索引中的每一个元组,扫描时仅考虑索引中的一部分。当查询中没有where条件的时候,松散索引扫描读取的索引元组的个数和groups的数量相同,如果where条件包含范围预测,松散索引扫描查找每个group中第一个满足范围条件,然后再读取最少可能数的keys。松散索引扫描只需要读取很少量的数据就可以完成group by操作,因而执行效率非常高。使用松散索引扫描需要满足以下条件:

1)查询在单一表上。

2)group by指定的所有列是索引的一个最左前缀,并且没有其它的列。比如表t1( c1,c2,c3,c4)上建立了索引(c1,c2,c3)。如果查询包含“group by c1,c2”,那么可以使用松散索引扫描。但是“group by c2,c3”(不是索引最左前缀)和“group by c1,c2,c4”(c4字段不在索引中)无法使用。

3)如果在选择列表select list中存在聚集函数,只能使用min()和max()两个聚集函数,并且指定的是同一列(如果min()和max()同时存在),这一列必须在索引中,且紧跟着group by指定的列。比如,select t1,t2,min(t3),max(t3) from t1 group by c1,c2。

4)如果查询中存在除了group by指定的列之外的索引其他部分,那么必须以常量的形式出现(除了min()和max()两个聚集函数)。比如,select c1,c3 from t1 group by c1,c2不能使用松散索引扫描。而select c1,c3 from t1 where c3 = 3 group by c1,c2可以使用松散索引扫描。

5)索引中的列必须索引整个数据列的值(full column values must be indexed),而不是一个前缀索引。比如,c1 varchar(20), INDEX (c1(10)),这个索引没发用作松散索引扫描。

如果查询能够使用松散索引扫描,那么执行计划中Etra中提示“ using index for group-by”。下面我们通过一个示例来描述松散索引扫描实现GROUP BY:

我们看到在执行计划的Extra信息中有信息显示“Using index for group-by”,实际上这就是告诉我们,MySQL Query Optimizer通过使用松散索引扫描来实现了我们所需要的GROUP BY操作。

下面这张图片描绘了扫描过程的大概实现:

MySQL查询优化:GROUP BY

假设idx(c1,c2,c3)表上有一个索引t1(c1,c2,c3,c4),松散索引扫描访问方法可用于以下查询:

执行以下查询无法使用松散索引扫描:

  • 除了MIN()或MAX()之外还有聚合功能:

  • GROUP BY子句中的列不会形成索引的最左前缀:

  • 查询列与GROUP BY不相等:

要查询包含WHERE c3=const可以使用松散索引扫描。

除了已经支持的MIN()和MAX()引用之外,松散索引扫描访问方法可以应用于选择列表中的其他形式的聚合函数引用,如:AVG(DISTINCT),SUM(DISTINCT)、COUNT(DISTINCT)。AVG(DISTINCT)和SUM(DISTINCT)采取单个列参数,COUNT(DISTINCT)可以有多个列参数。

假设idx(c1,c2,c3)表上有一个索引t1(c1,c2,c3,c4),松散索引扫描访问方法可用于以下查询:

为什么松散索引扫描的效率会很高?

因为在没有WHERE子句,也就是必须经过全索引扫描的时候, 松散索引扫描需要读取的键值数量与分组的组数量一样多,也就是说比实际存在的键值数目要少很多。而在WHERE子句包含范围判断式或者等值表达式的时候, 松散索引扫描查找满足范围条件的每个组的第1个关键字,并且再次读取尽可能最少数量的关键字。

2、紧凑索引扫描(Tight Index Scan)

紧凑索引扫描可能是全索引扫描或者范围索引扫描,取决于查询条件。当松散索引扫描条件没有满足的时候,group by仍然有可能避免创建临时表。如果where条件有范围扫描,那么紧凑索引扫描仅读取满足这些条件的keys(索引元组),否则执行全索引扫描。这种方式读取所有where条件定义的范围内的keys,或者扫描整个索引,因而称作紧凑索引扫描。对于紧凑索引扫描,只有在所有满足范围条件的keys被找到之后才会执行分组操作。

如果紧凑索引扫描起作用,那么必须满足:

在查询中存在常量相等的where条件字段(索引中的字段),且该字段在group by指定的字段的前面或者中间,来自于相等条件的常量能够填充搜索keys中的间隙,因而可以构成一个索引的完整前缀,索引前缀能够用于索引查找。而如果需要排序GROUP BY结果,并且能够形成索引前缀的搜索关键字,MySQL还可以避免额外的排序操作,因为使用有顺序的索引的前缀进行搜索已经按顺序检索到了所有关键字。

紧凑索引扫描实现GROUP BY和松散索引扫描的区别主要在于他需要在扫描索引的时候,读取所有满足条件的索引键,然后再根据读取的数据来完成GROUP BY 操作得到相应结果。

这时候的执行计划的Extra信息中已经没有“Using index for group-by”了,但并不是说MySQL的GROUP BY操作并不是通过索引完成的,只不过是需要访问WHERE条件所限定的所有索引键信息之后才能得出结果。这就是通过紧凑索引扫描来实现GROUP BY的执行计划输出信息。

假设idx(c1,c2,c3)表上有一个索引t1(c1,c2,c3,c4),以下查询不适用于之前描述的松散索引扫描访问方法,但仍然可以使用紧凑索引扫描访问方法。

  • 这GROUP BY有一个间隙,但它被覆盖的条件为c2 = ‘a’:

  • 这GROUP BY不是从列的第一部分开始,但是有一个条件为该部分提供了一个常数:

在MySQL中,MySQL Query Optimizer首先会选择尝试通过松散索引扫描来实现GROUP BY操作,当发现某些情况无法满足松散索引扫描实现GROUP BY的要求之后,才会尝试通过紧凑索引扫描来实现。

当GROUP BY条件字段并不连续或者不是索引前缀部分的时候,MySQL Query Optimize无法使用松散索引扫描,设置无法直接通过索引完成GROUP BY操作,因为缺失的索引键信息无法得到。但是,如果Query语句中存在一个常量值来引用缺失的索引键,则可以使用紧凑索引扫描完成GROUP BY操作,因为常量填充了搜索关键字中的“间隙”,可以形成完整的索引前缀,这些索引前缀可以用于索引查找。而如果需要排序GROUP BY结果,并且能够形成索引前缀的搜索关键字,MySQL还可以避免额外的排序操作,因为使用有顺序的索引的前缀进行搜索已经按顺序检索到了所有关键字。

3、使用临时表实现GROUP BY

MySQL在进行GROUP BY操作的时候要想利用索引,必须满足GROUP BY的字段必须同时存放于同一个索引中,且该索引是一个有序索引(如Hash索引就不能满足要求)。而且,并不只是如此,是否能够利用索引来实现GROUP BY还与使用的聚合函数也有关系。

前面两种GROUP BY的实现方式都是在有索引可以利用的时候使用的,当MySQL Query Optimizer无法找到合适的索引可以利用的时候,就不得不先读取需要的数据,然后通过临时表来完成GROUP BY操作。

这次的执行计划非常明显的告诉我们MySQL通过索引找到了我们需要的数据,然后创建了临时表,又进行了排序操作,才得到我们需要的GROUP BY结果。当MySQL Query Optimizer发现仅仅通过索引扫描并不能直接得到GROUP BY的结果之后,他就不得不选择通过使用临时表然后再排序的方式来实现GROUP BY了。

在这样示例中即是这样的情况。 group_id并不是一个常量条件,而是一个范围,而且GROUP BY字段为user_id。所以MySQL无法根据索引的顺序来帮助GROUP BY的实现,只能先通过索引范围扫描得到需要的数据,然后将数据存入临时表,然后再进行排序和分组操作来完成GROUP BY。

以上就介绍了MySQL查询优化MySQL分组查询Group By实现原理详解,包括了MySQL查询优化方面的内容,希望对MySQL有兴趣的朋友有所帮助。

二、排序不一致问题

源起,阿里云论坛有人反应MySQL 5.6分页有重复值(排序字段没有用索引,或则直接是全表扫描),MariaDB已经是优化后的方案,和5.6一致。阿里数据库月报也对此进行了回复:MySQL · 答疑解惑 · MySQL Sort 分页

测试表和数据:

假设每页3条记录,第一页limit 0,3和第二页limit 3,3查询结果如下:

我们可以看到id为4的这条记录居然同时出现在两次查询中,这明显是不符合预期的,而且在5.5版本中没有这个问题。

使用优先队列排序的目的就是在不能使用索引有序性的时候,如果要排序,并且使用了limit n,那么只需要在排序的过程中,保留n条记录即可,这样虽然不能解决所有记录都需要排序的开销,但是只需要 sort buffer 少量的内存就可以完成排序,上面已经说明。之所以MySQL5.6出现了第二页数据重复的问题,是因为使用了优先队列排序,其使用了堆排序的排序方法,而堆排序是一个不稳定的排序方法,也就是相同的值(例子中的值2)可能排序出来的数据和读出来的数据顺序不一致,无法保证排序前后数据位置的一致,所以导致分页重复的现象。

避免这个问题在阿里月报有说:MySQL · 答疑解惑 · MySQL Sort 分页

但在MySQL 5.7版本中此问题又没有了。

<参考>

http://www.codes51.com/article/detail_1774816_1.html

http://mysql.taobao.org/monthly/2015/06/04

https://dev.mysql.com/doc/refman/5.7/en/group-by-optimization.html


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (5)or分享 (0)
关于作者:

您必须 登录 才能发表评论!