[[394893]]

本文轉(zhuǎn)載自微信公眾號「UP技術(shù)控」，作者conan5566 。轉(zhuǎn)載本文請聯(lián)系UP技術(shù)控公眾號。

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

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

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

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

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

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

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

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

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

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

主鍵建議使用自增ID值。

常見索引列建議

出現(xiàn)在SELECT、UPDATE、DELETE語句的WHERE從句中的列

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

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

多表join的關(guān)聯(lián)列

如何選擇索引列的順序

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

區(qū)分度最高的放在聯(lián)合索引的最左側(cè)(區(qū)分度=列中不同值的數(shù)量/列的總行數(shù));

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

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

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

因為這樣會增加查詢優(yōu)化器生成執(zhí)行計劃的時間。

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

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

優(yōu)先考慮覆蓋索引

對于頻繁的查詢優(yōu)先考慮使用覆蓋索引。

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

覆蓋索引的好處：

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

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

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

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

索引SET規(guī)范

盡量避免使用外鍵約束

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

外鍵可用于保證數(shù)據(jù)的參照完整性，但建議在業(yè)務(wù)端實現(xiàn);

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