MySQL索引的型別

techcoder發表於2019-03-31
MySql索引型別

前言

MySQL索引是面試中最常見的問題，筆者前幾天接到一個HR小姐姐的面試電話，小姐姐說公司實行996，問我能不能接受?，我沒996過，我哪裡知道996是什麼感覺呀啊，我就敷衍的說了一句應該可以吧，然後我回到家仔細想了想，還是委婉的拒絕了面試的邀請。

最有趣的是第一次接受HR小姐姐的技術面試，問了我一堆MySQL索引的問題，這是要鬧哪樣？被一個HR面試了一堆技術問題，我表示不服(開個玩笑)，側面也反映了公司估計確實很忙，HR需要通過技術問題過濾掉一部分簡歷，這樣就可以減少開發的時間成本。

然鵝，上週突然github上的一個996-icu的repository突然就火了，截止到目前已經11K的star了。就是抗議現在很多的公司實行的996政策，很多公司被口誅筆伐，我也看了許多關於996的討論，我總結的關鍵點是：開發覺得強制996讓他們不爽，把996當成了理所應當讓他們不爽。他們覺得如果公司忙可以接受加班，而且大部分的開發如果事情沒做完，都會自覺加班。我個人表示贊同這些觀點。

額，說了那麼多廢話，現在就開始介紹些MySQL索引的型別，其中部分內容參考了《MySQL高效能》。

MySQL索引的型別

索引的型別有多種，可以為不同的場景提供更好的效能。在MySQL中，索引是在儲存引擎層工作的。所以沒有統一的索引標準，不同的索引工作方式是不同的，也不是所有的引擎都支援所有的索引型別。以下五種是很常用的索引型別，我們就來分析他們的優點和缺點。

索引優化應該是對查詢效能優化最有效的手段了。

B-Tree索引

當人們討論索引的時候，如果沒有特殊說明指的就是B-Tree索引，大多數MySQL引擎都是支援這種索引的。不同的MySQL引擎實現B-Tree索引的方法略有不同，各有優勢。例如，MyISAM使用字首壓縮技術使得索引的空間更小，但InnoDB則是按照原資料進行儲存。再比如MyISAM索引是通過資料的實體地址引用被索引的行，而InnoDB則是通過主鍵欄位引用被索引的行。

B-Tree索引能大大加快查詢的效率。因為查詢過程不需要要全表掃描所有的行，只需要從根節點向下搜尋，比改節點小的就向左子節點查詢，否則就向右子節點查詢，最終就能快速的找到要查詢的值。

假設有如下資料表：

  CREATE TABLE People(
    last_name varchar(20) not null,
    first_name varchar(20) not null,
    dob date null,
    gender enum('m', 'f') not null,
    key(last_name, first_name, dob)
  );
複製程式碼

對於表中的每一行資料，索引包含了last_name, first_name, dob列的值，如下是該索引的儲存結構圖。

索引樹中部分條目示例

索引樹中部分條目示例

需要注意的是對於多列索引，是根據CREATE TABLE時定義索引的順序來決定的，例如我們上面建立的索引，在節點上是按照先last_name然後first_name最後dob欄位來儲存的，上圖右下角當last_name和first_name相等時，是按照生日的日期來排序的。

可以使用B-Tree索引的查詢型別

  • 全值查詢

    全值查詢是指匹配所有的索引列。例如前面提到的索引可以用於查詢姓名為Cuba Allen、出生於1960-01-01的人。

  • 匹配最左字首查詢

    匹配最左字首查詢是指按照從左到右的順序匹配索引欄位，例如前面提到的索引用於查詢姓為Allen的人。

  • 匹配列字首查詢

    匹配列字首，就是匹配某個列最開頭的部分。例如前面提到的索引可用於查詢所有以Ja開頭的姓。具體查詢語句。

    SELECT * FROM People WHERE last_name Like 'Ja%';
複製程式碼

  • 匹配範圍查詢

    範圍查詢顧名思義就是查詢一段範圍，例如前面提到的索引可用於查詢姓Allen到Astaire之間的所有人。

  • 匹配某一列並範圍匹配到另一列

    前面提到的索引也可以查詢所有姓為Allen，並且名字以字母K開頭（例如Kim、Karl等）。即第一列lsit_name全匹配，第二列first_name列字首匹配。

  • 只訪問索引的查詢

    B-Tree支援“只訪問索引的查詢”，即查詢的時候只需要訪問索引，無需訪問資料行。這種查詢方式叫“覆蓋索引”，簡單來說覆蓋索引避免了回表查詢資料行的開銷，提高了查詢效率，感興趣的同學可以自行搜尋“覆蓋索引“的相關知識。

關於B-Tree索引使用的一些限制

  • B-Tree索引如果不是從最左邊的列開始查詢，則無法使用索引。例如前面例子中索引無法查詢名字為Kim的人，以及也無法查詢某個特定生日的人，因為這兩列不是最左列。類似也無法查詢姓氏以某個字母結尾的人。

  • 不能跳過索引的列，前面提到的索引無法查詢姓為Allen且生日為1960-01-01的所有人，如果是這樣的查詢語句，只能用到索引的第一列。

  • 如果查詢中有某個列用到了範圍查詢，則其右邊的所有列均不能用到索引優化查詢。例如這個查詢語句：

    SELECT * FROM People WHERE last_name='Allen' AND first_name like 'K%' AND dob='1930-07-12';
複製程式碼

上面的查詢語句只能用到索引的前兩列，因為第二個查詢條件是一個範圍查詢。如果範圍查詢的條件是有限的，可以通過多個等值條件查詢來代替。

小結

通過上面一系列的講解，我們可以發現，建立索引的順序簡直太重要了，查詢條件的順序也是非常的重要。我們在工作中建立MySQL表的時候一定要注意避開這些坑。我們會發現，由於業務的變化，我們其實需要回頭對原來建立表的結構進行優化，這些優化就包括對索引結構的優化，

雜湊索引

雜湊索引（hash index）是基於雜湊表實現的，只有精確等值匹配索引所有列的查詢才是有效的。在MySQL中只有Memory引擎支援雜湊索引，這也是Memory引擎預設的索引型別，下面看一個例子。假設有如下表：

CREATE TABLE testhash(
  fname varchar(50) not null,
  lname varchar(50) not null,
  KEY USING HASH(fname)
)ENGINE=MEMORY;
複製程式碼

插入資料：

INSERT INTO testhash(fname, lname) values('Arjen', 'Lentz'),('Baron', 'Schwartz'),('Peter', 'Zitsevo'), ('Vadim', 'Tachenkoe');
複製程式碼

表資料如下

fname lname
Arjen Lentz
Baron Schwartz
Peter Zaitsev
Vadim Tkachenko

假設有雜湊函式f()，他返回如下資料（雜湊值都是示例資料）：

f('Arjen') = 2098

f('Baron') = 4920

f('Peter') = 8372

f('Vadim') = 5293

雜湊索引的儲存結構如下：

槽（Slot） 值（value）
2098 指向第1行
4920 指向第2行
5293 指向第4行
8372 指向第3行

現在我們來看如下查詢：

SELECT * FROM testhash WHERE fname='Peter';
複製程式碼

上面的查詢語句MySQL先計算'Peter'的雜湊值，並使用該值尋找到對應的指標記錄，最後一步就是要比較查詢的結果是否為'Peter'。雜湊索引只需要儲存對應的雜湊值，所以雜湊索引結構比較緊湊，這也使得雜湊索引的查詢速度非常的快。然而雜湊索引也有一些限制：

  • 雜湊索引儲存的是行指標，而沒有儲存行資料，所以每次查詢無法避免讀取行。
  • 雜湊索引不是按照索引順序儲存的，所以無法用於排序。
  • 雜湊索引不支援部分索引列匹配查詢，因為雜湊索引儲存是全部索引列來計算雜湊值的。
  • 雜湊索引只能用於等值查詢，不支援任何的範圍查詢。
  • 如果雜湊衝突很多的話，一些索引的維護成本就會很高。

因為以上這些限制，雜湊索引只適用於某些特定的應用場景，而一旦使用雜湊索引，則它帶來的效能提升是非常明顯的。

空間資料索引(R-Tree)

MyISAM表支援空間索引，可以用於地理資料的儲存。和B-Tree索引不同，這類索引無需字首查詢。空間索引會從各個維度來索引資料。查詢時，可以有效的使用任意維度的組合來查詢。必須使用MySQL的GIS相關的的函式，如MBRCONTAINS()等來維護資料。MySQL的GIS函式還不夠完善，所以大部分人都不會使用這個特性。開源資料庫中對GIS的解決方案做的比較好的是PostgreSQL的PostGIS。

全文索引

全文索引是一種特殊的索引方式，他查詢的是文字中的關鍵詞，一般使用者關鍵詞檢索的業務場景，全文索引更像是類似於搜尋引擎做的事。全文索引在實際工作中用的非常的少，一般資料量稍大一點的關鍵詞檢索都用elasticsearch這種專門提供搜尋引擎的框架。

相關文章