《MySQL面試小抄》索引考點(diǎn)二面總結(jié)
我是肥哥，一名不專業(yè)的面試官！
我是囧囧，一名積極找工作的小菜鳥！
囧囧表示：小白面試最怕的就是面試官問的知識點(diǎn)太籠統(tǒng)，自己無法快速定位到關(guān)鍵問題點(diǎn)！??！
本期主要面試考點(diǎn)
面試官考點(diǎn)之談?wù)勊饕S護(hù)過程？頁分裂？頁合并？
面試官考點(diǎn)之簡述一下查詢時B+樹索引搜索過程？
面試官考點(diǎn)之什么是回表？
面試官考點(diǎn)之什么是索引覆蓋？使用場景？
面試官考點(diǎn)之什么情況下會索引失效？
面試官考點(diǎn)之哪些情況下，可能會面臨索引失效的問題？
面試官考點(diǎn)之or走索引和索引失效分別是什么場景？
面試官考點(diǎn)之哪些情況下需要創(chuàng)建索引？
面試官考點(diǎn)之聯(lián)合索引之最左前綴原則？
面試官考點(diǎn)之索引下推場景？
面試官考點(diǎn)之談?wù)勊饕S護(hù)過程？頁分裂？頁合并？
B+樹為了維護(hù)索引有序性，在插入刪除的時候需要做必要的維護(hù)，必要時候可能涉及到頁分裂，頁合并過程！
首先假設(shè)每個葉子節(jié)點(diǎn)（數(shù)據(jù)頁或磁盤塊）只能存儲3條索引和數(shù)據(jù)記錄，如圖
情況1、新增行記錄，ID=3，此時【數(shù)據(jù)頁1】未滿，只需要在data2后新增ID=3的行記錄，B+樹整體結(jié)構(gòu)不需要進(jìn)行調(diào)整
情況2、新增行記錄，ID=8，此時【數(shù)據(jù)頁2】已滿，這時候需要申請一個新的數(shù)據(jù)頁，然后挪動部分?jǐn)?shù)據(jù)過去。這個過程稱為頁分裂。
頁分裂過程消耗性能，同時空間利用率也降低了
有分裂就有合并，當(dāng)相鄰兩個頁由于刪除了數(shù)據(jù)，利用率很低之后，會將數(shù)據(jù)頁做合并。合并的過程，可以認(rèn)為是分裂過程的逆過程。
當(dāng)相鄰兩個頁由于刪除了數(shù)據(jù)，利用率很低之后，會將數(shù)據(jù)頁做合并。合并的過程，可以認(rèn)為是分裂過程的逆過程。
【數(shù)據(jù)頁2】刪除了ID=7,ID=8的行記錄，此時【數(shù)據(jù)頁2】【數(shù)據(jù)頁3】利用率很低，將進(jìn)行頁合并。
面試官考點(diǎn)之簡述一下查詢時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 簡述一下B+樹索引搜索過程？
假設(shè)要查詢的記錄
id=5,name="張三",age=22
MySQL為每個索引分別維護(hù)了一棵B+Tree索引樹，
主鍵索引非葉子節(jié)點(diǎn)維護(hù)了索引鍵，葉子節(jié)點(diǎn)存儲行數(shù)據(jù)；
非主鍵索引也稱為二級索引，非葉子節(jié)點(diǎn)存儲主鍵；
B+樹索引搜索過程
搜索條件 age=22，可走idx_age索引，首先加載idx_age索引樹，找到age=22的記錄，取得id=5
回表搜索，加載主鍵索引樹，找到id=22的記錄，取得整行數(shù)據(jù)
面試官考點(diǎn)之什么是回表？
idx_age二級索引樹找到主鍵id后，回到id主鍵索引搜索的過程,就稱為回表。
并非所有非主鍵索引搜索，都需要進(jìn)行回表搜索，也就是下面要說的索引覆蓋。
面試官考點(diǎn)之什么是索引覆蓋？使用場景？
在上面提到的例子中，由于查詢結(jié)果所需要的數(shù)據(jù)只在主鍵索引上有，所以不得不回表。
如果在查詢的數(shù)據(jù)列里面，直接從索引列就能取到想要的結(jié)果，就不需要再回表去查，也稱之為索引覆蓋！
索引覆蓋的優(yōu)點(diǎn)
- 可以避免對Innodb主鍵索引的二次查詢
- 可以避免MyISAM表進(jìn)行系統(tǒng)調(diào)用
- 可以優(yōu)化緩存,減少磁盤IO操作
修改一下上述栗子，滿足索引覆蓋條件？
select id, age from user where age=22
查詢的信息，id，age都可以直接在idx_age 索引樹中獲取，不需要回表搜索。
由于覆蓋索引可以減少樹的搜索次數(shù)，顯著提升查詢性能，所以使用覆蓋索引是一個常用
的性能優(yōu)化手段。
索引是一把雙刃劍，提供快速排序搜索的同時，索引字段的維護(hù)也是要付出相應(yīng)的代價的。
因此，在建立冗余索引來支持覆蓋索引時就需要權(quán)衡考慮了
面試官考點(diǎn)之索引失效？
創(chuàng)建的索引，到底有沒有生效，或者說SQL語句有沒有使用索引查詢？
一個最常見的查詢場景，建立idx_name索引
select * from t_user where user_name like '%mayun100%';
這條查詢是否走索引？
select * from t_user where user_name like 'mayun100%';
這條查詢是否走索引？
面試官考點(diǎn)之有哪些情況下，可能會面臨索引失效的問題？
- like通配符，左側(cè)開放情況下，全表掃描
- or條件篩選，可能會導(dǎo)致索引失效
- where中對索引列使用mysql的內(nèi)置函數(shù)，一定失效
- where中對索引列進(jìn)行運(yùn)算（如，+、-、*、/），一定失效
- 類型不一致，隱式的類型轉(zhuǎn)換，導(dǎo)致的索引失效
- where語句中索引列使用了負(fù)向查詢，可能會導(dǎo)致索引失效 負(fù)向查詢包括：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等。!<、!>為SQL Server語法。
- 索引字段可以為null，使用is null或is not null時，可能會導(dǎo)致索引失效
- 隱式字符編碼轉(zhuǎn)換導(dǎo)致的索引失效
- 聯(lián)合索引中，where中索引列違背最左匹配原則，一定會導(dǎo)致索引失效
- MySQL優(yōu)化器的最終選擇，不走索引
面試官考點(diǎn)之or走索引和索引失效分別是什么場景?
or走索引和索引失效分別是什么場景?
OR 連接的是同一個字段，相同走索引
explain select * from t_user where user_name = 'mayun10' or user_name = 'mayun1000'
OR 連接的是兩個不同的字段，不走索引
給address列增加索引
alter table t_user add index idx_address(address);
explain select * from t_user where user_name = 'mayun10' or address = '浙江杭州12';
OR 連接的是兩個不同字段，如果兩個字段皆有索引，走索引
（下一期：《MySQL面試小抄》幾種索引失效場景驗證）
面試小抄系列。
面試官考點(diǎn)之哪些情況下需要創(chuàng)建索引？
1.主鍵自動建立唯一索引
2.頻繁查詢的字段
3.JOIN 關(guān)聯(lián)查詢，作為外鍵關(guān)系的列建立索引
4.單鍵/組合索引的選擇問題，高并發(fā)下傾向創(chuàng)建組合索引，創(chuàng)建時遵循最左前綴匹配原則
5.ORDER BY 查詢中排序的字段，排序字段通過索引訪問大幅提高排序速度
6.GROUP BY 需要分組字段或查詢中統(tǒng)計字段
面試官考點(diǎn)之聯(lián)合索引之最左前綴原則
MySQL建立多列索引（聯(lián)合索引）有最左前綴的原則，即最左優(yōu)先
當(dāng)MySQL建立的是聯(lián)合索引，假設(shè)以(a,b,c) 列作為聯(lián)合索引，那么MySQL建樹規(guī)則是什么？
我們知道MySQL會為每一個索引維護(hù)一顆B+Tree，非葉子節(jié)點(diǎn)存儲索引key，葉子節(jié)點(diǎn)存儲行數(shù)據(jù)data。
聯(lián)合索引(a,b,c) 相當(dāng)于建立了 (a), (a,b), (a,b,c) 三個索引，MySQL組裝索引樹時，是按照從左到右的順序來建立B+Tree的聯(lián)合索引樹的。
匹配索引情況一
假設(shè)（a,b,c）索引要搜索的值為('張三', 21, 100) ，檢索數(shù)據(jù)時，匹配的順序就是a,b,c。
B+Tree會優(yōu)先比較a來確定下一步的所搜方向，如果a相同再依次比較b和c，最后得到檢索的數(shù)據(jù)；
匹配索引情況二
假設(shè)（a,c）索引要搜索的值為('張三', 100) ，檢索數(shù)據(jù)時，匹配的順序就是a,b,c。
B+Tree使用a來指定搜索方向，但下一個字段b缺失，所以只能把a(bǔ)等于張三的數(shù)據(jù)都找到，然后再匹配c是100的數(shù)據(jù)。
匹配索引情況三
假設(shè)（b,c）索引要搜索的值為('張三', 21) ，檢索數(shù)據(jù)時，無匹配順序
B+Tree不知道下一步該查哪個節(jié)點(diǎn)，因為建立搜索樹的時候a是第一個比較因子，必須要先根據(jù)a來搜索才能知道下一步去哪里查詢。此時索引失效！
索引項是按照索引定義里面出現(xiàn)的字段順序排序的，最左前綴可以是聯(lián)合索引的最左N個字段，也可以是字符串索引的最左M個字符。
面試官考點(diǎn)之索引下推場景？
索引下推，即減少二級索引回表搜索次數(shù)?。。?/p>
通俗說，減少查詢主鍵索引樹次數(shù)，減少磁盤IO
建立聯(lián)合索引 idx_age_weight
select * from user where age = 11 and weight = 98
5.6之前搜索過程是
在idx_age_weight 索引樹中匹配出所有的 age = 11 索引，拿到主鍵id，回表去一條條再比對weight字段
如下圖，需要進(jìn)行3次回表搜索操作
5.6后的搜索過程是
在idx_age_weight 索引樹中匹配出所有的 age = 11 索引，順便對weight字段進(jìn)行判斷，過濾掉weight = 100的記錄，然后再進(jìn)行回表搜索。
如下圖，只需要進(jìn)行2次回表搜索操作
閱讀原文：
《MySQL面試小抄》索引考點(diǎn)二面總結(jié)
《MySQL面試小抄》索引考點(diǎn)一面總結(jié)
隨緣更新，整理不易，歡迎聯(lián)系小白討論，大神巴巴請繞路！
更多精彩內(nèi)容，歡迎關(guān)注公眾號：囧么肥事 (或搜索：jiongmefeishi)
