MySQL优化GROUP BY（松散索引扫描与紧凑索引扫描）
满足GROUPBY子句的最一般的方法是扫描整个表并创建一个新的临时表，表中每个组的所有行应为连续的，然后使用该临时表来找到组并应用累积函数(如果有)。在某些情况中，MySQL能够做得更好，即通过索引访问而不用创建临时表。
为GROUPBY使用索引的最重要的前提条件是所有GROUPBY列引用同一索引的属性，并且索引按顺序保存其关键字。是否用索引访问来代替临时表的使用还取决于在查询中使用了哪部分索引、为该部分指定的条件，以及选择的累积函数。
由于GROUPBY实际上也同样会进行排序操作，而且与ORDERBY相比，GROUPBY主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUPBY的实现过程中，与ORDERBY一样也可以利用到索引。在MySQL中，GROUPBY的实现同样有多种（三种）方式，其中有两种方式会利用现有的索引信息来完成GROUPBY，另外一种为完全无法使用索引的场景下使用。下面我们分别针对这三种实现方式做一个分析。
1、使用松散索引扫描（Looseindexscan）实现GROUPBY
对“松散索引扫描”的定义，本人看了很多网上的介绍，都不甚明白。在此逻列如下：
定义1：松散索引扫描，实际上就是当MySQL完全利用索引扫描来实现GROUPBY的时候，并不需要扫描所有满足条件的索引键即可完成操作得出结果。
定义2：优化GroupBy最有效的办法是当可以直接使用索引来完全获取需要group的字段。使用这个访问方法时，MySQL使用对关键字排序的索引的类型（比如BTREE索引）。这使得索引中用于group的字段不必完全涵盖WHERE条件中索引对应的key。由于只包含索引中关键字的一部分，因此称为松散的索引扫描。
意思是索引中用于group的字段，没必要包含多列索引的全部字段。例如：有一个索引idx(c1,c2,c3)，那么groupbyc1、groupbyc1,c2这样c1或c1、c2都只是索引idx的一部分。要注意的是，索引中用于group的字段必须符合索引的“最左前缀”原则。groupbyc1,c3是不会使用松散的索引扫描的
例如：
explain
SELECTgroup_id,gmt_create
FROMgroup_message
WHEREuser_id>1
GROUPBYgroup_id,gmt_create;
本人理解“定义2”的例子说明
有一个索引idx(c1,c2,c3)
SELECTc1,c2FROMt1WHEREc1<constGROUPBYc1,c2;
索引中用于group的字段为c1,c2
不必完全涵盖WHERE条件中索引对应的key（where条件中索引，即为c1；c1对应的key，即为idx）
索引中用于group的字段(c1,c2)只包含索引中关键字(c1,c2,c3)的一部分，因此称为松散的索引扫描。
要利用到松散索引扫描实现GROUPBY，需要至少满足以下几个条件：
◆查询针对一个单表
◆GROUPBY条件字段必须在同一个索引中最前面的连续位置；
GROUPBY包括索引的第1个连续部分(如果对于GROUPBY，查询有一个DISTINCT子句，则所有DISTINCT的属性指向索引开头)。
◆在使用GROUPBY的同时，如果有聚合函数，只能使用MAX和MIN这两个聚合函数，并且它们均指向相同的列。
◆如果引用(where条件中)到了该索引中GROUPBY条件之外的字段条件的时候，必须以常量形式存在，但MIN()或MAX()函数的参数例外；
或者说：索引的任何其它部分（除了那些来自查询中引用的GROUPBY）必须为常数(也就是说，必须按常量数量来引用它们)，但MIN()或MAX()函数的参数例外。
补充：如果sql中有where语句，且select中引用了该索引中GROUPBY条件之外的字段条件的时候，where中这些字段要以常量形式存在。
◆如果查询中有where条件，则条件必须为索引，不能包含非索引的字段
松散索引扫描
explain
SELECTgroup_id,user_id
FROMgroup_message
WHEREgroup_idbetween1and4
GROUPBYgroup_id,user_id;
松散索引扫描
explain
SELECTgroup_id,user_id
FROMgroup_message
WHEREuser_id>1andgroup_id=1
GROUPBYgroup_id,user_id;
非松散索引扫描
explain
SELECTgroup_id,user_id
FROMgroup_message
WHEREabc=1
GROUPBYgroup_id,user_id;
非松散索引扫描
explain
SELECTgroup_id,user_id
FROMgroup_message
WHEREuser_id>1andabc=1
GROUPBYgroup_id,user_id;
松散索引扫描，此类查询的EXPLAIN输出显示Extra列的Usingindexforgroup-by
下面的查询提供该类的几个例子，假定表t1(c1,c2,c3,c4)有一个索引idx(c1，c2，c3)：
SELECTc1,c2FROMt1GROUPBYc1,c2;
SELECTDISTINCTc1,c2FROMt1;
SELECTc1,MIN(c2)FROMt1GROUPBYc1;
SELECTc1,c2FROMt1WHEREc1<constGROUPBYc1,c2;
SELECTMAX(c3),MIN(c3),c1,c2FROMt1WHEREc2>constGROUPBYc1,c2;
SELECTc2FROMt1WHEREc1<constGROUPBYc1,c2;
SELECTc1,c2FROMt1WHEREc3=constGROUPBYc1,c2；
由于上述原因，不能用该快速选择方法执行下面的查询：
1、除了MIN()或MAX()，还有其它累积函数，例如：
SELECTc1,SUM(c2)FROMt1GROUPBYc1;
2、GROUPBY子句中的域不引用索引开头，如下所示：
SELECTc1,c2FROMt1GROUPBYc2,c3;
3、查询引用了GROUPBY部分后面的关键字的一部分，并且没有等于常量的等式，例如：
SELECTc1,c3FROMt1GROUPBYc1,c2；
这个例子中，引用到了c3（c3必须为组合索引中的一个），因为groupby中没有c3。并且没有等于常量的等式。所以不能使用松散索引扫描
可以这样改一下：SELECTc1,c3FROMt1wherec3='a'GROUPBYc1,c2
下面这个例子不能使用松散索引扫描
SELECTc1,c3FROMt1wherec3='a'GROUPBYc1,c2
为什么松散索引扫描的效率会很高？
答：因为在没有WHERE子句，也就是必须经过全索引扫描的时候，松散索引扫描需要读取的键值数量与分组的组数量一样多，也就是说比实际存在的键值数目要少很多。而在WHERE子句包含范围判断式或者等值表达式的时候，松散索引扫描查找满足范围条件的每个组的第1个关键字，并且再次读取尽可能最少数量的关键字。
2、使用紧凑索引扫描（Tightindexscan）实现GROUPBY
紧凑索引扫描实现GROUPBY和松散索引扫描的区别主要在于：
紧凑索引扫描需要在扫描索引的时候，读取所有满足条件的索引键，然后再根据读取出的数据来完成GROUPBY操作得到相应结果。
这时候的执行计划的Extra信息中已经没有“Usingindexforgroup-by”了，但并不是说MySQL的GROUPBY操作并不是通过索引完成的，只不过是需要访问WHERE条件所限定的所有索引键信息之后才能得出结果。这就是通过紧凑索引扫描来实现GROUPBY的执行计划输出信息。
在MySQL中，MySQLQueryOptimizer首先会选择尝试通过松散索引扫描来实现GROUPBY操作，当发现某些情况无法满足松散索引扫描实现GROUPBY的要求之后，才会尝试通过紧凑索引扫描来实现。
当GROUPBY条件字段并不连续或者不是索引前缀部分的时候，MySQLQueryOptimizer无法使用松散索引扫描。
这时检查where中的条件字段是否有索引的前缀部分，如果有此前缀部分，且该部分是一个常量，且与groupby后的字段组合起来成为一个连续的索引。这时按紧凑索引扫描。
SELECTmax(gmt_create)
FROMgroup_message
WHEREgroup_id=2
GROUPBYuser_id
需读取group_id=2的所有数据，然后在读取的数据中完成groupby操作得到结果。（这里groupby字段并不是一个连续索引，正好where中group_id正好弥补缺失的索引键，又恰好是一个常量，因此使用紧凑索引扫描）
group_iduser_id这个顺序是可以使用该索引。如果连接的顺序不符合索引的“最左前缀”原则，则不使用紧凑索引扫描。
以下例子使用紧凑索引扫描
GROUPBY中有一个差距，但已经由条件user_id=1覆盖。
explain
SELECTgroup_id,gmt_create
FROMgroup_message
WHEREuser_id=1GROUPBYgroup_id,gmt_create
GROUPBY不以关键字的第1个元素开始，但是有一个条件提供该元素的常量
explain
SELECTgroup_id,gmt_create
FROMgroup_message
WHEREgroup_id=1GROUPBYuser_id,gmt_create
下面的例子都不使用紧凑索引扫描
user_id,gmt_create连接起来并不符合索引“最左前缀”原则
explain
SELECTgroup_id,gmt_create
FROMgroup_message
WHEREuser_id=1GROUPBYgmt_create
group_id,gmt_create连接起来并不符合索引“最左前缀”原则
explain
SELECTgmt_create
FROMgroup_message
WHEREgroup_id=1GROUPBYgmt_create;
3、使用临时表实现GROUPBY
MySQLQueryOptimizer发现仅仅通过索引扫描并不能直接得到GROUPBY的结果之后，他就不得不选择通过使用临时表然后再排序的方式来实现GROUPBY了。在这样示例中即是这样的情况。group_id并不是一个常量条件，而是一个范围，而且GROUPBY字段为user_id。所以MySQL无法根据索引的顺序来帮助GROUPBY的实现，只能先通过索引范围扫描得到需要的数据，然后将数据存入临时表，然后再进行排序和分组操作来完成GROUPBY。
explain
SELECTgroup_id
FROMgroup_message
WHEREgroup_idbetween1and4
GROUPBYuser_id;
示例数据库文件
---------------------------------------------------------- --Host:127.0.0.1 --Serverversion:5.1.57-community-MySQLCommunityServer(GPL) --ServerOS:Win32 --HeidiSQLversion:7.0.0.4156 --Date/time:2012-08-2016:52:10 ---------------------------------------------------------- /*!40101SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT*/; /*!40101SETNAMESutf8*/; /*!40014SETFOREIGN_KEY_CHECKS=0*/; --Dumpingstructurefortabletest.group_message DROPTABLEIFEXISTS`group_message`; CREATETABLEIFNOTEXISTS`group_message`( `id`int(10)unsignedNOTNULLAUTO_INCREMENT, `group_id`int(10)unsignedDEFAULTNULL, `user_id`int(10)unsignedDEFAULTNULL, `gmt_create`timestampNOTNULLDEFAULTCURRENT_TIMESTAMP, `abc`int(11)NOTNULLDEFAULT'0', PRIMARYKEY(`id`), KEY`group_id_user_id_gmt_create`(`group_id`,`user_id`,`gmt_create`) )ENGINE=MyISAMAUTO_INCREMENT=27DEFAULTCHARSET=utf8; --Dumpingdatafortabletest.group_message:0rows DELETEFROM`group_message`; /*!40000ALTERTABLE`group_message`DISABLEKEYS*/; INSERTINTO`group_message`(`id`,`group_id`,`user_id`,`gmt_create`,`abc`)VALUES (1,1,1,'2012-08-2009:25:35',1), (2,2,1,'2012-08-2009:25:39',1), (3,2,2,'2012-08-2009:25:47',1), (4,3,1,'2012-08-2009:25:50',2), (5,3,2,'2012-08-2009:25:52',2), (6,3,3,'2012-08-2009:25:54',0), (7,4,1,'2012-08-2009:25:57',0), (8,4,2,'2012-08-2009:26:00',0), (9,4,3,'2012-08-2009:26:02',0), (10,4,4,'2012-08-2009:26:06',0), (11,5,1,'2012-08-2009:26:09',0), (12,5,2,'2012-08-2009:26:12',0), (13,5,3,'2012-08-2009:26:13',0), (14,5,4,'2012-08-2009:26:15',0), (15,5,5,'2012-08-2009:26:17',0), (16,6,1,'2012-08-2009:26:20',0), (17,7,1,'2012-08-2009:26:23',0), (18,7,2,'2012-08-2009:26:28',0), (19,8,1,'2012-08-2009:26:32',0), (20,8,2,'2012-08-2009:26:35',0), (21,9,1,'2012-08-2009:26:37',0), (22,9,2,'2012-08-2009:26:40',0), (23,10,1,'2012-08-2009:26:42',0), (24,10,2,'2012-08-2009:26:44',0), (25,10,3,'2012-08-2009:26:51',0), (26,11,1,'2012-08-2009:26:54',0); /*!40000ALTERTABLE`group_message`ENABLEKEYS*/; /*!40014SETFOREIGN_KEY_CHECKS=1*/; /*!40101SETCHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT*/;
参考文献
1、MySQL如何优化GROUPBY
2、详解MySQL分组查询GroupBy实现原理
3、松散的索引扫描(Looseindexscan)
4、MySQL学习笔记