理論から学ぶデータベース実践入門 ch8 SELECTを攻略する (1/2)

gihyo.jp

---

まとめ

• SELECTには、リレーショナルな操作とそうでない操作の両方が含まれる
• リレーショナルモデルの垣根を縦横無尽に飛び越えることができてしまう
  • ゆえに扱いが難しい

SELECTはSQLの心臓部

SELECTの本質

SELECTの強大さ

これはSQLそのものだ。SELECTのパーサなんて開発したら、RDBそのものを開発することになる！ (Bill Karwin)

• 誇張は入っているがあながち間違いでもない

データを取得する唯一の手段

• SELECTはSQLにおける心臓部
  • RDBにおいて、テーブルからデータを参照できるのはSELECTのみ
  • リレーションの演算相当の操作はすべてSELECTにより行われる

SELECTの基本構造

• 論理的な評価順(再掲)
  1. FROM句
     • テーブルのリスト(直積)
  2. WHERE句
     • 検索条件(制限)
  3. SELECT句
     • カラムのリスト(射影)

SELECT七変化

• すべてがSELECTという万能APIに詰め込まれている
• 文脈や用法により意味が変化する点が厄介

集約関数

関数の有無だけで意味が変わる

• select list
  • SELECT句におけるカラムのリスト
• select listに特定の関数があるかないかによって出力の形式が変わる
• 特定の関数: 集約関数
  • COUNT()とか

COUNTの特殊性

• 空集合を対象としたとき
  • COUNT(): 0
  • その他: NULL
    • SUM()とか
• NULL対策を怠るな(ch.7)

GROUP BYによる集約の書式

• 論理的な評価順 = 書式の順番
  1. (WHERE句)
  2. GROUP BY句
  3. HAVING句
  4. ORDER BY句
• WHERE句の検索条件にマッチしない行は集計対象外となる点に注意
  • 「0件」という集計結果を含めるためには基本形を逸脱しなければならない
    • 相関サブクエリ
    • LEFT OUTER JOIN
    • CASE式

サブクエリ

• 複数のタイプがある

テーブルサブクエリ

• WHERE句の中で IN、ANY(SOME)、ALL句に伴って利用されるもの
  • 【補】EXISTSも
• FROM句に現れるやつも
  • 【補】UNIONやMINUSのオペランドもこれかな

スカラサブクエリ

• select listに現れるやつ
  • サブクエリ結果がスカラ(1行1列)もしくはNULLでないとエラー

行サブクエリ

• サブクエリ結果が1行複数列のもの
• select listには含められない

ビュー

• リレーショナルモデルにおいて、ビューとベーステーブルとに区別はない
• 複雑さ隠蔽
• 水面下の実際は把握しづらくなる
• 性能問題に注意
  • ビューにサブクエリ、UNION、集約関数などが含まれていると問題になりやすい

UNION

• 2つのSELECTを「足せる」
• 2つのSELECTは属性が同じなだけで、中身は似ても似つかないかもしれないことに注意
  • 異なるテーブル
  • 異なる実行計画

組み合わせは自由

• これまで挙げたSELECTは自由に組み合わせられる

理論から学ぶデータベース実践入門 ch6 ドメインの設計戦略 2/2

gihyo.jp

---

IDを設計するという考え方

現実世界の物体や概念を表す手段

• DBに格納するIDは、現実世界の物体や概念を表すもの
  • 物体や概念を集合で表現するのがリレーショナルモデル
• 1つの属性が1つの物や概念と対応する
  • 全単射
• IDが物や概念との全単射であるかは設計次第
  • 一意になるような設計を人間がすることによってIDはIDとして機能する
  • 【補】
    • 「Gカップ」は閃乱カグラの世界の少女と1:1対応しないためIDとしては不適切
    • 「トップとアンダーの差が25cmの少女たち」という集団とは1:1対応するためIDとして利用できる
    • 議題領域を「半蔵学院」に限定すれば斑鳩さんを特定できるためIDとして利用できる
      • 飛鳥ちゃんが育ったらGになっちゃうのでやめたほうがいいと思う
  • 【補】GitやDockerでハッシュをIDとして使用しているのも関連する話か
    • 理論的には衝突が発生し、一意性が崩れる
    • が、実運用上扱うもの(ファイルやコンテナイメージ)の数に鑑みて、衝突することはないと想定

ナチュラルキーとサロゲートキー

• 著者見解: いずれもRDBにとっては必要
  • どちらか一方を排するべき、というのは暴論
• 本質的な違いはなく、すべて人工物である
  • 「自然キー」とはいうが、宇宙の摂理として存在するわけではない
  • 過去にほかの誰かが割り当てたか、自分で新しくIDを割り当てるかの違いしかない
• 用いる値がIDとして機能し得るものか、が本質
  • よくあるダメな例: 名前

ナチュナルキーの使いどころと問題点

• すでに誰かが運用し・何かを識別でき・長期に渡り値に変更がなく・信頼できること
  • 基礎年金番号とか
• IDのライフサイクルを吟味せよ
  • IDを発行した機関が、システムのライフサイクルよりも十分に長く運用を継続してくれること
• 権威があるように見えるIDであっても、実際には運用が失敗することも
  • ISBN
• 何を特定するものなのか吟味せよ
  • 例: e-mailアドレス
    • あくまでもe-mailアドレス自身を特定するもの
    • 人を特定するものではない

サロゲートキーの使いどころと問題点

• OK
  • 識別したい対象の物や概念を表すIDがいまだこの世界に存在しない
• NG
  • すでに適切なナチュナルキーが存在するのに、サロゲートキーを新たに作成する
  • 複合主キーを嫌って新たにサロゲートキーを追加する
    • 複数のサロゲートキーからなる場合など
      • すでに適切なサロゲートキーがあるなら、なおさら必要ない
• 不要なサロゲートキーは関数従属性を生じてしまう
  • DB設計を無駄に複雑化する

リレーショナルモデルにおけるキー

• ナチュナルキーやサロゲートキーはリレーショナルモデル上の概念ではない
  • リレーショナルモデルにあるのは候補キーとスーパーキーのみ

意味を含んだID

• 「青いサイクロン式の掃除機」の製品コードCLN-CYC-0123-BL
  • CLN: 掃除機
  • CYC: サイクロン
  • 0123: 世代？
  • BL: 色
• このような属性を定義すると1NFではなくなる
  • 列の値がアトミックではなくなるため
• 個々のパーツを個々の属性として定義せよ
  • くっつけるのは表示上の問題であり、データの本質ではない
• こういうときにサロゲートキーを導入するのは悪い選択肢ではない
  • 製品コードを個々のパーツに個々の属性に分けると、製品と1:1で対応するIDとなる属性がそのリレーションに存在しなくなるから

紙の呪縛

• 人間が視認しやすいIDを推す人々が存在する
  • 「帳票に印刷したときに見やすい」
  • 「ITシステムが止まっても、紙を使って、業務を遂行できる」
• 紙の文化がITシステムに侵食しDB設計を歪めてしまう
  • 「表示上の問題はDBと分離せよ」という考えに反している

IDの欠陥は波及する

• あるリレーションのキーは別のリレーションにも登場する
  • 外部キー
• IDの選定をしくじるとあらゆるリレーションに影響が及ぶ

色、長さ、重さなどの性質を表す属性

• {製品ID, 製品カテゴリ, 製品カラー}という属性をもつリレーションの述語をP(x,y,z)とする

性質を述語と解釈すると、リレーショナルモデルでは扱えない

• ある物体cが「黒い」という述語

Black(c)

• cが黒ければ真、さもなくば偽
• P(x,y,z)のzには述語Black(c)が入る
• P(x,y,z)は述語に対する述語となる
• このような述語を扱えるのは二階述語論理であり、リレーショナルモデルでは扱えない

「集合にラベルをつけた」と解釈すればリレーショナルモデルに適合する

• 「黒」という色の名称、すなわちラベル

Black

• P(x,y,z)のzには値Blackが入る
• 一階述語論理なので、リレーショナルモデルに適合する
• 個々のラベルに重複がなければ、性質という概念を識別するIDとして使用できる

SQLによるドメインの表現

適切なデータ型を選ぶ

• SQLのデータ型はドメインと必ずしも1:1対応しない
• 最低限、ドメインの持つ値をすべてカバーできること
  • = ドメインからSQLのデータ型への写像が全射であること
  • 例
    • 数値の桁数が十分である
    • 文字列の長さが十分である
• 最もよくそのドメインの特徴を表すデータ型を用いること
  • 本質的に数値であるものを文字列型で表すのはNG
    • 無駄にデータサイズ大きい
    • 数値としての演算を行えない

述語を制約で表現する

• ドメインとカラムのデータ型とは1:1、全単射であることが望ましい
  • 全射止まりだと、ドメインにない値も格納できてしまうため
• CHECK制約で縛る
  • 数値を範囲で
  • 文字列を正規表現で
• デメリット
  • 制約をつける手間
  • オーバヘッド

ドメインをテーブルとして表現する

• マスタテーブル
  • ドメインに含まれるすべての値を、他のテーブルにあらかじめ格納しておく
  • 【補】FK制約で縛る
• ENUM型
  • DB製品によって利用可能
  • マスタテーブルよりデータサイズがコンパクト

JWT認証アプリケーションで認可つきCSVダウンロードを実装した話

---

背景

• Laravel + Vue.jsアプリケーション
• JWT認証のSPA
  • つまり、すべてのリクエストのAuthorizationヘッダにJWTを載せている

axios.defaults.headers.common['Authorization'] = `Bearer ${jwt}`;

• 集計したデータをCSVファイルとしてダウンロードさせたい
• ただし、認可もつけたい
  • 未ログイン状態ではダウンロードさせない

困ったこと

• RESTful APIを叩いているうちは何も問題はない
• CSVダウンロードで困った
  • <a href="csvダウンロードリンク">...ではダメ
    • Authorizationヘッダが載らないため、認可が通らない

やったこと

サーバ側でCSV生成

• こんな感じのCsvDownloaderクラスを作り、コントローラに注入して使った
• 参考

<?php
declare (strict_types = 1);

namespace App\Http\Controllers\Concerns;

use Illuminate\Http\Response;
use Illuminate\Support\Collection;

/**
 * データをCSVファイルとしてダウンロードする
 */
class CsvDownloader
{
    /**
     * @param array|Collection $header 見出し
     * @param array|Collection $body データ本体
     * @param string $fileName
     * @return Response
     */
    public function downloadCsv(
        $header,
        $body,
        string $fileName
    ): Response {
        // 仮ファイル
        $stream = fopen('php://temp', 'w');

        // 見出し書き込み
        fputcsv(
            $stream,
            collect($header)->toArray()
        );

        // データ本体書き込み
        collect($body)->each(
            function ($row) use (&$stream) {
                fputcsv(
                    $stream,
                    $row
                );
            }
        );

        // ポインタの先頭へ
        rewind($stream);

        // 変換
        // - 改行コード
        $csv = str_replace(
            PHP_EOL,
            "\r\n",
            stream_get_contents($stream)
        );
        // BOM
        $csv= pack('C*',0xEF,0xBB,0xBF) . $csv;


        $httpHeaders = [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => 'attachment; filename="' . $fileName . '"',
        ];

        return response(
            $csv,
            Response::HTTP_OK,
            $httpHeaders
        );
    }

• 何も考えずにBOMなしUTF-8で出力すると、Excelで開いた時に文字化けしてしまう
• 回避方法は下記のようなものがある:
  • BOMつきUTF-8にする
  • SJISにする
  • (他にもUTF-16とか)
• フロントエンドとの兼ね合いで、SJISではなくBOMつきUTF-8にした (後述)

フロントエンド側でBlob生成

• CSVデータのダウンロードはAjaxで行い、フロントエンドでBlob化しダウンロードリンクを生成するようにした
• 参考

 export default {
...
     methods: {
         // AjaxでサーバからCSVを落としてくる
         downloadCsvReport() {
             axios.get('/download/report')
                  .then(response => response.data)
                  .then(data => this.downloadCommon(data, 'filename.csv'));
         },
         // 落としてきたCSVをBlobにしてダウンロードリンクを生成する
         downloadCommon(data, filename) {
             const anchor = document.createElement('a');
             document.body.appendChild(anchor);
             // ここでもBOMをつける必要がある
             // さもないとExcelで文字化けする
             const bom = new Uint8Array([0xEF, 0xBB, 0xBF]);
             const blob = new Blob([bom, data], {type: 'text/csv'});
             const objectUrl = window.URL.createObjectURL(blob);
             anchor.href = objectUrl;
             anchor.download = filename;
             anchor.click();

             window.URL.revokeObjectURL(objectUrl);
         }
...

• buttonの@clickか何かで実行すればCSVファイルをダウンロードできる
• Chromeで動作確認

踏んだ文字化けのパターン

• サーバ側でCSVのエンコーディングをSJISにしてしまった
  • フロントエンド側でUTF-8としてデコードし、文字化けしてしまう
• フロントエンド側でBlob生成時にBOMを付与し忘れた
  • フロントエンドでHTTPレスポンスをデコードした時点でBOMは外れる
  • つまりサーバ側でBOMを付与する必要はない
    • が、サーバ単体でまともなCSVファイルをレスポンスできないのは気持ち悪いので付与した

LINEBotプロジェクト振り返り

プロジェクト概要

• LINEBot

よかった

• LINEBotの開発経験を積めた
• 静的なドキュメントをある程度書いた
  • ERD
    • PlantUMLを使用した
      • メリット
        • 差分管理できる
      • デメリット
        • 編集が面倒くさい
    • 今度はMySQL Workbenchを使って比較検討してみよう
  • ドメインモデル
    • PlantUMLを使用した
• JWT認証 + 認可付きCSVダウンロードの初実装
  • 別記事
• サーバ側は真面目にテスト書いた
  • 変更箇所が甚大な修正も安心して行えた
• きれいな設計の意識
  • 依存性逆転
    • サポートするLINEBotの返信タイプは追加が予想されるため、インタフェースに依存させるなど
  • デザインパターンの適用
    • GoF
      • Visitor
        • ドメインオブジェクトからLINEBot依存の振る舞いの分離
    • PoEAA
      • DataMapper
        • 複雑なドメインオブジェクトとテーブルとのマッピングに使用

よくなかった

• 永続化データ構造を自前で定義してしまった
  • 具体的には、LINEBotの返信オブジェクトのJSON表現
  • MessageBuilder@buildMessage()で返却されるJSON表現をそのまま使用するべきだった
  • プロジェクト初期に謎の自前フォーマットを定義し、そのまま突っ走ってしまった
    • LINE非依存であり、Slack等にも横展開できるため、一概に失敗とは言えないかも
• CI構築をサボった
  • 当初はもっとサクッと終わる想定だったんですよ
    • CI構築するほどではない、と思っていた
  • Laradockコンテナのビルドに時間がかかるためCI構築の試行錯誤が億劫
    • そろそろLaradockやめたい
    • ビルド済イメージをdockerhubかどこかにpushしておきたい
• Visitorパターンを適用した結果、Acceptor追加時の変更箇所が甚大になった
  • パターンの特徴なので、前もってわかってはいた
  • 今回はドメインオブジェクトからLINE実装を分離することを選んだ
• ユースケース記述やロバストネス図を書かなかった
  • 「元気な時に書こう」は永久に書かない
• migrationまわりの扱いが雑だった
  • migrationの編集はGitのrebaseと同じ感覚で実施したほうがよさそう
    • 一度作ってpushしたら二度と触ってはいけない
• プロジェクトの全体像を理解できていなかった
  • 客との調整をプロキシに丸投げして実装オンリー
  • 認識齟齬による手戻りが何度かあった
  • 客の温度感もわからなかった
• 見積もりが甘かった
• フロントエンドはテスト書かなかった
• emacsの調整不足
  • PHP(intelephense)
    • Carbon/Carbonなど巨大なファイルを読むと定義ジャンプで数秒〜十数秒固まる
  • Vue.js(忘れた)
    • なんか常に重い
    • <style lang="stylus">をちゃんと認識できてない

Keep

• 静的なドキュメント書く
• 真面目にテスト書く
• きれいな設計
  • そのために勉強
    • PoEAAさっさと倒す
    • ほいでDDD読む

Problem

• 「よくなかった」全部

Try

• 外部サービスとの連携等がある場合は、そのデータ表現を真っ先に調べる
  • 今回で言うとLINEBotの返信のJSON表現
• その次くらいに真っ先にCI環境を構築する
• ユースケース記述やロバストネス図も書く
• ERDの作成にMySQL Workbench使ってみる
• そのために、客やプロキシとのコミュニケーションを密にする
• Laradockそろそろやめる
• 見積もり精度を上げる
  • タスクを細分化して積み上げて見積もる
  • 実績値と比較して開発速度データを蓄積する
• emacs調整する

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

gihyo.jp

---

まとめ

• 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を使っても良いケース

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

理論から学ぶデータベース実践入門 ch6 ドメインの設計戦略 1/2

gihyo.jp

---

まとめ

• ドメインの設計は、現実世界とリレーショナルモデルの世界とをつなぐ架け橋となる作業
• 恣意的
  • 絶対的な正解はない
  • 最も難しい
• データの本質を理解することが重要
  • 本質的でないデータをDBに持ち込まない
    • 表示上の都合など
• リレーショナルモデルについての理解が不可欠
  • 例: IDに意味をもたせると1NFですらなくなる

ドメイン

1. アプリケーションにはどのようなリレーションが必要か？
2. リレーションにはどのような属性が必要か？
   • 決めなければならないことは2つ
     • 名前
     • ドメイン(データ型)

ドメインとは

• リレーショナルモデルにおけるデータ型
  • 属性が取りうる値の集合
  • 述語論理における「論理領域」に相当する

集合の要素

• 明確な値を持っていること
• 該当しないもの
  • NULL
  • ポインタ
• ドメインに含まれる可能性のある値をすべて列挙するのは無謀
• ドメインは集合なので、1:1対応する述語が存在する
• それがどのような意味を持つかを想定しておけば十分

ドメインの設計戦略の概要

• 「設計」
  • ノウハウや経験に基づく
  • 論理的に導出される絶対的な正解や筋道がない
  • 戦略や哲学が必要とされる

すべては恣意的な選択

• ドメインを定めるのは設計者の主観
  • 宇宙の摂理ではない
• 最終的にはSQL上で表現する
  • カラムの値がドメインの要素と対応するようカラムを設計

アプリケーションの要求から生まれる

• アプリケーションが必要とするデータを洗い出さないとドメインの設計はできない
  • DB全体の設計にもいえること
  • DBについていくら勉強しても身につくものではない
• 適切なDB設計は、アプリケーションに対する理解なくしては有り得ない
  • のでアプリケーション開発者が少なくとも関与できるべき
• アプリケーション設計手法を身につけておくべき
  • DDDとか
• DBリファクタリング
  • アプリケーションは何度もリファクタリングする
  • DBもリファクタリングが当然必要
    • DBもアプリケーションの一部

データの本質を見極める

• 数値に文字列カラムを割り当てる過ち
  • 学籍番号など、本質は数値であるもの
  • 表示上の都合でCHAR(8)とかにするな
    • 本質的なデータと表示は分けて設計すべき
    • DBが引き受けるべきは前者のみ

属性(カラム)の名前

• 名が体を表せ
  • DBに限らない
• アプリケーション内で対応するクラスや変数と同じ名前をつけるべき
  • アプリケーション側でクラス名や変数名をリファクタリングしたら、DB側も同期せよ

理論から学ぶデータベース実践入門 ch5 リレーションの直交性

gihyo.jp

---

まとめ

• RDBでしっかりと重複を解消しないのは道具としての使い方を間違っている
  • 正しい答えを得られない
  • 本来の性能を引き出せない

リレーションの直交性(Orthogonality)

• 複数のリレーション間の重複に関する概念
  • cf. 正規化は1つのリレーション内部の重複に関する概念
• DB全体から重複を解消する作業ともいえる
• 一言で言うと「同じ値を含まない」ということ
• 「同じ属性を含まない」ではない
  • もしそうなら直積しかできない
• 直交化してない状態とは？？

レプリカ

• 一番わかりやすいやつ
• 全く同じ構造、同じデータのリレーションが2つ
• どちらか片方だけ使え

同じ型のリレーション

• 同じ型だが重複データをもたないやつ
  • 積集合(結合の特殊なもの)が空集合になる
• 例: 水平分割したテーブル
  • 月ごとにテーブルを分けてるとか
• SQLに「同じ値を『含まない』」ことを保証する直接的な機能はない
  • cf. FK制約は同じ値を『含む』ことを保証
• トリガー使うとか？

見出しの一部だけが同じリレーション

• 判断が難しいやつ
• リレーションに下記が残っていると、リレーションを直接比較して直交性を判断することはできない
  • 自明ではない関数従属性
  • 暗黙的でない結合従属性
• 6NFまで無損失分解すれば、タプルの比較で直交性を保証できる
  • 値が重複しているか、あるいは将来的に重複する可能性のある設計であるかが重要
• 見出しが重複していても、値が直交していれば無理に統合する必要はない

リレーション直交化のための戦略

正規化

• 直交性の保証のためには6NFまでの正規化が必要
• 6NFまで正規化するためには5NFまで正規化されていることが必要
• 実際のテーブルも5NFまでは正規化せよ
  • 6NFは無駄な結合が多くなるため、普通しない

属性(カラム)の名前を統一する

• よくないもの
  • 同じものを指しているのに異なる名前
    • 見落とす可能性が高まる
  • 異なるものを指しているのに同じ名前
    • 同じものを指している可能性を逐一検証する手間が発生する
• 命名における注意点
  • 命名規則を統一する
    • 日本語/アルファベット
    • ローマ字/英単語
    • パスカル/スネーク
  • 主語を含める
    • id -> user_id
    • name -> student_name
    • email -> sns_user_email
    • qty -> order_item_qty

アプリケーションの整合性

• アプリケーションの設計上の問題に起因することがほとんど
• 例
  • 異なる2つの機能で同じ意味のデータが必要になった際、
    共通のコンポーネントを設計せず、それぞれ独自にDBにデータ登録した
  • システム統廃合で名寄せをサボった
• 共通の意味のデータが必要なら、アプリケーションコードもリファクタリングして共通化せよ
  • アプリケーションのロジックからDB側の問題を発見する

すべてを直交化する必要はない

• 例: 条件ごとにユーザを別々のテーブルに分ける場合
• 各テーブルが表す条件の意味が完全に独立したものであれば、特に設計上の問題はない
• 両方の条件を満たす

SELECT user_name
  FROM A
 INNER JOIN B
 USING (user_name)

• いずれかの条件を満たす

(SELECT user_name
   FROM A)
UNION
(SELECT user_name
   FROM B)

重複を解消することのメリット

• 正規化・直交化のメリットを振り返りまとめる

異常を防げる

• 述語論理の天敵
  • Principle of Explosion

必要なデータがどこにあるかが明確になる

• 直交化されていないDBでは、どのリレーションを対象に演算を行えば良いのか一意に定まらない

クエリの記述が宣言的になる

• 最低限1NFなら、テーブルはリレーションである
• テーブルがリレーションなら、クエリは述語論理になる
  • howではなくwhatを記述できる

不要な無損失分解が必要ない

• 正規化されていない = 結合(JOIN)されている
  • クエリを書くたびに無損失分解(SELECT DISTINCT サブクエリ)が必要になる

複雑な制約が必要ない

• 正規化されていないと
  • 自明ではない関数従属性が残っている場合
    • 更新時に関数従属性を壊さないよう制約が必要
      • クエリごとのトリガーで集計を行う必要があるだろう
• 直交化されていないと
  • 重複データをもつ複数のテーブルの同期をとる必要がある
  • FK制約は使えない
    • FK側が部分集合である必要がある
    • 共通でない行が互いのテーブルに含まれる場合は適用不可

アプリケーションのコードに無駄がなくなる

• DB側で重複を解消しないと、アプリケーション側で異常のチェックが必要になる
  • 異常発生の予防
  • 異常発生時のハンドリング
  • そのテスト
    • 予想以上に甚大

性能が向上する

• 重複を解消したほうが性能がよくなることも
  • 参照系
    • 結合は増えてしまう
    • が、前述の「クエリを書くたびに無損失分解」する必要がなくなる
  • 更新系
    • 複雑な制約が必要なくなる
    • アプリケーション側でも異常予防・ハンドリングのロジックが不要に
• 「性能を向上するために非正規化する」のは特殊なケース
  • 一般的には重複があることによりDBの負担は飛躍的に上昇する