上篇文章講到使用MySQL的Explain命令可以分析SQL性能瓶頸，優化SQL查詢，以及查看是否用到了索引。 我們都知道創建索引可以提高查詢效率，但是具體該怎麼創建索引？ 哪些欄位適合創建索引？ 哪些欄位又不適合創建索引？ 本文跟大家一塊學習一下如何創建合適資料庫索引。 ...
上篇文章講到使用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;
對於已經創建好的索引，我們還可以使用MySQL命令查看每個索引的區分度排名：
圖中Cardinality列表示索引的區分度排名，也被稱為基數。
2.4 有序的欄位適合創建索引
有序的欄位在插入資料庫的過程中，仍能保持B+樹的索引結構，不需要頻繁更新索引文件，性能更好。
3. 哪些欄位不合適創建索引？
說完哪些欄位適合創建索引，就有不適合創建索引的的欄位。
3.1 區分度低的欄位不適合創建索引。
剛纔說了用戶表中性別的區分度較低，不如生日欄位適合創建索引。
3.2 頻繁更新的欄位不適合創建索引
更新欄位的過程中，需要維護B+樹結構，會頻繁更新索引文件，降低SQL性能。
3.3 過長的欄位不適合創建索引
過長的欄位會占用更多的空間，不適合創建索引。
3.4 無序的欄位不適合創建索引
無序的欄位在插入資料庫的過程中，為了維護B+樹索引結構，需要頻繁更新索引文件，性能較差。
4. 創建索引的其他註意事項
4.1 優先使用聯合索引
查詢的時候，聯合索引比普通索引能更精準的匹配到所需數據。
圖中就是在（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，都需要更新大量索引文件，得不償失。
知識點總結：
文章持續更新，可以微信搜一搜「 一燈架構 」第一時間閱讀更多技術乾貨。
