程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> MySQL優化GROUP BY（松散索引掃描與緊湊索引掃描）

MySQL優化GROUP BY（松散索引掃描與緊湊索引掃描）

編輯：關於MYSQL數據庫

滿足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;
示例數據庫文件

-- --------------------------------------------------------
-- Host:             127.0.0.1
-- Server version:        5.1.57-community - MySQL Community Server (GPL)
-- Server OS:          Win32
-- HeidiSQL version:       7.0.0.4156
-- Date/time:          2012-08-20 16:52:10
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET FOREIGN_KEY_CHECKS=0 */;

-- Dumping structure for table test.group_message
DROP TABLE IF EXISTS `group_message`;
CREATE TABLE IF NOT EXISTS `group_message` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `group_id` int(10) unsigned DEFAULT NULL,
 `user_id` int(10) unsigned DEFAULT NULL,
 `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `abc` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `group_id_user_id_gmt_create` (`group_id`,`user_id`,`gmt_create`)
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

-- Dumping data for table test.group_message: 0 rows
DELETE FROM `group_message`;
/*!40000 ALTER TABLE `group_message` DISABLE KEYS */;
INSERT INTO `group_message` (`id`, `group_id`, `user_id`, `gmt_create`, `abc`) VALUES
	(1, 1, 1, '2012-08-20 09:25:35', 1),
	(2, 2, 1, '2012-08-20 09:25:39', 1),
	(3, 2, 2, '2012-08-20 09:25:47', 1),
	(4, 3, 1, '2012-08-20 09:25:50', 2),
	(5, 3, 2, '2012-08-20 09:25:52', 2),
	(6, 3, 3, '2012-08-20 09:25:54', 0),
	(7, 4, 1, '2012-08-20 09:25:57', 0),
	(8, 4, 2, '2012-08-20 09:26:00', 0),
	(9, 4, 3, '2012-08-20 09:26:02', 0),
	(10, 4, 4, '2012-08-20 09:26:06', 0),
	(11, 5, 1, '2012-08-20 09:26:09', 0),
	(12, 5, 2, '2012-08-20 09:26:12', 0),
	(13, 5, 3, '2012-08-20 09:26:13', 0),
	(14, 5, 4, '2012-08-20 09:26:15', 0),
	(15, 5, 5, '2012-08-20 09:26:17', 0),
	(16, 6, 1, '2012-08-20 09:26:20', 0),
	(17, 7, 1, '2012-08-20 09:26:23', 0),
	(18, 7, 2, '2012-08-20 09:26:28', 0),
	(19, 8, 1, '2012-08-20 09:26:32', 0),
	(20, 8, 2, '2012-08-20 09:26:35', 0),
	(21, 9, 1, '2012-08-20 09:26:37', 0),
	(22, 9, 2, '2012-08-20 09:26:40', 0),
	(23, 10, 1, '2012-08-20 09:26:42', 0),
	(24, 10, 2, '2012-08-20 09:26:44', 0),
	(25, 10, 3, '2012-08-20 09:26:51', 0),
	(26, 11, 1, '2012-08-20 09:26:54', 0);
/*!40000 ALTER TABLE `group_message` ENABLE KEYS */;
/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

參考文獻
1、MySQL如何優化GROUP BY
2、詳解MySQL分組查詢Group By實現原理
3、松散的索引掃描(Loose index scan)
4、MySQL學習筆記