數(shù)據(jù)庫允許空值(null)，往往是悲劇的開始（1分鐘系列）
很多小知識點，我以為自己懂了，實際沒搞透。
數(shù)據(jù)庫字段允許空值(null)的問題，你遇到過嗎？
實驗過程：
create table user (
id int,
name varchar(20),
index(id)
)engine=innodb;
【說明：id為索引，非唯一(non unique)，允許空(null)】
insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
【問題一：以下select返回什么？】
select * from user where id!=1;
【插入一行，id會出現(xiàn)空值(null)】
insert into user(name) values('wangwu');
【問題二：再次select，會返回什么？】
select * from user where id!=1;
一起來看一下，這個小實驗，涉及哪些知識點呢？
知識點1（熱身）：負向查詢不能命中索引，會導(dǎo)致全表掃描。
explain select * from user where id!=1;
索引字段id上的不等于查詢，如上圖所示：
（1）type=ALL，全表掃描；
（2）rows=3，全表只有3行；
畫外音：第一次select的結(jié)果。
知識點2（劃重點）：允許空值，不等于(!=)查詢，可能導(dǎo)致不符合預(yù)期的結(jié)果。
insert into user(name) values('wangwu');
先構(gòu)造一條id為NULL的數(shù)據(jù)，可以看到共有4條記錄。
select * from user where id!=1;
再次執(zhí)行不等于查詢。
你猜結(jié)果集有幾條記錄（共4條，不等于排除1條）？
答錯了！
結(jié)果集只有2條記錄，空值記錄并未出現(xiàn)在結(jié)果集里。
畫外音：第二次select的結(jié)果，意不意外？
此時，如果想到得到符合預(yù)期的結(jié)果集，必須加上一個or條件。
select * from user where id!=1 or id is null;
畫外音：惡心不惡心，這個大坑你踩過沒有？
知識點3（附加）：某些or條件，又可能導(dǎo)致全表掃描，此時應(yīng)該優(yōu)化為union。
explain select * from user where id=1;
索引字段id上的等值查詢，能命中索引，如上圖所示：
（1）type=ref，走非唯一索引；
（2）rows=1，預(yù)估掃描1行；
explain select * from user where id is null;
索引字段id上的null查詢，也能命中索引，如上圖所示：
（1）type=ref，走非唯一索引；
（2）rows=1，預(yù)估掃描1行；
explain select * from user where id=1 or id is null;
如果放到一個SQL語句里用or查詢，則會全表掃描，如上圖所示：
（1）type=ALL，全表掃描；
（2）rows=4，全表只有4行；
explain select * from user where id=1
union
select * from user where id is null;
此時應(yīng)該優(yōu)化為union查詢，又能夠命中索引了，如上圖所示：
（1）type=ref，走非唯一索引；
（2）rows=1，預(yù)估掃描1行；
畫外音：第三行臨時表的ALL，是兩次結(jié)果集的合并。
總結(jié)
（1）負向比較（例如：!=）會引發(fā)全表掃描；
（2）如果允許空值，不等于(!=)的查詢，不會將空值行(row)包含進來，此時的結(jié)果集往往是不符合預(yù)期的，此時往往要加上一個or條件，把空值(is null)結(jié)果包含進來；
（3）or可能會導(dǎo)致全表掃描，此時可以優(yōu)化為union查詢；
（4）建表時加上默認(default)值，這樣能避免空值的坑；
（5）explain工具是一個好東西；
希望大家有收獲！
畫外音：本文測試于MySQL5.6。
架構(gòu)師之路-分享技術(shù)思路