MySQL中建表時可空（NULL）和非空（NOT NULL）的一些思考

對于MySQL的一些個規范，某些公司建表規范中有一項要求是所有欄位非空，意味著沒有值的時候存盤一個默認值，其實所有欄位非空這么說應該是絕對了，應該說是盡可能非空，某些情況下不可能給出一個默認值，
那么這條要求，是基于哪些考慮因素，存盤空間？相關增刪查改操作的性能？亦或是其他考慮？該理論到底有沒有道理或者可行性，本文就個人的理解，做一個粗淺的分析，

1，基于存盤的考慮

這里對存盤的分析要清楚MySQL資料行的存盤格式，這里直接從這篇文章白嫖一部分結論，文章里分析的非常清楚（其實也是參考《MySQL技術內容Innodb存盤引擎》），
對于默認的Dynamic或者Compact格式的資料行結構，其行結構格式如下：
|變長欄位長度串列（1~2位元組）|NULL標志位（1位元組）|記錄頭資訊（5位元組）|RowID（6位元組）|事務ID（6位元組）|回滾指標（7位元組）|row content

1，對于變長欄位，當相關的欄位值為NULL時，相關欄位不會占用存盤空間，NULL值沒有存盤，不占空間，但是需要一個標志位（一行一個），
2，對于變長欄位，相關欄位要求NOT NULL，存盤成''的時候，也不占用空間，如果一個表中所有的字典都NOT NULL，行頭不需要NULL的標志位
3，所有欄位都是定長，不管是否要求為NOT NULL，都不需要標志位，同時不需要存盤變長列長度

鑒于null值和非空（not null  default ''）兩種情況，如果一個欄位存盤的內容是空，也就是什么都沒有，前者存盤為null，后者存盤為空字串'',兩者欄位內容本身存盤空間大小是一樣的，
但是如果一個表中存盤在可空欄位的情況下，其對應的資料行的頭部，都需要一個1位元組的NULL標志位，這個就決定了存盤同樣的資料，如果允許為null，相比not null的情況下，每行多了一個位元組的存盤空間的，
這個因素或者就是某些公司或者個人堅持“所有表禁止null欄位”這個信仰的原因之一（個人持否定態度，可以嘗試將資料庫中所有的欄位都至為not null 然后default一個值后會不會雞飛狗跳），
這里不再去做“微觀”的分析，直接從“宏觀”的角度來看一下差異，

測驗demo
直接創建結構一致，但是一個表欄位not null，一個表欄位為null，然后使用存盤此程序，兩張表同時按照null值與非null值1:10的比例寫入資料，也就是說每10行資料中1行資料欄位為null的方式寫入600W行資料，

CREATE TABLE a
(
    id INT AUTO_INCREMENT,
    c2 VARCHAR(50) NOT NULL DEFAULT '',
    c3 VARCHAR(50) NOT NULL DEFAULT '',
    PRIMARY KEY (id)
);

CREATE TABLE b
(
    id INT AUTO_INCREMENT,
    c2 VARCHAR(50),
    c3 VARCHAR(50),
    PRIMARY KEY (id)
);


CREATE DEFINER=`root`@`%` PROCEDURE `create_test_data`(
    IN `loop_cnt` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE v2 , v3 VARCHAR(36);
    
    START TRANSACTION;
    
        while loop_cnt>0 do
            SET v2 = UUID();
            SET v3 = UUID();

            
            if (loop_cnt MOD 10) = 0 then
                INSERT INTO a (c2,c3) VALUES(DEFAULT,DEFAULT);
                INSERT INTO b (c2,c3) VALUES(DEFAULT,DEFAULT);
            else
                INSERT INTO a (c2,c3) VALUES (v2,v3);
                INSERT INTO b (c2,c3) VALUES (v2,v3);
            END if ;
            
            SET loop_cnt=loop_cnt-1;
        END while;
    COMMIT;
    

a,b兩張表生產完全一致的資料，

查看占用的存盤空間情況，從information_schema.TABLES中查詢這兩個表的存盤資訊

1，一個位元組的差別，體現在avg_row_length,a表因為所有的欄位都是not null，因此相比b表，每行節省了每行節省了一個位元組的存盤
2，總得空間的差別：a表662683648/1024/1024=631.98437500MB，b表666877952/1024/1024=635.98437500MB，
　 也當前情況下，600W行資料有4MB的差異，差異在1%之內，其實實際情況下，欄位多，table size更大的的時候，這個差異會遠遠小于1%，

就存盤空間來說，你跟我說1T的資料庫你在乎1GB的存盤空間，隨便一點資料/索引碎片空間，一點預留空間，垃圾檔案空間，無用索引空間……，都遠遠大于可為空帶來的額外這一點差異，

2，增刪查改的效率

讀寫操作對比，通過連續讀寫一個范圍之內的資料，來對比a，b兩張表在讀上面的情況，
　　2.1.）首先buffer pool是遠大于table size的，因此不用擔心物理IO引起的差異，目前兩張表的資料完全都存在與buffer pool中，
　　2.1.）讀測驗操作放在MySQL實體機器上，因此網路不穩定引起的差異可以忽略，

增刪查改的差異與存盤空間的差異類似，甚至更小，因為單行相差1個位元組，放大到600W+才能看到一個5MB級別的差異，增刪查改的話，各種測驗下來，沒有發現有明顯的差異

#!/usr/bin/env python3
import pymysql
import time
mysql_conn_conf = {'host': '127.0.0.1', 'port': 3306, 'user': 'root', 'password': '******', 'db': 'db01'}


def mysql_read(table_name):
    conn = pymysql.connect(host=mysql_conn_conf['host'], port=mysql_conn_conf['port'], database=mysql_conn_conf['db'],user=mysql_conn_conf['user'],password = mysql_conn_conf['password'])
    cursor = conn.cursor()
    try:
        cursor.execute(''' select id,c2,c3 from {0} where id>3888888 and id<3889999;'''.format(table_name))
        row = cursor.fetchall()
    except pymysql.Error as e:
        print("mysql execute error:", e)
    cursor.close()
    conn.close()


def mysql_write(loop,table_name):
    conn = pymysql.connect(host=mysql_conn_conf['host'], port=mysql_conn_conf['port'], database=mysql_conn_conf['db'],user=mysql_conn_conf['user'],password = mysql_conn_conf['password'])
    cursor = conn.cursor()
    try:
        if loop%10 == 0:
            cursor.execute(''' insert into {0}} (c2,c3) values(DEFAULT,DEFAULT)'''.format(table_name))
        else:
            cursor.execute(''' insert into {1}} (c2,c3) values(uuid(),uuid())'''.format(table_name))
    except pymysql.Error as e:
        print("mysql execute error:", e)
    cursor.close()
    conn.commit()
    conn.close()


if __name__ == '__main__':
    time_start = time.time()
    loop=10
    while loop>0:
        mysql_write(loop)
        loop = loop-1

    time_end = time.time()
    time_c= time_end - time_start
    print('time cost', time_c, 's')

3，相關欄位上的語意決議和邏輯考慮

這一點就觀點差異就太多了，也是最容易引起口水或者爭議的了，

1，對于字符型別，NULL就是不存在，‘’就是空，不存在和空本身就不是一回事，不太認同一定要NOT NULL，然后給出默認值，
2，對于字符型別，任何資料庫中，NULL都是不等于NULL的，因為在處理相關欄位上進行join或者where篩選的時候，是不需要考慮連接雙方都為NULL的情況的，一旦用''替代了NULL，''是等于''的，此時就會出現與存盤NULL完全不用的語意
3，對于字符型別，一旦將相關欄位default成''，如何區分''與空字串，比如備注欄位，不允許為NULL，default成‘’，那么怎么區分，NULL表達的空和默認值的空字串''
4，對于相關的查詢操作，如果允許為NULL，篩選非NULL值就是where *** is not null，語意上很清晰直觀，一旦用欄位非空，默認成''，會使用where *** <>''這種看起來超級惡心的寫法，究竟要表達什么，語意上就已經開始模糊了
5，對于時間型別，絕大多數時候是不允許有默認值的，默認多少合適，當前時間合適么，千禧年2000合適么，2008年北京奧運會開幕時間合適么？
6，對于數值型別，比如int，比如decimal，在可空的情況下，如果禁止為NULL，默認給多少合適，0合適嗎？-1合適嗎？-9999999……合適嗎？10086合適嗎？1024合適嗎？說實話，默認多少都不合適，NULL自身就是最合適的，

個人觀點很明確，除非有特殊的需求要求一個欄位絕對不能出現NULL值的情況，正常情況下，該NULL就NULL，
如果NULL沒有存在的意義，干脆資料庫就不要存在這個NULL就好了，事實上，哪個資料庫沒有NULL型別？
當然也不排除，某些DBA為了顯得自己專業，弄出來一些莫須有的東西，現在就是有一種風氣，在資料庫上能提出來的限制條件越多，越有優越感，

想起來一個有關于默認值有意思的事，B站看視頻的時候某up主曾提到過，因為B站把注冊用戶默認為男，出生日期某認為某個指定的日期，導致該up主在對用戶點為分析后得到一些無法理解的資料，

個人認識有限，資料實話，非常想知道“所有欄位非空”會帶來什么其他哪些正面的影響，以及如何衡量這個正面的因素，還有，你們真的做到了，可以禁止整個實體下所有的庫表中的欄位禁止可空（nullable）？

標籤：MySQL

上一篇：急急急，求sql大神

下一篇：源代碼錯了