📚

【データベース設計】身近な例で理解する基本概念

に公開
デザイン
Database
index
transaction
normalization
tech

はじめに

データベース設計は難しそうに見えますが、実は私たちの身の回りにある身近なものと同じ考え方です。この記事では、図書館のある例を使って、データベース設計の基本概念を説明します。

この記事で学べること

  • 正規化：なぜデータを整理するのか？
  • インデックス：素早い検索の仕組み
  • トランザクション：データの整合性を保つ方法
  • データ型：適切な保存方法の選択
  • 実践的な設計のポイント

対象読者

  • データベース設計をこれから学ぶ方
  • 概念は知っているが、具体的なイメージが掴めない方
  • 身近な例で理解したい方

1. データベース設計の基礎理論

正規化：なぜデータを整理するのか？

図書館の例で理解する正規化

想像してください。あなたが図書館の司書だとします。

正規化前の問題（第 1 正規形違反）:

本の ID 本のタイトル 著者
001 データベース入門 田中太郎, 山田花子
002 料理の基本 佐藤次郎

この表の問題点：

  • 「著者」列に複数の著者がカンマ区切りで入っている
  • 特定の著者の本を検索するのが困難
  • 山田花子の名前を修正したい場合、全ての本を調べる必要がある

第 1 正規形（1NF）: 一つのセルには一つの値

この問題を解決するため、「一つのセルには一つの値しか入れない」ルールを適用：

本の ID 本のタイトル 著者
001 データベース入門 田中太郎
001 データベース入門 山田花子
002 料理の基本 佐藤次郎

でも、新たな問題が発生：
「データベース入門」というタイトルが重複してしまいました。

第 2 正規形（2NF）: 重複をなくすために分離

本の情報と著者の情報を別々に管理します：

本テーブル:

本の ID 本のタイトル
001 データベース入門
002 料理の基本

著者テーブル:

著者 ID 著者名
A01 田中太郎
A02 山田花子
A03 佐藤次郎

本-著者関係テーブル:

本の ID 著者 ID
001 A01
001 A02
002 A03

これで重複がなくなり、データの管理が整理されました！

第 3 正規形（3NF）: 間接的な依存関係の解消

第 2 正規形でも、まだ問題が残ることがあります。例えば、著者の所属出版社を追加する場合を考えてみましょう。

第 2 正規形の問題（間接的な依存）:

著者 ID 著者名 出版社 ID 出版社名 出版社住所
A01 田中太郎 P01 技術出版社 東京都...
A02 山田花子 P01 技術出版社 東京都...
A03 佐藤次郎 P02 文芸出版 大阪府...

この設計の問題点：

  • 「技術出版社」の情報が著者の数だけ重複している
  • 「技術出版社」の住所が変わったら、全ての該当著者のレコードを更新する必要がある
  • 「出版社名」と「出版社住所」は「出版社 ID」に依存しているが、「著者 ID」には直接関係ない

これが「間接的な依存関係」の問題です。

第 3 正規形での分離:

著者テーブル:

著者 ID 著者名 出版社 ID
A01 田中太郎 P01

出版社テーブル:

出版社 ID 出版社名 出版社住所
P01 技術出版社 東京都...
P02 文芸出版 大阪府...

これで間接的な依存関係が解消され、出版社情報の重複もなくなりました！出版社の住所が変わっても、出版社テーブルの 1 つのレコードを更新するだけで済みます。

インデックス：辞書の索引のようなもの

図書館で本を探すとき

図書館で「データベース入門」という本を探すとき、あなたはどうしますか？

  1. 書架の 1 番目から順番に見ていく？（テーブルスキャン）
  2. コンピューター分野の書架に行って、そこから「データベース」の棚を探す？（インデックス使用）

当然、2 番の方が早いですよね。これがインデックスの概念です。

データベースでのインデックス

図書館の本管理システムの例:

図書館に 10,000 冊の本があるとして、「田中太郎」さんが書いた本を探す場合：

インデックスなし:

  • 1 冊目から順番にチェック
  • 最悪の場合、10,000 冊全てをチェック

著者名の目録（インデックス）を作成した場合:

図書館で「著者名の五十音順カード目録」を別途作成します：

カード目録（著者名でソート済み）
あ行：安田花子 → 書架3-A-15, 書架7-B-03
か行：川田次郎 → 書架1-B-08
...
た行：田中太郎 → 書架2-C-22, 書架5-B-10, 書架1-A-05
     田村一郎 → 書架4-A-03
...

重要なポイント：目録には「場所」しか書かれていない

この目録からは以下のことが分かります：

  • 田中太郎の本は 3 冊ある
  • それぞれ書架 2-C-22、書架 5-B-10、書架 1-A-05 にある

でも、以下のことは分かりません：

  • それぞれの本のタイトルは何か
  • いつ出版されたか
  • どのジャンルか

検索の流れ:

  1. 本棚は触らず、まず五十音順の目録を使う
  2. 目録の中央から開始して「田中太郎」と比較
  3. 比較結果に基づいて左半分または右半分に移動
  4. 数回の比較で目録から「田中太郎 → 書架 2-C-22, 書架 5-B-10, 書架 1-A-05」を発見
  5. それぞれの書架に行って実際の本を確認

結果：10,000 冊あっても、約 13〜14 回の比較で目的の著者の本の場所を発見

インデックスの正体: 元のデータとは別に作られた「場所案内の目録」

単体インデックス vs 複合インデックス

単体インデックス（1 つの項目だけ）:

図書館では項目別に専用の目録を作ります：

  • 著者名目録: 「田中太郎の本はどこ？」→ 3 箇所の書架を案内
  • 出版年目録: 「2020 年の本はどこ？」→ 関連する書架を案内
  • ジャンル目録: 「コンピューター系の本はどこ？」→ 関連する書架を案内

複合インデックス（複数の項目を組み合わせ）:

より具体的な検索のために、組み合わせ目録も作ります：

著者名＋出版年の組み合わせ目録
田中太郎-2018年 → 書架2-C-22
田中太郎-2020年 → 書架5-B-10
田中太郎-2022年 → 書架1-A-05

なぜ組み合わせ目録が必要？

「田中太郎の 2020 年の本」を探す場合：

単体インデックスのみの場合:

  1. 著者名目録で田中太郎の本の場所を確認（3 箇所）
  2. それぞれの書架に行って出版年をチェック
  3. 2020 年の本を見つける

複合インデックスがある場合:

  1. 著者名＋出版年目録で直接「田中太郎-2020 年 → 書架 5-B-10」を発見
  2. 書架 5-B-10 に直行

使い分けの考え方:

  • よく単独で検索される項目 → 単体インデックス
  • よく組み合わせて検索される項目 → 複合インデックス

データベースでも同様に、検索パターンに応じて適切なインデックスを作成します。

トランザクション：図書館の本貸出処理

図書館で本を借りる処理を考えてみましょう

田中太郎さんが「データベース入門」を借りる場合：

  1. 本テーブル：「データベース入門」の状態を「貸出可能」→「貸出中」に変更
  2. 利用者テーブル：田中太郎さんの「現在の貸出冊数」を増やす
  3. 貸出記録テーブル：新しい貸出記録を追加

問題：途中でシステムが止まったら？

もし 1 番目の処理だけ成功して、2・3 番目が失敗したら：

  • 本の状態は「貸出中」になった
  • でも田中さんの貸出冊数は増えていない
  • 貸出記録も残っていない
  • 誰が借りたか分からない！

ACID 特性による解決

Atomicity（原子性）:
「全部成功」か「全部失敗」かのどちらか
→ 途中で止まった場合は、自動的に元の状態に戻る

Consistency（一貫性）:
データベース全体の整合性を保つ
→ 貸出中の本の数と貸出記録の数は必ず一致する

Isolation（独立性）:
他の処理の影響を受けない
→ 同時に他の人が同じ本を借りようとしても影響しない

Durability（永続性）:
一度確定したデータは失われない
→ 貸出完了後にシステムが止まっても、貸出記録は残る

具体的な例

人気本の予約競合:

「データベース入門」（残り 1 冊）を、田中さんと山田さんが同時に借りようとした場合：

問題のある処理:

  1. システム A: 「この本、貸出可能ですね」（田中さん）
  2. システム B: 「この本、貸出可能ですね」（山田さん）
  3. システム A: 「田中さんの貸出を登録しました」
  4. システム B: 「山田さんの貸出を登録しました」
    → 1 冊しかないのに 2 人が借りられた！

トランザクション処理:

  1. システム A: 本をロック → 確認 → 貸出登録 → ロック解除
  2. システム B: 本がロックされているので待機 → ロック解除後に確認 → 「貸出中です」

図書館システムでのトランザクションとロック

実際のデータベースではどのようにトランザクションが動作するのか、図書館システムの例で詳しく見てみましょう。

図書館のデータベース構造:

books（本テーブル）
book_id | title           | status
B001    | データベース入門  | available
B002    | 料理の基本       | available

users（利用者テーブル）
user_id | name    | current_rentals
U001    | 田中太郎 | 2
U002    | 山田花子 | 1

rentals（貸出記録テーブル）
rental_id | book_id | user_id | rental_date | return_date

田中太郎さんが「データベース入門」を借りる処理:

BEGIN TRANSACTION;
-- 1. 本の状態を「貸出中」に変更
UPDATE books SET status = 'borrowed' WHERE book_id = 'B001';
-- 2. 利用者の貸出冊数を増やす
UPDATE users SET current_rentals = current_rentals + 1 WHERE user_id = 'U001';
-- 3. 貸出記録を追加
INSERT INTO rentals (book_id, user_id, rental_date)
VALUES ('B001', 'U001', NOW());
COMMIT; -- 全て成功したら確定

ロックの種類と範囲

データベースは処理の安全性を保つために、様々な単位でデータをロックします：

ロック単位（細かい → 大きい順）:

1. 行ロック（Row Lock）

  • 特定の本（B001）のみをロック
  • 他の本（B002, B003）は同時に貸出可能
  • 最も効率的だが、管理コストが高い

2. ページロック（Page Lock）

  • B001〜B050 の本データ（1 つのデータページ）をまとめてロック
  • 田中さんの貸出中は、同じページの他の本も一時的に貸出不可
  • 行ロックと テーブルロックの中間

3. テーブルロック（Table Lock）

  • 本テーブル全体をロック
  • 1 人の貸出処理中は、全ての本の貸出が待機
  • 管理は簡単だが、パフォーマンスが悪い

4. データベースロック（Database Lock）

  • 図書館システム全体をロック
  • システムメンテナンス時などに使用

読み書きによる分類:

共有ロック（Shared Lock / 読み取りロック）:

  • 利用者が本の詳細情報を確認中
  • 他の人も同じ本の情報確認は可能
  • でも貸出など本の状態を変更する処理は待機

排他ロック（Exclusive Lock / 書き込みロック）:

  • 田中さんが本の貸出処理中
  • 他の人は同じ本の確認も貸出もできない
  • 処理完了まで全て待機

ロックは「単位」×「読み書き」の組み合わせで決まる:

  • 行ロック × 共有ロック = 特定の本だけ読み取り専用
  • 行ロック × 排他ロック = 特定の本だけ書き込み専用
  • テーブルロック × 共有ロック = 本テーブル全体を読み取り専用
  • テーブルロック × 排他ロック = 本テーブル全体を書き込み専用

実際の動作例（行ロック使用の場合）:

時刻 10:00:01 田中さんが貸出開始 → B001を行×排他ロック
時刻 10:00:02 山田さんがB002を貸出開始 → 問題なし（異なる本）
時刻 10:00:03 佐藤さんがB001の詳細確認 → 待機（B001は排他ロック中）
時刻 10:00:05 田中さんの貸出完了 → B001のロック解除
時刻 10:00:06 佐藤さんの詳細確認が実行される（B001を行×共有ロック）

ロック選択のトレードオフ:

  • 細かいロック（行ロック）: 高いパフォーマンス、複雑な管理
  • 大きなロック（テーブルロック）: 簡単な管理、低いパフォーマンス

トランザクションは、重要なデータを扱うシステムには欠かせない機能です。一見複雑に感じますが、「全部成功するか、全部なかったことにするか」という単純な考え方が根本にあります。

データ型：適材適所の重要性

引っ越し用の箱の例

引っ越しで荷物を梱包するとき：

間違った選択:

  • 本を巨大な箱に 1 冊だけ入れる（無駄にスペースを消費）
  • 重い本を薄い袋に入れる（破れる可能性）
  • 洋服を本用の小さい箱に入れる（入らない）

正しい選択:

  • 本は本用の頑丈な箱に適量
  • 洋服は大きくて軽い袋に
  • 割れ物は専用の箱にクッション材と一緒に

データベースでの適切なデータ型選択

人の年齢を保存する場合:

-- 無駄: 年齢に大きすぎる型
age BIGINT  -- -9京〜+9京まで格納可能（無駄）

-- 適切: 年齢に適したサイズ
age TINYINT UNSIGNED  -- 0〜255で十分

メールアドレスを保存する場合:

-- 無駄: 制限のないサイズ
email TEXT  -- 最大65,535文字（メールアドレスには大きすぎ）

-- 適切: 標準的なメールアドレス長
email VARCHAR(320)  -- RFC準拠の最大長

これらの選択により：

  • ストレージ容量の節約
  • 処理速度の向上
  • データの整合性確保

が実現できます。

外部キー制約：図書館の貸出チェック機能

親テーブルと子テーブルの関係

図書館のデータベース構造を見てみましょう：

親テーブル（参照される側）:

books（本テーブル）- 親テーブル
book_id | title           | author
B001    | データベース入門  | 田中太郎
B002    | 料理の基本       | 佐藤次郎

users（利用者テーブル）- 親テーブル
user_id | name    | email
U001    | 山田花子 | yamada@example.com
U002    | 鈴木一郎 | suzuki@example.com

子テーブル（参照する側）:

rentals（貸出記録テーブル）- 子テーブル
rental_id | book_id | user_id | rental_date
R001      | B001    | U001    | 2024-01-15
R002      | B002    | U002    | 2024-01-16

外部キー制約の仕組み

子テーブルのルール:

  • book_idは必ず親テーブル（books）に存在する値でないといけない
  • user_idは必ず親テーブル（users）に存在する値でないといけない

問題のある状況を自動で防ぐ:

-- これはエラーになる（存在しない本を借りようとしている）
INSERT INTO rentals (rental_id, book_id, user_id, rental_date)
VALUES ('R003', 'B999', 'U001', '2024-01-17');
-- エラー: book_id 'B999'はbooksテーブルに存在しない

-- これもエラーになる（存在しない利用者が借りようとしている）
INSERT INTO rentals (rental_id, book_id, user_id, rental_date)
VALUES ('R003', 'B001', 'U999', '2024-01-17');
-- エラー: user_id 'U999'はusersテーブルに存在しない

削除時の保護:

-- これはエラーになる（貸出中の本を削除しようとしている）
DELETE FROM books WHERE book_id = 'B001';
-- エラー: この本は貸出記録テーブルで参照されている

-- 正しい手順：
-- 1. まず貸出記録を削除（本を返却）
DELETE FROM rentals WHERE book_id = 'B001';
-- 2. その後で本を削除
DELETE FROM books WHERE book_id = 'B001';

-- 外部キー制約の設定
CREATE TABLE rentals (
    rental_id INT PRIMARY KEY,
    book_id INT,
    user_id INT,
    rental_date DATE,
    FOREIGN KEY (book_id) REFERENCES books(book_id),  -- booksが親、rentalsが子
    FOREIGN KEY (user_id) REFERENCES users(user_id)   -- usersが親、rentalsが子
);

メリット:

  • データの矛盾を自動的に防ぐ
  • 「幽霊データ」（実在しないものへの参照）を根絶
  • システムの信頼性向上

制約：図書館のルールブック

システムが自動で守るルール

図書館には様々なルールがあります：

貸出ルール:

  • 一般利用者：最大 5 冊まで
  • 学生：最大 3 冊まで
  • 貸出期間：最大 30 日
  • 同じ本の重複貸出は不可

データベース制約で実現:

-- 制約の設定例
CREATE TABLE rentals (
    rental_id INT PRIMARY KEY,
    user_id INT,
    book_id INT,
    rental_date DATE NOT NULL,
    return_date DATE,
    -- 返却日は貸出日より後でないといけない
    CHECK (return_date IS NULL OR return_date > rental_date)
);

-- 利用者テーブルの制約
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(320) UNIQUE,  -- 同じメールアドレスは登録不可
    age INT CHECK (age >= 0),   -- 年齢は0歳以上
    membership_type ENUM('general', 'student', 'senior'),
    -- 学生は6歳以上30歳以下
    CHECK (membership_type != 'student' OR (age >= 6 AND age <= 30))
);

実際のチェック動作:

-- これはエラーになる（返却日が貸出日より前）
INSERT INTO rentals (rental_id, rental_date, return_date)
VALUES (1, '2024-01-15', '2024-01-10');
-- エラー: CHECK制約違反

-- これもエラーになる（年齢がマイナス）
INSERT INTO users (user_id, name, age)
VALUES (1, '田中太郎', -5);
-- エラー: CHECK制約違反

-- これもエラーになる（同じメールアドレス）
INSERT INTO users (user_id, name, email) VALUES (1, '田中', 'tanaka@example.com');
INSERT INTO users (user_id, name, email) VALUES (2, '山田', 'tanaka@example.com');
-- エラー: UNIQUE制約違反

自動チェックの種類:

  • NOT NULL 制約: 必須項目の入力漏れを防ぐ
  • CHECK 制約: 値の範囲や条件を自動チェック
  • UNIQUE 制約: 重複を防ぐ（メールアドレス、会員番号など）
  • FOREIGN KEY 制約: 存在しないデータへの参照を防ぐ

マイグレーション：図書館のシステム改修

システム改修時の安全な引っ越し作業

図書館が新しいシステムを導入する時のことを考えてみましょう：

例：電子書籍対応への改修

従来の図書館システムは紙の本だけでしたが、電子書籍も管理できるようにシステムを改修する必要があります。

危険な方法（やってはいけない）:

  1. 土曜日の夜に全システム停止
  2. 新しいシステムに一気に切り替え
  3. 月曜日の開館までに完了を目指す
    → 失敗したら図書館が開けない！

安全なマイグレーション手順:

段階的な改修（ゼロダウンタイム）

Step 1: 新しい項目を「オプション」として追加

-- 既存のテーブルに新しいカラムを追加（NULL許可）
ALTER TABLE books ADD COLUMN ebook_url VARCHAR(500) NULL;
ALTER TABLE books ADD COLUMN ebook_format VARCHAR(50) NULL;

この段階では：

  • 既存の紙の本データはそのまま動作
  • 新しい電子書籍項目は空欄でも OK
  • システムは正常稼働を継続

Step 2: アプリケーションを新機能対応に更新

  • 電子書籍 URL を入力できる画面を追加
  • でも、まだ入力は任意（必須ではない）

Step 3: データの移行作業

-- 既存の電子書籍データがあれば移行
UPDATE books
SET ebook_url = 'https://example.com/ebooks/' + isbn,
    ebook_format = 'PDF'
WHERE category = 'ebook';

Step 4: 新機能を「必須」に変更

-- 電子書籍項目を必須に変更（データ移行完了後）
ALTER TABLE books MODIFY COLUMN ebook_format VARCHAR(50) NOT NULL;

Step 5: 古いシステムの除去
不要になった古いカラムやテーブルを削除

実際の安全対策

1. バックアップは絶対

  • 「改修前の完全バックアップ」を必ず作成
  • 失敗した時はこれで元に戻す

2. ロールバック計画

  • 「新システムで問題が発生した場合の戻し方」を事前に用意
  • 手順書を作成して、実際にテスト環境で練習

3. 段階的テスト

-- 移行前のデータ件数確認
SELECT COUNT(*) FROM books;  -- 結果：50,000件

-- 移行後の整合性確認
SELECT COUNT(*) FROM books WHERE ebook_url IS NULL AND category = '電子書籍';
-- 結果：0件なら正常（電子書籍なのにURLがないデータがない）

4. 本番環境での慎重な実行

  • 利用者の少ない時間帯（深夜・早朝）に実行
  • 段階ごとに動作確認
  • 問題があれば即座に前の段階に戻す

よくある失敗と対策

失敗例 1: 一気に変更してシステム停止

  • 対策：小さな変更を積み重ねる

失敗例 2: テスト不足でデータ消失

  • 対策：本番前に必ずテスト環境で全手順を実行

失敗例 3: ロールバック手順が不明確

  • 対策：「戻し方」も含めて手順書作成

成功の秘訣:

  • 小さく始める: 一度に大きな変更をしない
  • 確認しながら進む: 各段階で動作確認
  • いつでも戻せる: 問題があればすぐ元に戻せる準備

図書館の例でいえば：

  • 一部の本だけ先に新システムで管理開始
  • 問題なければ徐々に対象を拡大
  • 利用者サービスは常に継続

2. 高可用性とスケーリングの考え方

高可用性（High Availability）とは？
システムが常に利用可能な状態を保つこと。図書館でいえば「年中無休で開館」のようなイメージです。

スケーリングとは？
システムの処理能力を増強すること。図書館でいえば「利用者が増えても待たせない」ための工夫です。

レプリケーション：図書館の分館システム

本館と分館の関係

図書館には本館と分館があります：

本館（マスター DB）:

  • 新しい本の登録
  • 本の除籍処理
  • カタログの更新

分館（スレーブ DB）:

  • 利用者への貸出サービス
  • 本の検索サービス
  • 閲覧サービス

メリット:

  • 利用者は近くの分館で本を探せる（高速な読み取り）
  • 本館が忙しくても、分館でサービス継続
  • 本館に障害があっても、分館で基本サービス提供

本館に障害が起きた時の制限:

  • 分館では既存の本の「閲覧・検索・貸出」は継続可能
  • でも「新しい本の登録」「本の情報変更」「除籍処理」は不可
  • つまり、読み取り専用サービスのみ提供（書き込みは本館復旧待ち）

課題:

  • 新刊情報が分館に届くまで少し時間がかかる（レプリケーション遅延）
  • 分館では新しい本の登録はできない（書き込み権限がない）

シャーディング：データを分割して管理

水平シャーディング（地域別図書館システム）

全国の図書館システムで「同じ種類のデータを地域で分割」する例：

地域別分割（行の分割）:

東日本DB：本テーブル（東京図書館、横浜図書館、仙台図書館の蔵書）
西日本DB：本テーブル（大阪図書館、京都図書館、福岡図書館の蔵書）
中部DB：本テーブル（名古屋図書館、金沢図書館、静岡図書館の蔵書）

各 DB には同じ構造の本テーブルがあるが、所蔵館の地域でデータが分かれている。

垂直シャーディング（機能別分割）

図書館システムを「機能別に分割」する例：

機能別分割（列・テーブルの分割）:

蔵書管理DB：本テーブル、著者テーブル、出版社テーブル
利用者管理DB：利用者テーブル、会員カードテーブル
貸出管理DB：貸出記録テーブル、予約テーブル、延滞テーブル

各 DB には異なる種類のテーブルが格納されている。

水平シャーディングのメリット・デメリット:

  • ✅ 地域の利用者は近くの DB にアクセス（高速）
  • ✅ データ量に応じて柔軟に拡張可能
  • ❌ 地域をまたぐ検索が複雑
  • ❌ データの再配置が困難

垂直シャーディングのメリット・デメリット:

  • ✅ 機能ごとに最適化しやすい
  • ✅ 開発チームを機能別に分けやすい
  • ❌ 機能をまたぐ処理（本と利用者の結合など）が複雑
  • ❌ トランザクション管理が困難

パーティショニング：年代別書庫システム

データを効率的に分けて保管

大きな図書館では、本を年代別・分野別に分けて保管します：

年代別書庫の例:

  • 1 階（新刊エリア）: 2020 年以降の本
  • 2 階（一般エリア）: 2000-2019 年の本
  • 3 階（古書エリア）: 1980-1999 年の本
  • 地下書庫: 1979 年以前の本

メリット:

  • よく使われる新しい本は 1 階で素早くアクセス
  • 古い本は地下に保管してスペース効率化
  • 本の追加・削除が該当エリアのみで完結

データベースのパーティショニング:

-- 日付別パーティション
CREATE TABLE book_rentals (
    rental_id INT,
    book_id INT,
    user_id INT,
    rental_date DATE,
    return_date DATE
) PARTITION BY RANGE (YEAR(rental_date)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p_old VALUES LESS THAN MAXVALUE
);

実際の効果:

  • 「2024 年の貸出記録を検索」→ p2024 パーティションのみをチェック
  • 古いデータの削除が高速（パーティション単位で削除）
  • バックアップも部分的に実行可能

分野別パーティションの例:

-- 本のジャンル別パーティション
CREATE TABLE books (
    book_id INT,
    title VARCHAR(255),
    category_id INT,
    isbn VARCHAR(13)
) PARTITION BY LIST (category_id) (
    PARTITION p_fiction VALUES IN (1,2,3),      -- 小説・文学
    PARTITION p_science VALUES IN (4,5,6),      -- 科学・技術
    PARTITION p_history VALUES IN (7,8,9),      -- 歴史・社会
    PARTITION p_others VALUES IN (DEFAULT)
);

キャッシュ戦略：よくある質問コーナー

頻繁な質問は即座に答える

図書館のカウンターでよくある質問：

毎日何度も聞かれる質問:

  • 「Wi-Fi のパスワードは？」
  • 「コピー機はどこにありますか？」
  • 「開館時間は何時から？」
  • 「駐車場の料金は？」

司書の対応:

  1. 手間のかかる方法: 毎回資料を取り出して調べる
  2. 効率的な方法: 「よくある質問」をカウンターに用意

データベースでも同様:

レベル別キャッシュ戦略

レベル 1: 司書の記憶（CPU キャッシュ）

  • 最も頻繁な質問は覚えている
  • 一瞬で答えられる
  • 容量は小さい（覚えられる量に限界）

レベル 2: カウンターの早見表（メモリキャッシュ）

  • よくある質問と答えを書いた表
  • 数秒で確認できる
  • そこそこの容量

レベル 3: 事務室のファイル（ディスクキャッシュ）

  • 詳細な資料を保管
  • 取りに行く時間が必要
  • 大容量で保存可能

レベル 4: 図書館システム（データベース）

  • 全ての情報を正確に保管
  • アクセスに最も時間がかかる
  • 膨大な情報量

実際のキャッシュシステム

Redis（高速メモリキャッシュ）の活用例:

図書館システムでは、頻繁にアクセスされるデータを高速なメモリに保存：

# 人気本ランキングをRedisに保存
キー: "popular_books:2024-01"
値: [
  {"book_id": "B001", "title": "データベース入門", "count": 25},
  {"book_id": "B002", "title": "料理の基本", "count": 18}
]
有効期限: 1時間

# アクセスの流れ
1. まずRedisをチェック
2. データがあればそれを返す
3. なければDBから取得してRedisに保存

キャッシュの種類:

1. 読み取りキャッシュ:

  • よく検索される本の情報を記憶
  • 「データベース入門」の詳細は覚えている
  • 同じ検索が来たら即座に回答

2. 書き込みキャッシュ:

  • 貸出記録をいったん高速な場所に保存
  • まとめて本当のデータベースに記録
  • システム障害時のリスクあり

3. 分散キャッシュ:

  • 複数の図書館でキャッシュを共有
  • 一つの図書館で調べたことを他館でも活用

キャッシュ更新戦略

1. TTL（生存時間）方式:
「このデータは 30 分後に古くなるから、新しく取得し直そう」

2. 書き込み時更新:
「本の情報が更新されたら、キャッシュも同時に更新」

3. 需要に応じた更新:
「アクセスされた時に古かったら、その時に更新」

注意点:

  • キャッシュと実際のデータの不整合
  • メモリ容量の制限
  • 更新タイミングの調整

3. セキュリティ：銀行の金庫システム

アクセス制御：銀行の役職別権限

銀行では役職によってアクセスできる情報が違います：

窓口担当者:

  • 顧客の基本情報は見える
  • 口座残高は見える
  • でも他の顧客の取引履歴は見えない

支店長:

  • 支店内の全顧客情報
  • 取引履歴
  • でも他支店の情報は制限

システム管理者:

  • システムの設定変更
  • バックアップの管理
  • でも顧客の個人情報は見えない

データ暗号化：重要書類の保管

銀行の重要書類保管方法:

  • 普通の書類：通常のキャビネット
  • 機密書類：鍵付きキャビネット
  • 極秘書類：金庫に暗号化して保管

データベースでも同様に:

  • 一般的なデータ：通常の保存
  • 個人情報：暗号化して保存
  • 機密データ：強力な暗号化＋アクセス制限

4. バックアップ：重要書類の複製管理

図書館の蔵書管理例

図書館の貴重書保管方法:

  • 原本: 特別な保管庫に厳重保管
  • 複製 1: 閲覧用として提供
  • 複製 2: 他の図書館に預ける（災害対策）
  • 複製 3: デジタル化してクラウドに保存

データベースのバックアップも同様:

  • 日次バックアップ: 毎日の変更分を保存
  • 週次バックアップ: 完全なデータを保存
  • 地理的分散: 他の地域にもバックアップ保存

ポイントインタイムリカバリ：時間を戻す能力

図書館の例:
「昨日の午後 3 時に、間違って重要な本を除籍してしまった。その時点の状態に戻したい」

データベースでも同様:
「昨日の午後 3 時にデータを間違って削除してしまった。その時点の状態に戻したい」

→ バックアップデータ＋変更ログを使って、特定の時点まで復旧可能

5. 実践的な設計のポイント

設計フェーズで重要なこと

1. 要件の明確化

  • どんなデータを保存するの？（図書館なら：本、利用者、貸出記録）
  • どんな検索をするの？（著者名で検索、ジャンルで検索）
  • どのくらいのデータ量？（本 10 万冊、利用者 5 万人）

2. 成長を見据えた設計

  • データは増える前提で設計
  • 検索パターンの変化に対応
  • 新機能追加への拡張性

3. 運用の現実性

  • バックアップの自動化
  • 監視・アラートの設定
  • 障害時の復旧手順

よくある問題とその対策

問題 1: N+1 問題

図書館の例:
「今日借りられた本を、借りた人の情報と一緒に一覧表示したい」

悪い方法:

  1. 今日借りられた本のリストを取得（1 回の DB アクセス）
  2. 各本について、借りた人の情報を個別に取得（本の数だけ DB アクセス）

良い方法:

  1. 本と借りた人の情報を一度にまとめて取得（1 回の DB アクセス）

問題 2: スロークエリ

図書館の例:
「タイトルに『データベース』という言葉が含まれる本を探す」

悪い方法:
全ての本のタイトルを 1 冊ずつチェック

良い方法:
全文検索インデックスを使用して高速検索

問題 3: デッドロック

レストランの例:

  • 客 A: テーブル 1 を予約 → テーブル 2 も予約したい
  • 客 B: テーブル 2 を予約 → テーブル 1 も予約したい
    → お互いを待ち続けて処理が止まる

対策:

  • 予約の順序を統一（必ずテーブル 1→ テーブル 2 の順で予約）
  • タイムアウト設定（一定時間で諦める）

まとめ

データベース設計は、身の回りの整理整頓や管理と同じ考え方です：

基本原則

  1. 整理整頓（正規化）: 重複をなくして管理しやすく
  2. 素早い検索（インデックス）: よく使う項目は見つけやすく
  3. 安全性（トランザクション）: 重要な処理は確実に
  4. 適切な保存（データ型）: 目的に応じた最適な方法で

身近な例で理解したデータベース設計の概念を、ぜひ実際のプロジェクトで活用してください！

Discussion