關于mysql索引的一些疑問和解答
部分内容參考了 MySQL索引-B+樹（看完你就明白了）
文章目錄
-
-
- 疑問和解答
- 索引失效場景
- 不适合用索引的場景
- 利用聚集索引查找資料的過程
-
疑問和解答
- 每建立一個索引都會生成一棵B+樹嗎? 是的，是以索引不是越多越好，因為生成和維護b+樹也是有開銷的
- 聚集索引，（也叫聚簇索引）資料行的實體順序與列值（一般是主鍵的那一列）的邏輯順序相同，一個表中隻能擁有一個聚集索引。如果存在主鍵，主鍵就是聚集索引。若無主鍵，mysql會自行維護一個隐藏列作為主鍵索引
-
非聚集索引，（也叫非聚簇索引）（之前了解我錯了，以為myisam 的索引都是非聚集索引，innoDB的都是聚集索引，這是錯的！）
事實上，不是主鍵的索引，就是非聚集索引。二級索引、聯合索引啥的，都是非聚集索引。非聚集索引的特征是，葉子節點放的是索引列值+主鍵id值，除此之外，不會存其他字段值
-
為什麼建議盡量在建表時，就建立好索引？
這要從索引的本質說起，索引是一種有序的資料結構，是B+樹。
如果在表中已有資料的情況下再建立索引，那麼原有的資料行就要被重新排列組織成B+樹，會非常地耗費時間以及性能
-
回表問題的産生和如何解決回表問題
查詢時如果select到主鍵或者索引列值，則不會觸發回表，select到以上以外的字段，就會回表。
解決回表的方式是，生成複合索引（也叫覆寫索引）。
假設現在有個表：有字段id，name，age，其中id是主鍵，name有索引，age無索引。
如果要執行select name, age from t where name = “xx”時，給name和age字段設定成覆寫索引就可以避免回表了
索引失效場景
- 有or必全有索引;
- 複合索引未用左列字段;
- like以%開頭;
- 需要類型轉換，比如字元串類型一定要加“ ”;
- where中索引列有運算;
- where中索引列使用了函數;
- 如果mysql覺得全表掃描更快時（資料少）;
不适合用索引的場景
- 唯一性差，比如性别字段沒必要加;
- 頻繁更新的字段不用（更新索引消耗）;
- where中不用的字段;
- 索引使用<>時，效果一般;
利用聚集索引查找資料的過程
這部分的内容援引自部落格 MySQL索引-B+樹（看完你就明白了）
還是這張 B+ 樹索引圖，現在我們應該知道這就是聚集索引，表中的資料存儲在其中。
現在假設我們要查找 id>=18 并且 id<40 的使用者資料。對應的 sql 語句為：
MySQL
其中 id 為主鍵，具體的查找過程如下：
①一般根節點都是常駐記憶體的，也就是說頁 1 已經在記憶體中了，此時不需要到磁盤中讀取資料，直接從記憶體中讀取即可。
從記憶體中讀取到頁 1，要查找這個 id>=18 and id <40 或者範圍值，我們首先需要找到 id=18 的鍵值。
從頁 1 中我們可以找到鍵值 18，此時我們需要根據指針 p2，定位到頁 3。
②要從頁 3 中查找資料，我們就需要拿着 p2 指針去磁盤中進行讀取頁 3。
從磁盤中讀取頁 3 後将頁 3 放入記憶體中，然後進行查找，我們可以找到鍵值 18，然後再拿到頁 3 中的指針 p1，定位到頁 8。
③同樣的頁 8 頁不在記憶體中，我們需要再去磁盤中将頁 8 讀取到記憶體中。
将頁 8 讀取到記憶體中後。因為頁中的資料是連結清單進行連接配接的，而且鍵值是按照順序存放的，此時可以根據二分查找法定位到鍵值 18。
此時因為已經到資料頁了，此時我們已經找到一條滿足條件的資料了，就是鍵值 18 對應的資料。
因為是範圍查找，而且此時所有的資料又都存在葉子節點，并且是有序排列的，那麼我們就可以對頁 8 中的鍵值依次進行周遊查找并比對滿足條件的資料。
我們可以一直找到鍵值為 22 的資料，然後頁 8 中就沒有資料了，此時我們需要拿着頁 8 中的 p 指針去讀取頁 9 中的資料。
④因為頁 9 不在記憶體中，就又會加載頁 9 到記憶體中，并通過和頁 8 中一樣的方式進行資料的查找，直到将頁 12 加載到記憶體中，發現 41 大于 40，此時不滿足條件。那麼查找到此終止。
最終我們找到滿足條件的所有資料，總共 12 條記錄：
(18,kl), (19,kl), (22,hj), (24,io), (25,vg) , (29,jk), (31,jk) , (33,rt) , (34,ty) , (35,yu) , (37,rt) , (39,rt) 。
下面看下具體的查找流程圖
