为什么大家说mysql数据库单表最大两千万?依据是啥?
故事从好多年前说起。
想必大家也听说过数据库单表 建议最大2kw 条数据这个说法。如果超过了,性能就会下降得比较厉害。
巧了。
我也听说过。
但我不接受它的建议,硬是单表装了1亿条数据。
这时候,我们组里新来的实习生看到了之后,天真无邪地问我:"单表不是建议最大两千万吗?为什么这个表都 放了1个亿还不分库分表 "?
我能说我是 因为懒 吗?我当初设计时哪里想到这表竟然能涨这么快…
我不能。
说了等于承认自己是 开发组里的毒瘤 ,虽然我确实是,但我不能承认 。
我如坐针毡,如芒刺背,如鲠在喉。
开始了一波骚操作。
"我这么做是有道理的"
"虽然这个表很大,但你有没有发现它查询其实还是很快"
"这个2kw是个建议值,我们要来看下这个2kw是怎么来的"
数据库单表行数最大多大?
我们先看下单表行数理论最大值是多少。
建表的SQL是这么写的, CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT "主键", `name` varchar(100) NOT NULL DEFAULT "" COMMENT "名字", `age` int(11) NOT NULL DEFAULT "0" COMMENT "年龄", PRIMARY KEY (`id`), KEY `idx_age` (`age`) ) ENGINE=InnoDB AUTO_INCREMENT=100037 DEFAULT CHARSET=utf8;
其中id就是主键。主键本身唯一,也就是说主键的大小可以限制表的上限。
如果主键声明为 int 大小,也就是32位,那么能支持2^32-1,也就是21个亿 左右。
如果是 bigint ,那就是2^64-1,但这个数字太大 ,一般还没到这个限制之前,磁盘先受不了 。
搞离谱点。
如果我把主键声明为 tinyint ,一个字节,8位,最大2^8-1,也就是255 。 CREATE TABLE `user` ( `id` tinyint(2) unsigned NOT NULL AUTO_INCREMENT COMMENT "主键", `name` varchar(100) NOT NULL DEFAULT "" COMMENT "名字", `age` int(11) NOT NULL DEFAULT "0" COMMENT "年龄", PRIMARY KEY (`id`), KEY `idx_age` (`age`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
如果我想插入一个id=256的数据,那 就会报错 。 mysql> INSERT INTO `tmp` (`id`, `name`, `age`) VALUES (256, "", 60); ERROR 1264 (22003): Out of range value for column "id" at row 1
也就是说, tinyint 主键限制表内最多255条数据。
那除了主键,还有哪些因素会影响行数?
索引的结构
索引内部是用的B+树,这个也是八股文老股了,大家估计也背得很熟了。
为了不让大家有过于强烈的审丑疲劳,今天我尝试从另外一个角度给大家讲讲这玩意。
页的结构
假设我们有这么一张user数据表。
user表
其中id是 唯一主键 。
这看起来的一行行数据,为了方便,我们后面就叫它们 record 吧。
这张表看起来就跟个excel表格一样。excel的数据在硬盘上是一个xx.excel的文件。
而上面user表数据,在硬盘上其实也是类似,放在了user. ibd 文件下。含义是user表的innodb data文件,专业点,又叫表空间 。
虽然在数据表里,它们看起来是挨在一起的。但实际上在user.ibd里他们被分成很多小份的 数据页 ,每份大小16k。
类似于下面这样。
ibd文件内部有大量的页
我们把视角聚焦一下,放到页上面。
整个页 16k ,不大,但record这么多,一页肯定放不下,所以会分开放到很多页里。并且这16k,也不可能全用来放record对吧。
因为record们被分成好多份,放到好多页里了,为了唯一标识具体是哪一页,那就需要引入 页号 (其实是一个表空间的地址偏移量)。同时为了把这些数据页给关联起来,于是引入了前后指针 ,用于指向前后的页。这些都被加到了页头 里。
页是需要读写的,16k说小也不小,写一半电源线被拔了也是有可能发生的,所以为了保证数据页的正确性,还引入了校验码。这个被加到了 页尾 。
那剩下的空间,才是用来放我们的record的。而record如果行数特别多的话,进入到页内时挨个遍历,效率也不太行,所以为这些数据生成了一个 页目录 ,具体实现细节不重要。只需要知道,它可以通过二分查找 的方式将查找效率从O(n) 变成O(lgn) 。
页结构
从页到索引
如果想查一条record,我们可以把表空间里每一页都捞出来,再把里面的record捞出来挨个判断是不是我们要找的。
行数量小的时候,这么操作也没啥问题。
行数量大了,性能就慢了 ,于是为了加速搜索,我们可以在每个数据页里选出主键id最小 的record,而且只需要它们的主键id和所在页的页号 。组成新的record ,放入到一个新生成的一个数据页中,这个新数据页跟之前的页结构没啥区别,而且大小还是16k。
但为了跟之前的数据页进行区分。数据页里加入了 页层级(page level) 的信息,从0开始往上算。于是页与页之间就有了上下层级 的概念,就像下面这样。
两层B+树结构
突然页跟页之间看起来就像是一棵倒过来的树了。也就是我们常说的 B+树 索引。
最下面那一层, page level 为0 ,也就是所谓的叶子结点 ,其余都叫非叶子结点 。
上面展示的是 两层 的树,如果数据变多了,我们还可以再通过类似的方法,再往上构建一层。就成了三层 的树。
三层B+树结构
那现在我们就可以通过这样一棵B+树加速查询。举个例子。
比方说我们想要查找行数据5。会先从顶层页的record们入手。 record里包含了主键id和页号(页地址) 。看下图黄色的箭头,向左最小id是1,向右最小id是7。那id=5的数据如果存在,那必定在左边箭头。于是顺着的record的页地址就到了6号 数据页里,再判断id=5>4,所以肯定在右边的数据页里,于是加载105号 数据页。在数据页里找到id=5的数据行,完成查询。
B+树查询过程
另外需要注意的是,上面的页的页号并不是连续的,它们在磁盘里也不一定是挨在一起的。
这个过程中查询了三个页,如果这三个页都在磁盘中(没有被提前加载到内存中),那么 最多 需要经历三次磁盘IO查询 ,它们才能被加载到内存中。
B+树承载的记录数量
从上面的结构里可以看出B+树的 最末级叶子结点 里放了record数据。而非叶子结点 里则放了用来加速查询的索引数据。
也就是说
同样一个16k的页,非叶子节点里每一条数据都指向一个新的页,而新的页有两种可能。 如果是末级叶子节点的话,那么里面放的就是一行行record数据。 如果是非叶子节点,那么就会循环继续指向新的数据页。
假设 非叶子结点内指向其他内存页的指针数量为 x 叶子节点内能容纳的record数量为 y B+树的层数为 z
总行数的计算方法
那这棵B+树放的 行数据总量 等于 (x ^ (z-1)) * y 。
x怎么算
我们回去看数据页的结构。
页结构
非叶子节点里主要放索引查询相关的数据,放的是主键和指向页号。
主键假设是 bigint(8Byte) ,而页号在源码里叫FIL_PAGE_OFFSET(4Byte) ,那么非叶子节点里的一条数据是12Byte 左右。
整个数据页 16k , 页头页尾那部分数据全加起来大概128Byte ,加上页目录毛估占1k 吧。那剩下的15k 除以12Byte ,等于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行数据。
如果我单行数据用不了这么多,比如只用了 250byte 。那么单个数据页能放60行数据。
那同样是三层B+树,单表支持的行数就是 (1280 ^ (3-1)) * 60 1个亿 。
你看我一个亿的数据,其实也就三层B+树,在这个B+树里要查到某行数据,最多也是三次磁盘IO。所以并不慢。
这就很好的解释了文章开头,为什么我单表1个亿,但查询性能没啥大毛病。
B树承载的记录数量
既然都聊到这里了,我们就顺着这个话题多聊一些吧。
我们都知道,现在mysql的索引都是B+树,而有一种树,跟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的索引。
总结B+树叶子和非叶子结点的数据页都是16k,且数据结构一致,区别在于叶子节点放的是真实的行数据,而非叶子结点放的是主键和下一个页的地址。 B+树一般有两到三层,由于其高扇出,三层就能支持2kw以上的数据,且一次查询最多1~3次磁盘IO,性能也还行。 存储同样量级的数据,B树比B+树层级更高,因此磁盘IO也更多,所以B+树更适合成为mysql索引。 索引结构不会影响单表最大行数,2kw也只是推荐值,超过了这个值可能会导致B+树层级更高,影响查询性能。 单表最大值还受主键大小和磁盘大小限制。
参考资料
《MYSQL内核:INNODB存储引擎 卷1》
最后
虽然我在单表里塞了1亿条数据,但这个操作的前提是,我很清楚这不会太影响性能。
这波解释,毫无破绽,无懈可击。
到这里,连我自己都被自己说服了。想必实习生也是。
可恶,这该死的毒瘤竟然有些"知识渊博"。
最近原创更文的阅读量稳步下跌,思前想后,夜里辗转反侧。
我有个不成熟的请求。
离开广东好长时间了,好久没人叫我靓仔了。
大家可以在 评论区 里,叫我一靓仔吗?
我这么善良质朴的愿望,能被满足吗?
如果实在叫不出口的话,能帮我点下右下角的 点赞和在看 吗?
别说了,一起在知识的海洋里呛水吧
手游联运系统包括什么功能?一个完整的手游联运系统一般有PC官网WAP官网APP游戏盒子手游SDK推广员系统推广助手后台管理系统等六大五大基本功能。这六大基本功能围绕以下板块实现一PC官网WAP官网品牌宣传以
索尼Xperia5IV再曝支持NFC无线充电今年5月,索尼正式发布了旗下的Xperia10IV手机。据悉,Xperia1IV延续一贯的索尼风格,搭载骁龙8,内置5000mAh电池支持30W快速充电功能。不过,除了大家熟悉的X
索尼PS5官宣涨价,国行数字版光驱版改为3499元4299元IT之家8月25日消息,索尼今日宣布,将在部分市场上调PS5主机价格。新的推荐零售价将针对欧洲中东和非洲亚太地区和拉丁美洲地区以及加拿大的部分市场推出。索尼强调,美国不会涨价。除非
虽然真无线耳机大行其道,但我还是投这款索尼颈挂式一票虽然现在真无线耳机已经成为主流了,但是也有不少朋友表示早八人拥挤的地铁伤不起!地铁出来耳机就剩一个了。其实现在做的比较好的真无线耳机佩戴起来已经相当稳固了,但是有部分人会觉得在拥挤
欧冠32强出炉,抽签分档已定欧冠附加赛总比分哥本哈根2比1特拉布宗体育流浪者3比2埃因霍温萨格勒布迪那摩1比1波多格林特(加时赛4比1)皮尔森2比1卡拉巴克本菲卡5比0基辅迪那摩海法马卡比5比4贝尔格莱德红星
秦志戬回应国乒世乒赛名单争议!林高远又掉链子,关键时刻就输球日前,国乒终于公布了参加成都团体世乒赛的名单,男队4人确定入选,分别是樊振东马龙梁靖崑和王楚钦,而还剩下一个名额悬而未决,需要由6名队员通过队内比赛来争夺。这一名单还是引发了一些争
一则突发消息,东北药茅瞬间飙升7!五部委出手多只A股立马涨停中国基金报记者李智今天的A股涨跌不一,上证指数午盘涨0。41,深成指创业板指分别跌0。230。51。板块方面,海运燃气煤炭等表现强势。而新能源产业链全线熄火,光伏新能源等赛道股持续
同样是穿律师袍,把袁泉和刘亦菲放在一起,差别就出来了在影视剧中,扮演律师的女星很多,各种不同的服装造型也为人津津乐道。穿上律师袍或清冷孤傲,或娇俏迷人,或专业指数拉满,百花争艳,不过放在一起对比也就出来了。玫瑰之战可以说是2022年
她被琼瑶钦定为林青霞的接班人,美貌可与林青霞比肩80年代,台湾影坛有四大美人林青霞胡因梦胡慧中周丹薇,她们惊艳了时光温柔了岁月。还有一位堪称绝色的女明星,更被琼瑶钦定为林青霞的接班人。她,黑发如瀑气质如兰,美貌可与青霞比肩,更弹
朱小伟陈萌正式拍婚纱照!甜蜜揽腰眼神有爱,女方颜值胜过陈亚男早前,网上曝光了一组陈萌与朱小伟的婚纱照,不过明眼人一看就是P图,因为这组婚纱照是朱小伟和陈亚男的!虽说婚纱照是P图,但却释放了一个很重要的信息,那就是陈萌与朱小伟已经开始筹备婚礼
中兴远航30S手机宣布将于8月30日开售IT之家8月16日消息,中兴商城近日发布了最新一代的中兴远航30S手机,其中4GB128GB售价1199元,6GB128GB售价1399元,中兴手机宣布,这款新手机将在8月30日开
欧冠32强排定座次C罗下家云遮雾绕8月25日凌晨,20222023欧冠附加赛结束了第二回合的争夺,本赛季欧冠32支豪强最终浮出水面,围绕着金光闪耀的圣博莱德杯(欧冠奖杯),即将展开群雄逐鹿,欧罗巴大陆硝烟渐浓。纵观
CBA三热点国际篮联赞赵睿,国家队15人名单,郑准再战一年1中国男篮已经抵达哈萨克斯坦参加世预赛第二阶段比赛,国际篮联也是为比赛造势,昨日连发了两条微博,前面一条发的是赵睿比赛的视频,同时发文称赵睿是六边形战士,还说赵睿能否延续火热手感,
离间计!杜兰特争夺战后续,球星认清现实,四支球队被严重拖累所谓离间计,必须是有间可离,假如敌人密不可分,那么离间计是无法使用的。离间计原指使敌人的间谍为我所用,或使敌人获取假情报,而有利于自己的计策。后指用计谋离间敌人引起内讧。库里夺得F
中关村石景山园全区产业转型和发展的主战场近年来,中关村石景山园围绕现代金融科技服务新一代信息技术数字创意商务服务等高精尖产业,集聚上市企业19家独角兽企业6家市专精特新企业109家国高新企业794家。按照市政府一区十六园
唯品会Q2铁粉为何逆势增长?业内人士揭露真相电商财报季,唯品会的答卷已经出来了。在今年二季度,唯品会净营收245亿元,归属于公司股东的NonGAAP净利润16亿元。相比阿里京东这样的超级巨头,唯品会体量稍小但胜在表现稳定。之
当下中国地产已走入夜壶效应回顾自2019年以来的9次LPR降息中,仅有两次5年期下调幅度超过1年期,为2022年5月2022年8月。通过长端LPR的调降可以引导房贷利率进一步下降,有较强的稳地产意图。从今年
任正非告诉你什么叫行业风向标,股市一片绿,汽车行业重创行业风向标有多厉害?近日,华为CEO任正非一番对未来前景的预测,便给股市带来起伏便。可以说一番言论,便影响了整个股市!沪指跌近2,深成指跌近3,创业板指数更是跌超3,4300多只个
孕期谣言大盘点,这些你都信了吗?生育本是一件高兴的事情。但是正所谓人多嘴杂,一代又一代传下来关于孕期的种种谣言,相信各位孕妈妈们都听说过不少。今天给大家整理一些关于孕期的谣言,大家看看有没有中招哦。01女性酸碱度
北上资金最大买入100股名单出炉2022年8月25日081153501hr301051hr66。118。498。17万539。97万0。410。10100。007000。000。130。000。00玻璃玻纤502
尾盘前30分钟,主力资金最大卖出这100股名单若不喜欢看表格,输入任何数字进入底部图片区域。收盘前30分钟,主力资金大幅卖出最多的100股时间2022年8月25日142516序号代码名称最新价涨跌幅主力净流入主力净占比1hr0
北上资金最大卖出100股曝光2022825081403516hr000028hr28。841。06701。45万2。02亿1。911。647500。0021。62万0。110。020。02医药商业517hr0