专栏首页艾小仙为什么数据库字段要使用NOT NULL?

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

最近刚入职新公司,发现数据库设计有点小问题,数据库字段很多没有NOT NULL,对于强迫症晚期患者来说,简直难以忍受,因此有了这篇文章。

基于目前大部分的开发现状来说,我们都会把字段全部设置成NOT NULL并且给默认值的形式。

通常,对于默认值一般这样设置:

  1. 整形,我们一般使用0作为默认值。
  2. 字符串,默认空字符串
  3. 时间,可以默认1970-01-01 08:00:01,或者默认0000-00-00 00:00:00,但是连接参数要添加zeroDateTimeBehavior=convertToNull,建议的话还是不要用这种默认的时间格式比较好

但是,考虑下原因,为什么要设置成NOT NULL?

来自高性能Mysql中有这样一段话:

尽量避免NULL 很多表都包含可为NULL(空值)的列,即使应用程序并不需要保存NULL也是如此,这是因为可为NULL是列的默认属性。通常情况下最好指定列为NOT NULL,除非真的需要存储NULL值。 如果查询中包含可为NULL的列,对MySql来说更难优化,因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间,在MySql里也需要特殊处理。当可为NULL的列被索引时,每个索引记录需要一个额外的字节,在MyISAM里甚至还可能导致固定大小的索引(例如只有一个整数列的索引)变成可变大小的索引。 通常把可为NULL的列改为NOT NULL带来的性能提升比较小,所以(调优时)没有必要首先在现有schema中查找并修改掉这种情况,除非确定这会导致问题。但是,如果计划在列上建索引,就应该尽量避免设计成可为NULL的列。 当然也有例外,例如值得一提的是,InnoDB使用单独的位(bit)存储NULL值,所以对于稀疏数据有很好的空间效率。但这一点不适用于MyISAM。

书中的描述说了几个主要问题,我这里暂且抛开MyISAM的问题不谈,这里我针对InnoDB作为考量条件。

  1. 如果不设置NOT NULL的话,NULL是列的默认值,如果不是本身需要的话,尽量就不要使用NULL
  2. 使用NULL带来更多的问题,比如索引、索引统计、值计算更加复杂,如果使用索引,就要避免列设置成NULL
  3. 如果是索引列,会带来的存储空间的问题,需要额外的特殊处理,还会导致更多的存储空间占用
  4. 对于稀疏数据有更好的空间效率,稀疏数据指的是很多值为NULL,只有少数行的列有非NULL值的情况

默认值

对于MySql而言,如果不主动设置为NOT NULL的话,那么插入数据的时候默认值就是NULL。

NULL和NOT NULL使用的空值代表的含义是不一样,NULL可以认为这一列的值是未知的,空值则可以认为我们知道这个值,只不过他是空的而已。

举个例子,一张表中的某一条name字段是NULL,我们可以认为不知道名字是什么,反之如果是空字符串则可以认为我们知道没有名字,他就是一个空值

而对于大多数程序的情况而言,没有什么特殊需要非要字段要NULL的吧,NULL值反而会对程序造成比如空指针的问题。

对于现状大部分使用MyBatis的情况来说,我建议使用默认生成的insertSelective方法或者纯手动写插入方法,可以避免新增NOT NULL字段导致的默认值不生效或者插入报错的问题。

值计算

聚合函数不准确

对于NULL值的列,使用聚合函数的时候会忽略NULL值。

现在我们有一张表,name字段默认是NULL,此时对name进行count得出的结果是1,这个是错误的。

count(*)是对表中的行数进行统计,count(name)则是对表中非NULL的列进行统计。

=失效

对于NULL值的列,是不能使用=表达式进行判断的,下面对name的查询是不成立的,必须使用is NULL

与其他值运算

NULL和其他任何值进行运算都是NULL,包括表达式的值也是NULL。

user表第二条记录age是NULL,所以+1之后还是NULL,name是NULL,进行concat运算之后结果还是NULL。

可以再看下下面的例子,任何和NULL进行运算的话得出的结果都会是NULL,想象下你设计的某个字段如果是NULL还不小心进行各种运算,最后得出的结果。。。

distinct、group by、order by

对于distinctgroup by来说,所有的NULL值都会被视为相等,对于order by来说升序NULL会排在最前

其他问题

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

索引问题

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

关于网上很多说如果NULL那么不能使用索引的说法,这个描述其实并不准确,根据引用官方文档[3]里描述,使用is NULL和范围查询都是可以和正常一样使用索引的,实际验证的结果好像也是这样,看以下例子。

然后接着我们往数据库中继续插入一些数据进行测试,当NULL列值变多之后发现索引失效了。

我们知道,一个查询SQL执行大概是这样的流程:

首先连接器负责连接到指定的数据库上,接着看看查询缓存中是否有这条语句,如果有就直接返回结果。

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

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

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

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

存储空间

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

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

变长字段长度列表:有多个字段则以逆序存储,我们只有一个字段所有不考虑那么多,存储格式是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的。

可变字段长度列表c1c3字段值长度分别为1和2,所以长度转换为16进制是0x01 0x02,逆序之后就是0x02 0x01

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

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

这样就是一个完整的数据行数据的格式,反之,如果我们把所有字段都设置为NOT NULL,并且插入一条数据a,bb,ccc,dddd的话,存储格式应该这样:

虽然我们发现NULL本身并不会占用存储空间,但是如果存在NULL的话就会多占用一个字节的标志位的空间。

文章参考文档:

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

·················END·················

本文分享自微信公众号 - 艾小仙(aixiaoxianren),作者:艾小仙

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-04-05

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 为什么要使用Redis的多数据库

    为什么要使用redis的多数据库,我们项目中就这么用了,这点我也想不明白。如果是要做业务隔离,那么可以给不同业务的缓存key添加一个前缀,如果因此导致key过长...

    Java艺术
  • 双重检查锁为什么要使用volatile字段?

    单例模式中,有一个DCL(双重锁)的实现方式。在Java程序中,有时候可能需要推迟一些高开销的对象初始化操作,并且只有在使用这些对象时才开始初始化。

    Java_老男孩
  • 为什么需要数据库?

    excel可以存放十几万的数据,但是大数据时代,从来就不缺少数据,当数据量越来越大的时候,excel就无能为力了。而数据库却可以存放海量的数据。

    猴子聊数据分析
  • 数据库软件怎么安装 为什么要使用数据库软件

    在这个大数据的时代,有成千上万的数据,建立数据库软件是非常有必要的。急要对数据进行合理的分配和管理,这样既有利于数据利用时很方便,也有利于数据共享。毕竟一个数据...

    用户8739990
  • 使用新类型Nullable处理数据库表中null字段

    在.net 2.0中,提供了 Nullable的范型,通过它,我们可以为基础类型如int等赋予null的值,这样我们就可以处理null值了。 例子代码 数据表...

    张善友
  • 为什么要使用redis数据库?它有哪些妙用?

    redis是Nosql数据库中使用较为广泛的非关系型内存数据库,redis内部是一个key-value存储系统。它支持存储的value类型相对更多,包括stri...

    Java编程指南
  • Java : 对象不再使用时,为什么要赋值为 null ?

    原文链接:http://www.polarxiong.com/

    业余草
  • 数据库索引为什么使用B+树?

    java404
  • 为什么数据库不应该使用外键

    当我们想要持久化地存储数据时,使用关系型数据库往往都是最稳妥的选择,这不仅因为今天的关系型数据库种类非常丰富并且稳定,还因为不同社区对关系型数据库的支持都非常完...

    猿天地
  • 为什么不推荐数据库使用外键?

    我的经验告诉我,很多数据库(大多数我曾经使用的)不包含外键时并不总是一件坏事。在这篇文章中,我想把重点放在为什么的原因上。

    一个优秀的废人
  • 为什么数据库索引数据结构使用B+树,而不使用xxx?

    这个问题其实还是很有趣的,我在上一篇文章中,写了: 1、为什么数据库索引不能用二叉排序树; 2、为什么数据库索引不能用红黑树;

    TrueDei
  • 为什么MySQL数据库索引选择使用B+树?

    在进一步分析为什么MySQL数据库索引选择使用B+树之前,我相信很多小伙伴对数据结构中的树还是有些许模糊的,因此我们由浅入深一步步探讨树的演进过程,在一步步引出...

    Java后端技术
  • 为什么MySQL数据库索引选择使用B+树?

    我们在MySQL中的数据一般是放在磁盘中的,读取数据的时候肯定会有访问磁盘的操作,磁盘中有两个机械运动的部分,分别是盘片旋转和磁臂移动。盘片旋转就是我们市面上所...

    用户3467126
  • 什么是大数据营销?企业为什么要建立自己的数据库?

    什么是大数据营销? 大数据营销是衍生于互联网行业,又作用于互联网行业。依托多个平台的大数据技术的采集,以及大数据技术的分析与预测能力,能够应用于互联网广告行业的...

    企鹅号小编
  • 究竟为什么要引入数据库中间件

    不少朋友经常会问我以下问题: 58到家有没有使用数据库中间件 使用了什么数据库中间件,是自研,还是第三方 怎么实现的,是基于客户端的中间件,还是基于服务端的中间...

    架构师之路
  • 为什么有了 MySQL ,还要有时序数据库?

    近日,UCloud新发布了一款时间序列数据库UTSDB (UCloud TimeSeries Database) ,此次上线的UTSDB-InfluxDB版基于...

    芋道源码
  • 数据库连接池为什么要用threadlocal呢?(不用会怎样?)

    我先说为什么引入threadlocal,其实是为了解决数据库事务,而事务是和连接有关的,每个连接对应一个事务,多个连接的事务是不一样的,先大概了解一下,往下看?

    架构师修炼
  • 网络请求为什么要使用第三方库???

    版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u010105969/article/details/...

    用户1451823
  • 想要玩转Power BI,为什么一定要学会数据库?

    最近写过多篇文章强调,使用Power BI建立模型时,一定要从SQL导入数据,而不要直接使用excel文件,今天再来啰嗦两句。

    陈学谦

扫码关注云+社区

领取腾讯云代金券