精心總結十三條建議,幫你創建更合適的MySQL索引

上篇文章講到使用MySQL的Explain命令可以分析SQL性能瓶頸,優化SQL查詢,以及查看是否用到了索引。

我們都知道創建索引可以提高查詢效率,但是具體該怎麼創建索引?

哪些字段適合創建索引?

哪些字段又不適合創建索引?

本文跟大家一塊學習一下如何創建合適數據庫索引。

1. MySQL索引的分類

在創建索引之前了解一下MySQL有哪些索引,然後我們才能選擇合適的索引。

常見的索引有,普通索引、唯一索引、主鍵索引、聯合索引、全文索引等。

普通索引

普通索引就是最基本的索引,沒有任何限制。

可以使用命令創建普通索引:

ALTER TABLE `table_name` ADD INDEX index_name (`column`);

唯一索引

與普通索引不同,唯一索引的列值必須唯一,允許為null。

創建方式是這樣的:

ALTER TABLE `table_name` ADD UNIQUE index_name (`column`);

主鍵索引

主鍵索引是一種特殊的唯一索引,並且一張表只有一個主鍵,不允許為null。

創建方式是這樣的:

ALTER TABLE `table_name` ADD PRIMARY KEY (`column`);

聯合索引

聯合索引是同時在多個字段上創建索引,查詢效率更高。

創建方式是這樣的:

ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`);

全文索引

全文索引主要用來匹配字符串文本中關鍵字。

當需要字符串中是否包含關鍵字的時候,我們一般用like,如果是以%開頭的時候,則無法用到索引,這時候就可以使用全文索引了。

創建方式是這樣的:

ALTER TABLE `table_name` ADD FULLTEXT (`column`);

2. 哪些字段適合創建索引?

我總結了有以下幾條:

2.1 頻繁查詢的字段適合創建索引

一張表的字段總會有冷熱之分,很明顯那些頻繁使用的字段更適合為它創建索引。

2.2 在where和on條件出現的字段優先創建索引

為什麼不是在select後面出現的字段優先創建索引?

因為查詢SQL會先匹配on和where條件的字段,具體的匹配順序是這樣的:

from > on > join > where > group by > having > select > distinct > order by > limit

2.3 區分度高的字段適合創建索引

比如對於一張用戶表來說,生日比性別的區分度更高,更適合創建索引。

可以使用下面的方式手動統計一下,每個字段的區分度,值越大,區分度越高:

select 
    count(distinct birthday)/count(*), 
    count(distinct gender)/count(*) 
from user;

image

對於已經創建好的索引,我們還可以使用MySQL命令查看每個索引的區分度排名:

image

圖中Cardinality列表示索引的區分度排名,也被稱為基數。

2.4 有序的字段適合創建索引

有序的字段在插入數據庫的過程中,仍能保持B+樹的索引結構,不需要頻繁更新索引文件,性能更好。

3. 哪些字段不合適創建索引?

說完哪些字段適合創建索引,就有不適合創建索引的的字段。

3.1 區分度低的字段不適合創建索引。

剛才說了用戶表中性別的區分度較低,不如生日字段適合創建索引。

3.2 頻繁更新的字段不適合創建索引

更新字段的過程中,需要維護B+樹結構,會頻繁更新索引文件,降低SQL性能。

3.3 過長的字段不適合創建索引

過長的字段會佔用更多的空間,不適合創建索引。

3.4 無序的字段不適合創建索引

無序的字段在插入數據庫的過程中,為了維護B+樹索引結構,需要頻繁更新索引文件,性能較差。

4. 創建索引的其他注意事項

4.1 優先使用聯合索引

查詢的時候,聯合索引比普通索引能更精準的匹配到所需數據。

image

圖中就是在(age,name)兩個字段上建立的聯合索引,在B+樹中的存儲結構。

可以看出,是先age排序,age相等的數據,再按name排序。

對於這條查詢SQL:

select age,name from user where age=18 and name='李四';

聯合索引只需一次就可以查到所需數據,如果我們只在age字段上建立索引,會先匹配到age=18的三條數據,然後再逐個遍歷,效率更差,所以平時應該優先使用聯合索引。

4.2 使用聯合索引時,區分度的字段放前面

這樣可以減少查詢次數,更快地匹配到所需數據。

4.3 過長字符串可以使用前綴索引

比如在匹配用戶地址的時候,如果鄉鎮已經能區分大部分用戶了,就沒必要精確到街道小區了。

創建普通索引的時候,指定索引長度,就可以創建前綴索引了。

ALTER TABLE `user` ADD INDEX idx_address (address(3));

4.4 值唯一的字段,使用唯一索引

使用唯一索引,可以避免程序bug導致產生重複數據。

4.5 排序和分組字段也盡量創建索引

在order by和group by中的字段也盡量創建索引,避免使用文件排序,可以使用索引排序提供性能。

4.6 避免創建過多索引

索引好用,適度即可。創建過多的索引,會佔用更多存儲空間,也會嚴重影響SQL性能,每次更新SQL,都需要更新大量索引文件,得不償失。

知識點總結:

image

文章持續更新,可以微信搜一搜「 一燈架構 」第一時間閱讀更多技術乾貨。