主頁 > 知識庫 > 獲取 MySQL innodb B+tree 的高度的方法

獲取 MySQL innodb B+tree 的高度的方法

熱門標(biāo)簽:申請外呼電話線路 湖南人工外呼系統(tǒng)多少錢 南通自動外呼系統(tǒng)軟件 百度地圖圖標(biāo)標(biāo)注中心 日照旅游地圖標(biāo)注 信陽穩(wěn)定外呼系統(tǒng)運營商 廣東人工電話機器人 石家莊電商外呼系統(tǒng) 芒果電話機器人自動化

前言

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 值。十六進制的 16 轉(zhuǎ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ù) 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ù)記錄。可見120萬條記錄以下,樹的高度為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ù)記錄??梢?0萬條記錄以下,樹的高度為2。100萬條為 3 層。

其它工具

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

以上就是獲取 MySQL innodb 的 B+tree 的高度的示例的詳細內(nèi)容,更多關(guān)于MySQL innodb 的 B+tree 的資料請關(guān)注腳本之家其它相關(guān)文章!

您可能感興趣的文章:
  • 詳解MySQL InnoDB存儲引擎的內(nèi)存管理
  • MySQL Innodb關(guān)鍵特性之插入緩沖(insert buffer)
  • MySQL InnoDB 鎖的相關(guān)總結(jié)
  • 如何區(qū)分MySQL的innodb_flush_log_at_trx_commit和sync_binlog
  • Mysql InnoDB的鎖定機制實例詳解
  • Mysql技術(shù)內(nèi)幕之InnoDB鎖的深入講解
  • 修改MySQL數(shù)據(jù)庫引擎為InnoDB的操作
  • mysql innodb的重要組件匯總
  • Mysql InnoDB和MyISAM區(qū)別原理解析
  • MySQL InnoDB ReplicaSet(副本集)簡單介紹

標(biāo)簽:阿里 牡丹江 公主嶺 合肥 沈陽 呼和浩特 惠州 天津

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《獲取 MySQL innodb B+tree 的高度的方法》,本文關(guān)鍵詞  獲取,MySQL,innodb,B+tree,的,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《獲取 MySQL innodb B+tree 的高度的方法》相關(guān)的同類信息!
  • 本頁收集關(guān)于獲取 MySQL innodb B+tree 的高度的方法的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章