MySQL最佳化GROUP BY－鬆散索引掃描與緊湊索引掃描

標籤：

   滿足GROUP BY子句的最一般的方法是掃描整個表並建立一個新的暫存資料表，表中每個組的所有行應為連續的，然後使用該暫存資料表來找到組並應用累積函數(如果有)。在某些情況中，MySQL能夠做得更好，即通過索引訪問而不用建立暫存資料表。

       為GROUP BY使用索引的最重要的前提條件是所有GROUP BY列引用同一索引的屬性，並且索引按順序儲存其關鍵字。是否用索引訪問來代替暫存資料表的使用還取決於在查詢中使用了哪部分索引、為該部分指定的條件，以及選擇的累積函數。

       由於GROUP BY 實際上也同樣會進行排序操作，而且與ORDER BY 相比，GROUP BY 主要只是多了排序之後的分組操作。當然，如果在分組的時候還使用了其他的一些彙總函式，那麼還需要一些彙總函式的計算。所以，在GROUP BY 的實現過程中，與 ORDER BY 一樣也可以利用到索引。在MySQL 中，GROUP BY 的實現同樣有多種（三種）方式，其中有兩種方式會利用現有的索引資訊來完成 GROUP BY，另外一種為完全無法使用索引的情境下使用。下面我們分別針對這三種實現方式做一個分析。

1、使用鬆散索引掃描（Loose index scan）實現 GROUP BY

對“鬆散索引掃描”的定義，本人看了很多網上的介紹，都不甚明白。在此邏列如下：

定義1：鬆散索引掃描，實際上就是當 MySQL 完全利用索引掃描來實現 GROUP BY 的時候，並不需要掃描所有滿足條件的索引鍵即可完成操作得出結果。

定義2：最佳化Group By最有效辦法是當可以直接使用索引來完全擷取需要group的欄位。使用這個存取方法時，MySQL使用對關鍵字排序的索引的類型（比如BTREE索引）。這使得索引中用於group的欄位不必完全涵蓋WHERE條件中索引對應的key。由於只包含索引中關鍵字的一部分，因此稱為鬆散的索引掃描。
意思是索引中用於group的欄位，沒必要包含多列索引的全部欄位。例如：有一個索引idx(c1,c2,c3)，那麼group by c1、group by c1,c2這樣c1或c1、c2都只是索引idx的一部分。要注意的是，索引中用於group的欄位必須符合索引的“最左首碼”原則。group by c1,c3是不會使用鬆散的索引掃描的

例如：

explain
SELECT group_id,gmt_create
FROM group_message
WHERE user_id>1
GROUP BY group_id,gmt_create;

本人理解“定義2”的例子說明

有一個索引idx(c1,c2,c3)

SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;

索引中用於group的欄位為c1,c2
不必完全涵蓋WHERE條件中索引對應的key（where條件中索引，即為c1；c1對應的key，即為idx）
索引中用於group的欄位(c1,c2)只包含索引中關鍵字(c1,c2,c3)的一部分，因此稱為鬆散的索引掃描。

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

◆ 查詢針對一個單表

◆ GROUP BY 條件欄位必須在同一個索引中最前面的連續位置；
GROUP BY包括索引的第1個連續部分(如果對於GROUP BY，查詢有一個DISTINCT子句，則所有DISTINCT的屬性指向索引開頭)。

◆ 在使用GROUP BY 的同時，如果有彙總函式，只能使用 MAX 和 MIN 這兩個彙總函式，並且它們均指向相同的列。

◆ 如果引用(where條件中)到了該索引中GROUP BY 條件之外的欄位條件的時候，必須以常量形式存在，但MIN()或MAX() 函數的參數例外；
   或者說：索引的任何其它部分（除了那些來自查詢中引用的GROUP BY）必須為常數(也就是說，必須按常量數量來引用它們)，但MIN()或MAX() 函數的參數例外。

補充：如果sql中有where語句，且select中引用了該索引中GROUP BY 條件之外的欄位條件的時候，where中這些欄位要以常量形式存在。

◆ 如果查詢中有where條件，則條件必須為索引，不能包含非索引的欄位

鬆散索引掃描
explain
SELECT group_id,user_id
FROM group_message
WHERE group_id between 1 and 4
GROUP BY group_id,user_id;

鬆散索引掃描
explain
SELECT group_id,user_id
FROM group_message
WHERE user_id>1 and group_id=1
GROUP BY group_id,user_id;

非鬆散索引掃描
explain
SELECT group_id,user_id
FROM group_message
WHERE abc=1
GROUP BY group_id,user_id;

非鬆散索引掃描
explain
SELECT group_id,user_id
FROM group_message
WHERE user_id>1 and abc=1
GROUP BY group_id,user_id;

鬆散索引掃描，此類查詢的EXPLAIN輸出顯示Extra列的Using index for group-by

下面的查詢提供該類的幾個例子，假定表t1(c1,c2,c3,c4)有一個索引idx(c1，c2，c3)：

SELECT c1, c2 FROM t1 GROUP BY c1, c2;

SELECT DISTINCT c1, c2 FROM t1;

SELECT c1, MIN(c2) FROM t1 GROUP BY c1;

SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;

SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;

SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;

SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2；

由於上述原因，不能用該快速選擇方法執行下面的查詢：

1、除了MIN()或MAX()，還有其它累積函數，例如：

     SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

2、GROUP BY子句中的域不引用索引開頭，如下所示：

     SELECT c1,c2 FROM t1 GROUP BY c2, c3;

3、查詢引用了GROUP BY部分後面的關鍵字的一部分，並且沒有等於常量的等式，例如：

     SELECT c1,c3 FROM t1 GROUP BY c1, c2；

這個例子中，引用到了c3（c3必須為複合式索引中的一個），因為group by 中沒有c3。並且沒有等於常量的等式。所以不能使用鬆散索引掃描

可以這樣改一下：SELECT c1,c3 FROM t1 where c3=‘a‘ GROUP BY c1, c2

下面這個例子不能使用鬆散索引掃描

SELECT c1,c3 FROM t1 where c3=‘a‘ GROUP BY c1, c2

為什麼鬆散索引掃描的效率會很高？

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

2、使用緊湊索引掃描（Tight index scan）實現 GROUP BY

緊湊索引掃描實現 GROUP BY 和鬆散索引掃描的區別主要在於：

緊湊索引掃描需要在掃描索引的時候，讀取所有滿足條件的索引鍵，然後再根據讀取出的資料來完成 GROUP BY 操作得到相應結果。

這時候的執行計畫的 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 無法使用鬆散索引掃描。

這時檢查where 中的條件欄位是否有索引的首碼部分，如果有此首碼部分，且該部分是一個常量，且與group by 後的欄位組合起來成為一個連續的索引。這時按緊湊索引掃描。

SELECT max(gmt_create)
FROM group_message
WHERE group_id = 2
GROUP BY user_id

需讀取group_id=2的所有資料，然後在讀取的資料中完成group by操作得到結果。（這裡group by 欄位並不是一個連續索引，正好where 中group_id正好彌補缺失的索引鍵，又恰好是一個常量，因此使用緊湊索引掃描）

group_id user_id 這個順序是可以使用該索引。如果串連的順序不符合索引的“最左首碼”原則，則不使用緊湊索引掃描。

以下例子使用緊湊索引掃描

GROUP BY中有一個差距，但已經由條件user_id = 1覆蓋。

explain
SELECT group_id,gmt_create
FROM group_message
WHERE user_id = 1 GROUP BY group_id,gmt_create

GROUP BY不以關鍵字的第1個元素開始，但是有一個條件提供該元素的常量
explain
SELECT group_id,gmt_create
FROM group_message
WHERE group_id = 1 GROUP BY user_id,gmt_create

下面的例子都不使用緊湊索引掃描

user_id,gmt_create 串連起來並不符合索引“最左首碼”原則
explain
SELECT group_id,gmt_create
FROM group_message
WHERE user_id = 1 GROUP BY gmt_create

group_id,gmt_create 串連起來並不符合索引“最左首碼”原則

explain
SELECT gmt_create
FROM group_message
WHERE group_id=1 GROUP BY gmt_create;

 3、使用暫存資料表實現 GROUP BY

MySQL Query Optimizer 發現僅僅通過索引掃描並不能直接得到 GROUP BY 的結果之後，他就不得不選擇通過使用暫存資料表然後再排序的方式來實現 GROUP BY了。在這樣樣本中即是這樣的情況。 group_id 並不是一個常量條件，而是一個範圍，而且 GROUP BY 欄位為 user_id。所以 MySQL 無法根據索引的順序來協助 GROUP BY 的實現，只能先通過索引範圍掃描得到需要的資料，然後將資料存入暫存資料表，然後再進行排序和分組操作來完成 GROUP BY。

explain
SELECT group_id
FROM group_message
WHERE group_id between 1 and 4
GROUP BY user_id;

樣本資料庫檔案 [sql] view plaincopy 

1. -- --------------------------------------------------------  
2. -- Host:                         127.0.0.1  
3. -- Server version:               5.1.57-community - MySQL Community Server (GPL)  
4. -- Server OS:                    Win32  
5. -- HeidiSQL version:             7.0.0.4156  
6. -- Date/time:                    2012-08-20 16:52:10  
7. -- --------------------------------------------------------  
8.   
9. /*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;  
10. /*!40101 SET NAMES utf8 */;  
11. /*!40014 SET FOREIGN_KEY_CHECKS=0 */;  
12.   
13. -- Dumping structure for table test.group_message  
14. DROP TABLE IF EXISTS `group_message`;  
15. CREATE TABLE IF NOT EXISTS `group_message` (  
16.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  
17.   `group_id` int(10) unsigned DEFAULT NULL,  
18.   `user_id` int(10) unsigned DEFAULT NULL,  
19.   `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  
20.   `abc` int(11) NOT NULL DEFAULT ‘0‘,  
21.   PRIMARY KEY (`id`),  
22.   KEY `group_id_user_id_gmt_create` (`group_id`,`user_id`,`gmt_create`)  
23. ) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;  
24.   
25. -- Dumping data for table test.group_message: 0 rows  
26. DELETE FROM `group_message`;  
27. /*!40000 ALTER TABLE `group_message` DISABLE KEYS */;  
28. INSERT INTO `group_message` (`id`, `group_id`, `user_id`, `gmt_create`, `abc`) VALUES  
29.     (1, 1, 1, ‘2012-08-20 09:25:35‘, 1),  
30.     (2, 2, 1, ‘2012-08-20 09:25:39‘, 1),  
31.     (3, 2, 2, ‘2012-08-20 09:25:47‘, 1),  
32.     (4, 3, 1, ‘2012-08-20 09:25:50‘, 2),  
33.     (5, 3, 2, ‘2012-08-20 09:25:52‘, 2),  
34.     (6, 3, 3, ‘2012-08-20 09:25:54‘, 0),  
35.     (7, 4, 1, ‘2012-08-20 09:25:57‘, 0),  
36.     (8, 4, 2, ‘2012-08-20 09:26:00‘, 0),  
37.     (9, 4, 3, ‘2012-08-20 09:26:02‘, 0),  
38.     (10, 4, 4, ‘2012-08-20 09:26:06‘, 0),  
39.     (11, 5, 1, ‘2012-08-20 09:26:09‘, 0),  
40.     (12, 5, 2, ‘2012-08-20 09:26:12‘, 0),  
41.     (13, 5, 3, ‘2012-08-20 09:26:13‘, 0),  
42.     (14, 5, 4, ‘2012-08-20 09:26:15‘, 0),  
43.     (15, 5, 5, ‘2012-08-20 09:26:17‘, 0),  
44.     (16, 6, 1, ‘2012-08-20 09:26:20‘, 0),  
45.     (17, 7, 1, ‘2012-08-20 09:26:23‘, 0),  
46.     (18, 7, 2, ‘2012-08-20 09:26:28‘, 0),  
47.     (19, 8, 1, ‘2012-08-20 09:26:32‘, 0),  
48.     (20, 8, 2, ‘2012-08-20 09:26:35‘, 0),  
49.     (21, 9, 1, ‘2012-08-20 09:26:37‘, 0),  
50.     (22, 9, 2, ‘2012-08-20 09:26:40‘, 0),  
51.     (23, 10, 1, ‘2012-08-20 09:26:42‘, 0),  
52.     (24, 10, 2, ‘2012-08-20 09:26:44‘, 0),  
53.     (25, 10, 3, ‘2012-08-20 09:26:51‘, 0),  
54.     (26, 11, 1, ‘2012-08-20 09:26:54‘, 0);  
55. /*!40000 ALTER TABLE `group_message` ENABLE KEYS */;  
56. /*!40014 SET FOREIGN_KEY_CHECKS=1 */;  
57. /*!40101 SET [email protected]_CHARACTER_SET_C

MySQL最佳化GROUP BY－鬆散索引掃描與緊湊索引掃描