講真!CTO:MySQL 千萬不要再用 null 了!

港真，Null 貌似在哪里都是個頭疼的問題，比如 Java 里讓人頭疼的 NullPointerException，為了避免猝不及防的空指針異常，千百年來程序猿們不得不在代碼里小心翼翼的各種 if 判斷，麻煩而又臃腫，為此 java8 引入了 Optional 來避免這一問題。

下面咱們要聊的是 MySQL 里的 null，在大量的 MySQL 優(yōu)化文章和書籍里都提到了字段盡可能用NOT NULL，而不是NULL，除非特殊情況。但卻都只給結(jié)論不說明原因，猶如雞湯不給勺子一樣，讓不少初學(xué)者對這個結(jié)論半信半疑或者云里霧里。本文今天就詳細(xì)的剖析下使用 Null 的原因，并給出一些不用 Null 的理由。

1、NULL 為什么這么多人用？

NULL是創(chuàng)建數(shù)據(jù)表時默認(rèn)的，初級或不知情的或怕麻煩的程序員不會注意這點(diǎn)。

很多人員都以為not null 需要更多空間，其實這不是重點(diǎn)。

重點(diǎn)是很多程序員覺得NULL在開發(fā)中不用去判斷插入數(shù)據(jù)，寫sql語句的時候更方便快捷。

2、是不是以訛傳訛？

MySQL 官網(wǎng)文檔：

?

NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.

?

?

Mysql難以優(yōu)化引用可空列查詢，它會使索引、索引統(tǒng)計和值更加復(fù)雜。可空列需要更多的存儲空間，還需要mysql內(nèi)部進(jìn)行特殊處理。可空列被索引后，每條記錄都需要一個額外的字節(jié)，還能導(dǎo)致MYisam 中固定大小的索引變成可變大小的索引。

—— 出自《高性能mysql第二版》

?

照此分析，還真不是以訛傳訛，這是有理論依據(jù)和出處的。

3、給我一個不用 Null 的理由？

1、所有使用NULL值的情況，都可以通過一個有意義的值的表示，這樣有利于代碼的可讀性和可維護(hù)性，并能從約束上增強(qiáng)業(yè)務(wù)數(shù)據(jù)的規(guī)范性。

2、NULL值到非NULL的更新無法做到原地更新，更容易發(fā)生索引分裂，從而影響性能。

?

注意：但把NULL列改為NOT NULL帶來的性能提示很小，除非確定它帶來了問題，否則不要把它當(dāng)成優(yōu)先的優(yōu)化措施，最重要的是使用的列的類型的適當(dāng)性。

?

3、NULL值在timestamp類型下容易出問題，特別是沒有啟用參數(shù)explicit_defaults_for_timestamp

4、NOT IN、!= 等負(fù)向條件查詢在有 NULL 值的情況下返回永遠(yuǎn)為空結(jié)果，查詢?nèi)菀壮鲥e

舉例：

      
      create table table_2 (
     `id` INT (11) NOT NULL,
    user_name varchar(20) NOT NULL
)


create table table_3 (
     `id` INT (11) NOT NULL,
    user_name varchar(20)
)

insert into table_2 values (4,"zhaoliu_2_1"),(2,"lisi_2_1"),(3,"wangmazi_2_1"),(1,"zhangsan_2"),(2,"lisi_2_2"),(4,"zhaoliu_2_2"),(3,"wangmazi_2_2")

insert into table_3 values (1,"zhaoliu_2_1"),(2, null)

-- 1、NOT IN子查詢在有NULL值的情況下返回永遠(yuǎn)為空結(jié)果，查詢?nèi)菀壮鲥e
select user_name from table_2 where user_name not in (select user_name from table_3 where id!=1)

mysql root@10.48.186.32:t_test_zz5431> select user_name from table_2 where user_name not
                                    -> in (select user_name from table_3 where id!=1);
+-------------+
| user_name   |
|-------------|
+-------------+
0 rows in set
Time: 0.008s
mysql root@10.48.186.32:t_test_zz5431>

-- 2、單列索引不存null值，復(fù)合索引不存全為null的值，如果列允許為null，可能會得到“不符合預(yù)期”的結(jié)果集
-- 如果name允許為null，索引不存儲null值，結(jié)果集中不會包含這些記錄。所以，請使用not null約束以及默認(rèn)值。
select * from table_3 where name != 'zhaoliu_2_1'

-- 3、如果在兩個字段進(jìn)行拼接：比如題號+分?jǐn)?shù)，首先要各字段進(jìn)行非null判斷，否則只要任意一個字段為空都會造成拼接的結(jié)果為null。
select CONCAT("1",null) from dual; -- 執(zhí)行結(jié)果為null。

-- 4、如果有 Null column 存在的情況下，count(Null column)需要格外注意，null 值不會參與統(tǒng)計。
mysql [email protected]:t_test_zz5431> select * from table_3;
+------+-------------+
|   id | user_name   |
|------+-------------|
|    1 | zhaoliu_2_1 |
|    2 | <null>      |
|   21 | zhaoliu_2_1 |
|   22 | <null>      |
+------+-------------+
4 rows in set
Time: 0.007s
mysql root@10.48.186.32:t_test_zz5431> select count(user_name) from table_3;
+--------------------+
|   count(user_name) |
|--------------------|
|                  2 |
+--------------------+
1 row in set
Time: 0.007s

-- 5、注意 Null 字段的判斷方式， = null 將會得到錯誤的結(jié)果。
mysql root@localhost:cygwin> create index IDX_test on table_3 (user_name);
Query OK, 0 rows affected
Time: 0.040s
mysql root@localhost:cygwin>  select * from table_3 where user_name is null\G
***************************[ 1. row ]***************************
id        | 2
user_name | None

1 row in set
Time: 0.002s
mysql root@localhost:cygwin> select * from table_3 where user_name = null\G

0 rows in set
Time: 0.002s
mysql root@localhost:cygwin> desc select * from table_3 where user_name = 'zhaoliu_2_1'\G
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | table_3
type          | ref
possible_keys | IDX_test
key           | IDX_test
key_len       | 23
ref           | const
rows          | 1
Extra         | Using where

1 row in set
Time: 0.006s
mysql root@localhost:cygwin> desc select * from table_3 where user_name = null\G
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | None
type          | None
possible_keys | None
key           | None
key_len       | None
ref           | None
rows          | None
Extra         | Impossible WHERE noticed after reading const tables

1 row in set
Time: 0.002s
mysql root@localhost:cygwin> desc select * from table_3 where user_name is null\G
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | table_3
type          | ref
possible_keys | IDX_test
key           | IDX_test
key_len       | 23
ref           | const
rows          | 1
Extra         | Using where

1 row in set
Time: 0.002s
mysql root@localhost:cygwin>

    

5、Null 列需要更多的存儲空間：需要一個額外字節(jié)作為判斷是否為 NULL 的標(biāo)志位

舉例：

      
      alter table table_3 add index idx_user_name (user_name);
alter table table_2 add index idx_user_name (user_name);
explain select * from table_2 where user_name='zhaoliu_2_1';
explain select * from table_3 where user_name='zhaoliu_2_1';

    

可以看到同樣的 varchar(20) 長度，table_2 要比 table_3 索引長度大，這是因為：

兩張表的字符集不一樣，且字段一個為 NULL 一個非 NULL。

key_len 的計算規(guī)則和三個因素有關(guān)：數(shù)據(jù)類型、字符編碼、是否為 NULL

• key_len 62 == 20*3（utf8 3字節(jié)） + 2 （存儲 varchar 變長字符長度 2字節(jié)，定長字段無需額外的字節(jié)）

• key_len 83 == 20*4（utf8mb4 4字節(jié)） + 1 (是否為 Null 的標(biāo)識) + 2 （存儲 varchar 變長字符長度 2字節(jié)，定長字段無需額外的字節(jié)）

所以說索引字段最好不要為NULL，因為NULL會使索引、索引統(tǒng)計和值更加復(fù)雜，并且需要額外一個字節(jié)的存儲空間。基于以上這些理由和原因，我想咱們不用 Null 的理由應(yīng)該是夠了。

覺得本文對你有幫助？請分享給更多人

關(guān)注「 全棧開發(fā)者社區(qū) 」加星標(biāo)，提升全棧技能

本公眾號會不定期給大家發(fā)福利，包括送書、學(xué)習(xí)資源等，敬請期待吧！

如果感覺推送內(nèi)容不錯，不妨右下角點(diǎn)個 在看 轉(zhuǎn)發(fā)朋友圈或收藏，感謝支持。

好文章，留言、點(diǎn)贊、 在看和分享一條龍