MySQL 之索引部分

索引概述

索引的本質及作用

索引（也稱「鍵」）是為了加速表中數據行的檢索而創建的一種分散的數據結構。

資料庫查詢是資料庫的最主要功能之一。我們都希望查詢數據的速度能儘可能的快，因此資料庫系統的設計者會從查詢演算法的角度進行優化。最基本的查詢演算法當然是順序查找（linear search），這種複雜度為 O(n) 的演算法在數據量很大時顯然是糟糕的，好在計算機科學的發展提供了很多更優秀的查找演算法，例如二分查找（binary search）、二叉樹查找（binary tree search）等。如果稍微分析一下會發現，每種查找演算法都只能應用於特定的數據結構之上，例如二分查找要求被檢索數據有序，而二叉樹查找只能應用於二叉查找樹上，但是數據本身的組織結構不可能完全滿足各種數據結構（例如，理論上不可能同時將兩列都按順序進行組織），所以，在數據之外，資料庫系統還維護著滿足特定查找演算法的數據結構，這些數據結構以某種方式引用（指向）數據，這樣就可以在這些數據結構上實現高級查找演算法。這種數據結構，就是索引。

索引的優缺點

優點

1. 大大加快數據的檢索速度；
2. 創建唯一性索引，能保證資料庫表中每一行數據的唯一性；
3. 加速表與表之間的連接；
4. 在使用分組和排序子句進行數據檢索時，可以顯著減少查詢中分組和排序的時間。

缺點

1. 索引需要佔用額外的物理空間；
2. 當對表中的數據進行增加、刪除和修改時，索引也需要動態的維護，降低了數據的維護速度。

索引的類型

在 MySQL 中，索引是在存儲引擎層而不是伺服器層實現的，所以，並沒有統一的索引標準。不同存儲引擎的索引的工作方式並不一樣，也不是所有的存儲引擎都支持所有類型的索引。即使多個存儲引擎支持同一種類型的索引，其底層的實現方式也可能不同。

目前 MySQL 提供了常用的四種索引：

• B-Tree 索引：最常見的索引類型，大部分引擎都支持 B-Tree 索引；
• Hash 索引：只有 Memory 引擎支持 Hash 索引，基於 Hash 表實現，使用場景簡單；
• R-Tree：空間數據索引，MyISAM 引擎支持，主要用於地理數據存儲；
• 全文索引：一種特殊類型的索引，類似於搜索引擎的功能。

說明： 平時討論的索引，如果沒有特別指明，一般都是指 B-Tree 索引，而且從技術上說，底層實現方式其實是 B+Tree。 B-Tree 索引又分為幾種，比如普通索引、唯一索引、主鍵索引等等。關於全文索引，詳見 MySQL 之全文索引

三種常用存儲引擎索引支持情況

B-Tree 索引

• 普通索引：創建普通索引時，不限制任何條件（唯一、非空等限制），該類型的索引可以創建在任何數據類型上。
• 唯一索引：UNIQUE，唯一索引，就是限制被索引的值必須是唯一的。當為表中某個欄位設置主鍵或唯一性約束時，系統會自動創建關聯該欄位的唯一索引。
• 主鍵索引：PRIMARY，主鍵索引，一種特殊的唯一索引，不允許有空值。

說明： 關於唯一索引和唯一約束的探討，詳見 談談唯一約束和唯一索引

索引的操作

創建

三種創建方式

// 1. 建表時創建create table 表名 ( id int(11), xxx xxx, [索引類型] index|key [索引名] (屬性名1 [(長度)] [ASC|DESC]) );// 2. 直接創建create index 索引名 on 表名 (屬性名 [(長度)] [ASC|DESC]);// 3. 修改表創建alter table 表名 add [索引類型] index|key [索引名] (屬性名1 [(長度)] [ASC|DESC]);

注意： 不能使用第二種方式創建主鍵索引

修改

沒必要修改，直接刪除重建即可。

刪除

兩種刪除方式

// 1. 直接刪除drop index 索引名 on 表名;// 2. 修改表刪除alter table 表名 drop index 索引名;

查看

show index|keys from 表名

列的意義：

• Table：表名
• Non_unique：如果索引值唯一，則為 0，否則為 1
• Key_name：索引名
• Seqinindex：索引中列的序列號，從 1 開始
• Column_name：列名
• Collation：列在索引中的存儲形式，在 MySQL 中，值「A」（升序）或 NULL（無分類）
• Cardinality：索引中唯一值數目的估計值
• Sub_part：如果列只是被部分地編入索引，則為被編入索引的字元的數目，如果整列被編入索引，則為 NULL
• Packed：說明關鍵字如何被壓縮，如果沒有被壓縮，則為 NULL
• Null：如果列含有 NULL，則值為 YES，如果沒有，則該列為 NO
• Index_type：索引類型（BTREE，FULLTEXT，HASH，RTREE）
• Comment：更多評論

索引的選擇

索引選擇原則

1. 較頻繁的作為查詢條件的欄位應該創建索引；
2. 唯一性太差的欄位（比如性別）不適合單獨創建索引，即使頻繁作為查詢條件；
3. 更新非常頻繁的欄位不適合創建索引；
4. 不會出現在 Where 子句中的欄位不該創建索引。

索引選擇原則細述

• 性能優化過程中，選擇在哪個列上創建索引是最非常重要的。可以考慮使用索引的主要有兩種類型的列：在 where 子句中出現的列，在 join 子句中出現的列， 而不是在 select 關鍵字後選擇列表的列。
• 索引列的基數越大，索引的效果越好。例如，存放出生日期的列具有不同的值，很容易區分行，而用來記錄性別的列，只有「男」和「女」，對此進行索引沒有多大用處，因為不管搜索哪個值，都會得出大約一半的行。
• 使用短索引，如果對字元串列進行索引，應該指定一個前綴長度，可節省大量索引空間，提升查詢速度。
• 利用最左前綴原則。

索引選擇注意事項

既然索引可以加快查詢速度，那麼是不是只要查詢語句需要，就創建索引？答案是否定的。因為索引雖然加快了查詢速度，但索引也是有代價的：索引文件本身要消耗存儲空間，同時索引會加重插入、刪除和修改記錄時的負擔，另外，MySQL 在運行時也要消耗資源維護索引，因此索引並不是越多越好。

一般下面兩種情況不建議建索引：

1. 表記錄比較少，例如一兩千條甚至只有幾百條記錄的表，沒必要建索引，讓查詢做全表掃描就好了；
2. 索引的選擇性較低，所謂索引的選擇性（Selectivity），是指不重複的索引值（也叫基數，Cardinality）與表記錄數（#T）的比值：Index Selectivity = Cardinality / #T 顯然選擇性的取值範圍為 (0, 1]，選擇性越高的索引價值越大，這是由 B+Tree 的性質決定的。

除此之外，還有兩個點需要注意：

1. MySQL 只對以下操作符才使用索引：<、<=、=、>、>=、between、in，以及某些時候的 like（不以通配符 % 或 _ 開頭)；
2. 不要過度索引，只保持需要的索引。每個額外的索引都要佔用額外的磁碟空間，並降低寫操作的性能。在修改表的內容時，索引必須進行更新，有時可能需要重構，因此，索引越多，所花的時間越長。

索引是最好的解決方案？

索引並不總是最好的解決方案。總的來說，只有當索引幫助存儲引擎快速查找到記錄帶來的好處大於其帶來的額外工作時，索引才是有效的。對於非常小的表，大部分情況下簡單的全表掃描更高效。對於中到大型的表，索引就非常有效。但對於特大型的表，建立和使用索引的代價將隨之增長。這種情況下，則需要一種技術可以直接區分出查詢需要的一組數據，而不是一條記錄一條記錄地匹配。這是就要使用分表、分庫等分區技術了。

參考文章

MySQL索引背後的數據結構及演算法原理

索引的利弊與如何判定，是否需要索引

本文原始鏈接：MySQL 之索引部分

推薦閱讀：