MySQL フィールドで NOT NULL を使用する必要があるのはなぜですか?

私は最近新しい会社に入社したのですが、データベース設計にいくつか小さな問題があることに気付きました。多くのデータベースフィールドに NOT NULL がありません。これは強迫性障害末期の患者にとってはまったく耐えられないことなので、この記事を書きました。

現在の開発状況のほとんどに基づいて、すべてのフィールドをNOT NULLに設定し、デフォルト値を指定します。

通常、デフォルト値は次のように設定されます。
整数。通常はデフォルト値として 0 を使用します。
文字列、デフォルトは空の文字列

時間はデフォルトの1970-01-01 08:00:01またはデフォルトの0000-00-00 00:00:00にすることができますが、接続パラメータにzeroDateTimeBehavior=convertToNull追加する必要があります。このデフォルトの時間形式は使用しないことをお勧めします。

しかし、理由を考えてみましょう。なぜ NOT NULL に設定する必要があるのでしょうか?

High Performance MySQL からの一節があります:

NULLを避けるようにしてください

多くのテーブルには、アプリケーションが NULL 値を格納する必要がない場合でも、NULL が列のデフォルトプロパティであるため、NULL (空の値) になる可能性がある列が含まれています。通常、NULL 値を格納する必要がある場合を除き、列が NOT NULL であることを指定するのが最適です。

NULL 列を含むクエリは、NULL 列によってインデックス、インデックス統計、および値の比較がより複雑になるため、MySQL では最適化がより困難になります。 NULL になる可能性のある列は、より多くのストレージスペースを使用するため、MySQL で特別な処理が必要になります。 NULL 対応の列にインデックスを作成すると、インデックスレコードごとに 1 バイト余分に必要になります。これにより、MyISAM では固定サイズのインデックス (単一の整数列のインデックスなど) が可変サイズのインデックスになることもあります。

一般に、NULL 可能列を NOT NULL に変更してもパフォーマンスの向上はわずかであるため、これが問題を引き起こすことが確実でない限り、まず既存のスキーマでこの状況を見つけて変更する必要はありません (チューニング時)。ただし、列にインデックスを作成する予定の場合は、NULL 可能となるように設計しないようにしてください。

もちろん例外もあります。たとえば、InnoDB は NULL 値を格納するために別のビットを使用するため、スパースデータのスペース効率が優れていることは注目に値します。これは MyISAM には適用されません。

この本の説明には、いくつかの大きな問題が言及されています。ここでは、MyISAM の問題については触れず、InnoDB に焦点を当てて検討します。

NOT NULL が設定されていない場合、NULL が列のデフォルト値になります。必要がない場合は、NULL を使用しないようにしてください。
NULL を使用すると、インデックス、インデックス統計、値の計算が複雑になるなど、さらに多くの問題が発生します。インデックスを使用する場合は、列を NULL に設定しないようにする必要があります。
インデックス列の場合、ストレージスペースの問題が発生し、追加の特別な処理が必要になり、さらに多くのストレージスペースが占有されることになります。
スパースデータの場合、スペース効率が向上します。スパースデータとは、多くの値が NULL であり、列に NULL 以外の値を持つ行が少数しかない状況を指します。

デフォルト値

MySql の場合、NOT NULL にアクティブに設定されていない場合、データを挿入するときのデフォルト値は NULL になります。

NULL と NOT NULL で使用される空の値の意味は異なります。NULL は、この列の値が不明であると考えることができますが、空の値は、値はわかっているが空であると考えることができます。

たとえば、テーブル内のnameフィールドが NULL の場合、名前がわからないと想定できます。逆に、空の文字列の場合は、名前がないこと、つまり NULL 値であることがわかっていると想定できます。

ほとんどのプログラムでは、フィールドを NULL にする必要は特にありません。逆に、NULL 値はプログラム内でヌルポインターなどの問題を引き起こします。

MyBatisが使用されている現在のほとんどの状況では、デフォルト値が有効にならない問題や NOT NULL フィールドを追加することによって挿入エラーが発生する問題を回避するために、デフォルトで生成されたinsertSelectiveメソッドまたは完全に手動の挿入メソッドを使用することをお勧めします。

価値計算

不正確な集計関数

NULL 値を持つ列の場合、集計関数を使用するときに NULL 値は無視されます。

これでテーブルができました。name nameはデフォルトで NULL です。この時点で、 name count結果は 1 ですが、これは間違っています。

count(*)テーブル内の行数をカウントし、 count(name)はテーブル内の NULL 以外の列をカウントします。

= 無効

NULL 値を持つ列の場合、 =式を使用して判断することはできません。 nameに対する次のクエリは無効であり、 is NULL使用する必要があります。

他の値の操作

NULL とその他の値演算は NULL であり、式の値も NULL になります。

userテーブルの 2 番目のレコードのage NULL なので、 +1後も NULL のままです。Name name NULL なので、 concat操作後も結果は NULL のままです。

次の例をもう一度見てみましょう。NULL を使用した操作はすべて NULL になります。設計したフィールドが NULL で、誤ってさまざまな操作を実行した場合、結果はどうなるか想像してみてください。。。

個別、グループ化、順序付け

distinctおよびgroup by場合、すべての NULL 値は等しいとみなされ、 order by場合、昇順の NULL 値が最初にソートされます。

その他の問題

テーブルには名前を持つレコードが 1 つだけあります。この時点で、name !=aクエリすると、残りの 2 つのレコードが見つかるはずですが、期待した結果が一致しないことがわかります。

インデックス作成の問題

NULL フィールドがインデックスに与える影響を検証するには、 nameとageにそれぞれインデックスを追加します。

クエリがNULLの場合はインデックスが使用できないという意見がネット上に多くありますが、これは正確ではありません。公式ドキュメント[3]によると、is NULLおよび範囲クエリを使用する場合、インデックスは正常に使用できます。実際の検証結果も同じようです。次の例を参照してください。

その後、テストのためにデータベースにいくつかのデータを挿入し続けました。NULL 列値の数が増えると、インデックスが無効になることがわかりました。

クエリ SQL の実行プロセスは、おおよそ次のようになることがわかっています。

まず、コネクタは指定されたデータベースに接続し、クエリキャッシュにこのステートメントがあるかどうかを確認します。ある場合は、結果を直接返します。

キャッシュがヒットしない場合は、アナライザーが SQL ステートメントの構文と字句解析を実行して、SQL ステートメントが正当かどうかを判断する必要があります。

ここでオプティマイザーが登場し、どのインデックスをより合理的に使用するかが選択され、SQL ステートメントの具体的な実行プランが決定されます。

最後に、エグゼキュータはステートメントを実行し、権限があるかどうかを照会し、実行結果を返す責任を負います。

上記の簡単なテスト結果から、書籍に記載されているように、インデックス列に NULL がある場合、オプティマイザーはより複雑になり、インデックス選択を行う際の最適化がより困難になることがわかります。

収納スペース

データベース内のレコードの行も、最終的なディスクファイルに行として保存されます。InnoDB には、 REDUNDANT 、 COMPACT 、 DYNAMIC 、およびCOMPRESSED 4 つの行ストレージ形式があります。

InnoDB のデフォルトの行ストレージ形式はCOMPACTです。ストレージ形式を以下に示します。点線部分は存在しない場合があります。

可変長フィールド長リスト：フィールドが複数ある場合は逆順で格納されます。フィールドは1つしかないので、そこまで考慮する必要はありません。格納形式は16進数です。可変長フィールドがない場合は、この部分は必要ありません。

NULL 値リスト: レコード内の値が NULL の場合の保存に使用されます。NULL 値が複数ある場合、それらも逆順で保存され、8 ビットの整数倍である必要があります。8 ビット未満の場合、上位ビットは 0 で埋められます。1 は NULL を表し、0 は NULL ではないことを表します。両方とも NULL でない場合は、これが存在します。

ROW_ID: レコード行の一意の識別子。主キーが指定されていない場合は、自動的に生成された ROW_ID が主キーとして機能します。

TRX_ID: トランザクション ID。

ROLL_PRT: ロールバックポインター。

最後に、各列の値です。

ストレージ形式の問題を説明するために、テスト用のテーブルを作成します。このテーブルでは、 c1フィールドのみが NOT NULL であり、他のフィールドは NULL になることができます。

可変フィールド長リスト: c1とc3フィールドの長さはそれぞれ 1 と 2 なので、16 進数に変換すると長さは0x01 0x02になり、逆順にすると0x02 0x01になります。

NULL値リスト：NULLを許容する列があるため、 c2,c3,c4それぞれ010です。反転後も同様です。同時に、上位ビットは0で埋められ、8ビットになります。結果は00000010です。

他のフィールドは今のところ無視します。最初のレコードの結果は当然ですが、ここではエンコード後の結果は考慮しません。

これは完全なデータ行の形式です。逆に、すべてのフィールドを NOT NULL に設定し、データa,bb,ccc,ddddを挿入すると、保存形式は次のようになります。

NULL 自体はストレージスペースを占有しませんが、NULL が存在する場合は、フラグスペースの余分なバイトを占有します。

記事参考資料:

https://dev.mysql.com/doc/refman/8.0/en/nullの問題.html
参考：https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html
参考文献
行フォーマット
https://www.cnblogs.com/zhoujinyi/articles/2726462.html

MySQL フィールドで NOT NULL を使用する理由に関するこの記事はこれで終わりです。MySQL フィールドで NOT NULL を使用する方法の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

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