InnoDB- queries the height of IOT B + Tree

1. background

* In the InnoDB storage engine, tables are stored according to the order of primary keys, and tables stored in this way are called index organized tables (IOT).

* In the InnoDB storage engine, each table has a primary key. If the primary key is not defined when the table is created, the InnoDB storage engine selects the columns in the table that meet the conditions or implicitly creates the primary key.

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

* Query root page_no corresponding to primary key of each table

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 from table data file order_line.ibd [skip root_page_no * innodb_page_size + 64, get 2 bytes length is tree height]

This gives us a tree height of 3 and height markers starting at 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

Height Number of non-leaf nodes Number of leaf nodes Number of data rows Space occupied 10146816.0KiB211203> 563 thousand18.8MiB312041447209> 677 million22.1GiB414484131740992427> 814 billion25.9TiB

5. xxx