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

由於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個關鍵字，並且再次讀取儘可能最少數量的關鍵字。