Mysql篇（一）：小小的Null，居然影响这么大？！

青梅主码 2021-06-29

3601

点击蓝字关注我们

大家好，我是杰哥

相信Mysql大家经常在用吧，那你肯定也知道下面这两个存在的事实。相信很多人在最初跟我一样，也会觉得很神奇，想着mysql也真是任性，规则想怎样定就怎样定，不信你看看

一现象

你知道为何吗？

01.判断是否为null值，只能使用is (not) null

先来创建一个"test_user"表，建表语句如下

CREATE TABLE `test_user` (
`id` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户id',
`name` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '姓名',
`age` int(10) DEFAULT NULL COMMENT '年龄',
`sex` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '性别',
`t_id` int(11) DEFAULT NULL COMMENT '关联id',
  PRIMARY KEY (`id`),
UNIQUE KEY `t_index` (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

其中id为主键，t_id列为唯一索引列

接下来，向表中插入两条数据，其中一条数据的name列的值为null

INSERT INTO `test_user` VALUES ('1', '杰哥', 21, '1', 1);
INSERT INTO `test_user` VALUES ('2', NULL, 22, '0', 3);

OK，准备工作已完毕。接下来，让我们分别测试一下is null 、=null 以及!=null的查询情况

1）select * from test_user where name is NULL;

查询结果即为：name值为null的记录

2）select * from test_user where name = NULL;

查询结果为0条记录

3）select * from test_user where name != NULL;

查询结果为0条记录

很明显，只有is null 这种条件，查询的结果才是我们真正想要的，而=null 和!=null的条件似乎与"where false"的效果是一致的，怎样都没有记录返回

再来看看下面这个现象

02.唯一索引不可以重复，但是会出现多个null值

我们看到，t_id字段是一个唯一索引，此时向表里插入一条t_id为1的记录

会出现索引冲突的问题

再来添加两条t_id为null的数据

INSERT INTO `adp_test`.`test_user`(`id`, `name`, `age`, `sex`, `t_id`) VALUES ('3', '小明', 20, '1', NULL);
INSERT INTO `adp_test`.`test_user`(`id`, `name`, `age`, `sex`, `t_id`) VALUES ('4', '小花', 20, '0', NULL);

我们发现两条都可以成功

也就是说唯一索引虽然不能重复，但是却允许多个null值

二本质

究竟是为何？

也许你以前从来没有注意到这一点，但现在看到这个现象之后，一定能得出：

之所以会出现这两个现象，是因为每个null其实都对应不一样的值。是的，也可以理解成：

每个null其实都是一块独一无二的空间，因此每个null都是不相同的

三延伸

其他说法

此外，我们还会经常说到要尽量在表里面对于字段要尽可能地声明not null，或者最好不在不为索引列中放null

这又是为何呢？

1 null值的存储结构

mysql中的行记录格式如图所示

总得来说，mysql的一条行记录分为两个部分：记录的头部数据和记录的真实数据。真实数据都很好理解，就是我们平常真实看到的各个列的值。而记录的头部数据，则分别包含变长字段长度列表，null值列表以及记录头信息三个部分

1）变长字段长度列表

存放所有变长字段的真实数据占用的字节长度，形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放。

当然要是某个字段的长度为null了，就没必要开辟空间去存它了，所以呢，此处只会包含非null的变长字段

2）记录头信息

用于描述记录的记录头信息
，它是由固定的5
个字节组成。5
个字节也就是40
个二进制位，不同的位代表不同的意思，比如说是否删除的标记位啊之类的信息，此处不是重点，就先不赘述了

3）NULL值列表

在定义表时，某个字段如果没有加not null 约束，实际存储的值就会出现null和非null两种情况

mysql的null值列表，就用来将存放这些数据是否为null情况。若字段为null，对应位存储为1，否则为0。与变长字段列表一样，null值列表也是逆序排列

当然，表中未声明not null的字段值越少，这个区域占的空间就越来越小了，而当表中的字段均有not null的约束时，null值列表区域就自然不存在了~

总结

也就是说，在任何时候，为了节省空间，尽量要考虑为字段加上not null的约束

若能确定该字段不可能为null的时候，肯定要加上not null约束
若字段有可能为null的话，也要尽量加上not null。用默认值（如空字符串、0等值）去替代null值，这样不仅符合真实业务逻辑，也可以尽可能地节省空间，从而提升查询效率

2 索引中存在null时如何处理

首先，索引列中若存在null值，在查询的时候，依旧可能使用到索引

上面两个步骤，我们分别往test_user表中添加了两条数据，它目前的数据共4条：

这时，执行语句

explain SELECT * from test_user where t_id is null;

查看其执行计划，如下

我们看到，该语句使用到了索引。

但当我们再更新id为2的行的t_id的值为null，出现了神奇的一幕

此时，执行同样的sql语句，却并没有使用到索引！

咦~这是为什么呢？

这是因为呢，mysql在执行sql语句之前，会对各种可能的执行方案进行成本的计算分析，挑选出成本最低的一种方式来执行sql

这里，当表中的null值特别多的情况下，也就是说该索引列的基数太小的情况下，二级索引查出来的重复值（t_id为null的值）就特别多，查出来以后，还要再继续用这些重复值对应的主键，再回表查询一次，相对于直接去查全表的性能就差太多了

那么，当唯一二级索引列中null值太多或者普通二级索引中的重复值太多，即基数太小时，往往会采用全表扫描进行查询

以上，这么说，大家也就很容易明白了，实际上在这里，mysql在分析成本的过程中，是将所有的null看成一样的值的，它是通过innodb_stats_method这个全局变量来控制在统计某个索引列重复值数量时，对待NULL值的方式

innodb_stats_method
它包含三个值:

nulls_equal：
表示null值都相同

nulls_unequal：
表示null值都不同

nulls_ignored
：忽略掉null值

显然，出现上述现象的情况，肯定是将null看成是相同的值来对待的了，们来验证一下，执行SHOW VARIABLES LIKE 'innodb_stats_method';

当当当，豁然开朗，有木有~

3 null值与空字符串的比较

很多文章常常将null值与空字符串进行比较，其实在我看来，根本没有比较的意义。因为，在mysql的设计中，空值''与null值本身就是不一样的，可以说空值，是另一个特殊的存在。它不占空间，就是一个非null的值，那么自然跟null是不一样的。所以再要出现设置了not null约束的列，依旧可以插入空字符串值，就没什么想不通的了

四总结

总而言之

好了，本篇主要围绕mysql的null值，进行了一番分析，看到这里，我想你肯定知道了以下几点

1 mysql中的每个null值并不同

2 mysql中的null值存储结构

3 mysql索引中存在null时的处理情况

4 mysql与空值的比较

小小的null，在mysql中还是比较特殊的，太多可以null的列会导致表将占用更多的存储空间，影响查询效率，还会影响业务逻辑。

所以，建议在建表时，尽量为字段加上not null约束

并且，尽量选择为基数大的字段建索引，而不是基数小的字段哦~

嗯，就这样。每天学习一点，时间会见证你的强大~

下期预告：