前言

后端面試中一定是必問(wèn)mysql的，在以往的面試中好幾個(gè)面試官都反饋我Mysql基礎(chǔ)不行，今天來(lái)著重復(fù)習(xí)一下自己的弱點(diǎn)知識(shí)。在Mysql調(diào)優(yōu)中索引優(yōu)化又是非常重要的方法，不管公司的大小只要后端項(xiàng)目中用到了mysql,幾乎都會(huì)遇到Mysql查詢(xún)需要優(yōu)化的需求。經(jīng)常有時(shí)候前端業(yè)務(wù)沒(méi)有壓力，經(jīng)常會(huì)在管理后臺(tái)邏輯中遇到mysql統(tǒng)計(jì)查詢(xún)壓力，可能是代碼寫(xiě)太爛了，哈哈。在日常工作中我遇到過(guò)同事建立索引后問(wèn)我某個(gè)查詢(xún)條件是否能命中索引，我只能說(shuō)模糊記得最左匹配原則不能準(zhǔn)確地告訴別人是否能命中索引，我今天就打算徹底解決這個(gè)問(wèn)題。

如何驗(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)容是一些抖音的視頻的視頻名稱(chēng)，作者，保存路徑，狀態(tài)等等信息。
來(lái)使用explain關(guān)鍵字試一下執(zhí)行以下sql語(yǔ)句：

explain select * from videos where `user` like'%BY2girl%'

展示信息： 

 其中展示的詳細(xì)信息根據(jù)文章主題這里不做詳細(xì)說(shuō)明吧，就算根據(jù)其他資料稍微理解復(fù)制過(guò)來(lái)，我也記不住。

接下來(lái)我嘗試把這個(gè)user這個(gè)加一個(gè)索引試試：

 這里補(bǔ)充說(shuō)明一下，我直接新建一個(gè)B樹(shù)索引，B樹(shù)索引一般是默認(rèn)創(chuàng)建的索引類(lèi)型，因?yàn)?code>相對(duì)于哈希索引B樹(shù)索引可以獲得穩(wěn)定且較好的查詢(xún)速度，哈希索引更適合適合做精確查詢(xún)

看看不加索引和加索引同一個(gè)查詢(xún)的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)單使用哪些查詢(xún)會(huì)命中索引：

explain  select * from videos where `user` ='BY2' and `path` = 'BY2' and `name`  = 'BY2'

 果然使用到了3個(gè)索引，但是我一直有一個(gè)疑問(wèn)，在中間的查詢(xún)條件使用like模糊返回查詢(xún)，看看命中了哪個(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)化，把可以命中的查詢(xún)條件放在最前面讓它們命中索引，用來(lái)提高查詢(xún)速度。這樣一個(gè)字段增加一個(gè)索引無(wú)疑增加了表的空間，給表記錄的新增和修改操作增加了壓力，聯(lián)合索引可以稍微解決這個(gè)問(wèn)題，接下來(lái)就要說(shuō)聯(lián)合索引。

聯(lián)合索引

聯(lián)合索引指的是對(duì)一張表上把多個(gè)字段當(dāng)制作成一個(gè)索引：

聯(lián)合最左匹配原則解釋?zhuān)?code>以建立索引的字段為查詢(xún)條件，執(zhí)行查詢(xún)時(shí)候左邊的為起點(diǎn)任何連續(xù)的索引都能匹配上，當(dāng)遇到范圍查詢(xún)(>、<、between、like)就會(huì)停止匹配

explain  select * from videos where `user` ='BY2' and `path` = 'BY2' and `name`  = 'BY2'

 不用說(shuō)，這樣一定會(huì)命中這個(gè)聯(lián)合索引，接下來(lái)中間使用一個(gè)like試試：

explain  select * from videos where `user` ='BY2' and `path` like '%BY2%' and `name`  = 'BY2'

 完全沒(méi)有命中索引，中間被打斷了，我自己以為會(huì)命中了一個(gè)user也會(huì)命中整個(gè)聯(lián)合索引，我還以為mysql會(huì)把name和user兩個(gè)字段優(yōu)化在最前面實(shí)現(xiàn)最左原則從而命中整個(gè)聯(lián)合索引，學(xué)到了，接下來(lái)把這個(gè)like查詢(xún)放在最后：

explain  select * from videos where `user` ='BY2' and `path` = 'BY2' and `name`  like '%BY2%'

 看來(lái)是命中了這個(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ù)查詢(xún)條件來(lái)的，跟where 條件順序相關(guān)！

總結(jié)

在日常工作中發(fā)現(xiàn)阿里云的云數(shù)據(jù)庫(kù)會(huì)根據(jù)數(shù)據(jù)庫(kù)熱點(diǎn)查詢(xún)數(shù)據(jù)自動(dòng)增加索引，又減輕了某些不會(huì)建立索引的人的壓力或者減少了建立錯(cuò)誤索引的情況，同時(shí)自動(dòng)減少了數(shù)據(jù)庫(kù)壓力，哈哈。 索引是mysql非常復(fù)雜的知識(shí)，它又非常重要，后面遇到問(wèn)題一定要記錄下來(lái)，親自實(shí)踐增加印象，感覺(jué)在今天的驗(yàn)證過(guò)程中略過(guò)了好多復(fù)雜的知識(shí)，例如一些explain信息的意思，很重要，等到后面遇到了再仔細(xì)研究，今天就到這。

到此這篇關(guān)于驗(yàn)證Mysql中聯(lián)合索引的最左匹配原則詳情的文章就介紹到這了,更多相關(guān)Mysql中聯(lián)合索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論