如何使用group by进行去重
因为mysql的distinct在结果集中,全部不同,才可以去重。
所以,当我们进行去重处理的时候,需要单独对某列进行去重,可以使用group by子句进行分组去重
select _auto_id from account_login group by _auto_id; 该语句可以对_auto_id列进行去重。
在使用group by进行去重效率分析
无索引
0.23s
mysql> 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后会进行自动排序,如果该我们仅仅想去重,而不需要排序,可以使用
mysql> explain select _auto_id from account_login group by _auto_id order by null;
+----+-------------+---------------+------+---------------+------+---------+------+--------+-----------------+
| id | select_type | table | type | possible_keys