MySql索引總結

索引概念

B+樹索引分為聚集索引和非聚集索引（輔助索引），但是兩者的數據結構都和B+樹一樣，區別是存放的內容。

可以說資料庫必須有索引，沒有索引則檢索過程變成了順序查找，O(n)的時間複雜度幾乎是不能忍受的。我們非常容易想象出一個只有單關鍵字組成的表如何使用B+樹進行索引，只要將關鍵字存儲到樹的節點即可。當資料庫一條記錄里包含多個欄位時，一棵B+樹就只能存儲主鍵，如果檢索的是非主鍵欄位，則主鍵索引失去作用，又變成順序查找了。這時應該在第二個要檢索的列上建立第二套索引。 這個索引由獨立的B+樹來組織。有兩種常見的方法可以解決多個B+樹訪問同一套表數據的問題，一種叫做聚簇索引（clustered index ），一種叫做非聚簇索引（secondary index）。這兩個名字雖然都叫做索引，但這並不是一種單獨的索引類型，而是一種數據存儲方式。對於聚簇索引存儲來說，行數據和主鍵B+樹存儲在一起，輔助鍵B+樹只存儲輔助鍵和主鍵，主鍵和非主鍵B+樹幾乎是兩種類型的樹。對於非聚簇索引存儲來說，主鍵B+樹在葉子節點存儲是指向真正數據行的指針，並且輔助B+樹在葉子節點存儲也是指向真正數據行的指針，而非主鍵B+樹。

InnoDB使用的是聚簇索引，將主鍵組織到一棵B+樹中，而行數據就儲存在葉子節點上，若使用"where id = 14"這樣的條件查找主鍵，則按照B+樹的檢索演算法即可查找到對應的葉節點，之後獲得行數據。若對Name列進行條件搜索，則需要兩個步驟：第一步在輔助索引B+樹中檢索Name，到達其葉子節點獲取對應的主鍵。第二步使用主鍵在主索引B+樹種再執行一次B+樹檢索操作，最終到達葉子節點即可獲取整行數據。

InnoDB存儲引擎是索引組織表，也就是說數據文件本身就是按照B+樹方式存放數據的。Innodb 聚集索引是按照主鍵（primary key）進行聚集，被索引的列其實是主鍵列，如果沒定義主鍵，Innodb會試著使用唯一非空索引Unique Index來代替，如果還找不到，Innodb就會自動創建一個6位元組作為隱藏主鍵然後在上面進行索引聚集。除了主鍵的聚集索引，其他索引(輔助索引)中不會保存行的物理位置，而是保存主鍵的值，所以通過"二級索引"進行查找是先找到主鍵，再找到行，要進行二次索引查找。在InnoDB存儲引擎中，將B+樹索引分為聚集索引和非聚集索引（輔助索引），但是兩者的數據結構都和B+樹一樣，區別是存放的內容。無論是何種索引，每個頁的大小都是16KB，且不能改變。

MyISM使用的是非聚簇索引，非聚簇索引的兩棵B+樹看上去沒什麼不同，節點的結構完全一致只是存儲的內容不同而已，主鍵索引B+樹的節點存儲了主鍵，輔助鍵索引B+樹存儲了輔助鍵。表數據存儲在獨立的地方，這兩顆B+樹的葉子節點都使用一個地址指向真正的表數據，對於表數據來說，這兩個鍵沒有任何差別。由於索引樹是獨立的，通過輔助鍵檢索無需訪問主鍵的索引樹。

圖示清晰的顯示了聚簇索引和非聚簇索引的差異。

Cardinality

並不是所有在查詢條件中出現的列都需要添加索引，對於什麼時候添加B+樹索引，一般的檢驗是，在訪問表中很少一部分行是使用B+樹索引才有意義。查看索引是否是高選擇性的，可以通過SHOW INDEX語句中的Cardinality列來觀察。Cardinality是一個估計值，在實際中，Cardinality/n_rows_in_table應儘可能接近1，如果非常小，那麼需要考慮是否還要建這個索引。

InnoDB中如何統計Cardinality值，索引的更新可能非常平凡，如果每次更新操作時就統計Cardinality值，那麼對資料庫會帶來很大負擔。InnoDB存儲引擎中，Cardinality統計發生在INSERT和UPDATE操作中，不過並不會每次都去統計，它的策略是：

• 表中的1/16的數據已發生變化。
• stat_modified_counter > 2000000000。

stat_modified_counter是資料庫發生INSERT和UPDATE操作的計數器，如果每次對錶中的一行數據更新操作，表中的數據幾乎不會有變化，那麼第一種策略就無法試用。所以計數器用來統計操作的次數，如果滿足條件，也會統計Cardinality值。

InnoDB存儲引擎只對8個葉節點進行採樣。採樣的過程為：

1. 取得B+樹索引中葉節點的數量，即為A。
2. 隨機取B+樹索引中的8個節點。統計每頁中不同的記錄的個數，即為P1，P2，...，P8。
3. 根據採樣的信息給出Cardinality值預估值：Cardinality = （P1+P2+...+P8）* A / 8。

註意：如果表足夠小，表的葉節點小於或等於8個，這時即使隨機採樣，也總是採取到這些頁，因此每次的Cardinality值都是一樣的。

B+樹索引的使用

不同應用中B+樹索引的使用

資料庫存在兩種類型的應用：OLTP和OLAP應用。OLTP是傳統關係型資料庫的主要應用，其主要面向基本的、日常的事務處理，例如銀行交易。OLAP是數據倉庫系統的主要應用，支持複雜的分析操作，側重決策支持，並且提供直觀易懂的查詢結果。

在OLTP應用中，查詢操作一般只從資料庫中取得一小部分數據，在這種情況下，建立B+樹索引後，優化器就會使用索引。對於OLAP應用，一般需要訪問表中的大量數據，並根據這些數據來產生查詢的結果，而這些查詢多是分析的查詢，目的是為決策者提供支持。但是對於LOAP中的複雜查詢，需要涉及多表之間的聯接操作，這是索引的添加是有意義的，但是聯接操作使用Hash Join，那麼索引可能又變得不是非常重要。不過在OLAP應用中，通常需要對時間欄位進行索引，這是因為大多數統計需要根據時間維度來進行判斷。

聯合索引

聯合索引是指對錶上的多個列進行索引，又叫複合索引。對於複合索引:Mysql從左到右的使用索引中的欄位，一個查詢可以只使用索引中的一部份，但只能是最左側部分。例如索引是key index (a,b,c)。可以支持a | a,b | a,b,c 3種組合進行查找，但不支持b，c進行查找。當最左側欄位是常量引用時，索引就十分有效。查詢使用索引的條件不同一般組合索引需要按照“最左首碼”來執行查詢，並不是每個列都需要覆蓋，只是從左邊的列開始組合。在選擇組合索引的時候，當前Query 中過濾性最好的欄位在索引欄位順序中排列越靠前越好。

例如有索引key(a,b,c)

• where a=xx and b=xx and c=xxx 此語句可以用到索引
• where b=xx and a=xx and c=xxx 同上，順序沒有關係，同樣能用到索引
• where a=xx and b=xx 可以用到索引
• where a=xx and c=xx 可以用到索引
• where b=xx and c=xx 用不到索引
• where b=xx 用不到索引
• where c=xx 用不到索引

覆蓋索引

InnoDB存儲引擎支持覆蓋索引，即從輔助索引就可以得到查詢的記錄，而不需要查詢聚集索引中的記錄。使用覆蓋索引的好處是輔助索引不包含整行記錄的所有信息，故其大小要遠小於聚集索引，因此可以減小大量的IO操作。

對於InnoDb存儲引擎的輔助索引而言，由於其中包含了主鍵信息，因此其葉子節點放的數據為（primary key1， primary key2，...， key1， key2，...）。對於下麵的可以僅用一次輔助聯合索引來完成查詢。

• SELECT key2 FROM table key1 = xxx;
• SELECT primary key1, key2 FROM table WHERE key1 = xxx;
• SELECT primary key1, primary key2, key2 FROM table WHERE key1 = xxx;

簡單的說，如果查詢的列，正好是我們設置的主鍵或者鍵（建立聚合索引的鍵），如上圖展示的，輔助索引的葉子節點的值就是所要查詢的內容，就不必取到鍵值後再去聚合索引進行二次查找。可以想到如果想要更多的使用覆蓋索引這一特性，需要將我們需要的列，都建立聯合索引。

聯合索引和覆蓋索引有很大的區別：

覆蓋索引是查詢的列可以直接通過索引提取，比如只查詢主鍵的列！或者查詢聯合索引的所有列或者左邊開始的部分列（註意有順序的）！

而聯合索引並不一定只從索引中能獲取到所有的數據，這個取決於你所查詢的列。比如select * from table where ××××××;的方式就不太可能是覆蓋索引。因此如果你查詢的列能用到聯合索引，且你查詢的列都能通過聯合索引獲取，比如你只查詢聯合索引所在的列或者左邊開始的部分列，這就相當於覆蓋索引了。通常為了讓查詢能用到覆蓋索引，就將要查詢的多列數據設置成聯合索引。

優化器不使用（輔助）索引的情況

在某些情況下，優化器並沒有選擇輔助索引去查找數據，而是通過掃描聚集索引，也就是直接進行全表的掃描來得到數據。

SELECT * FROM orderdetails WHERE orderid>10000 and orderid<102000;

通過SHOW INDEX FROM orderdetails可以看到可以使用的索引包括PRIMARY、OrderID、OrderOrder_Details三個索引，正常情況下，應該選擇OrderID輔助索引開始查詢，但是優化器直接選擇PRIMARY聚集索引。

原因在於我們要選取的數據是整行信息，而OrderID索引不能覆蓋到我們要查詢的信息（覆蓋索引），因此在對OrderID索引進行查詢到指定數據的操作後，還需要進行一次書簽訪問來查找整行信息。雖然在輔助索引中數據是順序存放的，但是再一次的書簽查找數據是無序的，因此變為了磁碟上的離散讀取操作。如果要訪問的數據量很小，那麼優化器還是會選擇輔助索引，但是當訪問的數據占整個表中數據的很大一部分時（一般是20%左右），優化器會選擇通過聚集索引來查找數據。

INDEX HINT

MYSQL資料庫支持INDEX HINT（索引提示），顯示的告訴優化器使用哪個索引，在以下情況下可能需要用到INDEX HINT：

• MYSQL資料庫的優化器錯誤的選擇了某個索引，導致MYSQL語句運行很慢，情況少數。
• 某SQL語句可以選擇的索引非常多，這時優化器選擇執行計劃時間的開銷就可能會大於SQL語言本身。通過INDEX HINT可以強制優化器使用某個索引，直接執行選擇指定的索引來完成索引。

USE INDEX

SELECT * FROM t USE INDEX(a) WHERE a=1 AND b=2;

使用USE INDEX可以告訴優化器可以選擇該索引，但是不是強制使用該索引。實際的使用優化器還是會根據自己的判斷進行選擇。

FORCE INDEX

SELECT * FROM t FORCE INDEX(a) WHERE a=1 AND b=2;

使用FORCE INDEX可以指定優化器選擇某個索引進行查詢。

T樹索引

對於MYSQL資料庫的NDB CLuster記憶體存儲引擎，在使用它時可將其視為記憶體資料庫。在記憶體資料庫中，一般使用T樹作為其索引的數據結構。T樹是有平衡二叉樹和B樹發展而來。T樹的好處是節點不存放數據，只存放指針，這樣能減少記憶體的使用，這對記憶體資料庫來說是很重要的。同時T樹也是一棵平衡二叉樹，以此保證查找的性能。

哈希索引

當前MYSQL資料庫中，Memory存儲引擎支持哈希索引。InnoDB存儲引擎支持自適應哈希索引，用戶僅能開啟該特性，不能對其進行人工干預。通過參數innodb_adaptive_hash_index來禁用或啟動此特性，預設為開啟。

轉載請註明出處。
作者：wuxiwei
出處：http://www.cnblogs.com/wxw16/p/6131132.html