MySQL でテーブルを作成するときの NULL と NOT NULL の使用方法の詳細な説明

MySQL の仕様によっては、テーブル作成仕様にすべてのフィールドが空であってはならないという要件を設けている企業もあります。つまり、値がない場合にはデフォルト値が格納されます。実際のところ、すべてのフィールドは絶対に空ではないと言えます。可能な限り空ではないと言えるでしょう。場合によっては、デフォルト値を与えることが不可能です。
では、この要件、つまりストレージスペースに関する考慮事項は何でしょうか?関連する追加、削除、クエリ、変更操作のパフォーマンスはどうですか?それとも他に考慮すべき点があるのでしょうか?この記事では、私の個人的な理解に基づいて、この理論が合理的か実現可能かについて大まかに分析します。

1. ストレージベースの考慮事項

1. 可変長フィールドの場合、関連フィールドの値が NULL の場合、関連フィールドはストレージスペースを占有しません。 NULL 値は保存されず、スペースを占有しませんが、フラグビット (行ごとに 1 つ) が必要です。
2. 可変長フィールドの場合、関連するフィールドには NOT NULL が必要です。'' として保存すると、スペースを占有しません。テーブル内のすべての辞書が NOT NULL の場合、行ヘッダーに NULL フラグは必要ありません。
3. すべてのフィールドは固定長です。NOT NULL である必要があるかどうかに関係なく、フラグは必要ありません。同時に、可変長列の長さを格納する必要はありません。

NULL 値と空でない (NULL でないデフォルト '') の 2 つのケースを考慮すると、フィールドに格納されている内容が空、つまり何もない場合、前者は null として格納され、後者は空文字列 '' として格納されます。フィールド内容自体の格納領域サイズは同じです。
ただし、テーブルに NULL 可能なフィールドが格納されている場合、対応するデータ行のヘッダーには 1 バイトの NULL フラグが必要です。これにより、同じデータを格納するときに、NULL が許可されている場合は、各行に NULL でない場合よりも 1 バイト多いストレージスペースが割り当てられます。
この要因は、一部の企業や個人が「すべてのテーブルで null フィールドは禁止されている」という信念を主張する理由の 1 つである可能性があります (私は個人的に否定的な考えを持っていますが、データベース内のすべてのフィールドを null 以外に設定してから値をデフォルトに設定して、混乱が生じるかどうかを確認できます)。
ここでは「ミクロ」分析は行いませんが、「マクロ」の観点から直接違いを見ていきます。

テストデモ

同じ構造のテーブルを直接作成しますが、1 つのテーブルフィールドは null ではなく、もう 1 つのテーブルフィールドは null です。次に、このストアドプロシージャを使用して、null 値と null 以外の値の比率が 1:10 になるように、2 つのテーブルに同時にデータを書き込みます。つまり、10 行のデータごとに 1 行のデータフィールドが null である状態で、600 万行のデータが書き込まれます。

テーブルを作成
（
 id INT AUTO_INCREMENT、
 c2 VARCHAR(50) NOT NULL デフォルト ''
 c3 VARCHAR(50) NOT NULL デフォルト ''
 主キー (id)
);

テーブルbを作成
（
 id INT AUTO_INCREMENT、
 c2 可変長文字(50)
 c3 可変長文字(50)
 主キー (id)
);


CREATE DEFINER=`root`@`%` PROCEDURE `create_test_data`(
 `loop_cnt` 内の INT
）
言語 SQL
決定論的ではない
SQLを含む
SQL セキュリティ定義者
コメント ''
始める
 v2、v3をVARCHAR(36)で宣言します。
 
 トランザクションを開始します。
 
 loop_cnt>0の間、
 SET v2 = UUID();
 SET v3 = UUID();

 
 (loop_cnt MOD 10) = 0の場合
 (c2,c3) に VALUES(DEFAULT,DEFAULT) を挿入します。
 b (c2,c3) に値(DEFAULT,DEFAULT) を挿入します。
 それ以外
 (c2,c3) に値 (v2,v3) を挿入します。
 b (c2,c3) に値 (v2,v3) を挿入します。
 終了の場合;
 
 loop_cnt=loop_cnt-1 を設定します。
 END しながら;
 専念;

表 a と b はまったく同じデータを生成します。

占有されているストレージスペースを確認し、information_schema.TABLESから2つのテーブルのストレージ情報を照会します。

1. 1 バイトの違いは avg_row_length に反映されます。テーブル a のすべてのフィールドが null ではないため、テーブル b と比較して各行で 1 バイトのストレージが節約されます。
2. 総容量の差: 表aは662683648/1024/1024=631.98437500MB、表bは666877952/1024/1024=635.98437500MB、
今回のケースでは、600 万行のデータで 4MB の差があり、これは 1% 以内です。実際、実際の状況では、フィールドの数が多く、テーブルサイズが大きい場合、この差は 1% よりもはるかに小さくなります。

ストレージスペースに関して言えば、1T のデータベースに 1GB のストレージスペースが必要だとしたら、データ/インデックスの断片化スペース、少量の予約済みスペース、ジャンクファイルスペース、役に立たないインデックススペースなどは、NULL 可能性によってもたらされる追加の差よりもはるかに大きいです。

2. 追加、削除、確認、変更の効率

範囲内のデータを連続的に読み書きすることで、読み取り操作と書き込み操作を比較し、表 a と表 b の読み取り状態を比較します。
2.1.) まず、バッファプールはテーブルサイズよりもはるかに大きいため、物理 IO によって生じる差異を心配する必要はありません。現在、2 つのテーブルのデータはバッファプールに完全に保存されています。
2.1.) 読み取りテスト操作は MySQL インスタンスマシン上で実行されるため、ネットワークの不安定性によって生じる差異は無視できます。

追加、削除、確認、変更の違いは、ストレージ容量の違いと同程度か、それよりも小さくなります。1 行の差は 1 バイトで、5 MB の違いは 600 W 以上に拡大した場合にのみ確認できます。追加、削除、確認、変更については、さまざまなテストを行った後でも明らかな違いは見つかりませんでした。

#!/usr/bin/env python3
pymysqlをインポートする
インポート時間
mysql_conn_conf = {'ホスト': '127.0.0.1'、'ポート': 3306、'ユーザー': 'root'、'パスワード': '******'、'db': 'db01'}


def mysql_read(テーブル名):
 conn = pymysql.connect(ホスト=mysql_conn_conf['host']、ポート=mysql_conn_conf['port']、データベース=mysql_conn_conf['db']、ユーザー=mysql_conn_conf['user']、パスワード=mysql_conn_conf['password'])
 カーソル = conn.cursor()
 試す：
 cursor.execute(''' {0} から id、c2、c3 を選択します。id>3888888 かつ id<3889999 です。'''.format(table_name))
 行 = カーソル.fetchall()
 pymysql.Error を除き、e:
 print("mysql 実行エラー:", e)
 カーソルを閉じる()
 接続を閉じる()


def mysql_write(ループ、テーブル名):
 conn = pymysql.connect(ホスト=mysql_conn_conf['host']、ポート=mysql_conn_conf['port']、データベース=mysql_conn_conf['db']、ユーザー=mysql_conn_conf['user']、パスワード=mysql_conn_conf['password'])
 カーソル = conn.cursor()
 試す：
 ループ%10 == 0の場合:
 cursor.execute(''' insert into {0}} (c2,c3) values(DEFAULT,DEFAULT)'''.format(table_name))
 それ以外：
 cursor.execute(''' insert into {1}} (c2,c3) values(uuid(),uuid())'''.format(table_name))
 pymysql.Error を除き、e:
 print("mysql 実行エラー:", e)
 カーソルを閉じる()
 conn.commit()
 接続を閉じる()


__name__ == '__main__' の場合:
 time_start = time.time()
 ループ=10
 whileループ>0:
 mysql_write(ループ)
 ループ = ループ - 1

 time_end = time.time()
 time_c = time_end - time_start
 print('時間コスト', time_c, 's')

3. 関連分野における意味解析と論理的考察

この点については意見の相違が多すぎるため、噂や論争を引き起こす可能性も最も高いです。

1. 文字型の場合、NULL は存在しないことを意味し、'' は空を意味します。存在しないことと空は同じではありません。NOT NULL を使用してデフォルト値を指定しなければならないという考えには同意しません。
2. 文字型の場合、NULL はどのデータベースでも NULL と等しくありません。これは、関連フィールドの結合または where フィルターを処理するときに、接続の両側が NULL である状況を考慮する必要がないためです。NULL が '' に置き換えられると、'' は '' と等しくなり、NULL を格納する場合とはまったく異なるセマンティクスになります。
3. 文字型の場合、関連フィールドがデフォルトで '' に設定されると、 '' と空の文字列をどのように区別しますか? たとえば、備考フィールドでは NULL が許可されず、デフォルト値は '' です。 NULL の空表現とデフォルト値 '' の空文字列をどのように区別しますか?
4. 関連するクエリ操作では、NULLが許可されている場合、NULL以外の値をフィルタリングすることはwhere *** is not nullであり、これはセマンティクスの観点から非常に明確で直感的です。フィールドが空でなくなると、デフォルトで''になり、where *** <>''という超嫌な書き方が使用されます。正確に何を表現しているのか、セマンティクスが曖昧になり始めています。
5. 時間型の場合、デフォルト値はほとんどの場合許可されません。適切なデフォルト値は何ですか? 現在の時刻は適切ですか? 2000 年は適切ですか? 2008 年の北京オリンピックの開幕時間は適切ですか?
6. int や 10 進数などの数値型の場合、NULL が禁止されている場合、適切なデフォルト値は何ですか? 0 は適切ですか? -1 は適切でしょうか? -9999999…妥当でしょうか？ 10086は適切でしょうか? 1024は適切でしょうか?正直に言うと、デフォルトはどれも適切ではなく、NULL 自体が最も適切です。

私の個人的な意見は非常に明確です。フィールドに NULL 値があってはならないという特別な要件がない限り、通常の状況では、NULL は NULL です。
NULL に意味がないのであれば、データベースには NULL があってはなりません。実際、NULL 型を持たないデータベースはどれでしょうか?
もちろん、プロフェッショナルに見えるようにするために、根拠のないことを思いつく DBA がいる可能性も否定できません。現在では、データベースに対して課せられる制限が多ければ多いほど、優越感を抱く傾向があるのです。

デフォルト値について面白いことを思い出しました。ビリビリで動画を見ていたとき、ある投稿者が、ビリビリは登録ユーザーをデフォルトで男性に設定し、生年月日を特定日に設定しているため、ユーザーのクリックを分析した結果、不可解なデータを取得できたと言っていました。

私の個人的な知識は限られていますが、データは正直です。「すべてのフィールドが null ではない」ことが他にどのようなプラス効果をもたらすのか、そしてこのプラス要因をどのように測定するのかを本当に知りたいです。また、本当にそれを実行しましたか? インスタンス全体の下にあるすべてのデータベーステーブルのフィールドが null 可能になることを禁止できますか?

これで、MySQL でテーブルを作成するときの null と not null の詳細な使用法に関するこの記事は終了です。MySQL でテーブルを作成するときの null と not null に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: