[[394893]]

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

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

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

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

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

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

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

3、每個(gè)Innodb表必須有個(gè)主鍵

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

每個(gè)表都可以有多個(gè)索引，但是表的存儲(chǔ)順序只能有一種 Innodb是按照主鍵索引的順序來(lái)組織表的。

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

主鍵建議使用自增ID值。

常見(jiàn)索引列建議

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

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

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

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

如何選擇索引列的順序

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

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

盡量把字段長(zhǎng)度小的列放在聯(lián)合索引的最左側(cè)(因?yàn)樽侄伍L(zhǎng)度越小，一頁(yè)能存儲(chǔ)的數(shù)據(jù)量越大，IO性能也就越好);

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

避免建立冗余索引和重復(fù)索引

因?yàn)檫@樣會(huì)增加查詢優(yōu)化器生成執(zhí)行計(jì)劃的時(shí)間。

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

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

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

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

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

覆蓋索引的好處：

避免Innodb表進(jìn)行索引的二次查詢

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

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

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

索引SET規(guī)范

盡量避免使用外鍵約束

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

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

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