您好,登錄后才能下訂單哦!
本篇內容主要講解“MySQL索引的使用原則”,感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷,實用性強。下面就讓小編來帶大家學習“MySQL索引的使用原則”吧!
注:上面提到的B樹索引并沒有指出是B-Tree和B+Tree索引,但是B-樹和B+樹的定義是有區別的。
在 MySQL 中,主要有四種類型的索引,分別為: B-Tree 索引, Hash 索引, Fulltext 索引和 R-Tree 索引。
B-Tree 索引是 MySQL 數據庫中使用最為頻繁的索引類型,除了 Archive 存儲引擎之外的其他所有的存儲引擎都支持 B-Tree 索引。Archive 引擎直到 MySQL 5.1 才支持索引,而且只支持索引單個 AUTO_INCREMENT 列。
不僅僅在 MySQL 中是如此,實際上在其他的很多數據庫管理系統中B-Tree 索引也同樣是作為最主要的索引類型,這主要是因為 B-Tree 索引的存儲結構在數據庫的數據檢索中有非常優異的表現。
一般來說, MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree 的結構來存儲的,也就是所有實際需要的數據都存放于 Tree 的 Leaf Node(葉子節點) ,而且到任何一個 Leaf Node 的最短路徑的長度都是完全相同的,所以我們大家都稱之為 B-Tree 索引。
當然,可能各種數據庫(或 MySQL 的各種存儲引擎)在存放自己的 B-Tree 索引的時候會對存儲結構稍作改造。
如 Innodb 存儲引擎的 B-Tree 索引實際使用的存儲結構實際上是 B+Tree,也就是在 B-Tree 數據結構的基礎上做了很小的改造,在每一個Leaf Node 上面出了存放索引鍵的相關信息之外,還存儲了指向與該 Leaf Node 相鄰的后一個 LeafNode 的指針信息(增加了順序訪問指針),這主要是為了加快檢索多個相鄰 Leaf Node 的效率考慮。
InnoDB是Mysql的默認存儲引擎(Mysql5.5.5之前是MyISAM)
接下來我們先看看B-樹、B+樹的概念。弄清楚,為什么加了索引查詢速度會加快?
B樹
即二叉搜索樹:
1、所有非葉子結點至多擁有兩個兒子(Left和Right);
2、所有結點存儲一個關鍵字;
3、非葉子結點的左指針指向小于其關鍵字的子樹,右指針指向大于其關鍵字的子樹;
如:
B-樹
是一種多路搜索樹(并不是二叉的):
1、定義任意非葉子結點最多只有M個兒子;且M>2;
2、根結點的兒子數為[2, M];
3、除根結點以外的非葉子結點的兒子數為[M/2, M];
4、每個結點存放至少M/2-1(取上整)和至多M-1個關鍵字;(至少2個關鍵字)
5、非葉子結點的關鍵字個數=指向兒子的指針個數-1;
6、非葉子結點的關鍵字:K[1], K[2], …, K[M-1];且K[i] < K[i+1];
7、非葉子結點的指針:P[1], P[2], …, P[M];其中P[1]指向關鍵字小于K[1]的子樹,P[M]指向關鍵字大于K[M-1]的子樹,其它P[i]指向關鍵字屬于(K[i-1], K[i])的子樹;
8、所有葉子結點位于同一層;
如:(M=3)
B-樹的搜索,從根結點開始,對結點內的關鍵字(有序)序列進行二分查找,如果命中則結束,否則進入查詢關鍵字所屬范圍的兒子結點;重復,直到所對應的兒子指針為空,或已經是葉子結點;
B-樹的特性:
1、關鍵字集合分布在整顆樹中;
2、任何一個關鍵字出現且只出現在一個結點中;
3、搜索有可能在非葉子結點結束;
4、其搜索性能等價于在關鍵字全集內做一次二分查找;
5、自動層次控制;
由于限制了除根結點以外的非葉子結點,至少含有M/2個兒子,確保了結點的至少利用率。
所以B-樹的性能總是等價于二分查找(與M值無關),也就沒有B樹平衡的問題;
由于M/2的限制,在插入結點時,如果結點已滿,需要將結點分裂為兩個各占M/2的結點;刪除結點時,需將兩個不足M/2的兄弟結點合并;
B+樹
B+樹是B-樹的變體,也是一種多路搜索樹:
1、其定義基本與B-樹同,除了:
2、非葉子結點的子樹指針與關鍵字個數相同;
3、非葉子結點的子樹指針P[i],指向關鍵字值屬于[K[i], K[i+1])的子樹(B-樹是開區間);
5、為所有葉子結點增加一個鏈指針;
6、所有關鍵字都在葉子結點出現;
如:(M=3)
B+的搜索與B-樹也基本相同,區別是B+樹只有達到葉子結點才命中(B-樹可以在
非葉子結點命中),其性能也等價于在關鍵字全集做一次二分查找;
B+的特性:
1、所有關鍵字都出現在葉子結點的鏈表中(稠密索引),且鏈表中的關鍵字恰好是有序的;
2、不可能在非葉子結點命中;
3、非葉子結點相當于是葉子結點的索引(稀疏索引),葉子結點相當于是存儲(關鍵字)數據的數據層;
4、更適合文件索引系統;
了解B-/B+樹的概念之后,我們繼續分析B+樹提高效率的原理。
如上圖,是一顆b+樹,關于b+樹的定義可以參見B+樹,這里只說一些重點,淺藍色的塊我們稱之為一個磁盤塊,可以看到每個磁盤塊包含幾個數據項(深藍色所示)和指針(黃色所示),如磁盤塊1包含數據項17和35,包含指針P1、P2、P3,P1表示小于17的磁盤塊,P2表示在17和35之間的磁盤塊,P3表示大于35的磁盤塊。真實的數據存在于葉子節點即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非葉子節點只不存儲真實的數據,只存儲指引搜索方向的數據項,如17、35并不真實存在于數據表中。
b+樹的查找過程
如圖所示,如果要查找數據項29,那么首先會把磁盤塊1由磁盤加載到內存,此時發生一次IO,在內存中用二分查找確定29在17和35之間,鎖定磁盤塊1的P2指針,內存時間因為非常短(相比磁盤的IO)可以忽略不計,通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內存,發生第二次IO,29在26和30之間,鎖定磁盤塊3的P2指針,通過指針加載磁盤塊8到內存,發生第三次IO,同時內存中做二分查找找到29,結束查詢,總計三次IO。真實的情況是,3層的b+樹可以表示上百萬的數據,如果上百萬的數據查找只需要三次IO,性能提高將是巨大的,如果沒有索引,每個數據項都要發生一次IO,那么總共需要百萬次的IO,顯然成本非常非常高。
b+樹性質
1、通過上面的分析,我們知道IO次數取決于b+數的高度h,假設當前數據表的數據為N,每個磁盤塊的數據項的數量是m,則有h=㏒(m+1)N,當數據量N一定的情況下,m越大,h越小;而m = 磁盤塊的大小 / 數據項的大小,磁盤塊的大小也就是一個數據頁的大小,是固定的,如果數據項占的空間越小,數據項的數量越多,樹的高度越低。這就是為什么每個數據項,即索引字段要盡量的小,比如int占4字節,要比bigint8字節少一半。這也是為什么b+樹要求把真實的數據放到葉子節點而不是內層節點,一旦放到內層節點,磁盤塊的數據項會大幅度下降,導致樹增高。當數據項等于1時將會退化成線性表。
2、當b+樹的數據項是復合的數據結構,比如(name,age,sex)的時候,b+數是按照從左到右的順序來建立搜索樹的,比如當(張三,20,F)這樣的數據來檢索的時候,b+樹會優先比較name來確定下一步的所搜方向,如果name相同再依次比較age和sex,最后得到檢索的數據;但當(20,F)這樣的沒有name的數據來的時候,b+樹就不知道下一步該查哪個節點,因為建立搜索樹的時候name就是第一個比較因子,必須要先根據name來搜索才能知道下一步去哪里查詢。比如當(張三,F)這樣的數據來檢索時,b+樹可以用name來指定搜索方向,但下一個字段age的缺失,所以只能把名字等于張三的數據都找到,然后再匹配性別是F的數據了, 這個是非常重要的性質,即索引的最左匹配特性。
慢查詢優化
關于MySQL索引原理是比較枯燥的東西,大家只需要有一個感性的認識,并不需要理解得非常透徹和深入。我們回頭來看看一開始我們說的慢查詢,了解完索引原理之后,大家是不是有什么想法呢?先總結一下索引的幾大基本原則
四、建索引的幾大原則
1、最左前綴匹配原則,非常重要的原則,mysql會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配,比如a 1="" and="" b="2" c=""> 3 and d = 4 如果建立(a,b,c,d)順序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引則都可以用到,a,b,d的順序可以任意調整。
2、=和in可以亂序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序,mysql的查詢優化器會幫你優化成索引可以識別的形式
3、盡量選擇區分度高的列作為索引,區分度的公式是count(distinct col)/count(*),表示字段不重復的比例,比例越大我們掃描的記錄數越少,唯一鍵的區分度是1,而一些狀態、性別字段可能在大數據面前區分度就是0,那可能有人會問,這個比例有什么經驗值嗎?使用場景不同,這個值也很難確定,一般需要join的字段我們都要求是0.1以上,即平均1條掃描10條記錄
4、索引列不能參與計算,保持列“干凈”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很簡單,b+樹中存的都是數據表中的字段值,但進行檢索時,需要把所有元素都應用函數才能比較,顯然成本太大。所以語句應該寫成create_time = unix_timestamp(’2014-05-29’);
5、盡量的擴展索引,不要新建索引。比如表中已經有a的索引,現在要加(a,b)的索引,那么只需要修改原來的索引即可
到此,相信大家對“MySQL索引的使用原則”有了更深的了解,不妨來實際操作一番吧!這里是億速云網站,更多相關內容可以進入相關頻道進行查詢,關注我們,繼續學習!
免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。