《MySQL面試小抄》索引考點(diǎn)二面總結(jié)

我是肥哥，一名不專業(yè)的面試官！

我是囧囧患雏，一名積極找工作的小菜鳥！

囧囧表示：小白面試最怕的就是面試官問的知識(shí)點(diǎn)太籠統(tǒng)罢维，自己無法快速定位到關(guān)鍵問題點(diǎn)！１臁肺孵！

本期主要面試考點(diǎn)

面試官考點(diǎn)之談?wù)勊饕S護(hù)過程？頁(yè)分裂颜阐？頁(yè)合并平窘？

面試官考點(diǎn)之簡(jiǎn)述一下查詢時(shí)B+樹索引搜索過程？

面試官考點(diǎn)之什么是回表凳怨？

面試官考點(diǎn)之什么是索引覆蓋瑰艘？使用場(chǎng)景？

面試官考點(diǎn)之什么情況下會(huì)索引失效肤舞？

面試官考點(diǎn)之哪些情況下紫新，可能會(huì)面臨索引失效的問題？

面試官考點(diǎn)之or走索引和索引失效分別是什么場(chǎng)景李剖？

面試官考點(diǎn)之哪些情況下需要?jiǎng)?chuàng)建索引芒率？

面試官考點(diǎn)之聯(lián)合索引之最左前綴原則？

面試官考點(diǎn)之索引下推場(chǎng)景篙顺？

索引二面1

索引二面2

面試官考點(diǎn)之談?wù)勊饕S護(hù)過程偶芍？頁(yè)分裂充择？頁(yè)合并？

B+樹為了維護(hù)索引有序性匪蟀，在插入刪除的時(shí)候需要做必要的維護(hù)椎麦，必要時(shí)候可能涉及到頁(yè)分裂，頁(yè)合并過程材彪！

首先假設(shè)每個(gè)葉子節(jié)點(diǎn)（數(shù)據(jù)頁(yè)或磁盤塊）只能存儲(chǔ)3條索引和數(shù)據(jù)記錄观挎，如圖

ID索引樹

情況1、新增行記錄查刻，ID=3键兜，此時(shí)【數(shù)據(jù)頁(yè)1】未滿，只需要在data2后新增ID=3的行記錄穗泵，B+樹整體結(jié)構(gòu)不需要進(jìn)行調(diào)整

索引頁(yè)分裂

情況2普气、新增行記錄，ID=8佃延，此時(shí)【數(shù)據(jù)頁(yè)2】已滿现诀，這時(shí)候需要申請(qǐng)一個(gè)新的數(shù)據(jù)頁(yè)，然后挪動(dòng)部分?jǐn)?shù)據(jù)過去履肃。這個(gè)過程稱為頁(yè)分裂仔沿。

頁(yè)分裂過程消耗性能，同時(shí)空間利用率也降低了

有分裂就有合并尺棋，當(dāng)相鄰兩個(gè)頁(yè)由于刪除了數(shù)據(jù)封锉，利用率很低之后，會(huì)將數(shù)據(jù)頁(yè)做合并膘螟。合并的過程成福，可以認(rèn)為是分裂過程的逆過程。

當(dāng)相鄰兩個(gè)頁(yè)由于刪除了數(shù)據(jù)荆残，利用率很低之后奴艾，會(huì)將數(shù)據(jù)頁(yè)做合并。合并的過程内斯，可以認(rèn)為是分裂過程的逆過程蕴潦。

【數(shù)據(jù)頁(yè)2】刪除了ID=7,ID=8的行記錄，此時(shí)【數(shù)據(jù)頁(yè)2】【數(shù)據(jù)頁(yè)3】利用率很低俘闯，將進(jìn)行頁(yè)合并潭苞。

面試官考點(diǎn)之簡(jiǎn)述一下查詢時(shí)B+樹索引搜索過程？

準(zhǔn)備一張用戶表真朗，其中id為主鍵萄传，age為普通索引

CREATE TABLE `user` (
  `id` int(11) PRIMARY KEY,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

select * from user where age=22 簡(jiǎn)述一下B+樹索引搜索過程？

假設(shè)要查詢的記錄

 id=5,name="張三",age=22

MySQL為每個(gè)索引分別維護(hù)了一棵B+Tree索引樹，

主鍵索引非葉子節(jié)點(diǎn)維護(hù)了索引鍵秀菱，葉子節(jié)點(diǎn)存儲(chǔ)行數(shù)據(jù)振诬；

非主鍵索引也稱為二級(jí)索引，非葉子節(jié)點(diǎn)存儲(chǔ)主鍵衍菱；

B+樹索引搜索過程

搜索條件 age=22赶么，可走idx_age索引，首先加載idx_age索引樹脊串，找到age=22的記錄辫呻，取得id=5

回表搜索，加載主鍵索引樹琼锋，找到id=22的記錄放闺，取得整行數(shù)據(jù)

面試官考點(diǎn)之什么是回表？

idx_age二級(jí)索引樹找到主鍵id后缕坎，回到id主鍵索引搜索的過程,就稱為回表怖侦。

并非所有非主鍵索引搜索，都需要進(jìn)行回表搜索谜叹，也就是下面要說的索引覆蓋匾寝。

面試官考點(diǎn)之什么是索引覆蓋？使用場(chǎng)景荷腊？

在上面提到的例子中艳悔，由于查詢結(jié)果所需要的數(shù)據(jù)只在主鍵索引上有，所以不得不回表女仰。

如果在查詢的數(shù)據(jù)列里面猜年，直接從索引列就能取到想要的結(jié)果，就不需要再回表去查疾忍，也稱之為索引覆蓋码倦！

索引覆蓋的優(yōu)點(diǎn)

1. 可以避免對(duì)Innodb主鍵索引的二次查詢
2. 可以避免MyISAM表進(jìn)行系統(tǒng)調(diào)用
3. 可以優(yōu)化緩存,減少磁盤IO操作

修改一下上述栗子，滿足索引覆蓋條件锭碳？

select id, age from user where age=22

查詢的信息，id勿璃，age都可以直接在idx_age 索引樹中獲取擒抛，不需要回表搜索。

由于覆蓋索引可以減少樹的搜索次數(shù)补疑，顯著提升查詢性能歧沪，所以使用覆蓋索引是一個(gè)常用
的性能優(yōu)化手段。

索引是一把雙刃劍莲组，提供快速排序搜索的同時(shí)诊胞，索引字段的維護(hù)也是要付出相應(yīng)的代價(jià)的。

因此，在建立冗余索引來支持覆蓋索引時(shí)就需要權(quán)衡考慮了

面試官考點(diǎn)之索引失效撵孤？

創(chuàng)建的索引迈着，到底有沒有生效，或者說SQL語句有沒有使用索引查詢邪码？

一個(gè)最常見的查詢場(chǎng)景裕菠，建立idx_name索引

select * from t_user where user_name like '%mayun100%';

這條查詢是否走索引？

like不走索引

select * from t_user where user_name like 'mayun100%';

這條查詢是否走索引闭专？

like走索引

面試官考點(diǎn)之有哪些情況下奴潘，可能會(huì)面臨索引失效的問題？

1. like通配符影钉，左側(cè)開放情況下画髓，全表掃描
2. or條件篩選，可能會(huì)導(dǎo)致索引失效
3. where中對(duì)索引列使用mysql的內(nèi)置函數(shù)平委，一定失效
4. where中對(duì)索引列進(jìn)行運(yùn)算（如奈虾，+、-肆汹、*愚墓、/），一定失效
5. 類型不一致昂勉，隱式的類型轉(zhuǎn)換浪册，導(dǎo)致的索引失效
6. where語句中索引列使用了負(fù)向查詢，可能會(huì)導(dǎo)致索引失效 負(fù)向查詢包括：NOT岗照、!=村象、<>、!<攒至、!>厚者、NOT IN、NOT LIKE等迫吐。!<库菲、!>為SQL Server語法。
7. 索引字段可以為null志膀，使用is null或is not null時(shí)熙宇，可能會(huì)導(dǎo)致索引失效
8. 隱式字符編碼轉(zhuǎn)換導(dǎo)致的索引失效
9. 聯(lián)合索引中，where中索引列違背最左匹配原則溉浙，一定會(huì)導(dǎo)致索引失效
10. MySQL優(yōu)化器的最終選擇烫止，不走索引

面試官考點(diǎn)之or走索引和索引失效分別是什么場(chǎng)景?

or走索引和索引失效分別是什么場(chǎng)景?

or查詢

OR 連接的是同一個(gè)字段，相同走索引

explain select * from t_user where user_name = 'mayun10' or user_name = 'mayun1000'

or查詢走索引情況

OR 連接的是兩個(gè)不同的字段戳稽，不走索引

or查詢索引失效情況

給address列增加索引

alter table t_user add index idx_address(address);
explain select * from t_user where user_name = 'mayun10' or address = '浙江杭州12';

OR 連接的是兩個(gè)不同字段馆蠕，如果兩個(gè)字段皆有索引，走索引

or查詢走索引情況-兩邊字段有索引

（下一期：《MySQL面試小抄》幾種索引失效場(chǎng)景驗(yàn)證）

面試小抄系列。

面試官考點(diǎn)之哪些情況下需要?jiǎng)?chuàng)建索引互躬？

1.主鍵自動(dòng)建立唯一索引

2.頻繁查詢的字段

3.JOIN 關(guān)聯(lián)查詢播赁，作為外鍵關(guān)系的列建立索引

4.單鍵/組合索引的選擇問題，高并發(fā)下傾向創(chuàng)建組合索引吨铸，創(chuàng)建時(shí)遵循最左前綴匹配原則

5.ORDER BY 查詢中排序的字段行拢，排序字段通過索引訪問大幅提高排序速度

6.GROUP BY 需要分組字段或查詢中統(tǒng)計(jì)字段

面試官考點(diǎn)之聯(lián)合索引之最左前綴原則

MySQL建立多列索引（聯(lián)合索引）有最左前綴的原則，即最左優(yōu)先

當(dāng)MySQL建立的是聯(lián)合索引诞吱，假設(shè)以(a,b,c) 列作為聯(lián)合索引舟奠，那么MySQL建樹規(guī)則是什么？

我們知道MySQL會(huì)為每一個(gè)索引維護(hù)一顆B+Tree房维，非葉子節(jié)點(diǎn)存儲(chǔ)索引key沼瘫，葉子節(jié)點(diǎn)存儲(chǔ)行數(shù)據(jù)data。

聯(lián)合索引(a,b,c) 相當(dāng)于建立了 (a), (a,b), (a,b,c) 三個(gè)索引咙俩，MySQL組裝索引樹時(shí)耿戚，是按照從左到右的順序來建立B+Tree的聯(lián)合索引樹的。

匹配索引情況一

假設(shè)（a,b,c）索引要搜索的值為('張三', 21, 100) 阿趁，檢索數(shù)據(jù)時(shí)膜蛔，匹配的順序就是a,b,c。

B+Tree會(huì)優(yōu)先比較a來確定下一步的所搜方向脖阵，如果a相同再依次比較b和c皂股，最后得到檢索的數(shù)據(jù)；

匹配索引情況二

假設(shè)（a,c）索引要搜索的值為('張三', 100) 命黔，檢索數(shù)據(jù)時(shí)呜呐，匹配的順序就是a,b,c。

B+Tree使用a來指定搜索方向悍募，但下一個(gè)字段b缺失蘑辑，所以只能把a(bǔ)等于張三的數(shù)據(jù)都找到，然后再匹配c是100的數(shù)據(jù)坠宴。

匹配索引情況三

假設(shè)（b,c）索引要搜索的值為('張三', 21) 洋魂，檢索數(shù)據(jù)時(shí)，無匹配順序

B+Tree不知道下一步該查哪個(gè)節(jié)點(diǎn)喜鼓，因?yàn)榻⑺阉鳂涞臅r(shí)候a是第一個(gè)比較因子副砍，必須要先根據(jù)a來搜索才能知道下一步去哪里查詢。此時(shí)索引失效颠通！

索引項(xiàng)是按照索引定義里面出現(xiàn)的字段順序排序的，最左前綴可以是聯(lián)合索引的最左N個(gè)字段膀懈，也可以是字符串索引的最左M個(gè)字符顿锰。

面試官考點(diǎn)之索引下推場(chǎng)景？

索引下推，即減少二級(jí)索引回表搜索次數(shù)Ｅ鹂亍Ａ跆铡！

通俗說牢撼，減少查詢主鍵索引樹次數(shù)匙隔，減少磁盤IO

建立聯(lián)合索引 idx_age_weight

select * from user where age = 11 and weight = 98

5.6之前搜索過程是

在idx_age_weight 索引樹中匹配出所有的 age = 11 索引，拿到主鍵id熏版，回表去一條條再比對(duì)weight字段

如下圖纷责，需要進(jìn)行3次回表搜索操作

5.6之前回表操作

5.6后的搜索過程是
在idx_age_weight 索引樹中匹配出所有的 age = 11 索引，順便對(duì)weight字段進(jìn)行判斷撼短，過濾掉weight = 100的記錄再膳，然后再進(jìn)行回表搜索。

如下圖曲横，只需要進(jìn)行2次回表搜索操作

5.6后索引下推

閱讀原文：

《MySQL面試小抄》索引考點(diǎn)二面總結(jié)

《MySQL面試小抄》索引考點(diǎn)一面總結(jié)

隨緣更新喂柒，整理不易，歡迎聯(lián)系小白討論禾嫉，大神巴巴請(qǐng)繞路灾杰！

更多精彩內(nèi)容，歡迎關(guān)注公眾號(hào)：囧么肥事 (或搜索：jiongmefeishi)