說明

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

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 '用戶id',
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用戶名',
  `password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用戶密碼',
  `create_essay` int(5) NOT NULL COMMENT '原創(chuàng)文章',
  `user_visited` int(10) NOT NULL COMMENT '被訪問量',
  `user_rank` int(5) NOT NULL COMMENT '用戶排名',
  `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 '用戶昵稱',
  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`)

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

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

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

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

最左匹配原則

什么是最左匹配原則？

然后我們在建立索引的時(shí)候，還需要遵循一個(gè)規(guī)范，就是最左匹配原則，也就是帶頭大哥在不在的問題。

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

比如：

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

如果我們在查詢過程中，我們這樣寫

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

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

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

為什么會有最左匹配原則？

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

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

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

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

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

但是我們在a值一定的情況下，b的值又是順序排列的，但是這種順序排列是相對于a來說的。

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

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

實(shí)例說明

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

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

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

// 命中索引跟順序無關(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索引命中與失效具體見我另一篇博客：MySQL索引命中與失效

總結(jié)

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

最新評論