為什么要建立索引？

當(dāng)在非常大的表中進(jìn)行查詢，如果數(shù)據(jù)庫(kù)進(jìn)行全表遍歷的話那么速度是會(huì)非常慢的，而我們的索引則可以建立一個(gè)b+樹的結(jié)構(gòu)，可以自上而下的去進(jìn)行查詢（有點(diǎn)像二分查找），可以在一定程度避免走全表查詢，這樣查詢的速度是非?？斓模?/p>

①一般情況下掃描索引的速度是遠(yuǎn)遠(yuǎn)大于掃描全表的速度的；

②索引是天然有序的，具備B+樹的快速檢索（類似二分查找）

③索引天然聚合（存儲(chǔ)的數(shù)據(jù)是去重了的），在一些操作(分組，排序等)中不會(huì)再產(chǎn)生中間表；

哪些情況適合建立索引？

對(duì)于查詢占主要的應(yīng)用來(lái)說(shuō)，索引顯得尤為重要。很多時(shí)候性能問(wèn)題很簡(jiǎn)單的就是因?yàn)槲覀兺颂砑铀饕斐傻?，或者說(shuō)沒(méi)有添加更為有效的索引導(dǎo)致。如果不加索引的話，那么查找任何哪怕只是一條特定的數(shù)據(jù)都會(huì)進(jìn)行一次全表掃描，如果一張表的數(shù)據(jù)量很大而符合條件的結(jié)果又很少，那么不加索引會(huì)引起致命的性能下降。但是也不是什么情況都非得建索引不可，比如性別可能就只有兩個(gè)值，建索引不僅沒(méi)什么優(yōu)勢(shì)，還會(huì)影響到更新速度，這被稱為過(guò)度索引。

那么哪些情況下適合建索引？

1. 頻繁作為where條件語(yǔ)句查詢的字段

        這是因?yàn)樵?strong>頻繁查詢的字段列創(chuàng)建索引可以避免查詢數(shù)據(jù)的時(shí)候走全表掃描，這樣查詢的速度就會(huì)大大增加；

2. 關(guān)聯(lián)字段需要建立索引

        關(guān)聯(lián)的字段一般都是通過(guò)主鍵來(lái)進(jìn)行兩張表的關(guān)聯(lián)，主鍵大部分情況下都是主鍵；如果關(guān)聯(lián)的兩個(gè)主鍵都沒(méi)有索引，那么我們一般優(yōu)先考慮在被驅(qū)動(dòng)表中的字段建立索引，因?yàn)樵谕膺B接的查詢中被驅(qū)動(dòng)表是需要被多次重復(fù)掃描的，那么讓它走索引查詢是會(huì)快很多的，可以避免更多次數(shù)的全表掃描；

3. 排序字段可以建立索引

        這是因?yàn)閎+樹結(jié)構(gòu)的索引是天然有序的！

4.分組字段可以建立索引，因?yàn)榉纸M的前提是排序

5.統(tǒng)計(jì)字段可以建立索引，例如count(),max()

        這是因?yàn)樗饕翘烊痪酆系?，就是存放在b+樹的數(shù)據(jù)是已經(jīng)去重的數(shù)據(jù)了，存儲(chǔ)的數(shù)據(jù)還是比較緊湊的，那么通過(guò)B+樹的雙向指針可以更快的找到要統(tǒng)計(jì)的數(shù)據(jù)，而且在加了索引的列的統(tǒng)計(jì)的時(shí)候MySQL是不會(huì)產(chǎn)生中間表來(lái)專門去重了，可以減少不必要的性能開銷；（在沒(méi)有索引的列的統(tǒng)計(jì)，分組 的SQL語(yǔ)句中，MySQL都是會(huì)創(chuàng)建臨時(shí)表來(lái)存儲(chǔ)數(shù)據(jù)的）

哪些情況下不適合建索引？

1.頻繁更新的字段不適合建立索引 (因?yàn)閿?shù)據(jù)比較大的表的索引的創(chuàng)建是非常耗時(shí)的，而且如果一個(gè)字段被頻繁更新那么我們還需要頻繁的維護(hù)這個(gè)樹的結(jié)構(gòu)，這個(gè)開銷是非常大的)

2.參與列計(jì)算的列不適合建索引，因?yàn)橛?jì)算后的列的值最后不一定是有序的，不有序那么就會(huì)導(dǎo)致索引會(huì)失效

3.表數(shù)據(jù)可以確定比較少的不需要建索引

4.數(shù)據(jù)重復(fù)且分布比較均勻的的字段不適合建索引，因?yàn)檎f(shuō)不定你對(duì)這種索引字段的查詢的速度還沒(méi)有全表掃描快，例如性別，真假值；

5.where條件中用不到的字段不適合建立索引，因?yàn)樗饕强梢詭椭覀冊(cè)诓樵兊臅r(shí)候大大的提高查詢效率，但是在增加，刪除操作確實(shí)異常消耗性能的，因?yàn)樾枰粩嗟木S護(hù)B+樹的結(jié)構(gòu)（有序你就需要維護(hù)），你查詢的時(shí)候都不需要使用到這個(gè)字段了，那還建立這個(gè)字段的索引列干啥？等著吃你系統(tǒng)的性能嘛？

為什么索引是使用B+樹？（重點(diǎn)）

①因?yàn)閎+樹是把數(shù)據(jù)都存放在葉子節(jié)點(diǎn)中的（在innodb存儲(chǔ)引擎中一個(gè)b+樹的節(jié)點(diǎn)是 一頁(yè)(16k)），那么在固定大小的容量中  B+樹的非葉子節(jié)點(diǎn)中就可以存放更多的索引列數(shù)據(jù)，也就意味著B+樹的非葉子節(jié)點(diǎn)存儲(chǔ)的數(shù)據(jù)的范圍就會(huì)更大，那么樹的層次就會(huì)更少，IO次數(shù)也就會(huì)更少；

②b+樹的葉子節(jié)點(diǎn)維護(hù)了一個(gè)雙向鏈表，它更有利于范圍查詢

③b+樹中的葉子節(jié)點(diǎn)和非葉子節(jié)點(diǎn)的數(shù)據(jù)都是分開存儲(chǔ)的，分別存放在葉子節(jié)點(diǎn)段和非葉子節(jié)點(diǎn)段，那么進(jìn)行全表掃描的時(shí)候，就可以不用再掃描非葉子節(jié)點(diǎn)的數(shù)據(jù)了，并且這是一個(gè)順序讀取數(shù)據(jù)的過(guò)程（順序讀比隨機(jī)讀的速度要快很多很多），掃描的速度也會(huì)大大提高；

索引分為那幾類？

從大類來(lái)分：分為聚簇索引和非聚簇索引；

從具體的種類來(lái)分有：

主鍵索引: 也簡(jiǎn)稱主鍵。它可以提高查詢效率，并提供唯一性約束。一張表中只能有一個(gè)主鍵。

普通索引：就是普普通通的索引。

唯一索引：索引的值不能重復(fù)。

復(fù)合索引：在工作中用得比較頻繁的一個(gè)索引；

當(dāng)有多個(gè)查詢條件時(shí)，我們推薦使用復(fù)合索引。比如：我們經(jīng)常按照 A列 B列 C列進(jìn)行查詢時(shí)，通常的做法是建立一個(gè)由三個(gè)列共同組成的復(fù)合索引而不是對(duì)每一個(gè)列建立普通索引。

創(chuàng)建方式： 復(fù)合索引中的索引的順序是非常重要的；

alert table test add idx_a1_a2_a3 table (a1,a2,a3) 

使用復(fù)合索引可以極大的減少回表的帶來(lái)的性能開銷；（體現(xiàn)在 復(fù)合索引可以進(jìn)行更多的索引覆蓋（因?yàn)槟闼饕膫€(gè)數(shù)明顯更加多了呀），即便是回表也是攜帶更少的主鍵進(jìn)行回表查詢（與MySQL5.7后的索引下推有關(guān)））

復(fù)合索引是基于第一個(gè)索引的，比如你建立了一個(gè)（a,b,c）的復(fù)合索引，那么你不能跳過(guò)a索引直接去查詢b索引，因?yàn)樵诮ⅲ╝,b,c）這個(gè)復(fù)合索引的時(shí)候，是會(huì)創(chuàng)建（a），（a,b），（a,b,c）這三個(gè)索引的，你會(huì)發(fā)現(xiàn)它們都是基于a索引的； (并不會(huì)單獨(dú)的創(chuàng)建(a,c)這個(gè)索引)

hash索引：hash天然快（最快o(1),最慢o(n),樹化(lon(n))），但是天然無(wú)序；

空間索引；

全文索引；

什么是聚簇索引？（重點(diǎn)）

聚簇索引就是將數(shù)據(jù)(一行一行的數(shù)據(jù))跟索引結(jié)構(gòu)放到一塊，innodb存儲(chǔ)引擎使用的就是聚簇索引；

注意點(diǎn)：

1. InnoDB使用的是聚簇索引（聚簇索引默認(rèn)使用主鍵作為其索引），將主鍵組織到一棵B+樹中，而行數(shù)據(jù)就儲(chǔ)存在葉子節(jié)點(diǎn)上，若使用"where id = 14"這樣的條件查找主鍵，則按照B+樹的檢索算法即可查找到對(duì)應(yīng)的葉節(jié)點(diǎn)，之后獲得行數(shù)據(jù)。
2. 若對(duì)Name列進(jìn)行條件搜索，則需要兩個(gè)步驟：第一步在輔助索引B+樹中檢索Name，到達(dá)其葉子節(jié)點(diǎn)獲取對(duì)應(yīng)的主鍵。第二步使用主鍵在主索引B+樹種再執(zhí)行一次B+樹檢索操作，最終到達(dá)葉子節(jié)點(diǎn)即可獲取整行數(shù)據(jù)。（重點(diǎn)在于通過(guò)其他鍵需要建立輔助索引）

聚簇索引具有唯一性，由于聚簇索引是將數(shù)據(jù)(一行一行的數(shù)據(jù))跟索引結(jié)構(gòu)放到一塊，因此一個(gè)表僅有一個(gè)聚簇索引，其他輔助索引可能是只有幾個(gè)列的數(shù)據(jù)和索引放在一起！

表中行的物理順序和索引中行的物理順序是相同的，在創(chuàng)建任何非聚簇索引之前創(chuàng)建聚簇索引，這是因?yàn)榫鄞厮饕淖兞吮碇行械奈锢眄樞?，?shù)據(jù)行 按照一定的順序排列，并且自動(dòng)維護(hù)（有序就一定需要維護(hù)）這個(gè)順序；

聚簇索引中的索引默認(rèn)是主鍵，如果表中沒(méi)有定義主鍵，InnoDB 會(huì)選擇一個(gè)唯一且非空的索引代替。如果沒(méi)有這樣的索引，InnoDB 會(huì)隱式定義一個(gè)6個(gè)字節(jié)大小的row_id來(lái)作為主鍵，這個(gè)主鍵會(huì)作為聚簇索引中的索引。如果已經(jīng)設(shè)置了主鍵為聚簇索引又希望再單獨(dú)設(shè)置聚簇索引，必須先刪除主鍵，然后添加我們想要的聚簇索引，最后恢復(fù)設(shè)置主鍵即可。

使用聚簇索引的優(yōu)缺點(diǎn)？（知道）

1.由于行數(shù)據(jù)和聚簇索引的葉子節(jié)點(diǎn)存儲(chǔ)在一起，同一頁(yè)（16k）中會(huì)有多條行數(shù)據(jù)，訪問(wèn)同一數(shù)據(jù)頁(yè)不同行記錄時(shí)，已經(jīng)把頁(yè)加載到了Buffer中(讀取數(shù)據(jù)是按頁(yè)讀取的)，再次訪問(wèn)時(shí)，會(huì)在內(nèi)存中完成訪問(wèn)，不必訪問(wèn)磁盤。這樣主鍵和行數(shù)據(jù)是一起被載入內(nèi)存的，找到葉子節(jié)點(diǎn)就可以立刻將行數(shù)據(jù)返回了，如果按照主鍵Id來(lái)組織數(shù)據(jù)，獲得數(shù)據(jù)更快。

2.輔助索引的葉子節(jié)點(diǎn)，存儲(chǔ)主鍵值，而不是數(shù)據(jù)的存放地址。好處是當(dāng)行數(shù)據(jù)放生變化時(shí)，索引樹的節(jié)點(diǎn)也需要分裂變化；或者是我們需要查找的數(shù)據(jù)，在上一次IO讀寫的緩存中沒(méi)有，需要發(fā)生一次新的IO操作時(shí)，可以避免對(duì)輔助索引的維護(hù)工作，只需要維護(hù)聚簇索引樹就好了。另一個(gè)好處是，因?yàn)檩o助索引存放的是主鍵值，減少了輔助索引占用的存儲(chǔ)空間大小。

注：我們知道一次io讀寫，可以獲取到16K大小的資源，我們稱之為讀取到的數(shù)據(jù)區(qū)域?yàn)镻age。而我們的B樹，B+樹的索引結(jié)構(gòu)，葉子節(jié)點(diǎn)上存放好多個(gè)關(guān)鍵字（索引值）和對(duì)應(yīng)的數(shù)據(jù)，都會(huì)在一次IO操作中被讀取到緩存中，所以在訪問(wèn)同一個(gè)頁(yè)中的不同記錄時(shí)，會(huì)在內(nèi)存里操作，而不用再次進(jìn)行IO操作了。除非發(fā)生了頁(yè)的分裂，即要查詢的行數(shù)據(jù)不在上次IO操作的緩存里，才會(huì)觸發(fā)新的IO操作。

3.因?yàn)镸yISAM的主索引并非聚簇索引，那么他的數(shù)據(jù)的物理地址必然是凌亂的，拿到這些物理地址，按照合適的算法進(jìn)行I/O讀取，于是開始不停的尋道不停的旋轉(zhuǎn)。聚簇索引則只需一次I/O。（強(qiáng)烈的對(duì)比）

4.不過(guò)，如果涉及到大數(shù)據(jù)量的排序、全表掃描、count之類的操作的話，還是MyISAM占優(yōu)勢(shì)些，因?yàn)樗饕伎臻g小，這些操作是需要在內(nèi)存中完成的。

為什么推薦使用自增主鍵作為索引？（知道）

主鍵最好不要使用uuid，因?yàn)閡uid的值太過(guò)離散，不適合排序且可能出現(xiàn)新增加記錄的uuid，會(huì)插入在索引樹中間的位置，出現(xiàn)頁(yè)分裂（比如之前的索引已經(jīng)緊湊的排列在一起了，你此時(shí)需要在已經(jīng)緊湊排列好的數(shù)據(jù)中插入數(shù)據(jù)就會(huì)導(dǎo)致前面已經(jīng)排好序的索引出現(xiàn)松動(dòng)和重構(gòu)排序，但是使用自增id就不會(huì)出現(xiàn)這種情況了），導(dǎo)致索引樹調(diào)整復(fù)雜度變大，消耗更多的時(shí)間和資源。但是使用自增主鍵就可以避免出現(xiàn)頁(yè)分裂，因?yàn)樽栽鲋麈I后面的主鍵值是要比前面的大， 那后來(lái)的數(shù)據(jù)直接放在后面就行；

聚簇索引的數(shù)據(jù)的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那么對(duì)應(yīng)的數(shù)據(jù)一定也是相鄰地存放在磁盤上的。如果主鍵不是自增id，它會(huì)不斷地調(diào)整數(shù)據(jù)的物理地址、分頁(yè)，當(dāng)然也有其他一些措施來(lái)減少這些操作，但卻無(wú)法徹底避免。但如果是自增的id，它只需要一 頁(yè)一頁(yè)地寫，索引結(jié)構(gòu)相對(duì)緊湊，磁盤碎片少，效率也高。

什么叫回表？（重點(diǎn)）

如果一個(gè)查詢是先走輔助索引（聚簇索引外的索引都叫輔助索引）的，那么通過(guò)這個(gè)輔助索引（innodb中的輔助索引的data存儲(chǔ)的是主鍵）沒(méi)有獲取到我們想要的全部數(shù)據(jù)，那么MySQL就會(huì)拿著輔助索引查詢出來(lái)的主鍵去聚簇索引中進(jìn)行查詢，這個(gè)過(guò)程就是叫回表；

什么叫索引覆蓋？（重點(diǎn)）

如果一個(gè)查詢是先走輔助索引的，那么通過(guò)這個(gè)輔助索引就直接獲取到我們想要的全部數(shù)據(jù)了，不需要進(jìn)行回表，這個(gè)過(guò)程就叫做索引覆蓋；

什么是最左前綴原則？（重點(diǎn)）

大白話就是 從最左的索引開始匹配，遇到范圍查詢就會(huì)讓后面范圍列后的索引失效；

mysql會(huì)一直向右匹配直到遇到范圍查詢（>、<、between、like）就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 ，如果建立（a,b,c,d）順序的聯(lián)合索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。

了解一下：

在MySQL5.6及5.6以前，最左匹配就是只有最左道索引會(huì)生效；（這個(gè)時(shí)候創(chuàng)建復(fù)合索引是為了避免回表）

在MySQL5.7最左匹配引入了索引下推， 比如創(chuàng)建（a,b,c）的復(fù)合索引，在進(jìn)行where a=xxx and b=xxx and c=xxx 的查詢語(yǔ)句中，MySQL是先在索引中找到滿足a條件的數(shù)據(jù)，然后再在a中取滿足b的條件，然后再在b中取滿足c的數(shù)據(jù)，最后再拿著非常少的主鍵到聚簇索引中查詢最后的行數(shù)據(jù)；

比如：有1000W條數(shù)據(jù)的表，有如下sql:select from table where a =1 and b =2 and c =3,假設(shè)假設(shè)每個(gè)條件可以篩選出10%的數(shù)據(jù)，如果只有單值索引，那么通過(guò)該索引能篩選出1000W10%=100w條數(shù)據(jù)，然后再回表從100w條數(shù)據(jù)中找到符合b =2 and c = 3的數(shù)據(jù)，然后再排序，再分頁(yè)；如果是聯(lián)合索引，通過(guò)索引篩選出1000w10% 10% *10%=1w，效率提升可想而知！

MySQL索引失效的幾種情況（重點(diǎn)）

①like查詢以%開頭，因?yàn)闀?huì)導(dǎo)致查詢出來(lái)的結(jié)果無(wú)序；

②類型轉(zhuǎn)換，列計(jì)算也會(huì)可能會(huì)讓索引失效，因?yàn)榻Y(jié)果可能是無(wú)序的，也可能是有序的；

③在一些查詢的語(yǔ)句中，MySQL認(rèn)為走全表掃描比索引更加快也會(huì)導(dǎo)致索引失效；

④如果條件中有or并且or連接的字段中有列沒(méi)有索引，那么即使其中有條件帶索引也不會(huì)使用索引 (這是因?yàn)镸ySQL判斷即便你開始走了索引查詢，但是它發(fā)現(xiàn)查詢中有Or ，也就是說(shuō)or 后面的還是需要走全表掃描（因?yàn)閛r會(huì)導(dǎo)致后面的數(shù)據(jù)是無(wú)序的），所以MySQL還不如一開始就直接走全表掃描，這也是為什么盡量少用or的原因)要想使用or，又想讓索引生效，只能將or條件中的每個(gè)列都加上索引，當(dāng)檢索條件有or但是所有的條件都有索引時(shí)，索引不失效，可以走【兩個(gè)索引】，這叫索引合并（取二者的并集）;

⑤復(fù)合索引不滿足最左原則就不能使用全部索引

常見(jiàn)的索引優(yōu)化手段有哪些？

① 盡可能的使用復(fù)合索引而不是索引的組合；

②創(chuàng)建索引盡量讓輔助索引進(jìn)行索引覆蓋 而不是回表；

③在可以使用主鍵id的表中，盡量使用自增主鍵id，這樣可以避免頁(yè)分裂；

④查詢的時(shí)候盡量不要使用select * ，這樣可以避免大量的回表；

⑤盡量少使用子查詢，能使用外連接就使用外連接，這樣可以避免產(chǎn)生笛卡爾集；

⑥能使用短索引就是用短索引，這樣可以在非葉子節(jié)點(diǎn)存儲(chǔ)更多的索引列降低樹的層高，并且減少空間的開銷；

談一下你對(duì)MySQL索引的理解？

索引的b+樹結(jié)構(gòu)，為什么使用b+樹說(shuō)一下，然后再說(shuō)一下聚簇索引，回表和索引覆蓋；

然后再談一下索引失效；

總結(jié)

到此這篇關(guān)于MySQL索引的一些常見(jiàn)面試題大全的文章就介紹到這了,更多相關(guān)MySQL索引面試題內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論