MySQL 索引結構

rickiyang發表於2020-08-25
MySql索引

談到 MYSQL 索引服務端的同學應該是熟悉的不能再熟悉，新建表的時候怎麼著都知道先來個主鍵索引，對於經常查詢的列也會加個索引加快查詢速度。那麼 MYSQL 索引都有哪些型別呢？索引結構是什麼樣的呢？有了索引是如何檢索資料的呢？我們圍繞這些問題來探討一下。

你認為應該如何查詢資料

上一節談到 InnoDB 引擎的時候聊過在 InnoDB 引擎是面向行儲存的，資料都是儲存在磁碟的資料頁中，資料頁裡面按照固定的行格式儲存著每一行資料。

InnoDB儲存引擎是 B+ 樹索引組織的，所以資料即索引，索引即資料。B+ 樹的葉子節點儲存的都是資料段的資料。InnoDB 引擎對資料的儲存必須依賴於主鍵，主鍵對應的索引叫做聚集索引。如果不幸的是你建表沒有建主鍵，InnoDB 會從表欄位中尋找第一個非空的唯一索引作為聚集索引，如果還是不幸找不到，InnoDB 會生成一個不可見的名為 ROW_ID 的列，該列是一個 6 位元組的自增數字，用來建立聚集索引。

小Tips:

對於 ROW_ID 列的自增實現其實是來自於一個全域性自增序列，這意味著所有使用到 ROW_ID 作為聚集索引的表都共享該序列，如果在高併發的情況就有保證不了唯一性的可能。

大家都知道 MYSQL 中索引是使用 B+ 樹的資料結構，在此也就不故弄玄虛。但是大家有沒有想過除了 B+ 樹還有什麼資料結構也可以用於索引檢索呢？我們不妨來看看。

二叉樹

對於二叉樹而言，每個節點只能有兩個子節點，如果是一顆單邊二叉樹，查詢某個節點的次數與節點所處的高度相同，時間複雜度為O(n)；如果是一顆平衡二叉樹，查詢效率高出一半，時間複雜度為O(Log2n)。

並且二叉樹還有另一個壞處，二叉樹上的每一個節點都是資料節點，那麼對於一個比較高的數如果要獲取最下面的資料遍歷的節點數將會很消耗效能。

Hash 表

雜湊表的好處是雜湊查詢單條資料比較快，但是壞處也比較多，比如 Hash 碰撞的解決，範圍查詢等等。

B 樹

B樹是二叉樹的升級版，又叫平衡多路查詢樹。它和平衡二叉樹的區別在於：

  1. 平衡二叉樹最多兩個子樹，而 B 樹每個節點都可以有多個子樹，M 階 B 樹表示每個節點最多有M個子樹。
  2. 平衡二叉樹每個節點只有一個資料和兩個指向孩子的指標，而 B 樹每個中間節點有 k-1 個關鍵字（可以理解為資料）和 k 個子樹（ k 介於階數 M 和 M/2 之間，M/2 向上取整）。
  3. 所有葉子節點均在同一層、葉子節點除了包含關鍵字和關鍵字記錄的指標外也有指向其子節點的指標，只不過其指標地址都為 null 。

另外，它們相同的點是節點資料也是按照左小右大的順序排列。我們用一張圖來對比它們的區別：

1

B+ 樹

說到 B 樹就連著B+樹一起說了。B+ 樹是應檔案系統所需而產生的一種 B 樹的變形樹（檔案的目錄一級一級索引，只有最底層的葉子節點（檔案）儲存資料）非葉子節點只儲存索引，不儲存實際的資料）。

4

這裡有一個很重要的一點就是 B+ 樹的非葉子節點不儲存資料，只有索引。一棵 m 階的 B+ 樹和 m 階的 B 樹的異同點在於：

  1. 節點的子樹數和關鍵字數相同（B 樹是關鍵字數比子樹數少一）；
  2. 所有的葉子結點中包含了全部關鍵字的資訊，及指向含有這些關鍵字記錄的指標（節點的關鍵字表示的是子樹中的最大數，在子樹中同樣含有這個資料），且葉子結點本身依關鍵字的大小自小而大的順序連結。 (而 B 樹的葉子節點並沒有包括全部需要查詢的資訊)；
  3. 所有的非終端結點可以看成是索引部分，結點中僅含有其子樹根結點中最大（或最小）關鍵字。 (而 B 樹的非終節點也包含需要查詢的有效資訊)。
  4. 葉子節點之間通過指標連線。

對於 B 樹和 B+ 樹來說，兩種資料結構都是為了減少磁碟 I/O 讀寫過於頻繁而生，本身節點的個數是有限的，採用多叉結構就是為了讓每一層放盡可能多的節點以此來降低整棵樹的高度。但是為什麼 InnoDB 索引結構最終選擇了 B+ 樹而不是B 樹呢？

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

B+ 樹內部非葉子節點本身並不儲存資料，所以非葉子節點的儲存代價相比 B 樹就小的多。儲存容量減少同時也縮小了佔用盤塊的數量，那麼資料的聚集程度直接也影響了查詢磁碟的次數。

B+ 樹查詢效率更加穩定

樹高確定的前提下所有的資料都在葉子節點，那麼無論怎麼查詢所有關鍵字查詢的路徑長度是固定的。

B+ 樹對範圍查詢的支援更好

B+ 樹所有資料都在葉子節點，非葉子節點都是索引，那麼做範圍查詢的時候只需要掃描一遍葉子節點即可；而 B 樹因為非葉子節點也儲存資料，範圍查詢的時候要找到具體資料還需要進行一次中序遍歷。

MyISAM 和 InnoDB 索引組織的區別

在 MYSQL 中索引屬於儲存引級別的概念，儲存引擎不同，索引的實現方式也不一樣。我們分別看看看 MyISAM 和 InnoDB 中都是如何實現索引功能。

MyISAM 實現

MyISAM 也是使用 B+ 樹作為索引儲存結構，他的葉子節點 data 域存放的是資料的實體地址，即索引結構和真正的資料結構其實是分開儲存的。

2

InnoDB 索引實現

MyISAM 索引和資料是分離的，但是在 InnoDB 中卻大不相同，InnoDB 中採用主鍵索引的方式，所有的資料都儲存在主鍵索索引中。

3

所以這也是為什麼 InnoDB 要求每個表都必須要有主鍵的原因。本身就是基於主鍵來組織的資料儲存。

索引型別

以下所有索引型別都是基於 InnoDB 引擎。

主鍵索引

主鍵索引也就是我們說的聚集索引。上面說過主鍵索引是基於主鍵來建立的 B+ 樹索引結構，如果沒有指定主鍵，也找不到任何一列不重複的列可以作為主鍵的情況下，InnoDB 會新增一個隱藏列 RowId 作為主鍵繼而建立聚集索引。

二級索引(非主鍵索引)

二級索引就是指除了主鍵索引外的索引。主鍵索引和所有的二級索引都是各自維護各自的 B+ 樹結構，但是有個不同的地方在於，二級索引的葉子節點儲存的不是資料，而是主鍵索引對應的主鍵值。

即二級索引不再儲存一份 data 資料，而是去主鍵索引中查資料。那麼對於二級索引查詢一條資料索要做的操作就是：

  1. 首先在二級索引中找到葉子節點對應的資料主鍵值；
  2. 根據這個主鍵值去聚集索引中找到真正對應的資料行。

所以這裡需要兩次 B+ Tree 查詢。

覆蓋索引

覆蓋索引簡單來說就是隻查詢索引就能獲取到資料不必再回表查詢，換句話說要查詢的列已經被索引列覆蓋。

使用覆蓋索引有如下優點：

  1. 索引項通常比記錄要小，所以 MySQL 訪問更少的資料；
  2. 索引都按值的大小順序儲存，相對於隨機訪問記錄，需要更少的 I/O；
  3. 大多資料引擎能更好的快取索引。比如 MyISAM 只快取索引;
  4. 覆蓋索引對於 InnoDB 表尤其有用，因為 InnoDB 使用聚集索引組織資料，如果二級索引中包含查詢所需的資料，就不再需要在聚集索引中查詢了。
  5. 覆蓋索引不能是任何索引，只有 B Tree 索引儲存相應的值。而且不同的儲存引擎實現覆蓋索引的方式都不同，並不是所有儲存引擎都支援覆蓋索引( Memory 和 Falcon 就不支援)。
聯合索引

有的時候我們會對多個列建立一個索引，這種索引被稱為聯合索引。而關於聯合索引的建立和使用，從工作開始你的各位 “師長” 都在教導你要遵循 “左前匹配原則”，那到底是為什麼呢？什麼是左前匹配原則呢？

比如我們有這樣一張表：

CREATE TABLE `test_tb` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `a` varchar(10) NOT NULL,
  `b` varchar(10) NOT NULL,
  `c` varchar(10) NOT NULL,
  `d` int(10) NOT NULL,
  `e` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_a_b_c` (`a`,`b`,`c`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

上表建立了一個聯合索引：idx_a_b_c。下面給出一個 SQL， 大家看它會不會走索引查詢：

select  *  from test_tb where b = '10';

很顯然根據 “左前匹配原則” 肯定不會走索引查詢，最終還是全表掃描。

原因就在於聯合索引的結構上。上面對 a,b,c 三個欄位建立索引，那麼對應的 B+ Tree 索引結構每個節點其實是按照三個欄位的前後順序排列的，即 a 欄位檢索在最前面，然後是b，然後是c。如果你的查詢不是按照這個順序來檢索，是不會被這個索引識別的。

左前匹配原則

上面說到聯合索引會遵循左前匹配原則，那麼什麼是左前匹配呢？

其實就是字面意義上的從建立索引的第一個欄位開始先匹配查詢條件，如果當前查詢條件不是第一個欄位那麼就不會走該索引。

另外對於聯合索引的使用也有一些限制，比如說：

遇到範圍查詢 ( > ,<, between, like) 就會停止匹配

比如哦我們看這個 SQL：

select * from test_tb where a = '1' and b = '3' and d < 20 and c = '5';

大家覺得這個 SQL 會如何使用索引呢？

其實這 SQL 在前面a，b的查詢中是會走聯合索引的，但是在經歷了d的查詢之後，到了c就不會使用索引了，因為d的查詢已經將索引的順序打亂了，從 d 條件過後就沒有辦法直接使用聯合索引。

在索引列上做操作（函式，自定義計算）

同樣對索引列做計算也是無法直接應用索引，不言自明，索引是對已有的資料進行歸納排序，你計算之後的資料是新的內容，索引並沒有包含這些資料，無從查起。

查詢條件包含 or，可能導致索引失效

比如有一張 user 表：

CREATE TABLE `user` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `userId` int(11) NOT NULL,  
  `age` int(11) NOT NULL,  
  `name` varchar(255) NOT NULL,  
  PRIMARY KEY (`id`),  
  KEY `idx_userId` (`userId`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如下查詢語句：

select * from user where userId=3 or age > 20;

大家覺得這句會走索引嗎？

我們可以分析一下，如果 userId 查詢走了索引這沒問題，但是遇到 age > 20 這肯定沒法走索引，那麼前面 userId 走索引做一次 索引掃描就沒有意義，所以優化器認為這種情況下不如一開始就走全表掃描更省事。

但是如果 or 前後的兩個欄位都加了索引那麼可能會走，這種要依情況而定。

字串型別的索引欄位作為查詢條件時一定要用引號括起來，否則索引不生效

上面我們沒有說的一點是，B+Tree 索引結構的 key 都是用數字表示的，因為數字比較省空間，就算是字串格式的欄位，最終也會被轉為二進位制表示。但是對於不加引號的字串，MSYQL 會自動做一次隱式轉換將字串轉為浮點型別，這就導致不匹配。

like 萬用字元可能導致索引失效

使用 like 模糊查詢並不是所有的都會失效，只有以 “%” 開頭的 like 查詢才會失效。

左連線查詢或者右連線查詢查詢關聯的欄位編碼格式不一樣，可能導致索引失效。

這個比較不常見，一般來說同一個庫中的表使用的編碼格式應該是一樣的，但是不排除老專案新老表有區別。

索引的缺點

上面一直在談論索引的優點，凡事有利就有弊，它也不是沒有缺點的：

  • 磁碟空間佔用。這個對於當前磁碟比買菜還便宜的硬體大通貨時代其實算不上問題，但是要注意的是如果當前 MySQL 服務所在的機器有很多的大表，並且還建立了每一種可能的組合的索引，那麼索引檔案提及的增長可能超乎你的想象。

  • 維護索引對更新類操作所帶來的耗時。當對索引涉及到的列做更新或者新增操作時都會去維護相關的索引，這裡也是一個耗時的點，所以索引不在多，而在精。

檢查一條 SQL 是否是 bad SQL - 執行計劃

在 MySQL 中如何知道一條 sql 到底有沒有用到索引呢？MySQL 提供了 explain 關鍵字來查詢一條 sql 的執行效率。

比如我們有一張 user 表：

CREATE TABLE `user` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `userId` int(11) NOT NULL,  
  `age` int(11) NOT NULL,  
  `name` varchar(255) NOT NULL,  
  PRIMARY KEY (`id`),  
  KEY `idx_userId` (`userId`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查詢下面 sql 的查詢效率：

mysql> explain select * from user where id = 3;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | user  | const  | PRIMARY    | PRIMARY | 4    | const |     1 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.04 sec)

執行計劃各個欄位的含義如下：

列名 含義
id 執行序號，MSYQL 會按照從大到小的順序執行
select_type 查詢型別：SIMPLE: 簡單查詢
PRIMARY: 外層查詢
SUBQUERY: 子查詢
DERIVED: 派生查詢（FROM 中包含的子查詢）
UNION: UNION 中第二個或後面的那個查詢
UNION RESULT: UNION 的結果
table 引用的表
partitions 所屬分割槽
type 訪問型別官方文件，常見訪問型別：
system : 只有一條記錄的表（=系統表）
const : 通過索引一次就查詢到
eq_ref : 唯一索引等值掃描
ref : 非唯一索引等值掃描
range : 範圍索引掃描
index : 索引掃描
all : 全表掃描
possible_keys 可能使用的索引（優化前）
key 實際使用的索引（優化後）
key_len 使用索引的長度
ref 上述表的連線匹配條件（哪些列或常量被用於查詢索引列上的值）
rows 必須掃描的行數
Extra 附加資訊官方文件，常見附加資訊：
Using filesort : mysql 無法利用索引完成排序操作
Using temporary : 使用了臨時表儲存中間結果
Using index : select 操作使用了覆蓋索引
Using where : 使用 where 過濾
using join buffer : 使用了連線快取
impossible where : where 子句的值總是 false，不能用來獲取任何記錄
distinct : 優化 distinct，在找到第一個匹配的記錄後停止掃描同樣值的動作

這麼多欄位我們挑幾個重點來解釋一下：

id

執行序號，id 列的編號是 select 的序列號，有幾個 select 就有幾個 id，並且 id 的順序是按select 出現的順序增長的。id 越大執行優先順序越高，id 相同則從上往下執行，id 為 NULL 最後執行。

key_len

key_len 長度表示在索引裡使用的位元組數，通過這個值可以估算出具體使用了索引中的哪些列。

ken_len 計算規則如下：

  • 字串 ：char(n)：n 位元組長度； varchar(n)：n 位元組儲存字串長度，如果是 utf-8， 則長度是 3n+2，這裡的長度與字符集有直接關係；
  • 數值型別：tinyint：1 位元組；smallint：2 位元組 ；int：4 位元組； bigint：8位元組；
  • 時間型別 ：date：3位元組；timestamp：4位元組；datetime：8位元組。

如果欄位允許為 NULL，需要 1 位元組記錄是否為 NULL； 索引最大長度是 768 位元組，當字串過長時，MySQL 會做一個類似做字首索引的處理，將前半部分的字串提取出來做索引。

type

type 顯示的是訪問型別，結果值從好到壞依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般來說，得保證查詢至少達到 range 級別，最好能達到 ref。

下面對這幾個型別簡要說明：

  • system
    該表只有一行（如：系統表）。這是 const 連線型別的特例。

  • const
    該表最多隻有一個匹配行，在整個查詢過程中這個表最多隻會有一條匹配的行，用到了 primary key 或者unique 索引。

    比如主鍵查詢肯定只有一條記錄被匹配到。

  • eq_ref
    對於前面表格中的每個行組合，從該表中讀取一行。除了 system 和 const 型別之外，這是最好的連線型別。當連線使用索引的所有部分且索引是 索引 PRIMARY KEY 或 UNIQUE NOT NULL 索引時使用它。

    SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

  • ref
    表示上述表的連線匹配條件，即哪些列或常量被用於查詢索引列上的值。

  • fulltext
    使用 FULLTEXT 索引執行連線。

  • ref_or_null

    該連線型別如同 ref，但是新增了 MySQL 可以專門搜尋包含 NULL 值的行。

    SELECT * FROM ref_table WHERE key_column IS NULL;

  • index_merge
    該連線型別表示使用了索引合併優化方法。

    SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

  • unique_subquery
    此型別替換 以下形式的 eq_ref 某些 IN子查詢：

    value IN (SELECT primary_key FROM single_table WHERE some_expr)

  • index_subquery
    此連線型別類似於 unique_subquery。它替換 IN 子查詢，但它適用於以下形式的子查詢中的非唯一索引：

    value IN (SELECT key_column FROM single_table WHERE some_expr)

  • range
    給定範圍內的檢索，使用一個索引來檢查行。通常發生在在索引列上使用範圍查詢，如 >，<，in 等時，非索引列是 ALL。

  • index
    按索引次序掃描，先讀索引，再讀實際的行，結果也是全表掃描，主要優點是避免了排序。（索引是排好序的，並且 all 是從硬碟中讀的，index 可能不在硬碟上。s

  • ALL
    對前面表格中的每個行組合進行全表掃描。如果表是第一個未標記的表 const，通常不好，並且在所有其他情況下通常 非常糟糕。通常，您可以ALL通過新增基於常量值或早期表中的列值從表中啟用行檢索的索引來避免

row

這一列是 MYSQL 估計要讀取並檢測的行數，注意這個不是結果集的行數。

Extra

Extra 是 EXPLAIN 輸出中另外一個很重要的列，該列顯示 MySQL 在查詢過程中的一些詳細資訊。

  • Distinct：MySQL 發現第 1 個匹配行後，停止為當前的行組合搜尋更多的行。
  • Not exists：MySQL 能夠對查詢進行 LEFT JOIN 優化，發現1個匹配 LEFT JOIN 標準的行後，不再為前面的的行組合在該表內檢查更多的行。
  • range checked for each record：MySQL 沒有發現好的可以使用的索引，但發現如果來自前面的表的列值已知。可能部分索引可以使用。
  • Using filesort：看到這個的時候，查詢就需要優化了。MySQL 需要進行額外的步驟來發現如何對返回的行排序。它根據連線型別以及儲存排序鍵值和匹配條件的全部行的行指標來排序全部行。
  • Using index：從只使用索引樹中的資訊而不需要進一步搜尋讀取實際的行來檢索表中的列資訊。
  • Using temporary：為了解決查詢，MySQL 需要建立一個臨時表來容納結果。看到這個就需要進行優化了，這通常發生在對不同的列集進行 order by 上，而不是 group by 上。
  • Using where：WHERE 子句用於限制哪一個行匹配下一個表或傳送到客戶。
  • Using sort_union| Using union|Using intersect：這些函式說明如何為 index_merge 聯接型別合併索引掃描。
  • Using index for group-by：類似於訪問表的 Using index 方式，Using index for group-by 表示 MySQL 發現了一個索引，可以用來查詢 GROUP BY 或 DISTINCT 查詢的所有列，而不要額外搜尋硬碟訪問實際的表。

瞭解了執行計劃，當你不確定一條 sql 查詢效率的時候 就可以使用 Explain 來檢視。

相關文章