四種基本類型的mysql索引

• B-Tree 索引

B-Tree索引是Mysql數(shù)據(jù)庫中使用最為頻繁的索引類型，除了存儲引擎之外的所有存儲引擎都支持B-Tree.
B-Tree索引的物理文件大多都是以Balance Tree的結(jié)構(gòu)來存儲，也就是所有實際需要的數(shù)據(jù)節(jié)點都存放于Tree的leaf Node；
而且到任何一個Leaf Node的最短路徑的長度都是完全相同的；

【Innodb-Primary Key索引存放】
在Innodb中，Clustered形式存放的PK索引葉子節(jié)點存放的是表的實際數(shù)據(jù)，不僅僅包含主鍵的數(shù)據(jù)，還包含其他字段的數(shù)據(jù)；
整個數(shù)據(jù)以主鍵值有序的排列；

【Innodb-Secondary Index索引存放】
在Innodb中，Secondary Index的葉子節(jié)點存放的是查詢數(shù)據(jù)的主鍵信息；
所以，在Innodb中通過Secondary Index查找響應(yīng)的索引鍵索引到葉子節(jié)點后，再通過葉子節(jié)點中存放的主鍵信息來索引響應(yīng)的
數(shù)據(jù)行；

【MyISAM-Secondary Index索引存放】
MyISAM中的主鍵索引和非主鍵索引的差別非常小，只不過是主鍵的索引鍵是一個唯一且非空的鍵而已；
只不過葉子節(jié)點上存放的是對應(yīng)數(shù)據(jù)的數(shù)據(jù)行信息（如Row number）,但并不會存放主鍵的鍵值信息。

• Hash 索引

主要是Memory存儲引擎使用，而且是Memory存儲引擎默認的索引類型；
Hash索引是將索引鍵用過Hash運算之后，將Hash運算結(jié)果的Hash值和所對應(yīng)的行指針信息存放于一個Hash表中；
Hash檢索效率非常高，索引的檢索可以一次定位，所以hash索引的效率要遠高于B-Tree;

【hash索引的缺陷】
1.不能使用范圍查詢
2.hash索引無法在組合索引中使用部分索引；
3.無法避免表掃描，在hash索引中，存在非唯一索引；
  所以獲取滿足某個Hash鍵值的記錄條數(shù)，都無法直接從Hash索引中直接完成查詢，
  還是要通過訪問表中的實際數(shù)據(jù)進行相應(yīng)的比較而得到相應(yīng)的結(jié)果；
4.Hash索引無排序，因為hash運算后的值不一定與運算之前一樣；所以在使用hash索引有排序的情況，無法通過索引
  避免排序操作；
5.Hash索引遇到大量Hash值相等的情況后，性能不一定比B-Tree索引高；

• Fulltext 索引

只有Myisam支持全文索引（Fulltext）,也并不是所有的數(shù)據(jù)類型都支持全文索引，只有char，varchar,TEXT這三種類型的列
可以使用Full-text索引；
Full-text索引主要是來替代效率地下的LIKE "%***%"操作。

• R-Tree 索引

R-Tree索引是用來解決空間數(shù)據(jù)檢索的問題的；

使用索引的益處

• 加快檢索，降低檢索過程中的數(shù)據(jù)讀取量
• 降低數(shù)據(jù)的排序成本（索引中的索引數(shù)據(jù)都是按照索引鍵鍵值進行排序后存放的）

使用索引的弊端

• 降低寫性能，增加了IO量和調(diào)整索引所致的計算量（在更新列操作時，需要額外的也更新對應(yīng)的索引數(shù)據(jù)）
• 帶來存儲空間資源消耗的增長（增加索引，同時也增加了占用空間）

增加索引的幾條基本判斷策略

• 較頻繁的作為查詢條件的字段應(yīng)該創(chuàng)建索引；
• 唯一性太差的字段不適合單曲創(chuàng)建索引，及時頻繁作為查詢條件；

經(jīng)驗之談：當(dāng)一條Query所返回的數(shù)據(jù)超過了全表的15%的時候，就不應(yīng)該再使用索引掃描來完成這個Query了~

• 更新非常頻繁的字段不適合創(chuàng)建索引
• 不會出現(xiàn)在where字句中的字段不應(yīng)該創(chuàng)建索引

單鍵索引還是組合索引

• 單鍵與組合之間的利弊

1.在更新操作時，組合索引比起單鍵索引帶來的效率下降更多一些，因為組合索引涉及多個字段，在更新操作時
帶來的附加成本比單鍵索引要高；
2.但在多個組合條件查詢時，組合索引過濾數(shù)據(jù)相對單鍵索引更多，需要訪問的記錄數(shù)相對較少；
3.創(chuàng)建多個單鍵索引與創(chuàng)建一個多字段的組合索引，通常情況下，組合索引的效果更好一些；因為mysql的Query Optimizer大多數(shù)情況下只選擇一個索引；

• 選擇索引的基本建議

1.對于單鍵索引，盡量選擇針對當(dāng)前Query過濾性更好的索引；
2.在選擇組合索引的時候，當(dāng)前Query中過濾性最好的字段在索引字段順序中排列越靠前越好；
3.在選擇組合索引的時候，盡量選擇可以能夠包含前Query的Where子句中更多字段的索引
4.盡可能分析信息和調(diào)整Query的寫法來達到選擇合適索引的目的而減少通過使用Hint認為控制索引的選擇，因為這會使后期維護成本增加，同事增加潛在風(fēng)險；

Mysql中索引的限制

• MyISAM存儲引擎索引鍵長總和不能超過1000字節(jié)
• BLOB和TEXT類型的列只能 創(chuàng)建前綴索引
• Mysql目前不支持函數(shù)索引
• 使用不等于的時候mysql無法使用索引
• 過濾字段使用了函數(shù)運算后，mysql無法使用索引
• Join語句中Join條件字段類型不一致的時候mysql無法使用索引
• 使用Like條件，以通配符開始（“%ddd”）mysql無法使用索引
• 使用非等值查詢的時候，mysql無法使用hash索引
• 組合索引，在遇到范圍查詢時將會停止后續(xù)的索引操作