勉強日記

チラ裏

理論から学ぶデータベース実践入門 ch9 履歴データとうまく付き合う

RDB SQL 勉強メモ

gihyo.jp

まとめ

  • 日時を表すカラムがある場合に限らない
  • すぐに気づきにくい
    • 「時間軸とリレーションが直行していない」など
  • 本質的なまずさは「特別な意味を持つタプル」が一緒くたになっていること
    • テーブル分割せよ
    • ただし、集計処理で利用するだけならば分割しなくても何ら問題ない
  • ヒント
    • ステータスやフラグを示すカラムがある
    • 初期値がNULLのカラムがある
    • 現在時刻との比較をしている
    • オンライントランザクション中にORDER BY N DESC LIMIT 1もしくはMAX()/MIN()/COUNT()が用いられている
    • バージョンを表すカラムがある
    • INSERT/DELETEよりもUPDATEの比率が高い

履歴データの問題点

  • 遅いクエリの多くが履歴データの扱いに失敗している
    • 本質的にRDBと相性が良くない

世界は履歴データで溢れている

  • わりに上手に付き合えているケースは少ない

履歴とリレーショナルモデルの相性問題

  • そもそも履歴データをリレーションとして表現できるかどうか
    • リレーションは集合なので各要素に順序はない
    • 履歴には順序がある
  • テーブルが巨大になりやすい

履歴データの具体例

item price start_date end_date
ダンベルセット 10000 2010-01-01 9999-12-31
グリッパー 4000 2013-04-01 2014-03-31
グリッパー 5000 2014-04-01 9999-12-31
懸垂マシン 18000 2010-01-01 2011-12-31
懸垂マシン 20000 2012-01-01 2014-12-31
懸垂マシン 22000 2015-01-01 9999-12-31
  • 懸垂マシンの現在の価格を調べるクエリ
SELECT price 
  FROM price_list
 WHERE item = '懸垂マシン'
   AND NOW() BETWEEN start_date AND end_date;
  • 本章で本クエリ、テーブルの問題を認識できるようになること

履歴データの何が問題になるのか

リレーションと時間軸との直交性

  • 同じクエリでも実行時刻により結果が異なる
  • リレーションは「ある時点における事実の集合」
  • 時間軸と直行でない(相関がある)ものはリレーションではない
    • = 1NFですらない

NULLの可能性

  • 9999-12-31は「設定値なし」を表す特殊な値
    • 【補】前章で「誤ったNULL対策」として挙げられていたパターン
  • 代わりにNULLを使用する設計も考えられる

特定の行だけ意味が違う

  • 簡単のために題材を少し変更
    • end_dateなし
    • 未来日付なし
item price start_date
ダンベルセット 10000 2010-01-01
グリッパー 4000 2013-04-01
グリッパー 5000 2014-04-01
懸垂マシン 18000 2010-01-01
懸垂マシン 20000 2012-01-01
  • 懸垂マシンの現在の価格を求めるクエリ
SELECT price
  FROM price_list
 WHERE item = '懸垂マシン'
   AND start_date = (SELECT MAX(start_date)
                       FROM price_list
                      WHERE item = '懸垂マシン');
  • 集計でもないのになぜ集約関数MAX()が必要なんですか
    • リレーショナルな演算でないので使わないに越したことはない
  • タプルの意味が命題関数だけで決まらず、均一でない
    • 暗黙の決まりごとがある
      • 「日付が最新の価格が現在の価格を表す」
    • 閉世界仮説に反する
      • リレーションの個々のタプルの意味は命題関数だけで決まり、それ以上でも以下でもいけない

履歴データに対する解決策

  • 絶対的な解はない
    • リレーショナルモデルに収まらない以上、次善の策の域を出ない

リレーションを分割する

  • 同一の命題関数で評価できないタプルを同一のテーブルに混ぜない
  • DB設計の盲点
    • 正規化プロセスでは発見できない

最もシンプルな分割方法

price_list

item price start_date
ダンベルセット 10000 2010-01-01
グリッパー 5000 2014-04-01
懸垂マシン 20000 2012-01-01

price_list_history

item price start_date
グリッパー 4000 2013-04-01
懸垂マシン 18000 2010-01-01
  • 現在の懸垂マシンの価格
SELECT price
  FROM price_list
 WHERE item = '懸垂マシン';
  • メリット
    • 現在の価格を取得するクエリがシンプルに
  • デメリット
    • 過去から現在までの全価格に対してクエリする場合はUNIONが必要に
      • WHERE句のNOW()や、集計でもないのにサブクエリに現れるMAX()よりはマシ
    • FK制約効かない
    • 整合性の確保が必要
      • トリガー
  • 元の設計よりはずっと好ましい

重複した行を許容する

price_list

item price start_date
ダンベルセット 10000 2010-01-01
グリッパー 4000 2013-04-01
グリッパー 5000 2014-04-01
懸垂マシン 18000 2010-01-01
懸垂マシン 20000 2012-01-01

price_list_history

item price start_date
グリッパー 4000 2013-04-01
懸垂マシン 18000 2010-01-01
  • メリット
    • FK制約使える
  • デメリット
    • 直交性失う

サロゲートキー

  • 重複は許さないが、FK制約は使いたい

price_id_master

price_id
1
2
3
4
5

price_list

price_id item price start_date
1 ダンベルセット 10000 2010-01-01
3 グリッパー 5000 2014-04-01
5 懸垂マシン 20000 2012-01-01

price_list_history

price_id item price start_date
2 グリッパー 4000 2013-04-01
4 懸垂マシン 18000 2010-01-01
  • メリット
    • 単一のprice_id_masterテーブルに対してFK制約使える
    • 価格データの重複ない
  • デメリット
    • 結合多い
    • オーバヘッド
      • 余計な列
      • 余計なインデックス

未来の価格はどうすべきか

  • やはり分ける
    • 意味が異なるから

price_id_master

price_id
1
2
3
4
5

price_list

price_id item price start_date
1 ダンベルセット 10000 2010-01-01
3 グリッパー 5000 2014-04-01
5 懸垂マシン 20000 2012-01-01

price_list_upcoming

price_id item price start_date
7 ダンベルセット 12000 2014-08-01
8 懸垂マシン 20000 2015-01-01

price_list_history

price_id item price start_date
2 グリッパー 4000 2013-04-01
4 懸垂マシン 18000 2010-01-01
  • 「未来」そのときがやってきたら？
    • 「現在の価格」を更新するバッチ処理が必要
    • この設計ではリアルタイムに価格を切り替えることはできない
      • そのような要件を盛り込まないように注意する

履歴データのアンチパターン

フラグを立てる

item price start_date end_date active
ダンベルセット 10000 2010-01-01 9999-12-31 1
グリッパー 4000 2013-04-01 2014-03-31 0
グリッパー 5000 2014-04-01 9999-12-31 1
懸垂マシン 18000 2010-01-01 2011-12-31 0
懸垂マシン 20000 2012-01-01 2014-12-31 1
懸垂マシン 22000 2015-01-01 9999-12-31 0
  • 現在の懸垂マシンの価格を取得
    • WHERE句のNOW()は消えた
SELECT price
  FROM price_list
 WHERE item = '懸垂マシン'
   AND active = 1;

問題

  • フラグカラムはカーディナリティ低い
    • 効率悪い
  • 2NFでない
    • 候補キーは{item, start_date, end_date}
    • フラグカラムが候補キーの真部分集合に関数従属
{start_date, end_date) -> {active}
  • activeカラムの更新バッチ処理が必要
    • start_date/end_dateとNOW()を使ったクエリとactiveを使ったクエリが混在すると不整合が生じる
  • active = 1なる行は常に1つでなければならない
    • トリガー

手続き型として実装する

  • 最後の手段

コラム1 フラグのお化け

  • 異なる意味合いのタプルは異なるリレーションに含めよ
  • 安易にフラグカラムを追加する前に、リレーション分割を検討する
  • さもないとフラグのおばけになる
    • 検索条件だらけのスパゲティクエリ
    • 実行速度も遅い

コラム2 テーブルを分けたときの物理的なメリット

  • テーブルのサイズが小さくなる
  • 検索速くなる
    • インデックス: O(log(n))
    • フルスキャン: O(n)
  • 頻繁にアクセスするテーブルの分離
    • たいてい「現在の値」のほうがヒストリよりもアクセス頻度高い