高性能MySQL(3)——創建高性能索引

索引對于良好的性能非常關鍵。尤其是當表中的數據量越來越大時，索引對性能的影響愈發重要。

一、索引的類型

在MySQL中，索引是在存儲引擎層而不是服務器層實現的。所以沒用統一的索引標準，不同存儲引擎的索引工作方式并不相同。

1.1、B-Tree

B-Tree索引即使用B-Tree數據結構來存儲數據。B-Tree通常意味著所有值都是按順序存儲的，并且每個葉子頁到根的距離相同。存儲引擎已不同的方式來使用B-Tree索引，性能也各不相同。

可以使用B-Tree索引的查詢類型——全鍵值、鍵值范圍和鍵前綴查找。其中鍵前綴查找只適用于根據最左前綴查找。

1.2、哈希索引

哈希索引基于哈希表實現，只有精確匹配索引的所有列的查詢才有效。在MySQL中，只有Memory引擎顯示支持哈希索引，這也是Memory引擎的默認索引類型。

對于每一行數據，存儲引擎都會對所有的索引列計算一個哈希碼，如果多個列的哈希碼相同，索引會以鏈表的方式存放多個記錄指針到同一個哈希條目中。哈希索引將所有的哈希碼存儲在索引中，同時在哈希表中保存指向每個數據行的指針。

1.3、全文索引

全文索引是一種特殊類型的索引，它查找的是文本中的關鍵詞，而不是直接比較索引中的值。全文搜索和其他幾類索引的匹配方式完全不一樣。它有許多需要注意的細節，如停用詞、詞干和復數、布爾搜索等。全文索引更類似于搜索引擎做的事情，而不是簡單的WHERE條件匹配。

在相同的列上同時創建全文索引和基于值的B-Tree索引不會有沖突，全文索引適用于 MATCH AGAINST操作，而不是普通的WHERE條件操作。

1.4、其他索引類別

還有很多第三方的存儲引擎使用不同類型的數據結構來存儲索引。例如TokuDB使用分形樹索引(fractal tree index),這是一類較新開發的數據結構，既有B-Tree的很多優點，也避免了 B.Tree的一些缺點。如果通讀完本章，可以看到很多關于InnoDB的主題，包括聚簇索引、覆蓋索引等。多數情況下，針對InnoDB的討論也都適用于TokuDB。

二、索引的優點

索引可以讓服務器快速地定位到表的指定位置。但是這并不是索引的唯一作用，到目前為止可以看到，根據創建索引的數據結構不同，索引也有一些其他的附加作用。總結下來索引的三大優點：

索引大大減少了服務器需要掃描的數據量；
索引可以幫助服務器避免排序和臨時表；
索引可以將隨機I/O變為順序I/O。

索引是最好的解決方案嗎？

索引并不總是最好的工具。總的來說，只有當索引幫助存儲引擎快速查找到記錄帶來的好處大于其帶來的額外工作時，索引才是有效的。對于非常小的表，大部分情況下簡單的全表掃描更高效。對于中到大型的表，索引就非常有效。但對于特大型的表，建立和使用索引的代價將隨之增長。這種情況下，則需要一種技術可以直接區分出查詢需要的一組數據，而不是一條記錄一條記錄地匹配。

三、高性能的索引策略

3.1、獨立的列

索引列不能是表達式的一部分，也不能是函數的參數。

例如：SELECT actor_id FROM actor WHERE actor_id + 1 = 5;

或者：SELECT actor_id FROM actor WHERE f(actor_id) = 5;

3.2、前綴索引和索引選擇性

有時候需要索引很長的字符列，這會讓索引變得很大且很慢。此時可以有兩個策略，一個是自定義哈希索引，另一個就是前綴索引。

前綴索引能大大節約索引空間，從而提高索引效率，但這樣也會降低索引的選擇性（索引選擇性——不重復的索引值和數據表記錄總數的比值）；
索引前綴長度的選擇——計算法。例如：LELECT COUNT(DISTINCT city)/COUNT() AS sel1, COUNT(DISTINCT LEFT(city, 3))/COUNT() AS sel2, …; 如果前綴的選擇性接近sel1就可以使用了。有時候只看平均選擇型也不靠譜，還需要做進一步判斷。
缺點：MySQL無法使用前綴索引做ORDER BY和GROUP BY，也無法使用前綴索引做覆蓋掃描；
有時候也可以使用前綴索引——可將對應列的字符串反序存儲，并創建前綴索引。

3.3、多列索引

為多列創建合適的索引

多列索引。例如：key(col1, col2, col3)；
MySQL5.0之后的版本引入了“索引合并”的策略，一定程度上可以使用表上的多個單列索引來定位表中的行；
索引合并策略有時候是一種優化后的結果，但實際上更說明表上的索引建得很糟糕。
- 當出現服務器對多個索引做相交操作時（多個AND），通常意味著需要一個包含相關列的多列索引，而不是多個獨立的單列索引;
- 當服務器需要對多個索引做聯合操作時（多個OR），通常需要耗費大量的CPU和內存在算法的緩存、排序和合并上。

3.4、選擇合適的索引順序

正確的索引順序依賴于使用該索引的查詢，并且同時需要考慮如何更好的滿足排序和分組的需要；
索引可以按照升序或者降序進行掃描，以滿足精確符合列順序的ORDER BY 、GROUP BY和DISTINCT等子句的查詢需求；
索引列順序的選擇——在不考慮分組和排序的情況下，將選擇性最高的列放到索引最前面（經驗法則）；
避免隨機I/O和排序；
對于某些特殊用戶和分組，避免其使用普通的索引查詢。

3.5、聚簇索引

聚簇索引就是按照每張表的主鍵構造一顆B+樹，同時葉子節點中存放的就是整張表的行記錄數據，也將聚集索引的葉子節點稱為數據頁。這個特性決定了索引組織表中數據也是索引的一部分，每張表只能擁有一個聚簇索引。

Innodb通過主鍵聚集數據，如果沒有定義主鍵，innodb會選擇非空的唯一索引代替。如果沒有這樣的索引，innodb會隱式的定義一個主鍵來作為聚簇索引。

聚簇索引的優點：

數據訪問更快，因為聚簇索引將索引和數據保存在同一個B+樹中，因此從聚簇索引中獲取數據比非聚簇索引更快
聚簇索引對于主鍵的排序查找和范圍查找速度非常快

聚簇索引的缺點：

插入速度嚴重依賴于插入順序，按照主鍵的順序插入是最快的方式，否則將會出現頁分裂，嚴重影響性能。因此，對于InnoDB表，我們一般都會定義一個自增的ID列為主鍵
更新主鍵的代價很高，因為將會導致被更新的行移動。因此，對于InnoDB表，我們一般定義主鍵為不可更新。
二級索引訪問需要兩次索引查找，第一次找到主鍵值，第二次根據主鍵值找到行數據。

3.6、覆蓋索引

通常開發人員會根據查詢的where條件來創建合適的索引，但是優秀的索引設計應該考慮到整個查詢。其實mysql可以使用索引來直接獲取列的數據。如果索引的葉子節點包含了要查詢的數據，那么就不用回表查詢了，也就是說這種索引包含（亦稱覆蓋）所有需要查詢的字段的值，我們稱這種索引為覆蓋索引

3.7、使用索引掃描排序

MySQL有兩種方式可以生成有序結果：通過排序操作；按照索引順序掃描。

只有當索引的列順序和ORDER BY子句的順序完全一致，并且所有列的排序方向（升序/降序）都一樣時，MySQL才能使用索引來對結果做排序；
當查詢需要關聯多張表時，只有當ORDER BY子句引用的字段全部來自第一張表時，才能使用索引排序；
ORDER BY子句中的字段需要滿足索引的最左前綴的要求，才能使用索引排序；
當索引的前導列為常量時，ORDER BY子句可以不滿足索引的最左前綴要求也能使用索引排序。例如：key(rental_date, inventory_id, customer_id)；… where rental_data=‘2018-01-08’ ORDER BY inventory_id DESC;

四、維護索引和表

維護表有三個目的：找到并修復損壞的表；維護準確的索引統計信息；減少碎片

4.1、更新索引統計信息

MySQL的查詢優化器會通過兩個API來了解存儲引擎的索引值的分布信息，已決定如何使用索引信息。

records_in_range();
info()。如果存儲引擎向優化器提供的索引統計信息不準確，就會導致優化器做出錯誤的優化決定，這會嚴重影響查詢性能。可通過執行ANALYZE TABLE 來重新生成統計信息以解決這個問題。

4.2、減少索引和數據的碎片

B-Tree索引可能會碎片化，碎片化的索引可能會以很差或無序的方式存儲在磁盤上，這會降低查詢效率；
表數據存儲也可能碎片化。主要有行碎片、行間碎片、剩余空間碎片三種。對于MyISAM表，這三類碎片都可能發生，但InnoDB不會出現短小的行碎片，InnoDB會移動短小的行，并重寫到一個片段中。
【維護方法】可通過執行POTIMIZE TABLE或者導出再導入來重新整理數據；對于那些不支持POTIMIZE TABLE命令的引擎，可以執行ALTER TABLE操作來重建表。只需要將表的存儲引擎改為當前的引擎即可。例如：ALTER TABLE <table> ENGINE=<engine>;