最近剛?cè)肼毿鹿纠矍Γl(fā)現(xiàn)數(shù)據(jù)庫設(shè)計(jì)有點(diǎn)小問題，數(shù)據(jù)庫字段很多沒有NOT NULL站叼，對(duì)于強(qiáng)迫癥晚期患者來說娃兽，簡(jiǎn)直難以忍受，因此有了這篇文章尽楔。

基于目前大部分的開發(fā)現(xiàn)狀來說投储，我們都會(huì)把字段全部設(shè)置成NOT NULL并且給默認(rèn)值的形式。

通常阔馋，對(duì)于默認(rèn)值一般這樣設(shè)置：

1. 整形玛荞，我們一般使用0作為默認(rèn)值。

2. 字符串呕寝，默認(rèn)空字符串

3. 時(shí)間勋眯，可以默認(rèn)1970-01-01 08:00:01，或者默認(rèn)0000-00-00 00:00:00，但是連接參數(shù)要添加zeroDateTimeBehavior=convertToNull客蹋，建議的話還是不要用這種默認(rèn)的時(shí)間格式比較好

但是塞蹭，考慮下原因，為什么要設(shè)置成NOT NULL讶坯？

來自高性能Mysql中有這樣一段話：

盡量避免NULL

很多表都包含可為NULL（空值）的列番电，即使應(yīng)用程序并不需要保存NULL也是如此，這是因?yàn)榭蔀镹ULL是列的默認(rèn)屬性辆琅。通常情況下最好指定列為NOT NULL漱办，除非真的需要存儲(chǔ)NULL值。

如果查詢中包含可為NULL的列婉烟，對(duì)MySql來說更難優(yōu)化娩井，因?yàn)榭蔀镹ULL的列使得索引、索引統(tǒng)計(jì)和值比較都更復(fù)雜似袁∽怖危可為NULL的列會(huì)使用更多的存儲(chǔ)空間，在MySql里也需要特殊處理叔营。當(dāng)可為NULL的列被索引時(shí)屋彪，每個(gè)索引記錄需要一個(gè)額外的字節(jié)，在MyISAM里甚至還可能導(dǎo)致固定大小的索引（例如只有一個(gè)整數(shù)列的索引）變成可變大小的索引绒尊。

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

當(dāng)然也有例外华糖，例如值得一提的是，InnoDB使用單獨(dú)的位（bit）存儲(chǔ)NULL值瘟裸，所以對(duì)于稀疏數(shù)據(jù)有很好的空間效率客叉。但這一點(diǎn)不適用于MyISAM。

書中的描述說了幾個(gè)主要問題话告，我這里暫且拋開MyISAM的問題不談兼搏，這里我針對(duì)InnoDB作為考量條件。

1. 如果不設(shè)置NOT NULL的話沙郭，NULL是列的默認(rèn)值佛呻，如果不是本身需要的話，盡量就不要使用NULL
2. 使用NULL帶來更多的問題病线，比如索引吓著、索引統(tǒng)計(jì)鲤嫡、值計(jì)算更加復(fù)雜，如果使用索引绑莺，就要避免列設(shè)置成NULL
3. 如果是索引列泛范，會(huì)帶來的存儲(chǔ)空間的問題，需要額外的特殊處理紊撕，還會(huì)導(dǎo)致更多的存儲(chǔ)空間占用
4. 對(duì)于稀疏數(shù)據(jù)又更好的空間效率，稀疏數(shù)據(jù)指的是很多值為NULL赡突，只有少數(shù)行的列有非NULL值的情況

默認(rèn)值

對(duì)于MySql而言对扶，如果不主動(dòng)設(shè)置為NOT NULL的話，那么插入數(shù)據(jù)的時(shí)候默認(rèn)值就是NULL惭缰。

NULL和NOT NULL使用的空值代表的含義是不一樣浪南，NULL可以認(rèn)為這一列的值是未知的，空值則可以認(rèn)為我們知道這個(gè)值漱受，只不過他是空的而已络凿。

舉個(gè)例子，一張表中的某一條name字段是NULL昂羡，我們可以認(rèn)為不知道名字是什么絮记，反之如果是空字符串則可以認(rèn)為我們知道沒有名字，他就是一個(gè)空值虐先。

而對(duì)于大多數(shù)程序的情況而言怨愤，沒有什么特殊需要非要字段要NULL的吧，NULL值反而會(huì)對(duì)程序造成比如空指針的問題蛹批。

對(duì)于現(xiàn)狀大部分使用MyBatis的情況來說撰洗，我建議使用默認(rèn)生成的insertSelective方法或者純手動(dòng)寫插入方法，可以避免新增NOT NULL字段導(dǎo)致的默認(rèn)值不生效或者插入報(bào)錯(cuò)的問題腐芍。

值計(jì)算

聚合函數(shù)不準(zhǔn)確

對(duì)于NULL值的列差导，使用聚合函數(shù)的時(shí)候會(huì)忽略NULL值。

現(xiàn)在我們有一張表猪勇，name字段默認(rèn)是NULL设褐，此時(shí)對(duì)name進(jìn)行count得出的結(jié)果是1，這個(gè)是錯(cuò)誤的泣刹。

count(*)是對(duì)表中的行數(shù)進(jìn)行統(tǒng)計(jì)络断，count(name)則是對(duì)表中非NULL的列進(jìn)行統(tǒng)計(jì)。

image

=失效

對(duì)于NULL值的列项玛，是不能使用=表達(dá)式進(jìn)行判斷的貌笨，下面對(duì)name的查詢是不成立的，必須使用is NULL襟沮。

image

與其他值運(yùn)算

NULL和其他任何值進(jìn)行運(yùn)算都是NULL锥惋，包括表達(dá)式的值也是NULL昌腰。

user表第二條記錄age是NULL，所以+1之后還是NULL膀跌，name是NULL遭商，進(jìn)行concat運(yùn)算之后結(jié)果還是NULL。

image

可以再看下下面的例子捅伤，任何和NULL進(jìn)行運(yùn)算的話得出的結(jié)果都會(huì)是NULL劫流，想象下你設(shè)計(jì)的某個(gè)字段如果是NULL還不小心進(jìn)行各種運(yùn)算，最后得出的結(jié)果丛忆。祠汇。。

image

distinct熄诡、group by可很、order by

對(duì)于distinct和group by來說，所有的NULL值都會(huì)被視為相等凰浮，對(duì)于order by來說升序NULL會(huì)排在最前

image

其他問題

表中只有一條有名字的記錄我抠，此時(shí)查詢名字!=a預(yù)期的結(jié)果應(yīng)該是想查出來剩余的兩條記錄，會(huì)發(fā)現(xiàn)與預(yù)期結(jié)果不匹配袜茧。

image

索引問題

為了驗(yàn)證NULL字段對(duì)索引的影響菜拓，分別對(duì)name 和age添加索引。

image

關(guān)于網(wǎng)上很多說如果NULL那么不能使用索引的說法笛厦，這個(gè)描述其實(shí)并不準(zhǔn)確尘惧，根據(jù)引用官方文檔[3]里描述，使用is NULL和范圍查詢都是可以和正常一樣使用索引的递递，實(shí)際驗(yàn)證的結(jié)果好像也是這樣喷橙，看以下例子。

image

然后接著我們往數(shù)據(jù)庫中繼續(xù)插入一些數(shù)據(jù)進(jìn)行測(cè)試登舞，當(dāng)NULL列值變多之后發(fā)現(xiàn)索引失效了贰逾。

image

我們知道，一個(gè)查詢SQL執(zhí)行大概是這樣的流程：

image

首先連接器負(fù)責(zé)連接到指定的數(shù)據(jù)庫上菠秒，接著看看查詢緩存中是否有這條語句疙剑，如果有就直接返回結(jié)果。

如果緩存沒有命中的話践叠，就需要分析器來對(duì)SQL語句進(jìn)行語法和詞法分析言缤，判斷SQL語句是否合法。

現(xiàn)在來到優(yōu)化器禁灼，就會(huì)選擇使用什么索引比較合理管挟，SQL語句具體怎么執(zhí)行的方案就確定下來了。

最后執(zhí)行器負(fù)責(zé)執(zhí)行語句弄捕、有無權(quán)限進(jìn)行查詢僻孝，返回執(zhí)行結(jié)果导帝。

從上面的簡(jiǎn)單測(cè)試結(jié)果其實(shí)可以看到，索引列存在NULL就會(huì)存在書中所說的導(dǎo)致優(yōu)化器在做索引選擇的時(shí)候更復(fù)雜穿铆，更加難以優(yōu)化您单。

存儲(chǔ)空間

數(shù)據(jù)庫中的一行記錄在最終磁盤文件中也是以行的方式來存儲(chǔ)的，對(duì)于InnoDB來說荞雏，有4種行存儲(chǔ)格式：REDUNDANT虐秦、 COMPACT、 DYNAMIC 和 COMPRESSED凤优。

InnoDB的默認(rèn)行存儲(chǔ)格式是COMPACT悦陋，存儲(chǔ)格式如下所示，虛線部分代表可能不一定會(huì)存在别洪。

image

變長(zhǎng)字段長(zhǎng)度列表：有多個(gè)字段則以逆序存儲(chǔ)，我們只有一個(gè)字段所有不考慮那么多柳刮，存儲(chǔ)格式是16進(jìn)制挖垛，如果沒有變長(zhǎng)字段就不需要這一部分了。

NULL值列表：用來存儲(chǔ)我們記錄中值為NULL的情況秉颗，如果存在多個(gè)NULL值那么也是逆序存儲(chǔ)痢毒，并且必須是8bit的整數(shù)倍，如果不夠8bit蚕甥，則高位補(bǔ)0哪替。1代表是NULL，0代表不是NULL菇怀。如果都是NOT NULL那么這個(gè)就存在了凭舶。

ROW_ID：一行記錄的唯一標(biāo)志，沒有指定主鍵的時(shí)候自動(dòng)生成的ROW_ID作為主鍵爱沟。

TRX_ID：事務(wù)ID帅霜。

ROLL_PRT：回滾指針。

最后就是每列的值呼伸。

為了說明清楚這個(gè)存儲(chǔ)格式的問題身冀，我弄張表來測(cè)試，這張表只有c1字段是NOT NULL括享，其他都是可以為NULL的搂根。

image

可變字段長(zhǎng)度列表：c1和c3字段值長(zhǎng)度分別為1和2，所以長(zhǎng)度轉(zhuǎn)換為16進(jìn)制是0x01 0x02铃辖，逆序之后就是0x02 0x01剩愧。

NULL值列表：因?yàn)榇嬖谠试S為NULL的列，所以c2,c3,c4分別為010娇斩，逆序之后還是一樣隙咸，同時(shí)高位補(bǔ)0滿8位沐悦，結(jié)果是00000010。

其他字段我們暫時(shí)不管他五督，最后第一條記錄的結(jié)果就是藏否，當(dāng)然這里我們就不考慮編碼之后的結(jié)果了。

image

這樣就是一個(gè)完整的數(shù)據(jù)行數(shù)據(jù)的格式充包，反之副签，如果我們把所有字段都設(shè)置為NOT NULL，并且插入一條數(shù)據(jù)a,bb,ccc,dddd的話基矮，存儲(chǔ)格式應(yīng)該這樣：

image

雖然我們發(fā)現(xiàn)NULL本身并不會(huì)占用存儲(chǔ)空間淆储，但是如果存在NULL的話就會(huì)多占用一個(gè)字節(jié)的標(biāo)志位的空間。

文章參考文檔：

1. https://dev.mysql.com/doc/refman/8.0/en/problems-with-null.html
2. https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html
3. https://dev.mysql.com/doc/refman/5.6/en/is-null-optimization.html
4. https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format.html
5. https://www.cnblogs.com/zhoujinyi/articles/2726462.html