InnoDB--------Querying the height of IoT B + Tree

1. Background

* in the InnoDB storage engine, tables are organized according to the primary key order, which is called the Index Organization table (Index organized table IOT).

* in the InnoDB storage engine, each table has a primary key (Primary key), and if a primary key is not defined when the table is created, the InnoDB storage engine chooses the qualifying column in the table or implicitly creates the primary key.

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/9C/62/wKioL1lv2t6QpmC3AADQufUXL7Q958.png "title=" Iot.png "alt=" Wkiol1lv2t6qpmc3aadqufuxl7q958.png "/>

2. Environment

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. Enquiry

* query each table primary key corresponding to the 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) 

* Query order_line table Data volume

Mysql> Select COUNT (1) from order_line;+----------+|  Count (1) |+----------+| 6001615 |+----------+1 row in Set (9.03 sec)

* get height by table data file ORDER_LINE.IBD [Skip Root_page_no * innodb_page_size + 64, get 2 byte length is the height of the tree]

the height of the tree is 3, and the height marker starts at 0 .

[Email protected] src]# hexdump-s 24640-n 2-c/data/mysql_data_6/mytest/order_line.ibd;00006040 00 02 |..| 00006042

4. B+tree

Height	Non-leaf node points	Leaf node points	Number of data rows	Occupy space
1	0	1	468	16.0KiB
2	1	1203	> 563 thousand	18.8MiB
3	160°	1447209	> 677 million	22.1GiB
4	1448413	1740992427	> 814 billion	25.9TiB

5. xxx

This article is from the "Sea" blog, be sure to keep this source http://lisea.blog.51cto.com/5491873/1949144

InnoDB--------Querying the height of IoT B + Tree