1つの表に全部入れると、更新・挿入・削除で異常が起きます。わざと壊れる表を作って、正規化が何を解決するのかを見ます。
前回: SQLiteはおもちゃではない — 用途で選ぶデータベース 作業場所はVMの中(Ubuntu Server)です。MySQL に
appdbがあり、tasksテーブルと~/serverの Knex マイグレーションが動いている状態から始めます。
最初に、順番について正直に書きます。
テーブル設計の話は、本当ならもっと早い段階でやるものです。 MySQLの回で CREATE TABLE を打った、あの直後が「正しい」位置です。
それでもここまで引っ張りました。 理由は1つです。
困っていない人に「正規化」を説明しても、暗記にしかならないからです。
「第1正規形は繰り返しを無くす、第2正規形は部分関数従属を無くす、第3正規形は推移的関数従属を無くす」—— この3行を先に読んでも、何ひとつ手は動きません。 私は実際に、この3行を何度読んでも自分の表をどう直せばいいのか分かりませんでした。
いまなら違います。あなたはもう、
- 索引とEXPLAINの回で、表の作り方が速さを決めるのを見た
- トランザクションの回で、途中で壊れたデータを見た
- NoSQLの回で、スキーマはアプリ側に移るだけで消えないことを見た
この3つを見たあとの「正規化」は、暗記ではなく修理になります。
なので、この記事では番号を先に出しません。 まずわざと壊れる表を1枚作って、実際に壊します。 壊れたところを直していったら、結果として第3正規形になっていた——という順番で進めます。番号との対応表は、いちばん最後に置きます。
対象レベル: 初級〜中級 / 区分: 必修
この記事でわかること
- 1つの表に全部入れると、同じ情報が何行にも重複すること。そして重複そのものより、重複が食い違うことが問題だということ
- 「異常」には3種類あること。直したのに直っていない(更新時)/関係ないデータが無いと入れられない(挿入時)/消したら別の情報まで消える(削除時)
- 正規化は「重複を消す作業」であって、番号を当てはめる作業ではないこと。第3正規形まででほぼ足りること
- 外部キーは「繋ぐもの」ではなく「壊れた繋がりを拒否するもの」だということ。
ON DELETEの選択が事故を防ぐこと - 主キーを連番にするかUUIDにするかで、索引の太り方が変わること
- 正規化しすぎると
JOINが増えて遅くなること。あえて重複を残す判断は、測ってからしかできないこと - NULL は「無い」ではないこと。「空」「未定」「該当なし」を1つの記号で兼ねると、あとで区別できなくなること
この記事でやること
- わざと壊れる表を1枚作って、そこに現実的なデータを入れる
- SQLを打って、3つの異常を実際に起こす。 食い違った状態を目で見る
- 重複を外に出して表を分ける。 外部キーで繋ぎ直し、
ON DELETEを3種類とも試す - 主キーを連番とUUIDの2通りで作って、索引の大きさを比べる
- 正規化しすぎた形を
EXPLAINで測り、あえて重複を戻す判断をする - できあがった設計を Knex のマイグレーションに落とす
先に知る言葉
この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。
| 言葉 | 意味 |
|---|---|
| 正規化 | 同じ情報が2箇所以上に書かれている状態を、1箇所にまとめていく作業。 表を分けることになる |
| 異常(アノマリー) | 重複したせいで起きる不都合。 この記事では更新時・挿入時・削除時の3つを扱う |
| 主キー | 1行を1つに特定するための列。 重複できず、空にできない |
| 外部キー | 「この列の値は、あっちの表に必ず存在する」という決まり。 無い値を入れようとすると拒否される |
ON DELETE | 参照先を消したとき、参照している側をどうするかの指定。拒否する/一緒に消す/空にする |
| 候補キー | 主キーになれる資格のある列(の組)。メールアドレスのように「重複しない」列も候補キー |
| 関数従属 | 「Aが決まればBも決まる」という関係。 社員番号が決まれば氏名も決まる、など |
| 推移的関数従属 | A→B→C と2段階で決まってしまっている状態。Cは本来Bの表に置くべき |
| 非正規化 | 速さのために、あえて重複を残す(戻す)こと。正規化の失敗ではなく、測ったうえでの判断 |
| ER図 | 箱(表)と線(関係)だけで設計を描いた図。 Entity-Relationship の略 |
| カーディナリティ | 関係の個数の向き。1対1 / 1対多 / 多対多 |
| 中間テーブル | 多対多を表すために挟む表。 「どのタスクにどのタグが付いているか」だけを持つ |
| UUID | どこで作っても重複しないことになっている36文字の識別子。 550e8400-... の形 |
この記事を通して言いたいのは、たった1文です。 「同じことを2箇所に書くと、いつか片方だけ古くなる」。 正規化は、その「いつか」を構造的に起こせなくする作業です。
① わざと壊れる表を作る
説明を読むより、壊れるものを持っているほうが早いので、先に作ります。
この記事で作る表は、すべて d_ で始めます。 d は draft(下書き)のつもりです。既存の tasks や users には一切触りません。 最後に d_ で始まる表をまとめて消せば、元通りになります。
VMの中で MySQL に入ります。
「タスク管理アプリを作っていて、担当者とプロジェクトとタグも持ちたくなった」という、ごく普通の状況を想定します。何も考えずに列を足していくと、こうなります。
USE appdb;
CREATE TABLE d_flat (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
done BOOLEAN NOT NULL DEFAULT FALSE,
assignee_name VARCHAR(100),
assignee_email VARCHAR(255),
project_name VARCHAR(100),
project_due DATE,
tags VARCHAR(255)
);
この表は、動きます。 アプリからも普通に読めますし、JOIN も要りません。むしろ最初は、こちらのほうが書きやすいくらいです。
データを入れます。
INSERT INTO d_flat (title, assignee_name, assignee_email, project_name, project_due, tags) VALUES
('ログイン画面を作る', '佐藤', 'sato@example.com', 'サイト移行', '2026-10-31', 'ui,api'),
('APIの仕様を書く', '佐藤', 'sato@example.com', 'サイト移行', '2026-10-31', 'api'),
('DNSを切り替える', '鈴木', 'suzuki@example.com', 'サイト移行', '2026-10-31', 'infra'),
('バックアップを取る', '鈴木', 'suzuki@example.com', '運用改善', '2026-11-30', 'infra,api-docs');
中身を見ます。
SELECT id, title, assignee_name, assignee_email, project_name, project_due FROM d_flat;
+----+--------------------+---------------+----------------------+--------------+-------------+
| id | title | assignee_name | assignee_email | project_name | project_due |
+----+--------------------+---------------+----------------------+--------------+-------------+
| 1 | ログイン画面を作る | 佐藤 | sato@example.com | サイト移行 | 2026-10-31 |
| 2 | APIの仕様を書く | 佐藤 | sato@example.com | サイト移行 | 2026-10-31 |
| 3 | DNSを切り替える | 鈴木 | suzuki@example.com | サイト移行 | 2026-10-31 |
| 4 | バックアップを取る | 鈴木 | suzuki@example.com | 運用改善 | 2026-11-30 |
+----+--------------------+---------------+----------------------+--------------+-------------+
ここで、目で見て気づくことがあります。
sato@example.comが2回書いてあるサイト移行 / 2026-10-31が3回書いてある
タスクが4件でこれです。 タスクが1000件になれば、佐藤さんのメールアドレスは数百箇所に書かれます。
ここで多くの入門記事は「無駄だから消しましょう」と言います。 その説明では弱いです。 ディスクは安いので、「無駄」だけなら我慢すればいい話です。
本当の問題は、無駄ではありません。
同じ情報が何箇所にもあると、「全部を同時に直す」ことに失敗したときに、どれが正しいのか誰にも分からなくなります。
これを実際に起こします。
② 3つの異常を、実際に起こす
ここがこの記事の山場です。 読み飛ばさずに、手で打って結果を見てください。
異常その1 — 直したのに直っていない(更新時異常)
佐藤さんがメールアドレスを変えました。 アプリの「担当者の編集」画面から更新した——つもりの SQL がこれです。
UPDATE d_flat SET assignee_email = 'sato.new@example.com' WHERE id = 1;
1行だけ更新されます。 それはそのとおりです。問題は、この表には佐藤さんの行が2つあることです。
SELECT DISTINCT assignee_name, assignee_email FROM d_flat ORDER BY assignee_name;
+---------------+----------------------+
| assignee_name | assignee_email |
+---------------+----------------------+
| 佐藤 | sato.new@example.com |
| 佐藤 | sato@example.com |
| 鈴木 | suzuki@example.com |
+---------------+----------------------+
[スクリーンショット: 01_更新時異常.png] 上の UPDATE と、その直後の SELECT DISTINCT を実行して、「佐藤」が2つのメールアドレスを持っている状態が写るように撮ってください。
佐藤さんが2人になりました。 データベースは何のエラーも出していません。制約に違反していないからです。
そして、ここからが本当に困るところです。
どちらが正しいのか、データベースに聞いても分かりません。
「新しいほう」と言いたくなりますが、この表には「いつ更新したか」も入っていません。 アプリ側で「タスク一覧の担当者メール」を表示すれば、同じ佐藤さんが、行によって違うアドレスで表示されます。
これが更新時異常です。 「1箇所だけ直してしまった」のではなく、「1箇所だけ直せてしまう構造になっている」のが問題です。
WHERE assignee_name = '佐藤'にすれば全部直る、と思いましたか。 それは「アプリが毎回正しく書けば大丈夫」という話で、NoSQLの回で見た「スキーマがアプリ側に移る」と同じ形です。 人が忘れられる場所に、正しさを置かない。 これが今回のテーマです。
戻しておきます。
UPDATE d_flat SET assignee_email = 'sato@example.com' WHERE assignee_name = '佐藤';
異常その2 — 関係ないデータが無いと入れられない(挿入時異常)
新しいプロジェクト「監視の導入」が決まりました。 締切は 2026年12月20日。タスクはまだ1件も決まっていません。
この表に、プロジェクトだけを登録しようとします。
INSERT INTO d_flat (project_name, project_due) VALUES ('監視の導入', '2026-12-20');
ERROR 1364 (HY000): Field 'title' doesn't have a default value
入りません。 title が NOT NULL だからです。
ここで「じゃあ title を NULL 可にすればいい」と考えたなら、それが罠です。 そうすると今度は、「タイトルの無いタスク」がタスク一覧に出てきます。
現場でよく起きる回避策がこれです。
INSERT INTO d_flat (title, project_name, project_due) VALUES ('(仮)', '監視の導入', '2026-12-20');
入りました。 そしてタスク一覧に「(仮)」という幽霊タスクが1件増えました。
SELECT id, title, project_name FROM d_flat WHERE title = '(仮)';
これが挿入時異常です。
プロジェクトを登録したいだけなのに、タスクを1件でっちあげないと登録できない。
原因は明確です。 この表は「タスク」の表なのに、「プロジェクト」の情報も持たされているからです。プロジェクトは、タスクとは独立して存在できるものです。独立して存在できるものは、独立した表にするべきです。
幽霊を消しておきます。
DELETE FROM d_flat WHERE title = '(仮)';
異常その3 — 消したら別の情報まで消える(削除時異常)
「運用改善」プロジェクトのタスクは、いま1件だけです(バックアップを取る)。 これが終わったので消します。
消す前に、必ず件数を見ます。
SELECT COUNT(*) FROM d_flat WHERE title = 'バックアップを取る';
DELETE FROM d_flat WHERE title = 'バックアップを取る';
タスクを1件消しただけのつもりです。 ところが。
SELECT DISTINCT project_name, project_due FROM d_flat;
+--------------+-------------+
| project_name | project_due |
+--------------+-------------+
| サイト移行 | 2026-10-31 |
+--------------+-------------+
「運用改善」というプロジェクトが、締切ごと消滅しました。
誰も消していません。 プロジェクトの情報が、タスクの行にしか存在していなかったからです。最後の1件を消した瞬間に、道連れになりました。
これが削除時異常です。 そして3つの中でいちばん怖いです。理由は、
エラーが出ないうえに、消えたことに気づくのが数か月後だからです。
更新時異常は「食い違い」として残ります。 削除時異常は、痕跡ごと消えます。
3つをまとめる
| 異常 | 何が起きるか | 根本の原因 |
|---|---|---|
| 更新時異常 | 1箇所だけ直せてしまい、同じものが食い違う | 同じ情報が複数の行に書かれている |
| 挿入時異常 | 関係ないデータをでっちあげないと登録できない | 独立して存在できるものが、別のものの表に同居している |
| 削除時異常 | 1件消したら、別の情報まで消える | その情報の置き場所が、そこしか無い |
この3つは、全部「同じ1つの原因」から出ています。
1つの表に、2種類以上の「もの」を入れたから。
d_flat には「タスク」「担当者」「プロジェクト」「タグ」の4つが入っています。 だから4つ分の事故が起きます。
③ 直す — 番号ではなく「いま何をしているか」で
ここから直します。 正規形の番号は、まだ出しません。 やっていることだけを書きます。
直し1 — 1つのマスに2つ以上入れるのをやめる
まず tags を見てください。
SELECT id, title, tags FROM d_flat;
+----+--------------------+----------------+
| id | title | tags |
+----+--------------------+----------------+
| 1 | ログイン画面を作る | ui,api |
| 2 | APIの仕様を書く | api |
| 3 | DNSを切り替える | infra |
+----+--------------------+----------------+
1つのマスに、カンマ区切りで複数の値が入っています。 これをやると、SQLが使えなくなります。
「api タグの付いたタスクを探す」をやってみます。 api-docs というタグを付けた行は ② で消してしまったので、1件入れ直してから試します。
INSERT INTO d_flat (title, assignee_name, assignee_email, project_name, project_due, tags)
VALUES ('手順書を書く', '鈴木', 'suzuki@example.com', '運用改善', '2026-11-30', 'api-docs');
SELECT id, title, tags FROM d_flat WHERE tags LIKE '%api%';
+----+--------------------+----------------+
| id | title | tags |
+----+--------------------+----------------+
| 1 | ログイン画面を作る | ui,api |
| 2 | APIの仕様を書く | api |
| 6 | 手順書を書く | api-docs |
+----+--------------------+----------------+
api-docs が混ざりました。 別のタグなのに、文字列として含まれてしまうからです。
しかも、LIKE '%...%' は索引が効きません。 パフォーマンスの回でやったとおり、前が % で始まる検索は、必ず全件を読みます。
EXPLAIN SELECT id FROM d_flat WHERE tags LIKE '%api%';
type が ALL になっているはずです。 索引を張っても変わりません。
「部分一致を速くしたい」という話に見えますが、違います。 これは検索の問題ではなく、設計の問題です。 本当に部分一致で探したいものは全文検索の回の領域ですが、タグは部分一致で探すものではありません。
やることは1つです。
1つのマスには、1つの値しか入れない。 複数入れたいなら、行を増やす(=別の表にする)。
タグは「1つのタスクに複数」かつ「1つのタグが複数のタスクに」——多対多です。多対多は、間に表を1枚挟みます。
直し2 — 「そのもの自身の情報」を、そのものの表に移す
次に、assignee_email を見ます。
d_flat の主キーは id(タスクのID)です。 そこで、この表の各列が「本当にタスクの情報か」を1つずつ問います。
| 列 | これは何の情報か |
|---|---|
title | タスクの情報。 タスクが決まればタイトルが決まる |
done | タスクの情報 |
assignee_name | タスクの情報(誰が担当か、はタスクごとに決まる) |
assignee_email | 違う。 これは「佐藤さん」が決まれば決まる。タスクは関係ない |
project_name | タスクの情報(どのプロジェクトか) |
project_due | 違う。 これは「サイト移行」が決まれば決まる。タスクは関係ない |
assignee_email と project_due だけ、性質が違います。
タスクID → 担当者名 → メールアドレス タスクID → プロジェクト名 → 締切
2段階で決まっています。 これを推移的関数従属と呼びます。名前は覚えなくていいので、判定のしかたを覚えてください。
「この列は、主キーではなく、同じ行の別の列によって決まっていないか?」
決まっているなら、その列は「決めているほう」の表に移します。
直し3 — 中間テーブルの列を疑う
タグの中間テーブルを作るとき、こういう書き方をしたくなります。
-- ⚠️ これは作りません。悪い例です
CREATE TABLE d_bad_task_tags (
task_id INT NOT NULL,
tag_id INT NOT NULL,
tag_name VARCHAR(50) NOT NULL,
PRIMARY KEY (task_id, tag_id)
);
主キーが (task_id, tag_id) の2列組です。 そこに tag_name を持たせました。「毎回 JOIN しなくて済むから」という理由で、これは本当によく書かれます。
でも tag_name は、tag_id だけで決まります。 task_id は関係ありません。 つまり主キーの一部にしか依存していない——これを部分関数従属と呼びます。
何が起きるかは、もう分かるはずです。
タグ名を
apiからbackendに改名したとき、この中間テーブルの全行を書き換えないと食い違います。
更新時異常が、そのまま再発します。 だから中間テーブルには、繋ぐための2列だけを置きます。
④ 表を分けて、外部キーで繋ぐ
直し1〜3をそのまま形にします。
CREATE TABLE d_members (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE
);
CREATE TABLE d_projects (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
due_on DATE NULL
);
CREATE TABLE d_tags (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE d_tasks (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
done BOOLEAN NOT NULL DEFAULT FALSE,
project_id INT NULL,
member_id INT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_task_project FOREIGN KEY (project_id)
REFERENCES d_projects(id) ON DELETE SET NULL,
CONSTRAINT fk_task_member FOREIGN KEY (member_id)
REFERENCES d_members(id) ON DELETE RESTRICT
);
CREATE TABLE d_task_tags (
task_id INT NOT NULL,
tag_id INT NOT NULL,
PRIMARY KEY (task_id, tag_id),
CONSTRAINT fk_tt_task FOREIGN KEY (task_id)
REFERENCES d_tasks(id) ON DELETE CASCADE,
CONSTRAINT fk_tt_tag FOREIGN KEY (tag_id)
REFERENCES d_tags(id) ON DELETE RESTRICT
);
表が1枚から5枚になりました。 増えたことに不安を感じるのが普通です。 ⑦でその不安に答えます。
データを入れます。
INSERT INTO d_members (name, email) VALUES
('佐藤', 'sato@example.com'), ('鈴木', 'suzuki@example.com');
INSERT INTO d_projects (name, due_on) VALUES
('サイト移行', '2026-10-31'), ('運用改善', '2026-11-30'), ('監視の導入', '2026-12-20');
INSERT INTO d_tags (name) VALUES ('ui'), ('api'), ('infra'), ('api-docs');
INSERT INTO d_tasks (title, project_id, member_id) VALUES
('ログイン画面を作る', 1, 1),
('APIの仕様を書く', 1, 1),
('DNSを切り替える', 1, 2),
('手順書を書く', 2, 2);
INSERT INTO d_task_tags (task_id, tag_id) VALUES
(1,1), (1,2), (2,2), (3,3), (4,4);
さっきの3つの異常が、どうなったか確かめます。
更新時異常 —— 佐藤さんのメールを変えます。
UPDATE d_members SET email = 'sato.new@example.com' WHERE id = 1;
SELECT t.title, m.name, m.email FROM d_tasks t JOIN d_members m ON t.member_id = m.id;
佐藤さんの担当タスクは、全部いっぺんに新しいアドレスになります。 1箇所しか無いからです。 「全部直す」を頑張ったのではなく、「1箇所だけ直す」が不可能になりました。
挿入時異常 —— タスクの無いプロジェクト。
SELECT * FROM d_projects WHERE name = '監視の導入';
もう入っています。 タスクをでっちあげる必要はありませんでした。
削除時異常 —— 「運用改善」の最後のタスクを消します。
DELETE FROM d_tasks WHERE title = '手順書を書く';
SELECT * FROM d_projects WHERE name = '運用改善';
プロジェクトは残っています。 消したのはタスクだけです。
3つとも、構造で消えました。 アプリ側は何も書いていません。
ON DELETE の選択が、事故を防ぐ
上の CREATE TABLE で、ON DELETE を3種類とも使い分けました。 ここが外部キーのいちばん重要な部分です。
| 書き方 | 参照先を消そうとしたとき | 使いどころ |
|---|---|---|
ON DELETE RESTRICT(既定) | 削除そのものを拒否する(エラー) | 消えたら困るもの。 迷ったらこれ |
ON DELETE CASCADE | 参照している行も一緒に消す | 親が消えたら意味を失うもの(中間テーブル、明細行) |
ON DELETE SET NULL | 参照している列を NULL にする(列が NULL 可であること) | 親が消えても、子は残ってよいもの |
実際に試します。まず RESTRICT。
DELETE FROM d_members WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`appdb`.`d_tasks`, CONSTRAINT `fk_task_member` FOREIGN KEY (`member_id`) REFERENCES `d_members` (`id`))
[スクリーンショット: 02_外部キー拒否.png] 上の DELETE が ERROR 1451 で拒否されている画面を撮ってください。この失敗が正常な動作なので、そのまま撮って構いません。
「担当タスクが残っている人は消せない」という業務ルールが、SQLで書かれていないのに効いています。これが外部キーの本当の役割です。
外部キーは「表を繋ぐもの」ではありません。 繋ぐだけなら列に数字を入れるだけで足ります。 外部キーは「繋がっていない状態を作れなくするもの」です。
次に SET NULL。プロジェクトを消します。
DELETE FROM d_projects WHERE name = 'サイト移行';
SELECT id, title, project_id FROM d_tasks;
+----+--------------------+------------+
| id | title | project_id |
+----+--------------------+------------+
| 1 | ログイン画面を作る | NULL |
| 2 | APIの仕様を書く | NULL |
| 3 | DNSを切り替える | NULL |
+----+--------------------+------------+
プロジェクトは消え、タスクは残り、所属だけが外れました。 意図どおりなら正解です。
最後に CASCADE。タスクを消します。
SELECT * FROM d_task_tags WHERE task_id = 1;
DELETE FROM d_tasks WHERE id = 1;
SELECT * FROM d_task_tags WHERE task_id = 1;
タグの結び付きも一緒に消えました。 タスクが消えたのに結び付きだけ残っても、誰も使えないので、これが正しい挙動です。
CASCADEは、いちばん事故を起こす選択肢でもあります。 「1行消したつもりが、その下の数千行が黙って消える」が起きます。しかもエラーは出ません。 削除時異常を自分で作っているのと同じなので、「消えても本当に困らないか」を1回止まって考えてください。 迷ったらRESTRICTです。拒否されたら、そのとき考えれば済みます。
プロジェクトを戻しておきます。
INSERT INTO d_projects (id, name, due_on) VALUES (1, 'サイト移行', '2026-10-31');
UPDATE d_tasks SET project_id = 1 WHERE id IN (2, 3);
⑤ 主キーは連番かUUIDか
ここまで、主キーは全部 INT AUTO_INCREMENT(連番)にしてきました。 これでいいのか、という話をします。
選択肢は大きく2つです。
連番(INT AUTO_INCREMENT) | UUID | |
|---|---|---|
| 見た目 | 1, 2, 3 | 550e8400-e29b-41d4-a716-446655440000 |
| 誰が決めるか | データベース(入れてみないと分からない) | アプリ側で先に作れる |
| 大きさ | 4バイト(BIGINT なら8) | 文字列なら36バイト、BINARY(16) なら16 |
| 推測できるか | できる。 /tasks/1 の次は /tasks/2 | できない |
| 件数が漏れるか | 漏れる。 IDが 4821 なら「4821件くらい」と分かる | 漏れない |
| 複数のDBで作れるか | ぶつかる | ぶつからない |
「推測できる」は、思っているより現実的な問題です。
認証の回やセキュリティの回で見たとおり、/api/tasks/2 を /api/tasks/3 に書き換えて他人のデータが見えるなら、それは連番のせいではなく権限確認をしていないせいです。UUIDにしても穴は塞がりません。 ただし、総当たりで全件を舐めることは難しくなります。
索引への影響 —— ここがパフォーマンスの回の回収
UUIDには、「大きい」以外の落とし穴があります。 これはEXPLAINの回を踏まえないと意味が分からない部分です。
MySQL(InnoDB)では、次の2つが効いてきます。
- データは主キーの順に並べて格納される(クラスタ化索引)
- 主キー以外の索引は、内部に「主キーの値」を持っている
1 が意味すること。 連番なら、新しい行は必ず末尾に足されます。UUID(v4)はランダムなので、毎回まったく違う位置に割り込みます。 割り込まれたページは分割され、隙間が増えます。
2 が意味すること。 主キーが太ると、すべての索引が太ります。 主キーが36バイトの文字列なら、title の索引にも done の索引にも、その36バイトが付いてきます。
測ります。 パフォーマンスの回と同じ手で、10万行を2通り作ります。
CREATE TABLE d_pk_int (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
KEY idx_title (title)
);
CREATE TABLE d_pk_uuid (
id CHAR(36) PRIMARY KEY,
title VARCHAR(200) NOT NULL,
KEY idx_title (title)
);
SET SESSION cte_max_recursion_depth = 1000000;
INSERT INTO d_pk_int (title)
WITH RECURSIVE seq AS (
SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 100000
)
SELECT CONCAT('ダミー', n) FROM seq;
INSERT INTO d_pk_uuid (id, title)
SELECT UUID(), title FROM d_pk_int;
UUID() は MySQL が持っている関数なので、追加のインストールは要りません。
大きさを比べます。
ANALYZE TABLE d_pk_int, d_pk_uuid;
SELECT table_name, data_length, index_length
FROM information_schema.tables
WHERE table_schema = 'appdb' AND table_name LIKE 'd\_pk\_%';
+------------+-------------+--------------+
| TABLE_NAME | data_length | index_length |
+------------+-------------+--------------+
| d_pk_int | 5783552 | 3686400 |
| d_pk_uuid | 12582912 | 8388608 |
+------------+-------------+--------------+
手元では、データも索引も2倍以上になりました。 数字はVMの状態で変わりますし、information_schema の値は概算です。 正確な比較ではなく、「桁で違う」ことを見てください。
索引が2倍になるということは、メモリに載る量が半分になるということです。 パフォーマンスの回で見た「索引が効いている / 効いていない」の手前に、「索引がメモリに載っているか」という段があります。
結局どうするか
この記事の結論は、こうです。
| 状況 | 選ぶもの |
|---|---|
| 1台のデータベースに書く。学習用。個人開発 | 連番でよい。 迷わなくていい |
| IDをURLに出す。件数を隠したい | UUID、または連番+別途「公開用ID」 |
| 複数の場所でIDを作る(オフライン対応、分散) | UUID一択 |
そして、UUIDを使うときの実務的な逃げ道が2つあります。
CHAR(36)ではなくBINARY(16)に詰める。 MySQL 8 ならUUID_TO_BIN(uuid, 1)/BIN_TO_UUID(id, 1)が使えます。第2引数の1が重要で、時刻部分を前に並べ替えて、連番に近い順序にしてくれます- UUIDv7 を使う。 先頭に時刻が入っている新しい形式で、ランダムなのに時刻順に並びます。 ランダム割り込みの問題が起きません
「主キーは連番にしなさい」でも「UUIDにしなさい」でもありません。 どちらにも代償があり、その代償が索引に出る——これが分かっていれば十分です。
⑥ NULL は「無い」ではない
d_tasks で project_id INT NULL を許しました。 ここで NULL の話をします。
NULL は「値が無い」と説明されますが、実際には次の3つを兼ねてしまいます。
| 意味 | 例 |
|---|---|
| まだ決まっていない(未定) | 締切がまだ決まっていない |
| そもそも該当しない | プロジェクトに属さないタスク |
| 入力されなかった(不明) | 移行してきた古いデータで、値が取れなかった |
この3つは、業務上まったく違う話です。 なのに、データベース上は全部 NULL になります。 あとから「これは未定だったのか、該当なしだったのか」を区別することはできません。
そして NULL は、SQLの挙動そのものを変えます。 これは実際に打ってみるのが早いです。 ここまでの手順で project_id が NULL のタスクは残っていないので、先に1件入れてから打ちます。
INSERT INTO d_tasks (title, project_id, member_id) VALUES ('備品を発注する', NULL, 2);
SELECT COUNT(*) AS 全件, COUNT(project_id) AS project_idが入っている件数 FROM d_tasks;
SELECT * FROM d_tasks WHERE project_id = NULL;
SELECT * FROM d_tasks WHERE project_id IS NULL;
SELECT * FROM d_tasks WHERE project_id <> 1;
4つとも、結果が違います。
| 打ったもの | 結果 | 理由 |
|---|---|---|
COUNT(*) と COUNT(列) | 数が違う | COUNT(列) は NULL を数えない |
WHERE project_id = NULL | 0件(エラーにもならない) | NULL は「値」ではないので = で比較できない |
WHERE project_id IS NULL | 出る | NULL を調べる唯一の書き方 |
WHERE project_id <> 1 | NULL の行が出てこない | 「1ではない」の判定が、NULLに対しては「不明」になる |
最後の行が、いちばん事故ります。
「プロジェクト1以外のタスクを全部出す」つもりのSQLから、所属なしのタスクが黙って消えます。
空文字列('')と NULL も別物です。
SELECT '' = NULL, '' IS NULL, LENGTH(''), LENGTH(NULL);
'' は「長さ0の文字列がある」、NULL は「そもそも無い」です。混ぜて入れると、WHERE name = '' と WHERE name IS NULL の両方を書く羽目になります。
だから、既定は NOT NULL にする
設計時の方針を1つ決めておきます。
列は、まず
NOT NULLで作る。 NULL を許すのは、「NULLである」ことに意味があると説明できるときだけ。
d_tasks でいうと、こうです。
| 列 | 判断 |
|---|---|
title | NOT NULL。 タイトルの無いタスクは存在しない |
done | NOT NULL DEFAULT FALSE。 「未着手」は false であって NULL ではない |
project_id | NULL 可。 「どのプロジェクトにも属さない」に意味がある |
member_id | NULL 可。 「まだ誰も担当していない」に意味がある |
due_on | NULL 可。 「締切なし」に意味がある |
「なんとなく NULL 可」を無くすだけで、あとで書くSQLがかなり減ります。
NOT NULL DEFAULT ''に逃げないでください。 「空文字を入れておけば NULL を避けられる」は、NULLの問題を「空文字とNULLの2種類ある」問題に増やしただけです。
⑦ 正規化しすぎると遅くなる
ここまで「分けろ」と言い続けてきました。 その代償を、いま払います。
「プロジェクトごとのタスク件数を出す」をやります。
d_flat(1枚の表)ならこうです。
SELECT project_name, COUNT(*) FROM d_flat GROUP BY project_name;
分けたあとはこうです。
SELECT p.name, COUNT(t.id)
FROM d_projects p
LEFT JOIN d_tasks t ON t.project_id = p.id
GROUP BY p.id, p.name;
さらに「担当者名とタグも一緒に出したい」となると、こうなります。
SELECT t.title, m.name AS 担当, p.name AS プロジェクト,
GROUP_CONCAT(g.name) AS タグ
FROM d_tasks t
LEFT JOIN d_members m ON t.member_id = m.id
LEFT JOIN d_projects p ON t.project_id = p.id
LEFT JOIN d_task_tags tt ON tt.task_id = t.id
LEFT JOIN d_tags g ON g.id = tt.tag_id
GROUP BY t.id, t.title, m.name, p.name;
JOIN が4つになりました。 これが正規化の代償です。
| 1枚にまとめた形 | 正規化した形 | |
|---|---|---|
| 書きやすさ | 簡単(SELECT * FROM d_flat) | JOIN が要る |
| 読む速さ | 速い(1つの表を読むだけ) | 遅くなりうる(表を突き合わせる) |
| 正しさ | 壊れる | 壊れない |
| 書く速さ | 遅い(同じ値を何度も書く) | 速い(1箇所だけ書く) |
あえて重複を残す(非正規化)
「プロジェクト一覧に、タスク件数を出したい」という画面があったとします。毎回 JOIN して COUNT するのが重いなら、d_projects に件数の列を持たせる——これが非正規化です。
ALTER TABLE d_projects ADD COLUMN task_count INT NOT NULL DEFAULT 0;
これは「重複」です。 d_tasks を数えれば分かることを、もう1箇所に書いているからです。つまり、更新時異常が戻ってきます。
タスクを1件足したのに
task_countを足し忘れたら、画面の数字が嘘になります。
それでもやることはあります。 やるなら、条件が3つあります。
| 条件 | 中身 |
|---|---|
| 1. 先に測っている | EXPLAIN と実測で、その JOIN が本当に遅いことを確認した。パフォーマンスの回の型 |
| 2. 更新を1箇所に閉じ込めた | タスクを足す処理と task_count を足す処理を、トランザクションで1つにまとめた |
| 3. ズレを直せる手がある | d_tasks から数え直して上書きするSQLを用意してある |
3つ目は、必ず用意してください。 非正規化した列は、いつか必ずズレます。
UPDATE d_projects p
SET task_count = (SELECT COUNT(*) FROM d_tasks t WHERE t.project_id = p.id);
順番を間違えないでください。 「正規化する → 測る → 遅ければ崩す」 であって、「最初から崩しておく」ではありません。 崩れた設計を後から正規化するのは、正規化した設計を後から崩すよりずっと大変です。壊れたデータが既に入っているからです。
⑧ 番号との対応表
ここでようやく、教科書の言葉と突き合わせます。 やったことは全部済んでいるので、名前を付けるだけです。
| 正規形 | 教科書の言い方 | この記事でやったこと |
|---|---|---|
| 第1正規形(1NF) | 繰り返し項目を無くす | tags のカンマ区切りをやめて、1マス1値にした(直し1) |
| 第2正規形(2NF) | 部分関数従属を無くす | 中間テーブルに tag_name を持たせなかった(直し3) |
| 第3正規形(3NF) | 推移的関数従属を無くす | assignee_email と project_due を、担当者とプロジェクトの表に移した(直し2) |
判定のしかたも、日本語にしておきます。
| 番号 | 自分に問う言葉 |
|---|---|
| 1NF | 「1つのマスに、2つ以上の値が入っていないか?」 |
| 2NF | 「主キーが複数列のとき、その一部だけで決まる列が混ざっていないか?」 |
| 3NF | 「主キーではなく、同じ行の別の列で決まる列が混ざっていないか?」 |
2NF は、主キーが1列(
idの連番)なら自動的に満たされます。idの一部、というものが存在しないからです。気にする必要があるのは、中間テーブルのように主キーが2列以上あるときだけです。
やりすぎない
第4、第5正規形、そしてボイス・コッド正規形(BCNF)というものがあります。 この記事では扱いません。
理由を正直に書きます。
実務で作る表の大半は、第3正規形で止めて問題が出ません。
BCNF 以降が効いてくるのは、「候補キーが複数あって、それが重なっている」ような表です。そういう表を設計する場面は、少なくとも最初の数年ではほとんど来ません。
来たときに調べれば間に合います。 名前だけ知っておけば、「あ、これはもっと上の正規形の話だ」と気づけます。
逆に、次のことは毎回やる価値があります。
- 1つの表に、2種類の「もの」を入れていないか確かめる
- 同じ文字列が何行にも出てきていないか、
SELECT DISTINCTで見る NOT NULLを既定にする- 外部キーを張って、
ON DELETEを選ぶ
⑨ 設計は、先に紙でやる
ここまでの5枚を、いきなり CREATE TABLE から書き始めたわけではありません。 先に図を描きます。
ER図と呼ばれますが、道具は何でもかまいません。 紙とペンで十分です。 私は最初、罫線ノートに書きました。
描くのは3つだけです。
- 箱(=表)を書いて、中に列を並べる
- 箱と箱を線で結ぶ
- 線の両端に「1」か「多」を書く
[ d_members ]---1----多---[ d_tasks ]---多----1---[ d_projects ]
|
多
|
[ d_task_tags ]
|
多
|
[ d_tags ]
[スクリーンショット: 03_er図.png] 紙でもツールでもかまいません。上の5枚の関係を自分で描いた図を撮ってください。きれいである必要はありません。
この図を描く途中で、設計の間違いはほぼ全部見つかります。
| 図で気づけること | 意味 |
|---|---|
| 箱の中に、明らかに種類の違う列が並んでいる | その箱は2つに分けるべき |
| 線が「多対多」になっている | 中間テーブルが要る |
| どの箱にも繋がらない箱がある | 本当に要るのか考え直す |
| 1つの箱に線が5本以上入っている | 悪いとは限らないが、その箱を触る処理は必ず重くなる |
CREATE TABLEを書き始めてから設計を直すのは、コストが違います。 紙なら消しゴムで済むものが、データが入ってからだとマイグレーションとデータ移行になります。
Knex のマイグレーションに落とす
図が固まったら、ORMの回でやった形に落とします。 CREATE TABLE を手で打ちっぱなしにしないためです。
新しいマイグレーションを作ります。
exports.up = async function (knex) {
await knex.schema.createTable("members", (t) => {
t.increments("id");
t.string("name", 100).notNullable();
t.string("email", 255).notNullable().unique();
});
await knex.schema.createTable("projects", (t) => {
t.increments("id");
t.string("name", 100).notNullable().unique();
t.date("due_on").nullable();
});
await knex.schema.createTable("tags", (t) => {
t.increments("id");
t.string("name", 50).notNullable().unique();
});
await knex.schema.alterTable("tasks", (t) => {
t.integer("project_id").unsigned().nullable();
t.integer("member_id").unsigned().nullable();
t.foreign("project_id").references("projects.id").onDelete("SET NULL");
t.foreign("member_id").references("members.id").onDelete("RESTRICT");
});
await knex.schema.createTable("task_tags", (t) => {
t.integer("task_id").unsigned().notNullable();
t.integer("tag_id").unsigned().notNullable();
t.primary(["task_id", "tag_id"]);
t.foreign("task_id").references("tasks.id").onDelete("CASCADE");
t.foreign("tag_id").references("tags.id").onDelete("RESTRICT");
});
};
exports.down = async function (knex) {
await knex.schema.dropTableIfExists("task_tags");
await knex.schema.alterTable("tasks", (t) => {
t.dropForeign("project_id");
t.dropForeign("member_id");
t.dropColumn("project_id");
t.dropColumn("member_id");
});
await knex.schema.dropTableIfExists("tags");
await knex.schema.dropTableIfExists("projects");
await knex.schema.dropTableIfExists("members");
};
3つ、読みどころがあります。
| 書き方 | 意味 |
|---|---|
.unsigned() | t.increments("id") は符号なし整数を作ります。参照する側も unsigned にしないと型が合わず、外部キーが作れません(errno: 150) |
.onDelete("RESTRICT") を明示している | 既定でも RESTRICT ですが、書いておくと「考えた結果」だと分かります |
down の順番が up の逆 | 参照している側から消す。 逆にすると外部キーに拒否されます |
流します。
戻せることも、必ず確認してください。
すでに本番にデータが入っている表に、あとから外部キーを張ると失敗します。 参照先に存在しない値が既に入っていると、
ALTER TABLEが拒否されます(errno: 1452)。 これは外部キーが厳しいのではなく、「壊れたデータが既にある」ことが発覚しただけです。 先にSELECTで孤児を探して、片付けてから張ります。
⑩ NoSQL の場合はどうなるか
NoSQLの回を踏まえて、最後に1つ回収します。
MongoDB のようなドキュメント指向では、いまやった正規化を「やらない」のが普通です。 タスク1件が、こういう形になります。
{
_id: ObjectId("..."),
title: "ログイン画面を作る",
done: false,
assignee: { name: "佐藤", email: "sato@example.com" },
project: { name: "サイト移行", due_on: "2026-10-31" },
tags: ["ui", "api"]
}
この記事で「壊れる」と言った形そのものです。 担当者の情報もプロジェクトの情報も、タスクの中に埋め込まれています。
では、なぜ成立するのか。
| 論点 | リレーショナル | ドキュメント指向 |
|---|---|---|
| 重複はあるか | 無くす | ある。前提として受け入れる |
| 誰が食い違いを防ぐか | データベース(1箇所しか無い) | アプリ(全ドキュメントを書き換える) |
| 佐藤さんの改名 | 1行 UPDATE | 佐藤さんを含む全ドキュメントを書き換える |
| 1件を読む速さ | JOIN が要る | 1回で全部取れる |
トレードオフが逆になっているだけです。「正規化しなくていい」のではなく、「正規化しない代わりに、整合性の責任をアプリが持つ」です。
NoSQLの回で書いた「スキーマは消えずにアプリへ移る」と、まったく同じ構図です。移る先が、今度は「整合性」になっただけです。
そして、これがこの記事をリレーショナルで書いた理由でもあります。 正規化を知らないままドキュメント指向に行くと、「埋め込むしかない」ではなく「埋め込んでいいのかどうかが分からない」状態になります。 崩し方を知るには、まず組み立て方を知る必要があります。
つまずいたところ
| 症状 | 原因 |
|---|---|
ERROR 1364 Field 'title' doesn't have a default value | 正しい失敗。 ② の挿入時異常そのもの |
ERROR 1451 Cannot delete or update a parent row | 正しい失敗。 ON DELETE RESTRICT が効いている。子を先に消すか、ON DELETE を選び直す |
ERROR 1452 Cannot add or update a child row | 参照先に無いIDを入れようとした。 または外部キーを張る前から孤児が入っていた |
errno: 150 Foreign key constraint is incorrectly formed | 型が合っていない。 t.increments() は unsigned なので、参照する側も .unsigned() が要る。文字コード(COLLATE)違いでも出る |
Cannot drop index: needed in a foreign key constraint | 外部キーが索引を使っている。 先に外部キーを落としてから索引を落とす |
| 外部キーを張ったのに効かない | テーブルが MyISAM。 SHOW TABLE STATUS で Engine を確認。InnoDB でないと外部キーは無視される |
WHERE 列 <> 1 で件数が合わない | NULL の行が落ちている。 OR 列 IS NULL を足すか、そもそも NOT NULL にする |
WHERE 列 = NULL が0件でエラーも出ない | 正常。 IS NULL を使う |
GROUP_CONCAT の結果が途中で切れる | 既定で1024バイト。SET SESSION group_concat_max_len = 10000; |
UNIQUE にしたのに NULL が複数入る | 正常。 NULL 同士は「等しい」と判定されないので、UNIQUE は NULL を何個でも許す |
JOIN したら行が減った | INNER JOIN は、片方に無い行を落とします。 残したいなら LEFT JOIN |
LEFT JOIN して COUNT(*) が1になる | COUNT(*) は行を数えるので、繋がらなくても1。 COUNT(子の列) にする |
d_pk_uuid への INSERT が体感で遅い | 想定どおり。 ⑤ のランダム割り込みが起きている |
information_schema の数字が変わらない | ANALYZE TABLE を打つ。 それでも概算です |
cte_max_recursion_depth でエラー | SET SESSION を同じ接続で打つ。sudo mysql を抜けるとリセットされます |
後片づけ
この記事で作ったものは、すべて d_ で始まっています。
消す順番があります。 参照されている表は先に消せません。
中身を確認してから、まとめて消します。
USE appdb;
DROP TABLE IF EXISTS d_task_tags;
DROP TABLE IF EXISTS d_tasks;
DROP TABLE IF EXISTS d_tags;
DROP TABLE IF EXISTS d_projects;
DROP TABLE IF EXISTS d_members;
DROP TABLE IF EXISTS d_flat;
DROP TABLE IF EXISTS d_pk_int;
DROP TABLE IF EXISTS d_pk_uuid;
消えたことを確認します。
tasks・users・sessionsが残っていれば正常です。d_で始まる表が1枚も無ければ、元通りです。
⑨ の Knex マイグレーションを流していて、要らなくなった場合。
migrate:rollbackは「最後のバッチだけ」を戻します。--allを付けると全部消えますので、migrate:statusで戻る範囲を確認してから打ってください。
まとめ
- テーブル設計の話は、本当はもっと早い段階のもの。 ただし壊れたことがある人にしか意味が伝わらない
- 1つの表に全部入れると、同じ情報が何行にも重複する。 問題は無駄ではなく、重複が食い違うこと
- 更新時異常 —— 1箇所だけ直せてしまう構造だから、いつか食い違う。エラーは出ない
- 挿入時異常 —— 独立して存在できるものが他の表に同居していると、でっちあげないと登録できない
- 削除時異常 —— その情報の置き場所がそこしか無いと、1件消しただけで道連れになる。痕跡ごと消える
- 3つの異常は、全部「1つの表に2種類以上のものを入れた」ことから出ている
- 正規化は番号を当てはめる作業ではない。 「1マス1値にする」「そのもの自身の情報を、そのものの表に移す」の2つでほぼ終わる
- 第3正規形まででほぼ足りる。 BCNF以降は、必要になってから調べれば間に合う
- 2NF は主キーが1列なら自動的に満たされる。 気にするのは中間テーブルのような複合主キーのときだけ
- 外部キーは「繋ぐもの」ではなく「繋がっていない状態を作れなくするもの」
ON DELETEは3択。 迷ったらRESTRICT。CASCADEは削除時異常を自分で作ることがある- 主キーの連番とUUIDは、どちらにも代償がある。 代償は索引に出る
- InnoDB は主キーの順に並べて格納し、全ての索引が主キーの値を持つ。 主キーが太ると索引が全部太る
- UUIDを使うなら
BINARY(16)(UUID_TO_BIN(x, 1))か UUIDv7。 ランダム割り込みを避ける - NULL は「無い」ではない。 「未定」「該当なし」「不明」を1つの記号が兼ねてしまう
= NULLは常に0件。IS NULLだけが使える。<> 1は NULL の行を黙って落とす- 列はまず
NOT NULLで作る。 NULL を許すのは、NULLであることに意味があると説明できるときだけ NOT NULL DEFAULT ''に逃げない。 区別すべきものが2種類に増えるだけ- 正規化しすぎると
JOINが増えて遅くなる。 あえて重複を残す(非正規化)判断はある - 非正規化は「測ってから」。 正規化する → 測る → 遅ければ崩すの順番。最初から崩さない
- 非正規化した列は必ずズレる。 数え直して上書きするSQLを、先に用意しておく
- 設計は先に紙で描く。 箱と線と「1か多」の3つだけで、間違いのほとんどが見つかる
- できた設計はマイグレーションに落とす。
CREATE TABLEを手で打ちっぱなしにしない - NoSQL では正規化しないのが普通。 その代わり整合性の責任がアプリに移る。 トレードオフが逆になっているだけ
参考リンク
- roadmap.sh — Backend Developer
- roadmap.sh — SQL
- MySQL リファレンスマニュアル(公式・トップ) —
FOREIGN KEY・NULLの扱い・InnoDB の索引構造は、ここから自分の使っているバージョンを選んで読んでください - Knex.js — Schema Builder
- PostgreSQL 公式ドキュメント — 制約
次に読む
この記事は 【第5段階】バックエンドとデータベース の 18 / 29 本目です。
| 記事 | |
|---|---|
| ← 前の記事 | SQLiteはおもちゃではない — 用途で選ぶデータベース |
| → 次の記事 | 文字化けは壊れたのではない — 文字コードとUTF-8 |
上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。
全体の順路は はじめに にあります。
roadmap.sh のどこをやっているか
- 正規化(更新時・挿入時・削除時の異常を実際に起こす)
- 主キーの選び方・NULL・非正規化の判断
この記事は、上の項目を実際に手を動かして確かめる回です。

コメント