以下是結合網上及此前面試時遇到的一些關於mysql索引的面試題。
若對mysql索引不太瞭解可先翻閱相關文章
什麼是索引？
索引類似書本的目錄，查詢書中的指定內容時，先在目錄上查詢，之後可快速定位到內容位置。在資料庫中通常通過B樹/B+樹資料結構實現。
主鍵索引和非主鍵索引有什麼區別？
主鍵索引樹中葉子節點儲存的是整行資料，而非主鍵索引葉子節點上儲存的是主鍵的值。使用非主鍵索引時，先從非主鍵索引獲取到行對應主鍵ID，之後再根據id在主鍵索引樹上搜尋對應行資料，這個過程也被稱為回表。
一般使用什麼欄位作為主鍵，為什麼？
一般使用innodb的自增整數型別作為主鍵：
- 因為自增，容易保證主鍵索引的有序性，同時還能避免新資料中間位置插入時導致的頁分裂；
- 二級索引葉子節點上儲存的是主鍵值，整數型別主鍵長度較小，二級索引樹佔用的空間較小。
索引使用場景
where
為查詢條件欄位建立索引，以達到快速過濾指定條件資料的目的。
order by
當使用order by將查詢結果按某個欄位排序時，可考慮為該欄位建立索引。沒有索引時，會先將查詢結果放到記憶體中進行排序（若記憶體空間不足，會利用磁碟輔助排序），比較影響查詢效率。
索引本身是有序的，可以直接按索引的順序逐條回表取出資料即可。如果是分頁查詢，效果更好，這時候只需要取出某個範圍的索引對應的資料，而不需要取出所有滿足條件的資料排序後再擷取返回分頁資料。
join
使用join時，為被驅動表的關聯欄位建立索引，可以有效提高查詢效率。比如select * from t1 straight_join t2 on (t1.a=t2.a) where t1.b = 'xxxx'; t2的欄位a上有索引，查詢過程會是先從表1中依次取出滿足條件的行資料，之後用行資料中的a欄位去t2上匹配後將兩表欄位拼接返回，此時能使用到t2.a的索引，避免了t2全表掃描。
索引覆蓋
如果select欄位+where欄位欄位列數不太多且查詢頻繁時，可以考慮為select和where欄位建立聯合索引，避免查詢時回表，提高查詢效率。比如select a from t where b = ‘xx’, 建立聯合索引(b, a)， 此時掃描索引樹後，就已經得到需要查詢的欄位a了，不需要再回表。需要注意的是聯合索引欄位的順序，這個語句無法使用到索引(a, b)。
建立索引需要注意的地方
-
最左字首匹配原則，聯合索引需要注意索引欄位的順序，mysql 會一直向右匹配直到遇到範圍查詢 (>、<、between、like) 就停止匹配，比如
a = 1 and b = 2 and c > 3 and d = 4,如果建立 (a,b,c,d) 順序的索引，d 是用不到索引的。
欄位是否用到索引的意思是欄位是否能利用欄位在索引中的有序性進行快速過濾。索引(a,b,c,d), 在索引樹上是先按a進行排序，再按b進行排序，以此類推，排序規則類似order by a,b,c,d。上面查詢條件中，a定值，b是有序的；b定值，c是有序的；c範圍查詢，剩下的d是無序的。所以d無法使用到該索引。 -
基數小，區分度低的不適合建立索引。比如性別，最多基數最多總共就3個，此時索引過濾效能不高，查完索引後還需回表，可能比直接全表掃描效率更低。
-
更新頻繁的欄位建立索引時要權衡索引維護成本。
-
儘量擴充套件索引，比如已經有a索引，現在要加 (a,b) 的索引，那麼只需要修改原來的索引即可。
-
避免對text大欄位建立索引，會導致索引樹太大，查詢效率不高。如果大欄位前n個字元區分度較高，可以考慮建立字首索引，只索引開始的部分字元，這樣可以節約索引空間，提高索引效率。其缺點是不能用於ORDER BY和GROUP BY操作，也不能用於覆蓋索引(因為字首索引樹上只有欄位的部分內容，需要進行回表)。
什麼時候索引會失效？
-
模糊查詢時查詢條件以”%”開頭無法使用到索引
-
使用or查詢時，只有當所有的查詢條件欄位都有索引才能使用到，比如
a=1 or b = 2,只有當a和b都有索引才能使用到索引。 -
資料型別出現隱式轉換，如varchar不加單引號的時候可能會自動轉換為int型別，這個時候索引失效。
-
在索引列上使用IS NULL或者 IS NOT NULL 時候，索引失效，因為索引不會索引空值。
-
在索引欄位上使用”NOT、 <>、!=、NOT IN “時是不會使用索引的，這時只會進行全表掃描。
-
對索引欄位進行計算操作，函式操作時不會使用索引。
-
當優化器覺得全表掃描速度比索引速度快的時候不會使用索引。一般出現在全表資料比較少的情況下，這時全表掃描比在非主鍵索引上查詢後再回錶速度可能更快。
-
聯合索引時，查詢不滿足最左匹配規則，無法使用到聯合索引。
innodb使用b+樹作為索引模型的原因
Mysql設計的使用場景比較廣泛，需要對遍歷查詢、單條查詢、資料更新都需要較好的效能支援。B+樹的特性是隻在葉子節點上儲存資料。可以從資料讀寫方面與雜湊表、有序陣列、b樹其他幾種索引模型進行比較：
- 雜湊表：雜湊表只能進行等值查詢，在處理範圍查詢和排序查詢時，需要全表掃描雜湊表。
- 有序陣列：有序陣列在進行資料更新時成本較大。往陣列中間位置新增資料時，需要移動後面的資料位置。
- B樹：b樹在非葉子節點上也儲存資料，在遍歷資料時，需要對不同層級的節點上的資料進行拼接和排序，這會導致多次磁碟io。查詢效率較低。
如何刪除百萬級別或以上的資料？
可以考慮先刪掉表的索引，等刪除資料後再重建索引。當我們在進行資料修改時，需要同時修改索引，這些額外的索引維護成本較低資料修改的效率；同時，大量的資料刪除會導致索引資料頁產生大量的碎片空間，此時刪除資料後重建索引可以使索引樹更“緊湊”，提高磁碟空間利用率。
Innodb中的B+樹模型中，N叉樹的N能否被修改？
-
通過調整索引欄位大小來修改
N 叉樹中非葉子節點存放的是索引資訊，索引包含 Key 和 Point 指標。Point 指標固定為 6 個位元組，假如 Key 為 10 個位元組，那麼單個索引就是 16 個位元組。如果 B + 樹中頁大小為 16 K，那麼一個頁就可以儲存 1024 個索引，此時 N 就等於 1024。我們通過改變 Key 的大小，就可以改變 N 的值。 -
通過修改頁大小間接修改，頁越大，每頁存放的索引數量就越多，N就越大。
資料頁調整後，如果資料頁太小層數會太深，資料頁太大，載入到記憶體的時間和單個資料頁查詢時間會提高，需要達到平衡才行。
如何知道語句有沒有走索引查詢？
可以利用 explain 檢視 sql 語句的執行計劃，通過執行計劃來分析索引使用情況。
寫在最後
喜歡本文的朋友，歡迎關注公眾號「會玩 code」，專注大白話分享實用技術。
公眾號福利
回覆【mysql】獲取免費測試資料庫！！
回覆【pdf】獲取持續更新海量學習資料！！