数据库优化七 SQL优化之SELECT优化group by 优化

在数据库查询中，group by语句经常使用，而这个语句的使用是最耗性能的，按常理，

我们生活中要这样做也很麻烦，有两种情形：

1、有索引的情况

2、无索引的情况

对于第一种情况，如果在生活中要做这样的事情，很恼火，我们正常的想法是，先把所有的

按高矮顺序来排序，最后是一组的就直接挑出来了，但 怎样实现这个排序呢？

根据上一篇文件排序filesort的规则，我们可以这样，先让局部有序，然后在慢慢扩散

来排列整个数据，方法是：选择适当的大小的排序块缓存大小，每次取出块大小数据，利用

快速排序功能对该块排序，然后存入临时文件，然后利用归并排序的思想，将各个块之间进行

排序，最终达到排序完成。

对于上面的描述，虽然排序使用了非常快的排序算法快排和归并排序，但这个涉及的临时

文件的读取操作，浪费大量的I/O,性能上是危害很大的，因此，掌握好在使用group by的语句的

使用，尽量使用到索引，免除了排序的操作，group by的速度就上来了，并且也不会消耗太多

内存大小，下面叙述一下group by使用索引的原理

一、 group by 使用索引原理

1、group by 使用排序来读取数据，所以只能用btree索引，不能使用在hash索引的算法中

因为hash索引是一种类似键值对的快速访问方式，这个对于指定某个值查询很好，但

没有排序的方法，其使用的hash函数 + 碰撞冲突解决方案

2、当使用索引排序来查找数据时，不会在explain中extra列看到有using filesort

3、在group by操作完成后，还会对group出来的结果进行排序，因此如果对排序的结果

没有排序的需求，可以考虑在其后面加上order by null

二、group by 访问索引的方法

group by 访问数据有两种方法：

1、边扫描边执行group操作,叫做松散索引扫描(Loose index scan)

2、先执行一个范围(range)扫描，然后在执行group 操作,叫做

紧索引扫描(Tight index scan)

2.1、松散索引扫描(Loose index scan)

最高效的 处理group by的方法是，直接访问相应的索引，所以不用排序就能根据

索引来读取需要的数据，而对于如聚簇索引(cluster index),我们可以读取前面的一部分

的字段索引来获取数据，而不用满足所有的列，这就叫做松散索引扫描，我的定义可为：

前缀索引扫描

使用松散索引扫描的条件：

1、查询只能针对一个单表进行操作，这个可是个致命的缺点啊，但如果where

条件比较多，选出来的数据少的话，还是不用担忧的

2、group by使用索引为：对聚簇索引使用前缀索引

3、使用类似group by 的操作的函数有distinct函数，使用此函数时，要么在一个

索引上使用，要么在group by时，其group by的字句是索引扫描，否则会引

起全表扫描。

4、在使用group by语句中，如果使用聚合函数max(), min()等，如果列不在group

by的列中，或不在group by 列的聚簇索引的一部分，这将会用到排序操作

5、只能对整个列的值排序时使用到索引，而只有前面一部分索引不能用到排序，

如： 列 c1 char(20), index(c1(10))、这个只用了一半索引，将无法使用来对

整个数据排序

假设我们在表t1(c1, c2, c3, c4)有聚簇索引index(c1, c2, c3)，能使用Loose index scan例子：

1、SELECT c1, c2 FROM t1 GROUP BY c1, c2;2、SELECT DISTINCT c1, c2 FROM t1;3、SELECT c1, MIN(c2) FROM t1 GROUP BY c1;4、SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;5、SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;6、SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;7、SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

这些都使用了前缀索引

2.2、使用紧索引扫描和松索引扫描类似，只是会先根据where条件来获取所有的行，然后

根据group by的字段来分组，这种的使用方法，一般是，where条件返回的行较少时

使用，比如，你的where字句中使用了主键或唯一键=const等，这样的代价是，通过

where过滤出来的行很少，再分组操作时也很快的