MySQL中的索引
基本法則:索引應該構建在被用作查詢條件的字段.
索引的類型:
- B+ Tree索引: 順序存儲,每一個葉子節(jié)點到根節(jié)點的距離是相同的;左前綴索引,適合查詢范圍類的數(shù)據(jù).
- 可以使用B-Tree索引的查詢類型:
- 全鍵值:
- 鍵值范圍:
- 鍵前綴:
- 不適合使用B-Tree索引的場景:
- 如果不是從最左列開始,索引無效;
- 不能跳過索引中的列;
- 躲過查詢中某個列是范圍查詢,那么其右側的列都無法再使用索引優(yōu)化查詢;
- 可以使用B-Tree索引的查詢類型:
- Hash索引: 基于哈希表實現(xiàn),特別使用于匹配索引鐘的所有列;
- 只有Memory存儲;引擎支持顯式hash索引;
- 適用場景:
- 只支持等值比較查詢, 包括=,IN(),<=>;
- 不適宜hash索引的場景:
- 存儲的值為順序的,因此,不實用于順序查詢;
- 不支持模糊查詢;
- 空間索引(R-Tree):
- MyISAM支持
- 全文索引(FULLTEXT):
- 在文本中查找關鍵詞;
索引的優(yōu)點:
- 索引可以降低服務需要掃描的數(shù)據(jù)量,減少IO次數(shù);
- 索引可以幫助服務器避免排序和使用臨時表;
- 索引可以幫助降隨機I/O轉(zhuǎn)化為順序I/O;
高性能索引策略:
- 獨立使用列,盡量避免其參與運算;
- 左前綴索引: 索引構建于字段的左側的多少個字符,要通過索引選擇性來評估
- 索引選擇性:不重復的索引值和數(shù)據(jù)表的記錄總數(shù)的比值;
- 多列索引: AND操作時更適合使用多列索引;
- 選擇合適的索引次序:將選擇性最高的放左側;
冗余和重復索引:
- 不好的索引使用策略;
通過EXPLAIN來分析索引的有效性
EXPLAIN SELECT clause,獲取查詢執(zhí)行計劃性息,用來查看查詢優(yōu)化器如何執(zhí)行查詢;
輸出:
- id - 當前查詢語句鐘,每個SELECT語句的編號;
- 復雜類型的查詢有三種:
- 簡單子查詢;
- 用于FROM中的子查詢;
- 聯(lián)合查詢: UNION;
- 注意: UNION查詢的分析記過會出現(xiàn)一額外匿名臨時表;
- 復雜類型的查詢有三種:
- select_type
- 簡單查詢?yōu)镾IMPLE;
- 復雜查詢:
- SUBQUERY:簡單子查詢;
- DERIVED: 用于FROM中的子查詢;
- UNION: UNION 語句的第一個之后的SELECT語句;
- UNION RESULT: 匿名臨時表;
- table: SELECT語句關聯(lián)到的表;
- type: 關聯(lián)類型,或訪問類型,即MySQL決定的如何去查找表中的行的方式;
- ALL: 全表掃描;
- index: 根據(jù)索引的次序進行全表掃描;如果在Extra列出現(xiàn)"Using index"表示使用了覆蓋索引,而非全表索引;
- range: 有范圍限制的根據(jù)索引實現(xiàn)范圍掃描;
- ref: 根據(jù)縮影返回表中匹配單個值的所有行;
- eq_ref: 僅返回一個行,但需要額外與某個參考值做比較;
- const,system: 直接返回單個行;
- possible_keys: 查詢可能會用到的索引;
- key: 查詢鐘使用的索引;
- key_len: 在索引中使用的字節(jié)數(shù);
- ref: 在利用key字段所表示的索引完成查詢時所有的列或常值量;
- rows: MySQL估計為找到所有目標行而需要讀取的行數(shù);
- Extra: 額外信息
