您好,登錄后才能下訂單哦!
1. 背景
* 在InnoDB存儲(chǔ)引擎中,表都是根據(jù)主鍵順序組織存放的,這種存儲(chǔ)方式的表稱為索引組織表(index organized table IOT)。
* 在InnoDB存儲(chǔ)引擎中,每張表都有個(gè)主鍵(Primary key),如果在創(chuàng)建表時(shí)沒(méi)有地定義主鍵,則InnoDB存儲(chǔ)引擎會(huì)選擇表中符合條件的列或隱式創(chuàng)建主鍵。
2. 環(huán)境
mysql> select version(); +------------+ | version() | +------------+ | 5.6.36-log | +------------+ 1 row in set (0.02 sec) mysql> select database(); +------------+ | database() | +------------+ | mytest | +------------+ 1 row in set (0.00 sec) mysql> show tables; +------------------+ | Tables_in_mytest | +------------------+ | customer | | district | | history | | item | | new_orders | | order_line | | orders | | stock | | warehouse | +------------------+ 9 rows in set (0.00 sec) mysql> show variables like 'innodb_page_size'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | innodb_page_size | 8192 | +------------------+-------+ 1 row in set (0.04 sec)
3. 查詢
* 查詢每張表primary key對(duì)應(yīng)的root page_no
mysql> select t.table_id table_id, t.name table_name, i.page_no root_page_no from information_schema.INNODB_SYS_INDEXES i, information_schema.INNODB_SYS_TABLES t where i.table_id = t.table_id and i.name = 'PRIMARY' and t.name like 'mytest/%'; +----------+-------------------+--------------+ | table_id | table_name | root_page_no | +----------+-------------------+--------------+ | 22 | mytest/customer | 3 | | 21 | mytest/district | 3 | | 27 | mytest/item | 3 | | 24 | mytest/new_orders | 3 | | 26 | mytest/order_line | 3 | | 25 | mytest/orders | 3 | | 28 | mytest/stock | 3 | | 20 | mytest/warehouse | 3 | +----------+-------------------+--------------+ 8 rows in set (0.04 sec)
* 查詢order_line表數(shù)據(jù)量
mysql> select count(1) from order_line; +----------+ | count(1) | +----------+ | 6001615 | +----------+ 1 row in set (9.03 sec)
* 通過(guò)表數(shù)據(jù)文件order_line.ibd獲取高度 [跳過(guò) root_page_no * innodb_page_size + 64, 獲取2字節(jié)長(zhǎng)度就是樹的高度]
由此可得樹的高度為3, 高度標(biāo)記從0開始
[root@localhost src]# hexdump -s 24640 -n 2 -C /data/mysql_data_6/mytest/order_line.ibd; 00006040 00 02 |..| 00006042
4. B+Tree
高度 | 非葉節(jié)點(diǎn)數(shù) | 葉節(jié)點(diǎn)數(shù) | 數(shù)據(jù)行數(shù) | 占用空間 |
1 | 0 | 1 | 468 | 16.0KiB |
2 | 1 | 1203 | > 563 thousand | 18.8MiB |
3 | 1204 | 1447209 | > 677 million | 22.1GiB |
4 | 1448413 | 1740992427 | > 814 billion | 25.9TiB |
5. xxx
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。