MySQL最大建议行数2000w, 靠谱吗?
共 6523字,需浏览 14分钟
·
2022-07-31 10:12
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);
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,按照一秒新增一条记录的速度,大约什么时候能用完?
4、表空间
5、页的数据结构
6、索引的数据结构
7、单表建议值
需要注意的是,图中的页号只是个示例,实际情况下并不是连续的,在磁盘中存储也不一定是顺序的。
如果是叶子节点,那么里面就是一行行的数据
如果是非叶子节点的话,那么就会继续指向新的页
非叶子节点内指向其他页的数量为 x
叶子节点内能容纳的数据行数为 y
B+ 数的层数为 z
Total =x^(z-1) *y 也就是说总数会等于 x 的 z-1 次方 与 Y 的乘积。
根据上述的公式,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折优惠
往期推荐
如果我们是那晚负责修复 B 站崩了的开发人员
到底为什么不建议使用SELECT * ?
新来个技术总监要我做一个 IP 属地功能~
有道无术,术可成;有术无道,止于术
欢迎大家关注Java之道公众号
好文章,我在看❤️