参考:https://www.zhihu.com/question/351797203/answer/3137174084
1.MySQL innodb单表上限为啥都说是2k万条
2.GaussDB for MySQL 为啥可以突破单表2k万的限制
要讨论这两个问题,得先明确性下实际的DB部署环境
表是索引数据是放在磁盘上的,
如果MySQL的在InnoDB buffer size大小可以把表的索引数据可以完全加载进内存,那么单表数据的限制就是磁盘的大小了哈。
这里我们假设索引数据直接从磁盘查询,那么磁盘操作就是对我们的最大影响。
问题1:
MySQL Innodb采用B+树索引:
当B+树的层次为3,每条记录1K,一般能存储2k万左右,这个就是单表2k万的由来;
当B+树的层次为4,会增加1次磁盘IO,单表能存2 百亿条,影响性能,也需要看下有没有必要;
问题2:
B+Tree操作非原子,所以当一个线程做结构调整(SMO,Struction-Modification-Operation)时一般会涉及多个节点的改动。
SMO动作过程中,此时若有另一个线程进来可能会访问到错误的B+Tree结构,InnoDB为了解决这个问题采用了乐观锁和悲观锁的并发控制协议。
目前业界有一个更好的方案B-Link Tree,与B+Tree相比,B-Link Tree优化了B+Tree结构调整时的锁粒度,只需要逐层加锁,无需对root节点加全局锁。因此,可以做到在SMO过程中写操作的并发执行,保持高并发下性能的稳定。
B-Link Tree主要改进点有2个:
1.中间节点增加link指针,指向右兄弟节点;
2.每个节点内增加字段high key,存储该节点中最大的key值。
说明:
MySQL Innodb采用B+树索引:
假设
- 非叶子节点内指向其他页的数量为 x
- 叶子节点内能容纳的数据行数为 y
- B+ 数的层数为 z
Total =x^(z-1) *y 也就是说总数会等于 x 的z-1 次方 与Y 的乘积。
作者:京东云
链接:https://www.zhihu.com/question/351797203/answer/2595667438
来源:知乎
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
X =?
在文章的开头已经介绍了页的结构,索引也也不例外,都会有File Header(38 byte)、Page Header (56 Byte)、Infimum + Supermum(26 byte)、File Trailer(8byte),再加上页目录,大概1k 左右,我们就当做它就是1K,那整个页的大小是16K,剩下15k用于存数据,在索引页中主要记录的是主键与页号,主键我们假设是Bigint(8 byte),而页号也是固定的(4Byte),那么索引页中的一条数据也就是12byte; 所以x=15*1024/12≈1280 行。
Y=?
叶子节点和非叶子节点的结构是一样的,同理,能放数据的空间也是15k;但是叶子节点中存放的是真正的行数据,这个影响的因素就会多很多,比如,字段的类型,字段的数量;每行数据占用空间越大,页中所放的行数量就会越少;这边我们暂时按一条行数据1k 来算,那一页就能存下15条,Y≈15。
算到这边了,是不是心里已经有谱了啊
根据上述的公式,Total =x^(z-1) y,已知 x=1280,y=15
假设B+ 树是两层,那就是Z =2, Total = (1280 ^1 )15 = 19200
假设B+ 树是三层,那就是Z =3, Total = (1280 ^2) *15 = 24576000 (约2.45kw)
哎呀,妈呀! 这不是正好就是文章开头说的最大行数建议值 2000w嘛!对的,一般B+ 数的层级最多也就是3层,你试想一下,如果是4层,除了查询的时候磁盘IO次数会增加,而且这个Total 值会是多少,大概应该是3百多亿吧,也不太合理,所以,3层应该是比较合理的一个值。