InnoDB中索引分類

我們都知道InnoDB索引結(jié)構(gòu)是B+樹組織的，但是根據(jù)數(shù)據(jù)存儲形式不同可以分為兩類，分別是聚簇索引與二級索引。

ps：有些同學(xué)還聽過非聚簇索引和輔助索引，其他它們都是一個(gè)意思，本文統(tǒng)一稱為二級索引。

聚簇索引

聚簇索引默認(rèn)是由主鍵構(gòu)成，如果沒有定義主鍵，InnoDB會選擇非空的唯一索引代替，還是沒有的話，InnoDB會隱式的定義一個(gè)主鍵來作為聚簇索引。

其實(shí)聚簇索引的本質(zhì)就是主鍵索引。

因?yàn)槊繌埍碇荒軗碛幸粋€(gè)主鍵字段，所以每張表只有一個(gè)聚簇索引。

另外聚簇索引還有一個(gè)特點(diǎn)，表的數(shù)據(jù)和主鍵是一起存儲的，它的葉子節(jié)點(diǎn)存放的是整張表的行數(shù)據(jù)（樹的最后一層），葉子節(jié)點(diǎn)又稱為數(shù)據(jù)頁。

很簡單記住一句話：找到了索引就找到了行數(shù)據(jù)，那么這個(gè)索引就是聚簇索引。

如果這里無法理解的話，可以去補(bǔ)下阿星的前兩篇文章

• InnoDB原理篇：聊聊數(shù)據(jù)頁變成索引這件事

• InnoDB原理篇：為什么使用索引會變快?

二級索引

知道了聚簇索引，再來看看二級索引是什么，簡單概括，除主鍵索引以外的索引，都是二級索引，像我們平時(shí)建立的聯(lián)合索引、前綴索引、唯一索引等。

二級索引的葉子節(jié)點(diǎn)存儲的是索引值+主鍵id。

所以二級索引與聚簇索引的區(qū)別在于葉子節(jié)點(diǎn)是否存放整行記錄。

也就意味著，僅僅靠二級索引無法拿到完整行數(shù)據(jù)，只能拿到id信息。

那二級索引應(yīng)該如何拿到完整行數(shù)據(jù)呢？

索引的查詢

假設(shè)，我們有一個(gè)主鍵列為id的表，表中有字段k，k上有索引。這個(gè)表的建表語句是：

create?table?T( id?int?primary?key,? k?int?not?null,? name?varchar(16), index?(k))engine=InnoDB;

表中有5條記錄(id,k)，值分別為(100,1)、(200,2)、(300,3)、(500,5)、(600,6)，此時(shí)會有兩棵樹，分別是主鍵id的聚簇索引和字段k的二級索引，簡化的樹結(jié)構(gòu)圖如下

回表

我們執(zhí)行一條主鍵查詢語句select * from T where id = 100，只需要搜索id聚簇索引樹就能查詢整行數(shù)據(jù)。

接著再執(zhí)行一條select * from T where k = 1，此時(shí)要搜索k的二級索引樹，具體過程如下

• 在 k 索引樹上找 k = 1的記錄，取得 id = 100

• 再到聚簇索引樹查 id = 100 對應(yīng)的行數(shù)據(jù)

• 回到 k 索引樹取下一個(gè)值 k = 2，不滿足條件，循環(huán)結(jié)束

上圖中，回到聚簇索引樹搜索的過程，我們稱為回表。

也就是說，基于二級索引的查詢需要多掃描一棵聚簇索引樹，因此在開發(fā)中盡量使用主鍵查詢。

索引覆蓋

可是有時(shí)候我們確實(shí)需要使用?????????二級索引查詢，有沒有辦法避免回表呢？

辦法是有的，但需要結(jié)合業(yè)務(wù)場景來使用，比如本次查詢只返回id值，查詢語句可以這樣寫select id from T where k = 1，過程如下

• 在 k 索引樹上找 k = 1的記錄，取得 id = 100

• 返回 id 值

• 回到 k 索引樹取下一個(gè)值 k = 2，不滿足條件，循環(huán)結(jié)束

在這個(gè)查詢中，索引k已經(jīng)覆蓋了我們的查詢需求，不需要回表，這個(gè)操作稱為覆蓋索引。

由于覆蓋索引可以減少樹的搜索次數(shù)，顯著提升查詢性能，所以使用覆蓋索引是一個(gè)常用的性能優(yōu)化手段。

假設(shè)現(xiàn)在有一個(gè)高頻的業(yè)務(wù)場景，根據(jù)k查詢，返回name，我們可以把k索引變更成k與name的聯(lián)合索引。

這個(gè)聯(lián)合索引就有意義了，它可以在高頻場景用到覆蓋索引，不再需要回表查整行記錄，減少語句的執(zhí)行時(shí)間。

ps：設(shè)計(jì)索引時(shí)，請遵守最左原則匹配

索引下推

此時(shí)我們再建立一個(gè)name與k的聯(lián)合索引。

執(zhí)行select k from T where name like '張%' and k = 2語句。

首先會在name與k樹中用張找到第一條件滿足條件的記錄id = 100，然后從id = 100開始遍歷一個(gè)個(gè)回表，到主鍵索引上找出行記錄，再對比k字段值，是不是十分操蛋。

可以看到總共回表了6次

不過在MySQL 5.6版本引入的索引下推，可以在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數(shù)。

總共回表0次。

小結(jié)

本篇文章到這里就結(jié)束了，今天和大家聊了聚簇索引、二級索引、回表、覆蓋索引、索引下推等知識，可以看到，在滿足語句需求的情況下，盡量少地訪問資源是數(shù)據(jù)庫設(shè)計(jì)的重要原則之一，由于篇幅有限，很多內(nèi)容還沒展開，后續(xù)阿星會和大家聊聊如何設(shè)計(jì)索引。

有道無術(shù)，術(shù)可成；有術(shù)無道，止于術(shù)

歡迎大家關(guān)注Java之道公眾號

好文章，我在看??

總結(jié)

以上是生活随笔為你收集整理的InnoDB原理篇：如何用好索引的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。