準備工作，下面的演示都是基于user_innodb表：

DROP TABLE IF EXISTS `user_innodb`;
CREATE TABLE `user_innodb` (
  `id` bigint(64) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `gender` tinyint(1) NOT NULL,
  `phone` varchar(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1.聯(lián)合索引與最左前綴原則

在平時開發(fā)中，我們最常見的是單列索引（比如主鍵primary key），但在我們需要多條件查詢的時候，也會建立聯(lián)合索引。單列索引可以看成是特殊的聯(lián)合索引。比如我們在user表上面，給name和phone建立了一個聯(lián)合索引。

ALTER TABLE 可以用來創(chuàng)建索引，包括普通索引、UNIQUE索引或PRIMARY KEY索引：

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

ALTER TABLE user_innodb add INDEX comidx_name_phone(name,phone); -- 創(chuàng)建聯(lián)合索引

1.1 聯(lián)合索引是怎么組織的？

下圖是B+Tree的索引結(jié)構(gòu)，是一個單值索引：

我們可以看到，所有非葉子結(jié)點的構(gòu)成都是由兩部分組成，索引值+指針，而單值索引說的就是在索引值這里就只有一個值（比如id），而聯(lián)合索引在索引值可能會有多個值（比如name和phone）

相比于單值索引：

1. 聯(lián)合索引在 B+Tree 中是復(fù)合的數(shù)據(jù)結(jié)構(gòu)
2. 由于 B+樹本身是有序的，所以聯(lián)合索引是從左到右的順序來建立搜索樹的（name在左邊，phone在右邊）。從上圖可以看出來，name是有序的，phone是無序的。當name相等的時候，phone才是有序的。
3. 當存儲引擎是InnoDB時，葉節(jié)點存儲的是數(shù)據(jù)/主鍵

問題一：聯(lián)合索引是怎么查找數(shù)據(jù)的？

比如，我們使用 where name=‘Bob’ and phone = ‘132xx’ 去查詢數(shù)據(jù)的時候

1. B+Tree 會優(yōu)先比較 name 來確定下一步應(yīng)該搜索的方向，往左還是往右
2. 如果 name相同的時候再比較 phone

但是如果查詢條件沒有name，就不知道第一步應(yīng)該查哪個節(jié)點，因為建立搜索樹的時候name是第一個比較因子，所以用不到索引。

問題二：聯(lián)合索引與單值索引什么關(guān)系？

假設(shè)我們的項目里面有兩個查詢很慢：

SELECT * FROM user_innodb WHERE name= ?;
SELECT * FROM user_innodb WHERE name= ? AND phone=?;

按照我們的想法，一個查詢創(chuàng)建一個索引，所以我們針對這兩條SQL創(chuàng)建了兩個索引，這種做法覺得正確嗎？

CREATE INDEX idx_name on user_innodb(name); 
CREATE INDEX idx_name_phoneonuser_innodb(name,phone);

當我們創(chuàng)建一個聯(lián)合索引的時候，用左邊的字段name去查詢的時候，也能用到索引，所以單獨為name創(chuàng)建一個索引完全沒必要。相當于建立了兩個聯(lián)合索引(name)、(name,phone)。

如果我們創(chuàng)建三個字段的索引index(a,b,c)，相當于創(chuàng)建三個索引：index(a)、index(a,b)、index(a,b,c)。用 where b=? 和 where b=? and c=? 和where a=? and c=?是不能使用到索引的。因為不能不用第一個字段，不能中斷。

1.2 最左前綴原則

因為聯(lián)合索引中包含了多個字段，所以不能像單值索引那樣直接使用就行。那需要遵守什么規(guī)則呢？
答：最左前綴原則：帶頭大哥不能死，中間兄弟不能斷。

我們在建立聯(lián)合索引的時候，一定要把最常用的列放在最左邊。比如下面的三條語句，能用到聯(lián)合索引嗎？

1. 使用兩個字段，可以用到聯(lián)合索引（注：兩個字段的順序顛倒并不影響，因為全值匹配時mysql會優(yōu)化字段順序）

EXPLAIN SELECT * FROM user_innodb WHERE name= '張三' AND phone='12345678910'

2. 使用左邊的name字段，可以用到聯(lián)合索引：

EXPLAIN SELECT * FROM user_innodb WHERE name= '張三'

3. 使用右邊的phone字段，無法使用索引，全表掃描：

EXPLAIN SELECT * FROM user_innodb WHERE name= '12345678910'

從聯(lián)合索引的結(jié)構(gòu)中，我們看到了索引是已經(jīng)排好序的，那我們?nèi)缭谧袷刈钭笄熬Y原則的前提下，order by時用到索引(避免 filesort)？

• 使用形式一：order by 索引最前列 ==> 整體有序。
• 使用形式二：where + order by索引列 ==> 局部有序（為了最左前綴原則，盡量order by索引列（用where 保證中間不斷開）)

再說一句，分組（group by）的實質(zhì)是先排序后分組，原則類似order by。where 高于 having，where中能限定但條件不要去having中限定

另外，不要在索引上做任何操作，因為可能會導(dǎo)致索引失效，轉(zhuǎn)而全表掃描。

1.3 什么情況下會索引失效？

當索引列出現(xiàn)以下六種操作時常常出現(xiàn)索引失效：

1. 使用函數(shù)（replace\SUBSTR\CONCAT\sum count avg）、表達式、計算（+ - * /）。因為當前值改變后就無法與索引存的值匹配上。

SELECT * FROM user_innodb where left(name, 3)='張三'；-- left函數(shù)是一個字符串函數(shù)，它返回具有指定長度的字符串的左邊部分

2. 使用范圍查詢(!=,<=>,in)會導(dǎo)致右邊列失效。因為二叉樹的查找是 = 查找，若是一個范圍的話無法繼續(xù)下探。
   • 最左列用范圍，該列也不會使用索引，全部索引列失效
   • 其余列用范圍，當前列仍會使用索引，但右邊索引列失效

SELECT * FROM user_innodb where name='張三' and age > 22；

3. like以通配符開頭（‘%abc…’），mysql索引失效會變成全表掃描操作。因為無法判斷%代表多少字符。
   • 方案一：like (‘abc%’)
   • 方案二：覆蓋索引

SELECT * FROM user_innodb where name like '%三'；

4. 字符串不加’ '索引失效。因為會出現(xiàn)出現(xiàn)隱式轉(zhuǎn)換，相當于給索引列做了操作。

SELECT * FROM user_innodb where name = 007；-- "007"從字符串變成了數(shù)字007

5. 少用or，用它連接時很多情況下索引會失效

SELECT * FROM user_innodb where name = '張三' or name = '李四'；

6. is null，is not null 無法使用索引

SELECT * FROM user_innodb where name is null；

==> 對這一部分內(nèi)容通過一首打油詩做個總結(jié)：

2.覆蓋索引

回表：非主鍵索引，我們先通過索引找到主鍵索引的鍵值，再通過主鍵值查出索引里面沒有的數(shù)據(jù)，它比基于主鍵索引的查詢多掃描了一棵索引樹，這個過程就叫回表。例如：select * from user_innodb where name = ‘青山’;

在輔助索引里面，不管是單列索引還是聯(lián)合索引，如果select的數(shù)據(jù)列只用從索引中就能夠取得，不必從數(shù)據(jù)區(qū)中讀取，這時候使用的索引就叫做覆蓋索引，這樣就避免了回表。

我們先來創(chuàng)建一個聯(lián)合索引：

CREATE INDEX idx_name_phoneonuser_innodb(name,phone);

這三個查詢語句都用到了覆蓋索引：

EXPLAIN SELECT name,phone FROM user_innodb WHERE name='青山' AND phone='13666666666';
EXPLAIN SELECT name FROMuser_innodb WHERE name='青山' AND phone='13666666666'; 
EXPLAIN SELECT phone FROM user_innodb WHERE name='青山' AND phone='13666666666';

Extra里面值為“Using index”代表使用了覆蓋索引。另外，select * ，用不到覆蓋索引。很明顯，因為覆蓋索引減少了IO次數(shù)，減少了數(shù)據(jù)的訪問量，可以大大地提升查詢效率。

3.索引條件下推（ICP）

在講ICP前，我們再創(chuàng)建一張數(shù)據(jù)表（員工表），并且在last_name和first_name上面創(chuàng)建聯(lián)合索引。

CREATE TABLE `employees`( 
    `emp_no`int(11)NOTNULL,
    `birth_date`date NULL,
    `first_name`varchar(14)NOTNULL,
    `last_name`varchar(16)NOTNULL, 
    `gender`enum('M','F')NOTNULL, 
    `hire_date`date NULL, 
    PRIMARYKEY(`emp_no`)
)ENGINE=InnoDBDEFAULTCHARSET=latin1;
alter table employees add index idx_lastname_firstname(last_name,first_name); -- 創(chuàng)建聯(lián)合索引
INSERT INTO `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(1, NULL,'698','liu','F',NULL); 
INSERT INTO `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(2, NULL,'d99','zheng','F',NULL); 
INSERT INTO `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(3, NULL,'e08','huang','F',NULL); 
INSERT INTO `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(4, NULL,'59d','lu','F',NULL); 
INSERT INTO` employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(5, NULL,'0dc','yu','F',NULL); 
INSERT INTO` employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(6, NULL,'989','wang','F',NULL); 
INSERT INTO` employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(7, NULL,'e38','wang','F',NULL); 
INSERT INTO` employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(8, NULL,'0zi','wang','F',NULL); 
INSERT INTO` employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(9, NULL,'dc9','xie','F',NULL); 
INSERT INTO` employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(10, NULL,'5ba','zhou','F',NULL);

3.1 ICP是干什么用的？

現(xiàn)在我們要查詢所有姓wang，并且名字最后一個字是zi的員工，比如王胖子，王瘦子。查詢的SQL如下：

select * from employees where last_name='wang' and first_name LIKE '%zi';

這條SQL有兩種執(zhí)行方式：

根據(jù)聯(lián)合索引查出所有姓wang的二級索引數(shù)據(jù)，然后回表，到主鍵索引上查詢?nèi)糠蠗l件的數(shù)據(jù)（3 條數(shù)據(jù)）。然后返回給 Server 層，在 Server 層過濾出名字以zi結(jié)尾的員工。

注：索引的比較是在存儲引擎進行的，數(shù)據(jù)記錄的比較是在Server層進行的。而當first_name的條件不能用于索引過濾時，Server 層不會把first_name的條件傳遞給存儲引擎，所以讀取了兩條沒有必要的記錄。如果將數(shù)據(jù)規(guī)模擴大，比如滿足last_name='wang’的記錄有100000條，就會有99999條沒有必要讀取的記錄。

根據(jù)聯(lián)合索引查出所有姓wang的二級索引數(shù)據(jù)（3個索引），然后從二級索引中篩選出first_name以zi結(jié)尾的索引（1個索引），然后再回表，到主鍵索引上查詢?nèi)糠蠗l件的數(shù)據(jù)（1條數(shù)據(jù)），返回給Server 層。

很明顯，第二種方式到主鍵索引上查詢的數(shù)據(jù)更少，但mysql在沒開啟ICP前使用的都是第一種。

explain select * from employees where last_name='wang' and first_name LIKE '%zi';

Using Where代表從存儲引擎取回的數(shù)據(jù)不全部滿足條件，需要在Server 層過濾。先用last_name 條件進行索引范圍掃描，讀取數(shù)據(jù)表記錄，然后進行比較，檢查是否符合first_name LIKE ‘%zi’ 的條件。此時3條中只有1條符合條件。

3.2 怎么開啟ICP？

開啟命令如下：

set optimizer_switch='index_condition_pushdown=on';

開啟后再查看此時的執(zhí)行計劃，Using index condition：

把first_name LIKE '%zi’下推給存儲引擎后，只會從數(shù)據(jù)表讀取所需的1條記錄。索引條件下推（IndexConditionPushdown），5.6以后完善的功能。只適用于二級索引。ICP 的目標是減少訪問表的完整行的讀數(shù)量從而減少 I/O 操作。

到此這篇關(guān)于MySql索引原理之聯(lián)合索引與最左前綴原則、覆蓋索引及索引條件下推詳解的文章就介紹到這了,更多相關(guān)mysql最左前綴原則內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論