1.前言

這次小編準(zhǔn)備用兩篇文章來(lái)和大家分享下mysql innodb的索引: mysql的基礎(chǔ)知識(shí) 和 基于索引的sql優(yōu)化 。

2. 什么是索引？

定義：索引是數(shù)據(jù)庫(kù)中用于快速查找數(shù)據(jù)的機(jī)制，本質(zhì)是某種數(shù)據(jù)結(jié)構(gòu)。它存儲(chǔ)著指向數(shù)據(jù)的指針，從而幫助數(shù)據(jù)庫(kù)跳過(guò)不必要的行，直接定位到目標(biāo)數(shù)據(jù)，減少掃描時(shí)間。

比如我們看<<三國(guó)演義>>劉備三顧茅廬這章，如果沒(méi)有目錄，我們就需要一篇一篇的翻書(shū)，要翻很久，因?yàn)椴恢涝跁?shū)的哪一頁(yè)；但是如果有目錄，我們是不是先在目錄里面找到這章的目錄，這章的目錄對(duì)應(yīng)著正文的書(shū)頁(yè)，我們一下就可以翻到對(duì)應(yīng)的書(shū)頁(yè)，找到對(duì)應(yīng)的章節(jié)。目錄就類(lèi)似于mysql的索引。

3. 為什么需要索引？

• 加速查詢(xún)：索引可以顯著減少數(shù)據(jù)庫(kù)檢索所需的時(shí)間。沒(méi)有索引時(shí)，數(shù)據(jù)庫(kù)會(huì)執(zhí)行全表掃描（讀取每一行），而索引讓數(shù)據(jù)庫(kù)只需要掃描部分?jǐn)?shù)據(jù)。
• 降低 I/O 負(fù)載：索引通過(guò)減少物理讀取的次數(shù)，從而減少 I/O 操作。在大型數(shù)據(jù)集上尤其明顯，特別是對(duì)于復(fù)雜查詢(xún)或多表連接（JOIN）的場(chǎng)景。
• 常見(jiàn)應(yīng)用場(chǎng)景：索引在以下場(chǎng)景中最有用：

WHERE 子句中的條件過(guò)濾。

ORDER BY 語(yǔ)句進(jìn)行排序時(shí)。

GROUP BY 語(yǔ)句進(jìn)行分組時(shí)。

多表 JOIN 操作時(shí)匹配外鍵或其他相關(guān)列。

4. 索引的類(lèi)型

• 主鍵索引：主鍵是表中唯一標(biāo)識(shí)記錄的字段，通常自動(dòng)創(chuàng)建索引。InnoDB 存儲(chǔ)引擎會(huì)將主鍵索引作為聚簇索引（Clustered Index），即數(shù)據(jù)的物理存儲(chǔ)順序和主鍵索引順序一致。
• 唯一索引：保證索引列中的所有值唯一性，類(lèi)似于主鍵索引，但可以應(yīng)用于非主鍵字段。
• 普通索引：沒(méi)有唯一性要求的索引，用于加速查詢(xún)的非主鍵列。普通索引僅保證加速查找，沒(méi)有其他約束。
• 聯(lián)合索引（復(fù)合索引）：一個(gè)索引包含多個(gè)列，按照指定順序進(jìn)行索引。根據(jù)最左前綴原則，只要查詢(xún)的條件從左開(kāi)始匹配列的順序，索引就可以生效。

5. 索引的結(jié)構(gòu)

• B-Tree 索引：MySQL 中最常見(jiàn)的索引結(jié)構(gòu)是 B-Tree，特別是 InnoDB 存儲(chǔ)引擎。B-Tree 索引通過(guò)一種平衡樹(shù)結(jié)構(gòu)，使得數(shù)據(jù)查找時(shí)間復(fù)雜度為 O(log n)。每個(gè)節(jié)點(diǎn)存儲(chǔ)鍵值，并且有指向下層節(jié)點(diǎn)的指針。查詢(xún)通過(guò)層次結(jié)構(gòu)逐級(jí)縮小搜索范圍，從而快速定位目標(biāo)數(shù)據(jù)。
• 哈希索引：哈希索引基于哈希函數(shù)，將鍵值映射為固定大小的哈希值。其特點(diǎn)是查詢(xún)效率極高（O(1) 時(shí)間復(fù)雜度），但只適用于精確匹配的查詢(xún)。不支持范圍查詢(xún)（例如 <、> 操作），因此哈希索引不適合大多數(shù)通用場(chǎng)景。

這里強(qiáng)調(diào)一下：innodb只能創(chuàng)建B-Tree 索引，不支持哈希索引。即使創(chuàng)建的時(shí)候選擇的是哈希，實(shí)際創(chuàng)建之后的結(jié)果也是B-Tree，有興趣的同學(xué)可以試試，小編這里就不截圖了。

下面，小編用六條數(shù)據(jù)模擬下索引的結(jié)構(gòu)。首先看下表結(jié)構(gòu):

圖片

然后表里面的數(shù)據(jù):

圖片

然后看下主鍵索引的結(jié)構(gòu),這里是小編根據(jù)索引的結(jié)構(gòu)自己畫(huà)的:

圖片

根據(jù)這個(gè)B+ 樹(shù)索引結(jié)構(gòu)圖，小編簡(jiǎn)單的說(shuō)下：

• 綠色: 綠色框框表示頁(yè)數(shù),之前已經(jīng)講過(guò),數(shù)據(jù)庫(kù)的數(shù)據(jù)是以頁(yè)的方式存儲(chǔ)。
• 紅色: 紅色框框表示是否是索引還是數(shù)據(jù)行,1:索引;0:數(shù)據(jù)。
• B+樹(shù)的葉子節(jié)點(diǎn)存放的是完整的數(shù)據(jù)，非葉子節(jié)點(diǎn)存放的是索引數(shù)據(jù)。
• B+樹(shù)的子節(jié)點(diǎn)可以有多個(gè)，這里是只用4條數(shù)據(jù)模擬，多了難得畫(huà)。
• 葉子節(jié)點(diǎn)的數(shù)據(jù)是有序的，根據(jù)主鍵id由小到大存儲(chǔ)，并且行與行，頁(yè)與頁(yè)之間是用雙向鏈表連接的。

二級(jí)索引

除了主鍵外，我們會(huì)根據(jù)查詢(xún)的字段，也去建立相應(yīng)的字段索引。小編這里就不畫(huà)圖了，二級(jí)索引的葉子節(jié)點(diǎn)放的是對(duì)應(yīng)的主鍵索引值。

回表

指的是 MySQL 在通過(guò)二級(jí)索引查找到符合條件的記錄后，還需要回到主鍵索引中去讀取額外的列數(shù)據(jù)。因?yàn)樗饕话糠至械男畔ⅲ绻樵?xún)中請(qǐng)求了索引中未包含的列，數(shù)據(jù)庫(kù)就需要從主鍵索引中再次讀取完整的行數(shù)據(jù)。

比如user表中，我給name添加索引，我需要查詢(xún)name=小九這行數(shù)據(jù)的信息，因?yàn)槎?jí)索引是沒(méi)有完整的數(shù)據(jù)，所以mysql需要去主鍵索引里面找到對(duì)應(yīng)的完整數(shù)據(jù)返回。

覆蓋索引

指的是當(dāng)一個(gè)索引完全包含了查詢(xún)所需的所有列時(shí)，MySQL 不需要再回表，而是直接從索引中就可以返回結(jié)果。這種情況就稱(chēng)為覆蓋索引。

比如user表中，我給name添加索引，我需要查詢(xún)name=小九這條數(shù)據(jù)的id，因?yàn)閕d在葉子節(jié)點(diǎn)已經(jīng)有了，直接就返回了結(jié)果，不需要去主鍵索引再次查詢(xún)。

覆蓋索引的優(yōu)勢(shì)：

性能提升：避免了回表，降低了 I/O 和查詢(xún)時(shí)間。減少表掃描：索引的存儲(chǔ)比表的數(shù)據(jù)量小得多，索引掃描比全表掃描快。

6. 總結(jié)

• 索引是提升查詢(xún)性能的關(guān)鍵工具，選擇合適的索引類(lèi)型和合理優(yōu)化可以極大提高數(shù)據(jù)庫(kù)效率。
• 理解索引的類(lèi)型和底層原理能夠幫助開(kāi)發(fā)者在構(gòu)建數(shù)據(jù)庫(kù)時(shí)做出明智的決策。