mysql数据库分组,GROUP BY,查询实例

1.使用松散(Loose)索引扫描实现 GROUP BY

何谓松散索引扫描实现 GROUP BY 呢?实际上就是当 MySQL 完全利用索引扫描来实现 GROUP BY 的时候，并不需要扫描所有满足条件的索引键即可完成操作得出结果。

下面我们通过一个示例来描述松散索引扫描实现 GROUP BY，在示例之前我们需要首先调整一下 group_message 表的索引，将 gmt_create 字段添加到 group_id 和 user_id 字段的索引中,代码如下:

1. sky@localhost:example08:49:45>createindexidx_gid_uid_gc
3. ->ongroup_message(group_id,user_id,gmt_create);
5. QueryOK,rowsaffected(0.03sec)
7. Records:96Duplicates:0Warnings:0
9. sky@localhost:example09:07:30>dropindexidx_group_message_gid_uid
11. ->ongroup_message;
13. QueryOK,96rowsaffected(0.02sec)
15. Records:96Duplicates:0Warnings:0

然后再看如下 Query 的执行计划,代码如下:

1. sky@localhost:example09:26:15>EXPLAIN
3. ->SELECTuser_id,max(gmt_create)
5. ->FROMgroup_message
7. ->WHEREgroup_id<10
9. ->GROUPBYgroup_id,user_idG
11. ***************************1.row***************************
13. id:1
15. select_type:SIMPLE
17. table:group_message
19. type:range
21. possible_keys:idx_gid_uid_gc
23. key:idx_gid_uid_gc
25. key_len:8
27. ref:NULL
29. rows:4
31. Extra:Usingwhere;Usingindexforgroup-by
33. 1rowinset(0.00sec)

我们看到在执行计划的 Extra 信息中有信息显示“Using index for group-by”，实际上这就是告诉我们，MySQL Query Optimizer 通过使用松散索引扫描来实现了我们所需要的 GROUP BY 操作。

下面这张图片描绘了扫描过程的大概实现,要利用到松散索引扫描实现 GROUP BY,需要至少满足以下几个条件:

◆GROUP BY 条件字段必须在同一个索引中最前面的连续位置;

◆在使用GROUP BY 的同时，只能使用 MAX 和 MIN 这两个聚合函数;

◆如果引用到了该索引中 GROUP BY 条件之外的字段条件的时候，必须以常量形式存在;

为什么松散索引扫描的效率会很高?

因为在没有WHERE子句，也就是必须经过全索引扫描的时候， 松散索引扫描需要读取的键值数量与分组的组数量一样多，也就是说比实际存在的键值数目要少很多。而在WHERE子句包含范围判断式或者等值表达式的时候,松散索引扫描查找满足范围条件的每个组的第1个关键字，并且再次读取尽可能最少数量的关键字。

2.使用紧凑(Tight)索引扫描实现 GROUP BY

紧凑索引扫描实现 GROUP BY 和松散索引扫描的区别主要在于他需要在扫描索引的时候，读取所有满足条件的索引键，然后再根据读取恶的数据来完成 GROUP BY 操作得到相应结果,代码如下:

1. 　sky@localhost:example08:55:14>EXPLAIN
3. ->SELECTmax(gmt_create)
5. ->FROMgroup_message
7. ->WHEREgroup_id=2
9. ->GROUPBYuser_idG
11. ***************************1.row***************************
13. id:1
15. select_type:SIMPLE
17. table:group_message
19. type:ref
21. possible_keys:idx_group_message_gid_uid,idx_gid_uid_gc
23. key:idx_gid_uid_gc
25. key_len:4
27. ref:const
29. rows:4
31. Extra:Usingwhere;Usingindex
33. 1rowinset(0.01sec)

这时候的执行计划的 Extra 信息中已经没有“Using index for group-by”了,但并不是说 MySQL 的 GROUP BY 操作并不是通过索引完成的,只不过是需要访问 WHERE 条件所限定的所有索引键信息之后才能得出结果,这就是通过紧凑索引扫描来实现 GROUP BY 的执行计划输出信息.

在 MySQL 中,MySQL Query Optimizer 首先会选择尝试通过松散索引扫描来实现 GROUP BY 操作,当发现某些情况无法满足松散索引扫描实现 GROUP BY 的要求之后,才会尝试通过紧凑索引扫描来实现.

当 GROUP BY 条件字段并不连续或者不是索引前缀部分的时候,MySQL Query Optimizer 无法使用松散索引扫描,设置无法直接通过索引完成 GROUP BY 操作,因为缺失的索引键信息无法得到,但是,如果 Query 语句中存在一个常量值来引用缺失的索引键,则可以使用紧凑索引扫描完成 GROUP BY 操作,因为常量填充了搜索关键字中的“差距”,可以形成完整的索引前缀,这些索引前缀可以用于索引查找,而如果需要排序GROUP BY结果,并且能够形成索引前缀的搜索关键字,MySQL还可以避免额外的排序操作,因为使用有顺序的索引的前缀进行搜索已经按顺序检索到了所有关键字.

3.使用临时表实现 GROUP BY

MySQL 在进行 GROUP BY 操作的时候要想利用所有,必须满足 GROUP BY 的字段必须同时存放于同一个索引中,且该索引是一个有序索引(如 Hash 索引就不能满足要求),而且,并不只是如此,是否能够利用索引来实现 GROUP BY 还与使用的聚合函数也有关系.

前面两种 GROUP BY 的实现方式都是在有可以利用的索引的时候使用的,当 MySQL Query Optimizer 无法找到合适的索引可以利用的时候,就不得不先读取需要的数据，然后通过临时表来完成 GROUP BY 操作,代码如下:

1. sky@localhost:example09:02:40>EXPLAIN
3. ->SELECTmax(gmt_create)
5. ->FROMgroup_message
7. ->WHEREgroup_id>1andgroup_id<10
8. ->GROUPBYuser_idG
10. ***************************1.row***************************
12. id:1
14. select_type:SIMPLE
16. table:group_message
18. type:range
20. possible_keys:idx_group_message_gid_uid,idx_gid_uid_gc
22. key:idx_gid_uid_gc
24. key_len:4
26. ref:NULL
28. rows:32
30. Extra:Usingwhere;Usingindex;Usingtemporary;Usingfilesort

这次的执行计划非常明显的告诉我们 MySQL 通过索引找到了我们需要的数据,然后创建了临时表,又进行了排序操作,才得到我们需要的 GROUP BY 结果.

当 MySQL Query Optimizer 发现仅仅通过索引扫描并不能直接得到 GROUP BY 的结果之后，他就不得不选择通过使用临时表然后再排序的方式来实现 GROUP BY了.

在这样示例中即是这样的情况,group_id 并不是一个常量条件,而是一个范围,而且 GROUP BY 字段为 user_id,所以 MySQL 无法根据索引的顺序来帮助 GROUP BY 的实现,只能先通过索引范围扫描得到需要的数据,然后将数据存入临时表,然后再进行排序和分组操作来完成 GROUP BY.

讲了这么多其实最简单的就是,查询dedecms,织梦,程序的栏目标题表,以栏目id分组,代码如下:

1. SELECT*
2. FROM`dede_archives`
3. GROUPBY`typeid`
4. LIMIT0,30

这样即可了,一些相关group by 实例,代码如下,--按某一字段分组取最大(小)值所在行的数据,数据如下:

1. namevalmemo
2. a2a2(a的第二个值)
3. a1a1--a的第一个值
4. a3a3:a的第三个值
5. b1b1--b的第一个值
6. b3b3:b的第三个值
7. b2b2b2b2b2
8. b4b4b4
9. b5b5b5b5b5b5
10. */
11. --创建表并插入数据：
12. createtabletb(namevarchar(10),valint,memovarchar(20))
13. insertintotbvalues('a',2,'a2(a的第二个值)')
14. insertintotbvalues('a',1,'a1--a的第一个值')
15. insertintotbvalues('a',3,'a3:a的第三个值')
16. insertintotbvalues('b',1,'b1--b的第一个值')
17. insertintotbvalues('b',3,'b3:b的第三个值')
18. insertintotbvalues('b',2,'b2b2b2b2')
19. insertintotbvalues('b',4,'b4b4')
20. insertintotbvalues('b',5,'b5b5b5b5b5')
21. go
22. --一、按name分组取val最大的值所在行的数据。
23. --方法1：selecta.*fromtbawhereval=(selectmax(val)fromtbwherename=a.name)orderbya.name
24. --方法2：
25. selecta.*fromtbawherenotexists(select1fromtbwherename=a.nameandval>a.val)
26. --方法3：
27. selecta.*fromtba,(selectname,max(val)valfromtbgroupbyname)bwherea.name=b.nameanda.val=b.valorderbya.name
28. --方法4：
29. selecta.*fromtbainnerjoin(selectname,max(val)valfromtbgroupbyname)bona.name=b.nameanda.val=b.valorderbya.name
30. --方法5
31. selecta.*fromtbawhere1>(selectcount(*)fromtbwherename=a.nameandval>a.val)orderbya.name
32. /*
33. namevalmemo
34. -----------------------------------------
35. a3a3:a的第三个值
36. b5b5b5b5b5b5
37. */

本人推荐使用1,3,4,结果显示1,3,4效率相同,2，5效率差些,不过我3,4效率相同毫无疑问,1就不一样了,想不搞了.

二、按name分组取val最小的值所在行的数据。

方法1:select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name

方法2:select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)

方法3:select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name

方法4:select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name

方法5:select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name

1. namevalmemo
2. -----------------------------------------
3. a1a1--a的第一个值
4. b1b1--b的第一个值

三、按name分组取第一次出现的行所在的数据.

1. selecta.*fromtbawhereval=(selecttop1valfromtbwherename=a.name)orderbya.name
2. /*
3. namevalmemo
4. -----------------------------------------
5. a2a2(a的第二个值)
6. b1b1--b的第一个值
7. */

四、按name分组随机取一条数据.

1. selecta.*fromtbawhereval=(selecttop1valfromtbwherename=a.nameorderbynewid())orderbya.name/*
2. namevalmemo
3. -----------------------------------------
4. a1a1--a的第一个值
5. b5b5b5b5b5b5
6. */

五、按name分组取最小的两个(N个)val

1. selecta.*fromtbawhere2>(selectcount(*)fromtbwherename=a.nameandval<a.val)orderbya.name,a.valselecta.*fromtbawherevalin(selecttop2valfromtbwherename=a.nameorderbyval)orderbya.name,a.val
2. selecta.*fromtbawhereexists(selectcount(*)fromtbwherename=a.nameandval<a.valhavingCount(*)<2)orderbya.name
3. /*
4. namevalmemo
5. -----------------------------------------
6. a1a1--a的第一个值
7. a2a2(a的第二个值)
8. b1b1--b的第一个值
9. b2b2b2b2b2
10. */

六、按name分组取最大的两个(N个)val

1. selecta.*fromtbawhere2>(selectcount(*)fromtbwherename=a.nameandval>a.val)orderbya.name,a.val
2. selecta.*fromtbawherevalin(selecttop2valfromtbwherename=a.nameorderbyvaldesc)orderbya.name,a.val
3. selecta.*fromtbawhereexists(selectcount(*)fromtbwherename=a.nameandval>a.valhavingCount(*)<2)orderbya.name
4. /*phpfensi.com
5. namevalmemo
6. -----------------------------------------
7. a2a2(a的第二个值)
8. a3a3:a的第三个值
9. b4b4b4
10. b5b5b5b5b5b5
11. */

七,假如整行数据有重复,所有的列都相同,例如下表中的第5,6两行数据完全相同,按name分组取最大的两个(N个)val,数据如下:

1. namevalmemo
2. a2a2(a的第二个值)
3. a1a1--a的第一个值
4. a1a1--a的第一个值
5. a3a3:a的第三个值
6. a3a3:a的第三个值
7. b1b1--b的第一个值
8. b3b3:b的第三个值
9. b2b2b2b2b2
10. b4b4b4
11. b5b5b5b5b5b5