华山资源网

MySQL group by语句如何优化
数据库 2024/12/23 佚名
3 1

   在MySQL中，新建立一张表，该表有三个字段，分别是id，a，b，插入1000条每个字段都相等的记录，如下：

mysql> show create table t1\G
*************************** 1. row ***************************
    Table: t1
Create Table: CREATE TABLE `t1` (
 `id` int(11) NOT NULL,
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from t1 limit 10;
+----+------+------+
| id | a  | b  |
+----+------+------+
| 1 |  1 |  1 |
| 2 |  2 |  2 |
| 3 |  3 |  3 |
| 4 |  4 |  4 |
| 5 |  5 |  5 |
| 6 |  6 |  6 |
| 7 |  7 |  7 |
| 8 |  8 |  8 |
| 9 |  9 |  9 |
| 10 |  10 |  10 |
+----+------+------+
10 rows in set (0.00 sec)

   当我们执行下面包含group by的SQL时，查看执行计划，可以看到：

mysql> explain select id%10 as m, count(*) as c from t1 group by m limit 10;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+
| 1 | SIMPLE   | t1  | NULL    | index | PRIMARY,a   | a  | 5    | NULL | 1000 |  100.00 | Using index; Using temporary; Using filesort |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

最后面有：

  • using index：覆盖索引
  • using temporary：使用了内存临时表
  • using filesort：使用了排序操作

   为了更好的理解这个group by语句的执行过程，我画一个图来表示：

MySQL group by语句如何优化

对照上面这个表，我们不难发现，这个group by的语句执行流程是下面这样的：

a、首先创建内存临时表，内存表里有两个字段m和c，主键是m；m是id%10，而c是统计的count(*) 个数

b、扫描表t1的索引a，依次取出叶子节点上的id值，计算id%10的结果，记为x；此时如果临时表中没有主键为x的行，就插入一个记录(x,1);如果表中有主键为x的行，就将x这一行的c值加1；

c、遍历完成后，再根据字段m做排序，得到结果集返回给客户端。（注意，这个排序的动作是group by自动添加的。）

 如果我们不想让group by语句帮我们自动排序，可以添加上order by null在语句的末尾，这样就可以去掉order by之后的排序过程了。如下：

mysql> explain select id%10 as m, count(*) as c from t1 group by m order by null;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra            |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE   | t1  | NULL    | index | PRIMARY,a   | a  | 5    | NULL | 1000 |  100.00 | Using index; Using temporary |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

   可以看到，explain最后面的using filesort字样已经不见了。再来看下结果：

mysql> select id%10 as m, count(*) as c from t1 group by m;
+------+-----+
| m  | c  |
+------+-----+
|  0 | 100 |
|  1 | 100 |
|  2 | 100 |
|  3 | 100 |
|  4 | 100 |
|  5 | 100 |
|  6 | 100 |
|  7 | 100 |
|  8 | 100 |
|  9 | 100 |
+------+-----+
10 rows in set (0.00 sec)
mysql> select id%10 as m, count(*) as c from t1 group by m order by null;
+------+-----+
| m  | c  |
+------+-----+
|  1 | 100 |
|  2 | 100 |
|  3 | 100 |
|  4 | 100 |
|  5 | 100 |
|  6 | 100 |
|  7 | 100 |
|  8 | 100 |
|  9 | 100 |
|  0 | 100 |
+------+-----+
10 rows in set (0.00 sec)

   当我们不加order by null的时候，group by会自动为我们进行排序，所以m=0的记录会在第一条的位置，如果我们加上order by null，那么group by就不会自动排序，那么m=0的记录就在最后面了。

   我们当前这个语句，表t1中一共有1000条记录，对10取余，只有10个结果，在内存临时表中还可以放下，内存临时表在MySQL中，通过tmp_table_size来控制。

mysql> show variables like "%tmp_table%";
+----------------+----------+
| Variable_name | Value  |
+----------------+----------+
| max_tmp_tables | 32    |
| tmp_table_size | 39845888 |
+----------------+----------+
2 rows in set, 1 warning (0.00 sec)

   当我们的结果足够大，而内存临时表不足以保存的时候，MySQL就会使用磁盘临时表，整个访问的速度就变得很慢了。那么针对group by操作，我们如何优化？

01

group by优化之索引

   从上面的描述中不难看出，group by进行分组的时候，创建的临时表都是带一个唯一索引的。如果数据量很大，group by的执行速度就会很慢，要想优化这种情况，还得分析为什么group by 需要临时表？

   这个问题其实是因为group by的逻辑是统计不同的值出现的次数，由于每一行记录做group by之后的结果都是无序的，所以就需要一个临时表存储这些中间结果集。如果我们的所有值都是排列好的，有序的，那情况会怎样呢？

例如，我们有个表的记录id列是：

0,0,0,1,1,2,2,2,2,3,4,4，

当我们使用group by的时候，就直接从左到右，累计相同的值即可。这样就不需要临时表了。

   上面的结构我们也不陌生，当我们以在某个数据列上创建索引的时候，这个列本身就是排序的，当group by是以这个列为条件的时候，那么这个过程就不需要排序，因为索引是自然排序的。为了实现这个优化，我们给表t1新增一个列z，如下：

mysql> alter table t1 add column z int generated always as(id % 10), add index(z);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select z as m, count(*) as c from t1 group by z;
+------+-----+
| m  | c  |
+------+-----+
|  0 | 100 |
|  1 | 100 |
|  2 | 100 |
|  3 | 100 |
|  4 | 100 |
|  5 | 100 |
|  6 | 100 |
|  7 | 100 |
|  8 | 100 |
|  9 | 100 |
+------+-----+
10 rows in set (0.00 sec)

mysql> explain select z as m, count(*) as c from t1 group by z;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE   | t1  | NULL    | index | z       | z  | 5    | NULL | 1000 |  100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

   我们新增字段z，z的值是id%10之后的值，并且创建索引，再通过group by对这个z列进行分组，可以看到，结果中已经没有临时表了。

   所以，使用索引可以帮助我们去掉group by依赖的临时表

02

group by优化---直接排序

   如果我们已经知道表的数据量特别大，内存临时表肯定不足以容纳排序的时候，其实我们可以通过告知group by进行磁盘排序，而直接跳过内存临时表的排序过程。

  其实在MySQL中是有这样的方法的：在group by语句中加入SQL_BIG_RESULT这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。当我们使用这个语句的时候，MySQL将自动利用数组的方法来组织磁盘临时表中的字段，而不是我们所周知的B+树。关于这个知识点，这里给出官方文档的介绍：

SQL_BIG_RESULT or SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set has many rows or is small, respectively. For SQL_BIG_RESULT, MySQL directly uses disk-based temporary tables if they are created, and prefers sorting to using a temporary table with a key on the GROUP BY elements. For SQL_SMALL_RESULT, MySQL uses in-memory temporary tables to store the resulting table instead of using sorting. This should not normally be needed.

   整个group by的处理过程将会变成：

a、初始化sort_buffer，确定放入一个整型字段，记为m；

b、扫描表t1的索引a，依次取出里面的id值, 将 id%100的值存入sort_buffer中；

c、扫描完成后，对sort_buffer的字段m做排序（如果sort_buffer内存不够用，就会利用磁盘临时文件辅助排序）；

d、排序完成后，就得到了一个有序数组。类似0,0,0,1,1,2,2,3,3,3,4,4,4,4这样

e、根据有序数组，得到数组里面的不同值，以及每个值的出现次数。

昨天的文章中我们分析了union 语句会使用临时表，今天的内容我们分析了group by语句使用临时表的情况，那么MySQL究竟什么时候会使用临时表呢？

MySQL什么时候会使用内部临时表？

1、如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；

2、如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如union需要用到唯一索引约束， group by还需要用到另外一个字段来存累积计数。

以上就是MySQL group by语句如何优化的详细内容，更多关于MySQL group by优化的资料请关注其它相关文章！

华山资源网 Design By www.eoogi.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
华山资源网 Design By www.eoogi.com
www.eoogi.com 华山资源网
120,135影音资源
344,641技术资源
22,817软件资源
435,032站长资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
华山资源网 Design By www.eoogi.com