一張表到底建多少個索引才是合適呢？
上周的一天，到公司接了杯水剛剛坐穩(wěn)，就看到 DBA就在群里@ 某個研發(fā)帥哥，說“你們表已經(jīng)有10個索引了，怎么這次還要加呢？”
關(guān)于索引，是數(shù)據(jù)庫后臺用來加快查詢速度的強大工具，索引通過提供快速查找所需數(shù)據(jù)的方法來增強查詢能力。
但我們經(jīng)常聽到這樣一句話：“索引是把雙刃劍”。
說明索引并不是越多越好，索引可以提高查詢的效率，但會降低寫數(shù)據(jù)的效率，有時不恰當(dāng)?shù)乃饕€會降低查詢的效率。
那我就在想：一張表到底建多少個索引才是合適呢？
要搞懂這個問題，我們就需要弄清楚以下這幾個問題：
1）常見的索引分類有哪些？
2）MySQL 是如何使用索引的？
3）一張表最多可以建多少索引？
4）新建索引的規(guī)范原則有哪些？
本文我們就一起來展開聊聊這幾個問題~
1、常見的索引分類有哪些？
1.1 應(yīng)用層分類
從應(yīng)用層面，常見分類：
- 普通索引INDEX：加速查找
- 唯一索引：
- 主鍵索引PRIMARY KEY：加速查找+約束（不為空、不能重復(fù)）
- 唯一索引UNIQUE:加速查找+約束（不能重復(fù)）
- 聯(lián)合索引：
PRIMARY KEY(id,name):聯(lián)合主鍵索引
UNIQUE(id,name):聯(lián)合唯一索引
INDEX(id,name):聯(lián)合普通索引
1.2 數(shù)據(jù)結(jié)構(gòu)層分類
從數(shù)據(jù)結(jié)構(gòu)層面，分類如下：
- 哈希（hash）索引
- 基于哈希函數(shù)來實現(xiàn)。哈希函數(shù)會將索引鍵值（如數(shù)據(jù)庫表中的某個字段值）作為輸入，通過特定的算法運算，生成一個固定長度的哈希值。
- 查詢速度快，但不支持范圍查找
- B 樹（btree）索引
一種平衡的多叉樹數(shù)據(jù)結(jié)構(gòu)。B 樹索引會將表中的索引鍵值按照一定的順序（如升序或降序）存儲在樹的節(jié)點中。每個節(jié)點可以存儲多個鍵值以及指向其他節(jié)點的指針。
支持范圍查詢，但占用空間較大
圖片
2、新建索引的規(guī)范原則有哪些？
關(guān)于新建索引，通常需要注意以下規(guī)范原則：
2.1 最左前綴匹配原則
MySQL 會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配
比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的；
如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。
2.2 盡量選擇區(qū)分度高的列作為索引
區(qū)分度的公式是：count(distinct col)/count(*)，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少。
唯一鍵的區(qū)分度是1，而一些 status 狀態(tài)、性別等 字段可能在大數(shù)據(jù)面前區(qū)分度就是0。
2.3 索引列不能參與計算
保持索引列“干凈”，這個原因其實很簡單，b+樹中存的都是數(shù)據(jù)表中的字段值。但是在進(jìn)行檢索時，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然成本太大。
比如 from_unixtime(create_time) = ’2014-05-29’ 就不能使用到索引，需要將語句改寫成：create_time = unix_timestamp(’2014-05-29’)。
2.4 盡量的擴(kuò)展索引，不要新建索引
比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可，而不建議再單獨去建一個b索引。
3、MySQL 是如何使用索引的？
索引用于快速查找具有特定列值的行，其目的在于提高查詢效率。
與我們查閱圖書所用的目錄是一個道理：先定位到章，然后定位到該章下的一個小節(jié)，然后找到頁數(shù)。相似的例子還有：查字典，查火車車次，飛機(jī)航班等。
本質(zhì)都是：通過不斷地縮小想要獲取數(shù)據(jù)的范圍來篩選出最終想要的結(jié)果，同時把隨機(jī)的事件變成順序的事件。
也就是說，有了這種索引機(jī)制，我們可以總是用同一種查找方式來鎖定數(shù)據(jù)。
數(shù)據(jù)庫也是一樣，但顯然要復(fù)雜得多，因為不僅面臨著等值查詢，還有范圍查詢(>、<、between、in)、模糊查詢(like)、并集查詢(or)等等。數(shù)據(jù)庫應(yīng)該選擇怎么樣的方式來應(yīng)對所有的問題呢？
大多數(shù) MySQL 索引（PRIMARY KEY、UNIQUEINDEX 和FULLTEXT）都存儲在 B樹 中。
另外：
空間數(shù)據(jù)類型使用 R 樹；
MEMORYtable 還支持哈希索引；
InnoDB 對 FULLTEXT 索引使用倒排列表。
在 MySQL 中，使用索引進(jìn)行以下操作：
3.1 = 和 in 可以亂序
比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，MySQL 的查詢優(yōu)化器會幫你優(yōu)化成索引可以識別的形式。
3.2 and 與 or
聯(lián)合索引：(d,a,b,c)
1）查詢條件：
a = 10 and b = 'xxx' and c > 3 and d =4MySQL 會按照聯(lián)合索引，從左到右的順序找一個區(qū)分度高的索引字段(這樣便可以快速鎖定很小的范圍)，加速查詢，即按照d—>a->b->c的順序
2）查詢條件：
a = 10 or b = 'xxx' or c > 3 or d =4MySQL 會按照條件的順序，從左到右依次判斷，即a->b->c->d
4、一張表最多可以建多少個索引？
4.1 理論上來說
MySQL 的存儲引擎（如 InnoDB、MyISAM 等）本身并沒有對一個表能創(chuàng)建的索引數(shù)量設(shè)置一個固定數(shù)值限制，，而是由MySQL數(shù)據(jù)庫引擎內(nèi)部的數(shù)據(jù)結(jié)構(gòu)和算法決定的。
從數(shù)據(jù)庫設(shè)計和架構(gòu)的角度，理論上只要滿足以下條件，就可以新增創(chuàng)建索引：
- 存儲空間允許：每個索引都需要占用一定的磁盤空間來存儲索引數(shù)據(jù)結(jié)構(gòu)，所以只要磁盤空間足夠容納新創(chuàng)建的索引結(jié)構(gòu)及其相關(guān)數(shù)據(jù)，在空間層面就不會因空間不足而無法創(chuàng)建索引。
- 性能可接受：隨著索引數(shù)量的增加，雖然查詢性能在某些情況下可能會因合適的索引而提升，但過多的索引也會帶來一些負(fù)面效應(yīng)，比如數(shù)據(jù)更新（插入、刪除、修改操作）時需要同時更新相關(guān)索引，這會導(dǎo)致更新操作變慢。只要系統(tǒng)整體性能（包括查詢性能和更新性能等）在可接受的范圍內(nèi)，理論上可以繼續(xù)創(chuàng)建索引。
4.2 實際應(yīng)用情況
然而在實際應(yīng)用場景中，通常不會無限制地創(chuàng)建索引。一方面是因為上述提到的性能問題，過多的索引往往會導(dǎo)致數(shù)據(jù)更新操作變得極為緩慢，嚴(yán)重影響系統(tǒng)的正常運行。
例如，在一個高并發(fā)的電商訂單處理系統(tǒng)中，如果對訂單表的大量字段都創(chuàng)建了索引，那么每一次訂單的插入、修改或刪除操作，都要花費大量時間來更新相關(guān)索引，導(dǎo)致訂單處理效率大幅下降。
另一方面，不同的 MySQL 版本以及不同的存儲引擎在實際表現(xiàn)上也會有差異。
例如，對于一個擁有百萬條記錄的用戶信息表，在 MySQL 5.7 中按照用戶姓氏進(jìn)行模糊查詢，可能需要遍歷相當(dāng)一部分?jǐn)?shù)據(jù)，查詢速度相對較慢，假設(shè)平均每次查詢需要 20 秒。而 MySQL 8.0 引入了新的索引算法和數(shù)據(jù)結(jié)構(gòu)優(yōu)化，新的索引算法對這種模糊查詢的支持更好，同樣的查詢可能只需要 5 秒左右，性能提升明顯。
總結(jié)
索引是應(yīng)用程序設(shè)計和開發(fā)的一個重要方面。若索引太多，應(yīng)用程序的性能可能會受到影響。而索引太少，對查詢性能又會產(chǎn)生影響，要找到一個平衡點，這對應(yīng)用程序的性能至關(guān)重要。
MySQL 表能創(chuàng)建的索引數(shù)量沒有一個確切的、通用的絕對上限，而是要綜合考慮多方面因素，在滿足性能要求和存儲空間允許的條件下合理創(chuàng)建索引。
其實做了這么長時間的語句優(yōu)化后才發(fā)現(xiàn)，任何數(shù)據(jù)庫層面的優(yōu)化都抵不上應(yīng)用系統(tǒng)的優(yōu)化，同樣是MySQL，可以用來支撐Google/FaceBook/Taobao應(yīng)用，但可能連你的個人網(wǎng)站都撐不住。套用最近比較流行的話：“查詢?nèi)菀祝瑑?yōu)化不易，且寫且珍惜！”
