MYSQL优化-之GROUP BY

来源:转载

在web应用中,提倡sql简单,避免复杂度。所以在我们公司的应用中看不到jon,子查询等语句的存在,所以间接GROUP BY与 索引的使用占据大多数,其实很多技巧,别人都是总结过的,仔细分析,仔细学习别人的经验才是正道.而不可浮躁,凭经验主义.

满足GROUP BY子句的最一般的方法是扫描整个表并创建一个新的临时表,表中每个组的所有行应为连续的,然后使用该临时表来找到组并应用累积函数(如 果有)。(用通俗的语言就是,建立一个临时表。然后利用mysql内部算法。算出来结果)


在某些情况中,MySQL能够做得更好,通过索引访问而不用创建临时表。 (其实就是因为临时表的东西,我们才应该优化。)


一般GROUP BY优化分为2种优化方式:


1。松散索引扫描


2,。紧凑索引扫描


何为松散索引扫描:


其实就是:


通过该访问方法,MySQL使用某些关键字排序的索引类型的 属性。该属性允许使用 索引中的查找组而不需要考虑满足所有WHERE条件的索引中的所有关键字。既然该访问方法只考虑索引中的关键字的一小 部分,它被称为松散索引列表。(官方语言就是精辟)


例如:


explainselect TeamID from competeinfo where TeamID >10 group by TeamID



id


select_type


table


type


possible_keys


key


key_len


ref


rows


Extra




1


SIMPLE


competeinfo


range


TeamID


TeamID


4


NULL


26


Using where; Using index for group-by




这里的explain代表查看索引应用情况。我简单介绍一下.


id->SELECT识别符。这是SELECT的查询序列号。


select_type -> SELECT类型,SIMPLE代表比较简单的查询。(还包括union,primary等,具体去查下手册)


table代表所引用的表。


type->联接类型 。可以说索引应用状况。这里range只检索给定范围的行,使用一个索引来选择行 。大多数我们将对索引用到> <等之类的符号。(还包括其他的联接类型,all代表扫描整个表。至于其他关键字。还查手册吧)

possible_keys->MySQL能使用哪个索引在该表中找到行(这里不是真正应用到)


key ->MYSQL实际应用到的索引。


key_len->索引长度


ref使用哪个列或常数与key一起从表中选择行。


rows-》影响多少行


Extra-》一些索引应用状况信息。这里的Using index for group-by表示松散索引。


如果这里出现useing tempoary useingfilesort。这个是比较严重的。这证明你的索引,没有用用到。解决方式:改索引吧。

言归正传。这里select TeamID from competeinfo where TeamID >10 group by TeamID。就是通过TeamID来查找组。完成group by .这个也是一种方式。


但是大家注意。查询字段必须和后面GROUP BY一致.

第二种优化方式,也是最常用的。紧凑索引扫描。


何为紧凑索引扫描:索引扫描或一个范围索引扫描,取决于查询条件。


其实啊就是联合索引的应用。


explain select TeamID from competeinfo where TeamID >10 and CompeteID > 100020 group by CompeteID

这个表建立了。CompeteID,TeamID的联合索引。



id


select_type


table


type


possible_keys


key


key_len


ref


rows


Extra




1


SIMPLE


competeinfo


range


CompeteID,TeamID


CompeteID


4


NULL


22


Using where; Using index




大家已经看到了。这里我们会发现。可能应用索引有2个。实际应用到索引名为CompeteID.返回应用信息也是Using where; Using index。应用到索引。


下面我说下:哪些情况应用不到。


1.对不同的索引键做GROUP BY


SELECT * FROM a1 group by key1, key2;


2.在非连续的索引键部分上做group by


SELECT * FROM t1 WHERE key2=constant group by key_part2;


非连续索引:上面的索引CompeteID。他是由CompeteID,TeamID建立联合索引。

explain select TeamID from competeinfo where TeamID >10 and CompeteID > 100020 group by TeamID


这样的话。


id


select_type


table


type


possible_keys


key


key_len


ref


rows


Extra




1


SIMPLE


competeinfo


range


CompeteID,TeamID


CompeteID


4


NULL


22


Using where; Using index; Using temporary; Using filesort



大家看见了。EXTRA的信息。这样的话group by没用应用索引。影响效率。


所以一定注意到。GROUP BY顺序.问题.


用于搜索记录的索引键和做GROUP BY的不是同一个:

其实意思是。Where条件和GROUP BY字段得是一个索引里面的。

这个就不举例了。

我相信通过这篇文章大家也了解了GROUP BY的索引应用了吧!


其实我们多尝试,多分析下就可以了。

如有有疑问,请大家多多指出,我将会修正。

分享给朋友:
您可能感兴趣的文章:
随机阅读: