Mysql?InnoDB中B+樹(shù)索引使用注意事項(xiàng)
一、根頁(yè)面萬(wàn)年不動(dòng)
在之前的文章里，為了方便理解，都是先畫(huà)存儲(chǔ)用戶記錄的葉子節(jié)點(diǎn)，然后再畫(huà)出存儲(chǔ)目錄項(xiàng)記錄的內(nèi)節(jié)點(diǎn)。
但實(shí)際上 B+ 樹(shù)的行成過(guò)程是這樣的：
- 每當(dāng)為某個(gè)表創(chuàng)建一個(gè) B+ 樹(shù)索引，都會(huì)為這個(gè)索引創(chuàng)建一個(gè)根節(jié)點(diǎn)頁(yè)面。最開(kāi)始表里沒(méi)數(shù)據(jù)，所以根節(jié)點(diǎn)中既沒(méi)有用戶記錄，也沒(méi)有目錄項(xiàng)記錄。
- 當(dāng)往表里插入用戶記錄時(shí)，先把用戶記錄存儲(chǔ)到這個(gè)根節(jié)點(diǎn)上。
- 當(dāng)根節(jié)點(diǎn)頁(yè)空間用完，繼續(xù)插入記錄，此時(shí)會(huì)將根節(jié)點(diǎn)中所有記錄復(fù)制到一個(gè)新頁(yè)(比如頁(yè) a)，然后對(duì)這個(gè)新頁(yè)進(jìn)行頁(yè)分裂，得到另一個(gè)新頁(yè)(頁(yè) b)。這時(shí)候新插入的記錄就根據(jù)鍵值大小分配到頁(yè) a 和 頁(yè) b 中。于是，根節(jié)點(diǎn)頁(yè)就升級(jí)成了存儲(chǔ)目錄項(xiàng)記錄的頁(yè)，就需要把頁(yè)a 和 頁(yè)b 對(duì)應(yīng)的目錄項(xiàng)記錄插入到根節(jié)點(diǎn)中。
另外，當(dāng)一個(gè)B+樹(shù)索引的根節(jié)點(diǎn)創(chuàng)建后，它的頁(yè)號(hào)就不會(huì)再變。
所以只要我們對(duì)某個(gè)表建立一個(gè)索引，那么它的根節(jié)點(diǎn)的頁(yè)號(hào)就會(huì)被記錄到某個(gè)地方，后續(xù)只要 innodb引擎需要用這個(gè)索引，就會(huì)從那個(gè)固定的地方取出根節(jié)點(diǎn)的頁(yè)號(hào)，從而訪問(wèn)這個(gè)索引。
二、內(nèi)節(jié)點(diǎn)中目錄項(xiàng)記錄的唯一性
在B+樹(shù)索引的內(nèi)節(jié)點(diǎn)中，目錄項(xiàng)記錄的內(nèi)容是索引列+頁(yè)號(hào)。但是對(duì)于二級(jí)索引來(lái)說(shuō)，不太嚴(yán)謹(jǐn)。
因?yàn)槎?jí)索引的索引列可能存在相同的值，比如某張表里有這4條記錄，其中c1列是主鍵 ：
現(xiàn)在為c2列建立索引：
如果這時(shí)候繼續(xù)插入一條記錄，3個(gè)列分別為9、1、'c'，就會(huì)遇到問(wèn)題：
新記錄中 c2的值也是1，那么這個(gè)新記錄到底應(yīng)該放在頁(yè) 4，還是放到頁(yè) 5？
所以，為了能讓新插入的記錄可以找到自己應(yīng)該到哪個(gè)頁(yè)中，就需要保證B+樹(shù)同一層內(nèi)節(jié)點(diǎn)的目錄項(xiàng)記錄是唯一的。
那么，實(shí)際上二級(jí)索引的內(nèi)節(jié)點(diǎn)的目錄項(xiàng)記錄應(yīng)該由 3 個(gè)部分組成：
- 索引列的值
- 主鍵值
- 頁(yè)號(hào)
所以實(shí)際上給c2建立的索引應(yīng)該是這樣：
現(xiàn)在，當(dāng)插入新記錄9、1、'c'時(shí)：
- 可以先把新記錄的 c2 列的值和頁(yè) 3 中各目錄項(xiàng)記錄的 c2 列的值進(jìn)行比較。
- 如果 c2 列的值相同，就接著比較主鍵值。
所以，對(duì)于二級(jí)索引來(lái)說(shuō)，給 c2 列建索引，其實(shí)就相當(dāng)于用c2、c1建立了一個(gè)聯(lián)合索引。先按照二級(jí)索引的值進(jìn)行排序，在二級(jí)索引列值相同的情況下，再按照主鍵值進(jìn)行排序。
三、一個(gè)頁(yè)面至少容納 2 條記錄
在之前的文章里提到過(guò)，B+ 樹(shù)其實(shí)只需要很少的層級(jí)就可以輕松存儲(chǔ)數(shù)億條記錄，查詢速度還很快。
這是因?yàn)?B+ 樹(shù)本質(zhì)上就是一個(gè)大的多層級(jí)目錄。每經(jīng)過(guò)一個(gè)目錄時(shí)都會(huì)過(guò)濾許多無(wú)效的子目錄，直到最后訪問(wèn)到存儲(chǔ)真正數(shù)據(jù)的目錄。
那么現(xiàn)在不妨設(shè)想一下：還是同樣的數(shù)據(jù)量，如果一個(gè)大的目錄只存放一個(gè)子目錄，又是什么樣子？
- 目錄層級(jí)非常多
- 最后那個(gè)存放真正數(shù)據(jù)的目錄中只能存放一條記錄
如果是這樣的話，這種B+ 樹(shù)結(jié)構(gòu)就沒(méi)什么意義了，不能形成一個(gè)有效的索引。
于是，設(shè)計(jì) innoDB的大佬為了避免 B+樹(shù)的層級(jí)增長(zhǎng)得過(guò)高，要求所有數(shù)據(jù)頁(yè)都至少可以存放2條記錄。
本文參考書(shū)籍：《mysql是怎樣運(yùn)行的》
以上就是Mysql InnoDB中B+樹(shù)索引的注意事項(xiàng)的詳細(xì)內(nèi)容，更多關(guān)于Mysql InnoDB中B+樹(shù)索引的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
mysql查詢昨天 一周前 一月前 一年前的數(shù)據(jù)
這篇文章主要介紹了mysql查詢昨天 一周前 一月前 一年前的數(shù)據(jù)的方法,需要的朋友可以參考下2014-05-05
PHP連接MySQL主要是使用Mysql提供的 libmysqlclient 的客戶端庫(kù)，同時(shí)也延伸出來(lái) mysql 和 mysqli 兩套PHP的擴(kuò)展，相對(duì)來(lái)說(shuō) mysqli 比 mysql 更好，更穩(wěn)定。2011-05-05
mysql 詳解隔離級(jí)別操作過(guò)程(cmd)
這篇文章主要介紹了mysql 詳解隔離級(jí)別操作過(guò)程(cmd)的相關(guān)資料,需要的朋友可以參考下2017-01-01
詳解如何利用amoeba(變形蟲(chóng))實(shí)現(xiàn)mysql數(shù)據(jù)庫(kù)讀寫(xiě)分離
這篇文章主要介紹了詳解如何利用amoeba(變形蟲(chóng))實(shí)現(xiàn)mysql數(shù)據(jù)庫(kù)讀寫(xiě)分離,文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-05-05
Mysql數(shù)據(jù)庫(kù)的增刪改查、備份、還原等基本操作大全
這篇文章主要介紹了Mysql數(shù)據(jù)庫(kù)的增刪改查、備份、還原等基本操作,本文通過(guò)示例代碼給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2023-06-06
MYSQL滿足條件函數(shù)里放查詢最大函數(shù)的方法(最新推薦)
在MySQL中,如果我們想要在一個(gè)條件函數(shù)如CASE內(nèi)部使用聚合函數(shù)如MAX獲取某個(gè)字段的最大值,我們通常需要在外部查詢或子查詢中執(zhí)行這個(gè)聚合操作,并將結(jié)果作為參數(shù)傳遞給條件函數(shù),下面通過(guò)實(shí)例代碼講解MYSQL滿足條件函數(shù)里放查詢最大函數(shù)的方法,感興趣的朋友一起看看吧2024-05-05
MySQL復(fù)制架構(gòu)的搭建及配置過(guò)程
這篇文章主要介紹了MySQL復(fù)制架構(gòu)的相關(guān)知識(shí)，本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2022-02-02
Mysql數(shù)據(jù)庫(kù)錯(cuò)誤代碼中文詳細(xì)說(shuō)明
在mysql開(kāi)發(fā)中出現(xiàn)錯(cuò)誤代碼各種各樣，下面我來(lái)給大家收集常用見(jiàn)的mysql使用過(guò)程中出錯(cuò)代碼的中文說(shuō)明，希望些文章對(duì)各位朋友有所幫助了2013-08-08
深入解析MySQL的事務(wù)隔離及其對(duì)性能產(chǎn)生的影響
這篇文章主要介紹了MySQL的事務(wù)隔離及其對(duì)性能產(chǎn)生的影響,在MySQL的優(yōu)化方面具有一定的借鑒意義,需要的朋友可以參考下2015-12-12
MySQL中使用CTE獲取時(shí)間段數(shù)據(jù)的技巧分享
在數(shù)據(jù)庫(kù)操作中,獲取特定時(shí)間段的數(shù)據(jù)是一項(xiàng)常見(jiàn)任務(wù),MySQL自從8.0版本開(kāi)始支持CTE（公共表表達(dá)式）,使得我們可以更加靈活和高效地處理時(shí)間段數(shù)據(jù),本文小編介紹了MySQL中使用CTE獲取時(shí)間段數(shù)據(jù)的技巧分享,需要的朋友可以參考下2024-08-08