尼恩說在前面：

在40歲老架構師 尼恩的讀者交流群(50+)中，最近有小伙伴拿到了一線互聯網企業如得物、阿里、滴滴、極兔、有贊、shein 希音、shopee、百度、網易的面試資格，遇到很多很重要的面試題：

• mysql中，一個表最多只能加多少個索引嘛？一個聯合索引最多只能多少列呢？
• 索引加多了，會存在哪些問題呢？
• InnoDB存儲引擎
• MyISAM存儲引擎
• 一個表設計多少個索引合理呢？
• 索引設計過多存在哪些問題？
• 阿里巴巴編程規范中，  單表索引數量，建議控制在5個以內 ，為什么？

前幾天 小伙伴面試阿里，遇到了這個問題。但是由于 沒有回答好，導致面試掛了。

小伙伴面試完了之后，來求助尼恩。那么，遇到 這個問題，該如何才能回答得很漂亮，才能 讓面試官刮目相看、口水直流。

所以，尼恩給大家做一下系統化、體系化的梳理，使得大家內力猛增，可以充分展示一下大家雄厚的 “技術肌肉”，讓面試官愛到 “不能自已、口水直流”，然后實現”offer直提”。

1. 索引基礎 ：一個 表的 "數據目錄"

1.1 什么是索引？

• 沒有索引：數據庫要掃描整張表，就像你從圖書館第一本書開始找
• 有索引：直接定位到數據位置，效率提升幾十甚至上百倍

索引就是"數據的目錄" ， 想象一下你去圖書館找書，沒有目錄的話你得一本本翻，有了目錄就能直接找到想要的書。

索引就是數據庫的"數據目錄"，它能幫你快速定位數據。

在MySQL中，索引 是一種特殊的數據結構，通常是B+樹，它存儲著字段值 和對應記錄的主鍵值。

1.2 為什么需要索引？

沒有索引時，數據庫要執行全表掃描，就像你從圖書館第一本書開始一本本找，數據量越大查詢越慢。

有索引后，數據庫可以直接定位數據位置，效率提升幾十甚至上百倍。

數據量越大， 索引的價值就大， 在百萬級、甚至千萬級的 表中，有索引的查詢可能只需幾毫秒，沒索引可能要幾秒， 甚至更久。

但凡事都有不利的一面，  索引不是萬能的，它是以額外存儲空間和寫入性能為代價換取查詢速度的提升，需要權衡利弊。

1.3 索引的常見類型

索引的常見類型 有：

• 普通索引：最基本的索引，沒有任何限制
• 唯一索引：要求索引列的值必須唯一
• 主鍵索引：特殊的唯一索引，不允許有空值
• 聯合索引：多個列組合的索引

普通索引是最基本的索引類型，沒有任何限制，允許重復值和空值。

唯一索引要求索引列的值必須唯一，但允許有空值。

主鍵索引是特殊的唯一索引，不允許有空值，每個表只能有一個。

聯合索引是多個列組合的索引，遵循最左前綴原則。

此外還有全文索引、空間索引等特殊類型。

不同類型的索引適用于不同場景，比如用戶名適合用唯一索引，文章內容適合用全文索引。

2. InnoDB存儲引擎的索引限制

InnoDB存儲引擎 是 MySQL  最常用的存儲引擎，

InnoDB  作為MySQL5.5后的默認引擎，InnoDB支持事務、行級鎖、外鍵約束等高級功能。

InnoDB   的索引采用聚簇索引（主鍵索引）  +  非聚簇索引 （二級索引） 結合的結構，主鍵索引的葉子節點直接存儲行數據，這使得主鍵查詢特別高效。

InnoDB還支持MVCC多版本并發控制，大大提高了并發讀寫性能。

對于大多數業務場景，InnoDB都是最佳選擇。

InnoDB是MySQL最常用的存儲引擎，它就像一輛高性能跑車，既穩定又快速。

InnoDB存儲引擎  索引數量限制

• 最多64個普通索引 + 1個主鍵索引 = 65個
• 每個索引最多包含16個字段

尼恩對索引 使用建議：

• 雖然能創建 65個，除非迫不得已，不建議這么干！
• 就比如說，像一個人能吃10碗飯，不代表就一定要吃10碗。

2.1 InnoDB索引的數量限制

根據MySQL官方文檔， InnoDB存儲引擎  它最多允許 一個表最多   64個二級索引（即非主鍵索引），

官方文檔有說明如下：

image.png

鏈接如下：

dev.mysql.com/doc/refman/…

InnoDB最多允許64個二級索引（非主鍵索引）， 當然， 還有 加上1個主鍵索引，總共65個索引。

那在InnoDB中，一個表，最多可以有 64+1=65 個索引

而對于一個索引，最多有多少列呢？

2.2 InnoDB索引列的數量限制

InnoDB  中，一個 索引  最多 能允許  多少個 列 ？

結論是： 一個 索引最多是16列。

官方文檔也是有說明的：

image.png

鏈接如下：

dev.mysql.com/doc/refman/…

每個索引最多可以包含16個字段，這意味，  可以創建一個包含16個字段的超級聯合索引。

但要注意，這些是理論最大值，實際應用中應該遠低于這個限制。

索引越多，或者一個索引里邊的 列越多，  維護成本越高，特別是對于寫入頻繁的表，過多的索引會嚴重影響性能。

通常建議單表索引不超過5-8個，核心查詢字段優先建索引。

對于聯合索引，字段數最好控制在3-5個以內。

尼恩 建議是 ：  定期使用EXPLAIN分析查詢語句，確保索引被正確使用，刪除冗余和低效的索引。

3. MyISAM存儲引擎的索引限制

MyISAM 是 MySQL早期的默認存儲引擎，雖然現在用得少了，但在某些場景下仍有價值。

MyISAM  適合讀多寫少的場景。

MyISAM 不支持事務和行級鎖，但查詢速度非?？?，特別適合讀多寫少的場景。

尼恩提示：MyISAM的表級鎖在寫入時會鎖定整個表，不適合高并發寫入場景。

3.1 索引數量限制

MyISAM每個表最多支持64個索引，主鍵索引不計入此限制。

每個索引最多可以包含16個字段，與InnoDB相同。

MyISAM的索引使用B-tree結構存儲，支持前綴索引，可以只對字段的前N個字符建立索引。

MyISAM存儲引擎  的 索引 限制 如下：

• 最多64個索引（主鍵不算在內）
• 每個索引最多16個字段

3.2 MyISAM 與InnoDB的區別

MyISAM和InnoDB的主要區別包括：

• MyISAM 不支持事務
• MyISAM 表級鎖（不是行級鎖）
• MyISAM  適合讀多寫少的場景

MyISAM不支持事務，而InnoDB支持；

MyISAM只有表級鎖，InnoDB支持行級鎖；

MyISAM不支持外鍵，InnoDB支持；

MyISAM的崩潰恢復能力較弱，InnoDB更可靠；

MyISAM的全文索引較早出現，但現在InnoDB也支持了。

選擇存儲引擎時，如果不需要事務且讀多寫少，可以考慮MyISAM，否則應該選擇InnoDB。

4. 索引數量：少即是多

在數據庫設計中，索引數量應該遵循"少即是多"的原則。

過多的索引不僅不能提高性能，反而會帶來各種問題。

索引就像書中的目錄，一本幾百頁的書有3-5個目錄章節就足夠了，如果每頁都做一個目錄，反而會讓查找變得困難。

數據庫索引也是如此，需要精心設計，只給真正需要的查詢條件建立索引。

4.1 阿里巴巴規范建議

日常開發中，一個表設計多少個索引合適呢？

阿里巴巴《Java開發手冊》技術文檔，單表索引數量建議控制在5個以內, 單個索引的字段數不超過5個。

阿里巴巴《Java開發手冊》建議單表索引數量控制在5個以內，這是基于多年實戰經驗得出的結論。

5個索引對于大多數業務場景已經足夠，能夠覆蓋主要的查詢需求。

這個建議不是絕對的，對于特別復雜的業務表可以適當增加，但必須有充分的理由。

規范還建議單個索引的字段數不超過5個，避免創建過于復雜的聯合索引。

總之： 適當的索引能提高查詢效率，過多的索引會影響數據庫表的插入和更新功能。

有些時候，不加索引更合適：

• 數據量少的表，不適合加索引
• 更新比較頻繁的也不適合加索引

4.2 為什么阿里巴巴規范建議是5個？

阿里巴巴的《Java開發手冊》建議單表索引不超過5個，為啥呢？

因為，索引  太多的  "副作用"  ：

• 寫數據變慢：就像你每寫一篇日記，都要在10個不同的目錄里更新位置，累不累？
• 占用空間大：每個索引都要單獨存一份數據，就像你為了找書方便，買了10本一模一樣的字典放家里
• MySQL會犯選擇困難癥：索引太多，MySQL反而可能選錯最快的查詢路徑
• 維護成本高：備份、遷移數據時，索引越多越慢

所以，現實中的最佳實踐：  5個以內最健康。

5. 索引過多會導致的 "七宗罪"

索引雖然能提高查詢速度，但過多索引會帶來一系列問題，過度索引帶來的性能下降和維護困難 ， 這里 總結為索引的"七宗罪"。

理解 "七宗罪" 問題，有助于我們更好地設計索引策略，避免過度索引帶來的性能下降和維護困難。

5.1 第一宗罪：寫入變慢

每次執行INSERT、UPDATE、DELETE操作時，MySQL不僅要修改數據，還要更新所有相關的索引。

索引越多，寫入操作就越慢。

特別是在批量導入數據時，索引會顯著降低導入速度。

測試表明，一個沒有索引的表可能比有10個索引的表寫入速度快10倍以上。

對于在線web服務系統（如電商平臺、 金融交易平臺），過多的索引會導致系統吞吐量大幅下降。

5.2 第二宗罪：磁盤空間浪費

占用空間大：每個索引都要單獨存一份數據，就像你為了找書方便，買了10本一模一樣的字典放家里

每個索引都需要額外的磁盤存儲空間。

對于InnoDB，索引和數據存儲在同一個文件中，索引越多，文件越大。

一個包含10個索引的百萬級數據表，索引可能占用幾GB甚至更多的空間。

這不僅增加了存儲成本，還會影響備份恢復的速度。

5.3 第三宗罪：緩存效率降低

InnoDB使用 Buffer Pool 緩沖池來緩存數據和索引。

索引太多會占用大量 Buffer Pool  緩沖池空間，導致數據和索引的緩存命中率下降。

當 Buffer Pool  緩沖池無法容納常用數據時，MySQL就需要頻繁地從磁盤讀取數據，嚴重影響性能。

合理的索引數量可以讓緩沖池緩存更多熱點數據。

5.4 第4宗罪：鎖競爭加劇

在高并發環境下，索引更新會導致鎖競爭加劇。

特別是當多個事務同時修改同一索引時，可能出現鎖等待甚至死鎖。

InnoDB的行級鎖雖然緩解了這個問題，但索引太多仍然會增加鎖沖突的概率，影響系統并發性能。

5.5 第5宗罪：優化器困惑

MySQL會犯選擇困難癥：索引太多，MySQL反而可能選錯最快的查詢路徑

當表中有多個索引時，MySQL優化器需要選擇使用哪個索引來執行查詢。

索引太多會增加優化器做出錯誤選擇的風險，可能導致性能反而下降。

比如優化器可能選擇區分度不高的索引，或者錯誤估計索引的選擇性。這時就需要使用FORCE INDEX等提示來強制使用特定索引。

5.6 第6宗罪：維護困難

索引越多，數據庫維護工作就越復雜。

ALTER TABLE操作會變得更慢，特別是在大表上添加或刪除索引可能需要很長時間。

備份恢復也會變慢，因為需要處理更多的索引數據。

此外，監控和管理大量索引也需要更多的時間和精力。

5.7 第7宗罪：統計信息更新變慢

MySQL使用統計信息來優化查詢執行計劃。

索引越多，收集和維護統計信息所需的時間和資源就越多。

在數據變化頻繁的表上，過時的統計信息可能導致優化器選擇低效的執行計劃。

雖然可以手動分析表來更新統計信息，但這會增加維護負擔。

6. 索引使用實戰技巧

掌握了索引的基本原理后，尼恩建議大家 需要了解一些索引的實戰技巧， 幫助我們在實際項目中更好地設計和使用索引。

大家對于 索引的使用，存在很多誤區，其中  最大的誤區是認為"索引越多查詢越快"，實際上索引過多會降低整體性能。

另一個誤區是為所有查詢字段都建索引，這會導致索引泛濫。還有人認為聯合索引字段順序無關緊要，實際上順序對索引效率影響很大。

此外，過度依賴自動創建的索引、不評估索引使用效果、不刪除無用索引等都是常見問題。

6.1 哪些情況不加索引？

第一：數據量小的表（如配置表）不需要索引。為啥呢  ？  因為數據量小的表 在查詢的時候， 全表掃描可能比索引查找更快。

第二：頻繁更新的字段（寫多讀少的字段），要謹慎加索引。為啥呢  ？ 因為每次更新都需要維護索引。

第三：區分度低的字段（如性別、狀態標志），通常不適合單獨建索引。為啥呢  ？因為索引效果不明顯。

第四：太長的字段（如TEXT）不要加索引。如果一定要加，就要使用前綴索引。

第五： NULL值過多的字段，也不建議 加索引。

6.2 如何設計高效索引？

• 首先分析業務查詢模式，優先為高頻查詢條件建索引。
• 聯合索引要注意字段順序，區分度高的字段放前面。
• 避免創建冗余索引，比如已有(a,b)索引就不需要單獨建a索引。
• 定期使用EXPLAIN分析慢查詢，優化索引策略。
• 考慮使用覆蓋索引減少回表操作。
• 對于長字符串，考慮使用前綴索引節省空間。

6.3 對索引進行定期監控和優化

索引不是建完就一勞永逸的，需要定期監控和優化。

建議每月至少檢查一次索引使用情況，刪除無用索引。

使用SHOW INDEX FROM table命令可以查看表的索引信息，包括索引名稱、字段、基數等。

通過sys.schema_unused_indexes  視圖可以找出長期未使用的索引。

EXPLAIN命令可以分析查詢是否使用了合適的索引。

對于數據變化大的表，定期ANALYZE TABLE更新統計信息。

監控索引碎片化程度，必要時重建索引。

建立索引變更評審機制，避免隨意添加索引。記錄索引變更歷史，便于問題追蹤。

對于重要系統，可以考慮使用索引管理工具。

7. 索引的真實案例分享

理論結合實踐才能更好掌握索引設計，下面分享兩個真實案例。

這些案例來自實際項目經驗，展示了如何根據具體業務需求設計合理的索引策略，以及不當索引設計可能導致的問題和解決方案。

案例1：電商系統用戶表的索引案例分享

主鍵使用自增user_id，保證寫入性能。

mobile和email字段建立唯一索引，用于登錄和密碼找回。

register_time建立索引用于新用戶分析。

last_login建立索引用于活躍用戶統計。

nickname使用前綴索引支持模糊搜索。

避免為gender等低區分度字段單獨建索引。

定期清理不活躍用戶的索引條目。

案例2：訂單系統、訂單表的索引案例分享

主鍵使用order_id，分布式系統可以考慮雪花ID。

user_id建立索引支持用戶查詢。

create_time建立索引支持時間范圍查詢。

status和payment_type建立聯合索引用于訂單分析。

避免為price等頻繁更新的字段單獨建索引。

考慮使用部分索引只索引未完成訂單。定期歸檔歷史訂單減少索引大小。

8. 總結：索引使用黃金法則

經過前面的詳細講解，我們可以總結出一些索引使用的黃金法則。

記住這些法則可以幫助我們避免常見的索引設計錯誤，建立高效的數據庫結構。

• 不是所有字段都需要索引，只為真正需要的查詢條件建索引。
• 聯合索引優于多個單列索引，但要注意字段順序。
• 區分度高的字段更適合索引，低區分度字段考慮聯合索引。
• 定期維護比盲目添加更重要，及時刪除無用索引。
• 5個以內最健康，超過8個要三思，必須有充分理由。
• 理解業務查詢模式是設計好索引的前提。
• 監控和優化是持續過程，不是一次性的工作。

這些法則不是死板的教條，而是指導性的原則，在實際應用中需要根據具體情況進行調整。