前言

開門見山，直接上圖，下面的思維導(dǎo)圖即是現(xiàn)在要講的內(nèi)容，可以先有個(gè)印象～

• 常見索引類型(實(shí)現(xiàn)層面)
• 索引種類(應(yīng)用層面)
• 聚簇索引與非聚簇索引
• 覆蓋索引
• 最佳索引使用策略

1.常見索引類型(實(shí)現(xiàn)層面)

首先不談Mysql怎么實(shí)現(xiàn)索引的,先馬后炮一下,如果讓我們來(lái)設(shè)計(jì)數(shù)據(jù)庫(kù)的索引，該怎么設(shè)計(jì)？

我們首先思考一下索引到底想達(dá)到什么效果？其實(shí)就是想能夠?qū)崿F(xiàn)快速查找數(shù)據(jù)的策略，所以索引的實(shí)現(xiàn)本質(zhì)上就是一個(gè)查找算法。

但是跟普通的查找有所不同，因?yàn)槲覀兊臄?shù)據(jù)有一下特征：

1.存儲(chǔ)的數(shù)據(jù)是非常非常多的

2.并且還不斷的動(dòng)態(tài)變化

所以實(shí)現(xiàn)索引時(shí)需要考慮到這兩個(gè)特點(diǎn)。我們需要找一個(gè)最合適的數(shù)據(jù)結(jié)構(gòu)算法來(lái)實(shí)現(xiàn)查找功能。

下面一起看下常見的查找策略，如下圖：

由于前面說(shuō)的兩個(gè)特點(diǎn)我們首先排除靜態(tài)查找的算法。

至于查找樹，我們有二叉樹和多叉樹兩種選擇：

二叉樹：如果選擇二叉樹的話，由于我們的數(shù)據(jù)量龐大，二叉樹的深度會(huì)變得非常大，我們的索引樹會(huì)變成參天大樹，每次查詢會(huì)導(dǎo)致很多磁盤IO。

多叉樹：多叉樹解決了了樹的深度大的問題，那么我們到底選擇B樹還是B+樹呢？

B樹 摘自維基百科 zh.wikipedia.org/wiki/B%2B樹

B+樹 摘自維基百科 zh.wikipedia.org/wiki/B%2B樹

從上面圖可知B+樹的葉子節(jié)點(diǎn)存放了所有的索引值，并且葉子結(jié)點(diǎn)之間以鏈表的形式相互關(guān)聯(lián)，所以我們只需從最左的鏈表遍歷的話即可查找所有的值，最常見的用途就是范圍查找，而B樹則不滿足這范圍查找，又或者說(shuō)實(shí)現(xiàn)特別復(fù)雜,所以Mysql最終選擇了使用B+樹實(shí)現(xiàn)這一功能。

1.1 B-Tree 索引(B+樹)

先說(shuō)明一下，雖然叫在Mysql官方叫做B-Tree索引，但采用的是B+樹數(shù)據(jù)結(jié)構(gòu)。

B-tree索引能夠加快訪問數(shù)據(jù)的速度，不需要進(jìn)行全表掃描，而是從索引樹的根節(jié)點(diǎn)層層往下搜索，在根節(jié)點(diǎn)存放了索引值和指向下一個(gè)節(jié)點(diǎn)的指針。

下面看下單列索引的數(shù)據(jù)怎么組織的。

create table User(
`name` varchar(50) not null,
`uid` int(4) not null,
`gender` int(2) not null,
 key(`uid`)
);

上面User 表給uid列創(chuàng)建了一個(gè)索引，那么往表里插入uid(96~102)的時(shí)候存儲(chǔ)引擎是怎么管理索引的呢？看下面的索引樹

1.在葉子節(jié)點(diǎn)存放所有的索引值，非葉子節(jié)點(diǎn)值是為了更快定位包含目標(biāo)值的葉子節(jié)點(diǎn)

2.葉子節(jié)點(diǎn)的值是有序的

3.葉子節(jié)點(diǎn)之間以鏈表形式關(guān)聯(lián)

下面在看一下多列(聯(lián)合)索引的數(shù)據(jù)怎么組織的。

create table User(
`name` varchar(50) not null,
`uid` int(4) not null,
`gender` int(2) not null,
 key(`uid`,`name`)
);

給User 表創(chuàng)建了聯(lián)合索引 key(uid,name) 這種情況下他的索引樹是如下圖所示。

特點(diǎn)跟單列索引一樣，不同之處在于他的排序，如果第一個(gè)字段相同時(shí)會(huì)按第二個(gè)索引字段排序

如何通過B-tree快速查找數(shù)據(jù)？

對(duì)于InnoDb 存儲(chǔ)引擎的B-tree索引，會(huì)按一下步驟通過索引找到行數(shù)據(jù)

• 如果使用了聚簇索引(主鍵)，則葉子節(jié)點(diǎn)上就包含行數(shù)據(jù)，可直接返回
• 如果使用了非聚簇索引(普通索引),則在葉子節(jié)點(diǎn)存了主鍵，再根據(jù)主鍵查詢一次上面的聚簇索引，最后返回?cái)?shù)據(jù)

對(duì)于MyISAM 存儲(chǔ)引擎的B-tree索引，會(huì)按一下步驟通過索引找到行數(shù)據(jù)

• 在MyISAM 的索引樹的葉子節(jié)點(diǎn)上除了索引值之外即沒存儲(chǔ)主鍵，也沒存儲(chǔ)行數(shù)據(jù)，而是存了指向行數(shù)據(jù)的指針，根據(jù)這個(gè)指針在從表文件查詢數(shù)據(jù)。

1.2 Hash 索引(哈希表)

哈希索引是基于哈希表來(lái)實(shí)現(xiàn)的，只有精確匹配所有的所有列才能生效。

也就是說(shuō)假設(shè)有個(gè)hash索引 key (col1,col2) 那么每次只有 col1和col2兩個(gè)字段都用才能夠生效。因?yàn)樯蒱ash索引的時(shí)候是根據(jù)一個(gè)hash函數(shù)對(duì)所有的索引列取hash值來(lái)實(shí)現(xiàn)的。

如下方圖，有個(gè)hash索引key(name)

當(dāng)我們執(zhí)行 mysql> select * from User where name='張三'； 時(shí)怎么利用hash索引快速查找的？

1. 第一步，計(jì)算出hash值，hash(張三) = 1287
2. 第二步，定位行號(hào)，比如key=1287 對(duì)應(yīng)的行號(hào)為3
3. 第三步，找到指定行并且比較name列值是否為張三做個(gè)校驗(yàn)

2.常見索引種類(應(yīng)用層面)

主鍵索引

create table User(
`name` varchar(50) not null,
`uid` int(4) not null,
`gender` int(2) not null,
 primary key(`uid`)
);

主鍵索引是唯一的，通常以表的ID設(shè)置為主鍵索引,一個(gè)表只能有一個(gè)主鍵索引，這是他跟唯一索引的區(qū)別。

唯一索引

create table User(
`name` varchar(50) not null,
`uid` int(4) not null,
`gender` int(2) not null,
 unique key(`name`)
);

唯一索引主要用于業(yè)務(wù)上的唯一約束，他跟主鍵索引的區(qū)別是，一個(gè)表可以有多個(gè)唯一索引

單列索引

create table User(
`name` varchar(50) not null,
`uid` int(4) not null,
`gender` int(2) not null,
 key(`name`)
);

以某一個(gè)字段為索引

聯(lián)合索引

create table User(
`name` varchar(50) not null,
`uid` int(4) not null,
`gender` int(2) not null,
 key(`name`,`uid`)
);

兩個(gè)或兩個(gè)以上字段聯(lián)合組成一個(gè)索引。使用時(shí)需要注意滿足最左匹配原則！

還有其他不常用的就不介紹了～

3.聚簇索引與非聚簇索引

什么是聚簇索引？

聚簇索引指的是他的 索引和行數(shù)據(jù) 在一起存儲(chǔ)。也就是在一顆B+樹的葉子結(jié)點(diǎn)上存儲(chǔ)的不僅是他的索引值，還有對(duì)應(yīng)的某一行的數(shù)據(jù)。待會(huì)兒看圖便知。

聚簇索引不是一種索引，而是一種數(shù)據(jù)存儲(chǔ)組織方式 ?。?！

crreate table test(
 col1 int not null,
 col2 int not null,
 PRIMARY KEY(col1),
 KEY(col2)
);

如上所示，表test 由兩個(gè)索引，分別是主鍵 col1 和 普通索引 col2。那么這倆索引跟聚簇非聚簇有啥關(guān)系呢？

會(huì)生成一個(gè)聚簇索引和一個(gè)非聚簇索引(二級(jí)索引)，也就是說(shuō)會(huì)組織兩個(gè)索引樹。主鍵索引會(huì)生成聚簇索引的樹 以及以col2為索引的非聚簇索引的樹。

InnoDb 將通過主鍵來(lái)實(shí)現(xiàn)聚簇索引 ，如果沒有主鍵則會(huì)選選一個(gè)唯一非空索引來(lái)實(shí)現(xiàn)。如果沒有唯一非空索引則會(huì)隱式生成一個(gè)主鍵。

下面看下聚簇索引和非聚簇索引在索引樹上數(shù)據(jù)是怎么分布的，圖片摘自《高性能Nysql》

下圖是聚簇索引的數(shù)據(jù)組織方式。 col1為主鍵索引的聚簇索引樹

索引列是主鍵 col1

可以看出葉子結(jié)點(diǎn)除了存儲(chǔ)索引值 列col1 (3~99~4700)值 之外還存儲(chǔ)了其他列的值，如列col2 (92~8~13),如果還有別的列的話也會(huì)存儲(chǔ)，或者換句話說(shuō)聚簇索引樹 在葉子節(jié)點(diǎn)上存儲(chǔ)某個(gè)索引值對(duì)應(yīng)的一行數(shù)據(jù)。

下圖是非聚簇索引(二級(jí)索引)的數(shù)據(jù)組織方式。

索引列是 col2

與聚簇索引不同的是非聚簇索引在索引樹葉子節(jié)點(diǎn)上除了索引值之外只存了主鍵值。而聚簇索引則存了一行數(shù)據(jù)。

假如有一條sql 語(yǔ)句 select * from test where col2=93;

上面這條語(yǔ)句會(huì)經(jīng)歷兩次從索引樹查找過程

1.第一步從非聚簇索引的索引樹上找到包含col2=93的葉子節(jié)點(diǎn)，并定位到行的主鍵 3

2.第二步 根據(jù)主鍵 3 在從聚簇索引定位包含 主鍵=3的葉子節(jié)點(diǎn)并返回全部行數(shù)據(jù)。

以上說(shuō)的都是基于InnoDb存儲(chǔ)引擎的,MyISAM是不支持聚簇索引的，因?yàn)樗臄?shù)據(jù)文件和索引文件是相互獨(dú)立存儲(chǔ)的  MyISAM存儲(chǔ)引擎的索引樹的葉子節(jié)點(diǎn)不會(huì)寸主鍵值，而存一個(gè)指向?qū)?yīng)行的地址或者說(shuō)是指針，然后再?gòu)谋頂?shù)據(jù)文件里去找，如下面圖所示。

 結(jié)論：

• 聚簇索引:

通常由主鍵或者非空唯一索引實(shí)現(xiàn)的，葉子節(jié)點(diǎn)存儲(chǔ)了一整行數(shù)據(jù)

• 非聚簇索引：

又稱二級(jí)索引，就是我們常用的普通索引，葉子節(jié)點(diǎn)存了索引值和主鍵值，在根據(jù)主鍵從聚簇索引查

4.覆蓋索引

覆蓋索引就是指索引包含了所有需要查詢的字段。

create table User(
`name` varchar(50) not null,
`uid` int(4) not null,
`gender` int(2) not null,
 key(`uid`,`name`)
);

假如表 User有三個(gè)字段 User (name,uid,gender),且有個(gè)聯(lián)合索引 key(name,uid)那么

執(zhí)行如下面這條sql查詢時(shí)就用到了 覆蓋索引。

select name,uid from User where name in ('a','b') and uid >= 98 and uid =100 ;

上面這條sql語(yǔ)句使用了聯(lián)合索引 key(name,uid),并且只需查找 name,uid兩個(gè)字段，所以使用了覆蓋索引。覆蓋索引有什么好處呢？先看一下下面這個(gè)圖

上面這個(gè)圖就是 聯(lián)合索引key(name,uid) 所對(duì)應(yīng)的索引樹，從圖中可以看出，如果我們只需查詢(name,uid)兩個(gè)字段的話，從索引樹就能得到我們需要查的數(shù)據(jù)。不需要找到索引值之后再?gòu)谋頂?shù)據(jù)文件定位對(duì)應(yīng)的行數(shù)據(jù)了。

覆蓋索引好處

1.避免了對(duì)主鍵索引(聚簇)的二次查詢

2.由于不需要回表查詢(從表數(shù)據(jù)文件)所以大大提升了Mysql緩存的負(fù)載

總之大大提升了讀取數(shù)據(jù)的性能

5.最佳索引使用策略

最后在講講使用索引過程中的避坑指南

獨(dú)立的列

獨(dú)立的列不是指單列索引，而是指索引列不能是表達(dá)式的一部分或者是函數(shù)的一部分。

select * FROM test where col1 + 1 =100; // 不能是表達(dá)式一部分
select * FROM test where ABS(col1) =100; // 不能是函數(shù)一部分

最左匹配原則

假如有個(gè)聯(lián)合索引 key (col1,col2)。那么以下查詢是索引無(wú)效的

select * from test where col2 = 3;
select * from test where col1 like '%3';

對(duì)于最左匹配原則，大家想一下B+樹的葉子節(jié)點(diǎn)的關(guān)聯(lián)就差不多知道為啥需要最左匹配原則了，因?yàn)锽+的葉子結(jié)點(diǎn)，從左到右以鏈表的形式關(guān)聯(lián)的，索引我們查詢的時(shí)候要么范圍查詢，要么有明確的左邊一個(gè)開始的索引值，不能跳過或者不明確如 like '%XYZ'這種查詢。

索引值不能是null值

單列索引有null值會(huì)導(dǎo)致索引無(wú)效

多列索引只要有個(gè)列有null值會(huì)導(dǎo)致索引無(wú)效

使用聚簇索引和覆蓋索引大大提升讀取性能

因?yàn)榫鄞厮饕透采w索引的索引樹上就有了需要的字段，所以不需要回表文件查詢，所以提升了查詢速度

使用短索引

如果很長(zhǎng)的字符串進(jìn)行查詢，只需匹配一個(gè)前綴長(zhǎng)度，這樣能夠節(jié)省大量索引空間

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。