那些情況適合創建索引

1、字段的數值具有唯一性的限制

索引本身可以起到約束的作用，比如唯一索引、主鍵索引都是可以起到唯一性約束的，因此在我們的數據表中，如果某個字段是唯一性的，就可以直接創建唯一性索引，或者主鍵索引。這樣可以更快速地通過該索引來確定某條記錄。

例如，學生表中學號是具有唯一性的字段，為該字段建立唯一性索引可以很快確定某個學生的信息，如果使用姓名的話，可能存在同名現象，從而降低查詢速度。

• 業務上具有唯一特性的字段，即使是組合字段，也必須建成唯一索引。（來源：Alibaba）
• 說明：不要以為唯一索引影響了insert速度，這個速度損耗可以忽略，但提高查找速度是明顯的。

2、頻繁作為WHERE查詢條件的字段

某個字段在SELECT語句的WHERE條件中經常被使用到，那么就需要給這個字段建立索引了。尤其是在數據量大的情況下，創建普通索引就可以大幅提升數據查詢的效率。

3、經常GROUP BY 和 ORDER BY 的列

索引就是讓數據按照某種順序進行存儲或檢索，因此當我們使用GROUP BY 對數據進行分組查詢，或者使用ORDER BY 對數據進行排序的時候，就需要對分組或者排序的字段進行索引。如果待排序的列有多個，那么可以在這些列上建立組合索引。

4、UPDATE、DELETE的WHERE條件列

5、DISTINCT字段需要創建索引

6、多表 JOIN連接操作時，創建索引注意事項

首先，連接表的數據盡量不要超過3張，因為每增加一張表就相當于增加了一次嵌套的循環，數量級增長會非常快，嚴重影響查詢的效率。

其次，對WHERE條件創建索引，因為WHERE才是對數據條件的過濾。如果在數據量非常打的情況下，沒有WHERE條件過濾是非常可怕的。

最后，對用于連接的字段創建索引，并且該字段在多張表的類型必須一致。比如course_id在student_info表和course表都為int(11)類型，而不能一個為int另外一個為varchar類型。

7、使用類型小的創建索引

我們這里所說的類型大小指的就是該類型表示的數據范圍大小。

我們在定義表結構的時候要顯式的指定列的類型，以整數類型為例，有TINYINT、MEDIUMINT、INT、BIGINT等，它們占用的資源空間的依次遞增，能表示的整數范圍當然也是依次遞增。如果我們想要對某個整數列建立索引的話，在表示的整數范圍允許的情況下，盡量讓索引列使用較小的類型，比如我們能使用INT就不要使用BIGINT，能使用MEDIUMINT就不要使用INT。這是因為：

• 數據類型小，在查詢時進行的比較操作越快
• 數據類型越小，索引占用的存儲空間就越小，在一個數據頁內就可以放下更多的記錄，從而減少磁盤I/O帶來的性能損耗，也就意味著可以把更多的數據頁緩存在內存中，從而加快讀寫效率。

這個建議對于表的主鍵來說更加適用，因為不僅是聚簇索引會存儲主鍵值，其他所有的二級索引的節點處都會存儲一份記錄的主鍵值，如果主鍵使用更小的數據類型，也就意味著節省更多的存儲空間和更高效的I/O。

8、使用字符串前綴創建索引

9、區分度高（散列性高）的列適合作為索引

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

這樣也可以較少的建立一些索引。同時，由于最左前綴原則，可以增加聯合索引的使用率。

11、在多個字段都要創建索引的情況下，聯合索引優于單值索引

限制索引的數目

在實際工作中，我們也需要注意平衡，索引的數目不是越多越好。我們需要限制每張表上的索引數量，建議單表索引數量不超過6個。原因：

• 每個索引都需要占用磁盤空間，索引越多，需要的磁盤空間越大。
• 索引會影響INSERT、DELETE、UPDATE等語句的性能，因為表中的數據更改的同時，索引也會進行調整和更新，會造成負擔。
• 優化器在選擇如何優化查詢時，會根據統一信息，對每一個可以用到的索引來進行評估，以生成出一個最好的執行計劃，如果同時有很多個索引都可以用于查詢，會增加MySQL優化器生成執行計劃時間，降低查詢性能。

那些情況不適合創建索引

1、在where中使用不到的字段，不要設置索引

WHERE條件（包括GROUP BY、ORDER BY）里用不到的字段不需要創建索引，索引的價值是快速定位，如果起不到定位的字段通常是不需要創建索引的。

2、數據量小的表最好不要使用索引

如果表記錄太少，比如少于1000個，那么是不需要創建索引的。表記錄太少，是否創建索引對查詢效率的影響并不大。甚至說，查詢花費的時間可能比遍歷索引的時間還要短，索引可能不會產生優化效果。

3、有大量重復數據的列上不要建立索引

在條件表達式中經常用到的不同值較多的列上建立索引，但字段中如果有大量重復數據，也不用創建索引。比如在學生表的性別字段上只有男與女兩個不同值，因此無需建立索引。如果建立索引，不但不會提高查詢效率，反而會嚴重降低數據更新速度。

舉例1：要在100萬行數據中查找其中的50萬行（比如性別為男的數據），一旦建立了索引，你需要先訪問50萬次索引，然后再訪問50萬次數據表，這樣加起來的開銷比不使用索引可能還要大。

4、避免對經常更新的表創建過多的索引

頻繁更新的字段不一定要創建索引，因為更新數據的時候，也需要更新索引，如果索引太多，在更新索引的時候也會造成負擔，從而影響效率。

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

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

6、刪除不再使用或者很少使用索引

7、不要定義冗余或重復的索引

1、冗余索引

有時候有意或無意的就對同一個列創建了多個索引，比如：index(a,b,c)相當于index(a),index(a,b),index(a,b,c)

我們知道，通過index(a,b,c)索引就可以對a列進行快速搜索，再創建一個專門針對a列的索引就算一個冗余索引，維護這個索引只會增加維護的成本，并不會對搜索有什么好處。

2、重復索引

比如學生表id即使主鍵，又給它定義為一個唯一索引，還給它定義一個普通索引，可是主鍵本身就是聚簇索引，所以定義唯一索引和普通索引是重復的，這種情況要避免。