淺談為什么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)識替換NULL值,除非真的需要存儲NULL值,那到底是為什么?如果替換了會有什么好處?同時又有什么問題呢?那么就看下面:
(1)如果查詢中包含可為NULL的列,對Mysql來說更難優(yōu)化,因?yàn)榭蔀镹ULL的列使得索引,索引統(tǒng)計(jì)和值比較都更復(fù)雜。
(2)含NULL復(fù)合索引無效.
(3)可為NULL的列會使用更多的存儲空間,在Mysql中也需要特殊處理。
(4)當(dāng)可為NULL的列被索引時,每個索引記錄需要一個額外的字節(jié),在MyISAM里甚至還可能導(dǎo)致固定大小的索引(例如只有一個整數(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)識為變長占用長度)
explain2的32 由來: 10(字段長度) * 3(utf8字符編碼占用長度) + 2(varchar標(biāo)識為變長占用長度) + 1(null標(biāo)識位占用長度)
兩個字符串拼接, 如果包含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值, 且這個字段被索引. 如下的查詢可能會返回不正確的結(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)時)沒有必要首先在現(xiàn)有schema中查找并修改掉這種情況,除非確定這會導(dǎo)致問題。但是,如果計(jì)劃在列上建索引,就應(yīng)該盡量避免設(shè)計(jì)成可為NULL的列。
當(dāng)確實(shí)需要標(biāo)識未知值時也不要害怕使用NULL。在一些場景中,使用NULL可能會比某個神奇常數(shù)更好。從特定類型的值域中選擇一個不可能的值,例如用-1代表一個未知數(shù),可能導(dǎo)致代碼復(fù)雜的多,并容易引入BUG,還可能讓事情變得一團(tuán)糟(注:Mysql會在索引中存儲NULL值,Oracle不會)。
當(dāng)然也有例外,InnoDB使用單獨(dú)的位(bit)來存儲NULL值,所以對于稀疏數(shù)據(jù)(很多值位NULL,只有少數(shù)行的列有非NULL值)由很好的空間效率,這一點(diǎn)不適用于MyISAM。
所以任何的設(shè)計(jì)和考慮請注意關(guān)注實(shí)際需求
到此這篇關(guān)于淺談為什么Mysql數(shù)據(jù)庫盡量避免NULL的文章就介紹到這了,更多相關(guān)Mysql避免NULL內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站!
版權(quán)聲明:本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有,歡迎引用、轉(zhuǎn)載,請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站,禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像,否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來,僅供學(xué)習(xí)參考,不代表本站立場,如有內(nèi)容涉嫌侵權(quán),請聯(lián)系alex-e#qq.com處理。