獲取 MySQL innodb B+tree 的高度的方法
前言
MySQL 的 innodb 引擎之所以使用 B+tree 來存儲索引,就是想盡量減少數(shù)據(jù)查詢時磁盤 IO 次數(shù)。樹的高度直接影響了查詢的性能。一般樹的高度在 3~4 層較為適宜。數(shù)據(jù)庫分表的目的也是為了控制樹的高度。那么如何獲取樹的高度呢?下面使用一個示例來說明如何獲取樹的高度。
示例數(shù)據(jù)準(zhǔn)備
建表語句如下:
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) CHARACTER SET latin1 DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name`), KEY `age` (`age`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
表中插入100萬條數(shù)據(jù)。數(shù)據(jù)如下:
mysql> select * from user limit 2\G *************************** 1. row *************************** id: 110000 name: ab age: 100 *************************** 2. row *************************** id: 110001 name: ab age: 100 2 rows in set (0.00 sec)
通過查詢相關(guān)數(shù)據(jù)表獲取樹的高度
以 MySQL5.6 版本為例說明如何獲取樹的高度。
首先獲取 page_no
mysql> SELECT b.name, a.name, index_id, type, a.space, a.PAGE_NO FROM information_schema.INNODB_SYS_INDEXES a, information_schema.INNODB_SYS_TABLES b WHERE a.table_id = b.table_id AND a.space <> 0 and b.name='test/user'; +-----------+---------+----------+------+-------+---------+ | name | name | index_id | type | space | PAGE_NO | +-----------+---------+----------+------+-------+---------+ | test/user | PRIMARY | 22 | 3 | 6 | 3 | | test/user | name | 23 | 0 | 6 | 4 | | test/user | age | 24 | 0 | 6 | 5 | +-----------+---------+----------+------+-------+---------+ 3 rows in set (0.00 sec)
page_no 是索引樹中Root頁的序列號。其它各項的含義可以參照:
https://dev.mysql.com/doc/refman/5.6/en/innodb-sys-indexes-table.html
再讀取頁的大小
mysql> show global variables like 'innodb_page_size'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | innodb_page_size | 16384 | +------------------+-------+ 1 row in set (0.00 sec)
最后讀取索引樹的高度
$ hexdump -s 49216 -n 10 ./user.ibd 000c040 0200 0000 0000 0000 1600 000c04a
可以發(fā)現(xiàn) PAGE_LEVEL 為 0200,表示這棵二級索引樹的高度為 3。后面的 1600 是索引的 index_id 值。十六進(jìn)制的 16 轉(zhuǎn)換為十進(jìn)制數(shù)字是 22。這個 22 正好就是上面主鍵的 index_id。
上面 hexdump 命令中 49216 是怎么算出來的?公式是 page_no * innodb_page_size + 64。
3*16384+64=49216
我們在用這個方式查看下其他兩個索引的高度。
$ hexdump -s 65600 -n 10 ./user.ibd 0010040 0100 0000 0000 0000 1700 001004a $ hexdump -s 81984 -n 10 ./user.ibd 0014040 0200 0000 0000 0000 1800 001404a
可見,name 索引的高度是 2,age 索引的高度是 3。
根據(jù)索引的結(jié)構(gòu)估算
如果你沒有數(shù)據(jù)庫服務(wù)器的權(quán)限。自己也可以根據(jù)數(shù)據(jù)庫索引結(jié)構(gòu)進(jìn)行估算樹的高度。
根據(jù) B+Tree 結(jié)構(gòu),非葉子節(jié)點存儲的是索引數(shù)據(jù),葉子節(jié)點存儲的是每行的所有數(shù)據(jù)。
非葉子節(jié)點每個索引項的大小是,數(shù)據(jù)大小+指針大小。假設(shè)指針大小為 8 個字節(jié)。每頁不會被占滿,預(yù)留1/5的空隙。下面我們估算下 name 和 age 兩個索引的高度。
name 索引高度估算
非葉子節(jié)點每頁存放的索引項數(shù)量。每頁大小是 16k。name 的值為 ab。占2個字節(jié)。每項數(shù)據(jù)大小是 2+8=10字節(jié)。每頁能存放的索引項數(shù)量是 16384 * 0.8 / 10 = 1310 個。
葉子節(jié)點每頁存放的索引數(shù)量。每頁大小是 16k。每項數(shù)據(jù)大小是 4+2+8=14 個字節(jié)。沒頁能存放的索引數(shù)量是 16384 * 0.8 / 14 = 936 個。
兩層能存放 1310*936=1226160 個數(shù)據(jù)記錄??梢?20萬條記錄以下,樹的高度為2。
age 索引高度估算
非葉子節(jié)點每頁存放的索引項數(shù)量。每頁大小是 16k。age 的類型為 int。占4個字節(jié)。每項數(shù)據(jù)大小是 4+8=12字節(jié)。每頁能存放的索引項數(shù)量是 16384 * 0.8 / 12 = 1092 個。
葉子節(jié)點每頁存放的索引數(shù)量。每頁大小是 16k。每項數(shù)據(jù)大小是 4+4+8=16 個字節(jié)。沒頁能存放的索引數(shù)量是 16384 * 0.8 / 16 = 819 個。
兩層能存放 1092*819=894348 個數(shù)據(jù)記錄。可見90萬條記錄以下,樹的高度為2。100萬條為 3 層。
其它工具
還有一個小工具可以查看。InnoDB 表空間可視化工具innodb_ruby
以上就是獲取 MySQL innodb 的 B+tree 的高度的示例的詳細(xì)內(nèi)容,更多關(guān)于MySQL innodb 的 B+tree 的資料請關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
連接MySQL時出現(xiàn)1449與1045異常解決辦法
這篇文章主要介紹了連接MySQL時出現(xiàn)1449與1045異常解決辦法的相關(guān)資料,通過IP鏈接MySQL的時候會出現(xiàn)1499與1054錯誤異常的情況,這里提供解決辦法,需要的朋友可以參考下2017-09-09MySQL對數(shù)據(jù)庫數(shù)據(jù)進(jìn)行復(fù)制的基本過程詳解
這篇文章主要介紹了MySQL對數(shù)據(jù)庫數(shù)據(jù)進(jìn)行復(fù)制的基本過程,解讀了Slave的一些相關(guān)配置,需要的朋友可以參考下2015-11-11mysql全連接和oracle全連接查詢、區(qū)別及說明
這篇文章主要介紹了mysql全連接和oracle全連接查詢、區(qū)別及說明,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教2023-03-03MySql添加新用戶及為用戶創(chuàng)建數(shù)據(jù)庫和給用戶分配權(quán)限方法介紹
這篇文章主要介紹了MySql添加新用戶及為用戶創(chuàng)建數(shù)據(jù)庫和給用戶分配權(quán)限方法介紹,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-08-08mysql中Table is read only的解決方法小結(jié)
本文章總結(jié)了關(guān)于在linux與windows中 mysql出現(xiàn)Table is read only解決辦法總結(jié),有需要的朋友可參考一下2013-01-01MySQL高級學(xué)習(xí)筆記(三):Mysql邏輯架構(gòu)介紹、mysql存儲引擎詳解
這篇文章主要介紹了Mysql邏輯架構(gòu)介紹、mysql存儲引擎,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-04-04