&

一張表到底建多少個索引才是合適呢？

freeflydom

2025年4月14日 10:45 本文熱度 321

上周的一天，到公司接了杯水剛剛坐穩，就看到 DBA就在群里@ 某個研發帥哥，說“你的表已經有10個索引了，怎么這次還要再加呢？”

關于索引，是數據庫后臺用來加快查詢速度的強大工具，索引通過提供快速查找所需數據的方法來增強查詢能力。

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

說明索引并不是越多越好，索引可以提高查詢的效率，但會降低寫數據的效率，有時不恰當的索引還會降低查詢的效率。

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

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

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

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

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

4）新建索引的規范原則有哪些？

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

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

1.1 應用層分類

從應用層面，常見分類：

普通索引INDEX：加速查找
唯一索引：
主鍵索引PRIMARY KEY：加速查找+約束（不為空、不能重復）
唯一索引UNIQUE:加速查找+約束（不能重復）
聯合索引：
PRIMARY KEY(id,name):聯合主鍵索引
UNIQUE(id,name):聯合唯一索引
INDEX(id,name):聯合普通索引

1.2 數據結構層分類

從數據結構層面，分類如下：

哈希（hash）索引
基于哈希函數來實現。哈希函數會將索引鍵值（如數據庫表中的某個字段值）作為輸入，通過特定的算法運算，生成一個固定長度的哈希值。
查詢速度快，但不支持范圍查找
B 樹（btree）索引
一種平衡的多叉樹數據結構。B 樹索引會將表中的索引鍵值按照一定的順序（如升序或降序）存儲在樹的節點中。每個節點可以存儲多個鍵值以及指向其他節點的指針。
支持范圍查詢，但占用空間較大

2、新建索引的規范原則有哪些？

關于新建索引，通常需要注意以下規范原則：

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的順序可以任意調整。

2.2 盡量選擇區分度高的列作為索引

區分度的公式是：count(distinct col)/count(*)，表示字段不重復的比例，比例越大我們掃描的記錄數越少。

唯一鍵的區分度是1，而一些 status 狀態、性別等字段可能在大數據面前區分度就是0。

2.3 索引列不能參與計算

保持索引列“干凈”，這個原因其實很簡單，b+樹中存的都是數據表中的字段值。但是在進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。

比如 from_unixtime(create_time) = ’2014-05-29’ 就不能使用到索引，需要將語句改寫成：create_time = unix_timestamp(’2014-05-29’)。

2.4 盡量的擴展索引，不要新建索引

比如表中已經有a的索引，現在要加(a,b)的索引，那么只需要修改原來的索引即可，而不建議再單獨去建一個b索引。

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

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

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

本質都是：通過不斷地縮小想要獲取數據的范圍來篩選出最終想要的結果，同時把隨機的事件變成順序的事件。

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

數據庫也是一樣，但顯然要復雜得多，因為不僅面臨著等值查詢，還有范圍查詢(>、<、between、in)、模糊查詢(like)、并集查詢(or)等等。數據庫應該選擇怎么樣的方式來應對所有的問題呢？

大多數 MySQL 索引（PRIMARY KEY、UNIQUEINDEX 和FULLTEXT）都存儲在 B樹中。

另外：

空間數據類型使用 R 樹；

MEMORYtable 還支持哈希索引；

InnoDB 對 FULLTEXT 索引使用倒排列表。

在 MySQL 中，使用索引進行以下操作：

3.1 = 和 in 可以亂序

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

3.2 and 與 or

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

查詢條件：

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

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

查詢條件：

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

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

4、一張表最多可以建多少個索引？

4.1 理論上來說

MySQL 的存儲引擎（如 InnoDB、MyISAM 等）本身并沒有對一個表能創建的索引數量設置一個固定數值限制，，而是由MySQL數據庫引擎內部的數據結構和算法決定的。

從數據庫設計和架構的角度，理論上只要滿足以下條件，就可以新增創建索引：

存儲空間允許：每個索引都需要占用一定的磁盤空間來存儲索引數據結構，所以只要磁盤空間足夠容納新創建的索引結構及其相關數據，在空間層面就不會因空間不足而無法創建索引。
性能可接受：隨著索引數量的增加，雖然查詢性能在某些情況下可能會因合適的索引而提升，但過多的索引也會帶來一些負面效應，比如數據更新（插入、刪除、修改操作）時需要同時更新相關索引，這會導致更新操作變慢。只要系統整體性能（包括查詢性能和更新性能等）在可接受的范圍內，理論上可以繼續創建索引。

4.2 實際應用情況

然而在實際應用場景中，通常不會無限制地創建索引。一方面是因為上述提到的性能問題，過多的索引往往會導致數據更新操作變得極為緩慢，嚴重影響系統的正常運行。

例如，在一個高并發的電商訂單處理系統中，如果對訂單表的大量字段都創建了索引，那么每一次訂單的插入、修改或刪除操作，都要花費大量時間來更新相關索引，導致訂單處理效率大幅下降。

另一方面，不同的 MySQL 版本以及不同的存儲引擎在實際表現上也會有差異。

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