上周的一天，到公司接了杯水剛剛坐穩(wěn)，就看到 DBA就在群里@ 某個(gè)研發(fā)帥哥，說“你們表已經(jīng)有10個(gè)索引了，怎么這次還要加呢？”

關(guān)于索引，是數(shù)據(jù)庫后臺(tái)用來加快查詢速度的強(qiáng)大工具，索引通過提供快速查找所需數(shù)據(jù)的方法來增強(qiáng)查詢能力。

但我們經(jīng)常聽到這樣一句話：“索引是把雙刃劍”。

說明索引并不是越多越好，索引可以提高查詢的效率，但會(huì)降低寫數(shù)據(jù)的效率，有時(shí)不恰當(dāng)?shù)乃饕€會(huì)降低查詢的效率。

那我就在想：一張表到底建多少個(gè)索引才是合適呢？

要搞懂這個(gè)問題，我們就需要弄清楚以下這幾個(gè)問題：

1）常見的索引分類有哪些？

2）MySQL 是如何使用索引的？

3）一張表最多可以建多少索引？

4）新建索引的規(guī)范原則有哪些？

本文我們就一起來展開聊聊這幾個(gè)問題~

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ù)來實(shí)現(xiàn)。哈希函數(shù)會(huì)將索引鍵值（如數(shù)據(jù)庫表中的某個(gè)字段值）作為輸入，通過特定的算法運(yùn)算，生成一個(gè)固定長度的哈希值。
• 查詢速度快，但不支持范圍查找

• B 樹（btree）索引

• 一種平衡的多叉樹數(shù)據(jù)結(jié)構(gòu)。B 樹索引會(huì)將表中的索引鍵值按照一定的順序（如升序或降序）存儲(chǔ)在樹的節(jié)點(diǎn)中。每個(gè)節(jié)點(diǎn)可以存儲(chǔ)多個(gè)鍵值以及指向其他節(jié)點(diǎn)的指針。

• 支持范圍查詢，但占用空間較大

圖片

2、新建索引的規(guī)范原則有哪些？

關(guān)于新建索引，通常需要注意以下規(guī)范原則：

2.1 最左前綴匹配原則

MySQL 會(huì)一直向右匹配直到遇到范圍查詢(>、<、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 索引列不能參與計(jì)算

保持索引列“干凈”，這個(gè)原因其實(shí)很簡單，b+樹中存的都是數(shù)據(jù)表中的字段值。但是在進(jìn)行檢索時(shí)，需要把所有元素都應(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)的索引，那么只需要修改原來的索引即可，而不建議再單獨(dú)去建一個(gè)b索引。

3、MySQL 是如何使用索引的？

索引用于快速查找具有特定列值的行，其目的在于提高查詢效率。

與我們查閱圖書所用的目錄是一個(gè)道理：先定位到章，然后定位到該章下的一個(gè)小節(jié)，然后找到頁數(shù)。相似的例子還有：查字典，查火車車次，飛機(jī)航班等。

本質(zhì)都是：通過不斷地縮小想要獲取數(shù)據(jù)的范圍來篩選出最終想要的結(jié)果，同時(shí)把隨機(jī)的事件變成順序的事件。

也就是說，有了這種索引機(jī)制，我們可以總是用同一種查找方式來鎖定數(shù)據(jù)。

數(shù)據(jù)庫也是一樣，但顯然要復(fù)雜得多，因?yàn)椴粌H面臨著等值查詢，還有范圍查詢(>、<、between、in)、模糊查詢(like)、并集查詢(or)等等。數(shù)據(jù)庫應(yīng)該選擇怎么樣的方式來應(yīng)對(duì)所有的問題呢？

大多數(shù) MySQL 索引（PRIMARY KEY、UNIQUEINDEX 和FULLTEXT）都存儲(chǔ)在 B樹 中。

另外：

空間數(shù)據(jù)類型使用 R 樹；

MEMORYtable 還支持哈希索引；

InnoDB 對(duì) FULLTEXT 索引使用倒排列表。

在 MySQL 中，使用索引進(jìn)行以下操作：

3.1  = 和 in 可以亂序

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，MySQL 的查詢優(yōu)化器會(huì)幫你優(yōu)化成索引可以識(shí)別的形式。

3.2 and 與 or

聯(lián)合索引：(d,a,b,c)

1）查詢條件：

a = 10 and b = 'xxx' and c > 3 and d =4

MySQL 會(huì)按照聯(lián)合索引，從左到右的順序找一個(gè)區(qū)分度高的索引字段(這樣便可以快速鎖定很小的范圍)，加速查詢，即按照d—>a->b->c的順序

2）查詢條件：

a = 10 or b = 'xxx' or c > 3 or d =4

MySQL 會(huì)按照條件的順序，從左到右依次判斷，即a->b->c->d

4、一張表最多可以建多少個(gè)索引？

4.1 理論上來說

MySQL 的存儲(chǔ)引擎（如 InnoDB、MyISAM 等）本身并沒有對(duì)一個(gè)表能創(chuàng)建的索引數(shù)量設(shè)置一個(gè)固定數(shù)值限制，，而是由MySQL數(shù)據(jù)庫引擎內(nèi)部的數(shù)據(jù)結(jié)構(gòu)和算法決定的。

從數(shù)據(jù)庫設(shè)計(jì)和架構(gòu)的角度，理論上只要滿足以下條件，就可以新增創(chuàng)建索引：

• 存儲(chǔ)空間允許：每個(gè)索引都需要占用一定的磁盤空間來存儲(chǔ)索引數(shù)據(jù)結(jié)構(gòu)，所以只要磁盤空間足夠容納新創(chuàng)建的索引結(jié)構(gòu)及其相關(guān)數(shù)據(jù)，在空間層面就不會(huì)因空間不足而無法創(chuàng)建索引。
• 性能可接受：隨著索引數(shù)量的增加，雖然查詢性能在某些情況下可能會(huì)因合適的索引而提升，但過多的索引也會(huì)帶來一些負(fù)面效應(yīng)，比如數(shù)據(jù)更新（插入、刪除、修改操作）時(shí)需要同時(shí)更新相關(guān)索引，這會(huì)導(dǎo)致更新操作變慢。只要系統(tǒng)整體性能（包括查詢性能和更新性能等）在可接受的范圍內(nèi)，理論上可以繼續(xù)創(chuàng)建索引。

4.2 實(shí)際應(yīng)用情況

然而在實(shí)際應(yīng)用場(chǎng)景中，通常不會(huì)無限制地創(chuàng)建索引。一方面是因?yàn)樯鲜鎏岬降男阅軉栴}，過多的索引往往會(huì)導(dǎo)致數(shù)據(jù)更新操作變得極為緩慢，嚴(yán)重影響系統(tǒng)的正常運(yùn)行。

例如，在一個(gè)高并發(fā)的電商訂單處理系統(tǒng)中，如果對(duì)訂單表的大量字段都創(chuàng)建了索引，那么每一次訂單的插入、修改或刪除操作，都要花費(fèi)大量時(shí)間來更新相關(guān)索引，導(dǎo)致訂單處理效率大幅下降。

另一方面，不同的 MySQL 版本以及不同的存儲(chǔ)引擎在實(shí)際表現(xiàn)上也會(huì)有差異。

例如，對(duì)于一個(gè)擁有百萬條記錄的用戶信息表，在 MySQL 5.7 中按照用戶姓氏進(jìn)行模糊查詢，可能需要遍歷相當(dāng)一部分?jǐn)?shù)據(jù)，查詢速度相對(duì)較慢，假設(shè)平均每次查詢需要 20 秒。而 MySQL 8.0 引入了新的索引算法和數(shù)據(jù)結(jié)構(gòu)優(yōu)化，新的索引算法對(duì)這種模糊查詢的支持更好，同樣的查詢可能只需要 5 秒左右，性能提升明顯。

總結(jié)

索引是應(yīng)用程序設(shè)計(jì)和開發(fā)的一個(gè)重要方面。若索引太多，應(yīng)用程序的性能可能會(huì)受到影響。而索引太少，對(duì)查詢性能又會(huì)產(chǎn)生影響，要找到一個(gè)平衡點(diǎn)，這對(duì)應(yīng)用程序的性能至關(guān)重要。

MySQL 表能創(chuàng)建的索引數(shù)量沒有一個(gè)確切的、通用的絕對(duì)上限，而是要綜合考慮多方面因素，在滿足性能要求和存儲(chǔ)空間允許的條件下合理創(chuàng)建索引。

其實(shí)做了這么長時(shí)間的語句優(yōu)化后才發(fā)現(xiàn)，任何數(shù)據(jù)庫層面的優(yōu)化都抵不上應(yīng)用系統(tǒng)的優(yōu)化，同樣是MySQL，可以用來支撐Google/FaceBook/Taobao應(yīng)用，但可能連你的個(gè)人網(wǎng)站都撐不住。套用最近比較流行的話：“查詢?nèi)菀?，?yōu)化不易，且寫且珍惜！”