本篇文章主要學習了MySQL的索引的資料結構的認識，做一個大概的瞭解即可。
一、索引
在關聯式資料庫中，索引是一種單獨的、物理的對資料庫表中一列或多列的值進行排序的一種儲存資料結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的資料頁的邏輯指標清單。索引的作用相當於圖書的目錄，可以根據目錄中的頁碼快速查詢到所需的內容。
在MySQL中，儲存引擎用類似的方法使用索引，先在索引中找到對應值，然後根據匹配的索引記錄找到對應的行。
首先說明下MySQL的索引主要是基於Hash表或者B+樹。
二、索引資料結構
瞭解索引就需要從索引常見的資料結構開始瞭解學習，這裡有集中常見的的索引資料結構。
二叉樹（Binary Trees）
二叉樹是每個節點最多隻有兩個分支（即不存在分支度大於2的節點）的樹結構。通常被稱之為“左子樹”和“右子樹”
左子樹<父節點<=右子樹
二叉樹的第i層至多有有2^(i-1)個節點，
深度為K的二叉樹至多總共有個2^k-1節點（定義根節點所在深度 k0=0），而總計擁有節點數符合的，稱為“滿二叉樹”；
二叉樹通常作為資料結構應用，典型用法是對節點定義一個標記函式，將一些值與每個節點相關係。這樣標記的二叉樹就可以實現二叉搜尋樹和二叉堆，並應用於高效率的搜尋和排序。
同時學習資料結構，這裡還推薦Data Structure Visualizations進行學習，可以非常直觀的看到資料結構允許的過程，一步一步的怎麼走的都可以很清晰看得到。
找到其中的Binary Search Trees二叉樹
可以看到二叉樹不適合用作當作索引的，資料量龐大的話，二叉樹的層數會很大，查詢效率固然也很慢了。
推薦閱讀：維基百科-二叉樹
紅黑樹（Red-Black Trees）
是一種自平衡二叉查詢樹，典型用途是實現關聯陣列。
紅黑樹的結構複雜，但它的操作有著良好的最壞情況執行時間，並且在實踐中高效：它可以在O(log n)時間內完成查詢，插入和刪除，這裡的n是樹中元素的數目。
紅黑樹遵行以下原則：
- 節點是紅色或黑色。
- 根是黑色。
- 所有葉子都是黑色（葉子是NIL節點）。
- 每個紅色節點必須有兩個黑色的子節點。（從每個葉子到根的所有路徑上不能有兩個連續的紅色節點。）
- 從任一節點到其每個葉子的所有簡單路徑都包含相同數目的黑色節點。
下面是一個具體的紅黑樹的圖例：
這些約束確保了紅黑樹的關鍵特性：從根到葉子的最長的可能路徑不多於最短的可能路徑的兩倍長。結果是這個樹大致上是平衡的。因為操作比如插入、刪除和查詢某個值的最壞情況時間都要求與樹的高度成比例，這個在高度上的理論上限允許紅黑樹在最壞情況下都是高效的，而不同於普通的二叉查詢樹。
要知道為什麼這些性質確保了這個結果，注意到性質4導致了路徑不能有兩個毗連的紅色節點就足夠了。最短的可能路徑都是黑色節點，最長的可能路徑有交替的紅色和黑色節點。因為根據性質5所有最長的路徑都有相同數目的黑色節點，這就表明了沒有路徑能多於任何其他路徑的兩倍長。
同樣在Data Structure Visualizations中選擇Red-Black Trees紅黑樹進行插入操作可以直觀的看到紅黑樹的插入過程
同樣紅黑樹也不適用於MySQL的索引，資料量龐大之後，數層也會變大。
推薦閱讀：
其他結構的問題
由於無法裝入記憶體，則必然依賴磁碟（或SSD）儲存。而記憶體的讀寫速度是磁碟的成千上萬倍（與具體實現有關），因此，核心問題是“如何減少磁碟讀寫次數”。
首先不考慮頁表機制，假設每次讀、寫都直接穿透到磁碟，那麼：
- 線性結構：讀/寫平均O(n)次
- 二叉搜尋樹（
BST）：讀/寫平均O(log2(n))次；如果樹不平衡，則最差讀/寫O(n)次 - 自平衡二叉搜尋樹（
AVL）：在BST的基礎上加入了自平衡演算法，讀/寫最大O(log2(n))次 - 紅黑樹（
RBT）：另一種自平衡的查詢樹，讀/寫最大O(log2(n))次
BST、AVL、RBT很好的將讀寫次數從O(n)優化到O(log2(n))；其中，AVL和RBT都比BST多了自平衡的功能，將讀寫次數降到最大O(log2(n))。
假設使用自增主鍵，則主鍵本身是有序的，樹結構的讀寫次數能夠優化到樹高，樹高越低讀寫次數越少；自平衡保證了樹結構的穩定。如果想進一步優化，可以引入B樹和B+樹。
B樹（B-Trees）
又稱：多路平衡查詢樹。大多數儲存引擎都支援B樹索引。b樹通常意味著所有的值都是按順序儲存的，並且每一個葉子節點到根的距離相同。B樹索引能夠加快訪問資料的速度，因為儲存引擎不再需要進行全表掃描來獲取資料。下圖就是一顆簡單的B樹。
在B樹中，內部（非葉子）節點可以擁有可變數量的子節點（數量範圍預先定義好）。當資料被插入或從一個節點中移除，它的子節點數量發生變化。為了維持在預先設定的數量範圍內，內部節點可能會被合併或者分離。
如下圖所示：
- 葉節點具有相同的深度，葉節點的指標為空
- 所有索引元素不重複
- 節點中的資料索引從左到右遞增排列
- 無論中間節點還是葉子節點都帶有衛星資料data（索引元素所指向的資料記錄）
只演示了插入的過程，其中可以通過delete、find執行刪除和查詢操作。直觀的感受到B樹的執行過程。
每個節點儲存了多個Key和子樹，子樹與Key按順序排列。
同二叉搜尋樹類似，每個節點儲存了多個key和子樹，子樹與key按順序排列。
頁表的目錄是擴充套件外存+加速磁碟讀寫，一個頁（Page）通常4K（等於磁碟資料塊block的大小，見inode與block的分析），作業系統每次以頁為單位將內容從磁碟載入到記憶體（以攤分尋道成本），修改頁後，再擇期將該頁寫回磁碟。考慮到頁表的良好性質，可以使每個節點的大小約等於一個頁（使m非常大），這每次載入的一個頁就能完整覆蓋一個節點，以便選擇下一層子樹；對子樹同理。對於頁表來說，AVL（或RBT）相當於1個key+2個子樹的B樹，由於邏輯上相鄰的節點，物理上通常不相鄰，因此，讀入一個4k頁，頁面內絕大部分空間都將是無效資料。
假設key、子樹節點指標均佔用4B，則B樹節點最大m * (4 + 4) = 8m B；頁面大小4KB。則m = 4 * 1024 / 8m = 512，一個512叉的B樹，1000w的資料，深度最大 log(512/2)(10^7) = 3.02 ~= 4。對比二叉樹如AVL的深度為log(2)(10^7) = 23.25 ~= 24，相差了5倍以上。震驚！B樹索引深度竟然如此！
那為什麼B數這麼厲害了，還有B+樹的出現呢，必然是解決B樹存在的問題
1、為定位行數
2、無法處理範圍查詢
問題1：為定位行數
資料表的記錄有多個欄位，僅僅定位到主鍵是不夠的，還需要定位到資料行。有3個方案解決：
- 直接將key對應的資料行（可能對應多行）儲存子節點中。
- 資料行單獨儲存；節點中增加一個欄位，定位key對應資料行的位置。
- 修改key與子樹的判斷邏輯，使子樹大於等於上一key小於下一key，最終所有訪問都將落於葉子節點；葉子節點中直接儲存資料行或資料行的位置。
方案1直接pass，儲存資料行將減少頁面中的子樹個數，m減小樹高增大。
方案2的節點中增加了一個欄位，假設是4B的指標，則新的m = 4 * 1024 / 12m = 341.33 ~= 341，深度最大 log(341/2)(10^7) = 3.14 ~= 4。
方案3的節點m與深度不變，但時間複雜度變為穩定的O(logm(n))。
方案3可以考慮。
問題2：無法處理範圍查詢
實際業務中，範圍查詢的頻率非常高，B樹只能定位到一個索引位置（可能對應多行），很難處理範圍查詢。改動較小的是2個方案：
- 不改動；查詢的時候先查到左界，再查到右界，然後DFS（或BFS）遍歷左界、右界之間的節點。
- 在“問題1-方案3”的基礎上，由於所有資料行都儲存在葉子節點，B樹的葉子節點本身也是有序的，可以增加一個指標，指向當前葉子節點按主鍵順序的下一葉子節點；查詢時先查到左界，再查到右界，然後從左界到有界線性遍歷。
乍一看感覺方案1比方案2好——時間複雜度和常數項都一樣，方案1還不需要改動。但是別忘了區域性性原理，不管節點中儲存的是資料行還是資料行位置，方案2的好處在於，依然可以利用頁表和快取預讀下一節點的資訊。而方案1則面臨節點邏輯相鄰、物理分離的缺點。 推薦閱讀：
B+樹（B+Trees）
主要變動如上所述：
- 修改key與子樹的組織邏輯，將索引訪問都落到葉子節點
- 按順序將葉子節點串起來（方便範圍查詢）
回顧上一個B樹，一個m階的B樹具有如下幾個特徵：
1.根結點至少有兩個子女。
2.每個中間節點都包含k-1個元素和k個孩子，其中 m/2 <= k <= m
3.每一個葉子節點都包含k-1個元素，其中 m/2 <= k <= m
4.所有的葉子結點都位於同一層。
5.每個節點中的元素從小到大排列，節點當中k-1個元素正好是k個孩子包含的元素的值域分劃。
一個m階的B+樹具有如下幾個特徵：
1.有k個子樹的中間節點包含有k個元素（B樹中是k-1個元素），每個元素不儲存資料，只用來索引，所有資料都儲存在葉子節點。
2.所有的葉子結點包含了全部元素的資訊，及指向含這些元素記錄的指標，且葉子結點本身依關鍵字的大小自小而大順序連結。
3.所有的中間節點元素都同時存在於子節點，在子節點元素中是最大（或最小）元素。
B+樹特性總結
B+樹是B樹的升級版，其有如下特性
- 非葉子節點不儲存data，只儲存索引(冗餘)，可以放更多的索引
- 葉子節點包含所有索引欄位
- 葉子節點用指標連線，提高區間訪問的效能
- 只有葉子節點帶有衛星資料data（索引元素所指向的資料記錄）
同樣在Data Structure Visualizations中選擇B+ TreesB+樹進行插入操作可以直觀的看到插入過程
在動圖中可以看出，B+樹的每一個葉子節點都有一個指標指向下一個節點，把所有的葉子節點串在一起。索引資料都儲存在葉子節點中。
B+樹相比於B樹，有什麼優勢呢：
1.單一節點儲存更多的元素，使得查詢的IO次數更少。
2.所有查詢都要查詢到葉子節點，查詢效能穩定。
3.所有葉子節點形成有序連結串列，便於範圍查詢。
總結，B+樹相比B樹的優勢有三：http://1.IO次數更少；2.查詢效能穩定；3.範圍查詢簡便。
推薦閱讀：
Hash索引
hash索引基於hash表實現，Hash 索引是將索引鍵通過 Hash 運算之後，將 Hash運算結果的 Hash 值和所對應的行指標資訊存放於一個 Hash 表中。只有精準匹配索引所有列的查詢才有效。索引的檢索可以一次定位，不像B-Tree索引需要從根節點出發到目標節點。雖然Hash索引很快，遠高於B-tree索引，但是也有其弊端。
Hash索引僅僅能滿足’=’,’IN’,’<=>’查詢，也就是等值查詢，不能使用範圍查詢。很受限
由於 Hash 索引比較的是進行 Hash 運算之後的 Hash 值，所以它只能用於等值的過濾，不能用於基於範圍的過濾，因為經過相應的 Hash 演算法處理之後的 Hash 值的大小關係，並不能保證和Hash運算前完全一樣。
由於Hash索引是通過hash表實現，其本身是沒有排序的。
由於 Hash 索引中存放的是經過 Hash 計算之後的 Hash 值，而且Hash值的大小關係並不一定和 Hash 運算前的鍵值完全一樣，所以資料庫無法利用索引的資料來避免任何排序運算；
Hash索引不能利用部分索引鍵查詢
對於組合索引，Hash索引在計算hash值的時候是組合索引鍵合併後再一起計算hash值，而不是單獨計算hash值，所以通過組合索引的前面一個或幾個索引鍵進行查詢的時候，Hash 索引也無法被利用。
Hash 索引在任何時候都不能避免表掃描
前面已經知道，Hash 索引是將索引鍵通過 Hash 運算之後，將 Hash運算結果的 Hash 值和所對應的行指標資訊存放於一個 Hash 表中，由於不同索引鍵存在相同 Hash 值，所以即使取滿足某個 Hash 鍵值的資料的記錄條數，也無法從 Hash 索引中直接完成查詢，還是要通過訪問表中的實際資料進行相應的比較，並得到相應的結果。
Hash 索引遇到大量Hash值相等的情況後效能並不一定就會比B-Tree索引高。
對於選擇性比較低的索引鍵，如果建立 Hash 索引，那麼將會存在大量記錄指標資訊存於同一個 Hash 值相關聯。這樣要定位某一條記錄時就會非常麻煩，會浪費多次表資料的訪問，而造成整體效能低下。
三、MySQL資料庫引擎
通過navicat工具檢視錶設計選項中，從引擎中可以看到MySQL又這麼多引擎。具體細分到每個表，不同的表引擎可以不一樣。
MyISAM
新建一張表t_test_myisam，引擎使用MyISAM，檢視原檔案可以看到有3個檔案
可以看到索引和資料是分開的，其中索引檔案僅僅儲存資料記錄的地址，故屬於非聚簇索引。
主鍵索引（Primary Index）
MyISAM引擎使用B+Tree作為索引結構，葉節點的data存放的是資料記錄的地址。如下圖是MyISAM主鍵索引的原理圖。
其中Col1為主鍵，可以看出看出MyISAM的索引檔案僅儲存資料記錄的地址。
輔助索引（Secondary Index）
在Col2上建立一個輔助索引，如下圖輔助索引原理圖。
可以看到與主鍵索引沒有任何區別，只不過主鍵索引的key是唯一的，而輔助索引的key可以重複。
MyISAM中索引檢索的演算法為首先按照B+Tree搜尋演算法搜尋索引，如果指定的Key存在，則取出其data域的值，然後以data域的值為地址，讀取相應資料記錄。
InnoDB
新建一張表t_test_innodb，引擎使用InnoDB，檢視原檔案可以看到有2個檔案
主鍵索引（Primary Index）
InnoDB的索引和資料在一個檔案當中。
按照B+Tree組織的一個索引結構。
葉節點儲存了完整的資料記錄和索引。這種索引就叫做聚簇索引。
索引的Key是資料的主鍵，因此InnoDB表資料檔案本身就是主索引。
如下圖：
可以看到葉節點包含了完整的資料記錄。
因為InnoDB的資料檔案本身要按照主鍵聚集，所以InnoDB要求必須有主鍵。如果沒有顯式指定，則MySQL系統會自動選擇一個可以唯一標識資料記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表生成一個隱含欄位rowid作為主鍵，這個欄位長度為6個位元組，型別為長整形。
輔助索引（Secondary Index）
輔助索引，將途中的第二行name，作為索引如圖
聚簇索引這種實現方式使得按照主鍵的搜尋十分高效，但是首先檢索輔助索引獲得主鍵，然後用主鍵到主索引中檢索獲得記錄。
由於InnoDB索引的實現特性，推薦使用整形的自增主鍵。
有三點好處：
- 自增key一般為int等整數型，key比較緊湊，這樣m可以非常大，而且索引佔用空間小。最極端的例子，如果使用50B的varchar（包括長度），那麼
m = 4 * 1024 / 54m = 75.85 ~= 76，深度最大log(76/2)(10^7) = 4.43 ~= 5，再加上cache缺失、字串比較的成本，時間成本增加較大。同時，key由4B增長到50B，整棵索引樹的空間佔用增長也是極為恐怖的（如果二級索引使用主鍵定位資料行，則空間增長更加嚴重）。 - MySQL索引底層的資料比較都是整數型比較，如果主鍵時字串型別的，裡面還有英文，還得轉換ASCII碼進行比較。所以不建議使用uuid作為主鍵。
- 自增的主鍵使得資料行的插入比如落到索引數的最右側，發生節點分裂的頻率較低。B+Tree實際操作插入過程。如果不是非單調主鍵，插入過程很大程度會發生節點重排，不利於索引優化的初衷。
InnoDB索引和MyISAM索引的區別
一是主索引的區別：InnoDB的資料檔案本身就是索引檔案。而MyISAM的索引和資料是分開的。
二是輔助索引的區別：InnoDB的輔助索引data域儲存相應記錄主鍵的值而不是地址。而MyISAM的輔助索引和主索引沒有多大區別。
四、覆蓋索引
InnoDB儲存引擎支援覆蓋索引，即從輔助索引中就可以得到查詢的記錄，不需要查詢聚簇索引中的記錄了。可以減少大量的IO操作。
如果要查詢輔助索引中不含有的欄位，得先遍歷輔助索引，再遍歷聚集索引，而如果要查詢的欄位值在輔助索引上就有，就不用再查聚集索引了，這顯然會減少IO操作。
五、聯合索引
兩個或以上的列上的索引。如下圖聯合索引的原理圖：
上圖中的聯合索引有三個，從上到下，嚴格按照排序。
六、優化建議
最左字首匹配
索引可以簡單如一個列(a)，也可以複雜如多個列(a, b, c, d)，即聯合索引。如果是聯合索引，那麼key也由多個列組成，同時，索引只能用於查詢key是否存在（相等），遇到範圍查詢(>、<、between、like左匹配)等就不能進一步匹配了，後續退化為線性查詢。因此，列的排列順序決定了可命中索引的列數。
如有索引(a, b, c, d)，查詢條件a = 1 and b = 2 and c > 3 and d = 4，則會在每個節點依次命中a、b、c，無法命中d。也就是最左字首匹配原則。
=、in自動優化順序
不需要考慮=、in等的順序，mysql會自動優化這些條件的順序，以匹配儘可能多的索引列。
如有索引(a, b, c, d)，查詢條件c > 3 and b = 2 and a = 1 and d < 4與a = 1 and c > 3 and b = 2 and d < 4等順序都是可以的，MySQL會自動優化為a = 1 and b = 2 and c > 3 and d < 4，依次命中a、b、c。
索引列不能參與計算
有索引列參與計算的查詢條件對索引不友好（甚至無法使用索引），如from_unixtime(create_time) = '2014-05-29'。
原因很簡單，如何在節點中查詢到對應key？如果線性掃描，則每次都需要重新計算，成本太高；如果二分查詢，則需要針對from_unixtime方法確定大小關係。
因此，索引列不能參與計算。上述from_unixtime(create_time) = '2014-05-29'語句應該寫成create_time = unix_timestamp('2014-05-29')。
能擴充套件就不要新建索引
如果已有索引(a)，想建立索引(a, b)，儘量選擇修改索引(a)為索引(a, b)。
新建索引的成本很容易理解。而基於索引(a)修改為索引(a, b)的話，MySQL可以直接在索引a的B+樹上，經過分裂、合併等修改為索引(a, b)。
不需要建立字首有包含關係的索引
如果已有索引(a, b)，則不需要再建立索引(a)，但是如果有必要，則仍然需考慮建立索引(b)。
本作品採用《CC 協議》，轉載必須註明作者和本文連結