理論から学ぶデータベース実践入門 ch7 NULLとの戦い
まとめ
- NULLはリレーショナルモデルにとって害悪
- 2VLが3VLに変質
- 閉世界仮説の不成立
- オプティマイザ困惑
- 完全に排除できないこともある
- SQLのリレーショナルモデルを超えた表現力に起因
- 集計関数とか
- 対策が必要
- SQLのリレーショナルモデルを超えた表現力に起因
NULL
- NULL含みのテーブルは1NFの要件を満たさない
- 集合ではない => リレーションではない
NULLとは
3値論理(3VL)
NULLは演算を台無しにする
NULL + 1 NULL > 100 ...
- 値用の演算子を作用させると何やっても
NULL
になる(unknown)
検索結果が意図しないものになる可能性
- SELECTが行を返すのは、WHERE句の条件がTRUEになったときだけ
- FALSE, NULLのときは行を返さない
SELECT * FROM users WHERE age <> 20;
- ageがNULLを許す場合の意味合いは
「年齢が判明していて、かつ20歳ではないユーザ」 - 【補】排中律が成立しなくなる
NULLによる第3の論理値
- NULLがあるおかげで、論理値が3つ存在するかのように見える
- 3VL: Three Valued Logic
- 2VLより複雑
- SQLの開発効率への影響大
3値論理の限界
- 3VL自体に論理学的な誤りはない
- しかし3VLは現実を適切に表現できない
- リレーショナルモデルは現実の世界を適切に表現するためのモデル
- それが台無しになる
Unknownと曖昧さ
- Unknownってなに
- 情報が不自然に丸められてしまう
- 他の情報からの推論を行う余地がなくなる
- 論理的に正しくても値が正確でない
コラム: 量子コンピュータとNULL
- NULLは未知の値を表すマーカー
- 実際にはドメインのいずれかの値になるはず
- 量子ビット
- 0の状態と1の状態とを量子力学的に重ね合わせたもの
- 0の可能性も1の可能性も残して演算
- 量子コンピュータがあればNULLも怖くなくなるかもね!という妄想
NULLは閉世界仮説に反する
- 閉世界仮説
すべての問いがリレーションの演算だけで解決する
- NULLが演算に含まれると、この大前提が崩れる
- リレーショナルモデルが根底から崩壊する
オプティマイザへの弊害
- オプティマイザの仕事
- クエリの書き換え
- クエリのコスト見積もり
- クエリの書き換え
- 結果が等価になる範囲で、パフォーマンスが最適となるよう内部的に書き換える
- 結果の等価性は数学的な証明により裏付けられている
- NULLが含まれると、等価性を数学的に証明できる組み合わせが激減する
- クエリのコスト見積もり
- NULLになっているインデックスエントリは、インデックスの先頭もしくは最後尾にまとまって配置される
- NULL以外の値のカーディナリティが高くても、NULLが全部同列に扱われるため、IS NULLのスキャンに時間がかかる
NULL対策
テーブルを正規化する
- NULLが含まれると1NFでなくなる
- 1NFに正規化せよ
誤ったNULL対策
- カラムをNOT NULLにして、NULL代わりのデフォルト値にNULLのような意味を持たせる
- 状況はさらに悪化する
- 例: 年齢不明を
-1
としてしまう- 「20歳以下である」行を抽出しようとすると年齢不明の行も抽出されてしまう
- このようなローカルルールはテーブル定義には現れない
- ドキュメント等を通じて周知する必要のある技術的負債となる
- 例: 年齢不明を
COALESCE関数
- 正規化して、すべてのカラムをNOT NULLにしました
- それでもNULLは発生する
- 行数0の行に対する集計関数(COUNT以外)
- スカラまたは行サブクエリを実行した結果、該当する行がなかった場合
- OUTER JOIN
- CASE式でELSE省略時、どの条件にも該当しなかった場合
- NULLIF関数でわざわざNULLを生み出した場合
- やめろ
- COALESCE関数
- 使いどころ
- ダイナミックデフォルト
- 式を評価した結果がNULLだった場合のデフォルト値を設定する使い方
- SUM関数のデフォルトに0を使用
- スカラサブクエリのデフォルト値
- 式を評価した結果がNULLだった場合のデフォルト値を設定する使い方
- ダイナミックデフォルト
- 使うべきでないところ
- nullableなカラムのダイナミックデフォルト
- 前述「誤ったNULL対策」と本質的に何も変わらない
- nullableなカラムのダイナミックデフォルト
- 限界
- OUTER JOINで「マッチしない行を探す」場合などは依然として
IS NULL
が必要
- OUTER JOINで「マッチしない行を探す」場合などは依然として
空文字列の扱い
NULLを使っても良いケース
- テーブルをリレーションとして使用しないなら別にかまわない
- 正規化すらいらないだろう
- テーブルをリレーションとして使用するなら例外なく排除すべき