D.Horiyama's output

理論から学ぶデータベース実践入門 ch7 NULLとの戦い

RDBSQL勉強メモ

出典: 

まとめ

  • NULLはリレーショナルモデルにとって害悪

    • 2VLが3VLに変質
    • 閉世界仮説の不成立
    • オプティマイザ困惑

  • 完全に排除できないこともある

    • SQLのリレーショナルモデルを超えた表現力に起因

      • 集計関数とか
    • 対策が必要

NULL

  • NULL含みのテーブルは1NFの要件を満たさない

    • 集合ではない => リレーションではない

NULLとは

  • NULLってなに

    • 値が存在しない(Not Applicable)
    • 値が不明である(Unknown)

  • 「NULL値」は誤用

    • 値ではない

      • ので、値用の演算子は適用できない

        • =とか

      • 専用の演算子を適用する

        • IS NULLとか

  • 空集合ともちがう

    • 空集合は「要素数が0の、実在する集合」
  • C言語等の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な値を返す
    • 【補】C#とかにある「NULL合体演算子」は「null-coalescing operator」

    • IFNULL関数というのもあるが、移植性に難

      • SQL標準ではない

  • 使いどころ

    • ダイナミックデフォルト

      • 式を評価した結果がNULLだった場合のデフォルト値を設定する使い方

        • SUM関数のデフォルトに0を使用
        • スカラサブクエリのデフォルト値

  • 使うべきでないところ

    • nullableなカラムのダイナミックデフォルト

      • 前述「誤ったNULL対策」と本質的に何も変わらない

  • 限界

    • OUTER JOINで「マッチしない行を探す」場合などは依然としてIS NULLが必要

空文字列の扱い

  • 空文字列とNULLとは本来論理的に全く異なる

    • 「長さ0の実在する文字列」であり、NULLとは異なる
    • 空集合が「要素数0の実在する集合」であるのと同じ
  • 【補】 != ''のケースが危険(MySQL)

NULLを使っても良いケース

  • テーブルをリレーションとして使用しないなら別にかまわない

    • 正規化すらいらないだろう
  • テーブルをリレーションとして使用するなら例外なく排除すべき