共計 6979 個字符，預計需要花費 18 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

本篇文章為大家展示了 MySQL 中怎么實現高性能索引，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

什么是索引索引又可以稱為鍵 (key) 是存儲引擎用于快速找到記錄的一種數據結構。

索引是提高 MySQL 查詢性能最有效的手段，我們常說的 MySQL 性能調優基本都是對索引的優化。所以這是每個開發需要掌握并會應用的知識點。

索引是一種數據結構，它也是存儲在磁盤的一個文件。上一篇我們學習 MySQL 的邏輯架構的時候了解了 InnoDB 和 MyISM 存儲引擎，InnoDB 存儲引擎索引和數據是同一個文件，MyISAM 索引和數據是兩個獨立的文件。

在 MySQL 中，索引是在存儲引擎層實現的而不是 Server 層實現的，所以不同的存儲引擎的索引的工作方式是不一樣的。我們對索引的分析應該是建立在存儲引擎的基礎上的，InnoDB 是 MySQL 默認的存儲引擎。

索引的優點：

索引大大減少了服務器需要掃描的數據量。

索引可以幫助服務器避免排序和臨時表。

索引可以隨機 I / O 變為順序 I /O。

索引的缺點：

索引是數據結構，它占用了額外的磁盤空間。

當表數據量比較大時，維護索引的代價比較大。

索引數據模型

每個存儲引擎的數據結構和算法都是存在區別，我們先看下 MySQL 本身支持的索引類型。

B-Tree 索引

一般我們說的索引結構就是指 B -Tree 索引，MySQL 大部分的存儲引擎都支持這種索引，但是不同的存儲引擎以不同的方式使用 B -Tree 索引，性能也各有不同。InnoDB 使用的是 B +Tree，按照原有的數據格式進行存儲，根據主鍵引用被索引的行。

B-Tree 所有的值都是按順序存儲的，并且每一個葉子到根的距離相同。下圖是 B -Tree 的抽象圖：

B-Tree 能夠加快訪問數據的速度。

存儲引擎不需要全表掃描來獲取所需要的數據，它是從索引的根節點開始搜索。根節點的槽中存放指向子節點的指針，搜索引擎根據這些指針向下層查找。通過比較節點頁的值和要查找的值可以找到合適的指針進入到下層節點。最終引擎要么找到對應的值，要么該記錄不存在。

B-Tree 的索引如果多個列，索引值的排序是按照建表時定義的索引順序，所以索引的順序是比較重要的。

B-Tree 是 N 叉樹，N 的大小取決于數據塊的大小。

以 InnoDB 的一個整數字段索引為例，N 大概為 1200，當樹高是 4 的時候，就可以存 1200 的 3 次方的數據，大概為 17 億。一個擁有 10 億的表上一個整數字段的索引，查找一個值最多訪問 3 次磁盤。其實在應用時，如果第二層被提前加載到內存中，那么磁盤的訪問次數就更少了。

哈希索引

哈希索引是基于哈希表實現的，只有精確匹配所有列的查詢才有效。

對于每一行數據，存儲引擎都會對所有的索引列計算一個哈希碼(hash  code)，哈希碼是一個比較小的值，并且不同鍵值的行計算出來的哈希碼也不一樣。哈希索引將所有的哈希碼存儲在索引中，同時在 hash 表中保存指向每個數據行的指針。

創建表 test_hash，它的存儲引擎為 memory，索引為 full_name，索引類型為 hash。

CREATE TABLE `test_hash` ( `full_name` varchar(255) DEFAULT NULL, `short_name` varchar(32) DEFAULT NULL, `age` int(11) DEFAULT NULL, KEY `idx` (`full_name`) USING HASH ) ENGINE=MEMORY DEFAULT CHARSET=utf8;

表中的數據如下：

mysql  select * from test_hash; +-------------------+------------+------+ | full_name | short_name | age | +-------------------+------------+------+ | Dwayne Johnson | Johnson | NULL | | Taylor Swift | Taylor | NULL | | Leonardo DiCaprio | Leonardo | NULL | | Vin Diesel | Diesel | NULL | | Kobe Bryant | Kobe | NULL | +-------------------+------------+------+ 5 rows in set (0.00 sec)

那么哈希索引的數據結構可能是：

當我們執行查詢語句：

mysql  select short_name from test_hash where full_name =  Dwayne Johnson

這個 sql 語句的執行流程：

1)根據 where 條件 Dwayne Johnson 計算出哈希碼，那么得到的哈希碼為 1234。

2)MySQL 在索引中查找到 1234，并根據這個值找到了對應的行記錄指針。

3)根據指針地址找到對應的行，最后比較這個行中的 full_name 列是否為 Dwayne Johnson。

那現在有個問題，哈希碼沖突的時候怎么辦呢? 學過 HashMap 的小伙伴此時肯定靈機一動：哈希碼沖突的時候使用鏈表。對的，當鍵值的哈希碼沖突的時候，MySQL 也是使用的鏈表結構。如果是鏈表結構，在查找的時候就需要遍歷每個鏈表指針指向的行記錄做匹配，所以哈希沖突比較大的時候查找的效率是比較低的。

從上面的示例我們可以看出，哈希索引的結構中只存儲了哈希值，它的結構是比較緊湊的，對于精確查詢的效率是比較快的。

但是哈希索引還是有些限制的：

哈希索引中存儲的是鍵值的哈希值，它不是按照索引列的順序的，所以它不無法用于排序。

哈希索引不支持部分索引匹配查找，因為哈希索引始終是索引列的全部內容。如果我們索引有兩個列(A，B)，查詢的時候只想使用 A 列，這個時候是無法應用索引的。

哈希索引只支持等值查詢，比如 =、in 等，它不支持任何范圍查詢。

當哈希沖突的時候，存儲引擎必須要遍歷鏈表中的所有行指針，逐行比較，直到找到所有符合條件的行，如果哈希沖突比較多的時候，索引維護的代價比較高。

在 MySQL 中，目前只有 memory 引擎顯式支持哈希索引。

InnoDB 索引模型

我們前面提到，InnoDB 的索引結構是 B +Tee，它是以主鍵引用被索引的行。所以在 InnoDB 中，表都是根據主鍵順序以索引的形式存放的，每一個索引在 InnoDB 里面對應一棵 B + 樹。

B+Tree 索引

B+Tree 是我們前面提到的 B -Tree 的擴展，B-Tree 的每一個節點都包含了數據項，這樣每一塊磁盤存儲的索引值就會比較少，樹的高度就會變大，查詢的磁盤 I / O 次數就會增加。

那 B +Tree 是怎么樣的數據結構呢? 下圖是 B +Tree 的抽象圖：

B+Tree 與 B -Tree 的區別：

B+Tree 的非葉子節點不保存數據信息，只保存索引值和指向下一層節點的指針。

B+Tree 的葉子節點保存了數據

B+Tree 的葉子節點是順序排列的，并且葉子相鄰節點之間有指針的互相引用

B+Tree 能夠更好地配合磁盤的讀寫特性，減少單次查詢的磁盤訪問次數。

InnoDB 的索引類型分為主鍵索引和非主鍵索引。

主鍵索引和非主鍵索引

創建表 user，它的存儲引擎為 InnoDB，id 為主鍵，name 為普通索引。

CREATE TABLE `user` ( `id` int(10) NOT NULL, `name` varchar(32) DEFAULT NULL, `age` int(3) DEFAULT NULL, `sex` varchar(1) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `date` date DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx` (`name`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表中的數據如下:

mysql  select * from user; +----+-------+------+------+---------+------------+ | id | name | age | sex | comment | date | +----+-------+------+------+---------+------------+ | 1 | Alen | 20 | 1 | NULL | 2021-02-16 | | 2 | Alex | 21 | 1 | NULL | 2021-02-16 | | 3 | Saria | 16 | 0 | NULL | 2021-02-16 | | 4 | Semyt | 18 | 0 | NULL | 2021-02-16 | | 5 | Summy | 17 | 1 | NULL | 2021-02-16 | | 6 | Tom | 19 | 0 | NULL | 2021-02-16 | +----+-------+------+------+---------+------------+ 6 rows in set (0.00 sec)

主鍵索引也稱為聚簇索引，它的葉子節點都包含了主鍵值、事務 ID、用于事務和 MVCC 的回滾指針以及所有剩余的列。

mysql  select * from user where id = 1;

主鍵索引只需要搜索 ID 這棵 B +Tree 就可以拿到符合條件的行記錄。

InnoDB 是通過主鍵索引聚集數據，如果表中沒有定義主鍵，InnoDB 會選擇一個唯一的非空索引代替。如果沒有這樣的索引，InnoDB 會隱式定義一個主鍵來作為聚簇索引。這也是勾勾為每個表都創建主鍵的原因。

聚簇索引的優點：

把相關的數據保存在一起，減少了磁盤 I /O;

聚簇索引將數據和索引保存在同一顆 BTree 上，數據訪問更快;

聚簇索引的缺點：

如果數據都在內存中，聚簇索引的查詢性能就沒有那么好的優勢了。

插入的速度嚴重依賴于插入順序。盡量保證主鍵索引是有序的。

更新聚簇索引列的代價更高。

在插入行或者更新主鍵的時候導致需要移動行的時候可能導致頁分裂的問題。當插入到一個已滿的頁中，存儲引擎會將該頁分裂為兩頁來容納數據，頁分裂會導致占用更多的磁盤空間。

非主鍵索引也稱為非聚簇索引，在 InnoDB 中又被稱為二級索引。非主鍵索引的葉子節點內容是主鍵的值。

mysql  select * from user where name =  Alen

非主鍵索引查詢時，首先根據 name 普通查詢搜索 name 索引樹，找到 id 為 1，再根據 id= 1 到 ID 索引樹查詢一次才能獲取到符合條件的行記錄。

我們把先搜索普通索引樹得到主鍵，再搜索主鍵索引樹的過程稱為回表。

普通索引的查詢比主鍵索引多檢索了一棵 B +Tree，在實際應用場景下如果能用到主鍵索引盡量選擇主鍵索引。

在創建索引的時候還有其他的原則，我們接下來繼續學習高性能的索引策略。

索引策略

小伙伴們在學習索引策略的時候可以利用上一篇文章的 explian 關鍵字查詢執行計劃。

索引的選擇

索引的分類有多種，我們可以按照索引字段的個數將索引分為單列索引和聯合索引。

單列索引：一個索引只包含一個列，一個表中可以多個單列索引。

聯合索引：一個索引包含多個列。

我們還可以將索引分為普通索引、唯一索引和主鍵索引。

普通索引：基本的索引類型，常用來提高查詢效率，對數據沒有限制。允許在索引列中插入空值和重復值。

唯一索引：索引列中的值必須是唯一的，允許存在空值。

主鍵索引：不允許空值的特殊的唯一索引。

索引有這么多分類，我們在創建索引的時候如何選擇呢?

索引的三星系統：

一星：索引相關的記錄放到一起。

二星：索引中的數據順序和查找列中的順序一致。

三星：索引的列包含了查詢中需要的全部列。

正確的創建和使用索引是實現高性能查詢的基礎。索引的選擇沒有絕對的要求，主要是根據自己的業務需求，但是有些原則我們在創建索引的時候可以作為參考。

索引列的區分度越高則查詢效率越高。

將頻繁搜索的列加入索引，可以提高搜索效率。

索引不只提高了查詢效率，也可以參與排序和分組，經常用來排序和分組的字段也需考慮加入索引。

創建索引時，應將區分度高的字段排在前面。即需要注意索引字段的順序。

索引列不能參與任何運算。

避免創建重復索引，即在同一個列上按照相同的順序創建相同類型的索引。

對于從未使用的索引，應盡量刪除。

對于 blob、text 或者長 varchar 類型的列，必須要使用前綴索引，取最夠長的前綴來保證較高的區分度。

普通索引和唯一索引在查詢效率上差別并不大，因為引擎是按照頁讀取數據。對于唯一索引在查詢的時候只要找到就不再繼續比較了，因為索引已經保證了唯一性。而對于普通索引則在找到滿足條件的記錄后還需要繼續查找直到找到不滿足條件的第一條記錄，但是對于按照頁讀取數據的引擎來說，多一次的判斷對性能的影響較小。普通索引和唯一索引的選擇除了保證業務的準確性之外，其他更多的考慮更新數據時對性能的影響。

獨立的列

”獨立的列“是指索引不能是表達式的一部分，也不能是函數的參數。

例如，如下 sql 語句，在查詢時索引字段 name 參與了函數運算，會導致索引失效，全表掃描。

mysql  select * from user where CONCAT(name, n) =  Alen

添加索引 age 字段，如果我們在查詢的時候對 age 字段進行了運算也會導致索引失效：

mysql  select * from user where age + 1 = 21;

我們平時開發中要養成簡化 where 條件的習慣，始終使用單獨的索引列。

覆蓋索引

如果我們把按照普通索引查詢的 sql 語句修改如下:

mysql  select name from user where name like  Al%

這時只需要查詢普通索引樹即可得到要查詢的列，因為要查詢的列已經在索引樹了，而不需要再回表查詢。

這種索引字段覆蓋了我們需要查詢的結果字段的場景我們稱為覆蓋索引。

覆蓋索引可以減少回表，減少索引樹的搜索次數，顯著提高查詢性能，所以覆蓋索引是一個比較好的優化策略。

在實際開發中，可以按照業務需要把一些常用的檢索字段添加到索引中，利用覆蓋索引提高查詢效率，但是有些場景下不能為了使用覆蓋索引而過多的維護索引，畢竟索引的維護成本也是很高的。

最左前綴

這個時候我們還需要思考一個問題，在業務場景中我們的查詢是多樣化的，不能為了使用索引而為每一種場景都設計一個索引吧?

這個時候我們就要利用 B +Tree 樹索引結構的另外一個特性最左前綴。

最左前綴可以是聯合索引的最左的幾個字段，也可以是字符串索引的最左的幾個字符。

創建聯合索引(name,age)，順序一致。

此時執行 sql 語句：

mysql  select * from user where name =  Alen

雖然是聯合索引，但是 name 字段排在第一位，也是可以命中索引的。

mysql  select * from user where name like  Al%

如果使用 name 索引字段的最左 N 個字符串，也是可以命中索引的。但是如果我們使用 %Al 是不能命中索引的。

如果我們使用如下的 sql 查詢語句：

mysql  select * from user where age =  16；

雖然 age 也是聯合索引的字段，但是他的順序在 name 之后，直接使用 age 查詢無法命中索引。所以創建聯合索引時一定要考慮索引字段的順序。

索引維護時有一個原則：如果能通過調整索引順序，可以少維護一個索引，那么就需要優先調整順序而不是增加索引。

MySQL 可以利用同一個索引進行排序和掃描行，但是只有當索引的列順序和 order  by 子句的順序完全一致，并且列的排序方向都一致 (正序或者倒序) 時，MySQL 才能使用對結果進行排序。

order by 子句和查詢類型限制是一樣的，也需要滿足”最左前綴“的原則，否則 MySQL 無法利用索引排序。

索引下推

當我們的查詢語句不滿足最左前綴的時候會如何呢?

比如我們查詢名字第一個字為 A，年齡為 20，并且性別為 1(男)的人員信息，sql 語句如下：

mysql  select * from user where name like  A%  and age = 20 and sex = 1 ;

按照我們前面學習的最左前綴原則，按照 rsquo;A lsquo; 先搜索到第一個滿足條件的主鍵 1，然后回表查詢判斷其他的兩個條件是否滿足。

MySQL5.6 之后引入了索引下推的優化，即會按照索引中包含的字段優先過濾，減少回表的次數。

我們上述的 sql 語句在 MySQL5.6 之前會回表 2 次分別對比主鍵 1 和 2 兩條的數據的其他條件是否滿足，但是引入索引下推的優化之后 age =  20 這個條件不滿的會直接過濾掉，只需要對主鍵 1 回表一次就可以獲取到結果。

上述內容就是 MySQL 中怎么實現高性能索引，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注丸趣 TV 行業資訊頻道。

向 AI 問一下細節