公司最近組織開展一系列的技術分享會，我藉著這次機會把自己以前學的一些知識點重新歸納一下記錄起來。首先是對自己技術的積累有好處，其次也是想讓自己學習的知識能夠更深入的理解，有不足或者錯誤的地方歡迎指出，共同進步。
探究一下MYSQL索引為什麼這麼快？索引究竟是什麼？
1.什麼是索引？
- MYSQL官方文件介紹索引是一種方便快速查詢資料的資料結構。用我們生活中的例子來講，索引就好比書的目錄，如果沒有目錄，每次你想要查詢某些內容，你必須從頭開始查詢，這樣的效率極其低下。
- 索引一般比較大，所以大部分情況下索引是存在磁碟的索引檔案上，也有可能是存在資料檔案上。
- 索引的種類有很多：主鍵索引（這是最常見的一種索引，主鍵不能為空且必須唯一）、唯一索引（相對於主鍵索引，它的值可以為空）、全文索引（在char、varchar、text型別可以使用）、普通索引、字首索引。按照列數來區分：單一索引、組合索引（多欄位組成）
2.MYSQL索引的資料結構
在講解MYSQL索引的資料結構之前，我們先看看了解一下其他的資料結構，看看他們的優缺點進行對比。
2.1 二叉樹
二叉樹簡單來說就是左節點大於右節點，在理想的情況下，他的查詢速度就接近與二分法的效能O(log2n)。因為在記憶體排序的時間是非常快的，可以忽略不計，所以總的消耗時間就取決於IO的操作次數。二叉樹查詢速度取決樹高,每次查詢介面都是一次IO操作，也是效能的瓶頸所在。
但是也會有這種一種情況,同樣也是二叉樹，但是他的樹非常高，導致查詢一次需要多次IO操作，效率及其低下
2.2 平衡二叉樹
平衡二叉樹可以解決二叉樹不穩定導致查詢效率低下的缺點。平衡二叉樹的特點：樹的左右節點層級最高相差一層。在插入或者刪除的情況下，通過左旋轉或右旋轉使得整個二叉樹平衡，不會出現層級相差很多的情況。平衡二叉樹的效能接近二分法查詢O(log2n)。
平衡二叉樹查詢id為8的記錄，只需要IO操作2次即可。但是仔細想一下，如果資料量很多呢？假設資料表有100W的資料，根據O(log2n)計算，大約需要20次IO操作。磁碟尋道大概需要10ms，總的查詢時間為20 * 10 = 0.2，效率也比較低下。
還有就是平衡二叉樹不支援範圍查詢，範圍查詢每次都需要從根節點遍歷，效率及其低下。
2.3 B-樹(改造二叉樹成多叉樹)
之前的幾種樹形結構適合與小資料量的記憶體查詢，也叫做內查詢。在1970年，R.Bayer和E.Mccreight提出了一種適合於外查詢的平衡多叉樹B-樹。MYSQL資料檔案是存在磁碟的，每次都是按照一頁大小（一般而16K）讀取記憶體。像二叉樹、平衡二叉樹，每次讀取節點都要進行一次IO操作，所以樹越高IO操作次數越多。想要提高查詢效率，首先要解決的就是降低樹高的問題。
設想一下，每一次IO操作讀取一個節點，讀取16K大小的記憶體資料，但是每次節點的資料實際上遠遠小於16K。假設節點資料大小為16B，為了讓一次IO操作能夠讀取更多節點，我們可以在每個節點儘可能地儲存索引資料。我們在每個節點儲存1000個索引資料（1000*16B = 16K），將二叉樹改造成多叉樹，從樹高變成樹“胖”，解決了樹高的問題，從而降低IO操作次數，提高查詢效率。
B-樹的特點：1.每個節點儲存多個元素 2.節點的元素包含鍵值以及資料 3.所有葉子節點存放同一層，具有相同深度，葉子節點之間沒有指標連線。這種資料結構解決了樹高IO次數多的問題，但是在每個節點儲存資料，假設資料一旦很大，每個節點儲存的索引數也隨之減少，最後還是會導致樹很高，查詢效率低。其次，B-樹不能範圍查詢。設想一下如果我們要查詢15-25範圍內的資料，查到15之後就又要重新回到根節點繼續查詢，這樣迴圈遍歷的效率有待提高。
2.4 B+樹（改造B-樹）
結合了B-樹的缺點進行改造，就誕生了B+樹。B+樹跟B-樹的差異並不是很大，判斷的依據很簡單：節點是否存放資料。B+樹存放資料的節點只有葉子節點，而且葉子結點雙向指標連線，形成了雙向有序連結串列。
這樣一來，除了葉子節點其他存放的都是索引鍵值，可以很大程度增加節點存放索引樹，從理論上樹是要比B-樹“矮”的。同時B+樹支援範圍查詢，因為底層葉子節點是雙向有序連結串列，而且主鍵具有唯一性（對於輔助索引後面會講到），假設範圍為15到19，我們只需要查到15記錄之後繼續往後查詢，直到大於19即可，無需從根節點再次遍歷，效率較高。
3.MYSQL索引B+樹實踐
MYISAM引擎 (主鍵索引)
MYISAM引擎是非聚簇索引，也就是說B+樹的葉子節點的鍵值存放索引列的值，資料存在資料在磁碟的地址。MYISAM的索引檔案跟資料檔案是分開儲存的。CREATE TABLEstudent(idint(11) NOT NULL AUTO_INCREMENT,namevarchar(20) DEFAULT NULL,ageint(11) DEFAULT NULL, PRIMARY KEY (id) USING BTREE, KEYidx_age(age) USING BTREE ) ENGINE = MyISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;
建立了一個表student，id為主鍵索引,age為普通索引。假設表中有以下資料，現在執行以下語句SELECT * FROM stduent WHERE id = 16
具體鏈路：（實際上邏輯上相鄰實際磁碟並不一定相鄰，這裡只是方便展示）
（1）先從磁碟1載入資料到記憶體，因為18>16走左路（一次IO操作） （2）讀取磁碟2載入資料到記憶體，又因為16>14向下繼續讀取（一次IO操作） （3）檢索葉子節點，判斷到等於16則停止（一次IO操作）
MYISAM引擎 (輔助索引)
在MYISAM引擎中，主鍵索引跟輔助索引的差別並不很大，葉子節點存放的都是磁碟地址，只是輔助索引並並不是唯一值，所以在等值查詢檢索葉子節點的時候，也要按照範圍一樣，進行檢索資料。
Innodb引擎 （聚簇索引、主鍵索引）
Innodb引擎使用的是聚簇索引。每一個資料表都有一個聚簇索引，採用B+樹的資料結構，葉子節點鍵值對應存放的是整行資料記錄。在Innodb中，非聚簇索引就是輔助索引,葉子節點儲存的資料是主鍵值。如果一個表沒有主鍵，innodb引擎會自動構建一個隱藏的rowid在構成聚簇索引。依舊是按照我們剛剛講解MYISAM引擎的資料表例子：SELECT * FROM stduent WHERE age = 37
具體鏈路：（實際上邏輯上相鄰實際磁碟並不一定相鄰，這裡只是方便展示）
（1）先從磁碟1載入資料到記憶體，因為18<37走左路（一次IO操作） （2）讀取磁碟2載入資料到記憶體，又因為37>24向下繼續讀取（一次IO操作） （3）檢索葉子節點，判斷到等於37則停止（一次IO操作） （4）這時候查到的資料就是age欄位為37的記錄主鍵值。按照聚簇索引的方式再查詢資料就得到了資料結構集（這個過程叫做回表）相同索引欄位情況下，按主鍵欄位排序。因為要多加上三次回表操作，效率回相對低一點點。這裡有個概念叫做覆蓋索引，如果查詢所需要的欄位剛好就是索引欄位就不需要回表查詢，從而提高了查詢效率。
索引的原理遠遠不止於這麼一點點，組合索引以及一些其他的原理我暫時理解還不是到位，等到後面學習更加理解之後再寫一篇文章進行記錄總結吧。“學而不思則惘，思而不學則殆”，以前沒辦法理解這句話的涵義，直到後來才知道總結、思考才是學習最有效率的方式。多總結、多思考，也是作為一名程式設計師進步的最快方式。
本作品採用《CC 協議》，轉載必須註明作者和本文連結