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就会利用索引完成排序,分组