詳解MySQL分組查詢Group By實現原理

作者：簡朝陽 2009-03-25 09:00:11

本文將為大家講述怎么用GROUP BY實現排序操作，并與ORDER BY進行比較。GROUP BY有三種實現方式，我們也將為大家一一做分析。

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

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

1.使用松散(Loose)索引掃描實現 GROUP BY

何謂松散索引掃描實現 GROUP BY 呢?實際上就是當 MySQL 完全利用索引掃描來實現 GROUP BY 的時候，并不需要掃描所有滿足條件的索引鍵即可完成操作得出結果。

下面我們通過一個示例來描述松散索引掃描實現 GROUP BY，在示例之前我們需要首先調整一下 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 的執行計劃：
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)

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

下面這張圖片描繪了掃描過程的大概實現：

要利用到松散索引掃描實現 GROUP BY，需要至少滿足以下幾個條件：

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

◆在使用GROUP BY 的同時，只能使用 MAX 和 MIN 這兩個聚合函數;

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

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

因為在沒有WHERE子句，也就是必須經過全索引掃描的時候，松散索引掃描需要讀取的鍵值數量與分組的組數量一樣多，也就是說比實際存在的鍵值數目要少很多。而在WHERE子句包含范圍判斷式或者等值表達式的時候，松散索引掃描查找滿足范圍條件的每個組的第1個關鍵字，并且再次讀取盡可能最少數量的關鍵字。

#p#

2.使用緊湊(Tight)索引掃描實現 GROUP BY

緊湊索引掃描實現 GROUP BY 和松散索引掃描的區別主要在于他需要在掃描索引的時候，讀取所有滿足條件的索引鍵，然后再根據讀取惡的數據來完成 GROUP BY 操作得到相應結果。

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)

這時候的執行計劃的 Extra 信息中已經沒有“Using index for group-by”了，但并不是說 MySQL 的 GROUP BY 操作并不是通過索引完成的，只不過是需要訪問 WHERE 條件所限定的所有索引鍵信息之后才能得出結果。這就是通過緊湊索引掃描來實現 GROUP BY 的執行計劃輸出信息。

下面這張圖片展示了大概的整個執行過程：

在 MySQL 中，MySQL Query Optimizer 首先會選擇嘗試通過松散索引掃描來實現 GROUP BY 操作，當發現某些情況無法滿足松散索引掃描實現 GROUP BY 的要求之后，才會嘗試通過緊湊索引掃描來實現。

當 GROUP BY 條件字段并不連續或者不是索引前綴部分的時候，MySQL Query Optimizer 無法使用松散索引掃描，設置無法直接通過索引完成 GROUP BY 操作，因為缺失的索引鍵信息無法得到。但是，如果 Query 語句中存在一個常量值來引用缺失的索引鍵，則可以使用緊湊索引掃描完成 GROUP BY 操作，因為常量填充了搜索關鍵字中的“差距”，可以形成完整的索引前綴。這些索引前綴可以用于索引查找。而如果需要排序GROUP BY結果，并且能夠形成索引前綴的搜索關鍵字，MySQL還可以避免額外的排序操作，因為使用有順序的索引的前綴進行搜索已經按順序檢索到了所有關鍵字。

3.使用臨時表實現 GROUP BY

MySQL 在進行 GROUP BY 操作的時候要想利用所有，必須滿足 GROUP BY 的字段必須同時存放于同一個索引中，且該索引是一個有序索引(如 Hash 索引就不能滿足要求)。而且，并不只是如此，是否能夠利用索引來實現 GROUP BY 還與使用的聚合函數也有關系。

前面兩種 GROUP BY 的實現方式都是在有可以利用的索引的時候使用的，當 MySQL Query Optimizer 無法找到合適的索引可以利用的時候，就不得不先讀取需要的數據，然后通過臨時表來完成 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

這次的執行計劃非常明顯的告訴我們 MySQL 通過索引找到了我們需要的數據，然后創建了臨時表，又進行了排序操作，才得到我們需要的 GROUP BY 結果。整個執行過程大概如下圖所展示：

當 MySQL Query Optimizer 發現僅僅通過索引掃描并不能直接得到 GROUP BY 的結果之后，他就不得不選擇通過使用臨時表然后再排序的方式來實現 GROUP BY了。

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

【編輯推薦】

解決MySQL 5數據庫連接超時問題
MySQL數據庫接口的VC實現與應用
MySQL數據庫之UPDATE更新語句精解

責任編輯：彭凡來源： IT168

Group By 排序 MySQL