mysql 字段定義不要用null的原因分析

一 NULL 為什么這么經(jīng)常用

(1) java的null

null是一個讓人頭疼的問題，比如java中的NullPointerException。為了避免猝不及防的空指針，需要小心翼翼地各種if判斷，麻煩又臃腫.

為此有很多的開源包都有諸多處理

common lang3的StringUtils.isBlank(); CollectionUtils.isEmpty();

guava的Optional

甚至java8也引入了Optional來避免這一問題（和guava的大同小異，用法稍有一點點變化)

(2) mysql的null為什么橫行濫用

(a) 創(chuàng)建不規(guī)范 null是創(chuàng)建數(shù)據(jù)表時候默認的，一些mysql客戶端的自動生成表語句里面可能也沒有not null的指定。

(b) 錯誤認識 會有人覺得not null需要更多的空間

(c) 圖省事 null在開發(fā)中不用判斷插入數(shù)據(jù)，寫sql更方便

二 官方文檔

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ù)雜?？煽樟行枰嗟拇鎯臻g，還需要mysql內(nèi)部進行特殊處理?？煽樟斜凰饕?，每條記錄都需要一個額外的字節(jié)，還能導(dǎo)致MYisam 中固定大小的索引變成可變大小的索引。 —— 出自《高性能mysql第二版》

如此看來，不指定not null并沒有性能上的優(yōu)勢。

三 mysql不用null的理由

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

（2）NULL值到非NULL的更新無法做到原地更新，更容易發(fā)生索引分裂，從而影響性能。(null -> not null性能提升很小，除非確定它帶來了問題，否則不要當(dāng)成優(yōu)先的優(yōu)化措施)

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

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

四 null引發(fā)的bad case

數(shù)據(jù)初始化:

create table table1 (
    `id` INT (11) NOT NULL,
    `name` varchar(20) NOT NULL
)


create table table2 (
    `id` INT (11) NOT NULL,
    `name`  varchar(20)
)

insert into table1 values (4,"zhaoyun"),(2,"zhangfei"),(3,"liubei")
insert into table2 values (1,"zhaoyun"),(2, null)

(1)NOT IN子查詢在有NULL值的情況下返回永遠為空結(jié)果，查詢?nèi)菀壮鲥e

select name from table1 where name not in (select name from table2 where id!=1)

+-------------+
|      name   |
|-------------|
+-------------+

(2) 列值允許為空，索引不存儲null值，結(jié)果集中不會包含這些記錄。

select * from table2 where name != 'zhaoyun'

+------+-------------+
|   id |      name   |
|------+-------------|
|      |             |
+------+-------------+

select * from table2 where name != 'zhaoyun1'

+------+-------------+
|   id |      name   |
|------+-------------|
|   1  |  zhaoyun    |
+------+-------------+

(3) 使用concat拼接時，首先要對各個字段進行非null判斷，否則只要任何一個字段為空都會造成拼接的結(jié)果為null

select concat("1", null) from dual;

+--------------------+
|   concat("1", null)|
|--------------------|
|               NULL |
+--------------------+

(4) 當(dāng)計算count時候null column不會計入統(tǒng)計

select count(name) from table2;

+--------------------+
|   count(user_name) |
|--------------------|
|                  1 |
+--------------------+

五 索引長度對比

alter table table1 add index idx_name (name);
alter table table2 add index idx_name (name);
explain select * from table1 where name='zhaoyun';
explain select * from table2 where name='zhaoyun';

table1的key_len = 82

table2的key_len = 83

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

key_len 82 = 20 * 4(utf8mb4 - 4字節(jié), utf8 - 3字節(jié)) + 2(存儲varchar變長字符長度為2字節(jié)，定長字段無需額外的字節(jié))

key_len 83 = 20 * 4(utf8mb4 - 4字節(jié), utf8 - 3字節(jié)) + 2(存儲varchar變長字符長度為2字節(jié)，定長字段無需額外的字節(jié)) + 1(是否為null的標(biāo)志)

所以說索引字段最好不要為NULL，因為NULL會使索引、索引統(tǒng)計和值更加復(fù)雜，并且需要額外一個字節(jié)的存儲空間。

到此這篇關(guān)于mysql 字段定義不要用null的分析的文章就介紹到這了,更多相關(guān)mysql 字段定義null內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！