您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

为什么大家说mysql数据库单表最大两千万?依据是啥?

时间:2023-03-03 13:57:36  来源:  作者:IT168企业级

想必大家也听说过数据库单表建议最大2kw条数据这个说法。如果超过了,性能就会下降得比较厉害。

巧了。

我也听说过。

但我不接受它的建议,硬是单表装了1亿条数据。

这时候,我们组里新来的实习生看到了之后,天真无邪的问我:"单表不是建议最大两千万吗?为什么这个表都放了1个亿还不分库分表"?

我能说我是因为懒吗?我当初设计时哪里想到这表竟然能涨这么快。。。

我不能。

说了等于承认自己是开发组里的毒瘤,虽然我确实是,但我不能承认

我如坐针毡,如芒刺背,如鲠在喉。

开始了一波骚操作。

"我这么做是有道理的"

"虽然这个表很大,但你有没有发现它查询其实还是很快"

"这个2kw是个建议值,我们要来看下这个2kw是怎么来的"

数据库单表行数最大多大?

我们先看下单表行数理论最大值是多少。

建表的SQL是这么写的,

其中id就是主键。主键本身唯一,也就是说主键的大小可以限制表的上限。

如果主键声明为大小,也就是32位,那么能支持2^32-1,也就是21个亿左右。

如果是,那就是2^64-1,但这个数字太大,一般还没到这个限制之前,磁盘先受不了

搞离谱点。

如果我把主键声明为 ,一个字节,8位,最大2^8-1,也就是。

如果我想插入一个id=256的数据,那就会报错

也就是说,主键限制表内最多255条数据。

那除了主键,还有哪些因素会影响行数?

索引的结构

索引内部是用的B+树,这个也是八股文老股了,大家估计也背得很熟了。

为了不让大家有过于强烈的审丑疲劳,今天我尝试从另外一个角度给大家讲讲这玩意。

页的结构

假设我们有这么一张user数据表。

user表

其中id是唯一主键

这看起来的一行行数据,为了方便,我们后面就叫它们record吧。

这张表看起来就跟个Excel表格一样。excel的数据在硬盘上是一个xx.excel的文件。

而上面user表数据,在硬盘上其实也是类似,放在了user.ibd文件下。含义是user表的innodb data文件,专业点,又叫表空间

虽然在数据表里,它们看起来是挨在一起的。但实际上在user.ibd里他们被分成很多小份的数据页,每份大小16k。

类似于下面这样。

ibd文件内部有大量的页

我们把视角聚焦一下,放到页上面。

整个页,不大,但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的页地址就到了数据页里,再判断id=5>4,所以肯定在右边的数据页里,于是加载数据页。在数据页里找到id=5的数据行,完成查询。

B+树查询过程

另外需要注意的是,上面的页的页号并不是连续的,它们在磁盘里也不一定是挨在一起的。

这个过程中查询了三个页,如果这三个页都在磁盘中(没有被提前加载到内存中),那么最多需要经历三次磁盘IO查询,它们才能被加载到内存中。

B+树承载的记录数量

从上面的结构里可以看出B+树的最末级叶子结点里放了record数据。而非叶子结点里则放了用来加速查询的索引数据。

也就是说

同样一个16k的页,非叶子节点里每一条数据都指向一个新的页,而新的页有两种可能。

如果是末级叶子节点的话,那么里面放的就是一行行record数据。

如果是非叶子节点,那么就会循环继续指向新的数据页。

假设

非叶子结点内指向其他内存页的指针数量为

叶子节点内能容纳的record数量为

B+树的层数为

总行数的计算方法

那这棵B+树放的行数据总量等于 。

x怎么算

我们回去看数据页的结构。

页结构

非叶子节点里主要放索引查询相关的数据,放的是主键和指向页号。

主键假设是,而页号在源码里叫,那么非叶子节点里的一条数据是左右。

整个数据页, 页头页尾那部分数据全加起来大概,加上页目录毛估占吧。那剩下的15k除以,等于,也就是可以指向x=1280页

我们常说的二叉树指的是一个结点可以发散出两个新的结点。m叉树一个节点能指向m个新的结点。这个指向新节点的操作就叫扇出(fanout)

而上面的B+树,它能指向1280个新的节点,恐怖如斯,可以说扇出非常高了。

y的计算

叶子节点和非叶子节点的数据结构是一样的,所以也假设剩下可以发挥。

叶子节点里放的是真正的行数据。假设一条行数据,所以一页里能放y=15行

行总数计算

回到 这个公式。

已知,。

假设B+树是两层,那。则是

假设B+树是三层,那。则是

这个2.5kw,就是我们常说的单表建议最大行数2kw的由来。毕竟再加一层,数据就大得有点离谱了。三层数据页对应最多三次磁盘IO,也比较合理。

行数超一亿就慢了吗?

上面假设单行数据用了1kb,所以一个数据页能放个15行数据。

如果我单行数据用不了这么多,比如只用了。那么单个数据页能放60行数据。

那同样是三层B+树,单表支持的行数就是 。

你看我一个亿的数据,其实也就三层B+树,在这个B+树里要查到某行数据,最多也是三次磁盘IO。所以并不慢。

这就很好的解释了文章开头,为什么我单表1个亿,但查询性能没啥大毛病。

B树承载的记录数量

既然都聊到这里了,我们就顺着这个话题多聊一些吧。

我们都知道,现在MySQL的索引都是B+树,而有一种树,跟B+树很像,叫B树,也叫B-树

它跟B+树最大的区别在于,B+树只在末级叶子结点处放数据表行数据,而B树则会在叶子和非叶子结点上都放。

于是,B树的结构就类似这样

B树结构

B树将行数据都存在非叶子节点上,假设每个数据页还是16kb,掐头去尾每页剩15kb,并且一条数据表行数据还是占1kb,就算不考虑各种页指针的情况下,也只能放个15条数据。数据页扇出明显变少了。

计算可承载的总行数的公式也变成了一个等比数列

其中z还是层数的意思。

为了能放左右的数据,需要。也就是树需要有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》



Tags:mysql   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,不构成投资建议。投资者据此操作,风险自担。如有任何标注错误或版权侵犯请与我们联系,我们将及时更正、删除。
▌相关推荐
MySQL 核心模块揭秘
server 层会创建一个 SAVEPOINT 对象,用于存放 savepoint 信息。binlog 会把 binlog offset 写入 server 层为它分配的一块 8 字节的内存里。 InnoDB 会维护自己的 savepoint...【详细内容】
2024-04-03  Search: mysql  点击:(6)  评论:(0)  加入收藏
MySQL 核心模块揭秘,你看明白了吗?
为了提升分配 undo 段的效率,事务提交过程中,InnoDB 会缓存一些 undo 段。只要同时满足两个条件,insert undo 段或 update undo 段就能被缓存。1. 关于缓存 undo 段为了提升分...【详细内容】
2024-03-27  Search: mysql  点击:(11)  评论:(0)  加入收藏
MySQL:BUG导致DDL语句无谓的索引重建
对于5.7.23之前的版本在评估类似DDL操作的时候需要谨慎,可能评估为瞬间操作,但是实际上线的时候跑了很久,这个就容易导致超过维护窗口,甚至更大的故障。一、问题模拟使用5.7.22...【详细内容】
2024-03-26  Search: mysql  点击:(10)  评论:(0)  加入收藏
从 MySQL 到 ByteHouse,抖音精准推荐存储架构重构解读
ByteHouse是一款OLAP引擎,具备查询效率高的特点,在硬件需求上相对较低,且具有良好的水平扩展性,如果数据量进一步增长,可以通过增加服务器数量来提升处理能力。本文将从兴趣圈层...【详细内容】
2024-03-22  Search: mysql  点击:(24)  评论:(0)  加入收藏
MySQL自增主键一定是连续的吗?
测试环境:MySQL版本:8.0数据库表:T (主键id,唯一索引c,普通字段d)如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不...【详细内容】
2024-03-10  Search: mysql  点击:(6)  评论:(0)  加入收藏
准线上事故之MySQL优化器索引选错
1 背景最近组里来了许多新的小伙伴,大家在一起聊聊技术,有小兄弟提到了MySQL的优化器的内部策略,想起了之前在公司出现的一个线上问题,今天借着这个机会,在这里分享下过程和结论...【详细内容】
2024-03-07  Search: mysql  点击:(28)  评论:(0)  加入收藏
MySQL数据恢复,你会吗?
今天分享一下binlog2sql,它是一款比较常用的数据恢复工具,可以通过它从MySQL binlog解析出你要的SQL,并根据不同选项,可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。主要...【详细内容】
2024-02-22  Search: mysql  点击:(45)  评论:(0)  加入收藏
如何在MySQL中实现数据的版本管理和回滚操作?
实现数据的版本管理和回滚操作在MySQL中可以通过以下几种方式实现,包括使用事务、备份恢复、日志和版本控制工具等。下面将详细介绍这些方法。1.使用事务:MySQL支持事务操作,可...【详细内容】
2024-02-20  Search: mysql  点击:(53)  评论:(0)  加入收藏
为什么高性能场景选用Postgres SQL 而不是 MySQL
一、 数据库简介 TLDR;1.1 MySQL MySQL声称自己是最流行的开源数据库,它属于最流行的RDBMS (Relational Database Management System,关系数据库管理系统)应用软件之一。LAMP...【详细内容】
2024-02-19  Search: mysql  点击:(38)  评论:(0)  加入收藏
MySQL数据库如何生成分组排序的序号
经常进行数据分析的小伙伴经常会需要生成序号或进行数据分组排序并生成序号。在MySQL8.0中可以使用窗口函数来实现,可以参考历史文章有了这些函数,统计分析事半功倍进行了解。...【详细内容】
2024-01-30  Search: mysql  点击:(54)  评论:(0)  加入收藏
▌简易百科推荐
MySQL 核心模块揭秘
server 层会创建一个 SAVEPOINT 对象,用于存放 savepoint 信息。binlog 会把 binlog offset 写入 server 层为它分配的一块 8 字节的内存里。 InnoDB 会维护自己的 savepoint...【详细内容】
2024-04-03  爱可生开源社区    Tags:MySQL   点击:(6)  评论:(0)  加入收藏
MySQL 核心模块揭秘,你看明白了吗?
为了提升分配 undo 段的效率,事务提交过程中,InnoDB 会缓存一些 undo 段。只要同时满足两个条件,insert undo 段或 update undo 段就能被缓存。1. 关于缓存 undo 段为了提升分...【详细内容】
2024-03-27  爱可生开源社区  微信公众号  Tags:MySQL   点击:(11)  评论:(0)  加入收藏
MySQL:BUG导致DDL语句无谓的索引重建
对于5.7.23之前的版本在评估类似DDL操作的时候需要谨慎,可能评估为瞬间操作,但是实际上线的时候跑了很久,这个就容易导致超过维护窗口,甚至更大的故障。一、问题模拟使用5.7.22...【详细内容】
2024-03-26  MySQL学习  微信公众号  Tags:MySQL   点击:(10)  评论:(0)  加入收藏
从 MySQL 到 ByteHouse,抖音精准推荐存储架构重构解读
ByteHouse是一款OLAP引擎,具备查询效率高的特点,在硬件需求上相对较低,且具有良好的水平扩展性,如果数据量进一步增长,可以通过增加服务器数量来提升处理能力。本文将从兴趣圈层...【详细内容】
2024-03-22  字节跳动技术团队    Tags:ByteHouse   点击:(24)  评论:(0)  加入收藏
MySQL自增主键一定是连续的吗?
测试环境:MySQL版本:8.0数据库表:T (主键id,唯一索引c,普通字段d)如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不...【详细内容】
2024-03-10    dbaplus社群  Tags:MySQL   点击:(6)  评论:(0)  加入收藏
准线上事故之MySQL优化器索引选错
1 背景最近组里来了许多新的小伙伴,大家在一起聊聊技术,有小兄弟提到了MySQL的优化器的内部策略,想起了之前在公司出现的一个线上问题,今天借着这个机会,在这里分享下过程和结论...【详细内容】
2024-03-07  转转技术  微信公众号  Tags:MySQL   点击:(28)  评论:(0)  加入收藏
MySQL数据恢复,你会吗?
今天分享一下binlog2sql,它是一款比较常用的数据恢复工具,可以通过它从MySQL binlog解析出你要的SQL,并根据不同选项,可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。主要...【详细内容】
2024-02-22  数据库干货铺  微信公众号  Tags:MySQL   点击:(45)  评论:(0)  加入收藏
如何在MySQL中实现数据的版本管理和回滚操作?
实现数据的版本管理和回滚操作在MySQL中可以通过以下几种方式实现,包括使用事务、备份恢复、日志和版本控制工具等。下面将详细介绍这些方法。1.使用事务:MySQL支持事务操作,可...【详细内容】
2024-02-20  编程技术汇    Tags:MySQL   点击:(53)  评论:(0)  加入收藏
MySQL数据库如何生成分组排序的序号
经常进行数据分析的小伙伴经常会需要生成序号或进行数据分组排序并生成序号。在MySQL8.0中可以使用窗口函数来实现,可以参考历史文章有了这些函数,统计分析事半功倍进行了解。...【详细内容】
2024-01-30  数据库干货铺  微信公众号  Tags:MySQL   点击:(54)  评论:(0)  加入收藏
mysql索引失效的场景
MySQL中索引失效是指数据库查询时无法有效利用索引,这可能导致查询性能显著下降。以下是一些常见的MySQL索引失效的场景:1.使用非前导列进行查询: 假设有一个复合索引 (A, B)。...【详细内容】
2024-01-15  小王爱编程  今日头条  Tags:mysql索引   点击:(85)  评论:(0)  加入收藏
站内最新
站内热门
站内头条