乐趣区

关于mysql:mysql-最大建议行数2000w靠谱吗

1 背景

作为在后端圈开车的多年老司机,是不是常常听到过,“mysql 单表最好不要超过 2000w”,“单表超过 2000w 就要思考数据迁徙了”,“你这个表数据都马上要到 2000w 了,难怪查问速度慢”

这些名言民语就和“群里只探讨技术,不开车,开车速度不要超过 120 码,否则主动踢群”,只听过,没试过,哈哈。

上面咱们就把车速踩到底,干到 180 码试试…….

2 试验

试验一把看看…建一张表

插入一条数据

利用 mysql 伪列 rownum 设置伪列起始点为 1

运行上面的 sql,间断执行 20 次,就是 2 的 20 次方约等于 100w 的数据;执行 23 次就是 2 的 23 次方约等于 800w , 如此上来即可实现千万测试数据的插入,如果不想翻倍翻倍的减少数据,而是想大量,大量的减少,有个技巧,就是在 SQL 的前面减少 where 条件,如 id> 某一个值去管制减少的数据量即可。

此处须要留神的是,兴许你在执行到近 800w 或者 1000w 数据的时候,会报错:The total number of locks exceeds the lock table size,这是因为你的长期表内存设置的不够大,只须要扩充一下设置参数即可。

先来看一组测试数据,这组数据是在 mysql8.0 的版本,并且是在我本机上,因为本机还跑着 idea , 浏览器等各种工具,所以并不是机器配置就是用于数据库配置,所以测试数据只限于参考。

看到这组数据仿佛如同真的和题目对应,当数据达到 2000w 当前,查问时长急剧回升;难道这就是铁律吗?那上面咱们就来看看这个倡议值 2kw 是怎么来的?

3 单表数量限度

首先咱们先想想数据库单表行数最大多大?

看看下面的建表 sql,id 是主键,自身就是惟一的,也就是说主键的大小能够限度表的下限,如果主键申明 int 大小,也就是 32 位,那么反对 2^32-1 ~~21 亿;如果是 bigint,那就是 2^62-1?(36893488147419103232),难以想象这个的多大了,个别还没有到这个限度之前,可能数据库曾经爆满了!!

有人统计过,如果建表的时候,自增字段抉择无符号的 bigint , 那么自增长最大值是 18446744073709551615,依照一秒新增一条记录的速度,大概什么时候能用完?

4 表空间

上面咱们再来看看索引的构造,对了,咱们上面讲内容都是基于 Innodb 引擎的,大家都晓得 Innodb 的索引外部用的是 B + 树

这张表数据,在硬盘上存储也是相似如此的,它理论是放在一个叫 person.ibd(innodb data)的文件中,也叫做表空间; 尽管数据表中,他们看起来是一条连着一条,然而实际上在文件中它被分成很多小份的数据页,而且每一份都是 16K。大略就像上面这样,当然这只是咱们形象进去的,在表空间中还有段、区、组等很多概念,然而咱们须要跳出来看。

5 页的数据结构

因为每个页只有 16K 的大小, 然而如果数据很多,那一页必定就放不下这些数据,那数据必定就会被分到其余的页中,所以为了把这些页关联起来,必定就会有记录前后页地址,不便找到对应页;同时每页都是惟一的,那就会须要有一个惟一标记来标记页,就是页号;页中会记录数据所以会存在读写操作,读写操作会存在中断或者其余异样导致数据不全等,那就会须要有校验机制,所以外面还有会校验码,而读操作最重要的就是效率问题,如果依照记录一个个进行遍历,那必定是很吃力的,所以这外面还会为数据生成对应的页目录(Page Directory); 所以理论页的内部结构像是上面这样的。

从图中能够看出,一个 InnoDB 数据页的存储空间大抵被划分成了 7 个局部,有的局部占用的字节数是确定的,有的局部占用的字节数是不确定的。

在页的 7 个组成部分中,咱们本人存储的记录会依照咱们指定的行格局存储到 User Records 局部。

然而在一开始生成页的时候,其实并没有 User Records 这个局部,每当咱们插入一条记录,都会从 Free Space 局部,也就是尚未应用的存储空间中申请一个记录大小的空间划分到 User Records 局部,当 Free Space 局部的空间全副被 User Records 局部代替掉之后,也就意味着这个页应用完了,如果还有新的记录插入的话,就须要去申请新的页了。这个过程的图示如下。

刚刚下面说到了数据的新增的过程。

那上面就来说说,数据的查找过程,如果咱们须要查找一条记录,咱们能够把表空间中的每一页都加载到内存中,而后对记录挨个判断是不是咱们想要的,在数据量小的时候,没啥问题,内存也能够撑;然而事实就是这么残暴,不会给你这个场面;为了解决这问题,mysql 中就有了索引的概念;大家都晓得索引可能放慢数据的查问,那到底是怎么个回事呢?上面我就来看看。

6 索引的数据结构

在 mysql 中索引的数据结构和刚刚形容的页简直是截然不同的,而且大小也是 16K, 然而在索引页中记录的是页 (数据页,索引页) 的最小主键 id 和页号,以及在索引页中减少了层级的信息,从 0 开始往上算,所以页与页之间就有了高低层级的概念。

看到这个图之后,是不是有点似曾类似的感觉,是不是像一棵二叉树啊,对,没错!它就是一棵树,只不过咱们在这里只是简略画了三个节点,2 层构造的而已,如果数据多了,可能就会扩大到 3 层的树,这个就是咱们常说的 B + 树,最上面那一层的 page level =0, 也就是叶子节点,其余都是非叶子节点。

看上图中,咱们是单拿一个节点来看,首先它是一个非叶子节点(索引页),在它的内容区中有 id 和 页号地址两局部,这个 id 是对应页中记录的最小记录 id 值,页号地址是指向对应页的指针;而数据页与此简直大同小异,区别在于数据页记录的是实在的行数据而不是页地址,而且 id 的也是程序的。

7 单表倡议值

上面咱们就以 3 层,2 分叉(理论中是 M 分叉)的图例来阐明一下查找一个行数据的过程。

比如说咱们须要查找一个 id=6 的行数据,因为在非叶子节点中寄存的是页号和该页最小的 id,所以咱们从顶层开始比照,首先看页号 10 中的目录,有[id=1, 页号 =20],[id=5, 页号 =30], 阐明左侧节点最小 id 为 1,右侧节点最小 id 是 5;6>5, 那依照二分法查找的规定,必定就往右侧节点持续查找,找到页号 30 的节点后,发现这个节点还有子节点(非叶子节点),那就持续比对,同理,6>5&&6<7, 所以找到了页号 60,找到页号 60 之后,发现此节点为叶子节点(数据节点),于是将此页数据加载至内存进行一一比照,后果找到了 id=6 的数据行。

从上述的过程中发现,咱们为了查找 id=6 的数据,总共查问了三个页,如果三个页都在磁盘中(未提前加载至内存),那么最多须要经验三次的磁盘 IO。

须要留神的是,图中的页号只是个示例,理论状况下并不是间断的,在磁盘中存储也不肯定是程序的。

至此,咱们大略曾经理解了表的数据是怎么个构造了,也大略晓得查问数据是个怎么的过程了,这样咱们也就能大略估算这样的构造能寄存多少数据了。

从下面的图解咱们晓得 B + 数的叶子节点才是存在数据的,而非叶子节点是用来寄存索引数据的。

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

如果是叶子节点,那么外面就是一行行的数据
如果是非叶子节点的话,那么就会持续指向新的页

假如

非叶子节点内指向其余页的数量为 x
叶子节点内能包容的数据行数为 y
B+ 数的层数为 z

如下图中所示
Total =x^(z-1) *y 也就是说总数会等于 x 的 z -1 次方 与 Y 的乘积。

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 层应该是比拟正当的一个值。

到这里难道就完了?

咱们刚刚在说 Y 的值时候假如的是 1K,那比方我理论当行的数据占用空间不是 1K , 而是 5K, 那么单个数据页最多只能放下 3 条数据

同样,还是依照 Z =3 的值来计算,那 Total =(1280 ^2)*3 = 4915200(近 500w)

所以,在放弃雷同的层级(类似查问性能)的状况下,在行数据大小不同的状况下,其实这个最大倡议值也是不同的,而且影响查问性能的还有很多其余因素,比方,数据库版本,服务器配置,sql 的编写等等,MySQL 为了进步性能,会将表的索引装载到内存中。在 InnoDB buffer size 足够的状况下,其能实现全加载进内存,查问不会有问题。然而,当单表数据库达到某个量级的下限时,导致内存无奈存储其索引,使得之后的 SQL 查问会产生磁盘 IO,从而导致性能降落,所以减少硬件配置(比方把内存当磁盘使),可能会带来空谷传声的性能晋升哈。

8 总结

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

9 参考
https://www.jianshu.com/p/cf5…
https://www.modb.pro/db/139052
《MYSQL 内核:INNODB 存储引擎 卷 1》

退出移动版