??1. 索引底層采用什么數(shù)據(jù)結(jié)構(gòu)？為什么不用hash

索引底層數(shù)據(jù)結(jié)構(gòu)是B+樹

不使用hash：因為其底層是哈希表實現(xiàn)，等值查詢，可以快速定位，一般情況效率很高，不穩(wěn)定，當出現(xiàn)大量鍵重復(fù)哈希沖突，效率下降，不支持范圍查詢，無法用于排序分組，無法模糊查詢，多列索引的最左前綴匹配原則，總要回表操作等。

??2. B樹與B+樹區(qū)別？為何用B+樹？

B+樹：非葉子結(jié)點不存data，只存key，查詢更穩(wěn)定，增大了廣度(B+樹出度更大，樹高矮，節(jié)點小，磁盤IO次數(shù)少)；葉子結(jié)點下一級指針（范圍查詢）；索引冗余。

與紅黑樹相比：

更少查詢次數(shù)：B+樹出度更大，樹高更低，查詢次數(shù)更少
磁盤預(yù)讀原理：為了減少IO操作，往往不嚴格按需讀取，而是預(yù)讀。B+樹葉子結(jié)點存儲相臨，讀取會快一些。

存儲更多索引結(jié)點：B+樹只在葉子結(jié)點儲存數(shù)據(jù)，非葉子結(jié)點存索引，而一個結(jié)點就是磁盤一個內(nèi)存頁，內(nèi)存頁大小固定，那么相比B樹這些可以·存更多的索引結(jié)點，出度更大，樹高矮，查詢次數(shù)少，磁盤IO少。

??3. 自增主鍵理解？

自增主鍵：InnoDB引擎的自增值，其實是保存在了內(nèi)存里，并且到了MySQL 8.0版本后，才有了“自增值持久化”的能力。

也就是才實現(xiàn)了“如果發(fā)生重啟，表的自增值可以恢復(fù)為MySQL重啟前的值”，具體情況是：（查看表結(jié)構(gòu)，會看到自增主鍵=多少）

● 在MySQL 5.7及之前的版本，自增值保存在內(nèi)存里，并沒有持久化。每次重啟后，第一次打開表的時候，都會去找自增值的最大值max(id)，然后將max(id)+1作為這個表當前的自增值。

● 舉例來說：如果一個表當前數(shù)據(jù)行里最大的id是10，AUTO_INCREMENT=11。這時候，我們刪除id=10的行，AUTO_INCREMENT還是11。但如果馬上重啟實例，重啟后這個表的AUTO_INCREMENT就會變成10。也就是說，MySQL重啟可能會修改一個表的AUTO_INCREMENT的值。

● 在MySQL 8.0版本，將自增值的變更記錄在了redo log中，重啟的時候依靠redo log恢復(fù)重啟之前的值。

自增值修改機制：

1. 如果插入數(shù)據(jù)時id字段指定為0、null 或未指定值，那么就把這個表當前的 AUTO_INCREMENT值填到自增字段；
2. 如果插入數(shù)據(jù)時id字段指定了具體的值，就直接使用語句里指定的值。

自增值新增機制：

1. 如果準備插入的值>=當前自增值，新的自增值就是“準備插入的值+1”；
2. 否則，自增值不變。

??4. 為什么自增主鍵不連續(xù)

● 在MySQL 5.7及之前的版本，自增值保存在內(nèi)存里，并沒有持久化

● 事務(wù)回滾（自增值不能回退，因為并發(fā)插入數(shù)據(jù)時，回退自增ID可能造成主鍵沖突）

● 唯一鍵沖突（由于表的自增值已變，但是主鍵發(fā)生沖突沒插進去，下一次插入主鍵=現(xiàn)在變了的子增值+1，所以不連續(xù)）

假設(shè)，表t里面已經(jīng)有了(1,1,1)這條記錄，這時我再執(zhí)行一條插入數(shù)據(jù)命令：

insert into t values(null, 1, 1); (自增id,唯一鍵c,普通字段d)

這個語句的執(zhí)行流程就是：

1. 執(zhí)行器調(diào)用InnoDB引擎接口寫入一行，傳入的這一行的值是(0,1,1);
2. InnoDB發(fā)現(xiàn)用戶沒有指定自增id的值，獲取表t當前的自增值2；
3. 將傳入的行的值改成(2,1,1)；
4. 將表的自增值改成3；
5. 繼續(xù)執(zhí)行插入數(shù)據(jù)操作，由于已經(jīng)存在c=1的記錄，所以報Duplicate key error，語句返回。
這個表的自增值改成3，是在真正執(zhí)行插入數(shù)據(jù)的操作之前。這個語句真正執(zhí)行的時候，因為碰到唯一鍵c沖突，所以id=2這一行并沒有插入成功，但也沒有將自增值再改回去。

所以，在這之后，再插入新的數(shù)據(jù)行時，拿到的自增id就是3。也就是說，出現(xiàn)了自增主鍵不連續(xù)的情況。

??5. Innodb為什么推薦用自增ID

①主鍵頁就會近乎于順序的記錄填滿，提升了頁面的最大填充率，不會有頁的浪費。

②新插入的行一定會在原有的最大數(shù)據(jù)行下一行，mysql定位和尋址很快，不會為計算新行的位置而做出額外的消耗。

③減少了頁分裂和碎片的產(chǎn)生

UUID：大量的隨機IO+頁分裂導(dǎo)致移動大量的數(shù)據(jù)+數(shù)據(jù)會有碎片。

總結(jié)：自增ID有序，會按順序往最后插入，而UUID無序，隨機生成，隨機插入，會造成頻繁頁分裂，內(nèi)存碎片化，大量隨機IO

??6. 索引有哪些類型

● 排好序的數(shù)據(jù)結(jié)構(gòu)，可以幫助快速查找數(shù)據(jù)

● 優(yōu)缺點：索引可以提高查詢速度，查詢使用優(yōu)化隱藏器提高性能，但是也會占據(jù)物理空間，降低增刪改的速度，因為還要操作索引文件

類型：

覆蓋索引+回表+索引下推+聯(lián)合索引

● 普通索引：可以重復(fù)

● 唯一索引：唯一，可為空，表中只有一個主鍵索引，可多個唯一索引

● 主鍵索引

• 唯一，不為空，葉子結(jié)點存出了行記錄數(shù)據(jù)，主鍵索引也稱聚簇索引，對應(yīng)非主鍵索引的葉子結(jié)點存的主鍵的值（二級索引），用二級索引查需要回表操作（根據(jù)二級索引查到主鍵，再根據(jù)主鍵去主鍵索引查）
• 一般推薦用自增主鍵，保證空間利用率，減少頁分裂

● 全文索引

● 覆蓋索引：索引字段覆蓋了查詢語句涉及的字段，直接通過索引文件就可以返回查詢所需的數(shù)據(jù)，不必通過回表操作。

● 回表：通過索引找到主鍵，再根據(jù)主鍵id去主鍵索引查。

● 索引下推

○ 在根據(jù)索引查詢過程中就根據(jù)查詢條件過濾掉一些記錄，減少最后的回表操作

假如執(zhí)行 select * from stu where name=? and age=?
沒有索引下推先再存儲引擎根據(jù)name篩選數(shù)據(jù)返回給server層，然后server層再根據(jù)age過濾
有索引下推直接根據(jù)name和age在存儲引擎層就篩選得到結(jié)果

??7. InnoDB與MyISAM的區(qū)別？

7.1 MyISAM與InnoDB區(qū)別

● InnoDB聚簇索引，MyISAM非聚簇索引

● InnoDB數(shù)據(jù)與索引一起保存.ibd，MyISAM表結(jié)構(gòu).frm 索引.myi 數(shù)據(jù).myd

● InnoDB支持事務(wù)、外鍵、行鎖表鎖，MyISAM不支持事務(wù)、外鍵、只支持表鎖

● select count(*)

● MyISAM查詢更優(yōu)，InnoDB更新更優(yōu)

● 都是B+tree索引

● MyISAM支持全文索引，InnoDB5.6后支持

7.2 MyISAM

● 不支持事務(wù)，但是每次查詢都是原子的

● 支持表級鎖，每次操作對整個表加鎖

● 存儲表的總行數(shù)

● 一個MyISAM表有三個文件：表結(jié)構(gòu).frm 索引.myi 數(shù)據(jù) .myd

● 采用非聚集索引，索引文件的數(shù)據(jù)域存儲指向數(shù)據(jù)文件的指針。輔索引與主索引基本一致，但是輔索引不用保證唯一性。

7.3 Innodb

● 支持ACID事務(wù)，支持四種隔離級別

● 支持行級鎖及外鍵約束，因此支持寫并發(fā)

● 不存儲總行

● 主鍵索引采用聚集索引(索引的數(shù)據(jù)域存儲數(shù)據(jù)文件本身)，輔索引的數(shù)據(jù)域存儲主鍵的值;因此從輔索引查找數(shù)據(jù)，需要先通過輔索引找到主鍵值，再訪問輔索引；最好使用自增主鍵，防止插入數(shù)據(jù)時，為維持B+樹結(jié)構(gòu)，文件的大調(diào)整。

7.4 使用場景

大多數(shù)時候我們使用的都是 InnoDB 存儲引擎，在某些讀密集的情況下，使用 MyISAM 也是合適的。不過，前提是你的項目不介意 MyISAM 不支持事務(wù)、崩潰恢復(fù)等缺點（可是~我們一般都會介意?。。?。

● MyISAM適合讀多更新少的：MyISAM索引跟數(shù)據(jù)分開放，因此有讀取更快的說法。

● InnoDB適合插入更新頻繁的：索引與數(shù)據(jù)一起放，建立索引更復(fù)雜，使用行鎖，更新頻繁效率更高

● 需要事務(wù)，高并發(fā)場景用Innodb：Innodb支持事務(wù)，采用行鎖

● MyISAM查詢比InnoDB快，更新InnoDB快

場景：MyISAM查詢更優(yōu)，InnoDB更新更優(yōu)

??8. 索引設(shè)計原則（查詢快，占用空間少）

● 出現(xiàn)在where子句或則連接子句中的列

● 基數(shù)小的表沒必要

● 使用短索引，如果索引長字符串列，應(yīng)該指定前綴長度

● 定義有外鍵的數(shù)據(jù)列一定索引

● 不要過度索引

● 更新頻繁的不適合

● 區(qū)分度不高的不適合，如性別

● 盡量擴展索引，別新建索引，如(a)->(a,b)

● 字符串字段建立索引方法

○ 1、直接創(chuàng)建完整索引，這樣可能比較占用空間；
○ 2、創(chuàng)建前綴索引，節(jié)省空間，但會增加查詢掃描次數(shù)，并且不能使用覆蓋索引；
○ 3、倒序存儲，再創(chuàng)建前綴索引，用于繞過字符串本身前綴的區(qū)分度不夠的問題；
○ 4、額外用一個字段進行索引，額外計算開銷

總結(jié)：索引設(shè)計原則要求查詢快，占用空間少；一般建在where條件，匹配度高的；要求基數(shù)大，區(qū)分度高，不要過大索引，盡量擴展，用聯(lián)合索引，更新頻繁不適合、使用短索引。

??9. 索引有哪些失效場景

● 以“%”開頭的like語句，索引無效，后綴“%”不影響

● or語句前后沒有同時使用索引

● 列類型是字符串，一定要在條件中將數(shù)據(jù)用引號引用，否則失效（隱式轉(zhuǎn)換）

● 如果mysql估計使用全表掃描比索引快，則不用索引（鍵值少，重復(fù)數(shù)據(jù)多）

● 組合索引要遵守最左前綴原則——不使用第一列索引 失效

● 在索引字段上使用not，<>，！= （對它處理是全表掃描）

● 對索引字段進行計算操作，字段使用函數(shù)也會失效

??10. 普通索引和唯一索引怎樣選

● 查詢比較

○ 查詢會以頁為單位將數(shù)據(jù)頁加載進內(nèi)存，不需要一條記錄一條記錄讀取磁盤。然后唯一索引根據(jù)條件查詢到記錄時就返回結(jié)果，普通索引查到第一條記錄往后遍歷直到不滿足條件，由于都在內(nèi)存中，不需要磁盤讀取那么大開銷，帶來的額外查詢開銷忽略不計，所以查詢性能幾乎一致

● 更新比較

○ 唯一索引由于更新時要檢查唯一性，所以需要將數(shù)據(jù)頁先加載進內(nèi)存才能判斷，此時直接操作內(nèi)存，不需要操作change buffer

○ 補充：普通索引若數(shù)據(jù)再內(nèi)存中直接內(nèi)存中更新，否則會將更新操作先記錄到channge buffer中，等下一次查詢將數(shù)據(jù)讀到內(nèi)存中再進行change buffer里相關(guān)更新操作后將數(shù)據(jù)返回，這樣一來，再寫多讀少的情況下就減少了磁盤IO，若寫完就馬上查詢，就大可不必用change buffer，不但沒提高多少效率還造成維護change buffer額外消耗

○ 將change buffer的操作對應(yīng)到原始數(shù)據(jù)頁的操作稱為merge（可以查詢來時讀到內(nèi)存再修改數(shù)據(jù)，后臺線程也會merge，數(shù)據(jù)庫正常關(guān)閉也會merge）

● 適合場景

○ 寫多讀少，選用普通索引更好，可以利用change buffer進行性能優(yōu)化減少磁盤IO，將更新操作記錄到change bufer，等查詢來了將數(shù)據(jù)讀到內(nèi)存再進行修改.

文章來源地址http://www.zghlxwxcb.cn/news/detail-478320.html

到了這里，關(guān)于MySQL 索引的10 個核心要點的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！