​01為啥BAT大廠，在資料庫上都喜歡深入的問索引呢？

一線大廠，是很多人夢寐以求的盛典天堂。因為存在的無限的可能，可以幫你實現自己的遠大抱負。大平臺機會、視野、格局往往都比小廠多很多。但隨之而來也是那高挑的技術門檻需等你邁過。好事物大家都喜歡，但畢竟僧多粥少。外加任務有難度，如果你沒過硬的本領，那很難踏入平臺，領會一覽眾山小的風采。不知你心裡有沒有小九九？

大廠產品大多數都成型很久，資料庫裡面儲存的資料都以海量計算，如何在這種規模下的資料中做到快速篩選呢？那就需要你來答。

大家思路肯定和我一樣，話不多說，加索引再說！索引為的就是提高資料的檢索效率，進而減少請求的響應時間。

這時，有內涵的人可能會反問你啦？

那你說說索引有哪些型別？索引底層實現是什麼結構？B+Tree的優點？聚簇索引和非聚簇的區別？索引一次讀讀取多少資料合適？為什麼說索引會降低插入、刪除、修改等維護任務的速度？

這一套組合拳，可能虐的你是體無完膚。讓人招教不住，心理一萬個xxx省略。送他一個微笑，然後再尷尬而不失優雅的離去。

大家可能都知道查詢慢了加索引，那為啥加？在哪些欄位上加？以及索引的資料結構特點。索引優化、優點啥的都比較模糊或者不知道。

今天將是對索引來一次靈魂的拷問，在進一步對索引優化、常見大廠面試問題、SQL優化等內容進行分享。

這是個大工程，大家得關注再看。

深，那就得深出高度。MOG！太深啦

02用索引，那你得知道索引是什麼？

百度百科定義：索引是資料表中一列或多列的值進行排序的一種資料結構

故此，索引本質就是資料結構。這也是為什麼每次資料表建立索引都需要設定在列欄位上的原因。那常見資料結構有哪些？

常見資料結構大致可分為三大類，如下所示

• 線性表：順序表、連結串列、棧和佇列；

• 樹結構：二叉樹，堆、線索二叉樹、紅黑樹、B-Tree等；

• 圖儲存結構

但在資料庫中常用資料結構為B+Tree、Hash索引。

對於此，有人可能覺得有了Hash和那麼多樹結構（紅黑樹、B樹、完全平衡二叉（AVL）樹、B+樹），為啥Mysql唯獨喜歡B+樹？

請聽如下分解：

首先上場的是頑固不變Hash索引，這Hash索引又是什麼？

雜湊索引(hash index)基於雜湊表實現，只有精確匹配索引所有列的查詢才能生效額。切記！切記！切記！

雜湊的思路很簡單，以鍵-值（ key-value ）儲存資料的結構，對於待查詢每一行的資料值,用一個雜湊函式把資料值換算成一個確定的位置即 key，位置就是雜湊碼，並且不同鍵值的行計算出來的雜湊碼也不一樣。然後在 value 上存放每個資料行的指標。

對這樣的索引結構，執行如下sql語句的過程是什麼呢？

select * from nezha where name='lianhua'複製程式碼

MySQL首先計算'lianhua'的雜湊值，並使用該值尋找對應的記錄指標。然後根據指標尋找對應的資料，最後一步是比較讀取的值是否為'lianhua', 從而來確保就是要查詢的行。

那如果改變為範圍性查詢就會存在問題。還記得上面的切記嗎？因為它不支援範圍匹配，只支援等值匹配。例如：

select * from nezha where name like '%lianhua'複製程式碼

那像Hash這種等值查詢還有哪些場景？

Hash故名思議體現的就是（key-value）結構。所以像 Redis、Memcached 及其他一些 NoSQL 引擎（如 Memory）。

那有沒有既能快速查詢，又可以支援範圍型查詢呢？

自然有，有序陣列在等值查詢和範圍查詢場景中的效能就都非常OK，足以滿足你的口味。

那它就好的沒天理啦？不，世上沒得十全十美的！

有序陣列索引只適用於靜態儲存引擎，因為陣列的空間必須是連續的，這就造成陣列在記憶體中分配空間時必須找到一塊連續的記憶體空間。所以新增、刪除、修改資料時就會改變它的結構。

一下掉入無底洞，這在業務場景上怎樣使用？

靜態資料簡單點可以理解為不會在變化的資料，那你就可以用於歷史歸檔性的業務。比如你去年酷狗歌單、每上月的支付記錄等，這類不會再修改的資料。

接下來上場的是層次不齊的樹結構

樹結構基礎就是普通二叉樹，其它樹結構都是基於它演進產生。二叉樹會根據元素值的大小來建立樹形結構。所以它是有序的，並支援範圍查詢。具體可檢視資料結構相關書籍。

但普通二叉樹，有個問題，就是當元素是遞增或遞減時，它就會退化為線性表。

為了解決這個問題，就出現了我們的完全平衡二叉樹。可為何資料庫沒選擇它呢？

資料庫操作都是在記憶體裡面完成的，但最終還是要落地到磁碟。如果資料多了，樹會變得很高。然而查詢資料時，那都是從磁碟裡面把資料讀取出來放入到記憶體中。這樣I/O操作成本就會隨著樹的高度而增加。這也是常說完全平衡二叉樹具有高瘦特點。

好像女孩子都喜歡這樣的吧！

一般為節約成本，很多公司伺服器採用的還是機械硬碟，這樣一次千萬級別的查詢差不多就要10秒，這還不算網路傳輸、業務處理、CPU的執行時間，一但彙總那誰頂得住？

那這怎麼解決呢？不可能一直讓讓它變高吧! 可使用B-Tree。

B-Tree的特徵就是矮胖，也稱為多叉樹，就是在樹的同一高度上開闢多個分叉來容納元素。從而樹在橫向上面變寬了。這樣減少了磁碟I/O的查詢查詢次數，從而提升了效率。

B-Tree的特點簡寫：

• 每個節點中的元素（關鍵字）從小到大排列。

• 每個節點都儲存有資料

那為什麼最終資料庫選擇了B+Tree，而不是B-Tree呢？

B+Tree自然保持了B-Tree的矮胖特徵，但它還做了升級的處理。就是讓葉子節點儲存資料，而非葉子節點儲存關鍵字即可，並且會有指標指向下一個葉子節點。這樣的好處是為了提高範圍查詢的效率。找到資料後直接根據指標向後讀取即可，而B-Tree就不行，當它讀取下一個資料，還需要再一次的進行索引樹的查詢。

B+Tree特點：

• 所有的非葉子節點只儲存關鍵字資訊

• 只有葉子節點儲存資料

• 所有葉子節點之間都有一個鏈指標

小結：最終MySQL選用B+Tree作為索引，從而提高檢索索引時的磁碟IO效率，並且提高範圍查詢的效率，整個B+樹裡的元素也是有序的。因為B+Tree預設就是按照主鍵索引來構建的樹結構。那你說呢？

03索引是怎麼構建的？

開發過程中，MySQL都首先B+Tree。在MySQL下還擁有Hash索引，也就是它擁有2大索引型別。具體選擇用什麼，可在建立表時進行選擇。

那這索引到底是怎麼建立出來的？

那還得分情況而定，分為以下2種

建表建索引

建立表的時候，先把索引欄位建立好。如：

 create table nezha(id int unsigned   AUTO_INCREMENT PRIMARY KEY, phone int not null,name varchar(16),index (phone))engine=InnoDB;複製程式碼

當新增資料時，資料庫就會自動先去建立好索引結構，然後建立資料。最終在落地到磁碟上。

先建表，後添索引

這種情況需要注意，因為先建表，那可能你資料表已經擁有了大量資料，這時候你在新增索引，那你的整個資料庫肯定會阻塞，因為資料庫需要根據表中資料建立索引，這都是由資料庫後臺執行緒來完成。

這也是為什麼線上資料庫不要輕易變動索引，需根據使用者低峰時間來操作。所以索引建立過多，那也算是需要耗費資源的。

一般還需要維護表和索引，你這裡有什麼建議嗎？不妨留言說說你的提議，優化就留到下次。

所以當你的大表需要匯入到其它資料庫時，需在新資料庫上先關閉索引，然後再添上索引，要不然效率就太低了。

04 索引的表現型別代表作有哪些?

乖乖，索引還有表現種類，這神馬情況？

大家都知道B+Tree、Hash索引，但這些都底層實現的資料結構，而表現種類在明面上，我們常說的，例如：聚簇索引、非聚簇索引等，都包含了對應的資料結構。

問最多算聚簇索引、非聚簇索引，那它們是什麼呢？

聚簇索引：索引和資料都儲存在一起，代表作Innodb

非聚簇索引：索引和資料分開儲存，代表作MyIASM

上述的特性，也和它們的物理儲存檔案有關係。檔案放在資料庫安裝目錄下的data目錄中

/mysql-57/data/mysql複製程式碼

MyISAM結構如下：

.frm為表結構檔案，儲存像create alter等語句 .MYD為儲存資料檔案 .MYI為儲存索引檔案

InnoDb結構如下：

.frm為表結構檔案，.ibd為資料+索引檔案

在InnoDB儲存引擎中,就一定都是聚簇索引嗎？

並不是，只有主鍵索引被稱為聚簇索引（ clusteredindex ）。除開主鍵以外的欄位上建立的索引被稱為非主鍵索引，非主鍵索引也被稱為二級索引（ secondary index ）。

注：現在你該知道，為啥面試都不問你什麼唯一、普通、聯合索引了吧，那都是屬於二級索引呢

那這兩者之間有什麼區別嗎？區別在非主鍵索引的葉子節點內容是主鍵，當找到主鍵後，還需要根據主鍵再一次的進行索引樹的查詢，這個過程稱之為回表。

例如：

如果語句是 select * from nezha where ID=7 ，即主鍵查詢方式，那它只需搜尋 ID 這棵 B+ 樹；

如果語句是 select * from nezha where name = '哪吒' ，即普通索引查詢方式，則需要先搜尋 name 索引樹，得到 ID的值為 7，再到 ID 索引樹搜尋一次。這就是所謂的回表。

那這個問題怎麼解決呀！

內心獨白：哎呀！咋這麼多問題，煩不煩。

這個好辦，剛才我們是 select * ，查詢所有記錄，如果查詢欄位上只出現主鍵索引與建立索引的欄位，那就不需要回表了。因為走二級索引時，就已經包含了你需要的欄位列啦，那就不需要在回表了。這就被稱之為索引覆蓋，即索引已經包含了查詢操作的值。

這也是為什麼，當有多個欄位需建立索引時，會建立聯合索引，也是為了更好支援索引覆蓋。

瞬間飛過，"我怎麼這麼好看，這麼好看怎麼辦"

05資料庫內部利用索引是如何讀取資料的？

搞了這麼久，那這個索引查詢資料的時候，是怎麼個讀取原理又是什麼？

那這首先得說資料庫中的讀取資料單位，資料庫中的資料是按照頁讀取的。預設一頁的資料為16KB。而磁碟塊（OS）預設為4KB

 show global variables like 'innodb_page%';複製程式碼

那索引和資料都儲存在節點裡面，這個資料怎麼個讀法？

上面說到，資料庫讀取資料是根據頁為單位，並且讀的資料不滿足1頁或超過1頁，那麼也會讀滿1頁。這也叫做預讀

也就是說節點讀取資料的大小應該控制在1頁、2頁、3頁、4頁等倍數頁大小最為合適。

那你說說這個頁吧！

每個資料頁中的資料，採用單向連結串列的形式進行連線。

各個頁之間採用雙向連結串列連結。

查詢資料時是根據頁內分組定義的。首先在插入資料時就會根據主鍵大小做好排序結構，並按照最大和最小進行分組。

最小虛擬資料獨自一組，它擁有一條資料，就是最小資料。然後剩下的資料再分成一組，即最大資料為另一組。當進行資料插入的時，都是先插入到最大資料組，當最大資料組裝滿後在進行分裂。

分組確立後，在進行資料查詢的時就是根據二分查詢法確定對應資料所在的槽位置，然後在使用記錄頭資訊的next_record一條條進行查詢。

當以(非主鍵)作為搜尋條件：只能從最小虛擬資料記錄，開始依次遍歷單連結串列中的每條記錄。

所以，當寫複製程式碼

 select * from nezha where name='nezha'複製程式碼

 這樣沒有進行任何優化的sql語句，預設會這樣做：複製程式碼

• 讀取記錄所在頁的範圍

• 根據雙向連結串列，找到所在頁

• 從所在頁中查詢相應的記錄

• 由於不是主鍵查詢，就遍歷所在頁的單連結串列

06 索引就不命中？前提你得知道規則

使用索引當中，最核心的就是最左匹配原則，索引命中都是根據它來定義的。

最左匹配原則：

• 索引可以的簡單如單列 (a)，也可以複雜如多列 (a,b,c,d)，即聯合索引。

• 如果是聯合索引，那麼key也由多個列組成，同時，索引只能用於查詢key是否存在（相等），遇到範圍查詢 (>、<、BETWEEN、LIKE)等就不能進一步匹配了，後續退化為線性查詢。因此，列的排列順序決定了可命中索引的列數。

• 索引列不能是表示式的一部分，那樣無法命中索引，例如

• ：SELECT  *   FROM  nezha  WHERE  id + 1 = 5;   date(create_time)='2020-03-05'複製程式碼

例如：

• 如有索引 (a,b,c,d)，查詢條件 a=7 and b=8 and c>15 and d=32，則會在每個節點依次命中a、b、c，無法命中d。(c已經是範圍查詢了，d就沒辦法進行對比查詢了)

總結：

索引在資料庫中是一個非常重要的內容！

• 最左字首匹配原則。這是非常重要的原則，SQL查詢都是基於它來。MySQL會一直向右匹配，直到遇到範圍查詢 （>,<,BETWEEN,LIKE）就停止匹配。

• 頁也需要了解下，這個是資料庫在內部的工作機制。

• 索引的表現形式針對於不同的儲存引擎，表現也不一樣，並且2者之間的儲存引擎區別也要掌握瞭解

• 索引建立方式來自於建表前還是建表後。重點都是資料庫再用後臺執行緒建立與維護索引

• B+Tree和Hash這2個特點還是需要注意，並且它們之間區別還未細講。後面會針對面試問題，給大家補上來。

  
  

如有幫助，歡迎關注@蓮花童子哪吒

及時收看更多好文

索引未完，下期在談，索引優化、細節、執行效率等等階段性內容