非正規化の判断
注文一覧の画面を 20 件ぶん表示するたびに、明細テーブルを引いて合計金額を計算し直しています。注文が数千件なら気になりませんが、数百万件まで増えると、この 1 画面が数秒かかるようになります。
きれいに分けたのに、読み出しが重い
ここまでで、同じ値を 2 か所に持たないようにテーブルを分けてきました。分けた設計は更新に強い代わりに、読み出すたびに JOIN と集計が必要になります。注文の合計金額は明細から計算できる値なので、どこにも保存していません。だから毎回計算しています。
この重さを消す手が非正規化です。計算すれば出せる値を、あえて列として持ちます。
先に正規化して、遅い場所だけ崩す
順番を逆にしてはいけません。最初から「JOIN は遅いから 1 枚にまとめよう」と始めるのは、非正規化ではなくただの設計ミスです。3NF まで整えて、実際に動かして、どのクエリが遅いかを測って、そこだけ崩します。
崩すのは 1 列で足ります。
SQL クエリ
ALTER TABLE orders ADD COLUMN total_amount INT NOT NULL DEFAULT 0;これで一覧の表示は明細を見ずに済みます。ただし、インデックスの調整だけで十分なことも多いので、崩す前に一度そちらを試してください。
崩した列は、更新する場所が 2 か所になる
非正規化の代金は書き込み側で払います。明細を 1 行足したのに合計の列を直し忘れると、画面の合計と内訳が食い違います。しかも、どちらが正しいのか後からは誰にも分かりません。
2 か所の更新は必ず 1 つのトランザクションにまとめます。別の題材で書くと次のようになります。
SQL クエリ
BEGIN;
INSERT INTO comments (article_id, body) VALUES (7, '参考になりました');
UPDATE articles SET comment_count = comment_count + 1 WHERE id = 7;
COMMIT;片方だけ成功する状態を作らないのが要点です。それでも障害やバグでズレは出るので、夜間に元データから数え直して上書きする補正の仕組みも合わせて用意します。
SQL クエリ
UPDATE articles a
SET comment_count = (SELECT COUNT(*) FROM comments c WHERE c.article_id = a.id);非正規化を入れるかどうかは「速くなるか」ではなく「ズレを直し続けられるか」で決めます。直す仕組みまで用意できないなら、崩さないほうが安全です。
テーブル構造
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
unit_price INT
);
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
unit_price_at_order INT,
PRIMARY KEY (order_id, product_id)
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
total_amount INT
);
INSERT INTO products VALUES
(10, 'ノート', 350),
(11, 'ペン', 180),
(12, '消しゴム', 120);
INSERT INTO orders VALUES
(1001, 1, 1200),
(1002, 2, 500),
(1003, 3, 1380);
INSERT INTO order_items VALUES
(1001, 10, 2, 300),
(1001, 11, 4, 150),
(1002, 10, 1, 300),
(1002, 11, 2, 200),
(1003, 10, 3, 360),
(1003, 12, 3, 100);期待される出力
| order_id | total_amount | calculated_total |
|---|---|---|
| 1002 | 500 | 700 |