(MySQL经验)之MySQL单表行数最好低于2000w

news/2024/2/28 2:19:42

作为在后端开发,是不是经常听到过,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:=@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 以后,查询时长急剧上升。

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 '人员信息表';

看看上面的建表 sql,id 是主键,本身就是唯一的,也就是说主键的大小可以限制表的上限,如果主键声明 int 大小,也就是 32 位,那么支持 2^32-1 ~~21 亿;如果是 bigint,那就是 2^62-1 ?(36893488147419103232),难以想象这个的多大了,一般还没有到这个限制之前,可能数据库已经爆满了!!

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

在这里插入图片描述

3、表空间

下面我们再来看看索引的结构,对了,我们下面讲内容都是基于 Innodb 引擎的,大家都知道 Innodb 的索引内部用的是 B+ 树

在这里插入图片描述

这张表数据,在硬盘上存储也是类似如此的,它实际是放在一个叫 person.ibd (innodb data)的文件中,也叫做表空间;虽然数据表中,他们看起来是一条连着一条,但是实际上在文件中它被分成很多小份的数据页,而且每一份都是 16K。

大概就像下面这样,当然这只是我们抽象出来的,在表空间中还有段、区、组等很多概念,但是我们需要跳出来看。对于什么事 B+树,可以参考另一篇文章即可。

在这里插入图片描述

4、总结

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

http://www.ppmy.cn/news/1027838.html

相关文章

Ubuntu安装bfloat16==1.1出现问题 error: subprocess-exited-with-error

报错 error: subprocess-exited-with-error python setup.py bdist_wheel did not run successfully. 解决方法 确保你的系统上已经安装了 C/C 编译器(如 gcc、g)。 如果你使用的是 Linux 系统,你可以使用包管理器来安装它们。命令如下 u…

springboot项目重启的shell命令

大家好,我是雄雄,微信公众号:雄雄的小课堂,欢迎关注。 前言 我们都知道,springboot项目启动的时候,需要如下过程: 查找 服务的进程id杀掉该进程启动服务 并且每一步都有对应的shell命令&…

软件测试基础篇——Docker

1、docker技术概述 docker描述:docker是一项虚拟化的容器技术(类似于虚拟机),docker技术给使用者提供一个平台,在该平台上可以利用提供的容器,对每一个应用程序进行单独的封装隔离,每一个应用程…

初识mysql数据库之图形化界面

目录 一、好用的数据库图形化界面软件 1. Navicat 2. SQLyog 3. MYSQL Workbench 二、MYSQL Workbench基本使用 1. 安装 2. 远端连接 3. 执行sql语句 一、好用的数据库图形化界面软件 在以前的文章中,一共介绍了两种使用数据库的方式,分别为在l…

WebAPIs 第一天

1.声明变量const优先(补充) 2.WebAPI基本认知 作用和分类 DOM树和DOM对象 3.获取DOM元素 4.DOM修改元素内容 5.操作元素属性 6.定时器-间歇函数 一.声明变量const优先 ① 变量声明有var let const ② 建议const优先,尽量使用const…

每天40min,我们一起用70天稳扎稳打学完《JavaEE初阶》——13/70 第十三天【JavaEE初阶 面试题(一)】

专注 效率 记忆 预习 笔记 复习 做题 欢迎观看我的博客,如有问题交流,欢迎评论区留言,一定尽快回复!(大家可以去看我的专栏,是所有文章的目录)   文章字体风格: 红色文字表示:重难点★✔ 蓝色文字表示:思路以及想法★✔   如果大家觉得有帮助的话,感谢大家帮忙 点…

EPICS libCom库(1)-- dbmf

dbmf.h(数据库宏/Free)描述一个工具,它防止内存被分配时并且短时间之后被释放时内存碎片化。 在iocCore内像dbLoadDatabase()的例程具有以下特性: 1) 它们重复地调用malloc(),之后快速调用free()释放临时分配的存储区。 2&…

Codeforces Round 892 (Div. 2) C. Another Permutation Problem 纯数学方法 思维题

Codeforces Round 892 (Div. 2) C. Another Permutation Problem 源码&#xff1a; #include <iostream> #include <algorithm> #include <set> #include <map> #include <queue> #include <vector> #include <stack> #include &l…

买爱心气球(nim博弈)

链接&#xff1a;登录—专业IT笔试面试备考平台_牛客网 来源&#xff1a;牛客网 Alice 和 Bob 是一对竞技编程选手&#xff0c;他们路过了一家气球店&#xff0c;发现有 m 个大爱心气球和 n个小爱心气球。他们决定玩一个游戏&#xff0c;游戏规则如下&#xff1a; Alice先手拿…

每期一个小窍门: go处理异常的一些小窍门

go设计者更偏向于C的error处理方式, 快速失败是更简单高效的 我们可以利用error接口和多返回值来实现异常传递 error可以利用变量来复用 等价判断 var outOfRangeError errors.New("number out of range") var unknownError errors.New("unknown type erro…

NPM与外部服务的集成(上)

目录 1、关于访问令牌 1.1 关于传统令牌 1.2 关于粒度访问令牌 2、创建和查看访问令牌 2.1 创建访问令牌 在网站上创建传统令牌 在网站上创建粒度访问令牌 使用CLI创建令牌 CIDR限制令牌错误 查看访问令牌 在网站上查看令牌 在CLI上查看令牌 令牌属性 1、关于访问令…

视频分辨率: UXGA/SVGA/VGA/QVGA/QQVGA

视频分辨率除了常见的720p/2K/4K外, 还有VGA系列的分辨率 相关字段含义: V——Video &#xff08;视频&#xff09; G——Graphics&#xff08;图像&#xff09; A——Array&#xff08;阵列&#xff09; S——Super(超级) X——Extended(扩展) U——Ultra(终极) W——Wide&am…

c++ 有元

友元分为两部分内容 友元函数友元类 友元函数 问题&#xff1a;当我们尝试去重载operator<<&#xff0c;然后发现没办法将operator<<重载成成员函数。因为cout的输出流对象和隐含的this指针在抢占第一个参数的位置。this指针默认是第一个参数也就是左操作 数了。…

利用python实现网络设备配置批量上传和批量下载功能

利用python实现网络设备配置批量上传和批量下载功能 利用ensp实现网络设备和物理主机互通配置网络设备配置批量上传功能配置批量下载功能常见问题 提示&#xff1a; 本文章代码所使用目录均使用相对目录&#xff0c;只需将配置存放目录和文件下载目录&#xff08;已用符号标出…

【Lua基础入门】解密世界上最快的脚本语言

文章目录 前言一、Lua简介二、Lua功能三、安装LuaUbuntu LinuxWindows安装Lua 四、第一个Lua程序总结 前言 Lua是一种轻量级、快速且可嵌入的脚本语言&#xff0c;广泛应用于游戏开发、嵌入式系统、脚本扩展等领域。它的设计目标是简单、高效、可定制和易于集成。本文将介绍Lu…

易服客工作室:Elementor AI简介 – 彻底改变您创建网站的方式

Elementor 作为领先的 WordPress 网站构建器&#xff0c;是第一个添加本机 AI 集成的。Elementor AI 的第一阶段将使您能够生成和改进文本和自定义代码&#xff08;HTML、自定义代码和自定义 CSS&#xff09;。我们还已经在进行以下阶段的工作&#xff0c;其中将包括基于人工智…

SolidWorks不能使用选择如允许此选择将生成有冲突的前后关系

SolidWorks不能使用选择如允许此选择将生成有冲突的前后关系 1 SolidWorks不能使用选择如允许此选择将生成有冲突的前后关系 1 SolidWorks不能使用选择如允许此选择将生成有冲突的前后关系 https://www.swrjzxw.com/1556.html SolidWorks装配体时 显示 不能使用选择如允许此选…

Mac下全选,使用pynput,怎样调用command键?

Key.command 不行&#xff0c;用Key.cmd 。 win或linux下&#xff1a; with keyboard.pressed(Key.ctrl):keyboard.press(a)time.sleep(1)keyboard.release(a) 那么在mac下就是&#xff1a; with keyboard.pressed(Key.cmd):keyboard.press(a)time.sleep(1)keyboard.rel…

【算法|数组】双指针

算法|数组——双指针 引入 给你一个按 非递减顺序 排序的整数数组 nums&#xff0c;返回 每个数字的平方 组成的新数组&#xff0c;要求也按 非递减顺序 排序。 示例 1&#xff1a; 输入&#xff1a;nums [-4,-1,0,3,10] 输出&#xff1a;[0,1,9,16,100] 解释&#xff1a;…

.NET实现解析字符串表达式

一、引子功能需求 我们创建了一个 School 对象&#xff0c;其中包含了教师列表和学生列表。现在&#xff0c;我们需要计算教师平均年龄和学生平均年龄。 //创建对象 School school new School() {Name "小菜学园",Teachers new List<Teacher>(){new Teach…
最新文章