MySQL欄位究竟是否需要設定成not null

一些錯誤認知的更正

之前以為record header中的null的點陣圖資訊是這個欄位是nullable，那麼對應的位上就會設定成1，在extern中儲存的是可變長度欄位的設定的定義的大小，其實上面2個都不對，透過dump 塊的資訊，可以看到，在null的點陣圖上，只有對應的欄位值是null，才會設定成1，如果欄位定義的是 nullable但實際的記錄中有值，那麼是有空間佔用的，但是對應的位上是0. 

extern中的資訊是可變欄位的實際儲存的最大長度.我的測試中是6個字元。

null bitmap的作用應該是在儲存的時候，看對應的欄位是否有值，沒有值那麼儲存就會忽略這個欄位，不佔用空間。

extern保留實際最大值的長度。

dump資訊見下方的nulls，為了方便，將null和 externs放在了一起，第一個位元組是可變欄位長度，第二個位元組是null欄位的bitmap

測試的表記錄如下

master [localhost] {msandbox} (test) > show create table dba_user5\G
*************************** 1. row ***************************
       Table: dba_user5
Create Table: CREATE TABLE `dba_user5` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) DEFAULT NULL COMMENT '使用者名稱',
  `class` varchar(100) DEFAULT NULL COMMENT 'class',
  `account` int(11) DEFAULT NULL,
  `version` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
1 row in set (0.00 sec)
master [localhost] {msandbox} (test) > select * from dba_user5;
+----+----------+-------+---------+---------+
| id | username | class | account | version |
+----+----------+-------+---------+---------+
|  1 | cccccc   | ll    |    NULL |      30 |
+----+----------+-------+---------+---------+
1 row in set (0.00 sec)

record header: {
  "offset": 128,
  "length": 5,
  "next": 112,
  "prev": 0,
  "type": "conventional",
  "heap_number": 2,
  "n_owned": 0,
  "info_flags": 0,
  "offset_size": 0,
  "n_fields": 0,
  "nulls": "0000011000000100",
  "lengths": null,
  "externs": ""

下面開始正式測試

很多文章或開發規範都說，欄位要設定成not null，很主要的原因是說省空間。真的如此嗎？record header的資訊我們已經清楚，下面看下record的內容。

首先看下 nullable的儲存情況

master [localhost] {msandbox} (test) > show create table dba_user5\G
*************************** 1. row ***************************
       Table: dba_user5
Create Table: CREATE TABLE `dba_user5` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) DEFAULT NULL COMMENT '使用者名稱',
  `class` varchar(100) DEFAULT NULL COMMENT 'class',
  `account` int(11) DEFAULT NULL,
  `version` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
1 row in set (0.00 sec)
master [localhost] {msandbox} (test) > select * from dba_user5;
+----+----------+-------+---------+---------+
| id | username | class | account | version |
+----+----------+-------+---------+---------+
| -5 | cccccc   | ll    |    NULL |      30 |
+----+----------+-------+---------+---------+
1 row in set (0.00 sec)
解析看下
record header: {
  "offset": 165,
  "length": 5,
  "next": 112,
  "prev": 0,
  "type": "conventional",
  "heap_number": 3,
  "n_owned": 0,
  "info_flags": 0,
  "offset_size": 0,
  "n_fields": 0,
  "nulls": "0000011000000100",
  "lengths": null,
  "externs": ""
}
offset  165
cluster key fileds == -5
transaction_id == 1732686
roll pointer == 55450570435854608
 value1== cccccc
 value2== ll
 value3== 30
 value4== 0

我們可以看到在允許為空的欄位中，nulls有點陣圖資訊，並且null欄位沒有實際的儲存。

下面看下not null的情況

master [localhost] {msandbox} (test) > show create table dba_user7\G
*************************** 1. row ***************************
       Table: dba_user7
Create Table: CREATE TABLE `dba_user7` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL DEFAULT '' COMMENT '使用者名稱',
  `class` varchar(100) NOT NULL DEFAULT 'c' COMMENT 'class',
  `account` int(11) NOT NULL DEFAULT '0',
  `version` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
1 row in set (0.00 sec)
master [localhost] {msandbox} (test) > select * from dba_user7;
+----+----------+-------+---------+---------+
| id | username | class | account | version |
+----+----------+-------+---------+---------+
|  1 |          | ll    |       0 |      30 |
+----+----------+-------+---------+---------+
1 row in set (0.00 sec)
看下解析結果，
record header: {
  "offset": 127,
  "length": 5,
  "next": 112,
  "prev": 0,
  "type": "conventional",
  "heap_number": 2,
  "n_owned": 0,
  "info_flags": 0,
  "offset_size": 0,
  "n_fields": 0,
  "nulls": "0000001000000000",
  "lengths": null,
  "externs": ""
}
offset  127
cluster key fileds == 1
transaction_id == 1732701
roll pointer == 58828270628765968
 value1== ll
 value2== 0
 value3== 30
 value4== 0

先看下記錄，由於username我們使用了預設值，空字元，實際上mysql並沒有儲存，account使用了預設的0還是進行了儲存。

並且在我的結果中，即使所有的欄位都是not null,在null的bitmap上依然分配了一個位元組，這個位元組並沒有取消。

總結：

1 如果字元欄位not null，並且預設值是空字元，那麼實際儲存這個欄位不佔用空間，如果預設字元非空，那麼還是會佔用儲存空間，一個字元一個位元組。

如果欄位是數值型的，那麼使用預設值，也是佔用空間。

2 如果欄位允許為空，並且欄位中的值是null,那麼這個欄位實際不佔用儲存空間

3不管有沒有為空的欄位，mysql中都會至少有一個位元組的null bitmap，所以欄位小於9的，bitmap儲存空間上都一樣，空間上不會有節省。

所以，我們可以看到除非所有的欄位都是設定成not null，並且字元欄位預設值都是空字元，並且實際插入會用這個預設的空字元，才會省下一點空間，但實際的使用上，not null的欄位一般都會插入具體的值。因此物理記錄儲存上not null沒有節省很多空間

下面看下header，如果空欄位數量超過9會使用2個位元組儲存null bitmap，以此類推。這麼看在欄位非常多的情況下，將欄位設定成not null,會節省出來1，2個位元組。

如果有32個欄位是not null，那麼可以省下4-1=3個位元組，1000w記錄的一個表，能省下30M空間，這點空間基本可以忽略了

從上面的測試來看，欄位設定not null 節省空間的結論，表欄位少，數量少情況下，基本可以忽略。表欄位多，數量多，節省的空間還比較可觀，另外關於null影響統計資訊收集，執行計劃的分析，暫時沒有遇到具體案例，可能之前遇到過，當時沒有仔細深入分析。有案例的同學可以提供下。

測試版本5.7

有興趣學習原始碼的加群一起學習啊 QQ：                                                                                                                                700072075