mysql使用innodb的B+树存储表项,保存在.ibd文件中,其中叶子节点页存储数据页,非叶子节点页存储索引页
.ibd文件的一页大小16kb,其中有15kb存记录。
假设每页的记录都存满一页:
1.非叶子节点存索引记录,一条记录4字节主键8字节页号共12字节,一页能存15*1024/12=1280条记录,所以B+树是1280叉树
2.叶子节点存数据记录,假设一条记录1kb,一页能存15/1=15条记录
3.假设存满2层的B+树:
综合1、2、3,此时,能存的最大记录数量为
total=15*1280^(2-1)=19200条记录
如果存满3层B+树,total约=2450w条记录
刚好约等于2000w条
接下来咱看看,存满2层b+树,把所有索引页加载到内存,要占多少内存:
1280*16kb=20480kb
如果内存要完全存3层B+树的索引表需要
1280*1280*16kb=26214400kb的内存
如果内存要完全存4层B+树的索引表需要
1280*1280*1280*16kb=33554432000kb的内存
可以发现,3层B+树,数据库缓冲区就基本不够了,必须要进行磁盘io
而这个磁盘io时间就是增加单标查询时间的罪魁祸首
再思考,假如一条数据记录的大小不是1kb呢?
也是只建议,最多存满3层B+树
不是1kb,会让3层B+树的最大数据记录数量发生变化
比如一条数据记录5kb 那么一个数据页只能存15kb/5kb=3条记录
此时3层B+树的最大记录数量为
total=3*1280^(3-1)=4915200条
也就是说
对于复杂数据记录的单表,建议的最大存储记录要小于甚至远小于2000w条,当然具体合理的值是多少,要具体计算