眾所周知，InnoDB采用IOT（index organization table）即所謂的索引組織表，而葉子節點也就存放了所有的數據，這就意味著，數據總是按照某種順序存儲的。所以問題來了，如果是這樣一個語句，執行起來應該是怎麼樣的呢？語句如下：
select count(distinct a) from table1;
列a上有一個索引，那麼按照簡單的想法來講，如何掃描呢？很簡單，一條一條的掃描，這樣一來，其實做了一次索引全掃描，效率很差。這種掃描方式會掃描到很多很多的重復的索引，這樣說的話優化的辦法也是很容易想到的：跳過重復的索引就可以了。於是網上能搜到這樣的一個優化的辦法：
select count(*) from (select distinct a from table1) t;
從已經搜索到的資料看，這樣的執行計劃中的extra就從using index變成了using index for group-by。
但是，但是，但是，好在我們現在已經沒有使用5.1的版本了，大家基本上都是5.5以上了，這些現代版本，已經實現了loose index scan：
很好很好，就不需要再用這種奇技淫巧去優化SQL了。
文檔裡關於group by這裡寫的有點意思，說是最大眾化的辦法就是進行全表掃描並且創建一個臨時表，這樣執行計劃就會難看的要命了，肯定有ALL和using temporary table了。
5.0之後group by在特定條件下可能使用到loose index scan，
CREATE TABLE log_table ( id INT NOT NULL PRIMARY KEY, log_machine VARCHAR(20) NOT NULL, log_time DATETIME NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE INDEX ix_log_machine_time ON log_table (log_machine, log_time);
1
SELECT MAX(log_time) FROM log_table；
SELECT MAX(log_time) FROM log_table WHERE log_machine IN ('Machine 1')；
這兩條sql都只需一次index seek便可返回，源於索引的有序排序，優化器意識到min/max位於最左/右塊，從而避免范圍掃描；
extra顯示Select tables optimized away ；
2
復制代碼 代碼如下:SELECT MAX(log_time) FROM log_table WHERE log_machine IN (‘Machine 1','Machine 2','Machine 3','Machine 4');
執行計劃type 為range(extra顯示using where; using index)，即執行索引范圍掃描，先讀取所有滿足log_machine約束的記錄，然後對其遍歷找出max value；
改進
復制代碼 代碼如下:SELECT MAX(log_time) FROM log_table WHERE log_machine IN (‘Machine 1','Machine 2','Machine 3','Machine 4') group by log_machine order by 1 desc limit 1;
這滿足group by選擇loose index scan的要求，執行計劃的extra顯示using index for group-by，執行效果等值於
SELECT MAX(log_time) FROM log_table WHERE log_machine IN (‘Machine 1') Union SELECT MAX(log_time) FROM log_table WHERE log_machine IN (‘Machine 2') …..
即對每個log_machine執行loose index scan，rows從原來的82636下降為16(該表總共1,000,000條記錄)。
Group by何時使用loose index scan?
適用條件：
1 針對單表操作
2 Group by使用索引的最左前綴列
3 只支持聚集函數min()/max()
4 Where條件出現的列必須為=constant操作 ， 沒出現在group by中的索引列必須使用constant
5 不支持前綴索引，即部分列索引 ，如index(c1(10))
執行計劃的extra應該顯示using index for group-by
假定表t1有個索引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 SELECT c1, c3 FROM t1 GROUP BY c1, c2;--無法使用松散索引
而SELECT c1, c3 FROM t1 where c3= const GROUP BY c1, c2;則可以
緊湊索引掃描tight index scan
Group by在無法使用loose index scan，還可以選擇tight，若兩者都不可選，則只能借助臨時表；
掃描索引時，須讀取所有滿足條件的索引鍵，要麼是全索引掃描，要麼是范圍索引掃描；
Group by的索引列不連續；或者不是從最左前綴開始，但是where條件裡出現最左列；
SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3; SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;
5.6的改進
事實上，5.6的index condition push down可以彌補loose index scan缺失帶來的性能損失。
KEY(age,zip)
mysql> explain SELECT name FROM people WHERE age BETWEEN 18 AND 20 AND zip IN (12345,12346, 12347); +----+-------------+--------+-------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | people | range | age | age | 4 | NULL | 90556 | Using where | +----+-------------+--------+-------+---------------+------+---------+------+-------+-------------+ 1 row in set (0.01 sec)
根據key_len=4可以推測出sql只用到索引的第一列，即先通過索引查出滿足age (18,20)的行記錄，然後從server層篩選出滿足zip約束的行；
pre-5.6，對於復合索引，只有當引導列使用"="時才有機會在索引掃描時使用到後面的索引列。
mysql> explain SELECT name FROM people WHERE age=18 AND zip IN (12345,12346, 12347); +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | people | range | age | age | 8 | NULL | 3 | Using where | +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
對比一下查詢效率
mysql> SELECT sql_no_cache name FROM people WHERE age=19 AND zip IN (12345,12346, 12347); +----------------------------------+ | name | +----------------------------------+ | 888ba838661aff00bbbce114a2a22423 | +----------------------------------+ 1 row in set (0.06 sec) mysql> SELECT SQL_NO_CACHE name FROM people WHERE age BETWEEN 18 AND 22 AND zip IN (12345,12346, 12347); +----------------------------------+ | name | +----------------------------------+ | ed4481336eb9adca222fd404fa15658e | | 888ba838661aff00bbbce114a2a22423 | +----------------------------------+ 2 rows in set (1 min 56.09 sec)
對於第二條sql，可以使用union改寫，
mysql> SELECT name FROM people WHERE age=18 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people WHERE age=19 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people WHERE age=20 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people WHERE age=21 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people WHERE age=22 AND zip IN (12345,12346, 12347);
而mysql5.6引入了index condition pushdown，從優化器層面解決了此類問題。
