程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 簡略談談MySQL的loose index scan

簡略談談MySQL的loose index scan

編輯：MySQL綜合教程

簡略談談MySQL的loose index scan。本站提示廣大學習愛好者：（簡略談談MySQL的loose index scan）文章只能為提供參考，不一定能成為您想要的結果。以下是簡略談談MySQL的loose index scan正文

盡人皆知，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);

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，從優化器層面處理了此類成績。