快捷導(dǎo)航

一篇文章帶你掌握MySQL索引下推

更新時(shí)間：2022年12月21日 10:23:44 作者：xp_xpxp

索引條件下推,也叫索引下推,英文全稱(chēng)Index Condition Pushdown,簡(jiǎn)稱(chēng)ICP,索引下推是MySQL5.6新添加的特性,用于優(yōu)化數(shù)據(jù)的查詢(xún),下面這篇文章主要給大家介紹了關(guān)于MySQL索引下推的相關(guān)資料,需要的朋友可以參考下

1.什么是索引下推

索引下推(Index Condition PushDown,簡(jiǎn)稱(chēng)ICP)是從MySQL5.6開(kāi)始引入的一個(gè)特性,索引下推通過(guò)減少回表的次數(shù)來(lái)提高數(shù)據(jù)庫(kù)的查詢(xún)效率;

2.案例

準(zhǔn)備:

①.為了演示索引下推,需要安裝MySQL5.5和MySQL5.7兩個(gè)版本的MySQL,因?yàn)樗饕峦剖荕ySQL5.6版本中開(kāi)始引入的新特性,所以這兩個(gè)版本就可以演示出索引下推的特點(diǎn);

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

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í)行計(jì)劃

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

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

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

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

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

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

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

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

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

3>.模糊匹配:

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

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

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

小結(jié):

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

從MySQL5.6開(kāi)始引進(jìn)的索引下推技術(shù),就是用來(lái)解決這樣的問(wèn)題的!

2.2.MySQL5.7版本

1>.模糊匹配:

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

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

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

執(zhí)行流程如下:

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

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

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

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

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

3>.精確匹配:

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

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