由于GROUP BY 實(shí)際上也同樣會(huì)進(jìn)行排序操作，而且與ORDER BY 相比，GROUP BY 主要只是多了排序之后的分組操作。當(dāng)然，如果在分組的時(shí)候還使用了其他的一些聚合函數(shù)，那么還需要一些聚合函數(shù)的計(jì)算。所以，在GROUP BY 的實(shí)現(xiàn)過(guò)程中，與 ORDER BY 一樣也可以利用到索引。

在MySQL 中，GROUP BY 的實(shí)現(xiàn)同樣有多種(三種)方式，其中有兩種方式會(huì)利用現(xiàn)有的索引信息來(lái)完成 GROUP BY，另外一種為完全無(wú)法使用索引的場(chǎng)景下使用。下面我們分別針對(duì)這三種實(shí)現(xiàn)方式做一個(gè)分析。

1.使用松散(Loose)索引掃描實(shí)現(xiàn) GROUP BY

何謂松散索引掃描實(shí)現(xiàn) GROUP BY 呢?實(shí)際上就是當(dāng) MySQL 完全利用索引掃描來(lái)實(shí)現(xiàn) GROUP BY 的時(shí)候，并不需要掃描所有滿(mǎn)足條件的索引鍵即可完成操作得出結(jié)果。

下面我們通過(guò)一個(gè)示例來(lái)描述松散索引掃描實(shí)現(xiàn) GROUP BY，在示例之前我們需要首先調(diào)整一下 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 的執(zhí)行計(jì)劃：
1 sky@localhost : example 09:26:15> EXPLAIN
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_id\G
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)

我們看到在執(zhí)行計(jì)劃的 Extra 信息中有信息顯示“Using index for group-by”，實(shí)際上這就是告訴我們，MySQL Query Optimizer 通過(guò)使用松散索引掃描來(lái)實(shí)現(xiàn)了我們所需要的 GROUP BY 操作。

下面這張圖片描繪了掃描過(guò)程的大概實(shí)現(xiàn)：

 要利用到松散索引掃描實(shí)現(xiàn) GROUP BY，需要至少滿(mǎn)足以下幾個(gè)條件：

◆GROUP BY 條件字段必須在同一個(gè)索引中最前面的連續(xù)位置;

◆在使用GROUP BY 的同時(shí)，只能使用 MAX 和 MIN 這兩個(gè)聚合函數(shù);

◆如果引用到了該索引中 GROUP BY 條件之外的字段條件的時(shí)候，必須以常量形式存在;

為什么松散索引掃描的效率會(huì)很高?

因?yàn)樵跊](méi)有WHERE子句，也就是必須經(jīng)過(guò)全索引掃描的時(shí)候， 松散索引掃描需要讀取的鍵值數(shù)量與分組的組數(shù)量一樣多，也就是說(shuō)比實(shí)際存在的鍵值數(shù)目要少很多。而在WHERE子句包含范圍判斷式或者等值表達(dá)式的時(shí)候， 松散索引掃描查找滿(mǎn)足范圍條件的每個(gè)組的第1個(gè)關(guān)鍵字，并且再次讀取盡可能最少數(shù)量的關(guān)鍵字。

#p#

2.使用緊湊(Tight)索引掃描實(shí)現(xiàn) GROUP BY

緊湊索引掃描實(shí)現(xiàn) GROUP BY 和松散索引掃描的區(qū)別主要在于他需要在掃描索引的時(shí)候，讀取所有滿(mǎn)足條件的索引鍵，然后再根據(jù)讀取惡的數(shù)據(jù)來(lái)完成 GROUP BY 操作得到相應(yīng)結(jié)果。

1 　sky@localhost : example 08:55:14> EXPLAIN
2 
3 -> SELECT max(gmt_create)
4 
5 -> FROM group_message
6 
7 -> WHERE group_id = 2
8 
9 -> GROUP BY user_id\G
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)

下面這張圖片展示了大概的整個(gè)執(zhí)行過(guò)程：

 在 MySQL 中，MySQL Query Optimizer 首先會(huì)選擇嘗試通過(guò)松散索引掃描來(lái)實(shí)現(xiàn) GROUP BY 操作，當(dāng)發(fā)現(xiàn)某些情況無(wú)法滿(mǎn)足松散索引掃描實(shí)現(xiàn) GROUP BY 的要求之后，才會(huì)嘗試通過(guò)緊湊索引掃描來(lái)實(shí)現(xiàn)。

當(dāng) GROUP BY 條件字段并不連續(xù)或者不是索引前綴部分的時(shí)候，MySQL Query Optimizer 無(wú)法使用松散索引掃描，設(shè)置無(wú)法直接通過(guò)索引完成 GROUP BY 操作，因?yàn)槿笔У乃饕I信息無(wú)法得到。但是，如果 Query 語(yǔ)句中存在一個(gè)常量值來(lái)引用缺失的索引鍵，則可以使用緊湊索引掃描完成 GROUP BY 操作，因?yàn)槌Ａ刻畛淞怂阉麝P(guān)鍵字中的“差距”，可以形成完整的索引前綴。這些索引前綴可以用于索引查找。而如果需要排序GROUP BY結(jié)果，并且能夠形成索引前綴的搜索關(guān)鍵字，MySQL還可以避免額外的排序操作，因?yàn)槭褂糜许樞虻乃饕那熬Y進(jìn)行搜索已經(jīng)按順序檢索到了所有關(guān)鍵字。

3.使用臨時(shí)表實(shí)現(xiàn) GROUP BY

MySQL 在進(jìn)行 GROUP BY 操作的時(shí)候要想利用所有，必須滿(mǎn)足 GROUP BY 的字段必須同時(shí)存放于同一個(gè)索引中，且該索引是一個(gè)有序索引(如 Hash 索引就不能滿(mǎn)足要求)。而且，并不只是如此，是否能夠利用索引來(lái)實(shí)現(xiàn) GROUP BY 還與使用的聚合函數(shù)也有關(guān)系。

前面兩種 GROUP BY 的實(shí)現(xiàn)方式都是在有可以利用的索引的時(shí)候使用的，當(dāng) MySQL Query Optimizer 無(wú)法找到合適的索引可以利用的時(shí)候，就不得不先讀取需要的數(shù)據(jù)，然后通過(guò)臨時(shí)表來(lái)完成 GROUP BY 操作。

1 sky@localhost : example 09:02:40> EXPLAIN
2 
3 -> SELECT max(gmt_create)
4 
5 -> FROM group_message
6 
7 -> WHERE group_id > 1 and group_id < 10
8 
9 -> GROUP BY user_id\G
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_group_message_gid_uid,idx_gid_uid_gc
22 
23 key: idx_gid_uid_gc
24 
25 key_len: 4
26 
27 ref: NULL
28 
29 rows: 32
30 
31 Extra: Using where; Using index; Using temporary; Using filesort

這次的執(zhí)行計(jì)劃非常明顯的告訴我們 MySQL 通過(guò)索引找到了我們需要的數(shù)據(jù)，然后創(chuàng)建了臨時(shí)表，又進(jìn)行了排序操作，才得到我們需要的 GROUP BY 結(jié)果。整個(gè)執(zhí)行過(guò)程大概如下圖所展示：

 當(dāng) MySQL Query Optimizer 發(fā)現(xiàn)僅僅通過(guò)索引掃描并不能直接得到 GROUP BY 的結(jié)果之后，他就不得不選擇通過(guò)使用臨時(shí)表然后再排序的方式來(lái)實(shí)現(xiàn) GROUP BY了。

在這樣示例中即是這樣的情況。 group_id 并不是一個(gè)常量條件，而是一個(gè)范圍，而且 GROUP BY 字段為 user_id。所以 MySQL 無(wú)法根據(jù)索引的順序來(lái)幫助 GROUP BY 的實(shí)現(xiàn)，只能先通過(guò)索引范圍掃描得到需要的數(shù)據(jù)，然后將數(shù)據(jù)存入臨時(shí)表，然后再進(jìn)行排序和分組操作來(lái)完成 GROUP BY。

【編輯推薦】

1. 解決MySQL 5數(shù)據(jù)庫(kù)連接超時(shí)問(wèn)題
2. MySQL數(shù)據(jù)庫(kù)接口的VC實(shí)現(xiàn)與應(yīng)用
3. MySQL數(shù)據(jù)庫(kù)之UPDATE更新語(yǔ)句精解