驗證Mysql中聯合索引的最左匹配原則詳情

2022-08-02 14:06:56

前言

後端面試中一定是必問mysql的，在以往的面試中好幾個面試官都反饋我Mysql基礎不行，今天來著重複習一下自己的弱點知識。在Mysql調優中索引優化又是非常重要的方法，不管公司的大小隻要後端專案中用到了mysql,幾乎都會遇到Mysql查詢需要優化的需求。經常有時候前端業務沒有壓力，經常會在管理後臺邏輯中遇到mysql統計查詢壓力，可能是程式碼寫太爛了，哈哈。在日常工作中我遇到過同事建立索引後問我某個查詢條件是否能命中索引，我只能說模糊記得最左匹配原則不能準確地告訴別人是否能命中索引，我今天就打算徹底解決這個問題。

如何驗證聯合索引的有效性

使用explain,在select語句之前使用explain關鍵字，就會返回sql語句執行計劃的資訊,而不是執行sql。

這裡我們簡單實踐一下，選取一張表：

有興趣的同學可以拿這個sql語句生成一個一模一樣的表：

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勵志',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=36247 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

這個表的內容是一些抖音的視訊的視訊名稱，作者，儲存路徑，狀態等等資訊。
來使用explain關鍵字試一下執行以下sql語句：

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

展示資訊：

其中展示的詳細資訊根據文章主題這裡不做詳細說明吧，就算根據其他資料稍微理解複製過來，我也記不住。

接下來我嘗試把這個user這個加一個索引試試：

這裡補充說明一下，我直接新建一個B樹索引，B樹索引一般是預設建立的索引型別，因為相對於雜湊索引B樹索引可以獲得穩定且較好的查詢速度，雜湊索引更適合適合做精確查詢

看看不加索引和加索引同一個查詢的explain有什麼區別：

explain  select count(*) from videos where `user` like'%BY2girl%';

可以看到key關鍵字那一列使用到了我自己命名的user_key這個索引

多個單一索引進行驗證

接下再加兩個索引：

看下簡單使用哪些查詢會命中索引：

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

果然使用到了3個索引，但是我一直有一個疑問，在中間的查詢條件使用like模糊返回查詢，看看命中了哪個索引：

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

結論Mysql會自動對sql語句進行優化，把可以命中的查詢條件放在最前面讓它們命中索引，用來提高查詢速度。這樣一個欄位增加一個索引無疑增加了表的空間，給表記錄的新增和修改操作增加了壓力，聯合索引可以稍微解決這個問題，接下來就要說聯合索引。

聯合索引

聯合索引指的是對一張表上把多個欄位當製作成一個索引：

聯合最左匹配原則解釋：以建立索引的欄位為查詢條件，執行查詢時候左邊的為起點任何連續的索引都能匹配上，當遇到範圍查詢(>、<、between、like)就會停止匹配

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

不用說，這樣一定會命中這個聯合索引，接下來中間使用一個like試試：

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

完全沒有命中索引，中間被打斷了，我自己以為會命中了一個user也會命中整個聯合索引，我還以為mysql會把name和user兩個欄位優化在最前面實現最左原則從而命中整個聯合索引，學到了，接下來把這個like查詢放在最後：

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

看來是命中了這個聯合索引，兩個索引的命中直接命中了整個聯合索引，驗證成功。

在其中側面瞭解到，我設定索引的順序和最左匹配原則的順序不是一一匹配的，user, path這兩個欄位可能會優化順序。但是我設定的聯合索引的順序是path, name, user，其中user, path中間有一個name欄位的索引，最左匹配原則是依據查詢條件來的，跟where 條件順序相關！

總結

在日常工作中發現阿里雲的雲資料庫會根據資料庫熱點查詢資料自動增加索引，又減輕了某些不會建立索引的人的壓力或者減少了建立錯誤索引的情況，同時自動減少了資料庫壓力，哈哈。索引是mysql非常複雜的知識，它又非常重要，後面遇到問題一定要記錄下來，親自實踐增加印象，感覺在今天的驗證過程中略過了好多複雜的知識，例如一些explain資訊的意思，很重要，等到後面遇到了再仔細研究，今天就到這。

到此這篇關於驗證Mysql中聯合索引的最左匹配原則詳情的文章就介紹到這了,更多相關Mysql中聯合索引內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

驗證Mysql中聯合索引的最左匹配原則詳情

目錄

前言

如何驗證聯合索引的有效性

多個單一索引進行驗證

聯合索引

總結

熱門文章