淺談為什么Mysql數(shù)據(jù)庫盡量避免NULL

在Mysql中很多表都包含可為NULL（空值）的列，即使應(yīng)用程序并不需要保存NULL也是如此，這是因?yàn)榭蔀镹ULL是列的默認(rèn)屬性。但我們常在一些Mysql性能優(yōu)化的書或者一些博客中看到觀點(diǎn)：在數(shù)據(jù)列中，盡量不要用NULL 值，使用0，-1或者其他特殊標(biāo)識(shí)替換NULL值，除非真的需要存儲(chǔ)NULL值，那到底是為什么？如果替換了會(huì)有什么好處？同時(shí)又有什么問題呢？那么就看下面：

（1）如果查詢中包含可為NULL的列，對(duì)Mysql來說更難優(yōu)化，因?yàn)榭蔀镹ULL的列使得索引，索引統(tǒng)計(jì)和值比較都更復(fù)雜。

（2）含NULL復(fù)合索引無效.

（3）可為NULL的列會(huì)使用更多的存儲(chǔ)空間，在Mysql中也需要特殊處理。

（4）當(dāng)可為NULL的列被索引時(shí)，每個(gè)索引記錄需要一個(gè)額外的字節(jié)，在MyISAM里甚至還可能導(dǎo)致固定大小的索引（例如只有一個(gè)整數(shù)列的索引）變成可變大小的索引。

理由佐證

理由1不需要佐證

首先新建環(huán)境, sql語句如下

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 執(zhí)行可以看出, explain1中 key_len = 32, explain2中 key_len = 33
explain1的32 由來: 10(字段長度) * 3(utf8字符編碼占用長度) + 2(varchar標(biāo)識(shí)為變長占用長度)
explain2的32 由來: 10(字段長度) * 3(utf8字符編碼占用長度) + 2(varchar標(biāo)識(shí)為變長占用長度) + 1(null標(biāo)識(shí)位占用長度)

兩個(gè)字符串拼接, 如果包含null值, 則返回結(jié)果為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值, 且這個(gè)字段被索引. 如下的查詢可能會(huì)返回不正確的結(jié)果

select * from nulltesttable where name_null > 'three' -- out: null
select count(name_null) from nulltesttable -- out: 1 

通常把可為NULL的列改為NOT NULL 帶來的性能提升比較小，所以（調(diào)優(yōu)時(shí)）沒有必要首先在現(xiàn)有schema中查找并修改掉這種情況，除非確定這會(huì)導(dǎo)致問題。但是，如果計(jì)劃在列上建索引，就應(yīng)該盡量避免設(shè)計(jì)成可為NULL的列。

當(dāng)確實(shí)需要標(biāo)識(shí)未知值時(shí)也不要害怕使用NULL。在一些場(chǎng)景中，使用NULL可能會(huì)比某個(gè)神奇常數(shù)更好。從特定類型的值域中選擇一個(gè)不可能的值，例如用-1代表一個(gè)未知數(shù)，可能導(dǎo)致代碼復(fù)雜的多，并容易引入BUG，還可能讓事情變得一團(tuán)糟（注：Mysql會(huì)在索引中存儲(chǔ)NULL值，Oracle不會(huì)）。

當(dāng)然也有例外，InnoDB使用單獨(dú)的位（bit）來存儲(chǔ)NULL值，所以對(duì)于稀疏數(shù)據(jù)(很多值位NULL，只有少數(shù)行的列有非NULL值)由很好的空間效率，這一點(diǎn)不適用于MyISAM。

所以任何的設(shè)計(jì)和考慮請(qǐng)注意關(guān)注實(shí)際需求

到此這篇關(guān)于淺談為什么Mysql數(shù)據(jù)庫盡量避免NULL的文章就介紹到這了,更多相關(guān)Mysql避免NULL內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！