在项目中优化mysql的时候字段尽量不要用NULL 值,使用-1或者其他特殊标识来替代,原因如下:
- 为NULL的列会使用更多的存储空间,在Mysql中也需要特殊处理
- 对Mysql来说更难优化,因为可为NULL的列会使索引统计和值比较都更复杂。
- 当可为NULL的列被索引时,每个索引记录需要一个额外的字节,在MyISAM里甚至还可能导致固定大小的索引(例如只有一个整数列的索引)变成可变大小的索引。
索引问题
如果计划在列上建索引,列尽量设计成可not null
关于Mysql中null列是否会走索引:MySQL中NULL对索引的影响。虽然MySQL可以在含有null的列上使用索引,但不代表null和其他数据在索引中是一样的。
Mysql会在索引中存储NULL值,Oracle不会。
create table nulltesttable(
id int primary key,
name_not_null varchar(10) not null,
name_null varchar(10)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
alter table nulltesttable add index idx_nulltesttable_name_not_null(name_not_null);
alter table nulltesttable add index idx_nulltesttable_name_null(name_null);
explain select * from nulltesttable where name_not_null='name'; // explain1
explain select * from nulltesttable where name_null='name'; // explain2
从sql 执行可以看出, explain1中 key_len = 32, explain2中 key_len = 33
explain1的32 由来: 10(字段长度) * 3(utf8字符编码占用长度) + 2(varchar标识为变长占用长度)
explain2的32 由来: 10(字段长度) * 3(utf8字符编码占用长度) + 2(varchar标识为变长占用长度) + 1(null标识位占用长度)
SQL和函数问题
拼接函数规定,两个字符串拼接, 如果包含null值,则返回结果为null
insert into nulltesttable(id,name_not_null,name_null) values(1,'one',null);
insert into nulltesttable(id,name_not_null,name_null) values(2,'two','three');
select concat(name_not_null,name_null) from nulltesttable where id = 1; -- out: null
select concat(name_not_null,name_null) from nulltesttable where id = 2; -- out: twothree
关于null在not in上的应用会出现的坑:mysql 线上not in查询中的一个坑
-
在mysql中null与null不相等,除非使用<=> 。mysql null如何比较相等
-
null值与任何非null值比较都为null
-
not in中包含有null时,结果集一直为空
-
null字段不能直接用在in上,要附加is null 或is not null判断,因为in永远是in一个非null集合的
总结
不建议列上允许为空。最好限制not null,并设置一个默认值,比如-1和空字符串等,如果是datetime类型,可以设置成'1970-01-01 00:00:00
’这样的特殊值。
对MySQL来说,null是一个特殊的值,Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values。
比如:不能使用=,<,>这样的运算符,对null做算术运算的结果都是null,count时不会包括null行等,某列可为null比not null可能需要更多的存储空间等。