非正規化の判断
わざと冗長にする
ここまで冗長を潰してきました。今回はその逆、わざと冗長にする判断を扱います。これを非正規化と呼びます。
注文一覧の画面で合計金額を出したいとします。正規化された設計では、毎回こう計算します。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
SELECT sum(quantity * daily_fee) FROM loan_books WHERE loan_id = 1;明細が数件なら一瞬です。しかし注文が1000万件あって、一覧に100件並べるたびにこの計算をすると、じわじわ効いてきます。
そこで orders に total_amount という列を足して、合計を書き込んでおく。これが非正規化です。合計は明細から計算できるので、同じ事実が2か所にある状態を意図して作っています。
何を失うか
失うものははっきりしています。ずれます。
明細を1行追加したのに total_amount を更新し忘れたら、その瞬間から注文一覧の金額は嘘になります。しかも誰も気づきません。エラーは出ないし、画面は普通に表示されるからです。
正規化された設計では、この事故は起こりようがありません。合計は常に明細から計算されるので、明細を足せば合計も変わります。冗長を消すというのは、ずれる余地を消すということです。
それでもやるとき
非正規化は禁じ手ではありません。次の条件が揃ったときの正当な手段です。
- 速度の問題が実測で確認できている — 遅くなりそう、ではなく、実際に測って遅い
- ずれを防ぐ仕掛けがある — 明細を触ったら合計も必ず更新される仕組みを、トリガーやアプリの決まった1か所で作る
- ずれを検出できる — 定期的に再計算して突き合わせ、ずれたら気づけるようにする
この3つが無いまま「速そうだから」で足した集計列は、いずれずれる危険が高く、ずれても気づけません。
順番を間違えない
大事なのは順番です。まず正規化し切ってから、測って、必要なら崩す。
最初から崩した設計を作ると、どこが意図的な冗長でどこが設計ミスなのか、あとから誰にも区別できなくなります。正規形という基準があるからこそ、そこから外れた部分を「意図的に外した」と説明できます。
第5章ではインデックスを扱います。速度の問題の多くは、非正規化ではなくインデックスで解けるので、崩すのは最後の手段です。
用意済みの初期計算を読む
貸出ごとの料金を明細から求める例です。
SQL クエリ
UPDATE borrowings b
SET stored_fee = (
SELECT sum(days * daily_fee) FROM rental_fees WHERE loan_id = b.id
);括弧内は1つの合計を返す副問い合わせです。外側で更新している b.id を参照するので、貸出ごとに対応する明細だけを合計できます。これを相関副問い合わせと呼びます。演習ではこの計算は用意済みです。明細だけを変えると、保存済みの合計が自動では追従しないことを確認します。
手を動かす
集計列を足してから明細を変え、何も言われないままずれることを確かめます。
非正規化の危うさを実際に起こします。
ordersにtotal_amount integer列を足す- 各注文の合計金額を
order_itemsから計算してtotal_amountに入れる - 注文1のマウスの個数が間違っていたので、
quantityを5に UPDATE する。total_amountは更新しない - 注文ごとに、
order_id、保存されている合計をstored_total、明細から計算し直した合計をreal_totalの3列で、order_idの順に取り出す
手順3でエラーは出ません。それでも結果はずれます。
合計の初期計算と比較は用意済みです。保存する列を追加し、明細だけを更新して値のずれを観察します。
ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 4 行を補います。確認用の SQL は用意してあります。
テーブル構造
CREATE TABLE orders (
id integer PRIMARY KEY
);
CREATE TABLE order_items (
order_id integer NOT NULL REFERENCES orders (id),
product_id integer NOT NULL,
quantity integer NOT NULL,
unit_price integer NOT NULL,
PRIMARY KEY (order_id, product_id)
);
INSERT INTO orders VALUES (1), (2), (3);
INSERT INTO order_items VALUES
(1, 1, 1, 128000),
(1, 2, 2, 3200),
(2, 2, 1, 3200),
(2, 3, 1, 8900),
(3, 1, 2, 128000);
期待される出力
| order_id | stored_total | real_total |
|---|---|---|
| 1 | 134400 | 144000 |
| 2 | 12100 | 12100 |
| 3 | 256000 | 256000 |
ヒント
まだ開いていません
付箋は 3 枚。下の段ほど答えに近づきます
ヒント 2
ヒント 3答えに近い