数据库优化<七>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过滤出来的行很少,再分组操作时也很快的