group by调优的一些测试

最新推荐文章于 2025-08-09 20:21:24 发布

weixin_30715523

最新推荐文章于 2025-08-09 20:21:24 发布

阅读量68

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/yuyue2014/p/4338961.html

本文探讨了MySQL中Group By的使用方法与优化策略，详细分析了不同场景下的索引选择，包括组合索引和覆盖索引的应用。通过具体案例说明如何减少排序需求并提高查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

表结构信息：

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)

(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）

下面是具体的分析：

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

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

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

第四种情况：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)