正規化の功罪

• なんで正規化するの

  • 整合性の保持のため

• 負の側面

  • パフォーマンス劣化

    • しばしばシステムとして実用に耐えないくらい
    • 本章のテーマ:現場での対処

正規化とSQL(検索)

会社

社員

部署

• 第3正規形

• 田島さんがいま努めている会社は？

  • JOIN

SELECT 会社.会社名
      ,社員.社員名
  FROM 社員 INNER JOIN 会社
               ON 社員.会社コード = 会社.会社コード
 WHERE 社員.社員名 = '田島';

• 部署も知りたければ部署テーブルもINNER JOIN
• 会社ごとの社員数は？

SELECT 会社.会社コード
      ,COUNT(社員.社員名) AS 社員数
  FROM 会社 LEFT OUTER JOIN 社員
              ON 社員.会社コード = 会社.会社コード
 GROUP BY 会社.会社コード;

• 何か知りたければJOIN不可避

• SQLにおける結合は非常に高コスト

  • 正規化によるシステムパフォーマンスの劣化の原因

非正規化による解決

非正規化した社員テーブル

• 部分関数従属ありまくりなので第2正規形ですらない
• 田島さんがいま努めている会社と部署名は？

SELECT 会社名
      ,社員名
      ,部署名
  FROM 社員
 WHERE 社員名 = '田島';

• 非常にすっきりシンプル、パフォーマンスも良い

正規化とSQL(更新)

• 正規化のほうに軍配があがる

  • というかそもそもそのための正規化

• 例: 会社名が変わったときの更新行数

  • 第3正規形: 会社テーブルの1行
  • 第2正規形以前: 社員テーブルの複数行

正規化と非正規化、どちらが正解なのか？

• 原則は正規化
• 正規化の次数と検索SQLのパフォーマンスとの間には強いトレードオフがある

• 「非正規化」はあくまでも最後の手段
• パフォーマンスを向上させるためのその他すべての戦略が要件を満たさない場合だけ (C.デイト)

• 逆に言えば、最初は必ず正規化する

  • 次数は高いほどよい

非正規化とパフォーマンス

• 正規化すなわち冗長性排除

• 性能問題をひきおこすパターンの大分類

  • サマリデータの冗長性排除
  • 選択条件冗長性排除

• 1対多の関連を持つ2つのテーブル間で生ずる

  • 受注 -|---< 受注明細 とか

受注

受注明細

サマリデータの冗長性とパフォーマンス

• 受注日ごとに何個の商品が注文されていますか

SELECT 受注.受注日
      ,COUNT(*) AS 商品数
  FROM 受注 INNER JOIN 受注明細
               ON 受注.受注ID = 受注明細.受注ID
 GROUP BY 受注.受注日

• 受注、受注明細ともにレコード数は膨大
• レコード数が膨大なテーブルの結合は非常に高コスト

結合しないSQLを作るためのテーブル設計

• サマリデータ列追加する

受注

• 結合なしのSQL

SELECT DISTINCT 受注日
      ,商品数
  FROM 受注;

• 非正規化

  • 推移的関数従属

{受注ID} -> {受注日} -> {商品数}

選択条件の冗長性とパフォーマンス

• 受注日が2019-06-14〜2019-06-15の期間に注文された商品の一覧を出力せよ

SELECT 受注.受注ID
      ,受注明細.商品名
  FROM 受注 INNER JOIN 受注明細
                  ON 受注.受注ID = 受注明細.受注ID
 WHERE 受注.受注日 BETWEEN '2019-06-14' AND '2019-06-15';

• 膨大なレコード数のテーブルの結合

結合しないSQLを作るためのテーブル設計

検索条件列を追加した受注明細

• 結合なしのSQL

SELECT 受注ID
      ,商品名
  FROM 受注明細
 WHERE 受注日 BETWEEN '2019-06-14' AND '2019-06-15';

• 非正規化

  • 部分関数従属

{受注ID} -> {受注日}

「非正規化を勧めている」わけではない

• あくまで原則は可能な限り高次の正規化

冗長性とパフォーマンスのトレードオフ

• 更新不整合以外のリスク

更新時のパフォーマンス

• 商品数の例
• 集計の負荷がかかる

データのリアルタイム性低下

• いつ商品数集計すんの

  • 日次、深夜だけでいい？
  • 30分ごと、昼間でも必要？
  • まさかのリアルタイム

• これも結局トレードオフ

  • 高頻度: 高負荷、更新の性能問題起きやすい
  • 低頻度: ユーザが嬉しくない
  • 業務要件とセットで平衡点を探る

改修コストの大きさ

残念なことに、論理設計には物理設計の知識が必要である

• DOAではDの変更は非常に改修コスト高い

• したがって、論理設計はしくじれない

  • システムの品質はここで決まる

• 論理設計者は、開発チームのエースでなければならない

  • パフォーマンスとのトレードオフを知り尽くした上で、
    あらゆる要件を同時に満たせる平衡点を見つけ出せないといけない
  • パフォーマンスについて考えるからには、物理層についても踏み込まざるを得ない