bitsCN.com
1.使用松散(Loose)索引扫描实现 GROUP BY
何谓松散索引扫描实现 GROUP BY 呢?实际上就是当 MySQL 完全利用索引扫描来实现 GROUP BY 的时候，并不需要扫描所有满足条件的索引键即可完成操作得出结果。
下面我们通过一个示例来描述松散索引扫描实现 GROUP BY，在示例之前我们需要首先调整一下 group_message 表的索引，将 gmt_create 字段添加到 group_id 和 user_id 字段的索引中：
| 代码如下 | 复制代码 |
| 1 sky@localhost : example 08:49:45> create index idx_gid_uid_gc 2 3 -> on group_message(group_id,user_id,gmt_create); 4 5 Query OK, rows affected (0.03 sec) 6 7 Records: 96 Duplicates: 0 Warnings: 0 8 9 sky@localhost : example 09:07:30> drop index idx_group_message_gid_uid 10 11 -> on group_message; 12 13 Query OK, 96 rows affected (0.02 sec) 14 15 Records: 96 Duplicates: 0 Warnings: 0 | |
然后再看如下 Query 的执行计划：
| 代码如下 | 复制代码 | ||||
2 3 -> SELECT user_id,max(gmt_create) 4 5 -> FROM group_message 6 7 -> WHERE group_id < 10 8 9 -> GROUP BY group_id,user_idG 10 11 *************************** 1. row *************************** 12 13 id: 1 14 15 select_type: SIMPLE 16 17 table: group_message 18 19 type: range 20 21 possible_keys: idx_gid_uid_gc 22 23 key: idx_gid_uid_gc 24 25 key_len: 8 26 27 ref: NULL 28 29 rows: 4 30 31 Extra: Using where; Using index for group-by 32 33 1 row in set (0.00 sec) | |||||
我们看到在执行计划的 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 操作得到相应结果。
| 代码如下 | 复制代码 | ||||
2 3 -> SELECT max(gmt_create) 4 5 -> FROM group_message 6 7 -> WHERE group_id = 2 8 9 -> GROUP BY user_idG 10 11 *************************** 1. row *************************** 12 13 id: 1 14 15 select_type: SIMPLE 16 17 table: group_message 18 19 type: ref 20 21 possible_keys: idx_group_message_gid_uid,idx_gid_uid_gc 22 23 key: idx_gid_uid_gc 24 25 key_len: 4 26 27 ref: const 28 29 rows: 4 30 31 Extra: Using where; Using index 32 33 1 row in set (0.01 sec) | |||||
这时候的执行计划的 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 操作。
| 代码如下 | 复制代码 | ||||||||
2 3 -> SELECT max(gmt_create)4 5 -> FROM group_message6 7 -> WHERE group_id > 1 and group_id < 108 9 -> GROUP BY user_idG10 11 *************************** 1. row ***************************
| |||||||||
| 代码如下 | 复制代码 |
| SELECT *FROM `dede_archives`GROUP BY `typeid`LIMIT 0 , 30 | |
这样即可了
一些相关group by 实例
| 代码如下 | 复制代码 |
|
/* --创建表并插入数据： create table tb(name varchar(10),val int,memo varchar(20)) --一、按name分组取val最大的值所在行的数据。 --方法1：select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name */ 本人推荐使用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 */ --三、按name分组取第一次出现的行所在的数据。 select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name --四、按name分组随机取一条数据。 select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name/* */ --五、按name分组取最小的两个(N个)val select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.valselect a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val */ --六、按name分组取最大的两个(N个)val select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
/* | |
