为什么数据库字段要使用NOT NULL?(二)

发布于 2022-6-10 15:37
浏览
0收藏

 

其他问题

表中只有一条有名字的记录,此时查询名字!=a预期的结果应该是想查出来剩余的两条记录,会发现与预期结果不匹配。

 为什么数据库字段要使用NOT NULL?(二)-开源基础软件社区
索引问题

为了验证NULL字段对索引的影响,分别对name 和age添加索引。

 为什么数据库字段要使用NOT NULL?(二)-开源基础软件社区
关于网上很多说如果NULL那么不能使用索引的说法,这个描述其实并不准确,根据引用官方文档[3]里描述,使用is NULL和范围查询都是可以和正常一样使用索引的,实际验证的结果好像也是这样,看以下例子。

 为什么数据库字段要使用NOT NULL?(二)-开源基础软件社区
然后接着我们往数据库中继续插入一些数据进行测试,当NULL列值变多之后发现索引失效了。

 为什么数据库字段要使用NOT NULL?(二)-开源基础软件社区
我们知道,一个查询SQL执行大概是这样的流程:

 为什么数据库字段要使用NOT NULL?(二)-开源基础软件社区
首先连接器负责连接到指定的数据库上,接着看看查询缓存中是否有这条语句,如果有就直接返回结果。

如果缓存没有命中的话,就需要分析器来对SQL语句进行语法和词法分析,判断SQL语句是否合法。

现在来到优化器,就会选择使用什么索引比较合理,SQL语句具体怎么执行的方案就确定下来了。

最后执行器负责执行语句、有无权限进行查询,返回执行结果。

从上面的简单测试结果其实可以看到,索引列存在NULL就会存在书中所说的导致优化器在做索引选择的时候更复杂,更加难以优化。

存储空间

数据库中的一行记录在最终磁盘文件中也是以行的方式来存储的,对于InnoDB来说,有4种行存储格式:REDUNDANT、 COMPACT、 DYNAMIC 和 COMPRESSED。

InnoDB的默认行存储格式是COMPACT,存储格式如下所示,虚线部分代表可能不一定会存在。

 为什么数据库字段要使用NOT NULL?(二)-开源基础软件社区
变长字段长度列表:有多个字段则以逆序存储,我们只有一个字段所有不考虑那么多,存储格式是16进制,如果没有变长字段就不需要这一部分了。

NULL值列表:用来存储我们记录中值为NULL的情况,如果存在多个NULL值那么也是逆序存储,并且必须是8bit的整数倍,如果不够8bit,则高位补0。1代表是NULL,0代表不是NULL。如果都是NOT NULL那么这个就存在了。

ROW_ID:一行记录的唯一标志,没有指定主键的时候自动生成的ROW_ID作为主键。

TRX_ID:事务ID。

ROLL_PRT:回滚指针。

最后就是每列的值。

为了说明清楚这个存储格式的问题,我弄张表来测试,这张表只有c1字段是NOT NULL,其他都是可以为NULL的。

 为什么数据库字段要使用NOT NULL?(二)-开源基础软件社区
可变字段长度列表:c1和c3字段值长度分别为1和2,所以长度转换为16进制是0x01 0x02,逆序之后就是0x02 0x01。

NULL值列表:因为存在允许为NULL的列,所以c2,c3,c4分别为010,逆序之后还是一样,同时高位补0满8位,结果是00000010。

其他字段我们暂时不管他,最后第一条记录的结果就是,当然这里我们就不考虑编码之后的结果了。

 为什么数据库字段要使用NOT NULL?(二)-开源基础软件社区
这样就是一个完整的数据行数据的格式,反之,如果我们把所有字段都设置为NOT NULL,并且插入一条数据a,bb,ccc,dddd的话,存储格式应该这样:

 为什么数据库字段要使用NOT NULL?(二)-开源基础软件社区
虽然我们发现NULL本身并不会占用存储空间,但是如果存在NULL的话就会多占用一个字节的标志位的空间。

文章参考文档:

https://dev.mysql.com/doc/refman/8.0/en/problems-with-null.html
https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html
https://dev.mysql.com/doc/refman/5.6/en/is-null-optimization.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format.html
https://www.cnblogs.com/zhoujinyi/articles/2726462.html

 

文章转自公众号:艾小仙

分类
标签
已于2022-6-10 15:37:26修改
收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐