数据库单表行数最大多大？
巧了。
我也听说过。
但我不接受它的建议，硬是单表装了1亿条数据。
这时候，我们组里新来的实习生看到了之后，天真无邪的问我："单表不是建议最大两千万吗？为什么这个表都放了1个亿还不分库分表"？
我能说我是因为懒吗？我当初设计时哪里想到这表竟然能涨这么快。。。
我不能。
说了等于承认自己是开发组里的毒瘤，虽然我确实是，但我不能承认。
我如坐针毡，如芒刺背，如鲠在喉。
开始了一波骚操作。
"我这么做是有道理的"
"虽然这个表很大，但你有没有发现它查询其实还是很快"
"这个2kw是个建议值，我们要来看下这个2kw是怎么来的"
数据库单表行数最大多大？
我们先看下单表行数理论最大值是多少。
建表的SQL是这么写的，
CREATETABLE`user`(
`id`int(10)unsignedNOTNULLAUTO_INCREMENTCOMMENT'主键',
`name`varchar(100)NOTNULLDEFAULT''COMMENT'名字',
`age`int(11)NOTNULLDEFAULT'0'COMMENT'年龄',
PRIMARYKEY(`id`),
KEY`idx_age`(`age`)
)ENGINE=InnoDBAUTO_INCREMENT=100037DEFAULTCHARSET=utf8;
其中id就是主键。主键本身唯一，也就是说主键的大小可以限制表的上限。
如果主键声明为int大小，也就是32位，那么能支持2^32-1，也就是21个亿左右。
如果是bigint，那就是2^64-1，但这个数字太大，一般还没到这个限制之前，磁盘先受不了。
搞离谱点。
如果我把主键声明为 tinyint，一个字节，8位，最大2^8-1，也就是255。
CREATETABLE`user`(
`id`tinyint(2)unsignedNOTNULLAUTO_INCREMENTCOMMENT'主键',
`name`varchar(100)NOTNULLDEFAULT''COMMENT'名字',
`age`int(11)NOTNULLDEFAULT'0'COMMENT'年龄',
PRIMARYKEY(`id`),
KEY`idx_age`(`age`)
)ENGINE=InnoDBAUTO_INCREMENT=0DEFAULTCHARSET=utf8;
如果我想插入一个id=256的数据，那就会报错。
mysql>INSERTINTO`tmp`(`id`,`name`,`age`)VALUES(256,'',60);
ERROR1264(22003):Outofrangevalueforcolumn'id'atrow1
也就是说，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+树加速查询。举个例子。
比方说我们想要查找行数据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
行数超一亿就慢了吗？
上面假设单行数据用了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亿条数据，但这个操作的前提是，我很清楚这不会太影响性能。
这波解释，毫无破绽，无懈可击。
到这里，连我自己都被自己说服了。想必实习生也是。
可恶，这该死的毒瘤竟然有些"知识渊博"。
审核编辑 ：李倩
-
SQL
+关注
关注
1文章
762浏览量
44114 -
数据库
+关注
关注
7文章
3794浏览量
64352
原文标题：为什么大家说 mysql 数据库单表最大两千万？依据是啥？
文章出处：【微信号：DBDevs，微信公众号：数据分析与开发】欢迎添加关注！文章转载请注明出处。
发布评论请先 登录
相关推荐
如何使用cmp进行数据库管理的技巧
数据库数据恢复—Mysql数据库表记录丢失的数据恢复流程
数据库事件触发的设置和应用
数据库数据恢复—MYSQL数据库ibdata1文件损坏的数据恢复案例
Sybase数据恢复—Sybase数据库无法启动怎么恢复数据？
如何使用SQL进行数据分析
数据库数据恢复—通过拼接数据库碎片恢复SQLserver数据库
Oracle数据恢复—异常断电后Oracle数据库启库报错的数据恢复案例
数据库数据恢复—SQL Server数据库出现823错误的数据恢复案例
软件系统数据库的分库分表设计
数据库数据恢复—SqlServer数据库底层File Record被截断为0的数据恢复案例
数据库数据恢复—SQL Server数据库所在分区空间不足报错的数据恢复案例
数据库数据恢复—raid5阵列上层Sql Server数据库数据恢复案例
通过Modbus读写数据库中的数据
【数据库数据恢复】Oracle数据库ASM实例无法挂载的数据恢复案例
工商网监
评论