[TOC] ## 索引的作用 >[success] * 索引大大減少服務器需要掃描的數據量 > * 幫助服務器避免排序和臨時表 > * 將隨機I/O變成順序I/O > * 提高查詢速度 >[warning] * 降低了寫的速度，占用磁盤 > * 5.0版本前只會使用一個索引，5.1引入了索引合并`index merge` ### **使用場景** * 中到大型表，索引非常有效 * 特大型的表，建立和使用索引的代價也增長，可以使用分區技術來解決 ## **索引類型** > * `B+Tree ` `Hash` > * 查詢時間。B+ Tree為`O(log(n))`，與樹的高度有關；Hash為`O(1)` > * 適用范圍。在內存中，紅黑樹比B樹效率更高；若是涉及磁盤操作，則B樹更優 ## **類型區別** >[success] * 索引的樹型數據結構為什么使用B+樹，而非B樹或紅黑樹？ > * * B樹需要做局部的中序遍歷，可能要跨層訪問。 > * * 而**B+樹由于所有數據都在葉子結點，不用跨層，同時由于有鏈表結構，只需要找到首尾，通過鏈表就能把所有數據取出。** > * 為什么常用B+ Tree而非Hash作為索引類型？ > * * 數據庫中的索引一般是在磁盤上，數據量大的情況可能無法一次裝入內存，B+樹的設計可以允許數據分批加載，同時樹的高度較低，提高查找效率。 > * * 注：哈希索引數據并不是按照索引列的值順序存儲的，故無法用于排序 > * * 哈希索引只支持等值比較查詢，如：=、in()、<=>(安全比較運算符，用來做 NULL 值的關系運算)，不支持任何范圍查詢 **** ## **索引分類** > * 普通索引：最基本的索引，沒有任何約束限制 > * 唯一索引：與普通索引類似，但是具有唯一性約束 > * 主鍵索引：特殊的唯一索引，不允許有空置 > * 一個表只能有一個主鍵索引，可以有多個唯一索引 > * 主鍵可以與外鍵構成參照完整性約束，防止數據不一致 > * 組合索引：將多個列組合在一起創建索引，可以覆蓋多個列 > * 外鍵索引：只有InnoDB類型的表才可以使用，保證數據的一致性、完整性和實現級聯操作 > * 全文索引：MySql自帶的全文索引只能用于MyISAM，并且只能對英文進行全文檢索，全文索引(`FULL TEXT`)。目前只有`CHAR`、`VARCHAR`，`TEXT`列上可以創建全文索引。目的為解決`WHERE name LIKE '%word%'`的模糊搜索。 ### **索引規約** > * 業務上具有唯一特性的字段，即使是多個字段的組合，也必須建成唯一索引。 > * 在 varchar 字段上建立索引時，必須指定索引長度，沒必要對全字段建立索引，根據實際文本區分度決定索引長度即可。count(distinct left(列名, 索引長度))/count(*)計算區分度 > * SQL 性能優化的目標：至少要達到 range 級別，要求是 ref 級別，如果可以是 consts最好。 **創建原則** > * 最適合索引的列是出現在`where`子句中的列，或連接子句中的列而不是出現在`select`關鍵字后的列 > * 索引列的基數越大，索引的效果越好 > * 對字符串進行索引，應該制定一個前綴長度，可以節省大量的索引空間 **注意事項** >[danger] > * 組合索引遵循前綴原則 > * `like`查詢時， `%` 不能在前， 可以使用全文索引 > * `column is null` 可以使用索引 > * 如果MySql估計使用索引比全表掃描更慢，會放棄使用索引 > * 如果 `or`前的條件中的列有索引，后面的沒有，索引都不會被用到 > * 列類型是字符串，查詢時給值加引號，否則索引失效 ### **覆蓋索引** * 查詢的數據列從索引中就能夠獲取到，不必從數據表中讀取 > 了解覆蓋索引之前我們先大概了解一下什么是聚集索引(主鍵索引)和輔助索引(二級索引) ? ? ? ?聚集索引（主鍵索引）： ? ? ? ? ? ?聚集索引就是按照每張表的主鍵構造一顆B+樹，同時葉子節點中存放的即為整張表的記錄數據。 ? ? ? ? ? ?聚集索引的葉子節點稱為數據頁，聚集索引的這個特性決定了索引組織表中的數據也是索引的一部分。 ? ? ? ?輔助索引（二級索引）： ? ? ? ? ? ?非主鍵索引，葉子節點=鍵值+書簽。Innodb存儲引擎的書簽就是相應行數據的主鍵索引值。 >* 解釋一： 就是select的數據列只用從索引中就能夠取得，不必從數據表中讀取，換句話說查詢列要被所使用的索引覆蓋。 > * 解釋二： 索引是高效找到行的一個方法，當能通過檢索索引就可以讀取想要的數據，那就不需要再到數據表中讀取行了。如果一個索引包含了（或覆蓋了）滿足查詢語句中字段與條件的數據就叫 做覆蓋索引。 >* 解釋三：是非聚集組合索引的一種形式，它包括在查詢里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆蓋查詢語句\[select子句\]與查詢條件\[Where子句\]中所涉及的字段，也即，索引包含了查詢正在查找的所有數據）。 >[danger] * 不是所有類型的索引都可以成為覆蓋索引。覆蓋索引必須要存儲索引的列，而哈希索引、空間索引和全文索引等都不存儲索引列的值，所以MySQL只能使用B-Tree索引做覆蓋索引 >[danger] * 索引是通過二叉樹的數據結構來描述的，我們可以這么理解聚簇索引：索引的葉節點就是數據節點。而非聚簇索引的葉節點仍然是索引節點，只不過有一個指針指向對應的數據塊。