前言

MySQL 的 innodb 引擎之所以使用 B+tree 來(lái)存儲(chǔ)索引，就是想盡量減少數(shù)據(jù)查詢時(shí)磁盤 IO 次數(shù)。樹的高度直接影響了查詢的性能。一般樹的高度在 3~4 層較為適宜。數(shù)據(jù)庫(kù)分表的目的也是為了控制樹的高度。那么如何獲取樹的高度呢？下面使用一個(gè)示例來(lái)說(shuō)明如何獲取樹的高度。

示例數(shù)據(jù)準(zhǔn)備

建表語(yǔ)句如下：

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萬(wàn)條數(shù)據(jù)。數(shù)據(jù)如下：

mysql> select * from user limit 2G*************************** 1. row *************************** id: 110000name: ab age: 100*************************** 2. row *************************** id: 110001name: ab age: 1002 rows in set (0.00 sec)

通過(guò)查詢相關(guān)數(shù)據(jù)表獲取樹的高度

以 MySQL5.6 版本為例說(shuō)明如何獲取樹的高度。

首先獲取 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頁(yè)的序列號(hào)。其它各項(xiàng)的含義可以參照：https://dev.mysql.com/doc/refman/5.6/en/innodb-sys-indexes-table.html

再讀取頁(yè)的大小

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.ibd000c040 0200 0000 0000 0000 1600000c04a

可以發(fā)現(xiàn) PAGE_LEVEL 為 0200，表示這棵二級(jí)索引樹的高度為 3。后面的 1600 是索引的 index_id 值。十六進(jìn)制的 16 轉(zhuǎn)換為十進(jìn)制數(shù)字是 22。這個(gè) 22 正好就是上面主鍵的 index_id。上面 hexdump 命令中 49216 是怎么算出來(lái)的？公式是 page_no * innodb_page_size + 64。3*16384+64=49216

我們?cè)谟眠@個(gè)方式查看下其他兩個(gè)索引的高度。

$ hexdump -s 65600 -n 10 ./user.ibd0010040 0100 0000 0000 0000 1700001004a$ hexdump -s 81984 -n 10 ./user.ibd0014040 0200 0000 0000 0000 1800001404a

可見(jiàn)，name 索引的高度是 2，age 索引的高度是 3。

根據(jù)索引的結(jié)構(gòu)估算

如果你沒(méi)有數(shù)據(jù)庫(kù)服務(wù)器的權(quán)限。自己也可以根據(jù)數(shù)據(jù)庫(kù)索引結(jié)構(gòu)進(jìn)行估算樹的高度。根據(jù) B+Tree 結(jié)構(gòu)，非葉子節(jié)點(diǎn)存儲(chǔ)的是索引數(shù)據(jù)，葉子節(jié)點(diǎn)存儲(chǔ)的是每行的所有數(shù)據(jù)。非葉子節(jié)點(diǎn)每個(gè)索引項(xiàng)的大小是，數(shù)據(jù)大小+指針大小。假設(shè)指針大小為 8 個(gè)字節(jié)。每頁(yè)不會(huì)被占滿，預(yù)留1/5的空隙。下面我們估算下 name 和 age 兩個(gè)索引的高度。

name 索引高度估算

非葉子節(jié)點(diǎn)每頁(yè)存放的索引項(xiàng)數(shù)量。每頁(yè)大小是 16k。name 的值為 ab。占2個(gè)字節(jié)。每項(xiàng)數(shù)據(jù)大小是 2+8=10字節(jié)。每頁(yè)能存放的索引項(xiàng)數(shù)量是 16384 * 0.8 / 10 = 1310 個(gè)。葉子節(jié)點(diǎn)每頁(yè)存放的索引數(shù)量。每頁(yè)大小是 16k。每項(xiàng)數(shù)據(jù)大小是 4+2+8=14 個(gè)字節(jié)。沒(méi)頁(yè)能存放的索引數(shù)量是 16384 * 0.8 / 14 = 936 個(gè)。兩層能存放 1310*936=1226160 個(gè)數(shù)據(jù)記錄。可見(jiàn)120萬(wàn)條記錄以下，樹的高度為2。

age 索引高度估算

非葉子節(jié)點(diǎn)每頁(yè)存放的索引項(xiàng)數(shù)量。每頁(yè)大小是 16k。age 的類型為 int。占4個(gè)字節(jié)。每項(xiàng)數(shù)據(jù)大小是 4+8=12字節(jié)。每頁(yè)能存放的索引項(xiàng)數(shù)量是 16384 * 0.8 / 12 = 1092 個(gè)。葉子節(jié)點(diǎn)每頁(yè)存放的索引數(shù)量。每頁(yè)大小是 16k。每項(xiàng)數(shù)據(jù)大小是 4+4+8=16 個(gè)字節(jié)。沒(méi)頁(yè)能存放的索引數(shù)量是 16384 * 0.8 / 16 = 819 個(gè)。兩層能存放 1092*819=894348 個(gè)數(shù)據(jù)記錄。可見(jiàn)90萬(wàn)條記錄以下，樹的高度為2。100萬(wàn)條為 3 層。

其它工具

還有一個(gè)小工具可以查看。InnoDB 表空間可視化工具innodb_ruby

以上就是獲取 MySQL innodb 的 B+tree 的高度的示例的詳細(xì)內(nèi)容，更多關(guān)于MySQL innodb 的 B+tree 的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章！