mysql中过滤重复记录之distinct用法

本文章主要是讲述了关于利用mysql中distinct来过滤一些重启的记录,有需要的朋友可参考一下.

下面我们就通过几个简单的 Query 示例来展示一下 DISTINCT 的实现.

1.首先看看通过松散索引扫描完成 DISTINCT 的操作,代码如下:

1. sky@localhost:example11:03:41>EXPLAINSELECTDISTINCTgroup_id
2. ->FROMgroup_messageG
3. ***************************1.row***************************
4. id:1--phpfensi.com
5. SELECT_type:SIMPLE
6. table:group_message
7. type:range
8. possible_keys:NULL
9. key:idx_gid_uid_gc
10. key_len:4
11. ref:NULL
12. rows:10
13. Extra:Usingindexforgroup-by
14. 1rowinset(0.00sec)

我们可以很清晰的看到,执行计划中的 Extra 信息为“Using index for group-by”,这代表什么意思？为什么我没有进行 GROUP BY 操作的时候，执行计划中会告诉我这里通过索引进行了 GROUP BY 呢？其实这就是于 DISTINCT 的实现原理相关的,在实现 DISTINCT的过程中，同样也是需要分组的，然后再从每组数据中取出一条返回给客户端。而这里的 Extra 信息就告诉我们，MySQL 利用松散索引扫描就完成了整个操作。当然，如果 MySQL Query Optimizer 要是能够做的再人性化一点将这里的信息换成“Using index for distinct”那就更好更容易让人理解了，呵呵。

2.我们再来看看通过紧凑索引扫描的示例,代码如下:

1. sky@localhost:example11:03:53>EXPLAINSELECTDISTINCTuser_id
2. ->FROMgroup_message
3. ->WHEREgroup_id=2G
4. ***************************1.row***************************
5. id:1
6. SELECT_type:SIMPLE
7. table:group_message
8. type:ref
9. possible_keys:idx_gid_uid_gc
10. key:idx_gid_uid_gc
11. key_len:4
12. ref:const
13. rows:4
14. Extra:UsingWHERE;Usingindex
15. 1rowinset(0.00sec)

这里的显示和通过紧凑索引扫描实现 GROUP BY 也完全一样,实际上,这个 Query 的实现过程中,MySQL 会让存储引擎扫描 group_id = 2 的所有索引键,得出所有的 user_id,然后利用索引的已排序特性,每更换一个 user_id 的索引键值的时候保留一条信息,即可在扫描完所有 gruop_id = 2 的索引键的时候完成整个 DISTINCT 操作.

3.下面我们在看看无法单独使用索引即可完成 DISTINCT 的时候会是怎样,代码如下:

1. sky@localhost:example11:04:40>EXPLAINSELECTDISTINCTuser_id
2. ->FROMgroup_message
3. ->WHEREgroup_id>1ANDgroup_id<10G
4. ***************************1.row***************************
5. id:1
6. SELECT_type:SIMPLE
7. table:group_message
8. type:range
9. possible_keys:idx_gid_uid_gc
10. key:idx_gid_uid_gc
11. key_len:4
12. ref:NULL
13. rows:32
14. Extra:UsingWHERE;Usingindex;Usingtemporary
15. 1rowinset(0.00sec)

当 MySQL 无法仅仅依赖索引即可完成 DISTINCT 操作的时候，就不得不使用临时表来进行相应的操作了。但是我们可以看到，在 MySQL 利用临时表来完成 DISTINCT 的时候，和处理 GROUP BY 有一点区别，就是少了 filesort。实际上，在 MySQL 的分组算法中，并不一定非要排序才能完成分组操作的，这一点在上面的 GROUP BY 优化小技巧中我已经提到过了。实际上这里 MySQL 正是在没有排序的情况下实现分组最后完成 DISTINCT 操作的，所以少了 filesort 这个排序操作。

4.最后再和 GROUP BY 结合试试看,代码如下:

1. sky@localhost:example11:05:06>EXPLAINSELECTDISTINCTmax(user_id)
2. ->FROMgroup_message
3. ->WHEREgroup_id>1ANDgroup_id<10
4. ->GROUPBYgroup_idG
5. ***************************1.row***************************
6. id:1
7. SELECT_type:SIMPLE
8. table:group_message
9. type:range
10. possible_keys:idx_gid_uid_gc
11. key:idx_gid_uid_gc
12. key_len:4
13. ref:NULL
14. rows:32
15. Extra:UsingWHERE;Usingindex;Usingtemporary;Usingfilesort
16. 1rowinset(0.00sec)

最后我们再看一下这个和 GROUP BY 一起使用带有聚合函数的示例，和上面第三个示例相比，可以看到已经多了 filesort 排序操作了，正是因为我们使用了 MAX 函数的缘故。要取得分组后的 MAX 值，又无法使用索引完成操作，只能通过排序才行了。

在使用mysql时，有时需要查询出某个字段不重复的记录，虽然mysql提供有distinct这个关键字来过滤掉多余的重复记录只保留一条，但往往只用它来返回不重复记录的条数，而不是用它来返回不重记录的所有值。其原因是distinct只能返回它的目标字段，而无法返回其它字段，这个问题让我困扰了很久

下面先来看看例子,代码如下:

1. table
2. idname
3. 1a
4. 2b
5. 3c
6. 4c
7. 5b

库结构大概这样,这只是一个简单的例子,实际情况会复杂得多,比如我想用一条语句查询得到name不重复的所有数据,那就必须使用distinct去掉多余的重复记录,代码如下:

select distinct name from table

得到的结果是:

1. name
2. a
3. b
4. c

好像达到效果了,可是,我想要得到的是id值呢？改一下查询语句吧,代码如下:

select distinct name, id from table

结果会是如下代码:

1. idname
2. 1a
3. 2b
4. 3c
5. 4c
6. 5b

distinct怎么没起作用？作用是起了的,不过他同时作用了两个字段,也就是必须得id与name都相同的才会被排除,我们再改改查询语句,代码如下:

select id, distinct name from table

很遗憾,除了错误信息你什么也得不到,distinct必须放在开头,难到不能把distinct放到where条件里？能,照样报错.

试了半天，也不行，最后在mysql手册里找到一个用法，用group_concat(distinct name)配合group by name实现了我所需要的功能，兴奋，天佑我也，赶快试试

报错,郁闷,连mysql手册也跟我过不去,先给了我希望,然后又把我推向失望,好狠那.

再仔细一查，group_concat函数是4.1支持，晕，我4.0的。没办法，升级，升完级一试，成功。。。。。。

终于搞定了，不过这样一来，又必须要求客户也升级了,突然灵机一闪，既然可以使用group_concat函数，那其它函数能行吗？赶紧用count函数一试，成功，我。。。。。。。想哭啊，费了这么多工夫。。。。。。。。原来就这么简单。。。。。。

现在将完整语句放出,代码如下:

select id,name, count(distinct name) from table group by name

结果,代码如下:

1. idnamecount(distinctname)
2. 1a1
3. 2b1
4. 3c1

最后一项是多余的,不用管就行了,目的达到,哦,对,再顺便说一句，group by 必须放在 order by 和 limit之前，不然会报错,差不多了,我继续忙碌。。。。。。

原文:这篇文章是我从别人那里转来的，在自己的项目中也遇到了这样的问题，我的sql语句是向下面这样写的:

1. SELECTattention_join.memberID,nickName,headpic,attention_join.time
2. FROMattention_join
3. JOINmemberONattention_join.memberID=member.memberID
4. JOINmember_metaONmember.memberID=member_meta.memberID
5. GROUPBYattention_join.memberID
6. ORDERBYattention_join.timeDESC

意思是 '按 加入/关注 小组的时间降序，查出小组内的会员' ，但是语句里并没有用到向上文说的count()关键字，这个也让我很不解，mysql没有详细的学习过，它的 group by 关键字的用法好像和 sqlserver 的有很大不同，这个等有时间了，在查查看吧，现在没有时间了

哦,对了,我的mysql版本是:服务器版本: 5.1.54-1 ubuntu4,协议版本: 10