MYSQL 中的GROUP BY 的方式 (1)(loose index scan松散掃描 tight index scan緊湊掃描)

水平有限有誤請(qǐng)指出，轉(zhuǎn)載請(qǐng)說(shuō)明出處

測(cè)試腳本：
create table tgrploose(p_id int primary key auto_increment,s_id1 int,s_id2 int,s_id3 int, key(s_id1,s_id2,s_id3));
create table tgrpnloose(p_id int primary key auto_increment,s_id1 int,s_id2 int,s_id3 int, key(s_id1,s_id2,s_id3));


 delimiter //
 create procedure inloose1()
     begin
    declare i int;
     set i=0;
     while i<20000 do
         insert into tgrploose(s_id1,s_id2,s_id3) values(FLOOR((RAND()*2)),FLOOR((RAND()*3)),FLOOR((RAND()*4)) );
         set i=i+1;
     end while;
  end;
//
delimiter ;




 delimiter //
 create procedure innloose()
     begin
    declare i int;
     set i=0;
     while i<20000 do
         insert into tgrpnloose(s_id1,s_id2,s_id3) values(FLOOR((RAND()*10000)),FLOOR((RAND()*10000)),FLOOR((RAND()*10000)) );
         set i=i+1;
     end while;
  end;
//
delimiter ;


call inloose();
call innloose();


一、MYSQL 中可能的GROUP BY 方式
1、loose index scan(松散索引掃描) 執(zhí)行計(jì)劃必然出現(xiàn)Using index for group-by 
2、tight index scan(緊湊索引掃描) 執(zhí)行計(jì)劃必然出現(xiàn)Using index但是不涉及Using temporary; Using filesort
3、常規(guī)方式掃描                         執(zhí)行計(jì)劃涉及到Using temporary; Using filesort  

二、各種方式說(shuō)明
1、loose index scan(松散索引掃描) 執(zhí)行計(jì)劃必然出現(xiàn)Using index for group-by
   實(shí)際上這種掃描方式源于B+樹(shù)索引結(jié)構(gòu)的，我們回顧一下索引葉子結(jié)點(diǎn)的結(jié)構(gòu)
   首先考慮語(yǔ)句
     while i<20000 do
         insert into tgrploose(s_id1,s_id2,s_id3) values(FLOOR((RAND()*2)),FLOOR((RAND()*3)),FLOOR((RAND()*4)) );
   明顯這里我是向tgrploose插入大約20000條數(shù)據(jù)，同時(shí)s_id1的值只有2個(gè)不同值，s_id2的值只有3個(gè)不同的值，s_id3的值只有
   4個(gè)不同的值，這樣做是為了將loose(松散)的影響盡量放大，我們知道在key(s_id1,s_id2,s_id3)中索引的排列為先按照
   s_id1的順序排列如果相同按照s_id2的順序排列如果相同按照s_id3排列，如果都相同按照該primary id排列就是我們這里的
   p_id這個(gè)我早就證明過(guò)了，其實(shí)這也是B+樹(shù)索引的特性，驗(yàn)證參考如下文章:
   (http://blog.itpub.net/7728585/viewspace-2128817/)
   實(shí)際上我們可以通過(guò)語(yǔ)句驗(yàn)證索引的結(jié)構(gòu)


mysql> explain  select s_id1,s_id2,s_id3,min(p_id),max(p_id),count(*) from tgrploose group by s_id1,s_id2,s_id3;
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key   | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | tgrploose | NULL       | index | s_id1         | s_id1 | 15      | NULL | 19982 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.01 sec)


可以看到這里沒(méi)有filesort沒(méi)有排序，并且使用TYPE=INDEX的方式進(jìn)行訪(fǎng)問(wèn)索引S_ID1，這種方式實(shí)際上就是group by中的緊湊掃描方式
mysql> select s_id1,s_id2,s_id3,min(p_id),max(p_id),count(*) from tgrploose group by s_id1,s_id2,s_id3 ;
+-------+-------+-------+-----------+-----------+----------+
| s_id1 | s_id2 | s_id3 | min(p_id) | max(p_id) | count(*) |
+-------+-------+-------+-----------+-----------+----------+
|     0 |     0 |     0 |        20 |     19991 |      882 |
|     0 |     0 |     1 |        10 |     19950 |      835 |
|     0 |     0 |     2 |         3 |     19979 |      830 |
|     0 |     0 |     3 |         1 |     19944 |      853 |
|     0 |     1 |     0 |        22 |     19999 |      762 |
|     0 |     1 |     1 |        26 |     19987 |      786 |
|     0 |     1 |     2 |        21 |     19968 |      867 |
|     0 |     1 |     3 |        19 |     19997 |      908 |
|     0 |     2 |     0 |         7 |     19916 |      848 |
|     0 |     2 |     1 |        14 |     19971 |      906 |
|     0 |     2 |     2 |         4 |     19988 |      870 |
|     0 |     2 |     3 |        80 |     19906 |      762 |
|     1 |     0 |     0 |        49 |     19990 |      779 |
|     1 |     0 |     1 |        38 |     19976 |      886 |
|     1 |     0 |     2 |         2 |     19981 |      857 |
|     1 |     0 |     3 |        37 |     19998 |      830 |
|     1 |     1 |     0 |        65 |     19993 |      839 |
|     1 |     1 |     1 |         5 |     19984 |      822 |
|     1 |     1 |     2 |         8 |     19996 |      808 |
|     1 |     1 |     3 |         6 |     19927 |      792 |
|     1 |     2 |     0 |        91 |     19992 |      797 |
|     1 |     2 |     1 |        24 |     20000 |      839 |
|     1 |     2 |     2 |         9 |     19965 |      779 |
|     1 |     2 |     3 |        12 |     19977 |      863 |
+-------+-------+-------+-----------+-----------+----------+
實(shí)際也是驗(yàn)證了我們說(shuō)法s_id1的順序排列如果相同按照s_id2的順序排列如果相同按照s_id3排列，如果都相同按照該primary id排列。
那么考慮如下的group by查詢(xún)
select s_id1,s_id2,s_id3,min(p_id) from tgrploose group by s_id1,s_id2,s_id3 ;


mysql> explain select s_id1,s_id2,s_id3,min(p_id) from tgrploose group by s_id1,s_id2,s_id3 ;
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tgrploose | NULL       | range | s_id1         | s_id1 | 15      | NULL |   25 |   100.00 | Using index for group-by |
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)


因?yàn)閟_id1,s_id2,s_id3在本列子中一共才24個(gè)不同的值，相當(dāng)?shù)南∈瑁蔷涂梢愿鶕?jù)索引結(jié)構(gòu)訪(fǎng)問(wèn)到葉子結(jié)點(diǎn)找到最小的那個(gè)p_id值即可，
其他的p_id就不用看了，然后跳到下一個(gè)s_id1,s_id2,s_id3組合，這就是稀疏掃描的優(yōu)勢(shì)，如果非要給稀疏下一個(gè)定義那么就是group by
后字段組合相對(duì)于表總行數(shù)的比率，這里表為20000行，s_id1,s_id2,s_id3為24個(gè)值，那么比率為24/20000,如果這個(gè)值越大則越稠密，
如果越小越則稀疏(這讓我想到稀疏矩陣)。
其次我們要考慮一下loose稀疏索引掃描的性能問(wèn)題（這部分為自己理解的，沒(méi)有參考資料）一般的情況下我們使用type=INDEX這樣的方式完成
group by，這種方式下訪(fǎng)問(wèn)是比較順序的并且訪(fǎng)問(wèn)葉子結(jié)點(diǎn)即可，而稀疏索引掃描不得不多次使用根結(jié)點(diǎn)分支結(jié)點(diǎn)來(lái)定位，每次跳過(guò)的距離，這
個(gè)可能隨機(jī)訪(fǎng)問(wèn)，并且多次訪(fǎng)問(wèn)跟節(jié)點(diǎn)和分支節(jié)點(diǎn)也是需要開(kāi)銷(xiāo)的，索引他們之間就存在一個(gè)性能的綜合考慮，到底使用稀疏索引掃描還是緊湊
索引掃描其根源在于上面說(shuō)的那個(gè)稀疏的比例問(wèn)題?？紤]前面給出的tgrpnloose這個(gè)表我插入數(shù)據(jù)時(shí)候?qū)_id1,s_id2,s_id3不同值都設(shè)置為10000
那么這個(gè)時(shí)候就非常稠密了，比較執(zhí)行計(jì)劃如下：
mysql> explain select s_id1,s_id2,s_id3,min(p_id) from tgrpnloose group by s_id1,s_id2,s_id3;
+----+-------------+------------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key   | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | tgrpnloose | NULL       | index | s_id1         | s_id1 | 15      | NULL | 19982 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


mysql> explain select s_id1,s_id2,s_id3,min(p_id) from tgrploose group by s_id1,s_id2,s_id3;
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tgrploose | NULL       | range | s_id1         | s_id1 | 15      | NULL |   25 |   100.00 | Using index for group-by |
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)


可以看到MYSQL做出了選擇，對(duì)tgrpnloose使用了緊湊索引掃描tight index scan(后面描述)，當(dāng)出現(xiàn)Using index for group-by為使用稀疏索引掃描完成group 
by。一些不能用到稀疏索引掃描來(lái)完成group by 的限制如下：
1、性能考慮(參考如上tgrpnloose的列子)
2、對(duì)單一數(shù)據(jù)表進(jìn)行g(shù)roup by(參考tight index scan部分例子)
3、不能使用前綴索引(prefix index)
4、僅僅可以使用max(),min()聚合函數(shù)，其他聚合函數(shù)如count(),sum()不支持
如：
mysql> explain select a.s_id1,a.s_id2,count(*) from tgrploose a where a.s_id1>30 group by a.s_id1,a.s_id2;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | a     | NULL       | range | s_id1         | s_id1 | 5       | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
5、group by中必須滿(mǎn)足最左側(cè)列一致，如果不一致棄用松散掃描方式
如：
mysql> explain select a.s_id2,a.s_id3 from tgrploose a  group by a.s_id2,a.s_id3;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+----------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows  | filtered | Extra                                        |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | index | s_id1         | s_id1 | 15      | NULL | 19982 |   100.00 | Using index; Using temporary; Using filesort |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

注意一下這個(gè)查詢(xún)，雖然使用了索引掃描，同時(shí)使用了Using temporary; Using filesort，也就是使用了臨時(shí)表來(lái)存儲(chǔ)a.s_id2,a.s_id3和值然后做了排序操作。
但這個(gè)不是緊湊索引掃描的方式，因?yàn)槭褂昧伺R時(shí)表和排序。

在官方文檔中如下查詢(xún)都能使用到稀疏索引掃描(key(c1,c2,c3) table(c1,c2,c3,c4))
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;
SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;
SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;
其實(shí)根據(jù)索引的結(jié)構(gòu)和稀疏掃描的原理稍加考慮可以明白為什么能夠使用到稀疏索引掃描

2、tight index scan(緊湊索引掃描) 執(zhí)行計(jì)劃必然出現(xiàn)Using index但是不涉及Using temporary; Using filesort
   描述為僅對(duì)驅(qū)動(dòng)表(注意是驅(qū)動(dòng)表)中數(shù)據(jù)進(jìn)行分組的時(shí)候，如果group by按照索引的順序給出，如果有缺失需要使用column=constant的情況
   比如：
   按照順序給出
mysql>  explain select b.s_id1,b.s_id2,max(a.s_id3) from  tgrpnloose b STRAIGHT_JOIN tgrploose a on a.s_id1=b.s_id1 group by b.s_id1,b.s_id2;
+----+-------------+-------+------------+-------+---------------+-------+---------+--------------+-------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref          | rows  | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------+---------+--------------+-------+----------+--------------------------+
|  1 | SIMPLE      | b     | NULL       | index | s_id1         | s_id1 | 15      | NULL         | 19982 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | a     | NULL       | ref   | s_id1         | s_id1 | 5       | test.b.s_id1 |  9991 |   100.00 | Using index              |
+----+-------------+-------+------------+-------+---------------+-------+---------+--------------+-------+----------+--------------------------+
2 rows in set, 1 warning (0.01 sec)


  順序缺失但是使用s_id1=10
 mysql>  explain select b.s_id2,max(a.s_id3) from  tgrpnloose b STRAIGHT_JOIN tgrploose a on a.s_id1=b.s_id1 where b.s_id1=10 group by b.s_id2;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | b     | NULL       | ref  | s_id1         | s_id1 | 5       | const |    2 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | a     | NULL       | ref  | s_id1         | s_id1 | 5       | const |    1 |   100.00 | Using index              |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+--------------------------+
2 rows in set, 1 warning (0.02 sec)

我們能夠清楚的看到這種情況用不到索引稀疏掃描因?yàn)椴皇菃伪聿樵?xún)，但是在 Extra 都沒(méi)有出現(xiàn)Using temporary; Using filesort，因?yàn)槭褂昧藅ight 
index scan(緊湊索引掃描)，注意type=ref 是b.s_id1=10因?yàn)樗饕皇俏ㄒ凰饕?br /> 再看下面的例子：

mysql>  explain select b.s_id2,max(a.s_id3) from  tgrpnloose b STRAIGHT_JOIN tgrploose a on a.s_id1=b.s_id1 group by b.s_id2;
+----+-------------+-------+------------+-------+---------------+-------+---------+--------------+-------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref          | rows  | filtered | Extra                                                     |
+----+-------------+-------+------------+-------+---------------+-------+---------+--------------+-------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | b     | NULL       | index | s_id1         | s_id1 | 15      | NULL         | 19982 |   100.00 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | NULL       | ref   | s_id1         | s_id1 | 5       | test.b.s_id1 |  9991 |   100.00 | Using index                                               |
+----+-------------+-------+------------+-------+---------------+-------+---------+--------------+-------+----------+-----------------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

明顯group by b.s_id2不滿(mǎn)足按照索引順序進(jìn)行g(shù)roup by也就是不滿(mǎn)足最左原則，同時(shí)沒(méi)有s_id1=10這樣常量，使用了
Using index; Using temporary; Using filesort用到了臨時(shí)表和filesort排序。
還要明確一點(diǎn)這里是驅(qū)動(dòng)表一定要注意，如果不加STRAIGHT_JOIN MYSQL給出如下的執(zhí)行計(jì)劃
mysql>  explain select b.s_id1,b.s_id2,max(a.s_id3) from  tgrpnloose b join tgrploose a on a.s_id1=b.s_id1 group by b.s_id1,b.s_id2;
+----+-------------+-------+------------+-------+---------------+-------+---------+--------------+-------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref          | rows  | filtered | Extra                                                     |
+----+-------------+-------+------------+-------+---------------+-------+---------+--------------+-------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | index | s_id1         | s_id1 | 15      | NULL         | 19982 |   100.00 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | b     | NULL       | ref   | s_id1         | s_id1 | 5       | test.a.s_id1 |     2 |   100.00 | Using index                                               |
+----+-------------+-------+------------+-------+---------------+-------+---------+--------------+-------+----------+-----------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
和第一個(gè)列子的區(qū)別就是a變?yōu)榱蓑?qū)動(dòng)表，b變?yōu)榱吮或?qū)動(dòng)表，使用了 Using index; Using temporary; Using filesort

另外tight index scan(緊湊索引掃描)當(dāng)然也適用于單表的情況，如：
-- 不滿(mǎn)足最左原則，棄用loose index scan
mysql>  explain select b.s_id2 from  tgrploose b where b.s_id1=10  group by b.s_id2;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | b     | NULL       | ref  | s_id1         | s_id1 | 5       | const |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)
-- 聚合函數(shù)count，棄用loose index scan
mysql>  explain select b.s_id1,count(*) from  tgrploose b  group by b.s_id1;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | b     | NULL       | index | s_id1         | s_id1 | 15      | NULL | 19982 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+
-- 性能考慮，棄用loose index scan
mysql> explain select s_id1,s_id2,s_id3,min(p_id) from tgrpnloose group by s_id1,s_id2,s_id3;
+----+-------------+------------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key   | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | tgrpnloose | NULL       | index | s_id1         | s_id1 | 15      | NULL | 19982 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

簡(jiǎn)而言之，如果不能使用loose index scan會(huì)優(yōu)先考慮tight index scan，來(lái)避免可能的使用臨時(shí)表和排序操作

3、常規(guī)方式掃描,執(zhí)行計(jì)劃涉及到Using temporary; Using filesort  
   簡(jiǎn)單的說(shuō)在沒(méi)有辦法使用索引規(guī)避排序的情況下 需要使用這種方式進(jìn)行g(shù)roup by，需要使用這種常規(guī)的方式，先將group by的字段放到臨時(shí)表
   然后進(jìn)行排序去重操作。
   上面已經(jīng)給出了一列子這里再看一個(gè)
   -- //不滿(mǎn)足最左原則，棄用loose index scan,不滿(mǎn)足缺失部分column=constant棄用tight index scan
mysql>  explain select b.s_id3,count(*) from  tgrploose b  where b.s_id1=10 group by b.s_id3;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra                                                     |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | b     | NULL       | ref  | s_id1         | s_id1 | 5       | const |    1 |   100.00 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------------------------------------------+


改為
mysql>  explain select b.s_id3,count(*) from  tgrploose b  where b.s_id1=10 and b.s_id2=10 group by b.s_id3;
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref         | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+--------------------------+
|  1 | SIMPLE      | b     | NULL       | ref  | s_id1         | s_id1 | 10      | const,const |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+--------------------------+
可以使用tight index scan(緊湊索引掃描)，這是前面說(shuō)了的原則


三、oracle中的index skip scan
ORACLE中也有差不多的用法，但是范圍更廣不限于僅僅是group by，但是一般情況考慮為沒(méi)有正確的為謂詞建立前綴索引，效率不是最優(yōu)，而是
比全表好一些，它僅僅適用于前導(dǎo)列distinct值很少，非前導(dǎo)列選擇性比較高的情況，其實(shí)也就是前導(dǎo)列是稀疏的，而非前導(dǎo)列是稠密的。其性能的
問(wèn)題應(yīng)該也在于不斷的使用根結(jié)點(diǎn)和分支節(jié)點(diǎn)定位和可能的隨機(jī)讀上，這里就不給出例子了。
四、總結(jié)
可以看到MYSQL三種group by 方式效率越來(lái)越低及(優(yōu)化器選擇正確的情況下)：
loose index scan(松散索引掃描)>tight index scan(緊湊索引掃描)>常規(guī)方式掃描
但是適用范圍越來(lái)越廣。

關(guān)于在trace中發(fā)現(xiàn)了代價(jià)的計(jì)算，這也是導(dǎo)致loose index scan(松散索引掃描)和tight index scan(緊湊索引掃描)切換的
根據(jù)，隨后會(huì)在根據(jù)trace看看源碼的判斷。

tight index scan：                                                     loose index scan：
T@2: | | | | | | | | | | opt: distinct_aggregate: 0             T@2: | | | | | | | | | | opt: distinct_aggregate: 0
T@2: | | | | | | | | | | opt: rows: 19983                         T@2: | | | | | | | | | | opt: rows: 7
T@2: | | | | | | | | | | opt: cost: 8040.2                         T@2: | | | | | | | | | | opt: cost: 9.8


這里也給出一個(gè)12條數(shù)據(jù)key(s_id1,s_id2) primary key(p_id)的索引的排列方式，方便大家理 解
明顯先按照s_id1排序，s_id1相同按照s_id2排序，s_id2相同按照主鍵p_id排序

s_id1	0	0	0	0	0	0	1	1	1	1	1	1
s_id2	0	0	1	1	2	2	0	0	1	1	2	2
p_id	5	12	1	6	3	9	2	8	4	10	0	11