10張圖，搞懂索引為什么會(huì)失效？

MySQL數(shù)據(jù)是如何存儲(chǔ)的？

聚集索引

我們先建如下的一張表

CREATE?TABLE?`student`?(
??`id`?int(11)?NOT?NULL?AUTO_INCREMENT?COMMENT?'學(xué)號(hào)',
??`name`?varchar(10)?NOT?NULL?COMMENT?'學(xué)生姓名',
??`age`?int(11)?NOT?NULL?COMMENT?'學(xué)生年齡',
??PRIMARY?KEY?(`id`),
??KEY?`idx_name`?(`name`)
)?ENGINE=InnoDB;

插入如下sql

insert?into?student?(`name`,?`age`)?value('a',?10);
insert?into?student?(`name`,?`age`)?value('c',?12);
insert?into?student?(`name`,?`age`)?value('b',?9);
insert?into?student?(`name`,?`age`)?value('d',?15);
insert?into?student?(`name`,?`age`)?value('h',?17);
insert?into?student?(`name`,?`age`)?value('l',?13);
insert?into?student?(`name`,?`age`)?value('k',?12);
insert?into?student?(`name`,?`age`)?value('x',?9);

數(shù)據(jù)如下mysql是按照頁(yè)來(lái)存儲(chǔ)數(shù)據(jù)的，每個(gè)頁(yè)的大小為16k。

在MySQL中可以通過(guò)執(zhí)行如下語(yǔ)句，看到一個(gè)頁(yè)的大小

show?global?status?like?'innodb_page_size'

結(jié)果為16384，即16kb

在InnoDB存儲(chǔ)引擎中，是以主鍵為索引來(lái)組織數(shù)據(jù)的。記錄在頁(yè)中按照主鍵從小到大的順序以單鏈表的形式連接在一起。

可能有小伙伴會(huì)問(wèn)，如果建表的時(shí)候，沒(méi)有指定主鍵呢？

如果在創(chuàng)建表時(shí)沒(méi)有顯示的定義主鍵，則InnoDB存儲(chǔ)引擎會(huì)按如下方式選擇或創(chuàng)建主鍵。

1. 首先判斷表中是否有非空的唯一索引，如果有，則該列即為主鍵。如果有多個(gè)非空唯一索引時(shí)，InnoDB存儲(chǔ)引擎將選擇建表時(shí)第一個(gè)定義的非空唯一索引作為主鍵
2. 如果不符合上述條件，InnoDB存儲(chǔ)引擎自動(dòng)創(chuàng)建一個(gè)6字節(jié)大小的指針作為索引

頁(yè)和頁(yè)之間以雙鏈表的形式連接在一起。并且下一個(gè)數(shù)據(jù)頁(yè)中用戶(hù)記錄的主鍵值必須大于上一個(gè)數(shù)據(jù)頁(yè)中用戶(hù)記錄的主鍵值

假設(shè)一個(gè)頁(yè)只能存放3條數(shù)據(jù)，則數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)如下。可以看到我們想查詢(xún)一個(gè)數(shù)據(jù)或者插入一條數(shù)據(jù)的時(shí)候，需要從最開(kāi)始的頁(yè)開(kāi)始，依次遍歷每個(gè)頁(yè)的鏈表，效率并不高。我們可以給這頁(yè)做一個(gè)目錄，保存主鍵和頁(yè)號(hào)的映射關(guān)系，根據(jù)二分法就能快速找到數(shù)據(jù)所在的頁(yè)。但這樣做的前提是這個(gè)映射關(guān)系需要保存到連續(xù)的空間，如數(shù)組。如果這樣做會(huì)有如下幾個(gè)問(wèn)題

1. 隨著數(shù)據(jù)的增多，目錄所需要的連續(xù)空間越來(lái)越大，并不現(xiàn)實(shí)
2. 當(dāng)有一個(gè)頁(yè)的數(shù)據(jù)全被刪除了，則相應(yīng)的目錄項(xiàng)也要?jiǎng)h除，它后面的目錄項(xiàng)都要向前移動(dòng)，成本太高

我們可以把目錄數(shù)據(jù)放在和用戶(hù)數(shù)據(jù)類(lèi)似的結(jié)構(gòu)中，如下所示。目錄項(xiàng)有2個(gè)列，主鍵和頁(yè)號(hào)。數(shù)據(jù)很多時(shí)，一個(gè)目錄項(xiàng)肯定很多，畢竟一個(gè)頁(yè)的大小為16k，我們可以對(duì)數(shù)據(jù)建立多個(gè)目錄項(xiàng)目，在目錄項(xiàng)的基礎(chǔ)上再建目錄項(xiàng)，如下圖所示

圖片來(lái)自《MySQL 是怎樣運(yùn)行的：從根兒上理解 MySQL》

這其實(shí)就是一顆B+樹(shù)，也是一個(gè)聚集索引，即數(shù)據(jù)和索引在一塊。葉子節(jié)點(diǎn)保存所有的列值

以 InnoDB 的一個(gè)整數(shù)字段索引為例，這個(gè) N 差不多是 1200。這棵樹(shù)高是 4 的時(shí)候，就可以存 1200 的 3 次方個(gè)值，這已經(jīng)17 億了。考慮到樹(shù)根的數(shù)據(jù)塊總是在內(nèi)存中的，一個(gè) 10 億行的表上一個(gè)整數(shù)字段的索引，查找一個(gè)值最多只需要訪問(wèn) 3次磁盤(pán)。其實(shí)，樹(shù)的第二層也有很大概率在內(nèi)存中，那么訪問(wèn)磁盤(pán)的平均次數(shù)就更少了。《MySQL實(shí)戰(zhàn)45講》

非聚集索引

聚集索引和非聚集索引非常類(lèi)似，區(qū)別如下

聚集索引葉子節(jié)點(diǎn)的值為所有的列值非聚集索引葉子節(jié)點(diǎn)的值為索引列+主鍵

當(dāng)我們查詢(xún)name為h的用戶(hù)信息時(shí)（學(xué)號(hào)，姓名，年齡），因?yàn)閚ame上建了索引，先從name非聚集索引上，找到對(duì)應(yīng)的主鍵id，然后根據(jù)主鍵id從聚集索引上找到對(duì)應(yīng)的記錄。

從非聚集索引上找到對(duì)應(yīng)的主鍵值然后到聚集索引上查找對(duì)應(yīng)記錄的過(guò)程為回表

聯(lián)合索引/索引覆蓋

假設(shè)teacher表定義如下，在name和age列上建立聯(lián)合索引

CREATE?TABLE?`teacher`?(
??`id`?int(11)?NOT?NULL?AUTO_INCREMENT?COMMENT?'教師編號(hào)',
??`name`?varchar(10)?NOT?NULL?COMMENT?'教師姓名',
??`age`?int(11)?NOT?NULL?COMMENT?'教師年齡',
??`ismale`?tinyint(3)?NOT?NULL?COMMENT?'是否男性',
??PRIMARY?KEY?(`id`),
??KEY?`idx_name_age`?(`name`,?`age`)
)?ENGINE=InnoDB;

插入如下sql

insert?into?teacher?(`name`,?`age`,?`ismale`)?value('aa',?10,?1);
insert?into?teacher?(`name`,?`age`,?`ismale`)?value('dd',?12,?0);
insert?into?teacher?(`name`,?`age`,?`ismale`)?value('cb',?9,?1);
insert?into?teacher?(`name`,?`age`,?`ismale`)?value('cb',?15,?1);
insert?into?teacher?(`name`,?`age`,?`ismale`)?value('bc',?17,?0);
insert?into?teacher?(`name`,?`age`,?`ismale`)?value('bb',?15,?1);
insert?into?teacher?(`name`,?`age`,?`ismale`)?value('dd',?15,?1);
insert?into?teacher?(`name`,?`age`,?`ismale`)?value('dd',?12,?0);

對(duì)name和age列建立聯(lián)合索引

目錄頁(yè)由name列，age列，頁(yè)號(hào)這三部分組成。目錄會(huì)先按照name列進(jìn)行排序，當(dāng)name列相同的時(shí)候才對(duì)age列進(jìn)行排序。

數(shù)據(jù)頁(yè)由name列，age列，主鍵值這三部分組成。同樣的，數(shù)據(jù)頁(yè)會(huì)先按照name列進(jìn)行排序，當(dāng)name列相同的時(shí)候才對(duì)age列進(jìn)行排序。

當(dāng)執(zhí)行如下語(yǔ)句的時(shí)候，會(huì)有回表的過(guò)程

select?*?from?student?where?name?=?'aa';

當(dāng)執(zhí)行如下語(yǔ)句的時(shí)候，沒(méi)有回表的過(guò)程

select?name,?age?from?student?where?name?=?'aa';

為什么不需要回表呢？

因?yàn)閕dx_name_age索引的葉子節(jié)點(diǎn)存的值為主鍵值，name值和age值，所以從idx_name_age索引上就能獲取到所需要的列值，不需要回表，即索引覆蓋

仔細(xì)看一下聯(lián)合索引這個(gè)圖，你就基本上能明白為什么不滿(mǎn)足最左前綴原則的索引會(huì)失效？

索引下推

當(dāng)執(zhí)行如下語(yǔ)句的時(shí)候

select?*?from?student?where?name?like?'張%'?and?age?=?10?and?ismale?=?1;

在5.6版本之前的執(zhí)行過(guò)程如下，先從idx_name_age索引上找到對(duì)應(yīng)的主鍵值，然后回表找到對(duì)應(yīng)的行，判斷其他字段的值是否滿(mǎn)足條件圖片來(lái)自《MySQL實(shí)戰(zhàn)45講》

在5.6引入了索引下推優(yōu)化，可以在遍歷索引的過(guò)程中，對(duì)索引中包含的字段做判斷，直接過(guò)濾掉不滿(mǎn)足條件的數(shù)據(jù)，減少回表次數(shù)，如下圖圖片來(lái)自《MySQL實(shí)戰(zhàn)45講》

最左前綴原則

加速查詢(xún)

主要針對(duì)組合索引，滿(mǎn)足如下2個(gè)條件即可滿(mǎn)足左前綴原則

1. 需要查詢(xún)的列和組合索引的列順序一致
2. 查詢(xún)不要跨列

構(gòu)造數(shù)據(jù)如下，其中在name，address，country上建了聯(lián)合索引

CREATE?TABLE?`people`?(
??`name`?varchar(50)?NOT?NULL,
??`address`?varchar(50)?NOT?NULL,
??`country`?varchar(50)?NOT?NULL,
??KEY?`idx_name_addr_country`?(`name`,`address`,`country`)
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;

舉幾個(gè)例子，下面涉及到一些explain相關(guān)的知識(shí)，后面單開(kāi)一篇長(zhǎng)文來(lái)做介紹

例子一

explain?select?*?from?people?where?name?=?"jack"
and?address?=?"beijing"?and?country?=?"china"

type為ref，key_len為456=（50*3+2）*3，聯(lián)合索引的所有列都使用了

例子二

explain?select?*?from?people?where?name?=?"jack"

type為ref，key_len為152=50*3+2，聯(lián)合索引只使用了name列

例子三

explain?select?*?from?people?where?address?=?"beijing"

type為index，表明查詢(xún)的時(shí)候?qū)φ麄€(gè)索引進(jìn)行了掃描，并沒(méi)有加速查找。

假設(shè)有如下的聯(lián)合索引 key idx_a_b_c(a,b,c)

如果你仔細(xì)看了前面聯(lián)合索引是如何存儲(chǔ)的，那你一定能看懂是否使用索引的介紹

目錄頁(yè)是按照 a b c 列的順序依次遞增排序的。先按照a列排序，如果a列相同，再按照b列排序，如果b列相同，才按照c列排序

所以查詢(xún)列值a b c，則這個(gè)排序規(guī)則能用到，即會(huì)走索引。如果只查列值b，并不能用到這個(gè)排序規(guī)則，所以得遍歷所有的記錄

加速排序

最左前綴原則不僅用在查詢(xún)中，還能用在排序中。MySQL中，有兩種方式生成有序結(jié)果集：

1. 通過(guò)有序索引順序掃描直接返回有序數(shù)據(jù)
2. Filesort排序，對(duì)返回的數(shù)據(jù)進(jìn)行排序

因?yàn)樗饕慕Y(jié)構(gòu)是B+樹(shù)，索引中的數(shù)據(jù)是按照一定順序進(jìn)行排列的，所以在排序查詢(xún)中如果能利用索引，就能避免額外的排序操作。EXPLAIN分析查詢(xún)時(shí)，Extra顯示為Using index。

所有不是通過(guò)索引直接返回排序結(jié)果的操作都是Filesort排序，也就是說(shuō)進(jìn)行了額外的排序操作。EXPLAIN分析查詢(xún)時(shí)，Extra顯示為Using filesort，當(dāng)出現(xiàn)Using filesort時(shí)對(duì)性能損耗較大，所以要盡量避免Using filesort

還是先舉2個(gè)例子，然后總結(jié)

explain?select?*?from?people?order?by?name

Extra列只有Using index，即根據(jù)索引順序進(jìn)行掃描

explain?select?*?from?people?order?by?address

Extra列有Using filesort

總結(jié)：假如說(shuō)有如下聯(lián)合索引，key idx_a_b_c(a,b,c)

order by 能使用索引排序

order?by?a
order?by?a,b
order?by?a,b,c
order?by?a?desc,?b?desc,?c?desc
where?a?=?const?order?by?b,c
where?a?=?const?and?b?=?const?order?by?c
where?a?=?const?and?b?>?const?order?by?b,c

order by 不能使用索引進(jìn)行排序

order?by?b
order?by?c
order?by?b,?c
order?by?a?asc,?b?desc,?c?desc?//排序不一致
where?g?=?const?order?by?b,c?//丟失a索引
where?a?=?const?order?by?c?//丟失b索引
where?a?=?const?order?by?a,d?//d不是索引的一部分
where?a?in?(...)?order?by?b,c?//范圍查詢(xún)

這個(gè)原因就不用我解釋了把，相信你一定看懂了

聯(lián)合索引的好處

索引覆蓋，減少了很多回表的操作，提高了查詢(xún)的效率

索引下推，索引列越多，通過(guò)索引篩選出的數(shù)據(jù)越少。有1000W條數(shù)據(jù)的表，有如下sql:select * from table where col1=1 and col2=2 and col3=3,假設(shè)假設(shè)每個(gè)條件可以篩選出10%的數(shù)據(jù)，如果只有單值索引，那么通過(guò)該索引能篩選出1000W10%=100w條數(shù)據(jù)，然后再回表從100w條數(shù)據(jù)中找到符合col2=2 and col3= 3的數(shù)據(jù)；如果是聯(lián)合索引，通過(guò)索引篩選出1000w*10%*10% *10%=1w，效率提升可想而知！

索引為什么會(huì)失效？

當(dāng)別人問(wèn)我索引在什么條件下會(huì)失效時(shí)，我能背出一大堆規(guī)則

1. 不要在索引列上進(jìn)行運(yùn)算或使用函數(shù)
2. 前導(dǎo)模糊查詢(xún)不會(huì)使用索引，例如 like %李
3. 負(fù)向條件索引不會(huì)使用索引，建議用in。負(fù)向條件有：!=、<>、not in、not exists、not like 等

索引是按照一定規(guī)則排好序的，如果對(duì)索引列使用函數(shù)，或者 like % 李，具體的值都不知道，它怎么在B+樹(shù)上加速查詢(xún)？

喜歡我可以給我設(shè)為星標(biāo)哦

好文章，我“在看”