MySQL最大建议行数2000w, 靠谱吗?
共 6523字,需浏览 14分钟
· 2022-07-31
Hollis的新书限时折扣中,一本深入讲解Java基础的干货笔记!
来源 | OSCHINA 社区
作者 | 京东科技开发者
原文链接:https://my.oschina.net/u/4090830/blog/5559454
1、背景
作为在后端圈开车的多年老司机,是不是经常听到过,“mysql 单表最好不要超过 2000w”,“单表超过 2000w 就要考虑数据迁移了”,“你这个表数据都马上要到 2000w 了,难怪查询速度慢”
这些名言民语就和 “群里只讨论技术,不开车,开车速度不要超过 120 码,否则自动踢群”,只听过,没试过,哈哈。
下面我们就把车速踩到底,干到 180 码试试…….
2、实验
实验一把看看…
建一张表
CREATE TABLE person(
id int NOT NULL AUTO_INCREMENT PRIMARY KEY comment '主键',
person_id tinyint not null comment '用户id',
person_name VARCHAR(200) comment '用户名称',
gmt_create datetime comment '创建时间',
gmt_modified datetime comment '修改时间'
) comment '人员信息表';
insert into person values(1,1,'user_1', NOW(), now());
select (@i:=@i+1) as rownum, person_name from person, (select @i:=100) as init;
set @i=1;
insert into person(id, person_id, person_name, gmt_create, gmt_modified)
select @i:=@i+1,
left(rand()*10,10) as person_id,
concat('user_',@i%2048),
date_add(gmt_create,interval + @i*cast(rand()*100 as signed) SECOND),
date_add(date_add(gmt_modified,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND)
from person;
SET GLOBAL tmp_table_size =512*1024*1024; (512M)
SET global innodb_buffer_pool_size= 1*1024*1024*1024 (1G);
![](https://filescdn.proginn.com/a9d4561227f642b83f05ecfa85902197/3638f06f794a88046f1bae0e131eccdd.webp)
![](https://filescdn.proginn.com/c99659d9476f4bd48c66099722222056/e7838638b02a88aeaba84d1a62b5968b.webp)
3、单表数量限制
CREATE TABLE person(
id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY comment '主键',
person_id tinyint not null comment '用户id',
person_name VARCHAR(200) comment '用户名称',
gmt_create datetime comment '创建时间',
gmt_modified datetime comment '修改时间'
) comment '人员信息表';
有人统计过,如果建表的时候,自增字段选择无符号的 bigint , 那么自增长最大值是 18446744073709551615,按照一秒新增一条记录的速度,大约什么时候能用完?
![](https://filescdn.proginn.com/5735dcaeea3cfe95c3bc130e2a072265/e55bd54ac522f4ab80dc3837056869f2.webp)
4、表空间
![](https://filescdn.proginn.com/f714a916ae2d7b63dcfed59866c4b8c0/400d7bc6e3ff445d004586ef78cc49ff.webp)
![](https://filescdn.proginn.com/7a8664e0d2cc2f2fa6b151238591f236/ea42f4e35fcc622c5867af98b216d591.webp)
5、页的数据结构
![](https://filescdn.proginn.com/5763eba439cce95d610457d7bcee74f0/135500ba45d4ead562504ed3a6cad630.webp)
![](https://filescdn.proginn.com/78bdc396a32183d84c0308628975c71f/4d96f9a0ea19b006b13e8f066398564b.webp)
6、索引的数据结构
![](https://filescdn.proginn.com/72933a1be8a7615ba23a5793690fc609/7d4148ba5b1781e0a47f72a03fd62ba8.webp)
![](https://filescdn.proginn.com/b74b86a6eaf85e15c1ace7dc3ab4ffc7/3159bfa8001fff5d115e58b1e131a77e.webp)
7、单表建议值
需要注意的是,图中的页号只是个示例,实际情况下并不是连续的,在磁盘中存储也不一定是顺序的。
![](https://filescdn.proginn.com/c5a0e64bdff4e8d3ab2ad2b8f0880646/34c52b93f457a255eb17df571d66f6e1.webp)
如果是叶子节点,那么里面就是一行行的数据
如果是非叶子节点的话,那么就会继续指向新的页
非叶子节点内指向其他页的数量为 x
叶子节点内能容纳的数据行数为 y
B+ 数的层数为 z
Total =x^(z-1) *y 也就是说总数会等于 x 的 z-1 次方 与 Y 的乘积。
![](https://filescdn.proginn.com/c83fd87f7df665607ccb019010fa871d/3b84510e447876cdc1dfaf4678fc745e.webp)
根据上述的公式,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)
我们刚刚在说 Y 的值时候假设的是 1K ,那比如我实际当行的数据占用空间不是 1K , 而是 5K, 那么单个数据页最多只能放下 3 条数据
同样,还是按照 Z=3 的值来计算,那 Total = (1280 ^2) *3 = 4915200 (近 500w)
8、总结
Mysql 的表数据是以页的形式存放的,页在磁盘中不一定是连续的。
页的空间是 16K, 并不是所有的空间都是用来存放数据的,会有一些固定的信息,如,页头,页尾,页码,校验码等等。
在 B+ 树中,叶子节点和非叶子节点的数据结构是一样的,区别在于,叶子节点存放的是实际的行数据,而非叶子节点存放的是主键和页号。
索引结构不会影响单表最大行数,2kw 也只是推荐值,超过了这个值可能会导致 B + 树层级更高,影响查询性能。
9、参考
https://www.jianshu.com/p/cf5d381ef637
https://www.modb.pro/db/139052
《MYSQL 内核:INNODB 存储引擎 卷 1》
完
我的新书《深入理解Java核心技术》已经上市了,上市后一直蝉联京东畅销榜中,目前正在6折优惠中,想要入手的朋友千万不要错过哦~长按二维码即可购买~
长按扫码享受6折优惠
往期推荐
![](https://filescdn.proginn.com/271a17a82c77f4fa0b05b006a0085aad/2d6835ec406307464bc9d7f5d373a9d7.webp)
如果我们是那晚负责修复 B 站崩了的开发人员
![](https://filescdn.proginn.com/2b3aa805c4ffd4c2b9b0f21e95d3f131/ec85393d569e06d8664cbd2b77982949.webp)
到底为什么不建议使用SELECT * ?
![](https://filescdn.proginn.com/5b7f1ede664e08923a480a36ce6c4ea0/fa500eb325a23bf4b86773d4108018b7.webp)
新来个技术总监要我做一个 IP 属地功能~
有道无术,术可成;有术无道,止于术
欢迎大家关注Java之道公众号
好文章,我在看❤️