本篇文章給大家?guī)砹岁P(guān)于mysql的相關(guān)知識(shí)，其中主要介紹了關(guān)于mysql索引的相關(guān)問題，包括了索引按照邏輯功能劃分、按照物理實(shí)現(xiàn)劃分、按照字段個(gè)數(shù)劃分等索引類型問題，希望對(duì)大家有幫助。

推薦學(xué)習(xí)：mysql教程

在 SQL 優(yōu)化中，索引是至關(guān)重要的一環(huán)，能給查詢效率帶來質(zhì)的飛躍，但是索引并不是萬能的，不合理的索引設(shè)計(jì)甚至?xí)下樵冃省?/p>

索引定義

索引是一種專門用于幫助 SQL 高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)，一個(gè)常用的例子是，索引類似于一本書的目錄，可以快速對(duì)特定值進(jìn)行定位和查找，從而大大加快數(shù)據(jù)查詢的效率。實(shí)際上，索引也是一張表，這張表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄（類似指針）。

索引優(yōu)缺點(diǎn)

優(yōu)點(diǎn)

• 索引大大減小了服務(wù)器需要掃描的數(shù)據(jù)量
• 索引可以幫助服務(wù)器避免排序和臨時(shí)表
• 索引可以將隨機(jī)IO變成順序IO
• 索引對(duì)于InnoDB（對(duì)索引支持行級(jí)鎖）非常重要，InnoDB僅對(duì)需要訪問的元組加鎖，而索引能夠減少InnoDB訪問的元組數(shù)。如果查詢不能使用索引，MySQL會(huì)進(jìn)行全表掃描，并鎖住每一個(gè)元組，不管是否真正需要。

缺點(diǎn)

• 雖然索引大大提高了查詢速度，同時(shí)卻會(huì)降低更新表的速度。因?yàn)楦卤頃r(shí)，MySQL不僅要保存數(shù)據(jù)，還要保存索引文件。因此，對(duì)應(yīng)更新非常頻繁的字段，通常不建議使用索引。
• 建立索引會(huì)占用磁盤空間。
• 如果某個(gè)數(shù)據(jù)列包含許多重復(fù)的內(nèi)容，為它建立索引效果就很差，這個(gè)性質(zhì)稱為索引的選擇性：不重復(fù)的索引值和數(shù)據(jù)表中的記錄總數(shù)的比值。索引的選擇性越高則查詢效率越高。比如對(duì)性別字段建立索引，一百萬條數(shù)據(jù)，只有男女兩種可能，索引選擇性為五十萬分之一，索引效果就很差
• 對(duì)于非常小的表，索引意義不大，大部分情況下簡(jiǎn)單的全表掃描更高效。

因此應(yīng)該只為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)列建立索引。MySQL里同一個(gè)數(shù)據(jù)表里的索引總數(shù)限制為16個(gè)。

索引類型

按功能邏輯劃分

從功能邏輯來劃分，索引主要分為 普通索引、唯一索引、主鍵索引和全文索引

普通索引

最基本的索引，它沒有任何限制。普通索引（由關(guān)鍵字KEY或INDEX定義的索引）的唯一任務(wù)是加快對(duì)數(shù)據(jù)的訪問速度。因此，應(yīng)該只為那些最經(jīng)常出現(xiàn)在查詢條件(WHERE column = …)或排序條件(ORDER BY column)中的數(shù)據(jù)列創(chuàng)建索引。

普通索引的創(chuàng)建有三種方式。

# 創(chuàng)建索引CREATE INDEX idx_username ON user_tbl(username);# 對(duì)于字符串字段，可以手動(dòng)指定長(zhǎng)度，如 user_tbl(username(5))，表示只用前五個(gè)字符來做索引，可以進(jìn)一步加快查詢效率，索引長(zhǎng)度要小于字段長(zhǎng)度# 修改表結(jié)構(gòu)ALTER TABLE user_tbl ADD INDEX idx_username (username)# 創(chuàng)建表的時(shí)候直接指定，如CREATE TABLE user_tbl(  	ID INT NOT NULL,  	username VARCHAR(16) NOT NULL,  	INDEX idx_username (username) );

刪除索引

DROP INDEX idx_username ON user_tbl;

查看索引

SHOW INDEX FROM user_tbl;

唯一索引

它與前面的普通索引類似，不同的就是：普通索引允許被索引的數(shù)據(jù)列包含重復(fù)的值。而唯一索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。

唯一索引的創(chuàng)建跟普通索引類似：

#創(chuàng)建索引 CREATE UNIQUE INDEX idx_username ON user_tbl(username);  # 修改表結(jié)構(gòu) ALTER TABLE user_tbl ADD UNIQUE idx_username (username)  # 創(chuàng)建表的時(shí)候直接指定 CREATE TABLE user_tbl(  	ID INT NOT NULL,  	username VARCHAR(16) NOT NULL,  	UNIQUE idx_username (username)  );

主鍵索引

它是一種特殊的唯一索引，不允許有空值。一張表只能有一個(gè)主鍵，一般是在建表的時(shí)候同時(shí)創(chuàng)建。

CREATE TABLE user_tbl(  	ID INT NOT NULL,  	username VARCHAR(16) NOT NULL,  	PRIMARY KEY(ID)  );

與之類似的是外鍵索引，如果為某個(gè)外鍵字段定義了一個(gè)外鍵約束條件，MySQL就會(huì)定義一個(gè)內(nèi)部索引來幫助自己以最有效率的方式去管理和使用外鍵約束條件。

全文索引

在上一篇文章 MySQL 基礎(chǔ)語(yǔ)法 中，我們說過如果使用了 LIKE + % 開頭，就索引會(huì)失效，那么當(dāng)我們需要前后都模糊搜索的需求（如 LIKE ‘%hello%’），就需要使用全文索引，需要注意的是，Innodb 只有在 5.6 版本之后才支持全文索引。

全文索引的創(chuàng)建和刪除：

# 創(chuàng)建的兩種方法 CREATE FULLTEXT INDEX idx_name ON tbl_name(field_name); ALTER TABLE tbl_name ADD FULLTEXT INDEX idx_name(field_name);  # 刪除的兩種方法 DROP INDEX idx_name ON tbl_name; ALTER TABLE tbl_name DROP INDEX idx_name;

使用全文索引進(jìn)行全模糊匹配的語(yǔ)法為：

SELECT XXX FROM tbl_name WHERE match(field_name) against('xxx'); # 比如對(duì) user_tbl 的 user_name 字段加了全文索引 # 查詢結(jié)果等效于 SELECT user_name, user_id FROM user_tbl WHERE user_name LIKE '%hello%'; SELECT user_name, user_id FROM user_tbl WHERE match(user_name) against('hello');

使用 explain 檢查，可以發(fā)現(xiàn) fulltext 索引生效。

按物理實(shí)現(xiàn)劃分

按物理實(shí)現(xiàn)方式來劃分，通常可以分為聚集索引和非聚集索引。

聚集索引（clustered index）

存儲(chǔ)內(nèi)容是按照聚集索引排序的，聚集索引的順序和行記錄的順序一致,一張表只能有一個(gè)聚集索引。聚集索引的葉子節(jié)點(diǎn)直接儲(chǔ)存聚集索引指向的內(nèi)容，因此查詢的時(shí)候只需要進(jìn)行一次查找。

聚集索引在創(chuàng)建主鍵時(shí)自動(dòng)生成，如果沒有主鍵，則根據(jù)第一個(gè)不為空的唯一索引自動(dòng)生成，如果還沒有，則自動(dòng)生成一個(gè)隱式的聚集索引。

需要注意的是，在進(jìn)行查詢操作的時(shí)候，聚集索引的效率更高，因?yàn)樯倭艘淮尾檎?；但是進(jìn)行修改操作的時(shí)候，效率比非聚集索引低，因?yàn)橹苯有薷牧藬?shù)據(jù)內(nèi)容，為了標(biāo)準(zhǔn)數(shù)據(jù)內(nèi)容的順序和聚集索引順序一致，會(huì)對(duì)數(shù)據(jù)頁(yè)重新排序。

非聚集索引（non-clustered index）

非聚集索引雖然索引項(xiàng)是順序存儲(chǔ)的，但是索引項(xiàng)對(duì)應(yīng)的內(nèi)容是隨機(jī)存儲(chǔ)的，系統(tǒng)會(huì)維護(hù)單獨(dú)的索引表來存儲(chǔ)索引。

非聚集索引的葉子節(jié)點(diǎn)存儲(chǔ)的是數(shù)據(jù)的地址，查詢非聚集索引的時(shí)候，系統(tǒng)會(huì)進(jìn)行兩次查找，先查找索引，再查找索引對(duì)應(yīng)位置的數(shù)據(jù)。因此非聚集索引也叫二級(jí)索引或者輔助索引。

按字段個(gè)數(shù)劃分

按字段個(gè)數(shù)可以把索引分為單一索引和聯(lián)合索引。

單一索引

索引字段只有一列時(shí)為單一索引，上述所有索引都是單一索引。

聯(lián)合索引

將多個(gè)字段組合在一起創(chuàng)建的索引叫聯(lián)合索引。如下：

ALTER TABLE user_tbl ADD INDEX idx_name_city_age (username,city,age);

最左匹配原則

建立這樣的聯(lián)合索引，其實(shí)是相當(dāng)于分別建立了下面三組聯(lián)合索引：

usernname,city,age usernname,city usernname

為什么沒有 city，age 這樣的聯(lián)合索引呢？這是因?yàn)镸ySQL聯(lián)合索引的最左匹配原則，只會(huì)按照最左優(yōu)先的順序進(jìn)行索引匹配，也就是說，(x,y,z) 和 (z,y,x) 是不同的索引，即使是使用聯(lián)合索引中的字段查詢，聯(lián)合索引也有可能失效。

對(duì)于 (x,y,z)，只有在以下查詢條件聯(lián)合索引會(huì)生效：

WHERE x = 1WHERE x = 1 AND y = 1WHERE x = 1 AND y = 1 AND z = 1

對(duì)于其他情況，比如 WHERE y = 1 、WHERE y = 1 AND z = 1 等，就不會(huì)匹配聯(lián)合索引，索引失效，注意對(duì)于 WHERE x = 1 AND z = 1，聯(lián)合索引會(huì)對(duì) x 生效，但是對(duì) z 不生效。

可以擴(kuò)展了解一下，理論上最左匹配原則中索引對(duì) where 中子句的順序也是敏感的，但是由于MySQL的查詢優(yōu)化器會(huì)自動(dòng)調(diào)整 where 子句的條件順序以使用適合的索引，所以實(shí)際上 where 子句順序不影響索引的效果。

要注意的是，如果聯(lián)合索引查詢過程中有范圍查詢，就會(huì)停止匹配，比如下面的語(yǔ)句中， z 字段不能使用到索引：

WHERE x = 1 AND y > 2 AND z = 3

順便提一下，可以用 explain 命令來查看在某個(gè)查詢語(yǔ)句中索引是否生效，具體用法請(qǐng)參考官網(wǎng)文檔。

如果分別在 x, y, z 上建立單列索引，讓該表有3個(gè)單列索引，索引效率也會(huì)大不一樣，在聯(lián)合索引生效的情況下，單個(gè)索引的效率遠(yuǎn)遠(yuǎn)低于聯(lián)合索引。這是由 MySQL 查詢優(yōu)化器的執(zhí)行順序決定的，在執(zhí)行一條查詢 sql 時(shí)，針對(duì)索引的選擇大致有如下步驟：

1. MySQL 優(yōu)化器根據(jù)搜索條件，找出所有可能使用的索引
2. 計(jì)算全表掃描的代價(jià)
3. 計(jì)算使用不同索引執(zhí)行查詢的代價(jià)
4. 對(duì)比各種執(zhí)行方案的代價(jià)，找出成本最低的那一個(gè)

因此，雖然有多個(gè)單列索引，但 MySQL 只能用到其中的那個(gè)系統(tǒng)認(rèn)為似乎是最有效率的，其他的就會(huì)失效。

按索引結(jié)構(gòu)劃分

不同的 mysql 數(shù)據(jù)引擎支持不同結(jié)構(gòu)的索引，按結(jié)構(gòu)劃分，常用的索引為 B+樹索引、Hash 索引、FULLTEXT索引 等，將在下一篇文章 MySQL 索引結(jié)構(gòu) 中介紹。

使用總結(jié)

接下來我們來簡(jiǎn)單總結(jié)一下在什么場(chǎng)景下推薦使用索引。

推薦使用

• WHERE, GROUP BY, ORDER BY 子句中的字段

• 多個(gè)單列索引在多條件查詢是只會(huì)有一個(gè)最優(yōu)的索引生效，因此多條件查詢中最好創(chuàng)建聯(lián)合索引。

  聯(lián)合索引的時(shí)候必須滿足最左匹配原則，并且最好考慮到 sql 語(yǔ)句的執(zhí)行順序，比如 WHERE a = 1 GROUP BY b ORDER BY c, 那么聯(lián)合索引應(yīng)該設(shè)計(jì)為 (a,b,c)，因?yàn)樵谏弦黄恼?MySQL 基礎(chǔ)語(yǔ)法 中我們介紹過，mysql 查詢語(yǔ)句的執(zhí)行順序 WHERE > GROUP BY > ORDER BY。

• 多張表 JOIN 的時(shí)候，對(duì)表連接字段創(chuàng)建索引。

• 當(dāng) SELECT 中有不在索引中的字段時(shí)，會(huì)先通過索引查詢出滿足條件的主鍵值，然后通過主鍵回表查詢出所有的 SELECT 中的字段，影響查詢效率。因此如果 SELECT 中的內(nèi)容很少，為了避免回表，可以把 SELECT 中的字段都加到聯(lián)合索引中，這也就是寬索引的概念。但是需要注意，如果索引字段過多，存儲(chǔ)和維護(hù)索引的成本也會(huì)增加。

不推薦使用或索引失效情況

• 數(shù)據(jù)量很小的表

• 有大量重復(fù)數(shù)據(jù)的字段

• 頻繁更新的字段

• 如果對(duì)索引字段使用了函數(shù)或者表達(dá)式計(jì)算，索引失效

• innodb OR 條件沒有對(duì)所有條件創(chuàng)建索引，索引失效

• 大于小于條件 < >，索引是否生效取決于命中的數(shù)量比例，如果命中數(shù)量很多，索引生效，命中數(shù)量很小，索引失效

• 不等于條件 != <>，索引失效

• LIKE 值以 % 開頭，索引失效

推薦學(xué)習(xí)：mysql視頻教程