1.什么是索引下推

索引下推(Index Condition PushDown,簡稱ICP)是從MySQL5.6開始引入的一個特性,索引下推通過減少回表的次數(shù)來提高數(shù)據(jù)庫的查詢效率;

2.案例

準備:

①.為了演示索引下推,需要安裝MySQL5.5和MySQL5.7兩個版本的MySQL,因為索引下推是MySQL5.6版本中開始引入的新特性,所以這兩個版本就可以演示出索引下推的特點;

②.數(shù)據(jù)庫腳本:

CREATE TABLE `user1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `username` (`username`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
insert into user1(username,age,address) values('zhangsan',25,'China'),('lisi',30,'China');

2.1.MySQL5.5版本

1>.精確匹配:

select * from user1 where username='zhangsan' and age=25;

2>.查看執(zhí)行計劃

type: ref表示通過索引查找數(shù)據(jù),一般出現(xiàn)在等值匹配的時候,type為ref;

extra: Usering where表示數(shù)據(jù)在server層進行了過濾操作;

可以看到,這個查詢SQL是使用了索引(非主鍵索引)的! 

在MySQL5.5中,由于沒有索引下推,所以上面查詢SQL的執(zhí)行流程如下:

①.首先MySQL的server層調(diào)用存儲引擎獲取username='zhangsan’的一條記錄;

②.存儲引擎找到username='zhangsan’的第一條記錄之后,在B+Tree的葉子節(jié)點中保存著主鍵id,此時通過回表操作,去主鍵索引中找到該條記錄的完整數(shù)據(jù),并返回給server層;

③.server層拿到數(shù)據(jù)之后,判斷該條記錄的age是否為25,如果是,就把該條記錄返回給客戶端,如果不是,那么就丟棄該條記錄;

④.由于userame+age組成的復合索引只是一個普通索引,并不是唯一索引(如果是唯一索引,那么這個查詢就到此結(jié)束了),所以還需要繼續(xù)去搜索有沒有滿足條件的記錄;

注意: 第④步的搜索方式,并不是直接去B+Tree中搜索.由于在username索引中,username字段的存儲是有序的,即username='zhangsan'的記錄都是挨著的,而B+Tree的葉子節(jié)點之間通果雙向鏈表關聯(lián),通過一個葉子節(jié)點就能找到下一個葉子節(jié)點(或者上一個葉子節(jié)點),第②步返回的數(shù)據(jù)中有一個next_record屬性,該屬性就直接指向二級索引的下一條記錄,找到下一條記錄之后,回表拿到所有數(shù)據(jù)并返回給server層,然后重復③,④步; 

3>.模糊匹配:

select * from user1 where username like 'l%' and age=30;

type: range表示按照范圍搜索;

也使用了索引,其SQL的執(zhí)行流程跟上面一條查詢SQL的執(zhí)行流程基本一致! 

小結(jié):

前面兩個查詢SQL,由于查詢的時候是"select *",所以都是需要回表操作的,雖然是復合索引,索引中既有username又有age,但是查詢條件中只能傳入username到存儲引擎中,從存儲引擎中回表拿到一行數(shù)據(jù)的完整記錄之后,再返回給server層,再在server層判斷age是否滿足條件.其實這樣的查詢效率比較低,明明索引中有age的值,但是卻不在索引中比較age的值,而是要回表,取一行的完整記錄出來,返回給server層,然后在和age去比較,要是比較不通過,這條記錄就會被丟棄了.如果我們能夠把age直接傳入存儲引擎,在存儲引擎中直接去判斷age是否滿足條件.如果滿足條件了,再去回表查詢完整的記錄.如果不滿足條件就到此結(jié)束,這樣就可以減少回表的次數(shù),進而提高查詢效率;

從MySQL5.6開始引進的索引下推技術,就是用來解決這樣的問題的!

2.2.MySQL5.7版本

1>.模糊匹配:

select * from user1 where username like 'l%' and age=30;

2>.查看執(zhí)行計劃:

可以看到,MySQL5.7中的這個執(zhí)行計劃和上面MySQL5.5中的執(zhí)行計劃相比,主要是最后的Extra為"Using index condition",這就是MySQL5.6開始引入的索引下推技術(ICP);

執(zhí)行流程如下:

①.MySQL中的server層首先調(diào)用存儲引擎定位到第一個以"l"開頭的username;

②.找到記錄后,存儲引擎并不急著回表,而是繼續(xù)在存儲引擎中判斷這條記錄的age是否為30,如果是,再去回表查詢完整的記錄;如果不是,不去回表了,直接繼續(xù)讀取下一條記錄;

③.存儲引擎將符合條件的數(shù)據(jù)返回給server層,此時如果還有其他非索引的查詢條件,server層繼續(xù)過濾,在上面的案例中,此時沒有其他查詢條件了,server層將最終的數(shù)據(jù)返回給客戶端.假設server層還有其他的查詢條件,并且這個查詢條件把剛剛查到的記錄過濾掉了,那么就會通過該記錄中的next_record屬性讀取下一條記錄,然后重復第②步;

這就是索引下推(Index Condition Pushdown,ICP),有效的減少了回表次數(shù),提高了查詢效率!

上面的案例索引下推的時候不僅判斷age的值也判斷username的值;

3>.精確匹配:

select * from user1 where username='zhangsan' and age=25;

可以看到,這個查詢計劃也使用了索引.如果最后的Extra為null,就表示沒有額外的操作了,其實這只是一個特殊的處理而已,利用搜索條件"username='zhangsan' and age=25",從存儲引擎中找到數(shù)據(jù)之后,沒有再去重復判斷了而已;

3.小結(jié)

所謂的索引下推,就是在搜索引擎中提前判斷對應的搜索條件是否滿足,滿足了再去回表,通過減少回表次數(shù)進而提高查詢效率;

到此這篇關于一篇文章帶你掌握MySQL索引下推的文章就介紹到這了,更多相關MySQL索引下推內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論