电商网站策划,广州注册公司名称,网站开发颜色,沈阳定制网站开发作为在后端开发#xff0c;是不是经常听到过#xff0c;mysql 单表最好不要超过 2000w,单表超过 2000w 就要考虑数据迁移了#xff0c;表数据都要到 2000w #xff0c;查询速度变得贼慢。 1、建表操作
建一张表
CREATE TABLE person(
id int NOT NULL AUTO_INCREMENT PRI… 作为在后端开发是不是经常听到过mysql 单表最好不要超过 2000w,单表超过 2000w 就要考虑数据迁移了表数据都要到 2000w 查询速度变得贼慢。 1、建表操作
建一张表
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:i1) as rownum, person_name from person, (select i:100) as init;
set i1;运行下面的 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:i1,
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 以后查询时长急剧上升。
2、单表数量限是多少呢
首先我们先想想数据库单表行数最大多大
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 人员信息表;看看上面的建表 sqlid 是主键本身就是唯一的也就是说主键的大小可以限制表的上限如果主键声明 int 大小也就是 32 位那么支持 2^32-1 ~~21 亿如果是 bigint那就是 2^62-1 36893488147419103232难以想象这个的多大了一般还没有到这个限制之前可能数据库已经爆满了
有人统计过如果建表的时候自增字段选择无符号的 bigint , 那么自增长最大值是 18446744073709551615按照一秒新增一条记录的速度大约什么时候能用完 3、表空间
下面我们再来看看索引的结构对了我们下面讲内容都是基于 Innodb 引擎的大家都知道 Innodb 的索引内部用的是 B 树 这张表数据在硬盘上存储也是类似如此的它实际是放在一个叫 person.ibd innodb data的文件中也叫做表空间虽然数据表中他们看起来是一条连着一条但是实际上在文件中它被分成很多小份的数据页而且每一份都是 16K。
大概就像下面这样当然这只是我们抽象出来的在表空间中还有段、区、组等很多概念但是我们需要跳出来看。对于什么事 B树可以参考另一篇文章即可。 4、总结
MySQL 的表数据是以页的形式存放的页在磁盘中不一定是连续的。页的空间是 16K, 并不是所有的空间都是用来存放数据的会有一些固定的信息如页头页尾页码校验码等等。在 B 树中叶子节点和非叶子节点的数据结构是一样的区别在于叶子节点存放的是实际的行数据而非叶子节点存放的是主键和页号。索引结构不会影响单表最大行数2kw 也只是推荐值超过了这个值可能会导致 B 树层级更高影响查询性能。
