上一篇講的是Mysql存儲結構
https://blog.csdn.net/sbitxmdf/article/details/113766970
這篇記錄對索引存儲結構的了解
目前知道Mysql索引用的資料結構是B+樹,那麼什麼是B+樹,和B樹有什麼差別?
可以看出B樹與B+樹的差別
1、B+樹在葉子節點處都有箭頭相連,但B樹沒有。
2、B-樹的非葉子節點在葉子節點沒有,但B+樹不同,在葉子節點的資料,包含了非葉子節點的所有資料。
為什麼有這樣的差別?這和他們存儲資料的不同有關系。
B樹會在非葉子節點和葉子節點都記錄值和引用的頁指針;B+樹隻會在葉子節點處存儲值和頁指針,并且,資料是有序的,因為它是有序的,是以可以用箭頭将他們連接配接起來,對于查詢範圍内的資料相當有效。
那麼B+樹肯定比B樹更好了?所有的資料結構都有優缺點,直接複制
b樹的優點
B樹的每一個節點都包含key和value。
是以,經常通路的元素可能離根節點更近,是以通路也是更加的迅速
b+樹的優點
1、b+樹的中間節點不儲存資料,可以容納更多的節點元素
2、所有的葉子結點使用連結清單相連,有助于區間查找和周遊
B樹的話,就需要進行每一層的遞歸周遊
相鄰的元素可能在記憶體中不相鄰,是以緩存命中性沒有B+樹好
B+樹介紹完之後,看下索引存儲結構
下面看一下都在用的圖(盜圖)
解釋下這張圖:
存儲兩類資料,一類是索引值,一類是頁實體位址(這裡直接寫行資料是有問題的)
在索引時,應先找到對應值,然後根據頁位址找到資料所在頁,然後在頁中進行搜尋,找到資料所屬的糟,再确定資料行的位置,這部分内容可參考上一篇,mysql存儲結構
這張圖表示的是聚集索引,非聚集索引有些差別,下面說下非聚集索引
先簡單說下聚集和非聚集索引,也叫聚簇和非聚簇索引,聚集索引可以了解為主鍵索引,非聚集索引就是非主鍵索引,就是平時建立的普通索引,也是二級索引。
也是上面這張圖,但存儲内容會有變化,
存儲兩類資料,一類是索引值,一類是主鍵值,通過索引值找到對應的主鍵值,再走上面的聚集索引過程,這個過程也叫回表。
下面引用下聯合索引存儲結構,轉載
聯合索引
所謂聯合索引,也稱多列所謂,就是建立在多個字段上的索引,這個概念是跟單列索引相對的。聯合索引依然是B+樹,但聯合索引的健值數量不是一個,而是多個。建構一顆B+樹隻能根據一個值來建構,是以資料庫依據聯合索引最左的字段來建構B+樹。
例如在a和b字段上建立聯合索引,索引結構将如下圖所示:
一目了然,當我們再執行
SELECT score FROM student WHERE name='葉良辰';
時,可以直接通過掃描非聚集索引直接擷取score的值,而不再需要到聚集索引上二次掃描了。
最左字首比對
聯合索引中有一個重要的課題,就是最左字首比對。
最左字首比對原則:在MySQL建立聯合索引時會遵守最左字首比對原則,即最左優先,在檢索資料時從聯合索引的最左邊開始比對。
這是為什麼呢?我們再仔細觀察索引結構,可以看到索引key在排序上,首先按a排序,a相等的節點中,再按b排序。是以,如果查詢條件是a或a和b聯查時,是可以應用到索引的。如果查詢條件是單獨使用b,因為無法确定a的值,是以無法使用索引。
假如在table表的a,b,c三個列上建立聯合索引,簡要分類分析下聯合索引的最左字首比對。
首先看等值查詢:
1、全值比對查詢時(where子句搜尋條件順序調換不影響索引使用,因為查詢優化器會自動優化查詢順序 ),可以用到聯合索引
SELECT * FROM table WHERE a=1 AND b=3 AND c=2
SELECT * FROM table WHERE b=3 AND c=4 AND a=2
- 1
- 2
2、比對左邊的列時,可以用到聯合索引
SELECT * FROM table WHERE a=1
SELECT * FROM table WHERE a=1 AND b=3
- 1
- 2
3、未從最左列開始時,無法用到聯合索引
SELECT * FROM table WHERE b=1 AND b=3
- 1
4、查詢列不連續時,無法使用聯合索引(會用到a列索引,但c排序依賴于b,是以會先通過a列的索引篩選出a=1的記錄,再在這些記錄中周遊篩選c=3的值,是一種不完全使用索引的情況)
SELECT * FROM table WHERE a=1 AND c=3
- 1
再看範圍查詢:
1、範圍查詢最左列,可以使用聯合索引
SELECT * FROM table WHERE a>1 AND a<5;
- 1
2、精确比對最左列并範圍比對其右一列(a值确定時,b是有序的,是以可以使用聯合索引)
SELECT * FROM table WHERE a=1 AND b>3;
- 1
3、精确比對最左列并範圍比對非右一列(a值确定時,c排序依賴b,是以無法使用聯合索引,但會使用a列索引篩選出a>2的記錄行,再在這些行中條件 c >3逐條過濾)
SELECT * FROM table WHERE a>2 AND c>5;