以下文章来源于公众号:小白debug ,作者小白
页的结构
假设我们有一张user数据表
其中id是唯一主键。
这看起来的一行行数据,为了方便,我们后面就叫他record吧。
这张表看起来就跟excel表格一样。excel的数据在硬盘上是xx.excel的文件。
而上面user表数据,在硬盘上其实也是类似,放在了user.ibd文件下。
含义是user表的innodb data文件,专业点,又叫表空间。
虽然在数据表里,他们看起来是挨在一起的。但实际上在user.idb里他们被分成很多小份的数据页,每份大小16K。
类似于下面这样。
我们把视角聚集一下,放到页上面。
整个页16K
,不大,但record这么多,一页肯定放不下,所以会分开放到很多页里。
并且这16K,还要存放除record之外的其他信息。
因为record被分成好多份,放到好多页里了,为了唯一标识具体是哪一页,那就需要引入页号(其实是一个表空间的地址偏移量)。同时为了把这些数据页给关联起来,于是引入了前后指针,用于指向前后的页。这些都被加到了页头里。
页是需要读写的,16K说小也不小,写一半电源线被拔了也是有可能发生的,所以为了保证数据页的准确性,还引入了校验码。这个被加到了页尾。
那剩下的空间,才是用来放我们的record的。而record如果行数特别多的话,进入到页内时按个遍历,效率也不太行,所以为这些数据生成了一个页目录,具体实现不重要。只需要知道,它可以通过二分查找的方式将查找效率从O(n)变成O(lgn)。
从页到索引
如果想查一条record,我们可以把表空间里每一页都捞出来,在把里面的record捞出来挨个判断是不是我们要找的。
行数量小的时候,这么操作也没啥问题。
行数量大了,性能就慢了,于是为了加速搜索,我们可以在每一个数据页里选出主键id最小的record,而且只需要他们的主键id和所在页的页号,组成新的record,放入到一个新生成的一个数据页中,这个新数据页跟之前的页结构没啥区别,而且大小还是16K。
但为了更之前的数据页进行区分。数据页里加入了页层级(page level)的信息,从0开始往上算。于是页与页之间就有了上下层级的概念,就像下面这样:
页与页之间看起来就像一颗倒过来的树。也就是我们常说的B+树索引。
最下面那一层,page level 为 0,也就是所谓的叶子结点,其余都叫非叶子结点。
上面展示的是两层的树,如果数据变多了,我们还可以通过类似的方法,再往上构建一层。就成了三层的树。
这样我们就可以通过这样一颗B+树加速查询,举个例子。
比方我们要查找行数据5。会先从顶层页的record们入手,record里包含了主键id和页号(页地址)。看下图黄色箭头,向左最小id是1,向右最小id是7。那么id等于5的数据如果存在,必定在左边箭头。于是顺着record的页地址就到了6号
数据页里,再判断id=5>4,所以肯定在右边的数据页里,于是加载105号
数据页,在数据页里找到id=5的数据行,完成查询。
另外需要注意的是,上面的页的页号并不是连续的,他们在磁盘里也不一定是挨在一起的。
这个过程中查询了三个页,如果这三个页都在磁盘中(没有被提前加载到内存中),那么最多需要经历三次磁盘IO查询,他们才能被加载到内存中。
B+树承载的记录数量
从上面的结构可以看出B+树最末级叶子结点里放了record数据。而非叶子结点里则放了用来加速查询的索引数据。
也就是说
同样一个16k的页,非叶子结点里每一条数据都指向一个新的页,而新的页有两种可能。
- 如果是末级叶子结点的话,那么里面放的就是一行行record数据。
- 如果是非叶子结点,那么就会循环继续指向新的数据页。
假设:
- 非叶子结点内指向其他内存页的指针数量为
x
- 叶子结点能容纳的record数量为
y
- B+树的层数为
z
那么这颗B+树放的行数据总量等于( x ^ (z - 1) ) * y
x怎么计算?
我们回去看数据页的结构。
非叶子结点里主要放索引查询相关的数据,放的是主键和指向页号。
主键假设是bigint (8 byte)
,而页号在源码里叫 FIL_PAGE_OFFSET (4 byte)
,那么非叶子结点里的一条数据是12 byte
左右。
整个数据页 16k
,页头页尾部分数据加起来大概128 byte
,加上页目录大致估算1k
。那么剩下的15k 除以 128 byte
,等于1280
,也就是可以指向x=1280页。
我们常说的二叉树指的是一个结点可以发散出两个新的结点,。m叉树一个结点可以指向m个新的结点。这个指向新结点的操作就叫扇出(fanout)。
而上面的B+树,它能指向1280个新的结点,可以说扇出非常高了。
y的计算
叶子结点和非叶子结点的数据结构是一样的,所以也假设剩下15kb
可以发挥。
叶子结点里放的是真正的行数据,。假设一条行数据1kb
,所以一页里能放y=15行。
行总数计算
回到( x ^ (z - 1) * y)
这个公式。
已知 x = 1280
,y = 15
假设B+树是两层,那么z = 2
,则( 1280 ^ (2 - 1)) * 15 ≈ 2w
假设B+树是三层,那么z = 3
,则( 1280 ^ (3 - 1) * 15) ≈ 2.5kw
这个2.5kw,就是我们常说的单表建议最大行树2kw的由来,。毕竟再加一层,数据就大的有点离谱了。三层数据页对应最多三次磁盘IO,也比较合理。
行数超一亿就慢了吗?
上面假设单行数据用了1kb,所以一个数据页能放15行数据。
如果我单行数据用不了这么多,比如只用了250 byte
,。那么单个数据页能放60行数据。
那同样是三层B+树,单表支持的行数就是 (1280 ^ (3 -1) * 60) ≈ 1个亿
。
B树承载的记录数量
我们都知道,现在mysql的索引都是B+树,而有一种树,跟B+树很像,叫B树,也叫B-树。
它跟B+树最大的区别在于,B+树只在末级叶子结点处放数据表行数据,而B树则会在叶子结点和非叶子结点上都放。
于是,B树的结构类似于这样:
B树将行数据都存放在非叶子结点上,假设每个数据页还是16kb,掐头去尾每页剩15kb,并且每条数据表行数据还是占1kb,就算不考虑各种页指针的情况下,也只能放个15条数据。数据页扇出明显变少了。
计算可承载的总行书的公式也变成了一个等比数列。
15 + 15^2 +15^3 + … + 15^z
其中z还是层数的意思。
为了能放2kw
左右的数据,需要z>=6
。也就是树需要有6层,查一次要访问6个页。假设这6个页并不连续,为了查询其中一条数据,最坏情况需要进行6次磁盘IO。
而B+树同样情况下放2kw左右数据,查一次最多是3次磁盘IO。
磁盘IO越多则越慢,这两者在性能上差距略大。
为此,B+树比B树更适合成为mysql的索引。