本文是關于創建索引的的內容： ?（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意味著所有的值都是按照順序存儲的，比如對于nameMySQL，就是按照從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）覆蓋索引

單列索引

所謂單列索引是指：使用數據表字段中的某一列作為索引。但是索引列不能是MySQL的一部分，也不能是MySQL。

比如對于下面的一個例子：

select?actor_id?from?actor?where?actor_id+1=5;

對于這樣的一個SQL，where語句后面 是一個表達式，其實很明顯是actor_id=4的條件，但是MySQL卻無法解析，索引無法正卻使用索引。

還有一種是MySQL：也是無法正常的使用索引的

select?...?where?TO_DAYS(CURRENT_DATE)?-?TO_DAYS(date_col)<h2><span style="color:#0099ff;font-size:24px;font-family:微軟雅黑">多列索引以及選擇合適的索引順序</span></h2><p>注意這里要區分：為每個列創建獨立的索引和為多個列創建一個索引的區別。</p><p>比如下面這種情況：</p><pre class="brush:sql;toolbar:false;">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）索引會占用很大的存儲。