為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

--計算完整列的選擇性
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>18;

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%';

到此這篇關於為MySQL建立高效能索引的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。