同學你好，很高興你看到我的文章。今天我們聊聊：之前我去面試月薪30K中級開發，某公司CTO問我的問題。

適合人群：想對MySQL原理有深入進階，面試想獲得更高薪資的同學。閱讀本篇需要你具備MySQL基礎知識。

前言

導讀：

既然你點進來了，捂住手機試問你自己，會不會。如果會了就可以就此跳過，不要浪費時間，如果不會那麼你就花點心思好好看看吧。

文章內容長，但是很透徹，如果你壓根不想看，也請拜拜。既然花時間看了就要努力消化掉，這些你要回答出來月薪30穩穩的。

如果看了對你有用，那麼請把文章轉發，收藏並關注我。若沒用，那我和你道歉了，不好意思，浪費你的時間。

本篇文章會講到如下內容

MyIsAm與InnoDB資料儲存結構區別

MyIsAm與InnoDB聚集索引實現原理

聚集索引和非聚集索引原理，區別

覆蓋索引索引原理

問：你可以說說什麼是索引或者讓你說說對索引的理解嗎？

千萬不要像下面這麼回答，這麼回答和脫褲子放P沒啥區別。

答：索引就像書的目錄一樣，可以加快資料的檢索速度……，難道人家建立索引會不清楚這一點？其實面試官問你的是平衡樹，說白了就是B樹和B+樹，Hash這些資料結構。

應該這麼答：我們在平時建立資料表的時候，在某些關係型資料庫中，資料表只能有一個自增主鍵AUTO_INCREMENT。

我對錶的的理解是這樣的：

如果一個表沒有加索引，資料按順序一條一條的在磁碟上按插入順序儲存著。

如果一張表一旦加了索引，比如加了自增主鍵，那麼表中的資料在磁碟中就不是按順序排列的，而是變成了樹狀結構，也就是我們常說的平衡樹，換句話說就是整個表都變成了一個索引樹，也就是所謂的聚簇索引。

這也就說明白了為什麼一個表只能有一個自增主鍵以及只能有一個聚簇索引了，因為自增主鍵的作用根兒上就是根據一定演算法把表的資料按照一定格式轉換成平衡樹存放在磁碟的。所謂聚簇索引，就是指：主索引檔案和資料檔案為同一份檔案，目前我所瞭解的聚簇索引只是在Innodb儲存引擎中存在。

在建立表的時候，INNODB型別的表儲存的是一個檔案，字尾為。frm。這個檔案包含了資料、表結構、索引樹。在資料結構實現方式中B+Tree的葉子節點中data就是資料本身，key為主鍵，如果是二級索引的話，data便會指向對應的主索引。

聚集索引中表記錄的排列順序和索引的排列順序保持一致，所以查詢效率相當快。只要找到第一個索引記錄的值，其餘的連續性的記錄也一定是連續存放的。

上圖就是帶有主鍵的表（聚集索引）的結構圖。圖畫的不星史論是很好，將就著看。其中樹的所有節點（底部除外）的資料都是由主鍵欄位中的資料構成，也就是通男實常我們指定主鍵的id欄位。最下面部分是鈣類法真正表中的資料。 假如我們執行一個SQL語句：select * from user where id=1256；

首先根據索引定位到1256這個值所在的葉結點，然後再透過葉結點取到id等於1256的資料行。 這裡不講解平衡樹的執行細節，但是從上圖能看出，樹一共有三層，從根節點至葉節點只需要經過三次查詢就能得到結果。如下圖

假如一張表有一億條資料 ，需要查詢其中某一條資料，按照常規邏輯，一條一條地去匹配的話， 最壞的情況下需要匹配一億次才能得到結果，用大O標記法就是O（n）最壞時間複雜度，這是無法接受的，而且這一億條資料顯然不能一次性讀入記憶體供程式使用，因此，這一億次匹配在不經快取最佳化的情況下就是一億次IO開銷，以現在磁碟的IO能力和CPU的運算能力，有可能需要幾個月才能得出結果 。

如果把這張錶轉換成平衡樹結構（一棵非常茂盛和節點非常多的樹），假設這棵樹有10層，那麼只需要10次IO開銷就能查詢到所需要的資料， 速度以指數級別提升，用大O標記法就是O（log n），n是記錄總樹，底數是樹的分叉數，結果就是樹的層次數。換言之，查詢次數是以樹的分叉數為底，記錄總數的對數。

用程式來對數表示就是Math。Log（100000000，10），100000000是記錄數，10是樹的分叉數（真實環境下分叉數遠不止10），結果就是查詢次數，這裡的結果從億降到了個位數。因此，利用索引會使資料庫查詢有驚人的效能提升。

當然任何事物都是有兩面的，索引能讓資料庫查詢資料的速度提升， 同時對錶的寫入資料速度就會下降，原因很簡單的。 因為平衡樹這個結構必須一直維持在一個正確的狀態，增刪改資料都會改變平衡樹各節點中的索引資料內容，破壞樹結構。

因此，在每次資料改變時，DBMS必須去重新梳理樹（索引）的結構以確保它的正確，這會帶來不小的效能開銷，也就是為什麼索引會給查詢以外的操作帶來副作用的原因。

MySQL中InnoDB 的主鍵一定是聚集索引。如果沒有定義主鍵，聚集索引可能是第一個不允許為 null 的唯一索引，也有可能是 row id。（

從根兒上理解MySQL：10年老DBA告訴你1條記錄在表中是如何儲存的

具體可以看看這裡）

目前我對聚簇索引瞭解就這些了，下面我說說我對非聚簇索引的理解。

其實他們倆差不多，同樣都是使用平衡樹作為索引的資料結構。

區別就是：非聚集索引這棵樹中所有的節點都來自於表中二級索引欄位。假如給user表的name欄位加上索引 ， 那麼索引就是由name欄位中的值構成，在資料改變時， DBMS需要一直維護索引結構的正確性。如果給表中多個欄位加上索引 ，那麼就會出現多個獨立的索引結構，每個索引（非聚集索引）互相之間不存在關聯。

每次給欄位建一個新索引，欄位中的資料就會被複制一份出來，用於生成索引。因此，給表新增索引，會增加表的體積，佔用磁碟儲存空間。

非聚集索引和聚集索引的區別在於，透過聚集索引可以查到需要查詢的資料，而透過非聚集索引可以查到記錄對應的主鍵值 ，再使用主鍵的值透過聚集索引查詢到需要的資料，如下圖

聚集和非聚集索引不管以任何方式查詢表，最終都會利用主鍵透過聚集索引來定位到資料，聚集索引（主鍵）是通往真實資料所在的唯一路徑。

不過，有一種例外可以不使用聚集索引就能查詢出所需要的資料這種非主流的方法稱之為「覆蓋索引」查詢，也就是平時所說的複合索引或者多欄位索引查詢。

剛才說過了，每次給欄位加一次索引，所對應的內容就會被複製出來一份。如果為一個索引指定兩個欄位，那麼這個兩個欄位的內容都會被同步至索引之中。

先看下面這個SQL語句：

//建立索引

createindex index_birthday on user_info（birthday）；

//查詢生日在1991年11月1日出生使用者的使用者名稱

select user_name from user_info where birthday =‘1991-11-1’

這句SQL語句的執行過程如下：

首先，透過非聚集索引index_birthday查詢birthday等於1991-11-1的所有記錄的主鍵ID值

然後，透過得到的主鍵ID值執行聚集索引查詢，找到主鍵ID值對就的真實資料（資料行）儲存的位置

最後， 從得到的真實資料中取得user_name欄位的值返回， 也就是取得最終的結果

我們把birthday欄位上的索引改成雙欄位的覆蓋索引

createindex index_birthday_and_user_name on user_info（birthday， user_name）；

透過非聚集索引index_birthday_and_user_name查詢birthday等於1991-11-1的葉節點的內容，然而，葉節點中除了有user_name表主鍵ID的值以外，user_name欄位的值也在裡面，因此不需要透過主鍵ID值的查詢資料行的真實所在，直接取得葉節點中user_name的值返回即可。透過這種覆蓋索引直接查詢的方式，可以省略不使用覆蓋索引查詢的後面兩個步驟，大大地提高了查詢效能，如下圖：

覆蓋索引可以完美的解決二級索引回表查詢問題。但是前提是一定得注意查詢時候索引的最左側匹配原則。

使用聚集索引的查詢效率要比非聚集索引的效率要高，但是如果需要頻繁去改變聚集索引的值，寫入效能並不高，因為需要移動對應資料的物理位置。

聚集索引兩點關鍵資訊：根據主鍵值建立了 B+ 樹結構，每個葉子節點包含了整行資料。

注意點：mysql5。7。18新特性count（*）統計表資料數量，不在需要回表了，大大提升了統計速度在查詢的時候雖然優先走非聚集索引，但是它不需要回表操作，它只需要統計非聚集索引樹上的值即可！

MySQL原理：count（*）為什麼這麼慢，帶你重新認識count的方方面面

這一篇有詳細講解。

總的來說，在MySQL中MyIsAm使用的是B+tree索引結構，葉節點的data僅僅存放的是指向資料記錄的一個地址，在MyIsAm中主鍵索引和輔助索引沒有任何區別，只是主鍵索引要求key是唯一的，而輔助索引的key可以重複。

在InnoDB中使用的也是B+tree索引結構，但是在實現方式來說和MyIsAm完全不通，MyIsAm的資料檔案和索引檔案以及表定義檔案都是分開的，MyIsAm中索引檔案只是儲存一個指向具體資料的一個指標。但是在innodb中，Btree可以分為兩種：主鍵索引和二級索引（也叫輔助索引）。

innodb中主鍵索引一定是聚集索引，表資料檔案本身就是一個B+tree結構，這個樹的葉節點儲存了每一條記錄的完整資料，這個葉節點的key就是資料的主鍵，innodb中二級索引儲存的是索引列值以及指向主鍵的指標，所以我們使用覆蓋索引最佳化其實說白了就是對MySQL的innodb索引加速的。

MyISAM引擎中leaf node儲存的內容：

主鍵索引 ：僅僅儲存行指標；二級索引：儲存的也僅僅是行指標；

InnoDB引擎中leaf node儲存的內容

主鍵索引 ：聚集索引儲存完整的資料（整行資料）二級索引：儲存索引列值+主鍵資訊

結語

這篇文章，我們長篇大論mysql中的聚集索引和非聚集索引詳細原理。

希望大家都好好看看，正真的理解它。

下篇我們主要說說二叉樹、B樹B-、B+、B*之間的關係，如有幫助請收藏關注我。

本文為百家號首發，王者小哆啦原創文章，圖片部分來自於網路，如有侵權請與我聯絡。