InnoDB--------查询IOT B+ Tree的高度

1. 背景

* 在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table IOT)。

* 在InnoDB存储引擎中，每张表都有个主键(Primary key)，如果在创建表时没有地定义主键，则InnoDB存储引擎会选择表中符合条件的列或隐式创建主键。

2. 环境

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对应的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表数据量

mysql> select count(1) from order_line;+----------+| count(1) |+----------+|  6001615 |+----------+1 row in set (9.03 sec)

* 通过表数据文件order_line.ibd获取高度 [跳过 root_page_no * innodb_page_size + 64, 获取2字节长度就是树的高度]

由此可得树的高度为3, 高度标记从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

5. xxx