說(shuō)明

說(shuō)到最左匹配原則，我們還得先從組合索引說(shuō)起。

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(5) NOT NULL AUTO_INCREMENT COMMENT '用戶(hù)id',
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用戶(hù)名',
  `password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用戶(hù)密碼',
  `create_essay` int(5) NOT NULL COMMENT '原創(chuàng)文章',
  `user_visited` int(10) NOT NULL COMMENT '被訪問(wèn)量',
  `user_rank` int(5) NOT NULL COMMENT '用戶(hù)排名',
  `perms` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `nickname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用戶(hù)昵稱(chēng)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 116856 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
// 創(chuàng)建組合索引
ALTER TABLE `user` ADD INDEX idx_username_password_user_rank (`username`,`password`,`user_rank`)

我們?cè)趧?chuàng)建索引的時(shí)候，如果我們只選一列，那就叫單列索引，而如果我們選擇多列，那么就是組合索引。

在上表中我們創(chuàng)建了一個(gè)組合索引：idx_username_password_user_rank( username , password , user_rank )

一般我們組合索引字段數(shù)量不建議超過(guò)5個(gè)，而我們需要理解組合索引的最左匹配原則，我們就可以避免重復(fù)創(chuàng)建索引。

比如我們建立了（x,y,z）索引,我們就不需要建立（x）索引，（x，y）索引，因?yàn)槲覀兘ⅲ▁,y,z）索引就相當(dāng)于建立了（x）索引,（x,y）組合索引，（x,y,z）組合索引。  

最左匹配原則

什么是最左匹配原則？

然后我們?cè)诮⑺饕臅r(shí)候，還需要遵循一個(gè)規(guī)范，就是最左匹配原則，也就是帶頭大哥在不在的問(wèn)題。

在寫(xiě)查詢(xún)條件的時(shí)候，我們一定要遵循最左匹配原則，只要大哥不在，索引就會(huì)失效了。

比如：

我們創(chuàng)建了一個(gè)組合索引叫 idx_username_password_user_rank ( username , password , user_rank )

如果我們?cè)诓樵?xún)過(guò)程中，我們這樣寫(xiě)

select * from table where password= “xxx” and user_rank = 1

我們這個(gè)查詢(xún)語(yǔ)句是不會(huì)命中索引的，因?yàn)閹ь^大哥不在，所以索引失效。

也就是我們不能使用空中樓閣，我們把我們的 username當(dāng)成1樓，password當(dāng)成2樓，user_rank當(dāng)成3樓，1樓都不在，我們?cè)趺瓷?、3樓，這就是最左匹配原則，第一個(gè)根本就沒(méi)有匹配到，后面的就根本不用看了。  

為什么會(huì)有最左匹配原則？

要想知道為什么會(huì)在組合索引中有最左匹配原則，我們得先理解索引的本質(zhì)。

我們知道索引的本質(zhì)是一顆B+Tree，所以組合索引的本質(zhì)也是一顆B+Tree，不同的是組合索引的鍵值的數(shù)量不是1，而是>=2。

又因?yàn)闃?gòu)建一顆B+Tree只能根據(jù)一個(gè)值來(lái)確定索引關(guān)系，所以MySQL根據(jù)組合索引的最左字段來(lái)構(gòu)建B+Tree。

我們來(lái)舉一個(gè)例子。我們創(chuàng)建一個(gè)（a,b）的組合索引，這個(gè)組合索引會(huì)創(chuàng)建兩顆索引樹(shù)（a）,（a,b），而（a，b）的索引樹(shù)就是下面這個(gè)樣子的。

我們可以看到a的值是天然有序的1、 1、 2、 2、 3、 3，而b的值是沒(méi)有順序的1、 2、 1、 4、 1、 2。

但是我們?cè)赼值一定的情況下，b的值又是順序排列的，但是這種順序排列是相對(duì)于a來(lái)說(shuō)的。

所以最左匹配原則是因?yàn)?MySQL創(chuàng)建組合索引樹(shù)的規(guī)則是首先對(duì)組合索引最左邊第一個(gè)字段進(jìn)行排序，然后在第一個(gè)字段排序的基礎(chǔ)上，再對(duì)第二個(gè)字段進(jìn)行排序。

所以b=2這種查詢(xún)條件用不到這兩顆聯(lián)合索引樹(shù)。

實(shí)例說(shuō)明

針對(duì)聯(lián)合索引，是否遵循最左匹配原則；

建立一個(gè)組合索引

idx_username_password_user_rank(`username`,`password`,`user_rank`)

// 命中索引跟順序無(wú)關(guān)
explain SELECT * from `user` where username =  "liuxiangcheng" and password = "515239" and user_rank = 1
explain SELECT * from `user` where user_rank = 1 and username =  "liuxiangcheng" and password = "515239"  
explain SELECT * from `user` where user_rank = 1 and password = "515239" and username =  "liuxiangcheng"

結(jié)果：

去掉大哥，看看索引是否命中；

// 去掉大哥
explain SELECT * from `user` where   password = "515239" and user_rank = 1

去掉大哥之后，索引失效，全表掃描。

MySQL索引命中與失效

查看MySQL索引命中與失效具體見(jiàn)我另一篇博客：MySQL索引命中與失效

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論