滿(mǎn)足GROUP BY子句的最一般的方法是掃描整個(gè)表并創(chuàng)建一個(gè)新的臨時(shí)表，表中每個(gè)組的所有行應(yīng)為連續(xù)的，然后使用該臨時(shí)表來(lái)找到組并應(yīng)用累積函數(shù)(如果有)。在某些情況中，MySQL能夠做得更好，即通過(guò)索引訪(fǎng)問(wèn)而不用創(chuàng)建臨時(shí)表。
       為GROUP BY使用索引的最重要的前提條件是所有GROUP BY列引用同一索引的屬性，并且索引按順序保存其關(guān)鍵字。是否用索引訪(fǎng)問(wèn)來(lái)代替臨時(shí)表的使用還取決于在查詢(xún)中使用了哪部分索引、為該部分指定的條件，以及選擇的累積函數(shù)。
       由于GROUP BY 實(shí)際上也同樣會(huì)進(jìn)行排序操作，而且與ORDER BY 相比，GROUP BY 主要只是多了排序之后的分組操作。當(dāng)然，如果在分組的時(shí)候還使用了其他的一些聚合函數(shù)，那么還需要一些聚合函數(shù)的計(jì)算。所以，在GROUP BY 的實(shí)現(xiàn)過(guò)程中，與 ORDER BY 一樣也可以利用到索引。在MySQL 中，GROUP BY 的實(shí)現(xiàn)同樣有多種（三種）方式，其中有兩種方式會(huì)利用現(xiàn)有的索引信息來(lái)完成 GROUP BY，另外一種為完全無(wú)法使用索引的場(chǎng)景下使用。下面我們分別針對(duì)這三種實(shí)現(xiàn)方式做一個(gè)分析。

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

對(duì)“松散索引掃描”的定義，本人看了很多網(wǎng)上的介紹，都不甚明白。在此邏列如下：
定義1：松散索引掃描，實(shí)際上就是當(dāng) MySQL 完全利用索引掃描來(lái)實(shí)現(xiàn) GROUP BY 的時(shí)候，并不需要掃描所有滿(mǎn)足條件的索引鍵即可完成操作得出結(jié)果。
定義2：優(yōu)化Group By最有效的辦法是當(dāng)可以直接使用索引來(lái)完全獲取需要group的字段。使用這個(gè)訪(fǎng)問(wèn)方法時(shí)，MySQL使用對(duì)關(guān)鍵字排序的索引的類(lèi)型（比如BTREE索引）。這使得索引中用于group的字段不必完全涵蓋WHERE條件中索引對(duì)應(yīng)的key。由于只包含索引中關(guān)鍵字的一部分，因此稱(chēng)為松散的索引掃描。
意思是索引中用于group的字段，沒(méi)必要包含多列索引的全部字段。例如：有一個(gè)索引idx(c1,c2,c3)，那么group by c1、group by c1,c2這樣c1或c1、c2都只是索引idx的一部分。要注意的是，索引中用于group的字段必須符合索引的“最左前綴”原則。group by c1,c3是不會(huì)使用松散的索引掃描的
例如：
explain
SELECT group_id,gmt_create
FROM group_message
WHERE user_id>1
GROUP BY group_id,gmt_create;
本人理解“定義2”的例子說(shuō)明
有一個(gè)索引idx(c1,c2,c3)
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
索引中用于group的字段為c1,c2
不必完全涵蓋WHERE條件中索引對(duì)應(yīng)的key（where條件中索引，即為c1；c1對(duì)應(yīng)的key，即為idx）
索引中用于group的字段(c1,c2)只包含索引中關(guān)鍵字(c1,c2,c3)的一部分，因此稱(chēng)為松散的索引掃描。
要利用到松散索引掃描實(shí)現(xiàn)GROUP BY，需要至少滿(mǎn)足以下幾個(gè)條件：
◆ 查詢(xún)針對(duì)一個(gè)單表
◆ GROUP BY 條件字段必須在同一個(gè)索引中最前面的連續(xù)位置；
GROUP BY包括索引的第1個(gè)連續(xù)部分(如果對(duì)于GROUP BY，查詢(xún)有一個(gè)DISTINCT子句，則所有DISTINCT的屬性指向索引開(kāi)頭)。
◆ 在使用GROUP BY 的同時(shí)，如果有聚合函數(shù)，只能使用 MAX 和 MIN 這兩個(gè)聚合函數(shù)，并且它們均指向相同的列。
◆ 如果引用(where條件中)到了該索引中GROUP BY 條件之外的字段條件的時(shí)候，必須以常量形式存在，但MIN()或MAX() 函數(shù)的參數(shù)例外；
   或者說(shuō)：索引的任何其它部分（除了那些來(lái)自查詢(xún)中引用的GROUP BY）必須為常數(shù)(也就是說(shuō)，必須按常量數(shù)量來(lái)引用它們)，但MIN()或MAX() 函數(shù)的參數(shù)例外。
補(bǔ)充：如果sql中有where語(yǔ)句，且select中引用了該索引中GROUP BY 條件之外的字段條件的時(shí)候，where中這些字段要以常量形式存在。
◆ 如果查詢(xún)中有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;
松散索引掃描，此類(lèi)查詢(xún)的EXPLAIN輸出顯示Extra列的Using index for group-by

下面的查詢(xún)提供該類(lèi)的幾個(gè)例子，假定表t1(c1,c2,c3,c4)有一個(gè)索引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；

由于上述原因，不能用該快速選擇方法執(zhí)行下面的查詢(xún)：

1、除了MIN()或MAX()，還有其它累積函數(shù)，例如：
     SELECT c1, SUM(c2) FROM t1 GROUP BY c1;
2、GROUP BY子句中的域不引用索引開(kāi)頭，如下所示：
     SELECT c1,c2 FROM t1 GROUP BY c2, c3;
3、查詢(xún)引用了GROUP BY部分后面的關(guān)鍵字的一部分，并且沒(méi)有等于常量的等式，例如：
     SELECT c1,c3 FROM t1 GROUP BY c1, c2；
這個(gè)例子中，引用到了c3（c3必須為組合索引中的一個(gè)），因?yàn)間roup by 中沒(méi)有c3。并且沒(méi)有等于常量的等式。所以不能使用松散索引掃描
可以這樣改一下：SELECT c1,c3 FROM t1 where c3='a' GROUP BY c1, c2
下面這個(gè)例子不能使用松散索引掃描
SELECT c1,c3 FROM t1 where c3='a' GROUP BY c1, c2
為什么松散索引掃描的效率會(huì)很高？
答：因?yàn)樵跊](méi)有WHERE 子句，也就是必須經(jīng)過(guò)全索引掃描的時(shí)候， 松散索引掃描需要讀取的鍵值數(shù)量與分組的組數(shù)量一樣多，也就是說(shuō)比實(shí)際存在的鍵值數(shù)目要少很多。而在WHERE 子句包含范圍判斷式或者等值表達(dá)式的時(shí)候， 松散索引掃描查找滿(mǎn)足范圍條件的每個(gè)組的第1 個(gè)關(guān)鍵字，并且再次讀取盡可能最少數(shù)量的關(guān)鍵字。

2、使用緊湊索引掃描（Tight index scan）實(shí)現(xiàn) GROUP BY

緊湊索引掃描實(shí)現(xiàn) GROUP BY 和松散索引掃描的區(qū)別主要在于：
緊湊索引掃描需要在掃描索引的時(shí)候，讀取所有滿(mǎn)足條件的索引鍵，然后再根據(jù)讀取出的數(shù)據(jù)來(lái)完成 GROUP BY 操作得到相應(yīng)結(jié)果。
這時(shí)候的執(zhí)行計(jì)劃的 Extra 信息中已經(jīng)沒(méi)有“Using index for group-by”了，但并不是說(shuō) MySQL 的 GROUP BY 操作并不是通過(guò)索引完成的，只不過(guò)是需要訪(fǎng)問(wèn) WHERE 條件所限定的所有索引鍵信息之后才能得出結(jié)果。這就是通過(guò)緊湊索引掃描來(lái)實(shí)現(xiàn) GROUP BY 的執(zhí)行計(jì)劃輸出信息。
在 MySQL 中，MySQL Query Optimizer 首先會(huì)選擇嘗試通過(guò)松散索引掃描來(lái)實(shí)現(xiàn) GROUP BY 操作，當(dāng)發(fā)現(xiàn)某些情況無(wú)法滿(mǎn)足松散索引掃描實(shí)現(xiàn) GROUP BY 的要求之后，才會(huì)嘗試通過(guò)緊湊索引掃描來(lái)實(shí)現(xiàn)。
當(dāng) GROUP BY 條件字段并不連續(xù)或者不是索引前綴部分的時(shí)候，MySQL Query Optimizer 無(wú)法使用松散索引掃描。
這時(shí)檢查where 中的條件字段是否有索引的前綴部分，如果有此前綴部分，且該部分是一個(gè)常量，且與group by 后的字段組合起來(lái)成為一個(gè)連續(xù)的索引。這時(shí)按緊湊索引掃描。

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

需讀取group_id=2的所有數(shù)據(jù)，然后在讀取的數(shù)據(jù)中完成group by操作得到結(jié)果。（這里group by 字段并不是一個(gè)連續(xù)索引，正好where 中g(shù)roup_id正好彌補(bǔ)缺失的索引鍵，又恰好是一個(gè)常量，因此使用緊湊索引掃描）
group_id user_id 這個(gè)順序是可以使用該索引。如果連接的順序不符合索引的“最左前綴”原則，則不使用緊湊索引掃描。

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

GROUP BY中有一個(gè)差距，但已經(jīng)由條件user_id = 1覆蓋。
explain
SELECT group_id,gmt_create
FROM group_message
WHERE user_id = 1 GROUP BY group_id,gmt_create

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

下面的例子都不使用緊湊索引掃描
user_id,gmt_create 連接起來(lái)并不符合索引“最左前綴”原則
explain
SELECT group_id,gmt_create
FROM group_message
WHERE user_id = 1 GROUP BY gmt_create
group_id,gmt_create 連接起來(lái)并不符合索引“最左前綴”原則
explain
SELECT gmt_create
FROM group_message
WHERE group_id=1 GROUP BY gmt_create;

 3、使用臨時(shí)表實(shí)現(xiàn) GROUP BY

MySQL Query Optimizer 發(fā)現(xiàn)僅僅通過(guò)索引掃描并不能直接得到 GROUP BY 的結(jié)果之后，他就不得不選擇通過(guò)使用臨時(shí)表然后再排序的方式來(lái)實(shí)現(xiàn) GROUP BY了。在這樣示例中即是這樣的情況。 group_id 并不是一個(gè)常量條件，而是一個(gè)范圍，而且 GROUP BY 字段為 user_id。所以 MySQL 無(wú)法根據(jù)索引的順序來(lái)幫助 GROUP BY 的實(shí)現(xiàn)，只能先通過(guò)索引范圍掃描得到需要的數(shù)據(jù)，然后將數(shù)據(jù)存入臨時(shí)表，然后再進(jìn)行排序和分組操作來(lái)完成 GROUP BY。
explain
SELECT group_id
FROM group_message
WHERE group_id between 1 and 4
GROUP BY user_id;
示例數(shù)據(jù)庫(kù)文件

-- --------------------------------------------------------
-- 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 */;

參考文獻(xiàn)
1、MySQL如何優(yōu)化GROUP BY
2、詳解MySQL分組查詢(xún)Group By實(shí)現(xiàn)原理
3、松散的索引掃描(Loose index scan)
4、MySQL學(xué)習(xí)筆記

最新評(píng)論