Mysql的索引分類

MySQL 索引
MySQL索引的建立對于MySQL的高效運行是很重要的，索引可以大大提高MySQL的檢索速度。打個比方，如果合理的設(shè)計且使用索引的MySQL是一輛蘭博基尼的話，那么沒有設(shè)計和使用索引的MySQL就是一個人力三輪車。
拿漢語字典的目錄頁（索引）打比方，我們可以按拼音、筆畫、偏旁部首等排序的目錄（索引）快速查找到需要的字。索引分單列索引和組合索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個索引包含多個列。創(chuàng)建索引時，你需要確保該索引是應(yīng)用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)。實際上，索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄。上面都在說使用索引的好處，但過多的使用索引將會造成濫用。因此索引也會有它的缺點：雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件。建立索引會占用磁盤空間的索引文件。

單列索引

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

創(chuàng)建單列索引的幾種方式：

外部創(chuàng)建

CREATE INDEX indexName ON table_name (column_name)

修改表結(jié)構(gòu)（添加索引）

ALTER table tableName ADD INDEX indexName(columnName)

創(chuàng)建表的時候直接指定

如果是CHAR，VARCHAR類型，length可以小于字段實際長度；如果是BLOB和TEXT類型，必須指定 length。

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

唯一索引

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

創(chuàng)建唯一索引的幾種方式：

外部創(chuàng)建

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

修改表結(jié)構(gòu)（添加索引）

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

創(chuàng)建表的時候直接指定

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

聯(lián)合索引(復(fù)合索引)

復(fù)合索引是索引中功能最強大的一個，索引能夠同時覆蓋多個數(shù)據(jù)列,

創(chuàng)建聯(lián)合索引（復(fù)合索引）的方式：

外部創(chuàng)建

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

Mysql的索引類型

INDEX | NORMAL 普通索引

大多數(shù)情況下都可以使用，允許出現(xiàn)相同的索引內(nèi)容。

UNIQUE 唯一索引

不可以出現(xiàn)相同的值，可以有NULL值，如果該字段信息保證不會重復(fù)例如身份證號用作索引時，可以設(shè)置為UNIQUE

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

PRIMARY KEY 主鍵索引

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

FULLTEXT 全文索引

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

SPATIAL 空間索引

空間索引是對空間數(shù)據(jù)類型的字段建立的索引，MYSQL中的空間數(shù)據(jù)類型有4種，分別是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL關(guān)鍵字進行擴展，使得能夠用于創(chuàng)建正規(guī)索引類型的語法創(chuàng)建空間索引。創(chuàng)建空間索引的列，必須將其聲明為NOT NULL，空間索引只能在存儲引擎為MYISAM的表中創(chuàng)建。

Mysql的索引方法

BTREE

B樹（可以是多叉樹），mysql默認使用的方法，通過BTREE算法建立索引的字段，比如掃描20行就能得到未使用BTREE前掃描了2^20行的結(jié)果。

HASH

哈希算法，哈希算法通過建立特征值，然后根據(jù)特征值來快速查找。這種方式對范圍查詢支持得不是很好

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

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

由于 Hash 索引比較的是進行 Hash 運算之后的 Hash 值，所以它只能用于等值的過濾，不能用于基于范圍的過濾，因為經(jīng)過相應(yīng)的 Hash 算法處理之后的 Hash 值的大小關(guān)系，并不能保證和Hash運算前完全一樣。

（2）Hash 索引無法被用來避免數(shù)據(jù)的排序操作。

由于 Hash 索引中存放的是經(jīng)過 Hash 計算之后的 Hash 值，而且Hash值的大小關(guān)系并不一定和 Hash 運算前的鍵值完全一樣，所以數(shù)據(jù)庫無法利用索引的數(shù)據(jù)來避免任何排序運算；

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

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

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

前面已經(jīng)知道，Hash 索引是將索引鍵通過 Hash 運算之后，將 Hash運算結(jié)果的 Hash 值和所對應(yīng)的行指針信息存放于一個 Hash 表中，由于不同索引鍵存在相同 Hash 值，所以即使取滿足某個 Hash 鍵值的數(shù)據(jù)的記錄條數(shù)，也無法從 Hash 索引中直接完成查詢，還是要通過訪問表中的實際數(shù)據(jù)進行相應(yīng)的比較，并得到相應(yīng)的結(jié)果。

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

對于選擇性比較低的索引鍵，如果創(chuàng)建 Hash 索引，那么將會存在大量記錄指針信息存于同一個 Hash 值相關(guān)聯(lián)。這樣要定位某一條記錄時就會非常麻煩，會浪費多次表數(shù)據(jù)的訪問，而造成整體性能低下。

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

Mysql的索引使用示例

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

mysql explain的作用是：

模擬Mysql優(yōu)化器是如何執(zhí)行SQL查詢語句的，從而知道Mysql是如何處理你的SQL語句的。分析你的查詢語句或是表結(jié)構(gòu)的性能瓶頸。（這里只做簡單介紹，使用方法，在select語句前加上explain就行了）

單列索引使用示例

1，為monitor_concentration表的site_number字段創(chuàng)建普通索引

-- 創(chuàng)建索引  siteNumber：索引名稱唯一，monitor_concentration：表名，site_number：字段名
CREATE INDEX siteNumber ON monitor_concentration(site_number)

成功創(chuàng)建如下：

使用explain 優(yōu)化查詢檢測語，查看使用索引和沒有使用索引的區(qū)別

select site_number from monitor_concentration where site_number = 2036

沒有使用索引前：

可以看到?jīng)]有使用索引時，查詢時間為2.132秒

使用索引后：

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

創(chuàng)建的索引不作為條件使用

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

與其他字段配合使用索引

結(jié)果一

結(jié)果二

結(jié)果三

總結(jié)單列索引使用方式：可以看出，我們?yōu)樽侄蝧iteNumber創(chuàng)建了索引，通過它來作為條件和返回語句時（作為where條件有它，返回值也有它。不作為where條件，返回值有它即可），查詢的時候是能快速的幫助我們實現(xiàn)效果，但是與其他字段在一起使用的時候。起不到效果。所以我們在對單個列作為查詢的時候可以使用單列索引。如果想多個字段都能組合使用，下面我使用復(fù)合索引來實現(xiàn)（這種方式比單列更常用）

復(fù)合索引使用示例

1，為monitor_concentration表的site_number，date_time字段創(chuàng)建復(fù)合索引

-- 創(chuàng)建復(fù)合索引
CREATE INDEX idx_c1_c2 ON monitor_concentration(date_time,site_number)

成功創(chuàng)建如下：

使用

結(jié)果一

結(jié)果二

結(jié)果三

結(jié)果四

結(jié)果五

結(jié)果六

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

1，給state，date_time創(chuàng)建復(fù)合索引

CREATE INDEX idx_st_dt ON monitor_concentration(state,date_time)

使用

結(jié)果一

結(jié)果二

這里就不做過多結(jié)果展示了，前面使用復(fù)合索引頁演示過了（注意之前的結(jié)果五和結(jié)果六，我這里要做一點更改），我將演示這樣的效果

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

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

創(chuàng)建索引后，怎么查詢還這么慢，查詢時間8.441秒

看到了嗎，我們已經(jīng)為state和date_time建立了復(fù)合索引，可是我們這里并沒有生效哦！
因為使用聚合函數(shù)的列不能使用索引（可是我就是想用到索引怎么辦呢。。。可以實現(xiàn)的）

刪掉剛才創(chuàng)建的復(fù)合索引，重新創(chuàng)建復(fù)合索引，這里與上面創(chuàng)建的時候區(qū)別在于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

可以看到，這次我們創(chuàng)建的復(fù)合索引是有效的

創(chuàng)建復(fù)合索引字段順序總結(jié)：通過列子可以看到，如果我們在使用比較特殊的語句，想要使其生效的話，我們對復(fù)合索引字段的順序需要注意一下。從這里得出，作為條件的列應(yīng)當(dāng)放在返回使用的列后面

最新評論