为什么建议MySQL列属性尽量NOT NULL

本文测试环境：MySQL5.7，Engine=InnoDB，charset=utf8。

先看一段官方的话：很多表都包含可为null(空值)的列，即使应用程序并不需要保存null也是如此，这是因为可为null是列的默认属性。通常情况下最好指定为Not null，除非真的需要存储null值。

如果查询中包含可为null的列，对MySQL来说更难优化，因为可为null的列使得索引、索引统计和值比较都更复杂。

可为null的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为null的列被索引时，每个索引记录需要一个额外的字节。在MyISAM里甚至还可能导致固定大小的索引(例如只有一个整数列的索引)变成可变大小的索引。

通常把可为null的列改为not null带来的性能提升比较小，所以调优时没有必要首先在现有schema中查找并修改掉这种情况，除非确定这会导致问题。但是如果计划在列上建索引，就应该尽量避免设计为可为null的列。

当然也有例外，值得一提的是，InnoDB使用单独的位(bit)存储null值，所以对于稀疏数据有很好的空间效率。但这一点不适用于MyISAM。关于这一点可以参考博文认真学习InnoDB的行格式

本地创建两个表，user 和user2，表字段一样，但是user中字段user_name不允许为null，user2中user_name default null，都为user_name添加上普通索引。

user表数据：

user2表数据：

① 在使用 not in、!=查询结果集时，字段值为null的列不会出现在结果中

如下所示：

select * from user2 where user_name !='admin'

查询结果如下(没有出现ID为2的那一列)：

使用not in测试同上：

select * from user2 where user_name not in
(select user_name from user2 where user_id != 1)

② 在使用null作为条件判断时，应该使用is null或is not null ，而不要使用= 或者 !=

select * from user2 where user_name is null;
select * from user2 where user_name is NOT null;

③ 使用 concat 函数拼接时，首先要对各个字段进行非 NULL 判断，否则只要任何一个字段为空都会造成拼接的结果为 NULL。

如下所示，拼接最终结果为null：

null同样不能用于算术运算：

④ 在使用count(列名)进行统计时，null不会计入统计

select count(2) from user2;--2
select count(*) from user2;--2
select count(user_name) from user2;--1

如上所示，在使用count(列名)进行统计时，null不会计入统计。

count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。

⑤ NULL 列需要更多的存储空间

一般需要一个额外的字节作为判断是否为 NULL 的标志位。如果你仔细观察 user 和user2表的 key_len，会发现 user2 比user 多了一个字节。

explain select * from user where user_name ='admin'

explain select * from user2 where user_name ='admin';

key_len 的长度一般跟这三个因素有关，分别是数据类型，字符编码，是否为 NULL。

因此，user2比 user1 多出的这一个字节，用于作为判断是否为 NULL 的标志位了。

⑥ is null is not null可能用不到索引

如果某列可为null，那么当使用is null 或者 is not null进行判断的时候用不到索引。可能我们在很多地方看到过这句话，那么一定是这样吗？

① 单列索引测试

如下创建表testc2，单独为c1创建索引，c1 默认为null。

CREATE TABLE `testc2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `c1` varchar(100) DEFAULT NULL,
  `c2` varchar(100) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` varchar(100) DEFAULT NULL,
  `c5` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `testc_c1_IDX` (`c1`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

在没有null值存在的情况下进行验证，is not null没有用到索引 ，但是is null 用到了索引。

我们修改id=1的记录中c1为null,然后再进行测试。

update testc2  set c1 = null where id=1

如下两图所示 is not null没有用到索引，is null用到了索引。

针对上面测试结果，我们可以得到如下表格。

出于好奇，我们将c1设置为not null，并填充了数据后再次进行测试，发现此时均用不到索引。

explain select *  from testc2 where c1 is not null ;
# Impossible WHERE
explain select *  from testc2 where c1 is  null ;

② 联合索引测试

CREATE TABLE `testc` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `c1` varchar(100) DEFAULT NULL,
  `c2` varchar(100) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` varchar(100) DEFAULT NULL,
  `c5` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `testc_c1_IDX` (`c1`,`c2`,`c3`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

如上所示，我们创建联合索引，索引列可为null，但是此时没有数据为null。

如上四种情况，均用到了索引。

我们将 id =1 的记录的c2设置为null，再进行上面验证：

update testc  set c2 =null where id=1;

如上，四种情况，均用到了索引。

我们将 id =1 的记录的c1设置为null，再进行上面验证：

update testc  set c1 =null where id=1;
explain select *  from testc where c1 is null;
explain select *  from testc where c1 is not null;
explain select *  from testc where c1 ='b1' and c2 is  null;
explain select *  from testc where c1 ='b1' and c2 is not null;

可以看到，同样均使用到了索引 ！

同样出于好奇（虽然没啥实际意义），我们将数据进行了填充并设置c1 not null ,然后进行测试，如下图所示，此时没有用到索引。