這篇文章將為大家詳細講解有關MySQL 索引B+樹原理以及建索引的幾大原則是什么，文章內容質量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。
成都創(chuàng)新互聯(lián)公司長期為1000+客戶提供的網站建設服務，團隊從業(yè)經驗10年，關注不同地域、不同群體，并針對不同對象提供差異化的產品和服務；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網生態(tài)環(huán)境。為克東企業(yè)提供專業(yè)的網站建設、成都網站制作，克東網站改版等技術服務。擁有十余年豐富建站經驗和眾多成功案例,為您定制開發(fā)。MySQL事實上使用不同的存儲引擎也是有很大區(qū)別的，下面猿友們可以了解一下。
注：上面提到的B樹索引并沒有指出是B-Tree和B+Tree索引，但是B-樹和B+樹的定義是有區(qū)別的。
在 MySQL 中，主要有四種類型的索引，分別為：B-Tree 索引， Hash 索引， Fulltext 索引和 R-Tree 索引。
B-Tree 索引是 MySQL 數據庫中使用最為頻繁的索引類型，除了 Archive 存儲引擎之外的其他所有的存儲引擎都支持 B-Tree 索引。Archive 引擎直到 MySQL 5.1 才支持索引，而且只支持索引單個 AUTO_INCREMENT 列。
不僅僅在 MySQL 中是如此，實際上在其他的很多數據庫管理系統(tǒng)中B-Tree 索引也同樣是作為最主要的索引類型，這主要是因為 B-Tree 索引的存儲結構在數據庫的數據檢索中有非常優(yōu)異的表現(xiàn)。
一般來說， MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree 的結構來存儲的，也就是所有實際需要的數據都存放于 Tree 的 Leaf Node(葉子節(jié)點) ，而且到任何一個 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+樹的概念。弄清楚，為什么加了索引查詢速度會加快？
即二叉搜索樹：
所有非葉子結點至多擁有兩個兒子（Left和Right）；
所有結點存儲一個關鍵字；
非葉子結點的左指針指向小于其關鍵字的子樹，右指針指向大于其關鍵字的子樹；
如：
是一種多路搜索樹（并不是二叉的）：
定義任意非葉子結點最多只有M個兒子；且M>2；
根結點的兒子數為[2, M]；
除根結點以外的非葉子結點的兒子數為[M/2, M]；
每個結點存放至少M/2-1（取上整）和至多M-1個關鍵字；（至少2個關鍵字）
非葉子結點的關鍵字個數=指向兒子的指針個數-1；
非葉子結點的關鍵字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
非葉子結點的指針：P[1], P[2], …, P[M]；其中P[1]指向關鍵字小于K[1]的子樹，P[M]指向關鍵字大于K[M-1]的子樹，其它P[i]指向關鍵字屬于(K[i-1], K[i])的子樹；
所有葉子結點位于同一層；
如：
B-樹的搜索，從根結點開始，對結點內的關鍵字（有序）序列進行二分查找，如果命中則結束，否則進入查詢關鍵字所屬范圍的兒子結點；重復，直到所對應的兒子指針為空，或已經是葉子結點；
B-樹的特性：
關鍵字集合分布在整顆樹中；
任何一個關鍵字出現(xiàn)且只出現(xiàn)在一個結點中；
搜索有可能在非葉子結點結束；
其搜索性能等價于在關鍵字全集內做一次二分查找；
自動層次控制；
由于限制了除根結點以外的非葉子結點，至少含有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-樹是開區(qū)間）；
5、為所有葉子結點增加一個鏈指針；
6、所有關鍵字都在葉子結點出現(xiàn)；
如：
B+的搜索與B-樹也基本相同，區(qū)別是B+樹只有達到葉子結點才命中（B-樹可以在
非葉子結點命中），其性能也等價于在關鍵字全集做一次二分查找；
B+的特性：
所有關鍵字都出現(xiàn)在葉子結點的鏈表中（稠密索引），且鏈表中的關鍵字恰好是有序的；
不可能在非葉子結點命中；
非葉子結點相當于是葉子結點的索引（稀疏索引），葉子結點相當于是存儲（關鍵字）數據的數據層；
更適合文件索引系統(tǒng)；
了解B-/B+樹的概念之后，我們繼續(xù)分析B+樹提高效率的原理。
如上圖，是一顆b+樹，關于b+樹的定義可以參見B+樹，這里只說一些重點，淺藍色的塊我們稱之為一個磁盤塊，可以看到每個磁盤塊包含幾個數據項（深藍色所示）和指針（黃色所示），如磁盤塊1包含數據項17和35，包含指針P1、P2、P3，P1表示小于17的磁盤塊，P2表示在17和35之間的磁盤塊，P3表示大于35的磁盤塊。真實的數據存在于葉子節(jié)點即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非葉子節(jié)點只不存儲真實的數據，只存儲指引搜索方向的數據項，如17、35并不真實存在于數據表中。
如圖所示，如果要查找數據項29，那么首先會把磁盤塊1由磁盤加載到內存，此時發(fā)生一次IO，在內存中用二分查找確定29在17和35之間，鎖定磁盤塊1的P2指針，內存時間因為非常短（相比磁盤的IO）可以忽略不計，通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內存，發(fā)生第二次IO，29在26和30之間，鎖定磁盤塊3的P2指針，通過指針加載磁盤塊8到內存，發(fā)生第三次IO，同時內存中做二分查找找到29，結束查詢，總計三次IO。真實的情況是，3層的b+樹可以表示上百萬的數據，如果上百萬的數據查找只需要三次IO，性能提高將是巨大的，如果沒有索引，每個數據項都要發(fā)生一次IO，那么總共需要百萬次的IO，顯然成本非常非常高。
1、通過上面的分析，我們知道IO次數取決于b+數的高度h，假設當前數據表的數據為N，每個磁盤塊的數據項的數量是m，則有h=㏒(m+1)N，當數據量N一定的情況下，m越大，h越小；而m = 磁盤塊的大小 / 數據項的大小，磁盤塊的大小也就是一個數據頁的大小，是固定的，如果數據項占的空間越小，數據項的數量越多，樹的高度越低。這就是為什么每個數據項，即索引字段要盡量的小，比如int占4字節(jié)，要比bigint8字節(jié)少一半。這也是為什么b+樹要求把真實的數據放到葉子節(jié)點而不是內層節(jié)點，一旦放到內層節(jié)點，磁盤塊的數據項會大幅度下降，導致樹增高。當數據項等于1時將會退化成線性表。
2、當b+樹的數據項是復合的數據結構，比如(name,age,sex)的時候，b+數是按照從左到右的順序來建立搜索樹的，比如當(張三,20,F)這樣的數據來檢索的時候，b+樹會優(yōu)先比較name來確定下一步的所搜方向，如果name相同再依次比較age和sex，最后得到檢索的數據；但當(20,F)這樣的沒有name的數據來的時候，b+樹就不知道下一步該查哪個節(jié)點，因為建立搜索樹的時候name就是第一個比較因子，必須要先根據name來搜索才能知道下一步去哪里查詢。比如當(張三,F)這樣的數據來檢索時，b+樹可以用name來指定搜索方向，但下一個字段age的缺失，所以只能把名字等于張三的數據都找到，然后再匹配性別是F的數據了， 這個是非常重要的性質，即索引的最左匹配特性。
關于MySQL索引原理是比較枯燥的東西，大家只需要有一個感性的認識，并不需要理解得非常透徹和深入。回頭來看看一開始我們說的慢查詢，了解完索引原理之后，大家是不是有什么想法呢？先總結一下索引的幾大基本原則。
1、最左前綴匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and 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的查詢優(yōu)化器會幫你優(yōu)化成索引可以識別的形式
3、盡量選擇區(qū)分度高的列作為索引,區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復的比例，比例越大我們掃描的記錄數越少，唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數據面前區(qū)分度就是0，那可能有人會問，這個比例有什么經驗值嗎？使用場景不同，這個值也很難確定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄
4、索引列不能參與計算，保持列“干凈”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是數據表中的字段值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(’2014-05-29’);
5、盡量的擴展索引，不要新建索引。比如表中已經有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可。
關于MySQL 索引B+樹原理以及建索引的幾大原則是什么就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。
另外有需要云服務器可以了解下創(chuàng)新互聯(lián)scvps.cn，海內外云服務器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務器、裸金屬服務器、高防服務器、香港服務器、美國服務器、虛擬主機、免備案服務器”等云主機租用服務以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務可用性高、性價比高”等特點與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應用場景需求。
重慶分公司
重慶分公司
