MYSQL优化-之GROUP BY
转载：https://my.oschina.net/heguangdong/blog/38567
在web应用中,提倡sql简单,避免复杂度。所以在我们公司的应用中看不到jon,子查询等语句的存在,所以间接GROUP BY 与 索引的使用占据大多数,其实很多技巧,别人都是总结过的,仔细分析,仔细学习别人的经验才是正道.而不可浮躁,凭经验主义.
满足GROUP BY子句的最一般的方法是扫描整个表并创建一个新的临时表，表中每个组的所有行应为连续的，然后使用该临时表来找到组并应用累积函数(如 果有)。(用通俗的语言就是，建立一个临时表。然后利用mysql内部算法。算出来结果)
在某些情况中，MySQL能够做得更好，通过索引访问而不用创建临时表。 （其实就是因为临时表的东西，我们才应该优化。）
一般GROUP BY 优化分为2种优化方式：
1。松散索引扫描
2,。紧凑索引扫描
何为松散索引扫描：
其实就是:
通过该访问方法，MySQL使用某些关键字排序的索引类型的 属性。该属性允许使用 索引中的查找组而不需要考虑满足所有WHERE条件的索引中的所有关键字。既然该访问方法只考虑索引中的关键字的一小 部分，它被称为松散索引列表。(官方语言就是精辟)
例如：
explain select 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 的索引应用了吧！
其实我们多尝试，多分析下就可以了。
如有有疑问，请大家多多指出，我将会修正。
