我們準備了索引的15連問，相信大家看完肯定會有幫助的。
1. 索引是什么？
- 索引是一種能提高數據庫查詢效率的數據結構。它可以比作一本字典的目錄，可以幫你快速找到對應的記錄。
- 索引一般存儲在磁盤的文件中，它是占用物理空間的。
- 正所謂水能載舟，也能覆舟。適當的索引能提高查詢效率，過多的索引會影響數據庫表的插入和更新功能。
2. MySQL索引有哪些類型
數據結構維度
- B+樹索引：所有數據存儲在葉子節點，復雜度為O(logn)，適合范圍查詢。
- 哈希索引: 適合等值查詢，檢索效率高，一次到位。
- 全文索引：MyISAM和InnoDB中都支持使用全文索引，一般在文本類型char,text,varchar類型上創建。
- R-Tree索引：用來對GIS數據類型創建SPATIAL索引
物理存儲維度
- 聚集索引：聚集索引就是以主鍵創建的索引，在葉子節點存儲的是表中的數據。（Innodb存儲引擎）
- 非聚集索引：非聚集索引就是以非主鍵創建的索引，在葉子節點存儲的是主鍵和索引列。（Innodb存儲引擎）
邏輯維度
- 主鍵索引：一種特殊的唯一索引，不允許有空值。
- 普通索引：MySQL中基本索引類型，允許空值和重復值。
- 聯合索引：多個字段創建的索引，使用時遵循最左前綴原則。
- 唯一索引：索引列中的值必須是唯一的，但是允許為空值。
- 空間索引：MySQL5.7之后支持空間索引，在空間索引這方面遵循OpenGIS幾何數據模型規則。
3. 索引什么時候會失效？
- 查詢條件包含or，可能導致索引失效
- 如果字段類型是字符串，where時一定用引號括起來，否則索引失效
- like通配符可能導致索引失效。
- 聯合索引，查詢時的條件列不是聯合索引中的第一個列，索引失效。
- 在索引列上使用 mysql 的內置函數，索引失效。
- 對索引列運算（如，+、-、*、/），索引失效。
- 索引字段上使用（！= 或者 < >，not in）時，可能會導致索引失效。
- 索引字段上使用is null， is not null，可能導致索引失效。
- 左連接查詢或者右連接查詢查詢關聯的字段編碼格式不一樣，可能導致索引失效。
- mysql 估計使用全表掃描要比使用索引快,則不使用索引。
4. 哪些場景不適合建立索引？
- 數據量少的表，不適合加索引
- 更新比較頻繁的也不適合加索引
- 區分度低的字段不適合加索引（如性別）
- where、group by、order by等后面沒有使用到的字段，不需要建立索引
- 已經有冗余的索引的情況（比如已經有a,b的聯合索引，不需要再單獨建立a索引）
5. 為什么要用 B+樹，為什么不用二叉樹？
可以從幾個維度去看這個問題，查詢是否夠快，效率是否穩定，存儲數據多少， 以及查找磁盤次數，為什么不是二叉樹，為什么不是平衡二叉樹，為什么不是 B 樹，而偏偏是 B+樹呢？
為什么不是一般二叉樹？
如果二叉樹特殊化為一個鏈表，相當于全表掃描。平衡二叉樹相比于二叉查找 樹來說，查找效率更穩定，總體的查找速度也更快。
為什么不是平衡二叉樹呢？
我們知道，在內存比在磁盤的數據，查詢效率快得多。如果樹這種數據結構作 為索引，那我們每查找一次數據就需要從磁盤中讀取一個節點，也就是我們說 的一個磁盤塊，但是平衡二叉樹可是每個節點只存儲一個鍵值和數據的，如果 是 B 樹，可以存儲更多的節點數據，樹的高度也會降低，因此讀取磁盤的次數 就降下來啦，查詢效率就快啦。
那為什么不是 B 樹而是 B+樹呢？
- B+樹非葉子節點上是不存儲數據的，僅存儲鍵值，而 B 樹節點中不僅存儲 鍵值，也會存儲數據。innodb 中頁的默認大小是 16KB，如果不存儲數據，那 么就會存儲更多的鍵值，相應的樹的階數（節點的子節點樹）就會更大，樹就 會更矮更胖，如此一來我們查找數據進行磁盤的 IO 次數有會再次減少，數據查 詢的效率也會更快。
- B+樹索引的所有數據均存儲在葉子節點，而且數據是按照順序排列的，鏈 表連著的。那么 B+樹使得范圍查找，排序查找，分組查找以及去重查找變得 異常簡單。
6. 一次B+樹索引樹查找過程
假設有以下表結構，并且初始化了這幾條數據
假設有以下表結構，并且初始化了這幾條數據
CREATE TABLE `employee` (
`id` int(11) NOT NULL,
`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');
執行這條查詢SQL，需要執行幾次的樹搜索操作？可以畫下對應的索引樹結構圖~
select * from Temployee where age=32;
其實這個，這個大家可以先畫出idx_age普通索引的索引結構圖，大概如下：
再畫出id主鍵索引，我們先畫出聚族索引結構圖，如下：
這條 SQL 查詢語句執行大概流程是這樣的：
- 搜索idx_age 索引樹，將磁盤塊1加載到內存，由于32<43,搜索左路分支，到磁盤尋址磁盤塊2。
- 將磁盤塊2加載到內存中，由于32<36,搜索左路分支，到磁盤尋址磁盤塊4。
- 將磁盤塊4加載到內存中，在內存繼續遍歷，找到age=32的記錄，取得id = 400.
- 拿到id=400后，回到id主鍵索引樹。
- 搜索id主鍵索引樹，將磁盤塊1加載到內存，因為300<400<500,所以在選擇中間分支，到磁盤尋址磁盤塊3。
- 雖然在磁盤塊3，找到了id=400，但是它不是葉子節點，所以會繼續往下找。到磁盤尋址磁盤塊8。
- 將磁盤塊8加載內存，在內存遍歷，找到id=400的記錄，拿到R4這一行的數據，好的，大功告成。
7. 什么是回表？如何減少回表？
當查詢的數據在索引樹中，找不到的時候，需要回到主鍵索引樹中去獲取，這個過程叫做回表。
比如在第6小節中，使用的查詢SQL
select * from Temployee where age=32;
需要查詢所有列的數據，idx_age普通索引不能滿足，需要拿到主鍵id的值后，再回到id主鍵索引查找獲取，這個過程就是回表。
8. 什么是覆蓋索引？
如果我們查詢SQL的select * 修改為 select id, age的話，其實是不需要回表的。因為id和age的值，都在idx_age索引樹的葉子節點上，這就涉及到覆蓋索引的知識點了。
覆蓋索引是select的數據列只用從索引中就能夠取得，不必回表，換句話說，查詢列要被所建的索引覆蓋。
9. 聊聊索引的最左前綴原則
索引的最左前綴原則，可以是聯合索引的最左N個字段。比如你建立一個組合索引（a,b,c），其實可以相當于建了（a），（a,b）,(a,b,c)三個索引，大大提高了索引復用能力。
當然，最左前綴也可以是字符串索引的最左M個字符。比如，你的普通索引樹是醬紫：
這個SQL：
select * from employee where name like '小%' order by age desc;
也是命中索引的。
10. 索引下推了解過嗎？什么是索引下推
給你這個SQL：
select * from employee where name like '小%' and age=28 and sex='0';
其中，name和age為聯合索引（idx_name_age）。
如果是Mysql5.6之前，在idx_name_age索引樹，找出所有名字第一個字是“小”的人，拿到它們的主鍵id，然后回表找出數據行，再去對比年齡和性別等其他字段。如圖：
有些朋友可能覺得奇怪，idx_name_age（name,age)不是聯合索引嘛？為什么選出包含“小”字后，不再順便看下年齡age再回表呢，不是更高效嘛？所以呀，MySQL 5.6就引入了索引下推優化，可以在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。
因此，MySQL5.6版本之后，選出包含“小”字后，順表過濾age=28
11. 大表如何添加索引
如果一張表數據量級是千萬級別以上的，那么，如何給這張表添加索引？
我們需要知道一點，給表添加索引的時候，是會對表加鎖的。如果不謹慎操作，有可能出現生產事故的?？梢詤⒖家韵路椒ǎ?/p>
（1）先創建一張跟原表A數據結構相同的新表B。
（2）在新表B添加需要加上的新索引。
（3）把原表A數據導到新表B
（4）rename新表B為原表的表名A，原表A換別的表名；
12. 如何知道語句是否走索引查詢？
explain查看SQL的執行計劃，這樣就知道是否命中索引了。
當explain與SQL一起使用時，MySQL將顯示來自優化器的有關語句執行計劃的信息。
一般來說，我們需要重點關注type、rows、filtered、extra、key。
1.2.1 type
type表示連接類型，查看索引執行情況的一個重要指標。以下性能從好到壞依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
- system：這種類型要求數據庫表中只有一條數據，是const類型的一個特例，一般情況下是不會出現的。
- const：通過一次索引就能找到數據，一般用于主鍵或唯一索引作為條件，這類掃描效率極高，，速度非?？?。
- eq_ref：常用于主鍵或唯一索引掃描，一般指使用主鍵的關聯查詢
- ref : 常用于非主鍵和唯一索引掃描。
- ref_or_null：這種連接類型類似于ref，區別在于MySQL會額外搜索包含NULL值的行
- index_merge：使用了索引合并優化方法，查詢使用了兩個以上的索引。
- unique_subquery：類似于eq_ref，條件用了in子查詢
- index_subquery：區別于unique_subquery，用于非唯一索引，可以返回重復值。
- range：常用于范圍查詢，比如：between ... and 或 In 等操作
- index：全索引掃描
- ALL：全表掃描
1.2.2 rows
該列表示MySQL估算要找到我們所需的記錄，需要讀取的行數。對于InnoDB表，此數字是估計值，并非一定是個準確值。
1.2.3 filtered
該列是一個百分比的值，表里符合條件的記錄數的百分比。簡單點說，這個字段表示存儲引擎返回的數據在經過過濾后，剩下滿足條件的記錄數量的比例。
1.2.4 extra
該字段包含有關MySQL如何解析查詢的其他信息，它一般會出現這幾個值：
- Using filesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情況才會出現。一般見于order by語句
- Using index ：表示是否用了覆蓋索引。
- Using temporary: 表示是否使用了臨時表,性能特別差，需要重點優化。一般多見于group by語句，或者union語句。
- Using where : 表示使用了where條件過濾.
- Using index condition：MySQL5.6之后新增的索引下推。在存儲引擎層進行數據過濾，而不是在服務層過濾，利用索引現有的數據減少回表的數據。
1.2.5 key
該列表示實際用到的索引。一般配合possible_keys列一起看。
13.Hash 索引和 B+樹區別是什么？你在設計索引是怎么抉擇的？
- B+樹可以進行范圍查詢，Hash 索引不能。
- B+樹支持聯合索引的最左側原則，Hash 索引不支持。
- B+樹支持 order by 排序，Hash 索引不支持。
- Hash 索引在等值查詢上比 B+樹效率更高。（但是索引列的重復值很多的話，Hash沖突，效率降低）。
- B+樹使用 like 進行模糊查詢的時候，like 后面（比如%開頭）的話可以起到優化的作用，Hash 索引根本無法進行模糊查詢。
14. 索引有哪些優缺點？
優點：
- 索引可以加快數據查詢速度，減少查詢時間
- 唯一索引可以保證數據庫表中每一行的數據的唯一性
缺點：
- 創建索引和維護索引要耗費時間
- 索引需要占物理空間，除了數據表占用數據空間之外，每一個索引還要占用一定的物理空間
- 以表中的數據進行增、刪、改的時候，索引也要動態的維護。
15. 聚簇索引與非聚簇索引的區別
聚簇索引并不是一種單獨的索引類型，而是一種數據存儲方式。它表示索引結構和數據一起存放的索引。非聚集索引是索引結構和數據分開存放的索引。
接下來，我們分不同存存儲引擎去聊哈~
在MySQL的InnoDB存儲引擎中， 聚簇索引與非聚簇索引最大的區別，在于葉節點是否存放一整行記錄。聚簇索引葉子節點存儲了一整行記錄，而非聚簇索引葉子節點存儲的是主鍵信息，因此，一般非聚簇索引還需要回表查詢。
- 一個表中只能擁有一個聚集索引（因為一般聚簇索引就是主鍵索引），而非聚集索引一個表則可以存在多個。
- 一般來說，相對于非聚簇索引，聚簇索引查詢效率更高，因為不用回表。
而在MyISM存儲引擎中，它的主鍵索引，普通索引都是非聚簇索引，因為數據和索引是分開的，葉子節點都使用一個地址指向真正的表數據。
作者丨撿田螺的小男孩
來源丨公眾號：撿田螺的小男孩（ID：gh_51e0e901a289）
