阿里:MySQL 单表数据最大不要超过多少行?为什么?

【MySQL】阿里:MySQL 单表数据最大不要超过多少行?为什么? B+树叶子和非叶子结点的数据页都是16k,且数据结构一致,区别在于叶子节点放的是真实的行数据,而非叶子结点放的是主键和下一个页的地址。B+树一般有两到三层,由于其高扇出,三层就能支持2kw以上的数据,且一次查询最多1~3次磁盘IO,性能也还行。存储同样量级的数据,B树比B+树层级更高,因此磁盘IO也更多,所以B+树更适合成为mysql索引。索引结构不会影响单表最大行数,2kw也只是推荐值,超过了这个值可能会导致B+树层级更高,影响查询性能。单表最大值还受主键大小和磁盘大小限制。 阅读详情

点击上方“芋道源码”,选择“设为星标”

管她前浪,还是后浪?

能浪的浪,才是好浪!

每天 10:33 更新文章,每天掉亿点点头发...

源码精品专栏

 

来源:小白debug

80e84d968a9bfd2f75759e5d542d65e3.jpeg


故事从好多年前说起。

想必大家也听说过数据库单表建议最大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条数据。

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

基于 Spring Boot + MyBatis Plus + Vue & Element 实现的后台管理系统 + 用户小程序,支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能

  • 项目地址:https://github.com/YunaiV/ruoyi-vue-pro

  • 视频教程:https://doc.iocoder.cn/video/

索引的结构

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

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

页的结构

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

07481f8da5553688ce8b78b69d48bfdd.png
user表

其中id是唯一主键 。

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

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

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

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

类似于下面这样。

b9ffa03b02051bc1e74c744b5f4eb8ca.png
ibd文件内部有大量的页

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

整个页16k,不大,但record这么多,一页肯定放不下,所以会分开放到很多页里。并且这16k,也不可能全用来放record对吧。

因为record们被分成好多份,放到好多页里了,为了唯一标识具体是哪一页,那就需要引入页号 (其实是一个表空间的地址偏移量)。同时为了把这些数据页给关联起来,于是引入了前后指针 ,用于指向前后的页。这些都被加到了页头 里。

页是需要读写的,16k说小也不小,写一半电源线被拔了也是有可能发生的,所以为了保证数据页的正确性,还引入了校验码。这个被加到了页尾 。

那剩下的空间,才是用来放我们的record的。而record如果行数特别多的话,进入到页内时挨个遍历,效率也不太行,所以为这些数据生成了一个页目录 ,具体实现细节不重要。只需要知道,它可以通过二分查找 的方式将查找效率从O(n) 变成O(lgn) 。

687608c839c186283c1463bfe7c4c0fe.png
页结构

从页到索引

如果想查一条record,我们可以把表空间里每一页都捞出来,再把里面的record捞出来挨个判断是不是我们要找的。

行数量小的时候,这么操作也没啥问题。

行数量大了,性能就慢了 ,于是为了加速搜索,我们可以在每个数据页里选出主键id最小 的record,而且只需要它们的主键id和所在页的页号 。组成新的record ,放入到一个新生成的一个数据页中,这个新数据页跟之前的页结构没啥区别,而且大小还是16k。

但为了跟之前的数据页进行区分。数据页里加入了*页层级(page level)** 的信息,从0开始往上算。于是页与页之间就有了*上下层级 的概念,就像下面这样。

850ceb654f02bccde79ecd0514a376f4.png
两层B+树结构

突然页跟页之间看起来就像是一棵倒过来的树了。也就是我们常说的B+树 索引。

最下面那一层,page level 为0 ,也就是所谓的叶子结点 ,其余都叫非叶子结点 。

上面展示的是两层 的树,如果数据变多了,我们还可以再通过类似的方法,再往上构建一层。就成了三层 的树。

91a5e85f740869e59fd69edb4ded5748.png
三层B+树结构

那现在我们就可以通过这样一棵B+树加速查询。举个例子。

比方说我们想要查找行数据5。会先从顶层页的record们入手。record里包含了主键id和页号(页地址) 。看下图黄色的箭头,向左最小id是1,向右最小id是7。那id=5的数据如果存在,那必定在左边箭头。于是顺着的record的页地址就到了6号数据页里,再判断id=5>4,所以肯定在右边的数据页里,于是加载105号数据页。在数据页里找到id=5的数据行,完成查询。

f932c09f8924dcc5278d511bf1d168bc.png
B+树查询过程

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

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

基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 实现的后台管理系统 + 用户小程序,支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能

  • 项目地址:https://github.com/YunaiV/yudao-cloud

  • 视频教程:https://doc.iocoder.cn/video/

B+树承载的记录数量

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

也就是说

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

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

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

假设

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

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

  • B+树的层数为z

b193e861820e631271a9a55fc986f84b.png
总行数的计算方法

那这棵B+树放的行数据总量 等于 (x ^ (z-1)) * y。

x怎么算

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

d2263a0e5263ff3d493995674a35853f.png
页结构

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

主键假设是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树的结构就类似这样

2f7cf6b3e1f636df2e70b31e4b93af6f.png
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亿条数据,但这个操作的前提是,我很清楚这不会太影响性能。

这波解释,毫无破绽,无懈可击。

到这里,连我自己都被自己说服了。想必实习生也是。

可恶,这该死的毒瘤竟然有些"知识渊博"。



欢迎加入我的知识星球,一起探讨架构,交流源码。加入方式,长按下方二维码噢:

c0dda21a64a728eb5461c651ea1edeb0.png

已在知识星球更新源码解析如下:

a1c00bcaabd869c0e17a8f11d6a096ae.jpeg

9247bf090fd5b2d40c54e8fa0e5dbec6.jpeg

b63cec07e8a9c1eaa2494400d89ff41e.jpeg

1ee996ce795538c7c7ce76dfa18b370b.jpeg

最近更新《芋道 SpringBoot 2.X 入门》系列,已经 101 余篇,覆盖了 MyBatis、Redis、MongoDB、ES、分库分表、读写分离、SpringMVC、Webflux、权限、WebSocket、Dubbo、RabbitMQ、RocketMQ、Kafka、性能测试等等内容。

提供近 3W 行代码的 SpringBoot 示例,以及超 4W 行代码的电商微服务项目。

获取方式:点“在看”,关注公众号并回复 666 领取,更多内容陆续奉上。

文章有帮助的话,在看,转发吧。
谢谢支持哟 (*^__^*)
高速SerDes均衡技术解析 均衡技术是高速SerDes链路中补偿信道损耗和失真的核心技术,通过FFE、CTLE和DFE三种主要方法对抗码间干扰(ISI)。FFE在发射端预补偿前光标干扰,CTLE通过提升高频增益补偿信道损耗,DFE则精准消除后光标干扰。三种技术各具优势:FFE实现简单且不放大噪声,CTLE功耗低易于集成,DFE能自适应调整且保持信噪比。这些技术协同工作可有效提升系统传输速率和可靠性。 阅读详情

相关推荐

SAP S/4HANA 物料分类账(ML)/实际成本核算(Actual Costing)——底表全景 & CKMLCP 执行后数据变化

插入 ML 重估凭证行项目:Dr/Cr 库存科目(期末库存调整)、Dr/Cr COGS/消耗科目(消耗重估)、Cr/Dr ML 重估抵消科目(PRY/PRV 类逻辑)also STPRS/VERPR/PEINH/LBKUM 等(但 S/4 很多“真理”在 ACDOCA)MLPP / MLPPF / MLCR / MLCRF / MLCD / CKMLMV003/004 等。Material Ledger Document(行项目明细:收货/发票价差/结算写入/重估相关)间接(PUP在CKMLCR;

weixin_52203666的博客 588

mysql的b+树最高有几层

mysql的io就是从磁盘上读取数据写入到内存,这是一次io操作,从内存中读取数据写入到磁盘,也是一次io操作。

qq_21277357的博客 1990

高效网络流量回放:使用tcpreplay实现多目标IP的pcap重放

本文详细介绍了如何使用tcpreplay工具高效回放网络流量pcap文件,并实现多目标IP发送。通过tcprewrite编辑报文和并行回放策略,可满足安全测试、负载测试等场景需求。文章涵盖安装、基础回放、核心技巧及性能优化,帮助读者构建真实流量压力测试环境。

weixin_29297127的博客 483

Mysql的存储结构 - B+树

一次真实的美团面试经历 那么,我们来看一次笔者的一次真实美团面试,面试官关于Mysql的数据结构的提问。 面试官: Mysql的底层数据结构有了解过么? 帅航: 有,是B+树。 面试官: 为什么是B+树呢,刚才说到HashMap用的哈希表,红黑树查询效率都挺高的,为什么Mysql不用呢? 帅航: 哈希表的一次查询是很快,但是范围查询就很搓了;至于红黑树的话,由于二叉树的特性,数据量太大的情况下,树会很高,由于数据是存储在磁盘上的,这样与磁盘的IO会很频繁,是不可以接受的。所以选择B+树。 面试官: 那树高的

程序员大航子 6283

手把手教你用ROS2 Humble给PX4无人机写个“画圆”程序:从C++代码到Gazebo仿真全流程

本文详细介绍了如何使用ROS2 Humble和PX4飞控实现无人机圆形轨迹控制,涵盖从环境配置、Gazebo仿真到C++代码实现的完整流程。通过实战教程,读者将掌握轨迹跟踪仿真的核心技术,包括ROS2与PX4的通信接口、圆形轨迹算法设计及Gazebo仿真调试技巧。

weixin_29056781的博客 257

stm32开发3D打印机(一)——介绍(已完成)

如何运用stm32开发3D打印机

Nico_jion的博客 1万+

【面试】为什么B+树是3层

索引的底层实现是怎样的?为什么B+树是三层? 一个高度为3的B+树大概可以存放:1170*1170*16=21902400行数据。 所以在InnoDB中B+树高度一般为1-3层,它就能满足千万级的数据存储。 在查找数据时一次页的查找代表一次IO,所以通过主键索引查询通常只需要1-3次逻辑IO操作即可查找到数据。 ...

Soldier49Zed的博客 6359

手把手教你用Simulink搭建PMSM FOC控制模型(附完整仿真文件下载)

本文手把手教你使用Simulink搭建完整的永磁同步电机(PMSM)磁场定向控制(FOC)仿真模型。内容涵盖从电机与逆变器建模、核心的坐标变换(Clark/Park变换)实现、电流环与速度环PID整定,到SVPWM调制与系统集成调试的全过程,并提供关键参数设置与实用调试技巧,帮助工程师快速掌握FOC的工程实现。

silver的专栏 863

MySQL的B+树索引结构

MySQL的B+树索引结构

weixin_44224716的博客 661

Windows Server 2019整机备份与异机还原实战:从虚拟机到物理机的完整迁移指南

本文详细介绍了Windows Server 2019整机备份与异机还原的实战操作,涵盖从虚拟机到物理机的完整迁移流程。通过环境准备、备份策略设计、具体操作步骤及迁移后优化等内容,帮助用户实现系统环境的无缝迁移,特别适用于服务器硬件升级、数据中心迁移等场景。

weixin_30555125的博客 135

Jenkins离线安装保姆级教程:从下载.war包到SVN+Maven项目一键部署(附避坑脚本)

本文详细介绍了Jenkins离线安装的全流程,包括.war包下载、SVN+Maven项目配置及自动化部署。特别针对内网环境提供解决方案,涵盖插件管理、开发工具链配置、构建触发器和SSH部署等关键步骤,帮助企业在网络隔离环境下实现高效持续集成。

weixin_30096975的博客 375

mysql索引的数据结构

为什么使用索引 我们假如不使用索引的话,就像我们左边的这样,造成全文索引 加入索引的话,像我们右边的这样,那么它的速度就会快上很多。 打个比方,假如我们需要查字典的话,索引就像我们的目录一样,没有索引,我们就只能一页一页去找。而加上索引之后,我们可以根据目录来快速查找我们所需要的东西 对字段Col2添加了索引,就相当于在硬盘上为col 2维护了一个索引的数据结构,即这个二叉搜索树。二叉搜索树的每个结点存储的是(K,V)结构,key是Col 2,value是该key所在行的文件指针(地址)。比如:该二

small_engineer的博客 6770

【零基础入门unity游戏开发——unity通用篇14】unity特性相关的知识(基于unity6开发介绍)

unity特性相关的知识(基于unity6开发介绍)

向宇的博客,专注php/web全栈 unity游戏开发,欢迎大家评论纠错 1204

阿里一面:MySQL 单表数据最大不要超过多少行?为什么?

Mysql 的表数据是以页的形式存放的,页在磁盘中不一定是连续的。页的空间是 16K, 并不是所有的空间都是用来存放数据的,会有一些固定的信息,如,页头,页尾,页码,校验码等等。在 B+ 树中,叶子节点和非叶子节点的数据结构是一样的,区别在于,叶子节点存放的是实际的行数据,而非叶子节点存放的是主键和页号。索引结构不会影响单表最大行数,2kw 也只是推荐值,超过了这个值可能会导致 B + 树层级更高,影响查询性能。

IT_1024的博客 974

复分析 阿尔福斯(Ahlfors) 第三版答案 英文

答案分为十章,有部分习题没有答案,全英文复分析阿尔福斯(Ahlfors) 第三版答案

计算机控制技术及应用

《计算机控制技术及应用》通过总结作者的科研成果和吸收国内外的先进理论、方法和技术,以计算机控制系统的网络化、开放化、智能化和集成化发展趋势为主线索,主要介绍计算机控制系统的基本概念和计算机控制系统的典型形式,计算机控制系统的数学描述与基本分析方法,以及数字PID控制器、最少拍控制系统、达林算法、状态变量设计法等设计方法及其各种改进算法。重点突出计算机控制系统输入输出通道接口的设计方法和典型控制系统的设计案例与实现方法等。

阿里一面:MySQL 单表数据最大不要超过多少行?为什么?这样回答满分!

Mysql 的表数据是以页的形式存放的,页在磁盘中不一定是连续的。页的空间是 16K, 并不是所有的空间都是用来存放数据的,会有一些固定的信息,如,页头,页尾,页码,校验码等等。在 B+ 树中,叶子节点和非叶子节点的数据结构是一样的,区别在于,叶子节点存放的是实际的行数据,而非叶子节点存放的是主键和页号。索引结构不会影响单表最大行数,2kw 也只是推荐值,超过了这个值可能会导致 B + 树层级更高,影响查询性能。《MYSQL 内核:INNODB 存储引擎 卷 1》1.

Java技术栈,分享最主流的Java技术 1054

WINCC7.3破解文件

WINCC7.3硬件够破解文件,有需要的下载!WINCC7.3硬件够破解文件,有需要的下载!

阿里规范说MySQL单表行数不要超过2000w,为啥?

Mysql 的表数据是以页的形式存放的,页在磁盘中不一定是连续的。页的空间是 16K, 并不是所有的空间都是用来存放数据的,会有一些固定的信息,如,页头,页尾,页码,校验码等等。在 B+ 树中,叶子节点和非叶子节点的数据结构是一样的,区别在于,叶子节点存放的是实际的行数据,而非叶子节点存放的是主键和页号。索引结构不会影响单表最大行数,2kw 也只是推荐值,超过了这个值可能会导致 B + 树层级更高,影响查询性能。...

LoveSummer 1万+

文档:亲测可用解决vnc灰屏,黑屏,鼠标大黑叉情况

主要解决vnc-viewer灰屏,黑屏,鼠标大黑叉等现象,详细见文档。

上一篇: 面试官:说说git merge和git rebase的区别
下一篇: 阿里开源了一个新东西,上GitHub热榜了!
公众号-芋道源码
博客等级 码龄10年 1637粉丝 377原创
评论 1
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值