[[394893]]

本文轉載自微信公眾號「UP技術控」，作者conan5566 。轉載本文請聯系UP技術控公眾號。

1、限制每張表上的索引數量，建議單張表索引不超過5個

索引并不是越多越好!索引可以提高效率同樣可以降低效率。

索引可以增加查詢效率，但同樣也會降低插入和更新的效率，甚至有些情況下會降低查詢效率。

因為mysql優化器在選擇如何優化查詢時，會根據統一信息，對每一個可以用到的索引來進行評估，以生成出一個最好的執行計劃，如果同時有很多個索引都可以用于查詢，就會增加mysql優化器生成執行計劃的時間，同樣會降低查詢性能。

2、禁止給表中的每一列都建立單獨的索引

5.6版本之前，一個sql只能使用到一個表中的一個索引，5.6以后，雖然有了合并索引的優化方式，但是還是遠遠沒有使用一個聯合索引的查詢方式好

3、每個Innodb表必須有個主鍵

Innodb是一種索引組織表：數據的存儲的邏輯順序和索引的順序是相同的。

每個表都可以有多個索引，但是表的存儲順序只能有一種 Innodb是按照主鍵索引的順序來組織表的。

不要使用更新頻繁的列作為主鍵，不適用多列主鍵(相當于聯合索引) 不要使用UUID、MD5、HASH、字符串列作為主鍵(無法保證數據的順序增長)。

主鍵建議使用自增ID值。

常見索引列建議

出現在SELECT、UPDATE、DELETE語句的WHERE從句中的列

包含在ORDER BY、GROUP BY、DISTINCT中的字段

并不要將符合1和2中的字段的列都建立一個索引，通常將1、2中的字段建立聯合索引效果更好

多表join的關聯列

如何選擇索引列的順序

建立索引的目的是：希望通過索引進行數據查找，減少隨機IO，增加查詢性能 ，索引能過濾出越少的數據，則從磁盤中讀入的數據也就越少。

區分度最高的放在聯合索引的最左側(區分度=列中不同值的數量/列的總行數);

盡量把字段長度小的列放在聯合索引的最左側(因為字段長度越小，一頁能存儲的數據量越大，IO性能也就越好);

使用最頻繁的列放到聯合索引的左側(這樣可以比較少的建立一些索引)。

避免建立冗余索引和重復索引

因為這樣會增加查詢優化器生成執行計劃的時間。

重復索引示例：primary key(id)、index(id)、unique index(id)

冗余索引示例：index(a,b,c)、index(a,b)、index(a)

優先考慮覆蓋索引

對于頻繁的查詢優先考慮使用覆蓋索引。

覆蓋索引：就是包含了所有查詢字段(where,select,ordery by,group by包含的字段)的索引

覆蓋索引的好處：

避免Innodb表進行索引的二次查詢

Innodb是以聚集索引的順序來存儲的，對于Innodb來說，二級索引在葉子節點中所保存的是行的主鍵信息，如果是用二級索引查詢數據的話，在查找到相應的鍵值后，還要通過主鍵進行二次查詢才能獲取我們真實所需要的數據。而在覆蓋索引中，二級索引的鍵值中可以獲取所有的數據，避免了對主鍵的二次查詢 ，減少了IO操作，提升了查詢效率。

可以把隨機IO變成順序IO加快查詢效率

由于覆蓋索引是按鍵值的順序存儲的，對于IO密集型的范圍查找來說，對比隨機從磁盤讀取每一行的數據IO要少的多，因此利用覆蓋索引在訪問時也可以把磁盤的隨機讀取的IO轉變成索引查找的順序IO。

索引SET規范

盡量避免使用外鍵約束

不建議使用外鍵約束(foreign key)，但一定要在表與表之間的關聯鍵上建立索引;

外鍵可用于保證數據的參照完整性，但建議在業務端實現;

外鍵會影響父表和子表的寫操作從而降低性能。