索引是什么？有什么好處？

索引類似書籍的目錄，是數(shù)據(jù)庫額外維護(hù)的數(shù)據(jù)結(jié)構(gòu)，可以減少查詢時遍歷的數(shù)據(jù)量，從而提升查詢效率。

• 如果不走索引，查詢的時候就會變成全表掃描，查詢的效率為O(N)。
• 走了索引，那之后查詢就可以基于二分查找算法進(jìn)行快速查詢，MySQL底層用的是B+樹，所以查詢速度可以達(dá)到O(LogbN)。其中b是B+樹的階數(shù)，代表每個非葉子節(jié)點(diǎn)可以指向多少個子節(jié)點(diǎn)。

講講索引的分類情況

1、數(shù)據(jù)結(jié)構(gòu)分類：

如果按照數(shù)據(jù)結(jié)構(gòu)分類，分為三種類型：B+樹索引、Hash索引、Full-Text索引

• B+樹，是一種專門優(yōu)化磁盤查詢的平衡多路查找樹。Innodb索引的底層默認(rèn)使用B+樹結(jié)構(gòu)。
• Hash索引，底層采用的是哈希表，他的查詢速度比B+樹還快，但是范圍查詢表現(xiàn)比較差。
• Full-Text全文索引，底層采用倒排索引，會對傳進(jìn)來的數(shù)據(jù)進(jìn)行分詞，并做一個映射表。像Elasticsearch就用的倒排索引。

而，Innodb引擎支持B+樹索引，與Full-Text全文索引，不支持Hash索引

2、物理存儲分類：

分為 聚簇索引(主鍵索引) 與 非聚簇索引(二級索引、輔助索引)
他們兩個都是把 某種字段 作為鍵 B+樹 的key值。

一張表，只能有一個聚簇索引。
他是取目標(biāo)表的主鍵作為索引，建立B+樹。
若該表沒有設(shè)立主鍵，則取其不含NULL值的唯一索引，建立B+樹。
若連符合條件的唯一索引也沒有的話，則自建一個隱藏列，作為索引列。

而非聚簇索引，可以有很多個，他的作用就是，加速查詢數(shù)據(jù)效率。
幾乎所有字段，都可以被用來建立非聚簇索引。
但是對于像 text/blob 這種字段，不建議建立索引。因?yàn)橛辛烁鷽]有，幾乎沒啥區(qū)別。

此外，聚簇索引的葉子節(jié)點(diǎn)，都是存的整行完整數(shù)據(jù)。故其還有一個索引即數(shù)據(jù)的稱呼。
而非聚簇索引的葉子節(jié)點(diǎn)只會存儲主鍵數(shù)值。
所以，如果你走了非聚簇索引，并且要查詢的值，不是主鍵值，那就必須去先在非聚簇索引中得到目標(biāo)主鍵，然后再去聚簇索引中查找目標(biāo)字段。這個過程被成為回表。但若你查的數(shù)據(jù)在非聚簇索引中就能找到，不在需要回表，這種行為又被稱為覆蓋索引。

3、按字段特性分類

有主鍵索引、唯一索引、普通索引、前綴索引。

• 主鍵索引：以主鍵字段，作為建立索引的鍵值。通常一張表只能有一個，在innodb中又叫聚簇索引。
• 唯一索引：以unique字段，作為建立索引的鍵值，允許為空，一張表可以有多個。
• 普通索引：不指定建立索引的字段的有其他特性?？梢杂卸鄠€。
• 前綴索引：不直接使用一整個字段的數(shù)據(jù)，通常取前k個字符建立索引。如char、varchar類型。

4、按字段個數(shù)分類

通常有單列索引，僅有一個字段（如主鍵）。
另一個是聯(lián)合索引，可以有多個字段結(jié)合在一起，共同作用在一起。

哈希索引的使用場景是什么？卻又為啥不代替redis？

哈希索引，底層用的是哈希表，通常由mysql中的memeory引擎支持，可以快速查詢。
但是功能單一，不適合范圍查詢、沒有過期策略等功能，只是作為關(guān)系型數(shù)據(jù)庫的附屬產(chǎn)品。同時又因?yàn)榇嬖趦?nèi)存中，關(guān)機(jī)即丟失。所以大家往往選擇redis，因?yàn)樗訉I(yè)，且支持持久化。

MySQL聚簇索引和非聚簇索引的區(qū)別是什么？

數(shù)據(jù)存儲不同：雖然兩者都采用的B+樹進(jìn)行存儲，但聚簇索引的葉子節(jié)點(diǎn)內(nèi)存的是鍵值+對應(yīng)行的所有數(shù)據(jù)。而非聚簇索引只存了目標(biāo)行的主鍵值。
唯一性：聚簇索引只能有一個，非聚簇索引可以有多個
效率問題：通過聚簇索引，可以獲取目標(biāo)行的所有數(shù)據(jù)，可以不用在做其他多余尋址操作。但是非聚簇索引，在不發(fā)生覆蓋索引的情況下，就必須要回表。

如果聚簇索引的數(shù)據(jù)更新，他的存儲要不要變化？

分為兩種場景。
1、修改的其他字段： 如果修改的字段是在葉子節(jié)點(diǎn)存儲的業(yè)務(wù)數(shù)據(jù)，由于不涉及主鍵，則不會對B+樹造成重排、頁分裂的影響。
2、修改的主鍵： 如果修改的字段是維護(hù)聚簇索引的主鍵，則為了保持B+樹的特性，B+樹可能會觸發(fā)重排、頁分裂。

MySQL主鍵是聚簇索引嗎？

只要有主鍵，就一定是聚簇索引。通常一張表只會有一個聚簇索引。

• 最初就有主鍵時，則會將主鍵作為聚簇索引的索引。
• 若表建立初期，沒有主鍵索引，則會尋找一個無null值得，唯一索引做聚簇索引。
• 若既無主鍵，有無符合條件得唯一索引，則Innodb會自動建隱藏一個自增的row_id，作為聚簇索引。若后期一點(diǎn)手動增加了主鍵，Innodb引擎則會立馬對聚簇索引進(jìn)行重建。

什么字段適合當(dāng)做主鍵？

• 適合的：不可重復(fù)，且最好能連續(xù)遞增，這樣可以更好的按順序插入，而不必?fù)?dān)心頻繁的頁分裂。
• 不適合的：對于以后可能會重復(fù)的業(yè)務(wù)字段，則不適合當(dāng)主鍵（像學(xué)號、會員id這些，不知道后期是否可重復(fù)），在分布式的場景下，默認(rèn)自增的也不一定行，如果表數(shù)據(jù)合并時，可能會造成重復(fù)。

性別字段能加索引嗎？為啥？

可以加索引，但是不建議加索引。
假設(shè)有100w個行數(shù)據(jù)，則男/女分別有50w，他的區(qū)分度很低不說，因?yàn)槭欠蔷鄞厮饕樵兊臅r候最壞的情況是造成50w次回表，
所以優(yōu)化器，可能壓根都不會走這種方案。
可以通過建立（聯(lián)合索引、覆蓋索引）進(jìn)行優(yōu)化。

表中十個字段，你主鍵用自增ID，還是UUID，為什么？

自增ID。
選擇自增ID的原因：

• 自增ID，可以使加入的數(shù)據(jù)，緊挨著上條數(shù)據(jù)插入目標(biāo)頁，使磁盤利用率更高。
• 由于自增ID是按照順序插入的，所以大大減少了頁分裂的頻率。

不選擇UUID的原因：

• 由于uuid的無序性，每次插入時，可能都是無序插入。頻繁插入時，所需目標(biāo)頁可能剛被刷到磁盤上，也能壓根還沒被加載到磁盤上，加載就需要花費(fèi)額外時間。
• 且它的隨機(jī)插入，不僅會導(dǎo)致頁分裂更頻繁，而且由于隨機(jī)性，也會導(dǎo)致有大量磁盤碎片，資源利用率不高。
• 同時因?yàn)閁UID，占用36個字符。因?yàn)轶w積較大的原因，會導(dǎo)致一個數(shù)據(jù)頁能存的索引數(shù)量減少，會造成B+樹的層級更高，從而導(dǎo)致一次查詢需要多次磁盤IO。且作比較的時候，需要從字符串的頭部遍歷到尾部，耗費(fèi)的時間也會更長。

為什么自增ID更快一些，UUID不快嗎，它在B+樹里面存儲是有序的嗎？

回答如上一條。

Mysql中的索引是怎么實(shí)現(xiàn)的？

Mysql 中 Innodb引擎 默認(rèn)把 B+樹 作為索引的數(shù)據(jù)結(jié)構(gòu)。
從上向下來說，

• 上層非葉子節(jié)點(diǎn)只會存儲用于排序的主鍵，不存完整的數(shù)據(jù)，只是用來快速定位。
• 葉子節(jié)點(diǎn)則存了所有的字段信息，包括主鍵。
• 并且各個葉子節(jié)點(diǎn)之間，會存儲額外的指針，形成一個雙向鏈表，方便范圍查詢。

并且在Mysql中，每個節(jié)點(diǎn)，都是一個數(shù)據(jù)頁（16KB大?。?，所以千萬級別的數(shù)據(jù)，一般也就3~4層，所以一次插敘的磁盤IO通常也就3 ~ 4次。

查詢數(shù)據(jù)時，到了B+樹的葉子節(jié)點(diǎn)，之后的查找數(shù)據(jù)是如何做的？

1、葉子節(jié)點(diǎn)本質(zhì)上就是一個數(shù)據(jù)頁，而數(shù)據(jù)頁內(nèi)會的每條數(shù)據(jù)都是通過單鏈表連接。

為了優(yōu)化查詢效率。

2、Innodb引擎為每個數(shù)據(jù)頁都維護(hù)了一個頁目錄，這個頁目錄將內(nèi)部的所有數(shù)據(jù)分組放置。其中組內(nèi)數(shù)據(jù)都是按照主鍵大小進(jìn)行順序放置。
3、并且會為每一組，都會維護(hù)一個數(shù)據(jù)槽(slot)。其中數(shù)據(jù)槽內(nèi)存的都是每組數(shù)據(jù)的最后一個數(shù)據(jù)的偏移量。
之后查詢的話，會先基于數(shù)據(jù)槽進(jìn)行二分查找，之后在對找到的目標(biāo)組進(jìn)行順序遍歷。

B+樹的特性是什么？且與B樹的區(qū)別

• 所有葉子節(jié)點(diǎn)都在同一層：B+樹的所有葉子節(jié)點(diǎn)都在同一層，并且各個葉子節(jié)點(diǎn)之間以雙鏈表形式連接，方便范圍查詢。也正以內(nèi)所有的葉子節(jié)點(diǎn)存儲在同一層且只在葉子節(jié)點(diǎn)存儲詳細(xì)數(shù)據(jù)，所以每次磁盤IO次數(shù)都會非常穩(wěn)定。而B樹并非在同一層，葉子節(jié)點(diǎn)間也無連接，并且每個節(jié)點(diǎn)都會存有詳細(xì)信息。
• 非葉子節(jié)點(diǎn)存儲鍵值：B+樹的非葉子節(jié)點(diǎn)只存主鍵索引，與其子節(jié)點(diǎn)的指針，所以可以存儲更多數(shù)據(jù)。所以相較于B樹會更矮，磁盤IO會更少。
• 自平衡：B+樹是嚴(yán)格的自平衡。不論插入/刪除操作時會通過分裂與合并，來嚴(yán)格保持葉子節(jié)點(diǎn)在同一層。雖然B樹也是自平衡，但不會像B+樹那樣非常嚴(yán)格。

MySQL為什么用B+樹結(jié)構(gòu)？和其他結(jié)構(gòu)比的優(yōu)點(diǎn)？

• B+樹與B樹：上一段已經(jīng)講解的很清楚了。
• B+樹與二叉樹：二叉樹只有兩個分叉，B+樹卻是多路平衡，如果選則二叉樹的話會造成
• B+樹與哈希表：哈希表雖然查詢的速度極快O(1)，但是范圍查詢的效率遠(yuǎn)遠(yuǎn)低于B+樹。

為什么MySQL不用跳表？

跳表是 多層級的有序鏈表 ，通過多層鏈表的加速查詢，近似二分查找。
最底層是一條有序的鏈表，存著所有數(shù)據(jù)。上方是逐層精簡的加速鏈表。
查詢時，方便向下快速定位跳轉(zhuǎn)，俗稱跳表。

跳表更適合內(nèi)存查詢，若用到MySQL中，因?yàn)橹羔樀臒o序性，可能會觸發(fā)多次磁盤IO。
而B+樹，恰巧不僅能解決大量隨機(jī)磁盤IO，并且對千萬級別的存儲量數(shù)據(jù)進(jìn)行查詢時，通常也就只需要3~4次磁盤IO。

只有在內(nèi)存中，跳表的優(yōu)勢才能完全發(fā)揮出來。而對于磁盤來說，指針隨機(jī)跳轉(zhuǎn)的劣勢將會無限放大，還是B+樹更適合它。

第3層:      1 ----------- 7
第2層:      1 ----- 5 ---- 9
第1層:      1 -- 3 -- 5 -- 7 -- 9
第0層:      1 -> 3 -> 5 -> 7 -> 9 -> 11

聯(lián)合索引的實(shí)現(xiàn)原理

是什么：其實(shí)就是多個字段共同組成一個索引，這就叫做聯(lián)合索引。
例子：就像商品表中的 product_id商品ID 與 name商品名稱，這兩個字段，共同組成一個索引 (product_id,name)。他們兩者在 B+樹 中被一同存在一起，建立索引時，會先用第一個 id字段 排序，如果 id 相同，再用 第二個 name 字段進(jìn)行排序。
特性：它遵循最左匹配原則。就拿上方舉出的例子來說，他會先對比 product_id，在對比 name。
雖然索引是由兩個字段組成?？墒悄悴樵儠r，在where后面接 name=…，則不會觸發(fā)聯(lián)合索引。
但若你用第一個字段 product_id，就能走這條索引。這就是最左匹配！

創(chuàng)建聯(lián)合索引時，需要注意什么?

因?yàn)?code>最左匹配原則的特性，所以聯(lián)合索引會優(yōu)先匹配最前方的字段。
要點(diǎn)一：區(qū)分度
因此，要把區(qū)分度最高字段的放到最前方，就像UUID這種，放到最前方。
如果像 性別 這種，是不建議添加索引，若非要放到聯(lián)合索引中，那是越往后放越好。
因?yàn)椴樵儍?yōu)化器的存在，如果索引的區(qū)相似度太高（通常大于30%）可能會直接不走索引，轉(zhuǎn)身進(jìn)行全表掃描。
要點(diǎn)二：覆蓋索引
在做業(yè)務(wù)的時候，建立聯(lián)合索引的時，可權(quán)衡將所需字段盡量都放到聯(lián)合索引中，這樣可以避免回表。

聯(lián)合索引(a,b,c)，現(xiàn)在有個執(zhí)行語句是 A=XXX and C<XXX，索引怎么走

因?yàn)?code>最左匹配原則，A可以走聯(lián)合索引，C不行，C只能對A過濾之后的數(shù)據(jù)，進(jìn)行全數(shù)據(jù)掃描。當(dāng)然現(xiàn)在索引下推可以對其進(jìn)行優(yōu)化。

聯(lián)合索引(a,b,c)，查詢條件where b>xxx and a = xxx and c=xxx，索引怎么走

因?yàn)?code>最左匹配原則，兩者都會走聯(lián)合索引。但是c不行。

索引失效有哪些？

1、不符合最左匹配原則，跳過最開頭的字段，則無法進(jìn)行范圍查詢。
2、出現(xiàn)范圍查詢后，下一個字段就無法進(jìn)行聯(lián)合查詢。
3、左模糊匹配，或者左右模糊匹配都不行。
4、帶有函數(shù)運(yùn)算、數(shù)學(xué)操作、類型轉(zhuǎn)換等，都不可以。
5、or 連接中，任何一個字段不在 聯(lián)合索引中，也會失效。

什么是覆蓋索引？

所需的字段，在非聚簇索引中就能獲取，這種行為叫做覆蓋索引。
如果沒有找到，而只能拿著存在葉子節(jié)點(diǎn)的主鍵，再去聚簇節(jié)點(diǎn)中查詢，這就叫做回表。
注：聯(lián)合索引，有就非常適合覆蓋索引。

如果一個列即是單列索引，又是聯(lián)合索引，單獨(dú)查它的話先走哪個？

這個mysql查詢優(yōu)化器，會通過成本判斷，誰低走誰。

索引的優(yōu)缺點(diǎn)？

優(yōu)點(diǎn)是加速查詢過程，降低磁盤IO次數(shù)。
壞處：
1、索引的建立，需要消耗額外的磁盤空間。
2、需要花費(fèi)額外的時間，對B+樹的索引進(jìn)行維持。
3、會降低增刪改的效率，因?yàn)槊看尾僮?，都要額外花費(fèi)時間對B+樹進(jìn)行動態(tài)維護(hù)。

怎么決定建立哪些索引？

建立哪些索引，就意味著這些索引，值不值得建立。
需要建立：
1、需要頻繁的進(jìn)行where查詢。
2、經(jīng)常需要用到 order by / gourp by 這種順序范圍的查詢，因?yàn)锽+樹內(nèi)存的數(shù)據(jù)本身就是有序的。
不需要建立：
1、如果數(shù)據(jù)量很少，壓根就用不到。
2、如果where / order by / group by 這種查詢，幾乎不會使用的字段，也不用。
3、如果 增/刪/改 的字段，也不建議，因?yàn)轭l繁的更改，會導(dǎo)致維護(hù)B+樹的成本增大。
4、區(qū)分度記低的也不建議。

索引優(yōu)化詳細(xì)講講

1、覆蓋索引優(yōu)化，盡量使所需值，在非聚簇索引中就有，避免回表。
2、前綴索引優(yōu)化，前綴所以只取所需字段的前幾個字符，既可以降低磁盤存儲，又可以加快匹配速度，從而提升查詢速度。
3、主鍵的選值，盡量可以自增，從而避免UUID這種，頻繁插入導(dǎo)致磁盤IO過多與頁分裂加劇。
4、盡量迎合最左索引優(yōu)化建立索引。把區(qū)分度高的字段，往前放。

到此這篇關(guān)于MySQL 索引原理、分類、優(yōu)化與面試總結(jié)的文章就介紹到這了,更多相關(guān)mysql索引原理與優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論