MYSQL中GROUP BY语句

it2025-06-06  17

这里展示的MySQL版本是8.0.20,验证的结果可能不适应于其他版本。我们创建一个数据表,数据表的表结构如下:

CREATE TABLE `dailywork` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `content` varchar(300) NOT NULL DEFAULT '请及时追加当天工作内容' COMMENT '工作内容', `dailytime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `play` varchar(100) NOT NULL DEFAULT '明日计划为填写' COMMENT '明日计划', `complete` varchar(100) NOT NULL DEFAULT '100%' COMMENT '计划完成比率', PRIMARY KEY (`id`), KEY `cs` (`content`,`dailytime`) ) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8

然后向数据表中写入若干条记录,下面是数据表中的记录列表。其中,content和dailytime是索引。我们通过这些记录来了解GROUP By 的用法

+----+---------+---------------------+-----------------------+----------+ | id | content | dailytime | play | complete | +----+---------+---------------------+-----------------------+----------+ | 1 | test1 | 2020-09-01 20:21:08 | 明日计划为填写 | 100% | | 2 | test1 | 2020-09-01 20:30:12 | 明日计划为填写 | 100% | | 3 | test2 | 2020-09-01 20:30:12 | 明日计划为填写 | 100% | | 20 | test3 | 2020-09-03 01:52:38 | 明日计划为填写 | 100% | | 21 | test3 | 2020-09-03 01:54:04 | 明日计划为填写 | 100% | | 22 | test4 | 2020-09-03 06:00:28 | 明日计划为填写 | 100% | | 24 | test5 | 2020-09-03 09:30:35 | 明日计划为填写 | 100% | | 28 | test5 | 2020-09-03 10:08:34 | 明日计划为填写 | 100% | +----+---------+---------------------+-----------------------+----------+ 8 rows in set (0.01 sec)

我们通过几个SQL语句来了解MySQL的GROUP BY查询过程。通过四条语句来了解一下。第一条执行语句如下:

EXPLAIN SELECT content, dailytime FROM dailywork GROUP BY content, dailytime;

执行的返回结果中使用到了索引:

+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | dailywork | NULL | index | cs | cs | 906 | NULL | 5 | 100.00 | Using index | +----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

下面是第二条执行语句:

EXPLAIN SELECT content, dailytime FROM dailywork WHERE content = "test1" GROUP BY dailytime;

相应的语句执行的结果:

+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | dailywork | NULL | ref | cs | cs | 902 | const | 2 | 100.00 | Using index | +----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

下面执行第三条语句,以及对应的返回结果:

EXPLAIN SELECT dailytime FROM dailywork GROUP BY dailytime; +----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | dailywork | NULL | index | cs | cs | 906 | NULL | 5 | 100.00 | Using index; Using temporary | +----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+------------------------------+ 1 row in set, 1 warning (0.00 sec)

最后一条执行语句和它的返回结果:

EXPLAIN SELECT play FROM dailywork GROUP BY play; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | SIMPLE | dailywork | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using temporary | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 1 row in set, 1 warning (0.00 sec)

对比第一条、和第二条语句的执行结果集。通过 Extra 可以看出,它们都执行的是索引扫描,但第一条语句的 type 类型是 ref,第二条是 index。在MySQL查询中,语句底层是通过 JOIN 表的方式实现的,即使是单表查询,底层使用的也是 JOIN 的形式。这其实也好理解,就是单表查询和多表联合查询底层使用相同的逻辑。而 ref 表示的是匹配“前表”索引值的所有行,index 表示扫描索引树。针对第一条、第二条语句来说,两者差别并不大。

分析第四条语句的执行情况,Extra 中的结果是 Using temporary,所以,MySQL 在执行语句的过程中使用了临时表。MySQL 首先将符合条件的语句 COPY 到临时表,再执行的聚合操作。

最后,看第三条语句的执行结果,还是挺奇怪的。Extra 中的结果是 Using index; Using temporary,居然使用到了索引。

这篇文章的分析就到这里,后面继续。概括一下:

最新回复(0)