作为在后端圈开车的多年老司机，是不是经常听到过，“mysql 单表最好不要超过 2000w”,“单表超过 2000w 就要考虑数据迁移了”，“你这个表数据都马上要到 2000w 了，难怪查询速度慢”
这些名言民语就和 “群里只讨论技术，不开车，开车速度不要超过 120 码，否则自动踢群”，只听过，没试过，哈哈。
下面我们就把车速踩到底，干到 180 码试试…….
实验
实验一把看看…
建一张表
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());
复制代码
利用 mysql 伪列 rownum 设置伪列起始点为 1
select (@i:=@i+1) as rownum, person_name from person, (select @i:=100) as init;set @i=1;
复制代码
运行下面的 sql，连续执行 20 次，就是 2 的 20 次方约等于 100w 的数据；执行 23 次就是 2 的 23 次方约等于 800w , 如此下去即可实现千万测试数据的插入，如果不想翻倍翻倍的增加数据，而是想少量，少量的增加，有个技巧，就是在 SQL 的后面增加 where 条件，如 id > 某一个值去控制增加的数据量即可。
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;
复制代码
此处需要注意的是，也许你在执行到近 800w 或者 1000w 数据的时候，会报错：The total number of locks exceeds the lock table size，这是由于你的临时表内存设置的不够大，只需要扩大一下设置参数即可。
SET GLOBAL tmp_table_size =512*1024*1024; （512M）SET global innodb_buffer_pool_size= 1*1024*1024*1024 (1G);
复制代码
先来看一组测试数据，这组数据是在 mysql8.0 的版本，并且是在我本机上，由于本机还跑着 idea , 浏览器等各种工具，所以并不是机器配置就是用于数据库配置，所以测试数据只限于参考。
看到这组数据似乎好像真的和标题对应，当数据达到 2000w 以后，查询时长急剧上升；难道这就是铁律吗？
那下面我们就来看看这个建议值 2kw 是怎么来的？
单表数量限制
首先我们先想想数据库单表行数最大多大？
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 '人员信息表';
复制代码
看看上面的建表 sql，id 是主键，本身就是唯一的，也就是说主键的大小可以限制表的上限，如果主键声明 int 大小，也就是 32 位，那么支持 2^32-1 ~~21 亿；如果是 bigint，那就是 2^62-1 ？（36893488147419103232），难以想象这个的多大了，一般还没有到这个限制之前，可能数据库已经爆满了！！
有人统计过，如果建表的时候，自增字段选择无符号的 bigint , 那么自增长最大值是 18446744073709551615，按照一秒新增一条记录的速度，大约什么时候能用完？
作者：码猿技术专栏
链接：juejin.cn/post/716048…
来源：稀土掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
