為什么數(shù)據(jù)庫字段要使用NOT NULL？

最近剛?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ì)。

=失效

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

與其他值運(yùn)算

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

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

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

distinct、group by、order by

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

其他問題

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

索引問題

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

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

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

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

首先連接器負(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ì)存在。

變長(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的。

可變字段長(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é)果了。

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

雖然我們發(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