Mysql的索引分類(lèi)

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

單列索引

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

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

外部創(chuàng)建

CREATE INDEX indexName ON table_name (column_name)

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

ALTER table tableName ADD INDEX indexName(columnName)

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

如果是CHAR，VARCHAR類(lèi)型，length可以小于字段實(shí)際長(zhǎng)度；如果是BLOB和TEXT類(lèi)型，必須指定 length。

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

唯一索引

它與前面的普通索引類(lèi)似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創(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)建表的時(shí)候直接指定

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

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

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

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

外部創(chuàng)建

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

Mysql的索引類(lèi)型

INDEX | NORMAL 普通索引

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

UNIQUE 唯一索引

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

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

PRIMARY KEY 主鍵索引

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

FULLTEXT 全文索引

全文索引，可以針對(duì)值中的某個(gè)單城，比如一篇文章中的某個(gè)詞，然而并沒(méi)有什么卵用，因?yàn)橹挥衜yisam以及英文支持，并且效率讓人不敢恭維，但是可以用coreseek和xunsearch等第三方應(yīng)用來(lái)完成這個(gè)需求。

SPATIAL 空間索引

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

Mysql的索引方法

BTREE

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

HASH

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

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

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

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

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

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

（3）Hash 索引不能利用部分索引鍵查詢(xún)。

對(duì)于組合索引，Hash 索引在計(jì)算 Hash 值的時(shí)候是組合索引鍵合并后再一起計(jì)算 Hash 值，而不是單獨(dú)計(jì)算 Hash 值，所以通過(guò)組合索引的前面一個(gè)或幾個(gè)索引鍵進(jìn)行查詢(xún)的時(shí)候，Hash 索引也無(wú)法被利用。

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

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

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

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

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

Mysql的索引使用示例

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

mysql explain的作用是：

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

單列索引使用示例

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

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

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

使用explain 優(yōu)化查詢(xún)檢測(cè)語(yǔ)，查看使用索引和沒(méi)有使用索引的區(qū)別

select site_number from monitor_concentration where site_number = 2036

沒(méi)有使用索引前：

可以看到?jīng)]有使用索引時(shí)，查詢(xún)時(shí)間為2.132秒

使用索引后：

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

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

可以看到返回值用到了索引，在查詢(xún)?cè)摿械臅r(shí)候一樣有效，效率比沒(méi)有索引更高

與其他字段配合使用索引

結(jié)果一

結(jié)果二

結(jié)果三

總結(jié)單列索引使用方式：可以看出，我們?yōu)樽侄蝧iteNumber創(chuàng)建了索引，通過(guò)它來(lái)作為條件和返回語(yǔ)句時(shí)（作為where條件有它，返回值也有它。不作為where條件，返回值有它即可），查詢(xún)的時(shí)候是能快速的幫助我們實(shí)現(xiàn)效果，但是與其他字段在一起使用的時(shí)候。起不到效果。所以我們?cè)趯?duì)單個(gè)列作為查詢(xún)的時(shí)候可以使用單列索引。如果想多個(gè)字段都能組合使用，下面我使用復(fù)合索引來(lái)實(shí)現(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ù)合索引使用方式：從使用幾個(gè)結(jié)果來(lái)看，復(fù)合索引做到了單列使用方式。在單列索引我們提出想要實(shí)現(xiàn)的效果，在復(fù)合索引中成功實(shí)現(xiàn)。復(fù)合索引比較靈活，我們可以根據(jù)實(shí)際需要來(lái)建立復(fù)合索引，下面在舉一個(gè)列子。

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

CREATE INDEX idx_st_dt ON monitor_concentration(state,date_time)

使用

結(jié)果一

結(jié)果二

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

需求，查詢(xún)monitor_concentration表state=3的最新時(shí)間

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

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

看到了嗎，我們已經(jīng)為state和date_time建立了復(fù)合索引，可是我們這里并沒(méi)有生效哦！
因?yàn)槭褂镁酆虾瘮?shù)的列不能使用索引（可是我就是想用到索引怎么辦呢。。?？梢詫?shí)現(xiàn)的）

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

可以看到，比剛才有明顯的差距，查詢(xún)時(shí)間1.819

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

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

最新評(píng)論