尼恩說在前面：

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

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

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

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

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

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

1.1 什么是索引？

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

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

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

在MySQL中，索引 是一種特殊的數(shù)據(jù)結(jié)構(gòu)，通常是B+樹，它存儲(chǔ)著字段值 和對(duì)應(yīng)記錄的主鍵值。

1.2 為什么需要索引？

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

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

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

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

1.3 索引的常見類型

索引的常見類型 有：

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

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

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

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

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

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

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

2. InnoDB存儲(chǔ)引擎的索引限制

InnoDB存儲(chǔ)引擎 是 MySQL  最常用的存儲(chǔ)引擎，

InnoDB  作為MySQL5.5后的默認(rèn)引擎，InnoDB支持事務(wù)、行級(jí)鎖、外鍵約束等高級(jí)功能。

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

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

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

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

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

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

尼恩對(duì)索引 使用建議：

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

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

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

官方文檔有說明如下：

image.png

鏈接如下：

dev.mysql.com/doc/refman/…

InnoDB最多允許64個(gè)二級(jí)索引（非主鍵索引）， 當(dāng)然， 還有 加上1個(gè)主鍵索引，總共65個(gè)索引。

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

而對(duì)于一個(gè)索引，最多有多少列呢？

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

InnoDB  中，一個(gè) 索引  最多 能允許  多少個(gè) 列 ？

結(jié)論是： 一個(gè) 索引最多是16列。

官方文檔也是有說明的：

image.png

鏈接如下：

dev.mysql.com/doc/refman/…

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

但要注意，這些是理論最大值，實(shí)際應(yīng)用中應(yīng)該遠(yuǎn)低于這個(gè)限制。

索引越多，或者一個(gè)索引里邊的 列越多，  維護(hù)成本越高，特別是對(duì)于寫入頻繁的表，過多的索引會(huì)嚴(yán)重影響性能。

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

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

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

3. MyISAM存儲(chǔ)引擎的索引限制

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

MyISAM  適合讀多寫少的場景。

MyISAM 不支持事務(wù)和行級(jí)鎖，但查詢速度非?？欤貏e適合讀多寫少的場景。

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

3.1 索引數(shù)量限制

MyISAM每個(gè)表最多支持64個(gè)索引，主鍵索引不計(jì)入此限制。

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

MyISAM的索引使用B-tree結(jié)構(gòu)存儲(chǔ)，支持前綴索引，可以只對(duì)字段的前N個(gè)字符建立索引。

MyISAM存儲(chǔ)引擎  的 索引 限制 如下：

• 最多64個(gè)索引（主鍵不算在內(nèi)）
• 每個(gè)索引最多16個(gè)字段

3.2 MyISAM 與InnoDB的區(qū)別

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

• MyISAM 不支持事務(wù)
• MyISAM 表級(jí)鎖（不是行級(jí)鎖）
• MyISAM  適合讀多寫少的場景

MyISAM不支持事務(wù)，而InnoDB支持；

MyISAM只有表級(jí)鎖，InnoDB支持行級(jí)鎖；

MyISAM不支持外鍵，InnoDB支持；

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

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

選擇存儲(chǔ)引擎時(shí)，如果不需要事務(wù)且讀多寫少，可以考慮MyISAM，否則應(yīng)該選擇InnoDB。

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

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

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

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

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

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

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

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

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

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

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

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

總之： 適當(dāng)?shù)乃饕芴岣卟樵冃?，過多的索引會(huì)影響數(shù)據(jù)庫表的插入和更新功能。

有些時(shí)候，不加索引更合適：

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

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

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

因?yàn)椋饕? 太多的  "副作用"  ：

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

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

5. 索引過多會(huì)導(dǎo)致的 "七宗罪"

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

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

5.1 第一宗罪：寫入變慢

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

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

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

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

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

5.2 第二宗罪：磁盤空間浪費(fèi)

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

每個(gè)索引都需要額外的磁盤存儲(chǔ)空間。

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

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

這不僅增加了存儲(chǔ)成本，還會(huì)影響備份恢復(fù)的速度。

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

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

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

當(dāng) Buffer Pool  緩沖池?zé)o法容納常用數(shù)據(jù)時(shí)，MySQL就需要頻繁地從磁盤讀取數(shù)據(jù)，嚴(yán)重影響性能。

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

5.4 第4宗罪：鎖競爭加劇

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

特別是當(dāng)多個(gè)事務(wù)同時(shí)修改同一索引時(shí)，可能出現(xiàn)鎖等待甚至死鎖。

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

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

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

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

索引太多會(huì)增加優(yōu)化器做出錯(cuò)誤選擇的風(fēng)險(xiǎn)，可能導(dǎo)致性能反而下降。

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

5.6 第6宗罪：維護(hù)困難

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

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

備份恢復(fù)也會(huì)變慢，因?yàn)樾枰幚砀嗟乃饕龜?shù)據(jù)。

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

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

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

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

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

雖然可以手動(dòng)分析表來更新統(tǒng)計(jì)信息，但這會(huì)增加維護(hù)負(fù)擔(dān)。

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

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

大家對(duì)于 索引的使用，存在很多誤區(qū)，其中  最大的誤區(qū)是認(rèn)為"索引越多查詢?cè)娇?，實(shí)際上索引過多會(huì)降低整體性能。

另一個(gè)誤區(qū)是為所有查詢字段都建索引，這會(huì)導(dǎo)致索引泛濫。還有人認(rèn)為聯(lián)合索引字段順序無關(guān)緊要，實(shí)際上順序?qū)λ饕视绊懞艽蟆?/span>

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

6.1 哪些情況不加索引？

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

第二：頻繁更新的字段（寫多讀少的字段），要謹(jǐn)慎加索引。為啥呢  ？ 因?yàn)槊看胃露夹枰S護(hù)索引。

第三：區(qū)分度低的字段（如性別、狀態(tài)標(biāo)志），通常不適合單獨(dú)建索引。為啥呢  ？因?yàn)樗饕Ч幻黠@。

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

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

6.2 如何設(shè)計(jì)高效索引？

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

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

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

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

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

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

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

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

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

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

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

7. 索引的真實(shí)案例分享

理論結(jié)合實(shí)踐才能更好掌握索引設(shè)計(jì)，下面分享兩個(gè)真實(shí)案例。

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

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

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

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

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

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

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

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

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

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

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

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

create_time建立索引支持時(shí)間范圍查詢。

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

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

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

8. 總結(jié)：索引使用黃金法則

經(jīng)過前面的詳細(xì)講解，我們可以總結(jié)出一些索引使用的黃金法則。

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

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

這些法則不是死板的教條，而是指導(dǎo)性的原則，在實(shí)際應(yīng)用中需要根據(jù)具體情況進(jìn)行調(diào)整。