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

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

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

講講索引的分類情況

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

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

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

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

2、物理存儲(chǔ)分類：

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

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

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

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

3、按字段特性分類

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

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

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

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

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

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

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

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

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

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

MySQL主鍵是聚簇索引嗎？

只要有主鍵，就一定是聚簇索引。通常一張表只會(huì)有一個(gè)聚簇索引。

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

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

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

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

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

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

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

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

不選擇UUID的原因：

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

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

回答如上一條。

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

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

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

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

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

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

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

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

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

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

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

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

為什么MySQL不用跳表？

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

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

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

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

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

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

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

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

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

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

索引失效有哪些？

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

什么是覆蓋索引？

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

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

這個(gè)mysql查詢優(yōu)化器，會(huì)通過(guò)成本判斷，誰(shuí)低走誰(shuí)。

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

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

怎么決定建立哪些索引？

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

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

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

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

最新評(píng)論