高效能MySQL-創建高效能的索引詳解（圖文）

本文是關於建立索引的的內容：
 （1） 索引的型別
 （2）索引的優點
（3）優化索引的策略

這裡給出一個索引的心智圖：

索引是儲存引擎用來快速找到記錄的一種資料結構。索引是對 查詢效能最佳化 最有效的手段了，索引能夠輕鬆將查詢效能提升幾個數量級。索引我們一般都是對某一列加索引。

儲存引擎先在索引中找到對應值，然後根據符合的索引記錄上的rowid找到對應的資料行。例如執行以下查詢語句：

SELECT first_name from actor where actor_id=5;

如果在actor_id欄位上建立有索引，MySQL會使用該索引找到actor_id 為5對應的行，也就是說，MySQL先在索引上按值查找，然後傳回所有包含該值的資料行。

索引可以包含一個或則多個列的值，如果索引包含多個列，那麼列的順序也十分重要，因為MySQL只能有效率的使用索引的最左前綴列。建立一個包含兩個欄位的索引和建立兩個包含一個欄位的索引是大不相同的。

1.索引資料結構的類型：

#索引最常見的是B-Tree索引和雜湊索引。

（1）B-Tree樹索引

一般情況下索引都是指B-Tree索引，它使用B-Tree資料結構來儲存資料。其實其實是基於B+Tree實現的，在每個葉子結點都包含一個指向下一個葉子結點的指標。

B-Tree意味著所有的值都是依照順序儲存的，例如對於name屬性，就是按照從a-z的順序儲存的。使用B-Tree索引後，儲存引擎不再需要進行全表掃描來獲取需要的數據，而是從索引的根節點開始進行搜索，最終結果是要么找到對應的值，要么記錄不存在。這樣就能夠加快存取資料的速度。

B-Tree對索引列是順序組織儲存的，所以很適合用來尋找範圍資料。 （例如查找I-k開頭的名字，這樣效率會很高）

B-Tree索引適合的查詢類型
（1） 全值匹配：和索引中的所有列進行匹配。

（2）符合最左前綴：對於一個索引包含多個列，只使用索引的第一列。

（3）符合列前綴：符合某一列的值的開頭部分。 （例如符合name欄位的時候，只符合以J開頭的名稱）這裡只用到了索引的第一列。

（4）符合範圍值：符合欄位在某一個範圍內的記錄，這裡只用到了索引的第一列。

（5）精確符合某一列並範圍符合另一列：對於一個索引包含多個欄位的情況，例如精確比對第一列，第二列範圍符合。

（6）只存取索引的查詢：之存取索引行而不存取記錄中其餘欄位的資料行。

上面的範圍匹配，主要是因為索引的按順序儲存索引列，導致的範圍匹配的高效性。

對於B-Tree的索引也有一些限制：
（1）索引只能從最左列開始查找

（2）如果查詢中有某個列的範圍查找，則其右邊所有欄位都無法使用索引優化。

看到上面的兩個限制應該可以明白關於索引中包含多個欄位的時候，索引列的順序是很重要的。

（2）Hash哈希索引

哈希索引是基於哈希表實現的，只有精確匹配索引所有列的查詢才有效。對於每一行資料儲存引擎都會所有的索引列計算一個雜湊碼，雜湊碼是一個較小的值，不同鍵值的行計算出來的雜湊碼也不一樣。

1）哈希索引只儲存哈希值和行指針，並不會儲存特定的字段值，所以一定會存在讀取行的過程。

2）哈希索引並不是按照索引值順序儲存的，所以就無法用於排序。

3）雜湊索引只支援等值比較查詢，不支援範圍比較查詢，這與雜湊表的特性與有關。

4）雜湊索引就存在雜湊衝突的問題，對於雜湊衝突的資料必須遍歷鍊錶中的所有行指標。

上面的這些限制，哈希索引只適合於特定的場合，但是一旦適合哈希索引，性能就會特別高。

使用hash索引時，一般情況下還要再查詢條件中帶上hash前的值，例如：

mysql>select * from words where crc=crc32(‘gnu’) and word=’gnu’;

这里crc字段就是word字段哈希之后的值，因为hash之后可能存在冲突，带上原本的值做上二次比较，就可以精确定位。

2.索引的优点：

索引可以让服务器快速定位到表的指定位置。但是这不是唯一的作用，比如：
（1）对于B-Tree索引，由于B-Tree是按照顺序存储数据的，所以用来做order by 操作或则是 group by操作的效率很高。

（2）因为索引中存储了实际的列值，所以某些查询只需要索引就可以完成全部查询。

总结来说就是3点：
（1）索引大大减少服务器需要扫描的数据量；

（2）索引可以帮助服务器避免排序和临时表；

（3）索引可以将随机IO变为排序IO。

3.高性能的索引策略

先概括一下索引的策略：
1）单列索引
2）多列索引
3）前缀索引
4）聚簇索引
5）覆盖索引

单列索引

所谓单列索引是指：使用数据表字段中的某一列作为索引。但是索引列不能是表达式的一部分，也不能是函数的参数。

比如对于下面的一个例子：

select actor_id from actor where actor_id+1=5;

对于这样的一个SQL，where语句后面 是一个表达式，其实很明显是actor_id=4的条件，但是MySQL却无法解析，索引无法正却使用索引。

还有一种是函数参数：也是无法正常的使用索引的

select ... where TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col)<=10;

多列索引以及选择合适的索引顺序

注意这里要区分：为每个列创建独立的索引和为多个列创建一个索引的区别。

比如下面这种情况：

CREATE TABLE t{
c1 int,
c2 int,
c3 int,key(c1),key(c2),key(c3)
}

这一种就是为表中的3个列都创建了索引。

但是多个列创建索引就是：创建了一个索引，包含customer_id,和staff_id

alter table payment add KEY(customer_id, staff_id);

上面这个索引其实是包含了两个索引，一个是customer_id这个索引，还有一个是(customer_id，staff_id)。注意staff_id并不能作为单独的索引使用。

---

对于多列索引，最重要的就是怎么选择索引列的顺序，其实这一点与实际的查询需求有关。主要是为了满足排序和分组。

先从数据结构层次来分析，我们知道索引是以B-Tree的形式存储的，在一个多列索引列中，索引的顺序意味着索引首先按照最左列进行排序，其次是第二列。所以对于一个多列索引，如果以第二列或则第三列直接作为索引，基本是没有用到索引。由于索引的有序性很好的满足了order by、group by和distinct等子句的查询需求。

从上面的分析我们就能认识到多列索引中列的顺序是多么重要。关于多列索引中有一点经验法则：
（1）在不需要考虑排序和分组时，通常情况下将选择性最高的列放在索引最前列。(这时候索引只需要优化where查询条件，能够很快过滤出需要的行)

索引列的选择性定义：不重复的索引值和数据表的记录总数的比值。索引的选择性越高也就是查询效率越高。比如对于人员信息表，phone这一字段的选择性是很高的，几乎为1，但是对于sex性别这一字段的选择性是非常低的，因为只有两个选择男或则是女，几乎为0。

（2）实际情况下也与数据的分布有很大关系。
以下面的查询为例：

SELECT * FROM item WHERE staff_id=2 AND customer_id=584;

这时候应该创建（staff_id, customer_id）的索引还是应该创建（customer_id，staff_id）的索引呢？这时候就应该确认一下那个字段的选择性更高，先查询一下staff_id和customer_id的总数，哪个小就将哪个放在前面。

前缀索引

前缀索引：有时候需要索引的列可能会很长，这时候会导致索引大而且很慢，我们可以只索引列开始的部分（也就是只索引某一列的前面几个字符），这样可以大大节省索引空间也能加快索引的速度，但是也会降低索引的选择性（也就是索引查出来的结果会变多）。

使用的技巧在于：选择足够长的前缀保证较高的选择性，同时又不能太长，避免占用太多的存储空间。

叢集索引

叢集索引並不是一種單獨的索引類型，而是一種資料儲存方式。這裡主要以InnoDB為例來說明叢集索引。

InnoDB中聚族索引其實是在同一個結構中保存額B-tree索引和資料行。當表中有聚族索引時，它的資料行實際上是存放在索引的葉子頁中。聚簇的意義其實就是資料行和相鄰的B-Tree中鍵值緊湊的儲存在一起。資料行只能存放在一個地方，所以叢集索引只能有一個。

下面以一個範例圖來說明：索引列是整數值，葉子頁包含了行的全部數據，但是結點頁只包含了索引列(下圖中的整數值)。

在目前為止的MySQL版本中，InnoDB的叢集索引也只支援使用主鍵來叢集資料。如果沒有定義主鍵，InnoDB會選擇一個唯一的非空索引來取代。

聚集簇的資料的優點：
（1）可以把相關資料保存在一起。例如查詢電子郵件郵件為例，以用戶ID為主鍵，透過用戶ID叢集數據，這樣只需要從磁碟讀取少量數據頁就能取得某個用戶全部郵件。

（2）資料存取更快。叢集索引將索引和資料保存在一個B-Tree中，因此從叢集索引中取得資料通常例如同索引查找的快。 （當然存在查找列就是索引列的情況）

（3）使用覆蓋索引掃描的查詢可以直接使用頁結點中的主鍵。

利用查詢和設計表時上面的優點能夠極大的提升性能​​，但是也有一些缺點：
（1）聚簇數據極大提升了IO密集型應用的性能，但是數據全部放在記憶體中，存取的順序就不重要了，叢集索引也就失去了優勢。

（2）插入速度嚴重依賴插入順序。

（3）更新叢集索引列的代價很高，會強制把InnoDB每個被更新的行移到新的位置。

覆寫索引

如果一個索引包含（或則說覆寫）所有需要查詢的欄位的值，我們就稱之為覆寫索引。

覆蓋索引是非常有用的工具，對於索引來說，只需要掃描索引就能在索引的葉子節點中獲得所有的數據，而不需要回表查詢，這就能極大的提高性能。帶來的好處也很多：

（1）索引條目通常遠小於資料行的大小，如果只需要讀取索引那MySQL就會極大的減少資料存取量，這對快取的負載非常重要。

（2）因為索引是按照列值順序儲存的，所以對於IO密集型的範圍查找會比隨機從磁碟中讀取每一行資料的Io要少得多、

使用索引掃描來做排序

MySQL有兩種方式可以產生有序的結果：
（1）透過order by 排序操作;

（ 2）依索引順序掃描；
如果explain出來的type值是index，則表示MySQL使用了索引掃描來做排序。

索引掃描本身很快，只需要從一筆記錄往下一筆記錄移動即可，但是如果索引列不能覆蓋所有查詢字段，那麼每次掃描一條索引記錄都要回表查詢一次，其效能還不如直接順序全表掃描。

盡可能設計同一索引即滿足排序又可用於查找。

4.索引的缺點

（1）對於insert、update、delete操作，需要同步更新索引，導致速度變慢。

（2）索引會佔用很大的儲存。

以上是高效能MySQL-創建高效能的索引詳解（圖文）的詳細內容。更多資訊請關注PHP中文網其他相關文章！