以测试表 t100w 为例
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| world |
+--------------------+
6 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> desc t100w;
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| id | int | YES | | NULL | |
| num | int | YES | | NULL | |
| k1 | char(2) | YES | | NULL | |
| k2 | char(4) | YES | | NULL | |
| dt | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
5 rows in set (0.00 sec)
将表 information_schema.innodb_indexes
与表 information_schema.innodb_tables 链接查询出 表 t100w 的索引信息
mysql> select b.name,a.name,a.index_id,a.page_no from
information_schema.innodb_indexes as a join
information_schema.innodb_tables as b on
a.table_id=b.table_id where b.name='test/t100w';
+------------+-----------------+----------+---------+
| name | name | index_id | page_no |
+------------+-----------------+----------+---------+
| test/t100w | GEN_CLUST_INDEX | 148 | 4 |
+------------+-----------------+----------+---------+
其中index_id=148,为 表 t100w 的索引唯一id号, page_no=4 说明该索引在第5个数据页中(数据页以0开始)
索引树高度信息和索引id信息存储在数据页第65字节-74字节(共10字节)
所以偏移量计算结果如下:去除前4个数据页(每个数据页16kb,每kb1024字节)和第5个数据页的前64个字节共65600字节
mysql> select 4*16*1024+64;
+--------------+
| 4*16*1024+64 |
+--------------+
| 65600 |
+--------------+
1 row in set (0.00 sec)
使用hexdump命令, - s 参数 去除偏移量 , - n 截取数据长度 ,后跟数据文件
得出如下结果
[root@db01 /tmp]# hexdump -s 65600 -n 10 /data/3306/data/test/t100w.ibd
0010040 0200 0000 0000 0000 9400
001004a
0200 为索引树高度 3层,(叶节点从0开始)
0000 0000 0000 9400 为索引id 值,9*16+4=148