- 使用 Spring Initializr 创建 Spring Boot 应用程序
- 在Spring Boot中配置Cassandra
- 在 Spring Boot 上配置 Tomcat 连接池
- 将Camel消息路由到嵌入WildFly的Artemis上
1. InnoDB是干嘛的?
InnoDB是一个将表中的数据存储到磁盘上的存储引擎。
2. InnoDB是如何读写数据的?
InnoDB处理数据的过程是发生在内存中的,需要把磁盘中的数据加载到内存中,如果是处理写入或修改请求的话,还需要把内存中的内容刷新到磁盘上。
读写磁盘的速度非常慢,和内存读写差了几个数量级,所以当我们想从表中获取某些记录时,InnoDB存储引擎将数据划分为若干个页,以页作为磁盘和内存之间交互的基本单位,InnoDB中页的大小默认为 16 KB。也就是在一般情况下,一次最少从磁盘中读取16KB的内容到内存中,或者一次最少把内存中的16KB内容刷新到磁盘中。
所以当你用postman测试一个分页查询接口时,发现第一次打印耗时300 ~ 400ms,往后不停的查找下一页都是30 ~ 40ms,原因就是第一次请求接口时,读数据库的时候需要读磁盘,从磁盘加载16KB的数据到内存,往后下一页的数据都是从内存中获取,没有再读磁盘,除非在内存中的16KB的数据中找不到,才会再次读磁盘获取下一个16KB的数据到内存中。
我们不讨论mysql 8.0舍弃的查询缓存特性,我测试过mysql 5.7中关闭了查询缓存,也仍然是第一次慢,后续查询很快,查询时间相差大概10倍的样子
温馨提示:分页查询和数据库的一页16KB中的"页"是两个概念。
注意:innodb_page_size变量在服务器运行过程中不可以更改,只能在第一次初始化MySQL数据目录时指定。所以页在运行时的大小不可更改。
3. varchar疑问千千万——InnoDB行格式
看到这里,你一定有着和我相同的疑问,比如varchar(255)后面这个最大长度应该怎么选择呢?为什么不能varchar(65535)而最大只能varchar(16383)呢?我来带你看!
我们平时是以记录为单位来向表中插入数据的,这些记录在磁盘上的存放方式也被称为行格式或者记录格式。行格式有4种,分别是Dynamic、Compact、Redundant和Compressed
MySQL 5+默认行格式都是Dynamic, 在MySQL 5 和 MySQL 8经过验证确实是的。
SHOW VARIABLES LIKE "innodb_default_row_format"
大家在业务中和平时使用中都几乎没有修改过或者注意过InnoDB行格式,那么我就只重点讲默认行格式dynamic,让大家更深层次理解平时开发中的varchar。
请记住这个表结构,后面会围绕这个来讲
CREATE TABLE test (
c1 VARCHAR(10),
c2 VARCHAR(10) NOT NULL,
c3 CHAR(10),
c4 VARCHAR(10)) CHARSET = utf8mb4;
现在业务数据库字符集都是utf8mb4,我就以这个来讲,把理解难度降到最低。
INSERT INTO test ( c1, c2, c3, c4 )
VALUES('aaaa', '你好啊', 'cc', 'd'),('eeee', 'fff', NULL, NULL);
现在,表中的记录就是这样
3.1 dynamic——innodb默认行格式
关于记录的额外信息这部分,是服务器为了描述这条记录而不得不额外添加的一些信息,这些额外信息分为3类,分别是变长字段长度列表、NULL值列表和记录头信息。
在这里我只讲变长字段长度列表、NULL值列表。因为记录头信息非常的绕和本篇没多大关系。
3.2 innodb怎么知道varchar真正有多长?——变长字段长度列表
一些变长的数据类型,比如VARCHAR(M)、各种TEXT类型,各种BLOB类型,变长数据类型的字段中存储多少字节的数据是不固定的,在存储真实数据的时候需要把这些数据占用的字节数也存起来。
就像设计String类型,不仅仅是存放真实数据的char数组,还有length变量去记录字符串长度。又比如input输入框最大限制500字,但是你还得有一个变量去统计真实在输入框内有多少字符。同理,varchar也有记录真实数据长度的变量(假设为L,后文沿用方便描述),L表示varchar真实占用的字节数,innodb最多分配2个字节去表示这个L,就像unsigned short类型,2个字节,寄存器最多只有16位来让你存这个长度,所以L记录范围是2^16 - 1 = 65535。
这些变长字段(比如varchar)占用的存储空间分为两部分:
我们拿test表中的第一条记录来举个例子。因为test表的c1、c2、c4列都是VARCHAR(10)类型的,说明最大10个字符,所以这三个列的值的长度都需要保存在记录开头处,因为test表中的各个列都使用的是utf8mb4字符集,每个字符最大需要4个字节来进行编码(不使用utf8而是utf8mb4是因为可能存储emoji表情,如果只是文字,utf8就足够),来看一下第一条记录各变长字段内容的长度:
怎么确定这些字段有多少字节?
比如这里c2的"你好啊",使用如下sql可以确定
SELECT LENGTH(c2) from test where c1='aaaa';
各变长字段数据占用的字节数按照列的顺序逆序存放!!
由于第一行记录中c1、c2、c4列中的字符串都比较短,也就是说varchar真实占用的字节数比较小,L用1个字节(8个bit位) 就可以表示,但是如果varchar真实占用的字节数比较多,L可能就需要用2个字节(16个bit位) 来表示。到底varchar能存多少字节呢?继续往下看。
3.3 varchar(M) 能存多少个字符,为什么提示最大16383?
首先要理解varchar(M)的M是说字符个数,而不是字节。
为什么不能varchar(20000)之类的,是20000个字符放不下吗?
为什么提示只能最大16383个字符呢?这个数字是怎么算出来的?
这个我就得和你好好唠嗑了!
varchar是变长的,varchar(64) 能存放0~64个字符不等,并不一定是存了最大64个字符,谁知道这个类型到底存了几个字符呢?
innodb设计的时候,就已经考虑到了,不过是用字节作为单位,后续我们可以根据对应字符集转变为字符来理解,innodb必须记录变长字段varchar真实占用的字节数L。前面说过了,innodb最多分配2个字节(16个bit位)的空间去记录这个L。
InnoDB有它的一套规则,我们引入W、M和L这几个符号:
1、假设某个字符集中最多需要W字节来表示一个字符
2、对于变长类型VARCHAR(M)来说,这种类型表示能存储最多M个字符(注意是字符不是字节)
3、假设它实际存储的字符串占用的字节数是L。
来看极限边界情况,innodb为了记录一下varchar真实存储多少个字节,最多分配2个字节的空间去记录,2个字节16个比特位,全部为1,最大能记录的数字是2^16-1是65535个,innodb最大能记录varchar占用的字节数就是65535个,utf8mb4字符集一个字符是最大是4个字节,65535 / 4 = 16383.75,只要varchar字符数不超过16383个,innodb就可以记录真实占用的长度L,再多就记录不了了!所以就能解释刚刚的图了,varchar(20000)不行,最大也就16383个字符
但是!这里强调是有但是的!
行最大长度是65535字节,行里面有很多东西,包括变长字段列表、NULL值列表、记录头信息。你得考虑该字段如果允许为NULL,NULL值列表会占用一个字节(只要没超过8个字段),每一列字段的变长字段实际长度会花费1~2个字节,如果该字段的数据太大,会变成溢出列,该字段的数据会分成很多行存储(后面会讲,你可以看完NULL值列表和溢出列后再回来看这个例子)。所以即便提示16383个字符,你也绝对不可能存到16383。
我做了个测试
create table t2 ( name varchar(16383))charset=utf8mb4;
不断往这个字段添加字符保存测试,最后发现,这些字符总长度到极限也就是48545字节。
如果超过就会报错
这里48545个字节,再多一个字符就会报错,远不到65535字节,差了1W多字节。主要是因为溢出列的原因,数据分散在不同的行中,所以,很长的数据,建议往text类型考虑。这个现象可以看出,varchar(M)的M很大,实际是达不到M这个边界值的。
下面说明一下规则(讲解中字符集用utf8mb4,W=4)
规则一:如果允许存储的最大字节数M × W <= 255,varchar占用的真实字节数L只分配1个字节来表示。
有人说,允许存储的最大字节数M × W <= 255,即允许存储的最大字符数 <= ⌊255 / 4⌋ = 63个时,varchar占用的真实字节数L仅分配1个字节就能表示。这个结论正确吗?
显然错误,因为这里255 / 4,你怎么知道每个存储的一个字符是4个字节呢?难道全部存的emoji表情?不存字母汉字啥的?
InnoDB在读记录的变长字段长度列表时先查看表结构,如果某个变长字段允许存储的最大字节数不大于255时,只用1个字节来表示真实数据占用的字节。
规则二:如果允许存储的最大字节数M × W > 255,则分为两种情况:
如果实际存储字节L <= 127,varchar占用的真实字节数L仅分配1个字节就能表示。(⌊ … ⌋表示向下取整)
有人说,实际存储字节L <= 127,即实际存储字符 <= ⌊127 / 4⌋ = 31个时,varchar占用的真实字节数L仅分配1个字节就能表示。这个结论正确吗?
显然错误,因为这里127 / 4,你怎么知道实际存储的一个字符是4个字节呢?难道全部存的emoji表情?不存字母汉字啥的?
如果实际存储字节L > 127,varchar占用的真实字节数L需要分配2个字节才能表示。
另外需要注意的是,变长字段列表只存储非NULL的列的长度。
表记录是这样的
对于第二条记录,c4列值为NULL,所以只存储c1和c2列即可。
第一条记录的变长字段长度列表部分占用3字节空间,因为有c1、c2、c4列,且内容都很少,每列真实占用字节数用1个字节可以表示,加起来就是3个字节,第二条记录变长字段长度列表部分占用2字节。
当然,并不是所有记录都有这个变长字段长度列表部分,比方说表中所有的列都不是变长的数据类型或者 所有列的值都是NULL 的话,这一部分就不需要有。实际业务开发中,几乎没有不使用varchar的,所以实际开发中的记录都会有变长字段长度列表部分
3.4 记录为NULL,innodb如何处理?——NULL值列表
能仔细看到这里,你肯定是个高手了。如果你和我一样开发规范中不推荐NULL,一般都写NOT NULL,其实记录中就不存在NULL值列表了,也节省了空间。
如果表中的某些列可能存储NULL值,把这些NULL值都放到记录的真实数据中存储会很占地方,所以dynamic行格式把这些值为NULL的列统一管理起来,存储到NULL值列表中,它的处理过程是这样的:
1.统计表中允许存储NULL的列有哪些。
主键列、被NOT NULL修饰的列都是不可以存储NULL值的,所以在统计的时候不会把这些列算进去。比方说表test的3个列c1、c3、c4都是允许存储NULL值的,而c2列是被NOT NULL修饰,不允许存储NULL值。
2.如果表中没有允许存储 NULL 的列,则 NULL值列表也不存在了,否则将每个允许存储NULL的列对应一个二进制位,二进制位按照列的顺序逆序排列。二进制位的值为1时,代表该列的值为NULL,为0时,代表该列的值不为NULL。因为表test的c1、c3、c4都是允许存储NULL值的允许为NULL的列,所以这3个列和二进制位的对应关系就是这样:
3.NULL值列表必须用整数个字节的位表示,如果使用的二进制位个数不是整数个字节,则在字节的高位补0。
也就是说,表test只有3个字段允许为NULL,对应3个二进制位,不足1字节,那么就在高位补0即可。
以此类推,如果表中有9个字段都允许为NULL,那么这个记录的NULL值列表就需要2个字节来表示,高字节高位补0。
对于第一条记录,c1、c3、c4都不为NULL,对应的为进制位为0,十六进制表示就是0x00
对于第二条记录,c3、c4都是NULL,对应的二进制位为1,十六进制表示就是0x06
这两条记录在填充了NULL值列表后示意图如下:
3.5 某个列数据占用的字节数非常多怎么办?——dynamic行格式的溢出列
如果某个列中存储的数据占用的字节数非常多,该列就可能称为溢出列。
对于占用存储空间非常多的列,在记录真实数据时,该列只会用20字节空间,而这20字节的空间不存储数据,因为数据都分散存储在其他几行中了。这20字节的空间存储的是分散行的地址和占用的字节数。分散行记录是单链表连接的结构。
后续
如果大家对innodb存储结构其他行格式感兴趣,或者我没说的记录头信息,可以去阅读《MySQL是怎样运行的》一书,我和书中不同的是,书中讲的Compact格式,字符集是ascii,我选用的是平时开发中用到的默认dynamic格式,字符集是utf8mb4,字符集变化后所有的数据我在文中和图中都有重新计算。大家平时或许没关注过行格式,那么就是按照dynamic格式理解就可以,更贴近实际开发。
来源:liuchenyang0515.blog.csdn.net/article/details/117524328
推荐3个原创springboot+Vue项目,有完整视频讲解与文档和源码:
关注我,学Java
目前我正在构建相当大的网络系统,我需要强大的 SQL 数据库解决方案。我选择 Mysql 而不是 Postgres,因为一些任务需要只读(MyISAM 引擎)而其他任务需要大量写入(InnoDB)。
我在 mysql 中使用如下命令。当它显示表格数据时,它被格式化为一个非常干净的表格,间距均匀且 |作为列分隔符。 SELECT * FROM TABLE_NAME; 当我从 CLI 运行命令时,如下
我知道这个问题之前已经被问过好几次了,我已经解决了很多问题,但到目前为止没有任何效果。 MySQL 试图将自身安装到的目录 (usr/local/mysql) 肯定有问题。关于我的错误的奇怪之处在于我
以下是我的 SQL 数据结构,我正在尝试如下两个查询: Select Wrk_ID, Wrk_LastName, Skill_Desc from Worker, Skill where
我们有一个本地 mysql 服务器(不在公共(public)域上),并希望将该服务器复制到我们拥有的 google 云 sql 实例。我的问题是:1.这可能吗?2.我们的本地服务器只能在本地网络上访问
我有一个表(test_table),其中一些字段值(例如字段 A、B 和 C)是从外部应用程序插入的,还有一个字段(字段 D),我想从现有表(store_table)插入其值,但在插入前者(A、B 和
我想创建一个 AWS RDS 实例,然后使用 terraform 管理数据库用户。因此,首先,我创建了一个 RDS 实例,然后使用创建的 RDS 实例初始化 mysql 提供程序,以进一步将其用于用户
当用户在我的网站上注册时,他们会在我的一个数据库中创建自己的表格。该表存储用户发布的所有帖子。我还想做的是也为他们生成自己的 MySql 用户——该用户仅有权从他们的表中读取、写入和删除。 创建它应该
我有一个关于 ColdFusion 和 Mysql 的问题。我有两个表:PRODUCT 和 PRODUCT_CAT。我想列出包含一些标记为:IS_EXTRANET=1 的特殊产品的类别。所以我写了这个
我想获取 recipes_id 列的值,以获取包含 ingredient_id 的 2,17 和 26 条目的值。 假设 ingredient_id 2 丢失则不获取记录。 我已经尝试过 IN 运算符
在 Ubuntu 中,我通常安装两者,但 MySQL 的客户端和服务器之间有什么区别。 作为奖励,当一个新语句提到它需要 MySQL 5.x 时,它是指客户端、服务器还是两者兼而有之。例如这个链接ht
我重新访问了我的数据库并注意到我有一些 INT 类型的主键。 这还不够独特,所以我想我会有一个指导。 我来自微软 sql 背景,在 ssms 中你可以 选择类型为“uniqeidentifier”并自
我的系统上有 MySQL,我正在尝试确定它是 Oracle MySQL 还是 MySQL。 Oracle MySQL 有区别吗: http://www.oracle.com/us/products/m
我是在生产 MySQL 中运行的应用程序的新维护者。之前的维护者已经离开,留下的文档很少,而且联系不上了。 我面临的问题是执行以下请求大约需要 10 秒: SELECT COUNT(*) FROM `
我有两个位于不同机器上的 MySQL 数据库。我想自动将数据从一台服务器传输到另一台服务器。比方说,我希望每天早上 4:00 进行数据传输。 可以吗?是否有任何 MySQL 内置功能可以让我们做到这一
有什么方法可以使用 jdbc 查询位于 mysql 根目录之外的目录中的 mysql 表,还是必须将它们移动到 mysql 根目录内的数据库文件夹中?我在 Google 上搜索时没有找到任何东西。 最
我在 mysql 数据库中有两个表。成员和 ClassNumbers。两个表都有一个付费年份字段,都有一个代码字段。我想用代码数字表中的值更新成员表中的付费年份,其中成员中的代码与 ClassNumb
情况:我有 2 台服务器,其中一台当前托管一个实时 WordPress 站点,我希望能够将该站点转移到另一台服务器,以防第一台服务器出现故障。传输源文件很容易;传输数据库是我需要弄清楚如何做的。两台服
Phpmyadmin 有一个功能是“复制数据库到”..有没有mysql查询来写这个函数?类似于将 db A 复制到新的 db B。 最佳答案 首先创建复制数据库: CREATE DATABASE du
我有一个使用 mySQL 作为后端的库存软件。我已经在我的计算机上对其进行了测试,并且运行良好。 当我在计算机上安装我的软件时,我必须执行以下步骤: 安装 mySQL 服务器 将用户名指定为“root
我是一名优秀的程序员,十分优秀!