Loading...
墨滴

cross

2021/06/29  阅读:56  主题:红绯

mysql建表语句被DBA打回(一)之字段可空

背景

DBA 又把建表语句打回来了,原因是字段可空了!理由是为了省空间!!!!

为了证明这种说法的不严谨,我整理了一下

语句如下:

CREATE TABLE user_info (
id bigint unsigned NOT NULL AUTO_INCREMENT,
user_id int NOT NULL COMMENT '用户id',
user_name varchar(64NOT NULL COMMENT '真实姓名',
email varchar(30NOT NULL COMMENT '用户邮箱',
nick_name varchar(45COMMENT '昵称',
status tinyint NOT NULL COMMENT '用户状态,1-正常,2-注销,3-冻结',
address varchar(128COMMENT '家庭住址',
--省略了一些属性,篇幅原因,这里删掉了
PRIMARY KEY (id),
KEY idx_user_id (user_id)
ENGINE=InnoDB COMMENT='用户基本信息';

用户可能就是没有昵称啊,设置为可空,看起来也没啥问题啊!有没有一种被针对的感觉

其实你如果知道每条记录真正的存储格式,你就知道怎么怼他了。

正文

InnoDB 页的概念

Page 是 Innodb 存储的最基本结构,也是 Innodb 磁盘管理的最小单位,与数据库相关的所有内容都存储在 Page 结构里。

MySQL 的客户端和服务端的交互最小单位也是 Innodb 页(默认大小为 16K),也就是说如果你即使只查询一条记录,一次至少从磁盘上读取 16K 的内容到内存中。

Innodb 行格式

我们平时往数据库插入一条条的记录,这些记录在磁盘上存放格式也是以行记录的方式的。

Innodb 存储引擎目前有四种格式--COMPACT、REDUNDANT、DYNAMIC、COMPRESSED

从 MySQL5.7 开始,默认是 DYNAMIC 方式,我们用图片来看一下 Innodb 行格式大概示意图。(这里只讲 COMPACT、DYNAMIC,因为这 2 者基本上是一样的,只有一些细微的差别)

记录额外信息

这里只讲变长属性长度列表、NULL 值列表 2 部分

1、变长属性长度列表

常见的关系型数据库都支持变长属性,比如上面的语句username varchar(64)中,64 是代表可存储的最大字符数,如果某条记录的名字是“张三”,会有什么样的问题呢

我们要让 MySQL 服务器知道每条记录的每个变长属性知道实际长度是多少,不然它会懵

我们来举一个栗子:

上面的记录 user_name,email,nick_name,address 都是 varchar 类型的,那么他存储的记录格式是什么样的呢

上图中的 06、09、0b、06、09 分别对应 user_name,email,nick_name、address 对应字节长度,16 进制(实际上无空格,这里只是为了方便看)。

至于为什么是倒序放的,不是本文重点,这里先不做解答

小彩蛋: varchar(M)中的 M 代表能存储的最大字符数,实际存储时都是按照字节来计算的,所以一个字段占用的实际字节数的公式:

RL(存储属性最多的字节数)=M(最多字符数)* W(当前字符集下最多需要几个字节表示一个字符)

所以在编码是 utf8mb4 的情况下,user_name 最多占用的字节数=64 * 4 = 256,所以该属性需要2个字节表示长度

为什么不是 1 个字节,1 个字节不正好是 256 吗? 因为第一位是标记位,如果第一位是 0,表示用 1 个字节就可以表示该属性的长度,如果第一位是 1,表示需要用 2 个字节表示该属性的长度。

但是如果RL大于256,占用的字节也是2个,字段属性值保存一部分或者(数据地址)

如果另一条记录是这样的:

则对应的行格式如下:

昵称为空,则不在变长长度列表中出现。

2、NULL 值列表

我们在插入一条记录时,Innodb 引擎怎么处理

  • 先统计允许为 NULL 的列有哪些
  • 如果建表语句中,没有可空的字段,就没有 NULL 值列表;如果有可空的字段,那么每个可空字段对应一个二进制位,1-代表 NULL,0-不为 NULL
  • 最小单位需要用字节来表示 NULL 值列表,如果只有 2 个可空值,至少也需要 1 个字节来表示,高位补 0,如张无忌对应的 NULL 值列表为 00000011,张三丰对应的 NULL 值列表为 00000000

张三丰对应的记录行格式(00000000):

张无忌对应的记录行格式(00000011):

综上

如果一张表没有可空的字段,比如建表语句如下: 语句如下:

CREATE TABLE user_info (
id bigint unsigned NOT NULL AUTO_INCREMENT,
user_id int NOT NULL COMMENT '用户id',
user_name varchar(64NOT NULL COMMENT '真实姓名',
email varchar(30NOT NULL COMMENT '用户邮箱',
nick_name varchar(45NOT NULL COMMENT '昵称',
status tinyint NOT NULL COMMENT '用户状态,1-正常,2-注销,3-冻结',
address varchar(128NOT NULL COMMENT '家庭住址',
--省略了一些属性,篇幅原因,这里删掉了
PRIMARY KEY (id),
KEY idx_user_id (user_id)
ENGINE=InnoDB COMMENT='用户基本信息';

对应的行结构如下:

没有 NULL 值列表哪一项,但是虽然 NULL 列表中没有了,变长列表中会有,同时真实数据部分也会有,占用的空间更大,可见为了省空间这种说法站不住脚。

总结

原因到底是为什么呢? MySQL 官方有那么一句话:

NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.

中文意思是:

Mysql 难以优化引用可空列查询,它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间,还需要 mysql 内部进行特殊处理。可空列被索引后,每条记录都需要一个额外的字节,还能导致 MYisam 中固定大小的索引变成可变大小的索引。

照此看来,官方说法是其中一点原因

作者拙见:

  • 所有使用 NULL 值的情况,都可以通过一个有意义的值的表示,这样有利于代码的可读性和可维护性,并能从约束上增强业务数据的规范性。
  • NULL 值到非 NULL 的更新无法做到原地更新,更容易发生索引分裂,从而影响性能。
  • NULL 值在 timestamp 类型下容易出问题,特别是没有启用参数 explicit_defaults_for_timestamp
  • NOT IN、!= 等负向条件查询在有 NULL 值的情况下返回永远为空结果,查询容易出错,不是自己想要的结果,甚至可能出现重大损失

3、记录头

记录头占 5 个字节,40 位,下图是对应代表的含义

  • 1-2 位:预留,可以无视
  • 3 位:在记录刚被删除时,不是马上删掉,而是将其标记为已删除,实际还在占用空间
  • 4 位:最小目录项标记,B+树是非叶子节点(没个节点是一个 Innodb 页)内,最小目录项标记
  • 5-8 位:成员数量,Innodb 中,所有的记录间不是无关的,也是要再次分组的,这里表示一个组的组长。举个栗子,一个 Innodb 页好比军队的一个排长,下面每个班长都带个袖标,袖标上是班的人数
  • 9-21 位:相对位置,表示本记录在 Innodb 里的相对位置,可以简单的认为(不准确)在一个页里的第几条记录
  • 22-24 位:记录类型,一般记录类型为普通类型,值为 0
  • 25-40 位:下一条记录的相对位置,是指本记录距离下一条记录开头的真实位置,比如说,这里值为 64,就是从当前记录真实记录地址往后走 32 个字节,就是下一条记录的真实数据

cross

2021/06/29  阅读:56  主题:红绯

作者介绍

cross