DBA不让字段设置可空,理由太荒唐!

程序员阿牛
2021-06-15 / 1 评论 / 489 阅读
温馨提示:
本文最后更新于2021-06-15,若内容或图片失效,请留言反馈。

背景

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

为了证明这种说法错误,特地做了整理。

语句如下:

CREATE TABLE user_info (
id bigint unsigned NOT NULL AUTO_INCREMENT,
user_id int NOT NULL COMMENT '用户id',
user_name varchar(64) NOT NULL COMMENT '真实姓名',
email varchar(30) NOT NULL COMMENT '用户邮箱',
nick_name varchar(45) COMMENT '昵称',
status tinyint NOT NULL COMMENT '用户状态,1-正常,2-注销,3-冻结',
address varchar(128) COMMENT '家庭住址',
--省略了一些属性,篇幅原因,这里删掉了
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 者基本上是一样的,只有一些细微的查询,不做展开了)
1

记录额外信息

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

1、变长属性长度列表

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

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

我们来举一个栗子:
1

上面的记录 user_name,email,nick_name,address 都是 varchar 类型的,那么他存储的记录格式是什么样的呢
1
上图中的 06、09、0b、06、09 分别对应 user_name,email,nick_name、address 对应字节长度,16 进制(实际上无空格,这里只是为了方便看)。

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

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

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

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

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

也就意味着,如果RL大于127,占用的字节也是2个,如果属性值很长,也不会把数据都存进来,只会存一部分或者其真实数据地址

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

则对应的行格式如下:

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

2、NULL 值列表

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

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

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

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

综上

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

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

对应的行结构如下:
1

没有 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 值的情况下返回永远为空结果,查询容易出错,不是自己想要的结果,甚至可能出现重大损失

更多分享,请关注博主公众号:
gzh