Mysql索引分類及其使用範例詳解

Mysql的索引分類

MySQL 索引
MySQL索引的建立對於MySQL的高效執行是很重要的，索引可以大大提高MySQL的檢索速度。打個比方，如果合理的設計且使用索引的MySQL是一輛蘭博基尼的話，那麼沒有設計和使用索引的MySQL就是一個人力三輪車。
拿漢語字典的目錄頁（索引）打比方，我們可以按拼音、筆畫、偏旁部首等排序的目錄（索引）快速查詢到需要的字。索引分單列索引和組合索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個索引包含多個列。建立索引時，你需要確保該索引是應用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)。實際上，索引也是一張表，該表儲存了主鍵與索引欄位，並指向實體表的記錄。上面都在說使用索引的好處，但過多的使用索引將會造成濫用。因此索引也會有它的缺點：雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要儲存資料，還要儲存一下索引檔案。建立索引會佔用磁碟空間的索引檔案。

單列索引

單列索引又可以叫普通索引，一個索引只包含一個列，一個表中可以有多個單列索引.

建立單列索引的幾種方式：

外部建立

CREATE INDEX indexName ON table_name (column_name)

修改表結構（新增索引）

ALTER table tableName ADD INDEX indexName(columnName)

建立表的時候直接指定

如果是CHAR，VARCHAR型別，length可以小於欄位實際長度；如果是BLOB和TEXT型別，必須指定 length。

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
); 

唯一索引

它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種建立方式：

建立唯一索引的幾種方式：

外部建立

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

修改表結構（新增索引）

ALTER table mytable ADD UNIQUE [indexName] (username(length))

建立表的時候直接指定

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
); 

聯合索引(複合索引)

複合索引是索引中功能最強大的一個，索引能夠同時覆蓋多個資料列,

建立聯合索引（複合索引）的方式：

外部建立

CREATE INDEX indexName ON mytable(c1,c2,c3...)

Mysql的索引型別

INDEX | NORMAL 普通索引

大多數情況下都可以使用，允許出現相同的索引內容。

UNIQUE 唯一索引

不可以出現相同的值，可以有NULL值，如果該欄位資訊保證不會重複例如身份證號用作索引時，可以設定為UNIQUE

約束唯一標識資料庫表中的每一條記錄，即在單表中不能用每條記錄是唯一的（例如身份證就是唯一的），UNIQUE(要求列唯一)和Primary Key(primary key = unique + not null 列唯一)約束均為列或列集合中提供了唯一性的保證，Primary Key是擁有自動定義的UNIQUE約束，但是每個表中可以有多個UNIQUE約束，但是隻能有一個Primary Key約束。

PRIMARY KEY 主鍵索引

不允許出現相同的值，且不能為NULL值，一個表只能有一個PRIMARY KEY索引。

FULLTEXT 全文索引

全文索引，可以針對值中的某個單城，比如一篇文章中的某個詞，然而並沒有什麼卵用，因為只有myisam以及英文支援，並且效率讓人不敢恭維，但是可以用coreseek和xunsearch等第三方應用來完成這個需求。

SPATIAL 空間索引

空間索引是對空間資料型別的欄位建立的索引，MYSQL中的空間資料型別有4種，分別是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL關鍵字進行擴充套件，使得能夠用於建立正規索引型別的語法建立空間索引。建立空間索引的列，必須將其宣告為NOT NULL，空間索引只能在儲存引擎為MYISAM的表中建立。

Mysql的索引方法

BTREE

B樹（可以是多叉樹），mysql預設使用的方法，通過BTREE演演算法建立索引的欄位，比如掃描20行就能得到未使用BTREE前掃描了2^20行的結果。

HASH

雜湊演演算法，雜湊演演算法通過建立特徵值，然後根據特徵值來快速查詢。這種方式對範圍查詢支援得不是很好

hash 索引結構的特殊性，其檢索效率非常高，索引的檢索可以一次定位，不像 BTREE 索引需要從根節點到枝節點，最後才能存取到頁節點這樣多次的IO存取，所以 Hash 索引的查詢效率要遠高於 BTREE 索引。
可能很多人又有疑問了，既然 Hash 索引的效率要比 BTREE 高很多，為什麼大家不都用 Hash 索引而還要使用 BTREE 索引呢？任何事物都是有兩面性的，Hash 索引也一樣，雖然 Hash 索引效率高，但是 Hash 索引本身由於其特殊性也帶來了很多限制和弊端，主要有以下這些。

（1）Hash 索引僅僅能滿足”=”,”IN”和”<=>”查詢，不能使用範圍查詢。

由於 Hash 索引比較的是進行 Hash 運算之後的 Hash 值，所以它只能用於等值的過濾，不能用於基於範圍的過濾，因為經過相應的 Hash 演演算法處理之後的 Hash 值的大小關係，並不能保證和Hash運算前完全一樣。

（2）Hash 索引無法被用來避免資料的排序操作。

由於 Hash 索引中存放的是經過 Hash 計算之後的 Hash 值，而且Hash值的大小關係並不一定和 Hash 運算前的鍵值完全一樣，所以資料庫無法利用索引的資料來避免任何排序運算；

（3）Hash 索引不能利用部分索引鍵查詢。

對於組合索引，Hash 索引在計算 Hash 值的時候是組合索引鍵合併後再一起計算 Hash 值，而不是單獨計算 Hash 值，所以通過組合索引的前面一個或幾個索引鍵進行查詢的時候，Hash 索引也無法被利用。

（4）Hash 索引在任何時候都不能避免表掃描。

前面已經知道，Hash 索引是將索引鍵通過 Hash 運算之後，將 Hash運算結果的 Hash 值和所對應的行指標資訊存放於一個 Hash 表中，由於不同索引鍵存在相同 Hash 值，所以即使取滿足某個 Hash 鍵值的資料的記錄條數，也無法從 Hash 索引中直接完成查詢，還是要通過存取表中的實際資料進行相應的比較，並得到相應的結果。

（5）Hash 索引遇到大量Hash值相等的情況後效能並不一定就會比B-Tree索引高。

對於選擇性比較低的索引鍵，如果建立 Hash 索引，那麼將會存在大量記錄指標資訊存於同一個 Hash 值相關聯。這樣要定位某一條記錄時就會非常麻煩，會浪費多次表資料的存取，而造成整體效能低下。

在實際操作過程中，應該選取表中哪些欄位作為索引？
為了使索引的使用效率更高，在建立索引時，必須考慮在哪些欄位上建立索引和建立什麼型別的索引,有7大原則：
1．選擇唯一性索引
2．為經常需要排序、分組和聯合操作的欄位建立索引
3．為常作為查詢條件的欄位建立索引
4．限制索引的數目
5．儘量使用資料量少的索引
6．儘量使用字首來索引
7．刪除不再使用或者很少使用的索引
8. 經常更新修改的欄位不要建立索引（針對mysql說，因為欄位更改同時索引就要重新建立，排序，而Orcale好像是有這樣的機制欄位值更改了，它不立刻建立索引，排序索引，而是根據更改個數，時間段去做平衡索引這件事的）
9、不推薦在同一列建多個索引

Mysql的索引使用範例

接下的所有索引操作都圍繞這張表進行演示，在演示索引之前，先介紹一下explain，更多內容看菜鳥索引優化

mysql explain的作用是：

模擬Mysql優化器是如何執行SQL查詢語句的，從而知道Mysql是如何處理你的SQL語句的。分析你的查詢語句或是表結構的效能瓶頸。（這裡只做簡單介紹，使用方法，在select語句前加上explain就行了）

單列索引使用範例

1，為monitor_concentration表的site_number欄位建立普通索引

-- 建立索引  siteNumber：索引名稱唯一，monitor_concentration：表名，site_number：欄位名
CREATE INDEX siteNumber ON monitor_concentration(site_number)

成功建立如下：

使用explain 優化查詢檢測語，檢視使用索引和沒有使用索引的區別

select site_number from monitor_concentration where site_number = 2036

沒有使用索引前：

可以看到沒有使用索引時，查詢時間為2.132秒

使用索引後：

可以使用了索引在此執行這個語句，查詢時間為0.598秒（差距明顯）

建立的索引不作為條件使用

可以看到返回值用到了索引，在查詢該列的時候一樣有效，效率比沒有索引更高

與其他欄位配合使用索引

結果一

結果二

結果三

總結單列索引使用方式：可以看出，我們為欄位siteNumber建立了索引，通過它來作為條件和返回語句時（作為where條件有它，返回值也有它。不作為where條件，返回值有它即可），查詢的時候是能快速的幫助我們實現效果，但是與其他欄位在一起使用的時候。起不到效果。所以我們在對單個列作為查詢的時候可以使用單列索引。如果想多個欄位都能組合使用，下面我使用複合索引來實現（這種方式比單列更常用）

複合索引使用範例

1，為monitor_concentration表的site_number，date_time欄位建立複合索引

-- 建立複合索引
CREATE INDEX idx_c1_c2 ON monitor_concentration(date_time,site_number)

成功建立如下：

使用

結果一

結果二

結果三

結果四

結果五

結果六

總結複合索引使用方式：從使用幾個結果來看，複合索引做到了單列使用方式。在單列索引我們提出想要實現的效果，在複合索引中成功實現。複合索引比較靈活，我們可以根據實際需要來建立複合索引，下面在舉一個列子。

1，給state，date_time建立複合索引

CREATE INDEX idx_st_dt ON monitor_concentration(state,date_time)

使用

結果一

結果二

這裡就不做過多結果展示了，前面使用複合索引頁演示過了（注意之前的結果五和結果六，我這裡要做一點更改），我將演示這樣的效果

需求，查詢monitor_concentration表state=3的最新時間

select max(date_time) date_time from monitor_concentration where state = 3

建立索引後，怎麼查詢還這麼慢，查詢時間8.441秒

看到了嗎，我們已經為state和date_time建立了複合索引，可是我們這裡並沒有生效哦！
因為使用聚合函數的列不能使用索引（可是我就是想用到索引怎麼辦呢。。。可以實現的）

刪掉剛才建立的複合索引，重新建立複合索引，這裡與上面建立的時候區別在於state和date_time交換了位置

CREATE INDEX idx_st_dt ON monitor_concentration(date_time,state)

重新使用

select max(date_time) date_time from monitor_concentration where state = 3

可以看到，比剛才有明顯的差距，查詢時間1.819

可以看到，這次我們建立的複合索引是有效的

建立複合索引欄位順序總結：通過列子可以看到，如果我們在使用比較特殊的語句，想要使其生效的話，我們對複合索引欄位的順序需要注意一下。從這裡得出，作為條件的列應當放在返回使用的列後面