mysql group by 索引优化_MySQL，GROUP BY的实现与优化

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

在MySQL中，GROUP BY的实现同样有多种(三种)方式，其中有两种方式会利用现有的索引信息来完成GROUP BY，另外一种则在完全无法使用索引的场景下使用。下面分别针对这三种实现方式做一个分析。

1. 使用松散(Loose)索引扫描实现GROUP BY

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

要利用到松散索引扫描实现GROUP BY，需要至少满足以下几个条件：

•GROUP BY条件字段必须处在同一个索引中最前面的连续位置;

•在使用GROUP BY的同时，只能使用MAX和MIN这两个聚合函数;

•如果引用到了该索引中GROUP BY条件之外的字段条件，它就必须以常量形式存在;

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

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

2. 使用紧凑(Tight)索引扫描实现GROUP BY

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

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

当GROUP BY条件字段并不连续或不是索引前缀部分时，MySQL Query Optimizer无法使用松散索引扫描，设置无法直接通过索引完成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的方式，可以针对性地得出如下两种优化思路：

(1)尽可能让MySQL利用索引来完成 GROUP BY操作，当然最好是松散索引扫描的方式。在系统允许的情况下，可以通过调整索引或调整Query这两种方式来达到目的;

(2)当无法使用索引完成GROUP BY时，由于要使用到临时表且需要filesort，所以必须要有足够的sort_buffer_size供MySQL排序时使用，而且尽量不要进行大结果集的GROUP BY操作，因为如果超出系统设置的临时表大小就会出现将临时表数据复制(copy)到磁盘上面再进行操作的情况，这时的排序分组操作性能将成数量级的下降。

至于如何利用好这两种思路，还须要大家在实际应用场景中不断地尝试并测试效果，才能最终得到较佳方案。此外，在优化GROUP BY时还有一个小技巧可以让我们在有些无法利用到索引的情况下避免filesort操作，即在整个语句最后添加一个以null排序(ORDER BY null)的子句，大家可以尝试一下看会有什么效果。