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

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

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

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

　　下面我們通過一個(gè)示例來描述松散索引掃描實(shí)現(xiàn) GROUP BY，在示例之前我們需要首先調(diào)整一下 group_message 表的索引，將 gmt_create 字段添加到 group_id 和 user_id 字段的索引中：

sky@localhost: example 08:49:45> create index idx_gid_uid_gc
 -> on group_message(group_id,user_id,gmt_create);
 Query OK, rows affected (0.03 sec)
 Records: 96 Duplicates: 0 Warnings: 0
sky@localhost: example 09:07:30> drop index idx_group_message_gid_uid
 -> on group_message;
 Query OK, 96 rows affected (0.02 sec)
 Records: 96 Duplicates: 0 Warnings: 0

然后再看如下 Query 的執(zhí)行計(jì)劃：

sky@localhost: example 09:26:15> EXPLAIN
 -> SELECT user_id,max(gmt_create)
 -> FROM group_message
 -> WHERE group_id < 10
 -> GROUP BY group_id,user_id\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: group_message
 type: range
 possible_keys: idx_gid_uid_gc
 key: idx_gid_uid_gc
 key_len: 8
 ref: NULL
 rows: 4
 Extra: Using where; Using index for group-by

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

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

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

◆GROUP BY 條件字段必須在同一個(gè)索引中最前面的連續(xù)位置;
◆在使用GROUP BY 的同時(shí)，只能使用 MAX 和 MIN 這兩個(gè)聚合函數(shù);
◆如果引用到了該索引中 GROUP BY 條件之外的字段條件的時(shí)候，必須以常量形式存在;

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

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

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

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

 　sky@localhost : example 08:55:14> EXPLAIN
 -> SELECT max(gmt_create)
 -> FROM group_message
 -> WHERE group_id = 2
 -> GROUP BY user_id\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: group_message
 type: ref
 possible_keys: idx_group_message_gid_uid,idx_gid_uid_gc
 key: idx_gid_uid_gc
 key_len: 4
 ref: const
 rows: 4
 Extra: Using where; Using index
 1 row in set (0.01 sec)

這時(shí)候的執(zhí)行計(jì)劃的 Extra 信息中已經(jīng)沒有“Using index for group-by”了，但并不是說 MySQL 的 GROUP BY 操作并不是通過索引完成的，只不過是需要訪問 WHERE 條件所限定的所有索引鍵信息之后才能得出結(jié)果。這就是通過緊湊索引掃描來實(shí)現(xiàn) GROUP BY 的執(zhí)行計(jì)劃輸出信息。
下面這張圖片展示了大概的整個(gè)執(zhí)行過程：

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

當(dāng) GROUP BY 條件字段并不連續(xù)或者不是索引前綴部分的時(shí)候，MySQL Query Optimizer 無法使用松散索引掃描，設(shè)置無法直接通過索引完成 GROUP BY 操作，因?yàn)槿笔У乃饕I信息無法得到。但是，如果 Query 語句中存在一個(gè)常量值來引用缺失的索引鍵，則可以使用緊湊索引掃描完成 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í)候要想利用所有，必須滿足 GROUP BY 的字段必須同時(shí)存放于同一個(gè)索引中，且該索引是一個(gè)有序索引(如 Hash 索引就不能滿足要求)。而且，并不只是如此，是否能夠利用索引來實(shí)現(xiàn) GROUP BY 還與使用的聚合函數(shù)也有關(guān)系。

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

 sky@localhost : example 09:02:40> EXPLAIN
 -> SELECT max(gmt_create)
 -> FROM group_message
 -> WHERE group_id > 1 and group_id < 10
 -> GROUP BY user_id\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: group_message
 type: range
 possible_keys: idx_group_message_gid_uid,idx_gid_uid_gc
 key: idx_gid_uid_gc
 key_len: 4
 ref: NULL
 rows: 32
 Extra: Using where; Using index; Using temporary; Using filesort

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

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

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

最新評(píng)論