Mysql探索之索引詳解，又能和麵試官互扯了~

前言

• 索引是什麼？有什麼利弊？一旦在面試中被問道，對於新入門的小白可能是個棘手的問題。

• 本篇文章將會詳細講述什麼是索引、索引的優缺點、資料結構等等常見的知識。

什麼是索引

• 索引就是一種的資料結構，儲存表中特定列的值並對值進行排序，所以是在表的列上面建立的。索引將通過縮小一張表中需要查詢的記錄的數目來加快搜尋的速度。如果沒有索引，資料庫不得不進行全表掃描。索引就好比一本書的目錄，它會讓你更快的找到內容。

索引的優點

1. 通過建立唯一性索引，可以保證資料庫表中每一行資料的唯一性。

2. 可以大大加快資料的檢索速度，避免進行全表的資料掃描，大大減少遍歷匹配的行數，這也是建立索引的最主要的原因。

3. 可以加速表和表之間的連線，特別是在實現資料的參考完整性方面特別有意義。

4. 在使用分組和排序子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間。

5. 通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的效能。

索引的缺點

1. 建立索引和維護索引要耗費時間，這種時間隨著資料量的增加而增加。

2. 索引需要佔物理空間，除了資料表佔資料空間之外，每一個索引還要佔一定的物理空間，如果要建立聚簇索引，那麼需要的空間就會更大。

3. 當對錶中的資料進行增加、刪除和修改的時候，索引也要動態的維護，這樣就降低了資料的維護速度。

在哪些列建立索引

1. 在經常需要搜尋的列上，可以加快搜尋的速度；

2. 在作為主鍵的列上，強制該列的唯一性和組織表中資料的排列結構；

3. 在經常用在連線的列上，這些列主要是一些外來鍵，可以加快連線的速度；

4. 在經常需要根據範圍進行搜尋的列上建立索引，因為索引已經排序，其指定的範圍是連續的；

5. 在經常需要排序的列上建立索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；

6. 在經常使用在WHERE子句中的列上面建立索引，加快條件的判斷速度。

不在哪些列建索引？

1. 對於那些在查詢中很少使用或者參考的列不應該建立索引。這是因為，既然這些列很少使用到，因此有索引或者無索引，並不能提高查詢速度。相反，由於增加了索引，反而降低了系統的維護速度和增大了空間需求。

2. 對於那些只有很少資料值的列也不應該增加索引。這是因為，由於這些列的取值很少，例如人事表的性別列，在查詢的結果中，結果集的資料行佔了表中資料行的很大比例，即需要在表中搜尋的資料行的比例很大。增加索引，並不能明顯加快檢索速度。

3. 對於那些定義為text, image和bit資料型別的列不應該增加索引。這是因為，這些列的資料量要麼相當大，要麼取值很少。

4. 當修改效能遠遠大於檢索效能時，不應該建立索引。這是因為，修改效能和檢索效能是互相矛盾的。當增加索引時，會提高檢索效能，但是會降低修改效能。當減少索引時，會提高修改效能，降低檢索效能。因此，當修改效能遠遠大於檢索效能時，不應該建立索引。

索引的資料結構

常見的索引的資料結構有：B+Tree、Hash索引、FullText索引、R-Tree索引。

Hash 索引

1. 概述：

MySQL 中，只有Memory儲存引擎支援Hash索引，是Memory表的預設索引型別。hash 索引把資料的索引以 hash 值形式組織起來，因此檢索效率非常高，可以一次定位，不像B-/+Tree索引需要進行從根節點到葉節點的多次 IO 操作。

2. Hash 索引的缺點：

① Hash 索引僅僅能滿足等值的查詢，不能滿足範圍查詢。因為資料在經過 Hash 演算法後，其大小關係就可能發生變化。② Hash 索引不能被排序。同樣是因為資料經過 Hash 演算法後，大小關係就可能發生變化，排序是沒有意義的。

③ Hash 索引不能避免表資料的掃描。因為發生 Hash 碰撞時，僅僅比較 Hash 值是不夠的，需要比較實際的值以判定是否符合要求。

④ Hash 索引在發生大量 Hash 值相同的情況時效能不一定比 B-Tree 索引高。因為碰撞情況會導致多次的表資料的掃描，造成整體效能的低下，可以通過採用合適的 Hash 演算法一定程度解決這個問題。

⑤ Hash 索引不能使用部分索引鍵查詢。因為當使用組合索引情況時，是把多個資料庫列資料合併後再計算 Hash 值，所以對單獨列資料計算 Hash 值是沒有意義的。

FullText 索引

1. 概述：

全文索引，目前 MySQL 中只有MyISAM儲存引擎支援，並且只有char、varchar、text 型別支援。它用於替代效率較低的like 模糊匹配操作，而且可以通過多欄位組合的全文索引一次性全模糊匹配多個欄位。

2. 儲存結構：

同樣使用B-Tree存放索引資料，但使用的是特定的演算法，將欄位資料分割後再進行索引（一般每 4 個位元組一次分割），索引檔案儲存的是分割前的索引字串集合，與分割後的索引資訊，對應 Btree 結構的節點儲存的是分割後的詞資訊以及它在分割前的索引字串集合中的位置。

B-/+Tree 索引

• B+Tree 是 mysql 使用最頻繁的一個索引資料結構，是 Innodb 和 Myisam 儲存引擎模式的索引型別。相對 Hash 索引，B+樹在查詢單條記錄的速度比不上 Hash 索引，但是更適合排序等操作。

1. B+Tree 索引的優點：

• 帶順序訪問指標的 B+Tree：B+Tree 所有索引資料都在葉子結點上，並且增加了順序訪問指標,每個葉子節點都有指向相鄰葉子節點的指標。這樣做是為了提高區間查詢效率，例如查詢 key 為從 18 到 49 的所有資料記錄，當找到 18 後，只需順著節點和指標順序遍歷就可以一次性訪問到所有資料節點，極大提到了區間查詢效率。

• 大大減少磁碟 I/O 讀取次數。

B-/+Tree 索引：

• 檔案系統及資料庫系統普遍採用 B-/+Tree 作為索引結構：一般來說，索引本身也很大，不可能全部儲存在記憶體中，因此索引往往以索引檔案的形式儲存在磁碟上。這樣的話，索引查詢過程中就要產生磁碟 I/O 消耗，相對於記憶體存取，I/O 存取的消耗要高几個數量級，所以評價一個資料結構作為索引的優劣最重要的指標就是在查詢過程中磁碟 I/O 操作次數的漸進複雜度。換句話說，索引的結構組織要儘量減少查詢過程中磁碟 I/O 的存取次數。

區域性性處理與磁碟預讀

• 由於儲存介質的特性，磁碟本身存取就比主存慢很多，再加上機械運動耗費，磁碟的存取速度往往是主存的幾百分分之一，因此為了提高效率，要儘量減少磁碟 I/O。為了達到這個目的，磁碟往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個位元組，磁碟也會從這個位置開始，順序向後讀取一定長度的資料放入記憶體。

• 由於磁碟順序讀取的效率很高（不需要尋道時間，只需很少的旋轉時間），因此對於具有區域性性的程式來說，預讀可以提高 I/O 效率。預讀的長度一般為頁（page）的整倍數。頁是計算機管理儲存器的邏輯塊，硬體及作業系統往往將主存和磁碟儲存區分割為連續的大小相等的塊，每個儲存塊稱為一頁（在許多作業系統中，頁得大小通常為 4k），主存和磁碟以頁為單位交換資料。當程式要讀取的資料不在主存中時，會觸發一個缺頁異常，此時系統會向磁碟發出讀盤訊號，磁碟會找到資料的起始位置並向後連續讀取一頁或幾頁載入記憶體中，然後異常返回，程式繼續執行。

B-/+Tree 索引的效能分析

• 上文說過一般使用磁碟 I/O 次數評價索引結構的優劣。先從 B-Tree 分析，根據 B-Tree 的定義，可知檢索一次最多需要訪問 h 個節點。資料庫系統的設計者巧妙利用了磁碟預讀原理，將一個節點的大小設為等於一個頁，這樣每個節點只需要一次 I/O 就可以完全載入。為了達到這個目的，在實際實現 B-Tree 還需要使用如下技巧：

• 每次新建節點時，直接申請一個頁的空間，這樣就保證一個節點物理上也儲存在一個頁裡，加之計算機儲存分配都是按頁對齊的，就實現了一個節點只需一次 I/O。

• B-Tree 中一次檢索最多需要h-1次 I/O（根節點常駐記憶體），漸進複雜度為O(h)=O(logdN)。一般實際應用中，出度 d 是非常大的數字，通常超過 100，因此 h 非常小（通常不超過 3）。

• 綜上所述，用 B-Tree 作為索引結構效率是非常高的。

• 而紅黑樹這種結構，h 明顯要深的多。由於邏輯上很近的節點（父子）物理上可能很遠，無法利用區域性性，所以紅黑樹的 I/O 漸進複雜度也為 O(h)，效率明顯比 B-Tree 差很多。

• 另外，B+Tree 更適合外存索引，原因和內節點出度 d 有關。從上面分析可以看到，d 越大索引的效能越好，而出度的上限取決於節點內 key 和 data 的大小，由於 B+Tree 內節點去掉了 data 域，因此可以擁有更大的出度，擁有更好的效能。（詳細見本部分第 3 點）

B-Tree 與 B+Tree 的對比

• 根據 B-Tree 和 B+Tree 的結構，我們可以發現 B+樹相比於 B 樹，在檔案系統或者資料庫系統當中，更有優勢，原因如下：

1. B+樹的磁碟讀寫代價更低

B+樹的內部結點並沒有指向關鍵字具體資訊的指標。因此其內部結點相對 B 樹更小。如果把所有同一內部結點的關鍵字存放在同一盤塊中，那麼盤塊所能容納的關鍵字數量也越多。一次性讀入記憶體中的需要查詢的關鍵字也就越多。相對來說 I/O 讀寫次數也就降低了。

2. B+樹的查詢效率更加穩定

由於內部結點並不是最終指向檔案內容的結點，而只是葉子結點中關鍵字的索引。所以任何關鍵字的查詢必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導致每一個資料的查詢效率相當。

3. B+樹更有利於對資料庫的掃描

B 樹在提高了磁碟 IO 效能的同時並沒有解決元素遍歷的效率低下的問題，而 B+樹只需要遍歷葉子節點就可以解決對全部關鍵字資訊的掃描，所以對於資料庫中頻繁使用的 range query，B+樹有著更高的效能。

MySQL 索引的實現

• 在 MySQL 中，索引屬於儲存引擎級別的概念，不同儲存引擎對索引的實現方式是不同的，本部分主要討論 MyISAM 和 InnoDB 兩個儲存引擎的索引實現方式。

MyISAM 索引的實現

1. 主鍵索引

MyISAM 引擎使用 B+Tree 作為索引結構，葉節點的 data 域存放的是資料記錄的地址。下圖是 MyISAM 索引的原理圖：

• 這裡設表一共有三列，假設我們以 Col1 為主鍵，則上圖是一個 MyISAM 表的主索引（Primary key）示意。可以看出 MyISAM 的索引檔案僅僅儲存資料記錄的地址。

2. 輔助索引

在MyISAM中，主索引和輔助索引（Secondary key）在結構上沒有任何區別，只是主索引要求 key 是唯一的，而輔助索引的 key 可以重複。如果我們在 Col2 上建立一個輔助索引，則此索引的結構如下圖所示：

• 同樣也是一顆 B+Tree，data 域儲存資料記錄的地址。因此，MyISAM 中索引檢索的演算法為首先按照 B+Tree 搜尋演算法搜尋索引，如果指定的 Key 存在，則取出其 data 域的值，然後以 data 域的值為地址，讀取相應資料記錄。

• MyISAM 的索引方式也叫做“非聚集”的，之所以這麼稱呼是為了與 InnoDB 的聚集索引區分。

InnoDB 索引的實現

• 雖然 InnoDB 也使用 B+Tree 作為索引結構，但具體實現方式卻不相同。

1. 主鍵索引

與 MyISAM 第一個重大區別是 InnoDB 的資料檔案本身就是索引檔案。從上文知道，MyISAM 索引檔案和資料檔案是分離的，索引檔案僅儲存資料記錄的地址。而在 InnoDB 中，表資料檔案本身就是按 B+Tree 組織的一個索引結構，這棵樹的葉節點 data 域儲存了完整的資料記錄。這個索引的 key 是資料表的主鍵，因此 InnoDB 表資料檔案本身就是主索引。

上圖是 InnoDB 主索引（同時也是資料檔案）的示意圖，可以看到葉節點包含了完整的資料記錄。這種索引叫做聚集索引。因為 InnoDB 的資料檔案本身要按主鍵聚集，所以 InnoDB 要求表必須有主鍵（MyISAM 可以沒有），如果沒有顯式指定，則 MySQL 系統會自動選擇一個可以唯一標識資料記錄的列作為主鍵，如果不存在這種列，則 MySQL 自動為 InnoDB 表生成一個隱含欄位作為主鍵，這個欄位長度為 6 個位元組，型別為長整形。

2. 輔助索引

第二個與 MyISAM 索引的不同是 InnoDB 的輔助索引 data 域儲存相應記錄主鍵的值而不是地址。換句話說，InnoDB 的所有輔助索引都引用主鍵作為 data 域。例如，下圖為定義在 Col3 上的一個輔助索引：

• 這裡以英文字元的 ASCII 碼作為比較準則。聚集索引這種實現方式使得按主鍵的搜尋十分高效，但是輔助索引搜尋需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然後用主鍵到主索引中檢索獲得記錄。

• InnoDB 表是基於聚簇索引建立的。因此 InnoDB 的索引能提供一種非常快速的主鍵查詢效能。不過，它的輔助索引也會包含主鍵列，所以，如果主鍵使用過長的欄位，將會導致其他輔助索變得更大。如果想在表上定義 、很多索引，則爭取儘量把主鍵定義得小一些。InnoDB 不會壓縮索引。

• 更多內容請關注微信公眾號【碼猿技術專欄】

•