物理設計の考え方
論理設計で「どのテーブルに何を持つか」は決まりました。ここからは、それを実際の DBMS の上に置く工程です。同じ ER 図から出発しても、型と制約の決め方だけで、後から効いてくる差が出ます。
とりあえず VARCHAR(255) で作ったテーブル
新しいテーブルを作るとき、迷ったら全部文字列にしておく、という作り方があります。動きはします。
SQL クエリ
CREATE TABLE users_v1 (
id VARCHAR(255),
email VARCHAR(255),
name VARCHAR(255),
age VARCHAR(255),
created_at VARCHAR(255)
);困るのは半年後です。
「20 代を出す」が壊れる
年齢を文字列で持つと、比較が数の大小ではなく文字の並び順になります。
SQL クエリ
SELECT id, name FROM users_v1 WHERE age >= '20' AND age < '30';これは 20 代を返しません。文字列としては '9' が '30' より後ろに並ぶため、9 歳が混ざったり、100 歳が外れたりします。型は「入れるときの制限」ではなく「取り出すときの前提」です。同じことは日付でも起きます。作成日時を文字列で持つと、月をまたいだ範囲指定も日付の足し算も成り立ちません。
型と制約は、壊れにくさを先に買う
書き直すと次のようになります。
SQL クエリ
CREATE TABLE users_v2 (
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL,
age SMALLINT CHECK (age BETWEEN 0 AND 150),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);主キーがあるので 1 行を特定できます。UNIQUE があるので同じメールアドレスで二重登録できません。age は数値なので範囲検索が意味を持ちます。制約は入力する側を少し面倒にする代わりに、壊れたデータを後から掃除する仕事を消してくれます。掃除のほうが桁違いに高くつきます。
物理設計は速くするための工程だと思われがちですが、半分は壊れにくくするための工程です。
今の行数で決めると、1 年後に困る
もう 1 つ、判断は今のデータ量ではなく将来のデータ量で行います。1 日に何行増えるか、1 行がおよそ何バイトかを掛ければ、1 年後にどれくらいの大きさになるかは概算できます。数千万行に育つテーブルと、ずっと数百行のままのテーブルとでは、掛けるべき手間がまるで違います。
VARCHAR(255)を並べるのは「桁を決めていない」という宣言と同じです。名前が 100 文字を超えないと分かっているなら、そう書いたほうが読み手にも伝わります。
テーブル構造
CREATE TABLE table_stats (
table_name VARCHAR(50) PRIMARY KEY,
row_count BIGINT NOT NULL,
avg_row_bytes INT NOT NULL
);
INSERT INTO table_stats (table_name, row_count, avg_row_bytes) VALUES
('users', 1000000, 200),
('orders', 5000000, 150),
('order_items', 20000000, 80),
('products', 50000, 500),
('reviews', 800000, 300);期待される出力
| table_name | estimated_mb |
|---|---|
| order_items | 1525.88 |
| orders | 715.26 |
| reviews | 228.88 |
| users | 190.73 |
| products | 23.84 |