データベース

「流行っているから」ではなく、整合性・アクセスパターン・規模・運用体制で選ぶ。

DB設計は、いきなりCREATE TABLEを書くのではなく、3つの段階で考えると抜け漏れが減ります。概念設計では、業務に出てくる「もの」（ユーザー、商品）と「出来事」（注文、支払い）をエンティティとして洗い出し、関係をER図にします。論理設計では、それを表・列・キーに落とし、正規化で重複と矛盾をなくします。物理設計では、使うDB製品に合わせて型・インデックス・パーティションを決め、性能を確かめます。

どの段階でも、業務の人と同じ言葉を使うことが大事です。「顧客」と「会員」と「ユーザー」が同じものか違うものかを最初に決めておかないと、後で列やテーブルが重複します。最後に、主要な画面やAPIで実際に発行するSQLを書いてみて、無理なくデータが取れるか、インデックスで支えられるかを確認します。

6の「クエリで確かめる」を飛ばすと、正しいけれど遅い設計になりがち。

関係は3種類です。1対1はユーザーとプロフィールのように片方に1つだけ対応し、多くは同じ表にまとめるか、更新頻度や権限の違いで分けます。1対多はユーザーと注文のような最も多い関係で、外部キーは「多」の側（注文）に置きます。多対多は注文と商品のように互いに複数対応する関係で、直接は表せないので中間テーブル（注文明細）を作り、1対多を2つに分けます。

外部キー制約は整合性を守ってくれますが、大きな表では更新や削除のたびにチェックとロックが走るので、性能やマイグレーションを理由に張らない現場もあります。その場合は、アプリでの整合性チェックと定期的な不整合の検出をセットにします。

多対多は必ず中間テーブルに分解する。中間テーブル自体が「注文明細」のような意味のある表になることが多い。

正規化されていない表では、3つの異常が起きます。データを直すときに複数の行を直す必要があり、一部を直し忘れると矛盾する更新異常、まだ注文のない商品を登録できないような挿入異常、最後の注文を消すと商品の情報まで消える削除異常です。正規化は、これらを段階的になくす手順です。

考え方の土台は関数従属です。「Xが決まればYが1つに決まる」とき、YはXに関数従属している（X→Y）と言います。たとえば商品IDが決まれば商品名が決まるので、商品ID→商品名です。

実務では第3正規形までを目標にするのが一般的です。覚え方は「キーに、キー全体に、キー以外の何にも依存しない」。第1正規形は「キーに依存する（1つの値）」、第2正規形は「キー全体に依存する（一部ではなく）」、第3正規形は「キー以外の何にも依存しない」に対応します。

列を「商品1・商品2・商品3」と増やすのも第1正規形違反。4つ目が来た瞬間に破綻する。

複合主キーの表で、その一部だけで決まる列がないかを探す。

「主キーではない列」から決まる列がないかを探す。

正規化すると、データを取り出すときにJOINが増えます。読み取りが非常に多く、JOINや集計が性能のボトルネックになると分かった場合に限り、あえて重複を持つのが非正規化です。非正規化したら、元のデータとのずれが起きないよう、更新の仕組みを決めます。

この区別は面接でもよく聞かれます。「正規化すれば価格は商品表だけに持つべき」と答えると、過去の注文の金額が変わってしまう設計になります。何が「現在の値」で、何が「出来事が起きた時点の値」なのかを見分けることが、実務の設計では正規化の知識以上に大事です。

ユーザー（users）は複数の住所（addresses）と複数の注文（orders）を持ちます。注文と商品（products）は多対多なので、注文明細（order_items）を中間テーブルにします。商品はカテゴリ（categories）に属し、カテゴリは親カテゴリを持つ階層です。

CREATE TABLE users (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email       VARCHAR(255) NOT NULL,
  name        VARCHAR(100) NOT NULL,
  created_at  DATETIME(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  UNIQUE KEY uq_users_email (email)
) DEFAULT CHARSET = utf8mb4;

CREATE TABLE orders (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      BIGINT UNSIGNED NOT NULL,
  status       VARCHAR(20)  NOT NULL,          -- pending / paid / shipped / canceled
  ship_address VARCHAR(500) NOT NULL,          -- 注文時点の住所を保存
  ordered_at   DATETIME(6)  NOT NULL,
  KEY idx_orders_user_ordered (user_id, ordered_at),  -- 「ある人の最近の注文」用
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id    BIGINT UNSIGNED NOT NULL,
  product_id  BIGINT UNSIGNED NOT NULL,
  quantity    INT NOT NULL CHECK (quantity > 0),
  unit_price  INT NOT NULL,                    -- 注文時点の販売単価（円）
  UNIQUE KEY uq_items (order_id, product_id),
  CONSTRAINT fk_items_order   FOREIGN KEY (order_id)   REFERENCES orders(id),
  CONSTRAINT fk_items_product FOREIGN KEY (product_id) REFERENCES products(id)
);

面接のシステム設計でも、最初にこのくらいの粒度のER図を描けると議論が進めやすい。

InnoDBでは主キーの順にデータそのものが並ぶので、主キーの選び方が書き込み性能に直結します。また、セカンダリインデックスはすべて主キーの値を持つので、主キーが大きいとすべてのインデックスが大きくなります。外部に見せるIDと内部のIDを分け、URLには推測されにくいID（UUIDなど）を、内部の結合には連番を使う、という使い分けもよく行われます。

UUIDを使うならv7。ランダムなv4を主キーにして書き込みが遅くなる事例は多い。

NULLは「値がない・不明」を表します。NULLとの比較（= NULL）は真でも偽でもなく「不明」になるので、IS NULLで判定します。WHERE col <> 'A' はNULLの行を含まず、集計関数のCOUNT(col)はNULLを数えない、といった落とし穴があります。不要なNULLは事故の元なので、基本はNOT NULLにし、本当に「ない」ことに意味がある列だけNULLを許します。

削除の設計には、行を本当に消す物理削除、削除日時などの印をつけて残す論理削除、別の表やストレージに移してから消すアーカイブがあります。論理削除は手軽ですが、すべてのクエリに「削除されていない」条件が必要になり、一意制約（同じメールアドレスで再登録）が効かなくなり、表が太り続けるという問題があります。

個人情報については、退会後に「消してほしい」という要求や法令に応えられることも必要です。論理削除のまま残し続けると、それに応えられません。退会時に個人を特定できる列を消す・匿名化する処理と、バックアップやログに残る分の扱いも決めておきます。

「全部論理削除」にすると後で困る。データの種類ごとに方式を決める。

共通するのは「DBに守らせられることを、アプリやJSONに押し付けている」こと。

1つに決めず、「普段は共有、大口だけ分離」と混在させる設計も多い。

DynamoDBのようなKVS・ドキュメント型のDBでは、JOINがなく、キー以外の条件で効率よく検索できません。そのため、設計の最初にアクセスパターン（どんな画面で、どの条件で、何件を、どの順番で読むか）を全部書き出し、それぞれが1回のQueryで済むように、パーティションキー（PK）とソートキー（SK）を設計します。読み方に合わせて、同じデータを複数の形で持つこともためらいません。

アクセスパターンが固まっていて大量のアクセスがある場合には非常に強い一方、要件が頻繁に変わる初期のサービスや、柔軟な検索・集計が必要な業務データでは、RDBの方が向いていることが多いです。

「後から検索条件を増やしたい」がよくある要件なら、まずRDBを検討する。

具体的な手順は、インフラ設計の章のExpand / Contractも参照してください。

• インデックスなしでは全行を順に読む（フルスキャン、O(n)）

• B+Tree インデックスは木を根から辿るため O(log n)。100万行でも数回のページ読み込みで目的の行に届く

• リーフノード同士が連結されているので、範囲検索（BETWEEN、>、ORDER BY）にも強い

• InnoDB はデータ自体が主キーの B+Tree（クラスタインデックス）。セカンダリインデックスは主キー値を持ち、そこから本体を引く

ルートで「50以上100未満」、次の段で「65以上80未満」と絞り込み、リーフで行に届く。全件を読む必要がない。

ディスクやメモリからデータを読むときは、1件ずつではなく「ページ」という固まり単位（InnoDBは16KB、PostgreSQLは8KB）で読みます。だから検索を速くするには、比較の回数よりも読むページの数を減らすことが大事です。B-Treeは、1つのノード（＝1ページ）に多くのキーを並べて持たせ、枝分かれを大きくすることで木を浅くし、読むページ数を少なくした木構造です。

B+TreeはB-Treeの改良版で、違いは2つです。①行データ（または行の場所）を一番下のリーフにだけ置き、上の内部ノードは道案内用のキーだけを持つ。②リーフ同士を左右に鎖でつなぐ。①により内部ノードにより多くのキーが入って木がさらに浅くなり、②により範囲検索や並べ替えが「最初のリーフを探して、あとは横にたどるだけ」で済みます。MySQL（InnoDB）もPostgreSQLも、通常のインデックスはこのB+Tree系です。

補足：書き込みが非常に多いシステムでは、まずメモリに溜めてまとめてディスクに書くLSM-Tree（RocksDB、Cassandraなど）も使われます。書き込みは速いが、読み取りや整理（コンパクション）に負荷がかかる、という逆のトレードオフです。

内部ノードが軽いほど木が浅くなり、リーフがつながっているほど範囲検索が速い。

B+Tree は1ノード（1ページ、InnoDBは16KB）に数百のキーを持てるため、木がとても浅い。上の方のノードはメモリ（バッファプール）に載るので、実際のディスク読み込みはさらに少ない。

インデックスが (user_id, status, created_at) の場合：

先頭の列から条件がそろっているほど、読む範囲が連続して小さくなる。

1. slow query log やAPM、Performance Insights で遅いクエリを特定

2. EXPLAIN（PostgreSQLは EXPLAIN ANALYZE）で実行計画を確認

3. type（ALL はフルスキャン）、rows（見積もり行数）、Extra（Using filesort、Using temporary）に注目

4. インデックス追加、クエリ書き換え、N+1解消、キャッシュで改善

5. 改善前後の数値を比べて効果を確認

第12章「パフォーマンスチューニング」：クエリとインデックスのチューニングを図で詳しく

トランザクションは、BEGINで始めてCOMMITで確定し、途中で問題があればROLLBACKで開始前の状態に戻します。多くのDBはオートコミットが既定で、BEGINを書かなければSQL1文ごとに自動でCOMMITされます。複数の更新をまとめたいときは、明示的にトランザクションで囲む必要があります。

MVCC（多版型同時実行制御）は、行を書き換えるときに古い版を消さずに残す仕組みです。読む側は自分が始まった時点の版（スナップショット）を見るので、書き込み中の行でも待たずに読めます。その代わり古い版が溜まるので、定期的な掃除（InnoDBのパージ、PostgreSQLのVACUUM）が必要です。何時間も開きっぱなしのトランザクションがあると、その時点以降の古い版を消せずに肥大化し、性能が落ちます。

WAL（Write-Ahead Logging、先行書き込みログ）は、データ本体を書き換える前に、変更内容をログへ順番に追記してディスクに確定させる仕組みです。ログへの追記は順番に書くだけなので速く、COMMITはログが確定した時点で成功を返せます。データ本体への反映は後でまとめて行い、途中でクラッシュしても再起動時にログを読み直せば復元できます。このログはレプリケーション（PostgreSQLはWAL、MySQLはbinlog）や、任意の時刻に戻すPITRにも使われます。

途中で失敗しても「片方だけ反映」が起きないのが原子性。

読み取りがロック待ちをしないので、読み取りの多いWebサービスと相性が良い。

COMMITの速さ、クラッシュからの復旧、レプリケーション、PITRは全部このログが支えている。

デフォルトは MySQL（InnoDB）が Repeatable Read、PostgreSQL が Read Committed。

ロストアップデートは分離レベルだけでは防げないことが多い。UPDATE stock = stock - 1 のようにDB側で計算する、SELECT FOR UPDATE、楽観ロックで防ぐ。

読み取りと書き込みの間に他の更新が入り込むのが原因。DB側で計算するかロックで防ぐ。

第3章「コンピュータの基礎（CPU・メモリ・OS）」：競合状態とロックの基本を図で詳しく

第8章「ストレージ」：ストレージの性能（IOPS）を図で詳しく

レプリケーションは、プライマリ（書き込みを受ける DB）の変更を、レプリカ（複製）に送り続ける仕組みです。読み取りの負荷をレプリカに逃がすことと、プライマリが壊れたときにレプリカを昇格させて動き続けることの2つに使います。

方式の違いは「アプリにコミットの完了を返す前に、レプリカへの到達をどこまで待つか」です。待つほどフェイルオーバーでデータを失いにくくなりますが、書き込みは遅くなります。障害対応では「切り替えで直前の書き込みが消えるか（RPO：失ってよいデータの範囲）」、日常では「更新したのに古い値が見える（レプリケーション遅延）」の原因として効いてきます。

下の「ブラウザで試す」の図は非同期の場合です。プライマリでコミットが返ったあとで変更（MySQL の binlog、PostgreSQL の WAL）がレプリカに送られるため、更新した直後にレプリカを読むと古い値が返るところと、プライマリの障害でレプリカを昇格させたときに、まだ届いていなかった変更が失われうるところに注目してください。最後に、アプリが新しいプライマリへ接続し直す流れも見られます。

非同期レプリケーションでは、書き込みの成功とレプリカへの反映にずれがある。

DBが重いとき、原因はDBの外、つまりアプリの使い方にあることがほとんどです。クエリの回数が多い（N+1）、1本のクエリが大量の行を読む、接続が多すぎる、同じ行に書き込みが集中する、トランザクションが長くてロックを持ち続ける、の5つの形に分けて考えると、打ち手が見えます。

打ち手は「DBに来る量を減らす（キャッシュ、まとめて取得、非同期化）」「1回を軽くする（インデックス、必要な行と列だけ）」「分散する（リードレプリカ、書き込みの分割）」の順に考えます。スケールアップは即効性がありますが、原因が残るので、緊急の緩和として使い、根本の修正とセットにします。

DBのチューニングは、アプリのコードとセットで見る。

Redis（AWS では ElastiCache・MemoryDB、Valkey も同じ使い方）は、キャッシュ・セッション・レート制限・キューに使われ、止まると全体に響きやすい部品です。SRE としては、シングルスレッドであること・メモリがすべてであること・どこまで失ってよいか（永続化と冗長化）を押さえます。

障害対応の演習：全部の API がときどき止まる（KEYS）・ログインできない（Redis の OOM）