まとめ
- 日時を表すカラムがある場合に限らない
-
すぐに気づきにくい
- 「時間軸とリレーションが直行していない」など
-
本質的なまずさは「特別な意味を持つタプル」が一緒くたになっていること
- テーブル分割せよ
- ただし、集計処理で利用するだけならば分割しなくても何ら問題ない
-
ヒント
- ステータスやフラグを示すカラムがある
- 初期値が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()
は消えた
- WHERE句の
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)
- インデックス:
-
頻繁にアクセスするテーブルの分離
- たいてい「現在の値」のほうがヒストリよりもアクセス頻度高い