group by与distinct效率分析及优化措施

最新推荐文章于 2024-07-16 14:33:28 发布

taoy86

最新推荐文章于 2024-07-16 14:33:28 发布

阅读量1.6k

点赞数

分类专栏： Mysql

原文链接：https://blog.csdn.net/u013983450/article/details/52190699?utm_medium=distribute.pc_relevant.none-task-blog-title-2&spm=1001.2101.3001.4242

版权

Mysql 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

mysql的distinct在结果集中，全部不同，才可以去重。所以，当我们进行去重处理的时候，需要单独对某列进行去重，可以使用group by子句进行分组去重

select _auto_id from account_login group by _auto_id; 该语句可以对_auto_id列进行去重。去重效率分析

无索引

0.23s

explain select _auto_id from account_login group by _auto_id;

+----+-------------+---------------+------+---------------+------+---------+------+--------+---------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------------+------+---------------+------+---------+------+--------+---------------------------------+

| 1 | SIMPLE | account_login | ALL | NULL | NULL | NULL | NULL | 133257 | Using temporary; Using filesort |

+----+-------------+---------------+------+---------------+------+---------+------+--------+---------------------------------+

mysql> show profile;

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000154 |

| checking permissions | 0.000012 |

| Opening tables | 0.000029 |

| init | 0.000029 |

| System lock | 0.000014 |

| optimizing | 0.000010 |

| statistics | 0.000021 |

| preparing | 0.000020 |

| Creating tmp table | 0.000036 |

| Sorting result | 0.000007 |

| executing | 0.000005 |

| Sending data | 0.207841 |

| Creating sort index | 0.021024 |

| end | 0.000010 |

| removing tmp table | 0.000130 |

| end | 0.000010 |

| query end | 0.000016 |

| closing tables | 0.000019 |

| freeing items | 0.000035 |

| cleaning up | 0.000039 |

+----------------------+----------+

20 rows in set, 1 warning (0.00 sec)

此处创建了sort index进行排序，说明对MySQL使用了内存临时表， group by后面的排序过程是使用sort index来完成的，而且该内存临时表的大小是由MAX_HEAP_TABLE_SIZE来控制。

Sending data 显示的这个时间 = Time(Sending data) + Time (Sorting result), 这样其实应该是排序所用的时间

因为在group by后会进行自动排序，如果该我们仅仅想去重，而不需要排序，可以使用

 explain select _auto_id from account_login group by _auto_id order by null;

+----+-------------+---------------+------+---------------+------+---------+------+--------+-----------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------------+------+---------------+------+---------+------+--------+-----------------+

| 1 | SIMPLE | account_login | ALL | NULL | NULL | NULL | NULL | 133257 | Using temporary |

+----+-------------+---------------+------+---------------+------+---------+------+--------+-----------------+

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000155 |

| checking permissions | 0.000012 |

| Opening tables | 0.000029 |

| init | 0.000029 |

| System lock | 0.000014 |

| optimizing | 0.000009 |

| statistics | 0.000022 |

| preparing | 0.000020 |

| Creating tmp table | 0.000042 |

| executing | 0.000006 |

| Sending data | 0.219640 |

| end | 0.000021 |

| removing tmp table | 0.000014 |

| end | 0.000008 |

| query end | 0.000014 |

| closing tables | 0.000020 |

| freeing items | 0.000033 |

| cleaning up | 0.000020 |

+----------------------+----------+

可以发现，在加入order by null子句后，MySQL并没有创建sort index进行排序（内存排序非常快，优化效果并不明显，并且这个阶段只是每个数据块的排序）。

有索引

explain select _auto_id from account_login group by _auto_id;

使用时间 0.11s

执行计划

+----+-------------+---------------+-------+---------------+---------+---------+------+--------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------------+-------+---------------+---------+---------+------+--------+-------------+

| 1 | SIMPLE | account_login | index | idx_acc | idx_acc | 4 | NULL | 133257 | Using index |

+----+-------------+---------------+-------+---------------+---------+---------+------+--------+-------------+

profile

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000140 |

| checking permissions | 0.000011 |

| Opening tables | 0.000027 |

| init | 0.000028 |

| System lock | 0.000014 |

| optimizing | 0.000009 |

| statistics | 0.000035 |

| preparing | 0.000028 |

| Sorting result | 0.000006 |

| executing | 0.000005 |

| Sending data | 0.105595 |

| end | 0.000012 |

| query end | 0.000013 |

| closing tables | 0.000015 |

| freeing items | 0.000026 |

| cleaning up | 0.000034 |

+----------------------+----------+

explain select _auto_id from account_login group by _auto_id 时间0.11s

explain select _auto_id from account_login group by _auto_id order by null 时间0.11s

在使用索情况下，因为使用了索引自身的有序性，所以不需MySQL再次创建临时表（create sort index）进行排序，可以直接输出有序结果，两者的计算时间相同。

正常使用场景效率分析

mysql> explain select _auto_id,max(date) from account_login group by _auto_id;

没有索引

用时 3.16s

+----+-------------+---------------+------+---------------+------+---------+------+--------+---------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------------+------+---------------+------+---------+------+--------+---------------------------------+

| 1 | SIMPLE | account_login | ALL | NULL | NULL | NULL | NULL | 133257 | Using temporary; Using filesort |

+----+-------------+---------------+------+---------------+------+---------+------+--------+---------------------------------+

mysql> show profile;

+---------------------------+----------+

| Status | Duration |

+---------------------------+----------+

| starting | 0.000111 |

| checking permissions | 0.000010 |

| Opening tables | 0.000018 |

| init | 0.000030 |

| System lock | 0.000011 |

| optimizing | 0.000007 |

| statistics | 0.000014 |

| preparing | 0.000013 |

| Creating tmp table | 0.000037 |

| Sorting result | 0.000007 |

| executing | 0.000005 |

| Sending data | 0.545211 |

| converting HEAP to MyISAM | 1.307225 |

| Sending data | 0.738511 |

| Creating sort index | 0.573640 |

| end | 0.000020 |

| removing tmp table | 0.001682 |

| end | 0.000009 |

| query end | 0.000012 |

| closing tables | 0.000016 |

| freeing items | 0.000030 |

| logging slow query | 0.000051 |

| cleaning up | 0.000018 |

+---------------------------+----------+

在group by过程中，先使用sort index对group by子句进行处理；然后创建临时表，然后转换到磁盘临时表，使用文件排序取出max(date)

如果group by后面列数过多（即使不排序），也是会用converting HEAP to MyISAM

converting HEAP to MyISAM 该语句表明了在执行过程中，内存临时表转变成了硬盘临时表。可以使用 tmp_table_size，MAX_HEAP_TABLE_SIZE来改变内存临时表的最大大小，但是在该SQL下，因为要使用文件排序，所以无论内存临时表设置多大，都会进行内存临时表到文件临时表的转变。

有索引情况

时间 0.31s

mysql> explain select _auto_id,max(date) from account_login group by _auto_id;

+----+-------------+---------------+-------+---------------+---------+---------+------+--------+-------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------------+-------+---------------+---------+---------+------+--------+-------+

| 1 | SIMPLE | account_login | index | idx_acc | idx_acc | 4 | NULL | 133257 | NULL |

+----+-------------+---------------+-------+---------------+---------+---------+------+--------+-------+

profile

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000109 |

| checking permissions | 0.000010 |

| Opening tables | 0.000022 |

| init | 0.000031 |

| System lock | 0.000012 |

| optimizing | 0.000007 |

| statistics | 0.000021 |

| preparing | 0.000022 |

| Sorting result | 0.000006 |

| executing | 0.000005 |

| Sending data | 0.314817 |

| end | 0.000024 |

| query end | 0.000015 |

| closing tables | 0.000032 |

| freeing items | 0.000042 |

| cleaning up | 0.000023 |

+----------------------+----------+

在有索引的情况下，仅仅靠索引本身就完成了全部需求。

distinct进行分析

explain select distinct(_auto_id) from account_login;

+----+-------------+---------------+------+---------------+------+---------+------+--------+-----------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------------+------+---------------+------+---------+------+--------+-----------------+

| 1 | SIMPLE | account_login | ALL | NULL | NULL | NULL | NULL | 133257 | Using temporary |

+----+-------------+---------------+------+---------------+------+---------+------+--------+-----------------+

mysql> show profile;

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000087 |

| checking permissions | 0.000009 |

| Opening tables | 0.000016 |

| init | 0.000016 |

| System lock | 0.000011 |

| optimizing | 0.000007 |

| statistics | 0.000013 |

| preparing | 0.000014 |

| Creating tmp table | 0.000026 |

| executing | 0.000006 |

| Sending data | 0.221214 |

| end | 0.000024 |

| removing tmp table | 0.000190 |

| end | 0.000011 |

| query end | 0.000014 |

| closing tables | 0.000019 |

| freeing items | 0.000036 |

| cleaning up | 0.000024 |

+----------------------+----------+

select distinct _auto_id,sid,uid from account_login;

+---------------------------+----------+

| Status | Duration |

+---------------------------+----------+

| starting | 0.000095 |

| checking permissions | 0.000010 |

| Opening tables | 0.000019 |

| init | 0.000019 |

| System lock | 0.000010 |

| optimizing | 0.000006 |

| statistics | 0.000015 |

| preparing | 0.000016 |

| Creating tmp table | 0.000030 |

| executing | 0.000006 |

| Sending data | 0.529466 |

| converting HEAP to MyISAM | 1.928813 |

| Sending data | 0.157253 |

| end | 0.000020 |

| removing tmp table | 0.002778 |

| end | 0.000009 |

| query end | 0.000012 |

| closing tables | 0.000016 |

| freeing items | 0.000031 |

| logging slow query | 0.000062 |

| cleaning up | 0.000033 |

+---------------------------+----------+

发现distinct和没有排序的group by几乎是一样的，并且在进行多列的去重的时候也使用了 converting HEAP to MyISAM进行汇总

总结：

create sort index　使用内存临时表进行分块排序，分块排序后再进入磁盘进行汇总排序

converting HEAP to MyISAM 是进入硬盘进行汇总排序，如果group by数据列过多，即使不排序，也需要使用磁盘临时表进行汇总数据。

group by的主要消耗是在临时表排序阶段，而不是分组阶段。

所以制约group by性能的问题，就是临时表+排序，尽量减少磁盘排序，较少磁盘临时表的创建，是比较有用的处理办法。

最好的办法就是在group by条件后，添加索引或者复合索引，这样MySQL就会利用索引完成排序，分组

taoy86

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
group by与distinct效率分析及优化措施

mysql的distinct在结果集中，全部不同，才可以去重。所以，当我们进行去重处理的时候，需要单独对某列进行去重，可以使用group by子句进行分组去重select _auto_id from account_login group by _auto_id; 该语句可以对_auto_id列进行去重。在使用group by进行去重效率分析无索引0.23smysql> explain select _auto_id from account_login group by _aut
复制链接

扫一扫

专栏目录