尼恩說在前面：

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

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

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

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

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

1. 索引基礎 ：一個 表的 "數(shù)據(jù)目錄"

1.1 什么是索引？

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

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

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

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

1.2 為什么需要索引？

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

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

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

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

1.3 索引的常見類型

索引的常見類型 有：

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

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

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

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

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

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

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

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

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

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

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

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

對于大多數(shù)業(yè)務場景，InnoDB都是最佳選擇。

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

InnoDB存儲引擎  索引數(shù)量限制

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

尼恩對索引 使用建議：

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

2.1 InnoDB索引的數(shù)量限制

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

官方文檔有說明如下：

image.png

鏈接如下：

dev.mysql.com/doc/refman/…

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

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

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

2.2 InnoDB索引列的數(shù)量限制

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

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

官方文檔也是有說明的：

image.png

鏈接如下：

dev.mysql.com/doc/refman/…

每個索引最多可以包含16個字段，這意味，  可以創(chuàng)建一個包含16個字段的超級聯(lián)合索引。

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

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

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

對于聯(lián)合索引，字段數(shù)最好控制在3-5個以內。

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

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

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

MyISAM  適合讀多寫少的場景。

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

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

3.1 索引數(shù)量限制

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

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

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

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

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

3.2 MyISAM 與InnoDB的區(qū)別

MyISAM和InnoDB的主要區(qū)別包括：

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

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

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

MyISAM不支持外鍵，InnoDB支持；

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

MyISAM的全文索引較早出現(xiàn)，但現(xiàn)在InnoDB也支持了。

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

4. 索引數(shù)量：少即是多

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

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

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

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

4.1 阿里巴巴規(guī)范建議

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

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

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

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

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

規(guī)范還建議單個索引的字段數(shù)不超過5個，避免創(chuàng)建過于復雜的聯(lián)合索引。

總之： 適當?shù)乃饕芴岣卟樵冃剩^多的索引會影響數(shù)據(jù)庫表的插入和更新功能。

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

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

4.2 為什么阿里巴巴規(guī)范建議是5個？

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

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

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

所以，現(xiàn)實中的最佳實踐：  5個以內最健康。

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

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

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

5.1 第一宗罪：寫入變慢

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

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

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

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

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

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

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

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

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

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

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

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

InnoDB使用 Buffer Pool 緩沖池來緩存數(shù)據(jù)和索引。

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

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

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

5.4 第4宗罪：鎖競爭加劇

在高并發(fā)環(huán)境下，索引更新會導致鎖競爭加劇。

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

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

5.5 第5宗罪：優(yōu)化器困惑

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

當表中有多個索引時，MySQL優(yōu)化器需要選擇使用哪個索引來執(zhí)行查詢。

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

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

5.6 第6宗罪：維護困難

索引越多，數(shù)據(jù)庫維護工作就越復雜。

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

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

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

5.7 第7宗罪：統(tǒng)計信息更新變慢

MySQL使用統(tǒng)計信息來優(yōu)化查詢執(zhí)行計劃。

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

在數(shù)據(jù)變化頻繁的表上，過時的統(tǒng)計信息可能導致優(yōu)化器選擇低效的執(zhí)行計劃。

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

6. 索引使用實戰(zhàn)技巧

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

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

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

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

6.1 哪些情況不加索引？

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

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

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

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

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

6.2 如何設計高效索引？

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

6.3 對索引進行定期監(jiān)控和優(yōu)化

索引不是建完就一勞永逸的，需要定期監(jiān)控和優(yōu)化。

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

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

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

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

對于數(shù)據(jù)變化大的表，定期ANALYZE TABLE更新統(tǒng)計信息。

監(jiān)控索引碎片化程度，必要時重建索引。

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

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

7. 索引的真實案例分享

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

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

案例1：電商系統(tǒng)用戶表的索引案例分享

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

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

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

last_login建立索引用于活躍用戶統(tǒng)計。

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

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

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

案例2：訂單系統(tǒng)、訂單表的索引案例分享

主鍵使用order_id，分布式系統(tǒng)可以考慮雪花ID。

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

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

status和payment_type建立聯(lián)合索引用于訂單分析。

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

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

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

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

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

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

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