一篇文章帶你掌握MySQL索引下推
1.什么是索引下推
索引下推(Index Condition PushDown,簡稱ICP)是從MySQL5.6開始引入的一個(gè)特性,索引下推通過減少回表的次數(shù)來提高數(shù)據(jù)庫的查詢效率;
2.案例
準(zhǔn)備:
①.為了演示索引下推,需要安裝MySQL5.5和MySQL5.7兩個(gè)版本的MySQL,因?yàn)樗饕峦剖荕ySQL5.6版本中開始引入的新特性,所以這兩個(gè)版本就可以演示出索引下推的特點(diǎn);
②.數(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í)行計(jì)劃
type: ref表示通過索引查找數(shù)據(jù),一般出現(xiàn)在等值匹配的時(shí)候,type為ref;
extra: Usering where表示數(shù)據(jù)在server層進(jìn)行了過濾操作;
可以看到,這個(gè)查詢SQL是使用了索引(非主鍵索引)的!
在MySQL5.5中,由于沒有索引下推,所以上面查詢SQL的執(zhí)行流程如下:
①.首先MySQL的server層調(diào)用存儲(chǔ)引擎獲取username='zhangsan’的一條記錄;
②.存儲(chǔ)引擎找到username='zhangsan’的第一條記錄之后,在B+Tree的葉子節(jié)點(diǎn)中保存著主鍵id,此時(shí)通過回表操作,去主鍵索引中找到該條記錄的完整數(shù)據(jù),并返回給server層;
③.server層拿到數(shù)據(jù)之后,判斷該條記錄的age是否為25,如果是,就把該條記錄返回給客戶端,如果不是,那么就丟棄該條記錄;
④.由于userame+age組成的復(fù)合索引只是一個(gè)普通索引,并不是唯一索引(如果是唯一索引,那么這個(gè)查詢就到此結(jié)束了),所以還需要繼續(xù)去搜索有沒有滿足條件的記錄;
注意: 第④步的搜索方式,并不是直接去B+Tree中搜索.由于在username索引中,username字段的存儲(chǔ)是有序的,即username='zhangsan'的記錄都是挨著的,而B+Tree的葉子節(jié)點(diǎn)之間通果雙向鏈表關(guān)聯(lián),通過一個(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í)行流程跟上面一條查詢SQL的執(zhí)行流程基本一致!
小結(jié):
前面兩個(gè)查詢SQL,由于查詢的時(shí)候是"select *",所以都是需要回表操作的,雖然是復(fù)合索引,索引中既有username又有age,但是查詢條件中只能傳入username到存儲(chǔ)引擎中,從存儲(chǔ)引擎中回表拿到一行數(shù)據(jù)的完整記錄之后,再返回給server層,再在server層判斷age是否滿足條件.其實(shí)這樣的查詢效率比較低,明明索引中有age的值,但是卻不在索引中比較age的值,而是要回表,取一行的完整記錄出來,返回給server層,然后在和age去比較,要是比較不通過,這條記錄就會(huì)被丟棄了.如果我們能夠把a(bǔ)ge直接傳入存儲(chǔ)引擎,在存儲(chǔ)引擎中直接去判斷age是否滿足條件.如果滿足條件了,再去回表查詢完整的記錄.如果不滿足條件就到此結(jié)束,這樣就可以減少回表的次數(shù),進(jìn)而提高查詢效率;
從MySQL5.6開始引進(jìn)的索引下推技術(shù),就是用來解決這樣的問題的!
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開始引入的索引下推技術(shù)(ICP);
執(zhí)行流程如下:
①.MySQL中的server層首先調(diào)用存儲(chǔ)引擎定位到第一個(gè)以"l"開頭的username;
②.找到記錄后,存儲(chǔ)引擎并不急著回表,而是繼續(xù)在存儲(chǔ)引擎中判斷這條記錄的age是否為30,如果是,再去回表查詢完整的記錄;如果不是,不去回表了,直接繼續(xù)讀取下一條記錄;
③.存儲(chǔ)引擎將符合條件的數(shù)據(jù)返回給server層,此時(shí)如果還有其他非索引的查詢條件,server層繼續(xù)過濾,在上面的案例中,此時(shí)沒有其他查詢條件了,server層將最終的數(shù)據(jù)返回給客戶端.假設(shè)server層還有其他的查詢條件,并且這個(gè)查詢條件把剛剛查到的記錄過濾掉了,那么就會(huì)通過該記錄中的next_record屬性讀取下一條記錄,然后重復(fù)第②步;
這就是索引下推(Index Condition Pushdown,ICP),有效的減少了回表次數(shù),提高了查詢效率!
上面的案例索引下推的時(shí)候不僅判斷age的值也判斷username的值;
3>.精確匹配:
select * from user1 where username='zhangsan' and age=25;
可以看到,這個(gè)查詢計(jì)劃也使用了索引.如果最后的Extra為null,就表示沒有額外的操作了,其實(shí)這只是一個(gè)特殊的處理而已,利用搜索條件"username='zhangsan' and age=25",從存儲(chǔ)引擎中找到數(shù)據(jù)之后,沒有再去重復(fù)判斷了而已;
3.小結(jié)
所謂的索引下推,就是在搜索引擎中提前判斷對(duì)應(yīng)的搜索條件是否滿足,滿足了再去回表,通過減少回表次數(shù)進(jìn)而提高查詢效率;
到此這篇關(guān)于一篇文章帶你掌握MySQL索引下推的文章就介紹到這了,更多相關(guān)MySQL索引下推內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Mysql查詢?nèi)掌趖imestamp格式的數(shù)據(jù)實(shí)現(xiàn)
本文主要介紹了Mysql查詢?nèi)掌趖imestamp格式的數(shù)據(jù)實(shí)現(xiàn),文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-01-01Mysql主從同步Last_IO_Errno:1236錯(cuò)誤解決方法
最近遇到Mysql主從同步的Last_IO_Errno:1236錯(cuò)誤問題,然后在網(wǎng)上查找相關(guān)解決方案,這里分享給大家,供參考。2017-10-10MySQL中將逗號(hào)分隔的字段轉(zhuǎn)換為多行數(shù)據(jù)的方法
在我們的實(shí)際開發(fā)中,經(jīng)常需要存儲(chǔ)一些字段,它們使用像,?-?等連接符進(jìn)行連接,在查詢過程中,有時(shí)需要將這些字段使用連接符分割,然后查詢多條數(shù)據(jù),今天,我們將使用一個(gè)實(shí)際的生產(chǎn)場景來詳細(xì)解釋這個(gè)解決方案,需要的朋友可以參考下2024-04-04MySQL之select、distinct、limit的使用
這篇文章主要介紹了MySQL之select、distinct、limit的使用,下面文章圍繞select、distinct、limit的相關(guān)資料展開聚集內(nèi)容,需要的朋友可以參考一下2021-11-11mysql4.0升級(jí)到mysql5(4.1),解決字符集問題
軟件升級(jí)部分就不說了,光說數(shù)據(jù)庫遷移。2009-09-09