group by调优的一些测试

表结构信息：

mysql> show create table tb\G
*************************** 1. row ***************************
Table: tb
Create Table: CREATE TABLE `tb` (
`c` int(11) DEFAULT NULL,
`d` int(4) DEFAULT NULL,
`e` varchar(32) DEFAULT NULL,
KEY `c` (`c`),
KEY `c_2` (`c`,`d`),
KEY `c_3` (`c`,`d`,`e`),
KEY `c_4` (`c`,`e`),
KEY `e` (`e`,`c`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

表中的数据：

mysql> select * from tb;
+------+------+------+
| c | d | e |
+------+------+------+
| 2 | 40 | b |
| 1 | 10 | a |
| 2 | 30 | a |
| 1 | 10 | a |
| 3 | 30 | a |
| 1 | 10 | c |
| 1 | 50 | c |
| 2 | 50 | c |
+------+------+------+
8 rows in set (0.00 sec)

 

1. group by 算法: 先分组 还是 先排序。使用order by null可以禁用排序。

2. 单表group by 算法（不考虑覆盖索引）

(1) where 条件只含有group by, 视group by的个数而定，建立单列索引，或者组合索引。

(2) where 条件中含有等值（=）和group by，例如 where a = 1 group by b, c ， 建立组合索引（a,b,c）。

(3) where 条件中含有范围（ >,<,in）和group by

第一种情况，group by 中的字段包括范围字段值，比如 where c < 4 and c > 1 group by c,d ，建立组合索引（c,d）;

第二种情况，group by 中的字段和范围字段都不一样。到底是优先选取小的结果集合，还是优先避免临时表、排序，这里有一个权衡的问题。

比如下面的例子：

mysql> explain select * from tb where e > 'a' group by c,d;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------------------+
| 1 | SIMPLE | tb | range | e | e | 99 | NULL | 7 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from tb ignore index (e) where e > 'a' group by c,d;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tb | index | NULL | c_2 | 10 | NULL | 14 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

(4) where 条件中含有等值(=)、范围（ >,<,in）和group by，只考虑建立等值和group by字段的组合索引。

(5) group by 和 order by 一起用：

　　(a) group by 的字段包含 order by 的字段，并且是前缀包含，可以按照group by 的优化去处理。（group by c,d order by c）

　　(b) group by 的字段包含 order by 的字段，但不是前缀包含，可以按照group by 的优化去处理。（group by c,e order by e）

　　(c) group by c,d order by e; e 是聚集函数字段，可以按照group by 的优化去处理。

 

下面是具体的分析：

第一种情况: group by c,d order by c ; 这种情况其实不需要order by c，因为group by本身就是按照c升序排列的。建立组合索引（c,d）即可。

第二种情况: group by c,d order by c desc; 不可避免会排序。

mysql> explain select * from tb group by c,d order by c desc;
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
| 1 | SIMPLE | tb | index | NULL | e | 109 | NULL | 14 | Using index; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
1 row in set (0.00 sec)

 

第三种情况：group by c,e order by e; 建立组合索引(c,e)，依旧避免不了排序。个人感觉这个SQL没有什么实际业务含义。

mysql> explain select c, e,count(*) from tb group by c,e order by e;
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
| 1 | SIMPLE | tb | index | NULL | c_4 | 104 | NULL | 14 | Using index; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
1 row in set (0.00 sec)

 

第四种情况：group by c,d order by e; 

理论上而言，如果e只是一个数据库中的一般字段，这个SQL是没有什么实际业务意义的。

但是这里的e可以是select字段中的聚集函数，这样便有现实意义了。例如：select c, d,count(*) CNT from tb  group by c,d order by CNT;

按照group by去优化即可。

mysql> select c, d,count(*) from tb group by c,d;
+------+------+----------+
| c | d | count(*) |
+------+------+----------+
| 1 | 10 | 3 |
| 1 | 50 | 1 |
| 2 | 30 | 1 |
| 2 | 40 | 1 |
| 2 | 50 | 1 |
| 3 | 30 | 1 |
| 4 | 5 | 1 |
| 4 | 13 | 1 |
| 5 | 68 | 1 |
| 5 | 88 | 1 |
| 6 | 23 | 1 |
| 6 | 73 | 1 |
+------+------+----------+
12 rows in set (0.00 sec)

 

3. 单表group by 算法（考虑覆盖索引）

group by一般是伴随着聚集函数的，比如count(), max(), min(), avg()等等。

如果select 字段和where中的待添加索引的字段，加起来不超过4个，可以考虑加覆盖索引。