mysql优化groupby-松散索引扫描与紧凑索引扫描

《mysql优化groupby-松散索引扫描与紧凑索引扫描》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、MySQL优化GROUPBY－松散索引扫描与紧凑索引扫描       满足GROUPBY子句的最一般的方法是扫描整个表并创建一个新的临时表，表中每个组的所有行应为连续的，然后使用该临时表来找到组并应用累积函数(如果有)。在某些情况中，MySQL能够做得更好，即通过索引访问而不用创建临时表。       为GROUPBY使用索引的最重要的前提条件是所有GROUPBY列引用同一索引的属性，并且索引按顺序保存其关键字。是否用索引访问来代替临时表的使用还取决于在查询中使用了哪部分索引、为该部分指定的条件，以及选择的累积函数。       由于GROUPBY实际上也同样

2、会进行排序操作，而且与ORDERBY相比，GROUPBY主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUPBY的实现过程中，与ORDERBY一样也可以利用到索引。在MySQL中，GROUPBY的实现同样有多种（三种）方式，其中有两种方式会利用现有的索引信息来完成GROUPBY，另外一种为完全无法使用索引的场景下使用。下面我们分别针对这三种实现方式做一个分析。1、使用松散索引扫描（Looseindexscan）实现GROUPBY对“松散索引扫描”的定义，本人看了很多网上的介绍，都不甚

3、明白。在此逻列如下：定义1：松散索引扫描，实际上就是当MySQL完全利用索引扫描来实现GROUPBY的时候，并不需要扫描所有满足条件的索引键即可完成操作得出结果。定义2：优化GroupBy最有效的办法是当可以直接使用索引来完全获取需要group的字段。使用这个访问方法时，MySQL使用对关键字排序的索引的类型（比如BTREE索引）。这使得索引中用于group的字段不必完全涵盖WHERE条件中索引对应的key。由于只包含索引中关键字的一部分，因此称为松散的索引扫描。意思是索引中用于group的字段，没必要包含多列索引的全部字段。例如：有一个索引idx(c1,c2

4、,c3)，那么groupbyc1、groupbyc1,c2这样c1或c1、c2都只是索引idx的一部分。要注意的是，索引中用于group的字段必须符合索引的“最左前缀”原则。groupbyc1,c3是不会使用松散的索引扫描的例如：explainSELECTgroup_id,gmt_createFROMgroup_messageWHEREuser_id>1GROUPBYgroup_id,gmt_create;本人理解“定义2”的例子说明有一个索引idx(c1,c2,c3)SELECTc1,c2FROMt1WHEREc1

5、用于group的字段为c1,c2不必完全涵盖WHERE条件中索引对应的key（where条件中索引，即为c1；c1对应的key，即为idx）索引中用于group的字段(c1,c2)只包含索引中关键字(c1,c2,c3)的一部分，因此称为松散的索引扫描。要利用到松散索引扫描实现GROUPBY，需要至少满足以下几个条件：◆查询针对一个单表◆GROUPBY条件字段必须在同一个索引中最前面的连续位置；GROUPBY包括索引的第1个连续部分(如果对于GROUPBY，查询有一个DISTINCT子句，则所有DISTINCT的属性指向索引开头)。◆在使用GROUPBY的同时，

6、如果有聚合函数，只能使用MAX和MIN这两个聚合函数，并且它们均指向相同的列。◆如果引用(where条件中)到了该索引中GROUPBY条件之外的字段条件的时候，必须以常量形式存在，但MIN()或MAX()函数的参数例外；  或者说：索引的任何其它部分（除了那些来自查询中引用的GROUPBY）必须为常数(也就是说，必须按常量数量来引用它们)，但MIN()或MAX()函数的参数例外。补充：如果sql中有where语句，且select中引用了该索引中GROUPBY条件之外的字段条件的时候，where中这些字段要以常量形式存在。◆如果查询中有where条件，则条件必须

7、为索引，不能包含非索引的字段松散索引扫描explainSELECTgroup_id,user_idFROMgroup_messageWHEREgroup_idbetween1and4GROUPBYgroup_id,user_id;松散索引扫描explainSELECTgroup_id,user_idFROMgroup_messageWHEREuser_id>1andgroup_id=1GROUPBYgroup_id,user_id;非松散索引扫描explainSELECTgroup_id,user_idFROMgroup_messageWHEREabc=1GR

8、OUPBYgroup_id,user_