為什麼資料庫欄位要使用NOT NULL?

艾小仙發表於2021-04-05

最近剛入職新公司,發現資料庫設計有點小問題,資料庫欄位很多沒有NOT NULL,對於強迫症晚期患者來說,簡直難以忍受,因此有了這篇文章。

基於目前大部分的開發現狀來說,我們都會把欄位全部設定成NOT NULL並且給預設值的形式。

通常,對於預設值一般這樣設定:

  1. 整形,我們一般使用0作為預設值。

  2. 字串,預設空字串

  3. 時間,可以預設1970-01-01 08:00:01,或者預設0000-00-00 00:00:00,但是連線引數要新增zeroDateTimeBehavior=convertToNull,建議的話還是不要用這種預設的時間格式比較好

但是,考慮下原因,為什麼要設定成NOT NULL?

來自高效能Mysql中有這樣一段話:

儘量避免NULL

很多表都包含可為NULL(空值)的列,即使應用程式並不需要儲存NULL也是如此,這是因為可為NULL是列的預設屬性。通常情況下最好指定列為NOT NULL,除非真的需要儲存NULL值。

如果查詢中包含可為NULL的列,對MySql來說更難優化,因為可為NULL的列使得索引、索引統計和值比較都更復雜。可為NULL的列會使用更多的儲存空間,在MySql裡也需要特殊處理。當可為NULL的列被索引時,每個索引記錄需要一個額外的位元組,在MyISAM裡甚至還可能導致固定大小的索引(例如只有一個整數列的索引)變成可變大小的索引。

通常把可為NULL的列改為NOT NULL帶來的效能提升比較小,所以(調優時)沒有必要首先在現有schema中查詢並修改掉這種情況,除非確定這會導致問題。但是,如果計劃在列上建索引,就應該儘量避免設計成可為NULL的列。

當然也有例外,例如值得一提的是,InnoDB使用單獨的位(bit)儲存NULL值,所以對於稀疏資料有很好的空間效率。但這一點不適用於MyISAM。

書中的描述說了幾個主要問題,我這裡暫且拋開MyISAM的問題不談,這裡我針對InnoDB作為考量條件。

  1. 如果不設定NOT NULL的話,NULL是列的預設值,如果不是本身需要的話,儘量就不要使用NULL
  2. 使用NULL帶來更多的問題,比如索引、索引統計、值計算更加複雜,如果使用索引,就要避免列設定成NULL
  3. 如果是索引列,會帶來的儲存空間的問題,需要額外的特殊處理,還會導致更多的儲存空間佔用
  4. 對於稀疏資料又更好的空間效率,稀疏資料指的是很多值為NULL,只有少數行的列有非NULL值的情況

預設值

對於MySql而言,如果不主動設定為NOT NULL的話,那麼插入資料的時候預設值就是NULL。

NULL和NOT NULL使用的空值代表的含義是不一樣,NULL可以認為這一列的值是未知的,空值則可以認為我們知道這個值,只不過他是空的而已。

舉個例子,一張表中的某一條name欄位是NULL,我們可以認為不知道名字是什麼,反之如果是空字串則可以認為我們知道沒有名字,他就是一個空值

而對於大多數程式的情況而言,沒有什麼特殊需要非要欄位要NULL的吧,NULL值反而會對程式造成比如空指標的問題。

對於現狀大部分使用MyBatis的情況來說,我建議使用預設生成的insertSelective方法或者純手動寫插入方法,可以避免新增NOT NULL欄位導致的預設值不生效或者插入報錯的問題。

值計算

聚合函式不準確

對於NULL值的列,使用聚合函式的時候會忽略NULL值。

現在我們有一張表,name欄位預設是NULL,此時對name進行count得出的結果是1,這個是錯誤的。

count(*)是對錶中的行數進行統計,count(name)則是對錶中非NULL的列進行統計。

=失效

對於NULL值的列,是不能使用=表示式進行判斷的,下面對name的查詢是不成立的,必須使用is NULL

與其他值運算

NULL和其他任何值進行運算都是NULL,包括表示式的值也是NULL。

user表第二條記錄age是NULL,所以+1之後還是NULL,name是NULL,進行concat運算之後結果還是NULL。

可以再看下下面的例子,任何和NULL進行運算的話得出的結果都會是NULL,想象下你設計的某個欄位如果是NULL還不小心進行各種運算,最後得出的結果。。。

distinct、group by、order by

對於distinctgroup by來說,所有的NULL值都會被視為相等,對於order by來說升序NULL會排在最前

其他問題

表中只有一條有名字的記錄,此時查詢名字!=a預期的結果應該是想查出來剩餘的兩條記錄,會發現與預期結果不匹配。

索引問題

為了驗證NULL欄位對索引的影響,分別對nameage新增索引。

關於網上很多說如果NULL那麼不能使用索引的說法,這個描述其實並不準確,根據引用官方文件[3]裡描述,使用is NULL和範圍查詢都是可以和正常一樣使用索引的,實際驗證的結果好像也是這樣,看以下例子。

然後接著我們往資料庫中繼續插入一些資料進行測試,當NULL列值變多之後發現索引失效了。

我們知道,一個查詢SQL執行大概是這樣的流程:

首先聯結器負責連線到指定的資料庫上,接著看看查詢快取中是否有這條語句,如果有就直接返回結果。

如果快取沒有命中的話,就需要分析器來對SQL語句進行語法和詞法分析,判斷SQL語句是否合法。

現在來到優化器,就會選擇使用什麼索引比較合理,SQL語句具體怎麼執行的方案就確定下來了。

最後執行器負責執行語句、有無許可權進行查詢,返回執行結果。

從上面的簡單測試結果其實可以看到,索引列存在NULL就會存在書中所說的導致優化器在做索引選擇的時候更復雜,更加難以優化。

儲存空間

資料庫中的一行記錄在最終磁碟檔案中也是以行的方式來儲存的,對於InnoDB來說,有4種行儲存格式:REDUNDANTCOMPACTDYNAMICCOMPRESSED

InnoDB的預設行儲存格式是COMPACT,儲存格式如下所示,虛線部分代表可能不一定會存在。

變長欄位長度列表:有多個欄位則以逆序儲存,我們只有一個欄位所有不考慮那麼多,儲存格式是16進位制,如果沒有變長欄位就不需要這一部分了。

NULL值列表:用來儲存我們記錄中值為NULL的情況,如果存在多個NULL值那麼也是逆序儲存,並且必須是8bit的整數倍,如果不夠8bit,則高位補0。1代表是NULL,0代表不是NULL。如果都是NOT NULL那麼這個就存在了。

ROW_ID:一行記錄的唯一標誌,沒有指定主鍵的時候自動生成的ROW_ID作為主鍵。

TRX_ID:事務ID。

ROLL_PRT:回滾指標。

最後就是每列的值。

為了說明清楚這個儲存格式的問題,我弄張表來測試,這張表只有c1欄位是NOT NULL,其他都是可以為NULL的。

可變欄位長度列表c1c3欄位值長度分別為1和2,所以長度轉換為16進位制是0x01 0x02,逆序之後就是0x02 0x01

NULL值列表:因為存在允許為NULL的列,所以c2,c3,c4分別為010,逆序之後還是一樣,同時高位補0滿8位,結果是00000010

其他欄位我們暫時不管他,最後第一條記錄的結果就是,當然這裡我們就不考慮編碼之後的結果了。

這樣就是一個完整的資料行資料的格式,反之,如果我們把所有欄位都設定為NOT NULL,並且插入一條資料a,bb,ccc,dddd的話,儲存格式應該這樣:

雖然我們發現NULL本身並不會佔用儲存空間,但是如果存在NULL的話就會多佔用一個位元組的標誌位的空間。

文章參考文件:

  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

相關文章