《Mysql學(xué)習(xí)簡單談?wù)凪ySQL的loose index scan》要點(diǎn)：
本文介紹了Mysql學(xué)習(xí)簡單談?wù)凪ySQL的loose index scan，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。
MYSQL數(shù)據(jù)庫眾所周知,InnoDB采用IOT(index organization table)即所謂的索引組織表,而葉子節(jié)點(diǎn)也就存放了所有的數(shù)據(jù),這就意味著,數(shù)據(jù)總是依照某種順序存儲(chǔ)的.所以問題來了,如果是這樣一個(gè)語句,執(zhí)行起來應(yīng)該是怎么樣的呢?語句如下：
MYSQL數(shù)據(jù)庫
select count(distinct a) from table1;
MYSQL數(shù)據(jù)庫???? 列a上有一個(gè)索引,那么依照簡單的想法來講,如何掃描呢?很簡單,一條一條的掃描,這樣一來,其實(shí)做了一次索引全掃描,效率很差.這種掃描方式會(huì)掃描到很多很多的重復(fù)的索引,這樣說的話優(yōu)化的辦法也是很容易想到的：跳過重復(fù)的索引就可以了.于是網(wǎng)上能搜到這樣的一個(gè)優(yōu)化的辦法：
MYSQL數(shù)據(jù)庫
select count(*) from (select distinct a from table1) t;
MYSQL數(shù)據(jù)庫??? 從已經(jīng)搜索到的資料看,這樣的執(zhí)行計(jì)劃中的extra就從using index變成了using index for group-by.
MYSQL數(shù)據(jù)庫??? 但是,但是,但是,好在我們現(xiàn)在已經(jīng)沒有使用5.1的版本了,大家基本上都是5.5以上了,這些現(xiàn)代版本,已經(jīng)實(shí)現(xiàn)了loose index scan：
MYSQL數(shù)據(jù)庫???? 很好很好,就不需要再用這種奇技淫巧去優(yōu)化SQL了.
MYSQL數(shù)據(jù)庫???? 文檔里關(guān)于group by這里寫的有點(diǎn)意思,說是最大眾化的方法就是進(jìn)行全表掃描并且創(chuàng)建一個(gè)臨時(shí)表,這樣執(zhí)行計(jì)劃就會(huì)難看的要命了,肯定有ALL和using temporary table了.
MYSQL數(shù)據(jù)庫5.0之后group by在特定條件下可能使用到loose index scan,
MYSQL數(shù)據(jù)庫
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);
MYSQL數(shù)據(jù)庫1
MYSQL數(shù)據(jù)庫
SELECT MAX(log_time) FROM log_table；
SELECT MAX(log_time) FROM log_table WHERE log_machine IN ('Machine 1')；
MYSQL數(shù)據(jù)庫這兩條sql都只需一次index seek便可返回,源于索引的有序排序,優(yōu)化器意識(shí)到min/max位于最左/右塊,從而避免范圍掃描；
extra顯示Select tables optimized away ；
2
MYSQL數(shù)據(jù)庫執(zhí)行計(jì)劃type 為range(extra顯示using where; using index),即執(zhí)行索引范圍掃描,先讀取所有滿足log_machine約束的記錄,然后對(duì)其遍歷找出max value；
改進(jìn)
MYSQL數(shù)據(jù)庫
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')
…..
MYSQL數(shù)據(jù)庫即對(duì)每個(gè)log_machine執(zhí)行l(wèi)oose index scan,rows從原來的82636下降為16(該表總共1,000,000條記錄).
MYSQL數(shù)據(jù)庫Group by何時(shí)使用loose index scan?
MYSQL數(shù)據(jù)庫適用條件：
MYSQL數(shù)據(jù)庫1? 針對(duì)單表操作
2? Group by使用索引的最左前綴列
3? 只支持聚集函數(shù)min()/max()
4? Where條件出現(xiàn)的列必須為=constant操作 , 沒出現(xiàn)在group by中的索引列必須使用constant
5? 不支持前綴索引,即部分列索引 ,如index(c1(10))
執(zhí)行計(jì)劃的extra應(yīng)該顯示using index for group-by
假定表t1有個(gè)索引idx(c1,c2,c3)
MYSQL數(shù)據(jù)庫
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;--無法使用松散索引
MYSQL數(shù)據(jù)庫而SELECT c1, c3 FROM t1? where c3= const GROUP BY c1, c2;則可以
MYSQL數(shù)據(jù)庫緊湊索引掃描tight index scan
Group by在無法使用loose index scan,還可以選擇tight,若兩者都不可選,則只能借助臨時(shí)表；
掃描索引時(shí),須讀取所有滿足條件的索引鍵,要么是全索引掃描,要么是范圍索引掃描；
Group by的索引列不連續(xù)；或者不是從最左前綴開始,但是where條件里出現(xiàn)最左列；
MYSQL數(shù)據(jù)庫
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;
MYSQL數(shù)據(jù)庫5.6的改進(jìn)
事實(shí)上,5.6的index condition push down可以彌補(bǔ)loose index scan缺失帶來的性能損失.
KEY(age,zip)
MYSQL數(shù)據(jù)庫
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)
MYSQL數(shù)據(jù)庫根據(jù)key_len=4可以推測出sql只用到索引的第一列,即先通過索引查出滿足age (18,20)的行記錄,然后從server層篩選出滿足zip約束的行；
pre-5.6,對(duì)于復(fù)合索引,只有當(dāng)引導(dǎo)列使用"="時(shí)才有機(jī)會(huì)在索引掃描時(shí)使用到后面的索引列.
MYSQL數(shù)據(jù)庫
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數(shù)據(jù)庫對(duì)比一下查詢效率
MYSQL數(shù)據(jù)庫
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)
MYSQL數(shù)據(jù)庫對(duì)于第二條sql,可以使用union改寫,
MYSQL數(shù)據(jù)庫
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);
MYSQL數(shù)據(jù)庫而mysql5.6引入了index condition pushdown,從優(yōu)化器層面辦理了此類問題.
維易PHP培訓(xùn)學(xué)院每天發(fā)布《Mysql學(xué)習(xí)簡單談?wù)凪ySQL的loose index scan》等實(shí)戰(zhàn)技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養(yǎng)人才。
轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/11225.html
