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

今天丸趣 TV 小編給大家分享一下如何為 MySQL 創建高性能索引的相關知識點，內容詳細，邏輯清晰，相信大部分人都還太了解這方面的知識，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來了解一下吧。

1 索引基礎 1.1 索引作用

在 MySQL 中，查找數據時先在索引中找到對應的值，然后根據匹配的索引記錄找到對應的數據行，假如要運行下面查詢語句：

SELECT * FROM USER WHERE uid = 5;

如果在 uid 在建有索引，則 MySQL 將使用該索引先找到 uid 為 5 的行，也就是說 MySQL 先在索引上按值進行查找，然后返回所有包含該值的數據行。

1.2 MySQL 索引常用數據結構

MySQL 索引是在存儲引擎層面實現的，不是在服務器實現的。所以，沒有統一的索引標準：不同存儲引擎的索引工作方式不一樣。

1.2.1 B-Tree

大多數的 MySQL 引擎都支持這種索引 B -Tree，即時多個存儲引擎支持同一種類型的索引，其底層實現也可能不同。比如 InnoDB 使用的是 B +Tree。

存儲引擎以不同的方式實現 B -Tree，性能也各有不同，各有優勢。如，MyISAM 使用前綴壓縮技術是的索引更小，當 InnoDB 則按照原數據格式進行存儲，MyISAMy 索引通過數據的物理位置引用被索引的行，而 InnoDB 根據組件應用被索引的行。

B-Tree 所有值都是順序存儲的，并且每一個葉子頁到根的距離相同。如下圖大致反應了 InnoDB 索引是如何工作的，MyISAM 使用的結構有所不同。但基本實現是類似的。

實例圖說明：

每個節點占用一個磁盤塊，一個節點上有兩個升序排序的關鍵字和三個指向子樹根節點的指針，指針存儲的是子節點所在磁盤塊的地址。兩個關鍵詞劃分成的三個范圍域對應三個指針指向的子樹的數據的范圍域。以根節點為例，關鍵字為 16 和 34，P1 指針指向的子樹的數據范圍為小于 16，P2 指針指向的子樹的數據范圍為 16~34，P3 指針指向的子樹的數據范圍為大于 34。查找關鍵字過程：

根據根節點找到磁盤塊 1，讀入內存。【磁盤 I/O 操作第 1 次】

比較關鍵字 28 在區間（16,34），找到磁盤塊 1 的指針 P2。

根據 P2 指針找到磁盤塊 3，讀入內存。【磁盤 I/O 操作第 2 次】

比較關鍵字 28 在區間（25,31），找到磁盤塊 3 的指針 P2。

根據 P2 指針找到磁盤塊 8，讀入內存。【磁盤 I/O 操作第 3 次】

在磁盤塊 8 中的關鍵字列表中找到關鍵字 28。

缺點：

每個節點都有 key，同時也包含 data，而每個頁存儲空間是有限的，如果 data 比較大的話會導致每個節點存儲的 key 數量變小；

當存儲的數據量很大的時候會導致深度較大，增大查詢時磁盤 io 次數，進而影響查詢性能。

1.2.2 B+Tree 索引

B+ 樹是對 B 樹的變種。與 B 樹區別：B+ 樹只在葉子節點存儲數據，非葉子節點只存儲 key 值及指針。

在 B + 樹上有兩個指針，一個指向根葉子節點，另一個指向關鍵字最小的葉子節點，而且所有葉子節點（即數據節點）之間是一種鏈式環結構，因此可以對 B + 樹進行兩種查找運算：一種是對于組件的范圍查找，另一種是從根節點開始，進行隨機查找。

B* 樹與 B + 數類似，區別在于 B * 數非葉子節點之間也有鏈式環結構。

1.2.3 Hash 索引

哈希索引基于哈希表實現，只有精準匹配索引所有列的查詢才有效。對于每一行數據，存儲引擎都會對所有的索引列計算一個哈希碼（hash code）, 哈希碼是一個較小的值，并且不同鍵值的行計算出來的哈希碼也不一樣。哈希索引將所有的哈希碼存儲在索引中，同時在哈希表中保存指向每個數據行的指針。

在 MySQL 中只有 Memory 默認索引類型就是使用的哈希索引，memory 也支持 B -Tree 索引。同時，Memory 引擎支持非唯一哈希索引，如果多個列的哈希值相同，索引會以鏈表的方式存放多個指針相同一個哈希條目中。類似 HashMap。

優點：
索引自身只需要存儲對應的哈希值，所以索引的結構十分緊湊，哈希所以查找的速度非常快。
缺點：

利用 hash 存儲的話需要將所有的數據文件添加到內存，比較耗費內存空間；

哈希索引數據并不是按順序存儲的，所以無法用于排序；

如果所有的查詢都是等值查詢，那么 hash 確實很快，但是在企業或者實際工作環境中范圍查找的數據更多，而不是等值查詢，因此 hash 就不太適合了；

如果哈希沖突很多的話，索引維護操作的代價也會很高，這也是 HashMap 后期通過增加紅黑樹解決 Hash 沖突的問題；

2 高性能索引策略 2.1 聚簇索引與非聚簇索引聚簇索引

不是單獨的索引類型，而是一種數據存儲方式，在 InnoDB 存儲引擎中聚簇索引實際在同一個結構中保存了鍵值和數據行。當表中有聚簇索引時，它的數據行實際上存放在索引的葉子頁中。因為無法同時把數據行存放在不同的地方，所以一個表中只能有一個聚簇索引（索引覆蓋可以模擬出多個聚簇索引的情況）。

聚簇索引優點：

可以把相關數據保存在一起; 數據訪問更快，因為索引和數據保存在同一個樹中; 使用覆蓋索引掃描的查詢可以直接使用頁節點中的主鍵值;

缺點：

聚簇數據最大限度地提高了 IO 密集型應用的性能，如果數據全部在內存，那么聚簇索引就沒有什么優勢；插入速度嚴重依賴于插入順序，按照主鍵的順序插入是最快的方式；更新聚簇索引列的代價很高，因為會強制將每個被更新的行移動到新的位置；基于聚簇索引的表在插入新行，或者主鍵被更新導致需要移動行的時候，可能面臨頁分裂的問題；聚簇索引可能導致全表掃描變慢，尤其是行比較稀疏，或者由于頁分裂導致數據存儲不連續的時候；

非聚簇索引

數據文件跟索引文件分開存放

2.2 前綴索引

有時候需要索引很長的字符串，這會讓索引變的大且慢，通常情況下可以使用某個列開始的部分字符串，這樣大大的節約索引空間，從而提高索引效率，但這會降低索引的選擇性，索引的選擇性是指：不重復的索引值（也稱為基數 cardinality）和數據表記錄總數的比值，范圍從 1 /#T 到 1 之間。索引的選擇性越高則查詢效率越高，因為選擇性更高的索引可以讓 mysql 在查找的時候過濾掉更多的行。

一般情況下某個列前綴的選擇性也是足夠高的，足以滿足查詢的性能，但是對應 BLOB,TEXT,VARCHAR 類型的列，必須要使用前綴索引，因為 mysql 不允許索引這些列的完整長度，使用該方法的訣竅在于要選擇足夠長的前綴以保證較高的選擇性，通過又不能太長。

舉例

表結構及數據 MySQL 官網或 GItHub 下載。

city Table Columns

字段名含義 city_id 城市主鍵 IDcity 城市名 country_id 國家 IDlast_update: 創建或最近更新時間

-- 計算完整列的選擇性
select count(distinct left(city,3))/count(*) as sel3,
 count(distinct left(city,4))/count(*) as sel4,
 count(distinct left(city,5))/count(*) as sel5,
 count(distinct left(city,6))/count(*) as sel6,
 count(distinct left(city,7))/count(*) as sel7,
 count(distinct left(city,8))/count(*) as sel8 
from citydemo;

可以看到當前綴長度到達 7 之后，再增加前綴長度，選擇性提升的幅度已經很小了。由此最佳創建前綴索引長度為 7。

2.3 回表

要理解回表需要先了解聚族索引和普通索引。聚族索引即建表時設置的主鍵索引，如果沒有設置 MySQL 自動將第一個非空唯一值作為索引，如果還是沒有 InnoDB 會創建一個隱藏的 row-id 作為索引（oracle 數據庫 row-id 顯式展示，可以用于分頁）；普通索引就是給普通列創建的索引。普通列索引在葉子節點中存儲的并不是整行數據而是主鍵，當按普通索引查找時會先在 B + 樹中查找該列的主鍵，然后根據主鍵所在的 B + 樹中查找改行數據，這就是回表。

2.4 覆蓋索引

覆蓋索引在 InnoDB 中特別有用。MySQL 中可以使用索引直接獲取列的數據，如果索引的葉子節點中已經包含要查詢的數據，那么就沒必要再回表查詢了，如果一個索引包含（覆蓋）所有需要查詢的字段的值，那么該索引就是覆蓋索引。簡單的說：不回表直接通過一次索引查找到列的數據就叫覆蓋索引。

表信息

CREATE TABLE `t_user` ( `uid` int(11) NOT NULL AUTO_INCREMENT,
 `uname` varchar(255) DEFAULT NULL,
 `age` int(11) DEFAULT NULL,
 `update_time` datetime DEFAULT NULL,
 PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

舉例

-- 將 uid 設置成主鍵索引后通過下面的 SQL 查詢   在 explain 的 Extra 列可以看到“Using index”explain select uid from t_user where uid = 1;

覆蓋索引在組合索引中用的比較多，舉例

explain select age,uname from t_user where age = 10 ;

當不建立組合索引時，會進行回表查詢

設置組合索引后再次查詢

create index index_user on t_user(age,uname);

2.5 索引匹配方式 2.5.1 最左匹配

在使用組合索引中，比如設置 (age,name) 為組合索引，單獨使用組合索引中最左列是可以匹配索引的，如果不使用最左列則不走索引。例如下面 SQL

-- 走索引
explain select * from t_user where age=10 and uname= zhang

下面的 SQL 不走索引

explain select * from t_user where uname= zhang

2.5.2 匹配列前綴

可以匹配某一列的值的開頭部分，比如 like abc%。

2.5.3 匹配范圍值

可以查找某一個范圍的數據。

explain select * from t_user where age

2.5.4 精確匹配某一列并范圍匹配另外一列

可以查詢第一列的全部和第二列的部分

explain select * from t_user where age=18 and uname like  zhang%

2.5.5 只訪問索引的查詢

查詢的時候只需要訪問索引，不需要訪問數據行，本質上就是覆蓋索引。

explain select age,uname,update_time from t_user 
 where age=18 and uname=  zhang  and update_time= 123

3 索引優化最佳實踐

1. 當使用索引列進行查詢的時候盡量不要使用表達式，把計算放到業務層而不是數據庫層。

-- 推薦
select uid,age,uname from t_user where uid=1;
-- 不推薦
select uid,age,uname from t_user where uid+9=10;

2. 盡量使用主鍵查詢，而不是其他索引，因為主鍵查詢不會觸發回表查詢

3. 使用前綴索引參考 2.2 前綴索引
4. 使用索引掃描排序 mysql 有兩種方式可以生成有序的結果：通過排序操作或者按索引順序掃描，如果 explain 出來的 type 列的值為 index, 則說明 mysql 使用了索引掃描來做排序。
掃描索引本身是很快的，因為只需要從一條索引記錄移動到緊接著的下一條記錄。但如果索引不能覆蓋查詢所需的全部列，那么就不得不每掃描一條索引記錄就得回表查詢一次對應的行，這基本都是隨機 IO，因此按索引順序讀取數據的速度通常要比順序地全表掃描慢。
mysql 可以使用同一個索引即滿足排序，又用于查找行，如果可能的話，設計索引時應該盡可能地同時滿足這兩種任務。
只有當索引的列順序和 order by 子句的順序完全一致，并且所有列的排序方式都一樣時，mysql 才能夠使用索引來對結果進行排序，如果查詢需要關聯多張表，則只有當 orderby 子句引用的字段全部為第一張表時，才能使用索引做排序。order by 子句和查找型查詢的限制是一樣的，需要滿足索引的最左前綴的要求，否則，mysql 都需要執行順序操作，而無法利用索引排序。
舉例表結構及數據 MySQL 官網或 GItHub 下載。

CREATE TABLE `rental` ( `rental_id` int(11) NOT NULL AUTO_INCREMENT,
 `rental_date` datetime NOT NULL,
 `inventory_id` mediumint(8) unsigned NOT NULL,
 `customer_id` smallint(5) unsigned NOT NULL,
 `return_date` datetime DEFAULT NULL,
 `staff_id` tinyint(3) unsigned NOT NULL,
 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`rental_id`),
 UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
 KEY `idx_fk_inventory_id` (`inventory_id`),
 KEY `idx_fk_customer_id` (`customer_id`),
 KEY `idx_fk_staff_id` (`staff_id`),
 CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
 CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
 CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4;

rental 表在 rental_date,inventory_id,customer_id 上有 rental_date 的索引。使用 rental_date 索引為下面的查詢做排序

-- 該查詢為索引的第一列提供了常量條件，而使用第二列進行排序，將兩個列組合在一起，就形成了索引的最左前綴
explain select rental_id,staff_id from rental 
where rental_date= 2005-05-25  order by inventory_id desc
-- 下面的查詢不會利用索引
explain select rental_id,staff_id from rental 
where rental_date 2005-05-25  order by rental_date,inventory_id

5. union all,in,or 都能夠使用索引，但是推薦使用 in

explain select * from actor where actor_id = 1 union all select * from actor where actor_id = 2;
explain select * from actor where actor_id in (1,2);
explain select * from actor where actor_id = 1 or actor_id =2;

6. 范圍列可以用到索引范圍條件是：、=、、=、between。范圍列可以用到索引，但是范圍列后面的列無法用到索引，索引最多用于一個范圍列。

7. 更新十分頻繁，數據區分度不高的字段上不宜建立索引

更新會變更 B + 樹，更新頻繁的字段建議索引會大大降低數據庫性能；

類似于性別這類區分不大的屬性，建立索引是沒有意義的，不能有效的過濾數據；

一般區分度在 80% 以上的時候就可以建立索引，區分度可以使用 count(distinct(列名))/count(*) 來計算；

8. 創建索引的列，不允許為 null，可能會得到不符合預期的結果

9. 當需要進行表連接的時候，最好不要超過三張表，如果需要 join 的字段，數據類型必須一致

10. 能使用 limit 的時候盡量使用 limit

11. 單表索引建議控制在 5 個以內

12. 單索引字段數不允許超過 5 個（組合索引）

13. 創建索引的時候應該避免以下錯誤概念

索引越多越好

過早優化，在不了解系統的情況下進行優化

4 索引監控

show status like  Handler_read%

參數說明 Handler_read_first 讀取索引第一個條目的次數 Handler_read_key 通過 index 獲取數據的次數 Handler_read_last 讀取索引最后一個條目的次數 Handler_read_next 通過索引讀取下一條數據的次數 Handler_read_prev 通過索引讀取上一條數據的次數 Handler_read_rnd 從固定位置讀取數據的次數 Handler_read_rnd_next 從數據節點讀取下一條數據的次數

以上就是“如何為 MySQL 創建高性能索引”這篇文章的所有內容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，丸趣 TV 小編每天都會為大家更新不同的知識，如果還想學習更多的知識，請關注丸趣 TV 行業資訊頻道。