定义
引用自《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 R=(Z/X)*(Z/Y)^{H-1} R=(Z/X)∗(Z/Y)H−1,这就是计算公式了
当Z=16KB,X=1KB,Y=16B(BigInt 8个字节,指针8个字节)时, R = 16 ∗ K H − 1 R=16 * K^{H-1} R=16∗KH−1,不妨假设K=1000,那么H与R的关系如下
H | R | 字节 |
---|---|---|
1 | 16 16 16 | 16KB |
2 | 1.6 ∗ 1 0 4 1.6*10^4 1.6∗104 | 16MB |
3 | 1.6 ∗ 1 0 7 1.6*10^7 1.6∗107 | 16GB |
4 | 1.6 ∗ 1 0 10 1.6*10^{10} 1.6∗1010 | 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,一起学习交流