通过.ibd文件浅析InnoDB中一张表的主键索引B+树结构

最新推荐文章于 2024-02-18 17:15:30 发布

圆圆要努力变强

最新推荐文章于 2024-02-18 17:15:30 发布

阅读量1.9k

点赞数 2

文章标签： mysql

本文链接：https://blog.csdn.net/UESTC_peterpan/article/details/111636952

版权

定义
三个问题
解决问题一
py_innodb_page_info工具
hexdump核对
解决问题二
解决问题三
小结

定义

引用自《MySQL技术内幕：InnoDB存储引擎》第5.4.1节

InnoDB存储引擎表示索引组织表，即表中数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分

同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引

主键索引B+树的定义：聚集索引按照每张表的主键构造一棵B+树，这一棵树就称为主键索引B+树，每张表只能拥有一个主键索引B+树

三个问题

在学习MySQL过程中，有一个问题很吸引我，是“InnoDB一棵树能存储多少数据”，在网上搜索之，基本上搜索出来的文章都是类似的，多次确认后，都是来源于这一篇博客园的文章：InnoDB一棵B+树可以存放多少行数据？

这篇文章还算分析了一下主键索引B+树有多高，但我认为分析的不够透彻，然后剩下的其他文章，要不然转载这篇文章，要不然借鉴这篇文章，它们的核心内容如下

画一个B+树的图，已知一页的大小是16KB，假设非叶子节点中占字节数为14（8+6，8是主键BigInt字节数，6是指针长度），那么计算一下 16KB / (8 + 6) = 1170，就认为B+树的扇出是 1170，同时假设了mysql表中一行数据是1KB，因此一页可以存储16行数据。那么当高度为1时，可以存储16行数据，当高度为2时，可以存储117016=18720行数据，当高度为3时，可以存储11701170*16=21902400行数据，因此简单回答“InnoDB一棵B+树可以存放多少行数据？”，答案是2000多万行

先说明，我并没有否定这些博文的结论，而是认为他们分析的不够透彻，它们分析的角度不是从MySQL存储数据文件出发的，显得比较“跳跃”、“空洞”，甚至其中还有些许错误，至少我读完后会留下几个问题

问题一：InnoDB中，主键索引B+树的一个叶子节点是一页吗？怎么确定的？
问题二：InnoDB中，一张表中插入多少行数据后主键索引B+树的高度会变为3？
问题三：InnoDB中，主键索引B+树能存储多少行数据是否存在计算公式？

带着这些疑惑，我尝试寻找着答案，花了几天时间，直到看了姜承尧的《MySQL技术内幕：InnoDB存储引擎》后，心中的疑惑才逐渐解开

在这本书中，作者写了一个分析工具 py_innodb_page_info（下文会有源码），用于分析 .ibd 文件的内部结构，举个分析结果例子，是如下这样子的，这个结果中明确的说明了B+树有5个节点，并且高度为2（0001表示根层，0000表示叶子层）

page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0001>
page offset 00000004, page type <B-tree Node>, page level <0000>
page offset 00000005, page type <B-tree Node>, page level <0000>
page offset 00000006, page type <B-tree Node>, page level <0000>
page offset 00000007, page type <B-tree Node>, page level <0000>
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 9:
Freshly Allocated Page: 1
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 5
File Segment inode: 1

这种分析方式是我个人比较喜欢的，我们都知道，MySQL的数据是存储在本地磁盘上的，那么磁盘中一定存在一些文件记录这些数据，这些文件一定会有它的存储结构（好比JAVAC编译出的class文件一样），如果能知晓存储文件的结构，就能进行解析分析了，而 .ibd 文件就是负责存储每张表数据的一份文件

如果读者对 MySQL索引、B+树原理还不是很了解，或者希望自己也在Ubuntu上测试下MySQL，可以先看下这些文章：B树和B+树的插入删除图文详解、在 Ubuntu 上安装 MySQL、一文读懂MySQL的索引结构及查询优化

解决问题一

下面开始我的分析，我买的是腾讯云的云主机，操作系统是 Ubuntu18，安装的MySQL版本是 Server version: 5.7.32-0ubuntu0.18.04.1 (Ubuntu)，用的远程连接工具是 Navicat

下文中会不断用到InnoDB中一页大小是16KB的概念，因此我这里先查询一下，确认一页大小是16KB

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

我创建了一个数据库peter，然后创建一张表p96。叫p96的原因是这个例子来自于《MySQL技术内幕：InnoDB存储引擎》第2版第96页中的例子。这张p96表中有两个字段，一个是col1，其类型是INT，作为主键使用，一个是 col2，其类型是VARCHAR(7000)，这么做的目的是我一会会往表中插入一行行数据，每行的第二列有7000个字符，这样子的话，一个16KB的页最多只能存储两行，存储不了三行，方便后续测试

DROP TABLE IF EXISTS p96;
CREATE TABLE p96 (col1 INT NOT NULL AUTO_INCREMENT, col2 VARCHAR(7000), PRIMARY KEY(col1)) ENGINE=InnoDB;

然后写一个批量执行PROCEDURE

CREATE PROCEDURE load_p96(count INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c VARCHAR(7000) DEFAULT REPEAT('a', 7000);
WHILE s <= count DO
INSERT INTO p96 SELECT NULL, c;
SET s = s + 1;
END WHILE;
END;

再调用PROCEDURE，往表中插入两行数据（正好为一页）

CALL load_p96(2);

执行完成，结果如下

CALL load_p96(2);
受影响的行: 1
时间: 0.023s

到Ubuntu上，来到目录 /var/lib/mysql/，可以看到有几个文件夹，其中文件夹peter就是对应着数据库peter的（文件夹/数据库mysql是默认存在的，ib_logfile0、ib_logfile1是重做日志文件，ibdata1是共享表空间文件，这些在书中第3章都有介绍）

root@VM-8-6-ubuntu:/var/lib/mysql# ls
auto.cnf    client-cert.pem  ib_buffer_pool  ib_logfile1  performance_schema  public_key.pem   sys
ca-key.pem  client-key.pem   ibdata1         ibtmp1       peter               server-cert.pem
ca.pem      debian-5.7.flag  ib_logfile0     mysql        private_key.pem     server-key.pem

cd 到 peter 下，可以看到有几份文件，其中p96.ibd就是刚创建的表p96对应的表空间文件（还有t1.ibd，t2.ibd是我之前创建的表对应的表空间文件）

root@VM-8-6-ubuntu:/var/lib/mysql/peter# ls
db.opt  p96.frm  p96.ibd  t1.frm  t1.ibd  t2.frm  t2.ibd

在 MySQL 中开启了 innodb_file_per_table 参数后（默认是ON），每个数据表A.b（A是数据库名称，b是表名）都会在 MySQL 中目录下（Ubuntu下是 /var/lib/mysql/ ）创建一个文件夹A，然后以 b.ibd 文件作为表空间文件，存储索引、数据、缓冲BItMap等，对这份文件进行分析就可以知道“InnoDB一张表的主键索引B+树结构”

现在，使用作者写的 py_innodb_page_info 工具，分析一下这份 t1.ibd 文件，得到如下结果

root@VM-8-6-ubuntu:/var/lib/mysql/peter# ibd p96.ibd -v
page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0000>
page offset 00000000, page type <Freshly Allocated Page>
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 6:
Freshly Allocated Page: 2
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 1
File Segment inode: 1

从结果中可知，此时B+树只有一个节点，既是根节点，也是叶子节点，高度为1，一个节点大小是一页大小（16KB）不到

然后我再插入一行数据

CALL load_p96(1);

再使用 py_innodb_page_info 工具分析，得到如下结果

root@VM-8-6-ubuntu:/var/lib/mysql/peter# ibd p96.ibd -v
page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0001>
page offset 00000004, page type <B-tree Node>, page level <0000>
page offset 00000005, page type <B-tree Node>, page level <0000>
Total number of page: 6:
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 3
File Segment inode: 1

从结果可知，B+树节点数变为3了，且高度为2了，page level 0001 是根节点，page level 0000 是叶子节点，也是数据节点，且有2个

经过我测试后，关系如下

插入行数	B+树节点个数	B+树高度
1	1	1
2	1	1
3	3	2
4	4	2
5	4	2
6	5	2
7	5	2
8	6	2
9	6	2
10	7	2

别忘了，我们每一行插入的数据是有7000个字符，这样一页16KB就只够存放两行数据，不够存放三行数据（当然InnoDB中一行还有一些隐藏列，但所占字符都是常数，不会导致 7000 * 2 + 常数超过 16KB）。结合上面关系表，可以发现从行数4开始，每多插入2行（一页），就会多产生1个B+树节点，这也就可以回答问题一了

问题一：InnoDB中，主键索引B+树的一个叶子节点是一页吗？怎么确定的？

回答：一棵B+树的一个叶子节点是一页，通过py_innodb_page_info 工具分析确定的（当然，回答正确的前提是工具的分析逻辑是正确的，这一点我相信书的作者）

上面的分析工具 py_innodb_page_info 给了我们一个很好的分析结果，它能够告诉我们一张表中B+树有多少个节点，有多高，从而去推论出一些疑惑的结论，而工具分析的数据源正是 MySQL表的 .ibd 文件（表空间文件），这就实打实的确定了许多问题，相比于空想的画一棵B+树图，然后YY能存储多少数据，更能让人信服的多

此时我们不妨来画一下B+树

插入了2行数据
在这里插入图片描述
插入了3行数据

插入了5行数据

需要说明的是，上面图可能不全面代表InnoDB中真实的B+树，比如一页中不止只有行，还有FileHeader、PageHeader、PageDirectory等数据，还有，插入了4行数据的B+树该怎么画？为什么它是4个节点，不应该是3个节点吗？还有，InnoDB中，假设B+树扇出为3，那么非叶子节点是有3个孩子节点还是4个孩子节点？这些问题都属于本文的遗留问题，但本文不会涉及它，你可以有所猜测，比如InnoDB中在4行数据的情况下，2个叶子节点放不下，必须要3个叶子节点，但至于为什么，这就需要深究、看源码了，这不属于本文的讨论范畴

py_innodb_page_info工具

写到这里，就非常有必要介绍一下分析工具 py_innodb_page_info 了，它的作者是《MySQL技术内幕：InnoDB存储引擎》的作者姜承尧，可以在 code.google.com 上搜索 david-mysql-tools 来下载到，我这里直接将源码贴出来，是用 python 写的，代码很短，有3份文件

py_innodb_page_info.py

#! /usr/bin/env python 
#encoding=utf-8
import mylib
from sys import argv
from mylib import myargv
 
# main函数
if __name__ == '__main__':
	myargv = myargv(argv)
	if myargv.parse_cmdline() == 0:
		pass
	else:
		# 执行main方法
		mylib.get_innodb_page_type(myargv)

mylib.py

#encoding=utf-8
import os
import include
from include import *

VARIABLE_FIELD_COUNT = 1
NULL_FIELD_COUNT = 0
 
class myargv(object):
    def __init__(self, argv):
        self.argv = argv
        self.parms = {}
        self.tablespace = ''
 
    # 解析输入参数
    def parse_cmdline(self):
        argv = self.argv
        if len(argv) == 1:
            print 'Usage: python py_innodb_page_info.py [OPTIONS] tablespace_file'
            print 'For more options, use python py_innodb_page_info.py -h'
            return 0 
        while argv:
            if argv[0][0] == '-':
                if argv[0][1] == 'h':
                    self.parms[argv[0]] = ''
                    argv = argv[1:]
                    break
                if argv[0][1] == 'v':
                    self.parms[argv[0]] = ''
                    argv = argv[1:]
                else:
                    self.parms[argv[0]] = argv[1]
                    argv = argv[2:]
            else:
                self.tablespace = argv[0]
                argv = argv[1:]
        if self.parms.has_key('-h'):
            print 'Get InnoDB Page Info'
            print 'Usage: python py_innodb_page_info.py [OPTIONS] tablespace_file\n'
            print 'The following options may be given as the first argument:'
            print '-h   help '
            print '-o output put the result to file'
            print '-t number thread to anayle the tablespace file'
            print '-v   verbose mode'
            return 0
        return 1
 
def mach_read_from_n(page,start_offset,length):
    ret = page[start_offset:start_offset+length]
    return ret.encode('hex')

# main方法
def get_innodb_page_type(myargv):
	# 读取 .ibd 文件，存入 f
    f=file(myargv.tablespace,'rb')
    # INNODB_PAGE_SIZE=16KB，f 一定是16KB的整数倍，一页大小是16KB
    # 使用 f 的字节总数除以 16KB，得到的 fsize 表示页数
    fsize = os.path.getsize(f.name)/INNODB_PAGE_SIZE
    ret = {}
    # 枚举每一页
    for i in range(fsize):
    	# 读取16KB数据，存入page，是第i的数据
    	# page 是二进制文件
        page = f.read(INNODB_PAGE_SIZE)
        # page_offset 是 page[4， 8) 字符所表示的数据
        page_offset = mach_read_from_n(page,FIL_PAGE_OFFSET,4)
        # page_type 是 page[24, 26) 字符所表示的数据
        page_type = mach_read_from_n(page,FIL_PAGE_TYPE,2)
        # 如果加了参数 -v，表示输出详细数据
        if myargv.parms.has_key('-v'):
        	# page_type 为 45bf，表示 B-tree Node，输出它的信息
            if page_type == '45bf':
            	# page_level 是 page[64, 66) 字符所表示的数据
                page_level = mach_read_from_n(page,FIL_PAGE_DATA+PAGE_LEVEL,2)
                print "page offset %s, page type <%s>, page level <%s>"%(page_offset,innodb_page_type[page_type],page_level)
            # page_type 对应 include.py 中的 innodb_page_type，输出它的信息
            else:
                print "page offset %s, page type <%s>"%(page_offset,innodb_page_type[page_type])
        # 统计数据
        if not ret.has_key(page_type):
            ret[page_type] = 1
        else:
            ret[page_type] = ret[page_type] + 1
    # 输出fsize，表示有多少页
    print "Total number of page: %d:"%fsize
    # 输出统计数据
    for type in ret:
        print "%s: %s"%(innodb_page_type[type],ret[type])

include.py

#include.py
#encoding=utf-8
INNODB_PAGE_SIZE = 1024 * 16 # InnoDB Page 16K
 
# Start of the data on the page
FIL_PAGE_DATA = 38

FIL_PAGE_OFFSET = 4 # page offset inside space
FIL_PAGE_TYPE = 24 # File page type
 
# Types of an undo log segment */
TRX_UNDO_INSERT = 1
TRX_UNDO_UPDATE = 2
 
# On a page of any file segment, data may be put starting from this offset
FSEG_PAGE_DATA = FIL_PAGE_DATA
 
# The offset of the undo log page header on pages of the undo log
TRX_UNDO_PAGE_HDR = FSEG_PAGE_DATA
 
PAGE_LEVEL = 26 #level of the node in an index tree; the leaf level is the level 0 */
 
innodb_page_type = {
	'0000':u'Freshly Allocated Page',
	'0002':u'Undo Log Page',
	'0003':u'File Segment inode',
	'0004':u'Insert Buffer Free List',
	'0005':u'Insert Buffer Bitmap',
 	'0006':u'System Page',
	'0007':u'Transaction system Page',
	'0008':u'File Space Header',
    '0009':u'Extend Description Page',
    '000a':u'Uncompressed BLOB Page',
    '000b':u'1st compressed BLOB Page',
    '000c':u'Subsequent compressed BLOB Page',
    '45bf':u'B-tree Node'
}
innodb_page_direction = {
	'0000': 'Unknown(0x0000)',
    '0001': 'Page Left',
    '0002': 'Page Right',
    '0003': 'Page Same Rec',
    '0004': 'Page Same Page',
    '0005': 'Page No Direction',
    'ffff': 'Unkown2(0xffff)'
}

读者如果希望自己测试的话，可以将这三份文件创建于一个目录下，然后运行 python py_innodb_page_info.py xxx.ibd -v，就可以得到结果了，加 -v 是打印更详细的输出结果。我在ubuntu上写了一个bash脚本，因此上面的执行命令直接变为了 ibd xxx.ibd 的形式

上面的代码中我加了注释，相信能帮助阅读，从代码中我们可以得到一些小结论

.ibd文件的大小一定是16KB的整数倍，因此从文件大小除以16KB就可以知道有多少页了
每一页（16KB）中第[24, 26)字节表示 page_type，这里 page_type 有多种，其中值为 45bf 表示 B-tree Node，还有的表示 Freshly Allocated Page、Undo Log Page、File Segment inode 等
每一页根据不同page_type，其内部结构是确定的，比如 page_type=B-tree Node，那么 page_level就是 page[64, 66) 字符所表示的数据

hexdump核对

这里再贴一下py_innodb_page_info的分析结果

root@VM-8-6-ubuntu:/var/lib/mysql/peter# ibd p96.ibd -v
page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0001>
page offset 00000004, page type <B-tree Node>, page level <0000>
page offset 00000005, page type <B-tree Node>, page level <0000>
Total number of page: 6:
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 3
File Segment inode: 1

此时我们不妨通过 hexdump 工具（Linux自带）来进一步分析核对一下

先看head 10行（hexdump输出的是十六进制，0表示1个十六进制数，对应是4个二进制数，因此00对应是8个二进制数，8个二进制数是1个字节）

root@VM-8-6-ubuntu:/var/lib/mysql/peter# hexdump -C -v p96.ibd | head
00000000  b2 53 f1 ba 00 00 00 00  00 00 00 00 00 00 00 00  |.S..............|
00000010  00 00 00 00 0e 6e dd de  00 08 00 00 00 00 00 00  |.....n..........|
00000020  00 00 00 00 00 3a 00 00  00 3a 00 00 00 00 00 00  |.....:...:......|
00000030  00 0b 00 00 00 40 00 00  00 21 00 00 00 0a 00 00  |.....@...!......|
00000040  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 00 00  |................|
00000050  00 01 00 00 00 00 00 9e  00 00 00 00 00 9e 00 00  |................|
00000060  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 00 00  |................|
00000070  00 00 00 00 00 03 00 00  00 00 ff ff ff ff 00 00  |................|
00000080  ff ff ff ff 00 00 00 00  00 01 00 00 00 02 00 26  |...............&|
00000090  00 00 00 02 00 26 00 00  00 00 00 00 00 00 ff ff  |.....&..........|

根据刚才的小结论，每一页（16KB）中第[24, 26)字节表示 page_type，那么 page_type = 0008，查找 include.py 的 innodb_page_type，0008对应着File Space Header，因此第一页的page_type是File Space Header

以此类推，ibd分析结果中，page offset 00000003 是第4页，也就是前面有3页，每页是16KB，hexdump输出中每行是16个字节，因此一页有1024行（16KB/16B），那么第4页就是从 3 * 1024 + 1 = 3073 行开始

此时从3073行开始看

root@VM-8-6-ubuntu:/var/lib/mysql/peter# hexdump -C -v p96.ibd | awk 'NR >= 3073' | head
0000c000  18 e0 56 bb 00 00 00 03  ff ff ff ff ff ff ff ff  |..V.............|
0000c010  00 00 00 00 0e 6e dd de  45 bf 00 00 00 00 00 00  |.....n..E.......|
0000c020  00 00 00 00 00 3a 00 02  00 cc 80 08 00 00 00 00  |.....:..........|
0000c030  00 c4 00 02 00 05 00 06  00 00 00 00 00 00 00 00  |................|
0000c040  00 01 00 00 00 00 00 00  00 4b 00 00 00 3a 00 00  |.........K...:..|
0000c050  00 02 00 f2 00 00 00 3a  00 00 00 02 00 32 01 00  |.......:.....2..|
0000c060  02 00 1b 69 6e 66 69 6d  75 6d 00 07 00 0b 00 00  |...infimum......|
0000c070  73 75 70 72 65 6d 75 6d  00 10 00 11 00 0e 80 00  |supremum........|
0000c080  00 01 00 00 00 04 00 00  00 19 00 0e 80 00 00 02  |................|
0000c090  00 00 00 05 00 00 00 21  00 0e 80 00 00 04 00 00  |.......!........|

第[24, 26)字节表示 page_type，那么 page_type = 45bf，查找 include.py 的 innodb_page_type，45bf对应着B-tree Node，因此这一页表示的就是B+树的一页了，再看，第[64, 66)字节表示 page_level，那么 page_level = 0001，因此是非叶子节点

至此，我们通过py_innodb_page_info工具分析以及hexdump核对就结束了

解决问题二

问题二：InnoDB中，一张表中插入多少行数据后主键索引B+树的高度会变为3？

要解决这个问题，我们可以用上面的方法，不断地插入数据，然后通过py_innodb_page_info工具分析，找到最小的插入行数，使得B+树高度为3，也就是出现page_level=2的页

执行SQL

DROP TABLE IF EXISTS p96;
CREATE TABLE p96 (col1 INT NOT NULL AUTO_INCREMENT, col2 VARCHAR(7000), PRIMARY KEY(col1)) ENGINE=InnoDB;

CREATE PROCEDURE load_p96(count INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c VARCHAR(7000) DEFAULT REPEAT('a', 7000);
WHILE s <= count DO
INSERT INTO p96 SELECT NULL, c;
SET s = s + 1;
END WHILE;
END;

CALL load_p96(x);

查询是否存在page_level为2的页

ibd p96.ibd -v | grep "page level <0002>"

上面这个过程满足单调性，可以用二分查找加速优化，其实就是在不断调整CALL load_p96(x)中传入参数x的过程，比如设置x=2000，发现grep无结果，设置x=3000，发现grep有结果，那么答案就在2000与3000之间，下次设置x=2500… 一步步进行下去，最终得到结果是 x = 2240

x=2239时的结果

root@VM-8-6-ubuntu:/var/lib/mysql/peter# ibd p96.ibd -v | grep "page level <0002>"
root@VM-8-6-ubuntu:/var/lib/mysql/peter# ibd p96.ibd -v | grep "page level <0001>"
page offset 00000003, page type <B-tree Node>, page level <0001>
root@VM-8-6-ubuntu:/var/lib/mysql/peter# ibd p96.ibd -v | grep "page level <0000>" | wc -l
1120

x=2240时的结果

root@VM-8-6-ubuntu:/var/lib/mysql/peter# ibd p96.ibd -v | grep "page level <0002>"
page offset 00000003, page type <B-tree Node>, page level <0002>
root@VM-8-6-ubuntu:/var/lib/mysql/peter# ibd p96.ibd -v | grep "page level <0001>"
page offset 00000024, page type <B-tree Node>, page level <0001>
page offset 00000025, page type <B-tree Node>, page level <0001>
root@VM-8-6-ubuntu:/var/lib/mysql/peter# ibd p96.ibd -v | grep "page level <0000>" | wc -l
1121

我们来分析一下结果的含义，当x=2239时，有1120个叶子节点，再插入一行数据时，应该会使得B+树上多一个节点，然后B+树分裂为高度3的树，因此这课B+树的扇出为1121

至此，问题二就解决了

解决问题三

我们继续问题二中的分析结果，当插入了2240行数据后，B+树高度变为了3，B+树的扇出为1121，我们知道，主键索引B+树中，非叶子节点是不存储数据的，而存储的是主键与指针，而当x从2239变化到2240时，根节点发生了分裂，由高度2变为了高度3，且根节点这一页的大小是16KB，因此可知非叶子节点中一个主键+指针的大小 = 16KB/(1121) = 14.61KB

我们计算出了主键+指针的大小约等于14KB，这似乎与文章InnoDB一棵B+树可以存放多少行数据？中假设的14字节不谋而合，但是仔细一样，并非如此，那篇文章中，假设的主键是BigInt，占8个字节，指针占6个字节，但是我们创建的表中主键用的可是INT，占4个字节，那么意思是指针占10个字节吗？这是怎么一回事？

DROP TABLE IF EXISTS p96;
CREATE TABLE p96 (col1 INT NOT NULL AUTO_INCREMENT, col2 VARCHAR(7000), PRIMARY KEY(col1)) ENGINE=InnoDB;

为了进一步进行测试，我们不妨做一下修改，将主键类型改为SMALLINT（占2个字节）

DROP TABLE IF EXISTS p96;
CREATE TABLE p96 (col1 SMALLINT NOT NULL AUTO_INCREMENT, col2 VARCHAR(7000), PRIMARY KEY(col1)) ENGINE=InnoDB;

其他的条件都不变，同样用二分的方式定位，最终得到的结果是，x=2631时，B+树高度变为3，扇出为1315，用同样的方法计算，此时主键+指针的大小 = 16KB/(1315) = 12.45KB

我们会发现，当我将主键类型由INT（4个字节）改为SMALLINT（2个字节）后，主键+指针的大小，也随之减少了2个字节，这说明了什么？

至少可以说明一个问题，不能一味的假设除了非叶子节点中指针的大小是6个字节，根据实测，我们得到的结论是占10个字节，但还有一种可能，就是主键索引B+树的非叶子节点中，除了主键与指针外，还存在其他数据，占了一些字节，这我就不是很清楚了，也不属于本文的讨论范畴，但这不影响测试结果是可信赖的，是一步步推出来的

现在来回答问题三：InnoDB中，主键索引B+树能存储多少行数据是否存在计算公式？

要回答这个问题，还需要定义一些变量才行

假设一页的大小是Z（默认为16KB），一行全部数据的大小是X(B)，主键索引B+树中非叶子节点中主键+指针的大小为Y(B)，B+树高度为H

可推出，B+树的扇出为 Z/Y，叶子节点的一页能存 Z/X 行数据

因此可得，一棵高度为H的B+树可存储的数据行数 $R=(Z/X)*(Z/Y)^{H-1}$ ，这就是计算公式了

当Z=16KB，X=1KB，Y=16B（BigInt 8个字节，指针8个字节）时， $R=16 * K^{H-1}$ ，不妨假设K=1000，那么H与R的关系如下

H	R	字节
1	$16$	16KB
2	$1.6*10^4$	16MB
3	$1.6*10^7$	16GB
4	$1.6*10^{10}$	16TB

由此也可以侧面的说明了，为什么MySQL InnoDB的主键索引B+树中，一般高度就是3 - 4行，已经完全达到当前计算机物理硬件的存储能力瓶颈了

磁盘读取一页需要1次IO，因此一次SQL主键索引B+树的查询，需要2 - 4次IO，一般的机械磁盘每秒至少可以做100次IO，那么2 - 4次IO时间就是20 - 40ms

小结

本文参考了《MySQL技术内幕：InnoDB存储引擎》，使用工具分析了如何通过.ibd文件查看InnoDB中一张表的主键索引B+树结构，其有多少个节点，高度为多少。确定了主键索引B+树中一个叶子节点就是一页（默认为16KB）。同时分析了插入多少行数据后，主键索引B+树的高度会变为3。最后得出了一个主键索引B+树能存储多少行数据的计算公式

如有错误之处，欢迎读者指正。希望一些学习的朋友，可以加我工作微信xiaopanpanloveu，一起学习交流

圆圆要努力变强

关注

2
点赞
踩
5

收藏

觉得还不错? 一键收藏
2
评论
通过.ibd文件浅析InnoDB中一张表的主键索引B+树结构

定义三个问题解决问题一py_innodb_page_info工具hexdump核对解决问题二解决问题三小结定义引用自《MySQL技术内幕：InnoDB存储引擎》第5.4.1节InnoDB存储引擎表示索引组织表，即表中数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+数据结构一样，每个数据页都通过一个双向链表来进行链接。由于实.
复制链接

扫一扫