1. 索引是什么？

• 索引是一種能提高數(shù)據(jù)庫(kù)查詢效率的數(shù)據(jù)結(jié)構(gòu)。它可以比作一本字典的目錄，可以幫你快速找到對(duì)應(yīng)的記錄。

• 索引一般存儲(chǔ)在磁盤(pán)的文件中，它是占用物理空間的。

• 正所謂水能載舟，也能覆舟。適當(dāng)?shù)乃饕芴岣卟樵冃?#xff0c;過(guò)多的索引會(huì)影響數(shù)據(jù)庫(kù)表的插入和更新功能。

2. MySQL索引有哪些類型

數(shù)據(jù)結(jié)構(gòu)維度

• B+樹(shù)索引：所有數(shù)據(jù)存儲(chǔ)在葉子節(jié)點(diǎn)，復(fù)雜度為O(logn)，適合范圍查詢。

• 哈希索引: 適合等值查詢，檢索效率高，一次到位。

• 全文索引：MyISAM和InnoDB中都支持使用全文索引，一般在文本類型char,text,varchar類型上創(chuàng)建。

• R-Tree索引: 用來(lái)對(duì)GIS數(shù)據(jù)類型創(chuàng)建SPATIAL索引

物理存儲(chǔ)維度

• 聚集索引：聚集索引就是以主鍵創(chuàng)建的索引，在葉子節(jié)點(diǎn)存儲(chǔ)的是表中的數(shù)據(jù)。（Innodb存儲(chǔ)引擎）

• 非聚集索引：非聚集索引就是以非主鍵創(chuàng)建的索引，在葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵和索引列。（Innodb存儲(chǔ)引擎）

邏輯維度

• 主鍵索引：一種特殊的唯一索引，不允許有空值。

• 普通索引：MySQL中基本索引類型，允許空值和重復(fù)值。

• 聯(lián)合索引：多個(gè)字段創(chuàng)建的索引，使用時(shí)遵循最左前綴原則。

• 唯一索引：索引列中的值必須是唯一的，但是允許為空值。

• 空間索引：MySQL5.7之后支持空間索引，在空間索引這方面遵循OpenGIS幾何數(shù)據(jù)模型規(guī)則。

3. 索引什么時(shí)候會(huì)失效？

• 查詢條件包含or，可能導(dǎo)致索引失效

• 如果字段類型是字符串，where時(shí)一定用引號(hào)括起來(lái)，否則索引失效

• like通配符可能導(dǎo)致索引失效。

• 聯(lián)合索引，查詢時(shí)的條件列不是聯(lián)合索引中的第一個(gè)列，索引失效。

• 在索引列上使用 mysql 的內(nèi)置函數(shù)，索引失效。

• 對(duì)索引列運(yùn)算（如，+、-、*、/），索引失效。

• 索引字段上使用（！= 或者 < >，not in）時(shí)，可能會(huì)導(dǎo)致索引失效。

• 索引字段上使用is null， is not null，可能導(dǎo)致索引失效。

• 左連接查詢或者右連接查詢查詢關(guān)聯(lián)的字段編碼格式不一樣，可能導(dǎo)致索引失效。

• mysql 估計(jì)使用全表掃描要比使用索引快,則不使用索引。

4. 哪些場(chǎng)景不適合建立索引？

• 數(shù)據(jù)量少的表，不適合加索引

• 更新比較頻繁的也不適合加索引

• 區(qū)分度低的字段不適合加索引（如性別）

• where、group by、order by等后面沒(méi)有使用到的字段，不需要建立索引

• 已經(jīng)有冗余的索引的情況（比如已經(jīng)有a,b的聯(lián)合索引，不需要再單獨(dú)建立a索引）

5. 為什么要用 B+樹(shù)，為什么不用二叉樹(shù)？

可以從幾個(gè)維度去看這個(gè)問(wèn)題，查詢是否夠快，效率是否穩(wěn)定，存儲(chǔ)數(shù)據(jù)多少，以及查找磁盤(pán)次數(shù)，為什么不是二叉樹(shù)，為什么不是平衡二叉樹(shù)，為什么不是B 樹(shù)，而偏偏是 B+樹(shù)呢？

為什么不是一般二叉樹(shù)？

如果二叉樹(shù)特殊化為一個(gè)鏈表，相當(dāng)于全表掃描。平衡二叉樹(shù)相比于二叉查找 樹(shù)來(lái)說(shuō)，查找效率更穩(wěn)定，總體的查找速度也更快。

為什么不是平衡二叉樹(shù)呢？

我們知道，在內(nèi)存比在磁盤(pán)的數(shù)據(jù)，查詢效率快得多。如果樹(shù)這種數(shù)據(jù)結(jié)構(gòu)作為索引，那我們每查找一次數(shù)據(jù)就需要從磁盤(pán)中讀取一個(gè)節(jié)點(diǎn)，也就是我們說(shuō)的一個(gè)磁盤(pán)塊，但是平衡二叉樹(shù)可是每個(gè)節(jié)點(diǎn)只存儲(chǔ)一個(gè)鍵值和數(shù)據(jù)的，如果是 B 樹(shù)，可以存儲(chǔ)更多的節(jié)點(diǎn)數(shù)據(jù)，樹(shù)的高度也會(huì)降低，因此讀取磁盤(pán)的次數(shù)就降下來(lái)啦，查詢效率就快啦。

那為什么不是 B 樹(shù)而是 B+樹(shù)呢？

• B+樹(shù)非葉子節(jié)點(diǎn)上是不存儲(chǔ)數(shù)據(jù)的，僅存儲(chǔ)鍵值，而 B 樹(shù)節(jié)點(diǎn)中不僅存儲(chǔ)鍵值，也會(huì)存儲(chǔ)數(shù)據(jù)。innodb 中頁(yè)的默認(rèn)大小是 16KB，如果不存儲(chǔ)數(shù)據(jù)，那 么就會(huì)存儲(chǔ)更多的鍵值，相應(yīng)的樹(shù)的階數(shù)（節(jié)點(diǎn)的子節(jié)點(diǎn)樹(shù)）就會(huì)更大，樹(shù)就 會(huì)更矮更胖，如此一來(lái)我們查找數(shù)據(jù)進(jìn)行磁盤(pán)的 IO 次數(shù)有會(huì)再次減少，數(shù)據(jù)查詢的效率也會(huì)更快。

• B+樹(shù)索引的所有數(shù)據(jù)均存儲(chǔ)在葉子節(jié)點(diǎn)，而且數(shù)據(jù)是按照順序排列的，鏈表連著的。那么 B+樹(shù)使得范圍查找，排序查找，分組查找以及去重查找變得 異常簡(jiǎn)單。

6. 一次B+樹(shù)索引樹(shù)查找過(guò)程

假設(shè)有以下表結(jié)構(gòu)，并且初始化了這幾條數(shù)據(jù)

CREATETABLE `employee` (`id` int(11) NOTNULL,`name` varchar(255) DEFAULT NULL,`age` int(11) DEFAULT NULL,`date` datetime DEFAULT NULL,`sex` int(1) DEFAULT  NULL,PRIMARY KEY (`id`),KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into employee values(100,'小倫',43,'2021-01-20','0');
insert into employee values(200,'俊杰',48,'2021-01-21','0');
insert into employee values(300,'紫琪',36,'2020-01-21','1');
insert into employee values(400,'立紅',32,'2020-01-21','0');
insert into employee values(500,'易迅',37,'2020-01-21','1');
insert into employee values(600,'小軍',49,'2021-01-21','0');
insert into employee values(700,'小燕',28,'2021-01-21','1');

執(zhí)行這條查詢SQL，需要執(zhí)行幾次的樹(shù)搜索操作？可以畫(huà)下對(duì)應(yīng)的索引樹(shù)結(jié)構(gòu)圖~

select * from Temployee where age=32;
復(fù)制代碼

其實(shí)這個(gè)，這個(gè)大家可以先畫(huà)出idx_age普通索引的索引結(jié)構(gòu)圖，大概如下：

再畫(huà)出id主鍵索引，我們先畫(huà)出聚族索引結(jié)構(gòu)圖，如下：

這條 SQL 查詢語(yǔ)句執(zhí)行大概流程是這樣的：

• 搜索idx_age 索引樹(shù)，將磁盤(pán)塊1加載到內(nèi)存，由于32<43,搜索左路分支，到磁盤(pán)尋址磁盤(pán)塊2。

• 將磁盤(pán)塊2加載到內(nèi)存中，由于32<36,搜索左路分支，到磁盤(pán)尋址磁盤(pán)塊4。

• 將磁盤(pán)塊4加載到內(nèi)存中，在內(nèi)存繼續(xù)遍歷，找到age=32的記錄，取得id = 400.

• 拿到id=400后，回到id主鍵索引樹(shù)。

• 搜索id主鍵索引樹(shù)，將磁盤(pán)塊1加載到內(nèi)存，因?yàn)?00<400<500,所以在選擇中間分支，到磁盤(pán)尋址磁盤(pán)塊3。

• 雖然在磁盤(pán)塊3，找到了id=400，但是它不是葉子節(jié)點(diǎn)，所以會(huì)繼續(xù)往下找。 到磁盤(pán)尋址磁盤(pán)塊8。

• 將磁盤(pán)塊8加載內(nèi)存，在內(nèi)存遍歷，找到id=400的記錄，拿到R4這一行的數(shù)據(jù)，好的，大功告成。

7. 什么是回表？如何減少回表？

當(dāng)查詢的數(shù)據(jù)在索引樹(shù)中，找不到的時(shí)候，需要回到主鍵索引樹(shù)中去獲取，這個(gè)過(guò)程叫做回表。

比如在第6小節(jié)中，使用的查詢SQL

select * from employee where age=32;

需要查詢所有列的數(shù)據(jù)，idx_age普通索引不能滿足，需要拿到主鍵id的值后，再回到id主鍵索引查找獲取，這個(gè)過(guò)程就是回表。

8. 什么是覆蓋索引？

如果我們查詢SQL的select * 修改為 select id, age的話，其實(shí)是不需要回表的。因?yàn)閕d和age的值，都在idx_age索引樹(shù)的葉子節(jié)點(diǎn)上，這就涉及到覆蓋索引的只是點(diǎn)了。

覆蓋索引是select的數(shù)據(jù)列只用從索引中就能夠取得，不必回表，換句話說(shuō)，查詢列要被所建的索引覆蓋。

9. 聊聊索引的最左前綴原則

索引的最左前綴原則，可以是聯(lián)合索引的最左N個(gè)字段。比如你建立一個(gè)組合索引（a,b,c），其實(shí)可以相當(dāng)于建了（a），（a,b）,(a,b,c)三個(gè)索引，大大提高了索引復(fù)用能力。

當(dāng)然，最左前綴也可以是字符串索引的最左M個(gè)字符。。 比如，你的普通索引樹(shù)是醬紫：

這個(gè)SQL: select * from employee where name like '小%' order by age desc; 也是命中索引的。

10. 索引下推了解過(guò)嗎？什么事索引下推

給你這個(gè)SQL：

select*from employee where name like'小%'and age=28and sex='0';

其中，name和age為聯(lián)合索引（idx_name_age）。

如果是Mysql5.6之前，在idx_name_age索引樹(shù)，找出所有名字第一個(gè)字是“小”的人，拿到它們的主鍵id，然后回表找出數(shù)據(jù)行，再去對(duì)比年齡和性別等其他字段。如圖：

有些朋友可能覺(jué)得奇怪，idx_name_age（name,age)不是聯(lián)合索引嘛？為什么選出包含“小”字后，不再順便看下年齡age再回表呢，不是更高效嘛？所以呀，MySQL 5.6就引入了索引下推優(yōu)化，可以在索引遍歷過(guò)程中，對(duì)索引中包含的字段先做判斷，直接過(guò)濾掉不滿足條件的記錄，減少回表次數(shù)。

因此，MySQL5.6版本之后，選出包含“小”字后，順表過(guò)濾age=28

11. 大表如何添加索引

如果一張表數(shù)據(jù)量級(jí)是千萬(wàn)級(jí)別以上的，那么，如何給這張表添加索引？

我們需要知道一點(diǎn)，給表添加索引的時(shí)候，是會(huì)對(duì)表加鎖的。如果不謹(jǐn)慎操作，有可能出現(xiàn)生產(chǎn)事故的。可以參考以下方法：

1. 先創(chuàng)建一張跟原表A數(shù)據(jù)結(jié)構(gòu)相同的新表B。

2. 在新表B添加需要加上的新索引。

3. 把原表A數(shù)據(jù)導(dǎo)到新表B

4. rename新表B為原表的表名A，原表A換別的表名；

12. 如何知道語(yǔ)句是否走索引查詢？

explain查看SQL的執(zhí)行計(jì)劃，這樣就知道是否命中索引了。

當(dāng)explain與SQL一起使用時(shí)，MySQL將顯示來(lái)自優(yōu)化器的有關(guān)語(yǔ)句執(zhí)行計(jì)劃的信息。

一般來(lái)說(shuō)，我們需要重點(diǎn)關(guān)注type、rows、filtered、extra、key。

1.2.1 type

type表示連接類型，查看索引執(zhí)行情況的一個(gè)重要指標(biāo)。以下性能從好到壞依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

• system：這種類型要求數(shù)據(jù)庫(kù)表中只有一條數(shù)據(jù)，是const類型的一個(gè)特例，一般情況下是不會(huì)出現(xiàn)的。

• const：通過(guò)一次索引就能找到數(shù)據(jù)，一般用于主鍵或唯一索引作為條件，這類掃描效率極高，，速度非?？?。

• eq_ref：常用于主鍵或唯一索引掃描，一般指使用主鍵的關(guān)聯(lián)查詢

• ref : 常用于非主鍵和唯一索引掃描。

• ref_or_null：這種連接類型類似于ref，區(qū)別在于MySQL會(huì)額外搜索包含NULL值的行

• index_merge：使用了索引合并優(yōu)化方法，查詢使用了兩個(gè)以上的索引。

• unique_subquery：類似于eq_ref，條件用了in子查詢

• index_subquery：區(qū)別于unique_subquery，用于非唯一索引，可以返回重復(fù)值。

• range：常用于范圍查詢，比如：between ... and 或 In 等操作

• index：全索引掃描

• ALL：全表掃描

1.2.2 rows

該列表示MySQL估算要找到我們所需的記錄，需要讀取的行數(shù)。對(duì)于InnoDB表，此數(shù)字是估計(jì)值，并非一定是個(gè)準(zhǔn)確值。

1.2.3 filtered

該列是一個(gè)百分比的值，表里符合條件的記錄數(shù)的百分比。簡(jiǎn)單點(diǎn)說(shuō)，這個(gè)字段表示存儲(chǔ)引擎返回的數(shù)據(jù)在經(jīng)過(guò)過(guò)濾后，剩下滿足條件的記錄數(shù)量的比例。

1.2.4 extra

該字段包含有關(guān)MySQL如何解析查詢的其他信息，它一般會(huì)出現(xiàn)這幾個(gè)值：

• Using filesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情況才會(huì)出現(xiàn)。一般見(jiàn)于order by語(yǔ)句

• Using index ：表示是否用了覆蓋索引。

• Using temporary: 表示是否使用了臨時(shí)表,性能特別差，需要重點(diǎn)優(yōu)化。一般多見(jiàn)于group by語(yǔ)句，或者union語(yǔ)句。

• Using where : 表示使用了where條件過(guò)濾.

• Using index condition：MySQL5.6之后新增的索引下推。在存儲(chǔ)引擎層進(jìn)行數(shù)據(jù)過(guò)濾，而不是在服務(wù)層過(guò)濾，利用索引現(xiàn)有的數(shù)據(jù)減少回表的數(shù)據(jù)。

1.2.5 key

該列表示實(shí)際用到的索引。一般配合possible_keys列一起看。

13.Hash 索引和 B+樹(shù)區(qū)別是什么？你在設(shè)計(jì)索引是怎么抉擇的？

• B+樹(shù)可以進(jìn)行范圍查詢，Hash 索引不能。

• B+樹(shù)支持聯(lián)合索引的最左側(cè)原則，Hash 索引不支持。

• B+樹(shù)支持 order by 排序，Hash 索引不支持。

• Hash 索引在等值查詢上比 B+樹(shù)效率更高。（但是索引列的重復(fù)值很多的話，Hash沖突，效率降低）。

• B+樹(shù)使用 like 進(jìn)行模糊查詢的時(shí)候，like 后面（比如%開(kāi)頭）的話可以起到優(yōu)化的作用，Hash 索引根本無(wú)法進(jìn)行模糊查詢。

14. 索引有哪些優(yōu)缺點(diǎn)？

優(yōu)點(diǎn)：

• 索引可以加快數(shù)據(jù)查詢速度，減少查詢時(shí)間

• 唯一索引可以保證數(shù)據(jù)庫(kù)表中每一行的數(shù)據(jù)的唯一性

缺點(diǎn)：

• 創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間

• 索引需要占物理空間，除了數(shù)據(jù)表占用數(shù)據(jù)空間之外，每一個(gè)索引還要占用一定的物理空間

• 以表中的數(shù)據(jù)進(jìn)行增、刪、改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)。

15. 聚簇索引與非聚簇索引的區(qū)別

聚簇索引并不是一種單獨(dú)的索引類型，而是一種數(shù)據(jù)存儲(chǔ)方式。它表示索引結(jié)構(gòu)和數(shù)據(jù)一起存放的索引。非聚集索引是索引結(jié)構(gòu)和數(shù)據(jù)分開(kāi)存放的索引。

接下來(lái)，我們分不同存存儲(chǔ)引擎去聊哈~

在MySQL的InnoDB存儲(chǔ)引擎中， 聚簇索引與非聚簇索引最大的區(qū)別，在于葉節(jié)點(diǎn)是否存放一整行記錄。聚簇索引葉子節(jié)點(diǎn)存儲(chǔ)了一整行記錄，而非聚簇索引葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵信息，因此，一般非聚簇索引還需要回表查詢。

• 一個(gè)表中只能擁有一個(gè)聚集索引（因?yàn)橐话憔鄞厮饕褪?span id="vxwlu0yf4" class="kdocs-bold" style="font-weight:bold;">主鍵索引），而非聚集索引一個(gè)表則可以存在多個(gè)。

• 一般來(lái)說(shuō)，相對(duì)于非聚簇索引，聚簇索引查詢效率更高，因?yàn)椴挥没乇怼?/p>

而在MyISM存儲(chǔ)引擎中，它的主鍵索引，普通索引都是非聚簇索引，因?yàn)閿?shù)據(jù)和索引是分開(kāi)的，葉子節(jié)點(diǎn)都使用一個(gè)地址指向真正的表數(shù)據(jù)。