準(zhǔn)備工作，下面的演示都是基于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)合索引與最左前綴原則

在平時(shí)開(kāi)發(fā)中，我們最常見(jiàn)的是單列索引（比如主鍵primary key），但在我們需要多條件查詢(xún)的時(shí)候，也會(huì)建立聯(lián)合索引。單列索引可以看成是特殊的聯(lián)合索引。比如我們?cè)趗ser表上面，給name和phone建立了一個(gè)聯(lián)合索引。

ALTER TABLE 可以用來(lái)創(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)，是一個(gè)單值索引：

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

相比于單值索引：

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

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

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

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

但是如果查詢(xún)條件沒(méi)有name，就不知道第一步應(yīng)該查哪個(gè)節(jié)點(diǎn)，因?yàn)榻⑺阉鳂?shù)的時(shí)候name是第一個(gè)比較因子，所以用不到索引。

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

假設(shè)我們的項(xiàng)目里面有兩個(gè)查詢(xún)很慢：

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

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

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

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

如果我們創(chuàng)建三個(gè)字段的索引index(a,b,c)，相當(dāng)于創(chuàng)建三個(gè)索引：index(a)、index(a,b)、index(a,b,c)。用 where b=? 和 where b=? and c=? 和where a=? and c=?是不能使用到索引的。因?yàn)椴荒懿挥玫谝粋€(gè)字段，不能中斷。

1.2 最左前綴原則

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

我們?cè)诮⒙?lián)合索引的時(shí)候，一定要把最常用的列放在最左邊。比如下面的三條語(yǔ)句，能用到聯(lián)合索引嗎？

1. 使用兩個(gè)字段，可以用到聯(lián)合索引（注：兩個(gè)字段的順序顛倒并不影響，因?yàn)槿灯ヅ鋾r(shí)mysql會(huì)優(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字段，無(wú)法使用索引，全表掃描：

EXPLAIN SELECT * FROM user_innodb WHERE name= '12345678910'

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

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

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

另外，不要在索引上做任何操作，因?yàn)榭赡軙?huì)導(dǎo)致索引失效，轉(zhuǎn)而全表掃描。

1.3 什么情況下會(huì)索引失效？

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

1. 使用函數(shù)（replace\SUBSTR\CONCAT\sum count avg）、表達(dá)式、計(jì)算（+ - * /）。因?yàn)楫?dāng)前值改變后就無(wú)法與索引存的值匹配上。

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

2. 使用范圍查詢(xún)(!=,<=>,in)會(huì)導(dǎo)致右邊列失效。因?yàn)槎鏄?shù)的查找是 = 查找，若是一個(gè)范圍的話(huà)無(wú)法繼續(xù)下探。
   • 最左列用范圍，該列也不會(huì)使用索引，全部索引列失效
   • 其余列用范圍，當(dāng)前列仍會(huì)使用索引，但右邊索引列失效

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

3. like以通配符開(kāi)頭（‘%abc…’），mysql索引失效會(huì)變成全表掃描操作。因?yàn)闊o(wú)法判斷%代表多少字符。
   • 方案一：like (‘abc%’)
   • 方案二：覆蓋索引

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

4. 字符串不加’ '索引失效。因?yàn)闀?huì)出現(xiàn)出現(xiàn)隱式轉(zhuǎn)換，相當(dāng)于給索引列做了操作。

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

5. 少用or，用它連接時(shí)很多情況下索引會(huì)失效

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

6. is null，is not null 無(wú)法使用索引

SELECT * FROM user_innodb where name is null；

==> 對(duì)這一部分內(nèi)容通過(guò)一首打油詩(shī)做個(gè)總結(jié)：

2.覆蓋索引

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

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

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

CREATE INDEX idx_name_phoneonuser_innodb(name,phone);

這三個(gè)查詢(xún)語(yǔ)句都用到了覆蓋索引：

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 * ，用不到覆蓋索引。很明顯，因?yàn)楦采w索引減少了IO次數(shù)，減少了數(shù)據(jù)的訪(fǎng)問(wèn)量，可以大大地提升查詢(xún)效率。

3.索引條件下推（ICP）

在講ICP前，我們?cè)賱?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)在我們要查詢(xún)所有姓wang，并且名字最后一個(gè)字是zi的員工，比如王胖子，王瘦子。查詢(xún)的SQL如下：

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

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

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

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

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

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

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

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

3.2 怎么開(kāi)啟ICP？

開(kāi)啟命令如下：

set optimizer_switch='index_condition_pushdown=on';

開(kāi)啟后再查看此時(shí)的執(zhí)行計(jì)劃，Using index condition：

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

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

最新評(píng)論