一篇文章帶你掌握MySQL索引下推
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ù)判斷了而已;
3.小結(jié)
所謂的索引下推,就是在搜索引擎中提前判斷對(duì)應(yīng)的搜索條件是否滿(mǎn)足,滿(mǎn)足了再去回表,通過(guò)減少回表次數(shù)進(jìn)而提高查詢(xún)效率;
到此這篇關(guān)于一篇文章帶你掌握MySQL索引下推的文章就介紹到這了,更多相關(guān)MySQL索引下推內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Mysql查詢(xún)?nèi)掌趖imestamp格式的數(shù)據(jù)實(shí)現(xiàn)
本文主要介紹了Mysql查詢(xún)?nèi)掌趖imestamp格式的數(shù)據(jù)實(shí)現(xiàn),文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-01-01關(guān)于mysql的時(shí)區(qū)問(wèn)題
這篇文章主要介紹了關(guān)于mysql的時(shí)區(qū)問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家,2023-11-11Mysql主從同步Last_IO_Errno:1236錯(cuò)誤解決方法
最近遇到Mysql主從同步的Last_IO_Errno:1236錯(cuò)誤問(wèn)題,然后在網(wǎng)上查找相關(guān)解決方案,這里分享給大家,供參考。2017-10-10MySQL中將逗號(hào)分隔的字段轉(zhuǎn)換為多行數(shù)據(jù)的方法
在我們的實(shí)際開(kāi)發(fā)中,經(jīng)常需要存儲(chǔ)一些字段,它們使用像,?-?等連接符進(jìn)行連接,在查詢(xún)過(guò)程中,有時(shí)需要將這些字段使用連接符分割,然后查詢(xún)多條數(shù)據(jù),今天,我們將使用一個(gè)實(shí)際的生產(chǎn)場(chǎng)景來(lái)詳細(xì)解釋這個(gè)解決方案,需要的朋友可以參考下2024-04-04MySQL之select、distinct、limit的使用
這篇文章主要介紹了MySQL之select、distinct、limit的使用,下面文章圍繞select、distinct、limit的相關(guān)資料展開(kāi)聚集內(nèi)容,需要的朋友可以參考一下2021-11-11mysql4.0升級(jí)到mysql5(4.1),解決字符集問(wèn)題
軟件升級(jí)部分就不說(shuō)了,光說(shuō)數(shù)據(jù)庫(kù)遷移。2009-09-09