前言
通常,我们都会人云亦云,一个表的数据最多只能放2000万条数据,再多就会变慢了,但是之前我有一个项目,一个表的数据放了7000多万数据,但是查询速度还是很快这是为什么那?怎么和别人说的不一样那?下面就来一探究竟:
user表
首先,假设我有个用户user表,里面有字段id主键,name,age,那么我们都知道,索引和数据都是放在mysql的.idb文件中,那么这个user表的数据就放在user.idb中,前提是使用的是inondb,在数据表中,看起来他们是挨在一起的,其实在user.idb中他们被分成了很多的数据页,每个数据页的大小规定是16k,如下图所示:
为了很好的区分这些页,为这些页添加了唯一的标识,那就是页号,同时为了把这些数据页给关联起来,于是引进了前后指针,用于指向前后的页,这些都被加到了页头里
页是需要被读写的,16k说小也不小,写一半突然掉电了,所以为了保证数据页的正确性,还引入了校验码,这个被加到了页尾
那么剩下的空间,才是用来放我们的数据的,如果数据特别多的话,进入到页内挨个循环,效率也比较低,所以为了这些数据生成了一个页目录,可以通过这个页目录使用二分法来将查询速度从O(n)变为O(longn),如下图所示:
从页到索引
下图所示就是B+树索引结构图:
B+树所承受的数量
从上面的结构来看,假设:
- 非叶子节点内指向其他内存页的指针数量为x
- 叶子节点内能容纳的数据数量为y
- B+树的层数为z
x怎么计算
在非叶子节点中,主键假设是bigint(8byte),而页号在源码中叫做FIL_PAGE_OFFSER(4byte),那么非叶子节点里的一条数据是128byet左右,整个数据页是16k,页头页尾那部分加起来大概128byte,加上页目录毛估占1k吧,那么剩下的15k除于12byte,等于1280,也就是x是1280页
y的计算
叶子节点和非叶子节点的数据结构是一样的,所以假设剩下的也是15k,假设一条数据占1k,所以一页能放y=15
存放数量计算
存放数量公式为:(x^(z-1))* y
x = 1280 y = 15
假设树高z是两层 z = 2,那么能存储数量:将近2w
假设树高z是两层 z = 3,那么能存储数量:将近2.5kw
这个2.5kw,也就是我们常说的单表建议2kw的由来,毕竟再加一层,数据量就大的离谱了,多查询一次,效率也就慢了
总结
由以上的结论可以看出,只要我们的叶子节点里面一行的数据越小,B+树控制在3层,一行数据越小,就可以存储数量越多,这是可以计算出来的