mysql 索引使用及優化詳情

前言

索引對有一定開發經驗的同學來說並不陌生，合理使用索引，能大大提升sql查詢的效能，可以這麼講，隨著業務資料量的不斷增長，優化系統的響應速度，很大程度上可以說就是集中在索引的優化上；

mysql索引原理

在正式瞭解與學習mysql索引之前，先對mysql的索引原理再次回顧下；

我們知道，目前大多數使用的mysql引擎為 innodb，而innodb引擎使用的是 B+ Tree，下面通過幾張圖快速瞭解下 B+ Tree的結構，

假如存在下面這張表：

那麼通過 B+ Tree構建出來的 “ 以ID為主鍵索引 ” 的樹形結構如下：

說明：

• 葉子節點存放的是ID對應的一條完整的記錄；
• 查詢一條記錄時，如果是按照ID搜尋，則會採用類似二元樹的方式，最終定位到葉子節點的ID對應的那條記錄；

也就是說，如果查詢的時候，以ID為引數，則效率是最高的，反之，如果以非主鍵欄位，建立索引，則B+Tree的索引結構將會如下，

說明：

• 主鍵索引構建出來的 B+Tree 結構保持不變；
• 再以主鍵ID之外的欄位，構建出一個B+Tree結構，其葉子節點儲存的是欄位的值以及對應的主鍵值；

以上關於mysql 的inndb的索引結構原理先介紹到這兒，後文中將會用到裡面的東西，還請留意；

mysql索引分類

MySQL的索引包括普通索引、唯一性索引、全文索引、單列索引、多列索引和空間索引等；

• 從 功能邏輯 上說，索引主要有 4 種，分別是普通索引、唯一索引、主鍵索引、全文索引；
• 按照 物理實現方式 ，可以分為 2 種：聚簇索引和非聚簇索引；
• 按照 作用欄位個數 進行劃分，分成單列索引和聯合索引；、

常規分類

• 主鍵索引，針對表的主鍵建立的索引，預設建表的時候，自動建立，有且只能有一個；
• 唯一索引，為了避免一個表中的某列資料出現重複的值，可以有多個，關鍵字：UNIQUE；
• 常規索引，用於快速定位特定欄位的資料，可以有多個；全文索引，
• 全文索引常用於查詢文字中的關鍵詞，而不是比較索引中的值，可以有多個，關鍵字FULLTEXT；

補充說明

不同的儲存引擎支援的索引型別也不一樣

• InnoDB ：支援 B-tree、Full-text 等索引，不支援 Hash索引；
• MyISAM ： 支援 B-tree、Full-text 等索引，不支援 Hash 索引；
• Memory ：支援 B-tree、Hash 等索引，不支援 Full-text 索引；
• NDB ：支援 Hash 索引，不支援 B-tree、Full-text 等索引；
• Archive ：不支援 B-tree、Hash、Full-text 等索引；

索引建立語法

資料準備，有如下建表sql

CREATE TABLE `user` (
	`user_id` VARCHAR (32) NOT NULL COMMENT '使用者ID',
	`user_name` VARCHAR (64) DEFAULT NULL COMMENT '使用者姓名',
	`passwd` VARCHAR (64) NOT NULL COMMENT '密碼',
	`email` VARCHAR (64) DEFAULT NULL COMMENT '郵箱',
	`mobile` VARCHAR (32) DEFAULT NULL COMMENT '手機號',
	`address` VARCHAR (128) DEFAULT NULL COMMENT '地址',
	`ID` VARCHAR (18) DEFAULT NULL COMMENT '身份證號',
	`sex` INT (11) DEFAULT NULL COMMENT '使用者性別 1:男 2:女',
	PRIMARY KEY (`user_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

1、建立索引

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name[length],...) [ASC | DESC]

• UNIQUE 、 FULLTEXT 和 SPATIAL 為可選引數，分別表示唯一索引、全文索引和空間索引；
• index_name 指定索引的名稱，為可選引數，如果不指定，那麼MySQL預設col_name為索引名；
• col_name 為需要建立索引的欄位列，該列必須從資料表中定義的多個列中選擇；
• length 為可選引數，表示索引的長度，只有字串型別的欄位才能指定索引長度；
• ASC 或 DESC 指定升序或者降序的索引值儲存；

比如給上表的user_name建立一個唯一索引

CREATE UNIQUE INDEX user_name_idx on `user`(user_name); 

2、檢視索引

SHOW INDEX FROM table_name;

3、刪除索引

DROP INDEX index_name ON table_name;

刪除上面的表建立的唯一索引：

4、為 username和password建立聯合索引

create index name_passwd_index on `user`(user_name, passwd);

5、給user表新增一個info的欄位，併為這個欄位新增全文索引

ALTER  TABLE  `user`  ADD  FULLTEXT ( `info` );

全文索參照match+against方式查詢：

SELECT * FROM `user` WHERE MATCH(欄位名稱) AGAINST (‘查詢字串');

注意點：

使用全文索引前，搞清楚版本支援情況，不同的版本可能對全文索引支援不一樣；全文索引比 like + % 快 N 倍，但是可能存在精度問題；如果需要全文索引的是大量資料，建議先新增資料，再建立索引；

已經存在的表建立、刪除索引等

現實中，如果之前已經給表建立過相關的索引，現在需要修改或刪除，或重新建立，該如何做呢？

1、使用ALTER TABLE語句建立索引

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],…) [ASC | DESC]

比如給 user表的mobile欄位新增索引

ALTER TABLE `user` ADD INDEX `idx_mobile` (`mobile`);

2、使用ALTER TABLE語句刪除索引

ALTER TABLE table_name DROP INDEX index_name;

或者直接使用drop語句

DROP INDEX index_name ON table_name;

說明

刪除表中的列時，如果要刪除的列為索引的組成部分，則該列也會從索引中刪除。如果組成索引的所有列都被刪除，則整個索引將被刪除；

常用的索引設計原則

在實際開發中，索引並不是越多越好，建立索引需結合業務情況進行綜合考量，下面結合實際經驗列舉出一些常用的索引設計原則，作為建立索引時的參考；

1、欄位值在業務中具備唯一性

業務上具有唯一特性的欄位，即使是組合欄位，也必須建成唯一索引，比如使用者表中，可以通過身份證號或微訊號作為 唯一索引；

2、頻繁作為 WHERE 查詢條件的欄位

某欄位在SELECT語句WHERE 條件中頻繁使用，就需要給這個欄位建立索引。尤其是資料量大時，建立索引就可以大幅提升查詢的效率；比如使用者表的 user_id

3、UPDATE、DELETE 的 WHERE 條件列

我們知道，如果更新的這個列建立了索引，在真正執行資料更新的時候，不僅要更新資料，還要更新這個列的索引資訊，在這種情況下，建議只對查詢的那個欄位建立索引（非索引欄位更新的時候速度更快）；

4、經常 GROUP BY 和 ORDER BY 的列

索引是讓資料按某種順序進行儲存或檢索，因此使用 GROUP BY 對資料進行分組查詢，或者使用 ORDER BY 對資料排序的時候，就需要 對分組或者排序的欄位進行索引 。如果待排序的列有多個，那麼可以在這些列上建立 組合索引 ，以提升速度；

5、對DISTINCT 欄位建立索引

有時候需要使用DISTINCT對某欄位進行去重，那麼對這個欄位建立索引，也會提升查詢效率。這是因為索引會對資料按照某種順序進行排序，所以有了索引在去重的時候會快很多。

6、使用列的型別小的建立索引

7、使用字串字首建立索引

舉個例子來說，在user表中存在一個郵箱eamil欄位，通常來說，對於一個穩定的業務系統，user標準的email格式可以說格式上是固定的，比如 數位@qq.com，名稱@163.com等；

我們知道索引也是要佔用儲存空間的，欄位的長度越長，建立的索引最終佔用的空間也越大，當表的資料量大到一定程度，查詢時就算是檢索走索引也會花費較長時間，這時候就可以考慮使用字串字首建立索引了；

可以使用下面的語句建立字串字首建立索引：

alter table useradd index(email(6));

這裡的問題是，擷取多少呢？擷取多了，達不到節省索引儲存空間的目的；擷取少了，重複內容太多，欄位的雜湊度(選擇性)會降低。怎麼計算不同的長度的選擇性呢？下面給出一個經驗公式作為參考，

先看一下欄位在全部資料中的選擇度：

select count(distinct email) / count(*) from user;

通過不同長度去計算，與全表的選擇性對比:

count(distinct left(列名, 索引長度))/count(*)

8、使用最頻繁的列放到聯合索引的左側

這樣可以儘可能的向查詢時的最佳左字首原則靠攏；

9、在多欄位都要建立索引的情況下，聯合索引優於單值索引

試想當user表中，當user_name，mobile都需要建立索引時，分別建立兩個單列索引帶來的開銷，與建立一個聯合索引帶來的開銷哪個更大呢？

10、 區分度高(雜湊性高)的列適合作為索引

如果表中存在性別這樣的欄位，就不太適合建立索引，這個需要注意；

11、多表 JOIN 時，建立索引注意事項

• 連線表數量儘量不要超過 3 張 ，因為每增加一張表就相當於增加了一次巢狀的迴圈，數量級增長會非常快，嚴重影響查詢的效率；
• 儘可能對 WHERE 後面的條件欄位建立索引 ，因為 WHERE 才是對資料條件的過濾；
• 對於連線欄位建立索引 ，並且該欄位在多張表中的型別必須一致 ，欄位型別不一致將會帶來較大的查詢效能損耗；

12、有大量重複資料的列上不要建立索引

在這樣的列上一旦建立了索引，比如表中有50萬資料，你需要先存取 50 萬次索引，然後再存取 50 萬次資料表，這樣加起來的開銷比不使用索引可能還要大；

一個經驗值

當資料重複度大，比如高於 10% 的時候，就不需要對這個欄位建立索引；

13、避免對經常更新的表建立過多索引

這個算是常識性的經驗了，更新資料時候，不經要更新資料本身，還需要更新索引；

14、不建議用無序的值作為索引

例如身份證、UUID(在索引比較時需要轉為ASCII，並且插入時可能造成頁分裂)、MD5、HASH、無序長字串等；

15、不要定義冗餘或重複的索引

即對同一個欄位而言，不要建立多個不同名稱的索引，這樣只會增加維護的成本，並不會對搜尋有什麼好處；

16、及時刪除不再使用或者很少使用的索引

減少索引儲存空間對整個表空間的開銷

索引失效情況總結

1、不要在索引列上進行操作計算

計算、函數、型別轉換(自動或手動)導致索引失效，上面的user表中，我們給phone建立了索引，但如果使用下面的函數進行查詢，索引將會失效；

explain select * from `user` where SUBSTRING(mobile,10,2) = '12'

2、字串查詢時一定要加引號

字串查詢不加引號時存在隱式轉換，將會使索引失效

3、模糊匹配

如果僅僅是尾部的模糊匹配，仍然會走索引（即後like）；但如果是頭部匹配，索引將會失效；

like在前

like在後

前後都有like

很多同學在實際開發中，習慣性的就寫上前後都帶有 like的語句，而在表資料量越來越大的情況下，效率將會非常低；

4、使用 or 連線的條件

用or分開的欄位作為條件查詢時，如果or前面的列有索引，但是後面的列沒有索引，那麼整條查詢將不會使用到索引；

explain select * from `user` where mobile = '13366767812'  or email = '16678623@qq.com' 

usr表中，email由於沒有建索引，所以or 的查詢結果中，最終沒有用到索引；

5、範圍條件右邊的列索引失效

對於某些聯合索引，如果用到了範圍查詢但是查詢條件的欄位未按照聯合索引的欄位順序，將會使得索引失效；

如下，user_name 和age建立了聯合索引，使用下面這條sql分析可知

explain select * from `user` where age > 25 and user_name = '張小華'

在這種情況下，需要將範圍查詢條件放置語句最後；

6、不等於(!= 或者<>)索引失效

儘量避免在巨量資料量的查詢中使用 != 這種查詢

7、 is null可以使用索引，is not null無法使用索引

CREATE index idx_mobile on `user`(mobile)

分別使用下面的sql進行分析

8、使用 not in和not exists 這樣的關鍵字導致索引失效

9、不合理的使用order by導致索引失效

其實，這個也很好理解，畢竟需要對全表資料進行排序處理，在網路上看到有說如果order by條件滿足最左匹配則會正常走索引， 在當前8.0.18版本中並未出現。所以，在基於order by和limit進行使用時，要特別留意。是否走索引不僅涉及到資料庫版本，還要看Mysql優化器是如何處理的。

但是使用主鍵進行排序，則是可以走索引的，這一點需要注意

10、其他情況

當然，還有其他一些是否走索引的規則，這與索引的型別是B-tree索引還是點陣圖索引也有關係；

這裡要說的其他，可以總結為：

Mysql優化器的其他優化策略，比如優化器認為在某些情況下，全表掃描比走索引快，則它就會放棄索引；針對這種情況，一般不用過多理會，當發現問題時再定點排查即可

儘量使用覆蓋索引

最後，補充一個知識點，即使用覆蓋索引

即查詢要返回的結果欄位中，儘可能的在索引中使用到（或全部用到），減少使用 "select * " 這樣的查詢；

比如下面這條語句

select address,age from `user` where user_id = '1' 

儘管使用到了主鍵查詢，但是查詢的欄位 address 和 age並未包含在索引列中，所以這種情況下，innodb引擎最終需要通過回表的方式才能將資料的結果查出來；

還記得本文開頭的這張圖吧

當我們查詢一條記錄時，使用的條件為 name = ‘名字’，儘管對name建立了索引，但是name的索引結構中，其葉子節點儲存的是name值以及id值，假如這時，你的查詢語句中需要返回的欄位不是id和name，而是address和mobile，這時，就需要拿到id再次去查詢，一直查到id的索引樹下的完整的記錄，這就是所謂的回表；

當表的資料量足夠大的時候，回表耗費的時間是很長的，這個對整體的查詢響應來說一定會成為一個需要優化的點；

到此這篇關於mysql 索引使用及優化詳情的文章就介紹到這了,更多相關mysql 索引優化內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！