程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 簡單談談MySQL的loose index scan,

簡單談談MySQL的loose index scan,

編輯:MySQL綜合教程

簡單談談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);

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,從優化器層面解決了此類問題。

您可能感興趣的文章:

  • mysql創建Bitmap_Join_Indexes中的約束與索引
  • MySQL裡Create Index 能否創建主鍵 Primary Key
  • MySQL 創建索引(Create Index)的方法和語法結構及例子
  • MySQL 啟動報錯:File ./mysql-bin.index not found (Errcode: 13)
  • mysql截取的字符串函數substring_index的用法
  • MySQL導出所有Index和約束的方法

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved