作為開發(fā)人員，我們經(jīng)常需要設(shè)計數(shù)據(jù)庫表，這個時候我們需要考慮使用字段使用哪種數(shù)據(jù)類型，以及默認(rèn)值，字符集等等一些問題，我們今天就來探討下字段為啥盡量設(shè)置為NOT NULL。

簡介

如果一個字段設(shè)置為NOT NULL ,表明我們在寫數(shù)據(jù)時，在沒有默認(rèn)值的情況下，不能寫入一個空值 例如：

create table friends (
id int(3) not null,
name varchar(8) not null,
pass varchar(20) not null
);
 
INSERT INTO friends
VALUES (
NULL , 'simaopig', 'simaopig'
);

我們看下插入數(shù)據(jù)后的結(jié)果：

圖一.png

我們發(fā)現(xiàn)報了一個錯誤：Column 'id' cannot be null，id不能為null。 當(dāng)然，在列為auto_increment 或者timestamp時不會報錯，我們來看下：

create table t2 (
id int(3) not null  auto_increment,
days timestamp not null,
primary key(`id`)
);
insert into t2 values (NULL,NULL);

看下插入后的結(jié)果：

圖二.png

發(fā)現(xiàn)可以正確插入，自增列會根據(jù)當(dāng)前記錄數(shù)插入對應(yīng)的值，而timestamp在沒有設(shè)置值的情況下會插入當(dāng)前時間。
上面我們簡單介紹了下關(guān)于not null ,相信大家對它也有一定的認(rèn)識，接下來說下優(yōu)化的問題。

字段優(yōu)化

前面有說到過我們的字段盡量設(shè)置為NOT NULL,那針對這樣做的原因，本人總結(jié)了以下幾點。

1. 我們的應(yīng)用程序一般是不需要使用NULL值的。
2. 如果查詢中包含可為NULL的列，對MySQL來說更難優(yōu)化，因為可為NULL的列會使索引、索引統(tǒng)計和值比較都更復(fù)雜。可為NULL的列會使用更多的存儲空間，在MySQL里也需要做特殊處理。當(dāng)可為NULL的列被索引時，每個索引記錄需要一個額外的字節(jié)，在MyISAM里面甚至可能導(dǎo)致固定大小的索引（例如只有一個整數(shù)列的索引）變成可變大小的索引。
3. 通常把可為NULL的列改為NOT NULL 帶來的性能提升比較小，所以調(diào)優(yōu)的時候沒有必要再現(xiàn)有的schema中查找并修改掉這種情況，除非確定這會導(dǎo)致問題，但是如果計劃在列上建索引，就應(yīng)該盡量避免設(shè)計成可為NULL的列。
4. InnoDb 使用單獨的位(bit)存儲NULL值，所以對于稀疏數(shù)據(jù)（很多值為NULL,只有少數(shù)行的列有非NULL值）有很好的空間效率。但這一點不適用于MyISAM.
   對于上面提到的幾點，小伙伴們可能不是很理解，下面來做幾個測試。

相關(guān)測試

1. 可為NULL的列對索引的影響
先創(chuàng)建兩張表
staffs表; //name 設(shè)置為不能為空

CREATE TABLE staffs (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR (24) NOT NULL DEFAULT '' COMMENT '姓名',
age INT NOT NULL DEFAULT 0 COMMENT '年齡',
pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '職位',
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時間',
KEY `idx_staffs_nameAgePos` (`name`)
) CHARSET utf8 COMMENT '員工記錄表' ; 

staffs1表；// name 默認(rèn)為空

CREATE TABLE staffs1 (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR (24)  COMMENT '姓名',
age INT NOT NULL DEFAULT 0 COMMENT '年齡',
pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '職位',
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時間',
KEY `idx_staffs_nameAgePos` (`name`)
) CHARSET utf8 COMMENT '員工記錄表1' ; 

分別插入兩條數(shù)據(jù)

insert into staffs values (1,'z3',22,'manager',NOW()),(2,'z4',23,'woker1',NOW());
insert into staffs1 values (1,'z3',22,'manager',NOW()),(2,'z4',23,'woker1',NOW());

那目前表里面是這個樣子的：

圖三.png

下面我們來看下這條語句的執(zhí)行計劃：

explain select * from staffs where name = 'z4';
explain select * from staffs1 where name = 'z4';

圖4.png

上面這個圖只截取了需要展示的部分，我們發(fā)現(xiàn)兩個執(zhí)行計劃只有key_len(索引的長度)。也就是我們上面所說的：可為NULL的列被索引時會使用更多的存儲空間。

2. 對統(tǒng)計的影響
先插入一條數(shù)據(jù)

insert into staffs1 values (3,NULL,24,'woker2',NOW());

圖5.png

我們來測試幾個操作:

count(*);
count(name);
count(distinct(name));

看下執(zhí)行結(jié)果：

圖6.png

我們發(fā)現(xiàn)當(dāng)統(tǒng)計的時候count(name)和count(distinct(name))會省略值為NULL的行。

總結(jié)：
當(dāng)我們的字段是經(jīng)常需要使用的字段，那我們盡量設(shè)置為NOT NULL，因為當(dāng)這個字段可為空的時候，并且也插入了一些空值的時候，會對程序效率和正確性產(chǎn)生一定的負(fù)面影響。

這里就簡單展示幾個例子，如有補充的，歡迎在下面給我留言。