?前言

大家好，我是田螺。

金三銀四很快就要來啦，準備了索引的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存儲引擎中，它的主鍵索引，普通索引都是非聚簇索引，因為數據和索引是分開的，葉子節點都使用一個地址指向真正的表數據。