引子
hello,各位小伙伴,大家好,这篇是mysql的第三篇了,还没看过前两篇的小伙伴可以去看看,对理解这一篇有很大的帮助哦。
废话不多说,我们直接开始正文,作为一名java后端开发工程师,我们都知道,数据库中一张表最大存储数据官方建议是在两千万左右,而业界中,大家普遍认为,数据超过五百万了,就可以考虑分库分表了,也就是说,单表数据一般是不会超过五百万的。
如何分库分表我们下一篇再聊,这篇,叶子主要想和大家聊一下,为什么mysql官方建议单表数据不能超过两千万呢,单表超过了两千万数据会怎么样,公司数据库中表单数据过亿了,为什么查询速度依旧在可接受的范围内。
ok,我们带着这些问题,来和叶子一起看看吧。
为什么官方建议单表数据不要超过两千万?
先看看限制表中数据的因素有哪些?
第一个,我们首先想到的,就是主键了。
我们新建一张表,先不设置主键,数据表中也没有递增。唯一的字段作为主键,这时,这张表就会默认生成一个主键字段,这个字段会被隐藏起来,在表中是看不到的。
默认生成的主键是六个字节 ,我们知道一个字节是八位,2^48-1 换算下来,这是一个很大的数字,到达了兆级,所以,永远不用担心默认的主键不够用。
好了继续往下看 ,主键大小限制表的上限,这也意味着,如果是int类型的主键,单表最大存储21亿多条数据,对于大多数业务场景,这样够用了。
那除了主键之外,还有什么因素限制表中的数据呢?
那肯定是表数据的底层,B+树了,看过上一篇的小伙伴应该知道,InnoDB存储结构是表空间,区,页,行。
上一篇中,我们已经知道了,B+树的数据存储在叶子结点,非叶子结点存储指针和主键,三层B+树就可以存储二千多万条数据,但这是在行数据小于1k的情况下的。
看看这张表,一行行数据紧挨在一起,实际上,在ibd文件中,他们是分成数据页,一个数据页大小是16k。
那我们知道,页空间大小固定为16kb,行数据大小有时候是超过1K的,这个时候,一行数据就会分开放在很多页里,产生页分裂,为了标识这一行数据具体在哪一页,就需要引入页号,实际上就是一个内存地址偏移量。
同时为了把这些数据页给关联起来,于是引入了前后指针,用于指向前后的页。这些都被加到了页头里。
页是需要读写的,16k写一半电源线被拔了也是有可能发生的,所以为了保证数据页的正确性,还引入了校验码。这个被加到了页尾。
页头放完指针,页尾放完校验码,剩下的空间,才是用来放我们的行数据的。
而如果行数特别多的话,进入到页内时挨个遍历,效率也不太行,所以为这些数据生成了一个页目录,具体实现后面再和大家聊。现在只需要知道,它可以通过二分查找的方式将查找效率从O(n) 变成O(lgn)。
如果想要查询一行数据,我们可以把每一页都捞出来,然后挨个去判断表中数据量小,分成的页比较少的时候,这样做没问题。
行数据量大了,性能就会变慢,为了加快速度,每个数据页选出主键id最小的数据,只要他们的主键id和页号,放入到一个新生成的一个数据页中,这个新数据页跟之前的页结构没啥区别,而且大小还是16k。
行总数计算
这里记住这样一个公式, (x ^ (z-1)) * y 。
已知x=1170,也就是一层B+树的指针数量,y=15,这个是指,除去页头页尾,页目录占据的内存空间后,存储数据可用空间。
假设B+树是两层,那z=2。则是(1170^ (2-1)) * 15 ≈ 1.7w
假设B+树是三层,那z=3。则是(1170 ^ (3-1)) * 15 ≈ 2.0kw
这个2.0kw,就是我们常说的单表建议最大行数2kw的由来。毕竟再加一层,数据就大得有点离谱了。三层数据页对应最多三次磁盘IO,也比较合理。
最后,解答一下上一篇提出的问题。
如果我单行数据用不了这么多,用不到1kb,比如只用了250byte。那么单个数据页能放大约65行数据。
那同样是三层B+树,单表支持的行数就是 (1170 ^ (3-1)) * 65 ≈ 九千多万,四舍五入,就是一个亿。
总结
ok,这篇我们深入了解了B+树的底层,以及B+树三层结构最大可以存储多少数据,希望小伙伴们有所收获,最后,不要忘记点赞哦。