我們準備了索引的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. 大表如何添加索引
如果一張表資料量級是千萬級别以上的，那麼，如何給這張表添加索引？
我們需要知道一點，給表添加索引的時候，是會對表加鎖的。如果不謹慎操作，有可能出現生産事故的。可以參考以下方法：
（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）
dbaplus社群歡迎廣大技術人員投稿，投稿郵箱：[email protected]
關于我們
dbaplus社群是圍繞Database、BigData、AIOps的企業級專業社群。資深大咖、技術幹貨，每天精品原創文章推送，每周線上技術分享，每月線下技術沙龍，每季度Gdevops&DAMS行業大會。
關注公衆号【dbaplus社群】，擷取更多原創技術文章和精選工具下載下傳