InnoDB - 如何计算 B+树层高

mooddance

已于 2022-07-17 18:07:43 修改

阅读量534

点赞数

分类专栏： MySQL 文章标签： Innodb B+树

于 2022-07-12 16:06:13 首次发布

本文链接：https://blog.youkuaiyun.com/m0_51504545/article/details/125745029

版权

MySQL 专栏收录该内容

18 篇文章

订阅专栏

一查看数据文件方案

原理：使用 hexdump 命令读取数据文件中索引数据的高度。

1.1 获取 page_no

page_no 是索引 B+树的根页码，可以从 InnoDB 元数据信息表（INFORMATION_SCHEMA）中得到。

可以使用 SHOW TABLES FROM INFORMATION_SCHEMA LIKE ‘INNODB_SYS%’; 查询到 InnoDB 相关的元数据。

mysql> 	SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_SYS%';
+--------------------------------------------+
| Tables_in_INFORMATION_SCHEMA (INNODB_SYS%) |
+--------------------------------------------+
| INNODB_SYS_DATAFILES                       |
| INNODB_SYS_VIRTUAL                         |
| INNODB_SYS_INDEXES                         |
| INNODB_SYS_FIELDS                          |
| INNODB_SYS_TABLESPACES                     |
| INNODB_SYS_FOREIGN_COLS                    |
| INNODB_SYS_FOREIGN                         |
| INNODB_SYS_TABLES                          |
| INNODB_SYS_COLUMNS                         |
| INNODB_SYS_TABLESTATS                      |
+--------------------------------------------+

仅对本文涉及的进行解释：
INNODB_SYS_INDEXES：提供关于InnoDB索引的元数据。
INNODB_SYS_TABLES：提供关于InnoDB表的元数据

以表 users 为例，该表有一个主键和一个唯一建：

CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

查看 users 表的 page_no：

mysql> SELECT b.*, a.* FROM information_schema.INNODB_SYS_INDEXES a, information_schema.INNODB_SYS_TABLES b  WHERE a.table_id = b.table_id  AND a.space <> 0  AND b.NAME = "zc_test/users"\G
*************************** 1. row ***************************
       TABLE_ID: 2290
           NAME: zc_test/users
           FLAG: 33
         N_COLS: 11
          SPACE: 2268
    FILE_FORMAT: Barracuda
     ROW_FORMAT: Dynamic
  ZIP_PAGE_SIZE: 0
     SPACE_TYPE: Single
       INDEX_ID: 3721
           NAME: PRIMARY
       TABLE_ID: 2290
           TYPE: 3
       N_FIELDS: 1
        PAGE_NO: 3
          SPACE: 2268
MERGE_THRESHOLD: 50
*************************** 2. row ***************************
       TABLE_ID: 2290
           NAME: zc_test/users
           FLAG: 33
         N_COLS: 11
          SPACE: 2268
    FILE_FORMAT: Barracuda
     ROW_FORMAT: Dynamic
  ZIP_PAGE_SIZE: 0
     SPACE_TYPE: Single
       INDEX_ID: 3722
           NAME: users_email_unique
       TABLE_ID: 2290
           TYPE: 2
       N_FIELDS: 1
        PAGE_NO: 4
          SPACE: 2268
MERGE_THRESHOLD: 50

INDEX_ID：索引的标识符，一个数据库实例中是唯一的。
TABLE_ID：表ID。
N_COLS：外键索引中的列数。
TYPE：索引类型，聚集索引、二级索引。
N_FILEDS：组成索引的字段数。
PAGE_NO：索引B+树的根页码。
SPACE：索引所在表空间的ID，等于0时代表该表使用系统表空间。
MERGE_THRESHOLD：索引页中数据量的百分比阈值。如果删除行或更新操作缩短行时索引页中的数据量低于此值（默认值为 50%），则 InnoDB尝试将索引页与相邻索引页合并。
ROW_FORMAT：行格式。
ZIP_PAGE_SIZE：仅适用于 Compressed 行格式的表。

1.2 数据页尺寸

默认 16Kb (16384 字节)，可以使用 show global variables like “innodb_page_size”; 得到。

1.3 hexdump 命令

使用 hexdump 命令看任何文件的十六进制编码，一般看“二进制”文件。

可用选项：

-n length 只格式化输入文件的前length个字节。
-C 输出规范的十六进制和ASCII码。
-b 单字节八进制显示。
-c 单字节字符显示。
-d 双字节十进制显示。
-o 双字节八进制显示。
-x 双字节十六进制显示。
-s 从偏移量开始输出。

1.4 读取树高

计算公式：

hexdump -s 偏移量 -n 10 table.ibd

其中偏移量计算公式：

page_no * innodb_page_size + 64

以上面 users 表聚集索引 page_no = 3 为例：偏移量 = 3 * 16384 + 64 = 49216

切换到 zc_test库独立表空间下：

hexdump -s 49216 -n 10 ./users.ibd

000c040 0000 0000 0000 0000 890e
000c04a

其中第一行的的第二列为：PAGE_LEVEL 页层级，通常为001（2层B+树）～002（3层B+树）。由于测试表中没什么数据，故为 000 代表为一层。

所以 users 表树高为 1。

二通过已有数据推导

原理：通过数据占用倒推树高。

以聚集索引为例：非叶子结点存储的是索引数据、页指针，叶子结点存储的是每行记录及一些指针。可以通过页大小、主键大小、指针大小以及每行数据平均大小进行推算。
具体的逻辑见文章《如何计算 InnDB 最大记录总数》，然后了解便可以推导

三其他工具

如：innodb_ruby等