驗(yàn)證Mysql中聯(lián)合索引的最左匹配原則詳情
前言
后端面試中一定是必問mysql的,在以往的面試中好幾個(gè)面試官都反饋我Mysql基礎(chǔ)不行,今天來著重復(fù)習(xí)一下自己的弱點(diǎn)知識(shí)。在Mysql調(diào)優(yōu)中索引優(yōu)化又是非常重要的方法,不管公司的大小只要后端項(xiàng)目中用到了mysql,幾乎都會(huì)遇到Mysql查詢需要優(yōu)化的需求。經(jīng)常有時(shí)候前端業(yè)務(wù)沒有壓力,經(jīng)常會(huì)在管理后臺(tái)邏輯中遇到mysql統(tǒng)計(jì)查詢壓力,可能是代碼寫太爛了,哈哈。在日常工作中我遇到過同事建立索引后問我某個(gè)查詢條件是否能命中索引,我只能說模糊記得最左匹配原則不能準(zhǔn)確地告訴別人是否能命中索引,我今天就打算徹底解決這個(gè)問題。
如何驗(yàn)證聯(lián)合索引的有效性
使用explain
,在select語(yǔ)句之前使用explain
關(guān)鍵字,就會(huì)返回sql語(yǔ)句執(zhí)行計(jì)劃的信息,而不是執(zhí)行sql。
這里我們簡(jiǎn)單實(shí)踐一下,選取一張表:
有興趣的同學(xué)可以拿這個(gè)sql語(yǔ)句生成一個(gè)一模一樣的表:
CREATE TABLE `videos` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `user` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `like` int DEFAULT NULL, `unlike` int DEFAULT NULL, `status` tinyint(1) DEFAULT NULL, `count` int DEFAULT '0', `type` tinyint DEFAULT '1' COMMENT '1美女2勵(lì)志', PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=36247 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
這個(gè)表的內(nèi)容是一些抖音的視頻的視頻名稱,作者,保存路徑,狀態(tài)等等信息。
來使用explain
關(guān)鍵字試一下執(zhí)行以下sql語(yǔ)句:
explain select * from videos where `user` like'%BY2girl%'
展示信息:
其中展示的詳細(xì)信息根據(jù)文章主題這里不做詳細(xì)說明吧,就算根據(jù)其他資料稍微理解復(fù)制過來,我也記不住。
接下來我嘗試把這個(gè)user這個(gè)加一個(gè)索引試試:
這里補(bǔ)充說明一下,我直接新建一個(gè)B樹索引,B樹索引一般是默認(rèn)創(chuàng)建的索引類型,因?yàn)?code>相對(duì)于哈希索引B樹索引可以獲得穩(wěn)定且較好的查詢速度,哈希索引更適合適合做精確查詢
看看不加索引和加索引同一個(gè)查詢的explain有什么區(qū)別:
explain select count(*) from videos where `user` like'%BY2girl%';
可以看到key
關(guān)鍵字那一列使用到了我自己命名的user_key
這個(gè)索引
多個(gè)單一索引進(jìn)行驗(yàn)證
接下再加兩個(gè)索引:
看下簡(jiǎn)單使用哪些查詢會(huì)命中索引:
explain select * from videos where `user` ='BY2' and `path` = 'BY2' and `name` = 'BY2'
果然使用到了3個(gè)索引,但是我一直有一個(gè)疑問,在中間的查詢條件使用like模糊返回查詢,看看命中了哪個(gè)索引:
explain select * from videos where `user` ='BY2' and `path` like '%BY2%' and `name` = 'BY2'
結(jié)論Mysql會(huì)自動(dòng)對(duì)sql語(yǔ)句進(jìn)行優(yōu)化,把可以命中的查詢條件放在最前面讓它們命中索引,用來提高查詢速度。這樣一個(gè)字段增加一個(gè)索引無(wú)疑增加了表的空間,給表記錄的新增和修改操作增加了壓力,聯(lián)合索引可以稍微解決這個(gè)問題,接下來就要說聯(lián)合索引。
聯(lián)合索引
聯(lián)合索引指的是對(duì)一張表上把多個(gè)字段當(dāng)制作成一個(gè)索引:
聯(lián)合最左匹配原則解釋:以建立索引的字段為查詢條件,執(zhí)行查詢時(shí)候左邊的為起點(diǎn)任何連續(xù)的索引都能匹配上,當(dāng)遇到范圍查詢(>、<、between、like)就會(huì)停止匹配
explain select * from videos where `user` ='BY2' and `path` = 'BY2' and `name` = 'BY2'
不用說,這樣一定會(huì)命中這個(gè)聯(lián)合索引,接下來中間使用一個(gè)like試試:
explain select * from videos where `user` ='BY2' and `path` like '%BY2%' and `name` = 'BY2'
完全沒有命中索引,中間被打斷了,我自己以為會(huì)命中了一個(gè)user
也會(huì)命中整個(gè)聯(lián)合索引,我還以為mysql會(huì)把name
和user
兩個(gè)字段優(yōu)化在最前面實(shí)現(xiàn)最左原則從而命中整個(gè)聯(lián)合索引,學(xué)到了,接下來把這個(gè)like查詢放在最后:
explain select * from videos where `user` ='BY2' and `path` = 'BY2' and `name` like '%BY2%'
看來是命中了這個(gè)聯(lián)合索引,兩個(gè)索引的命中直接命中了整個(gè)聯(lián)合索引,驗(yàn)證成功。
在其中側(cè)面了解到,我設(shè)置索引的順序和最左匹配原則的順序不是一一匹配的,user
, path
這兩個(gè)字段可能會(huì)優(yōu)化順序。但是我設(shè)置的聯(lián)合索引的順序是path
, name
, user
,其中user
, path
中間有一個(gè)name
字段的索引,最左匹配原則是依據(jù)查詢條件來的,跟where 條件順序相關(guān)!
總結(jié)
在日常工作中發(fā)現(xiàn)阿里云的云數(shù)據(jù)庫(kù)會(huì)根據(jù)數(shù)據(jù)庫(kù)熱點(diǎn)查詢數(shù)據(jù)自動(dòng)增加索引,又減輕了某些不會(huì)建立索引的人的壓力或者減少了建立錯(cuò)誤索引的情況,同時(shí)自動(dòng)減少了數(shù)據(jù)庫(kù)壓力,哈哈。 索引是mysql非常復(fù)雜的知識(shí),它又非常重要,后面遇到問題一定要記錄下來,親自實(shí)踐增加印象,感覺在今天的驗(yàn)證過程中略過了好多復(fù)雜的知識(shí),例如一些explain信息的意思,很重要,等到后面遇到了再仔細(xì)研究,今天就到這。
到此這篇關(guān)于驗(yàn)證Mysql中聯(lián)合索引的最左匹配原則詳情的文章就介紹到這了,更多相關(guān)Mysql中聯(lián)合索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
mysql建表報(bào)錯(cuò):invalid?default?value?for?'date'的解決方
最近遇到一個(gè)這樣的問題,出現(xiàn)了invalid default value for 'end_date'錯(cuò)誤,所以下面這篇文章主要給大家介紹了關(guān)于mysql建表報(bào)錯(cuò):invalid?default?value?for?'date'的解決方法,需要的朋友可以參考下2022-12-12mysql創(chuàng)建外鍵報(bào)錯(cuò)的原因及解決(can't?not?create?table)
這篇文章主要介紹了mysql創(chuàng)建外鍵報(bào)錯(cuò)的原因及解決方案(can't?not?create?table),具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2022-09-09MySQL算術(shù)/比較/邏輯/位/運(yùn)算符與正則舉例詳解
每種數(shù)據(jù)庫(kù)都支持SQL語(yǔ)句,但是它們也都有各自支持的運(yùn)算符,下面這篇文章主要給大家介紹了關(guān)于MySQL算術(shù)/比較/邏輯/位/運(yùn)算符與正則的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-02-02MySQL實(shí)戰(zhàn)文章(非常全的基礎(chǔ)入門類教程)
半個(gè)月時(shí)間把MySQL重新鞏固了一遍,梳理了一篇幾萬(wàn)字超硬核文章,想學(xué)習(xí)mysql的朋友可以看看2023-05-05Navicat把csv數(shù)據(jù)導(dǎo)入mysql
這篇文章主要為大家詳細(xì)介紹了Navicat把csv數(shù)據(jù)導(dǎo)入mysql,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2019-05-05MySQL多實(shí)例安裝開機(jī)自啟動(dòng)服務(wù)配置過程
這篇文章主要介紹了MySQL中,多實(shí)例安裝開機(jī)自啟服務(wù)配置的過程詳細(xì)步驟,有需要的朋友可以借鑒參考下,希望可以有所幫助,感謝閱讀2021-09-09