データベースの基礎——RDB の仕組み、SQL、トランザクション、インデックスの原理

Lab AI データベースの基礎——RDB の仕組み、SQL、トランザクション、インデックスの原理

現代のアプリケーションは、データベースなしでは成り立たない。ユーザー情報、商品カタログ、注文履歴、ブログ記事——あらゆるデータがデータベースに保存され、検索され、更新されている。本記事では、最も広く使われているリレーショナルデータベース（RDB）に焦点を当て、その基本概念から実用的な設計テクニックまでを体系的に解説する。

データベースとは

**データベース（Database）**とは、データを体系的に整理し、効率的に検索・更新できるようにしたシステムだ。

【データベースの 3 つの役割】

1. データの保存
   - 永続的にデータを保管
   - 停電でもデータは消えない

2. データの検索
   - 条件に合うデータだけを高速に取得
   - 複数テーブルをまたぐ結合も可能

3. データの整合性維持
   - 矛盾したデータが入らないように制約
   - 同時アクセスでもデータが壊れない

ファイルシステムとの違い

データベースを使わず、CSV や JSON ファイルに直接保存するアプローチも考えられる。しかし、これには重大な問題がある。

【ファイルシステムの限界】

・同時書き込みでデータが壊れる
・一部だけ更新失败すると整合性が崩れる
・大量データから検索が遅い
・データ形式の制約をかけられない

【データベースの解決策】

・トランザクションで同時書き込みを制御
・ACID 特性で整合性を保証
・インデックスで高速検索
・型制約・ UNIQUE 制約でデータ品質を担保

リレーショナルデータベースの基本概念

テーブル・行・列

リレーショナルデータベースは、**テーブル（表）**の集合としてデータを表現する。

【テーブルの例：users】

+----+----------+---------------------+------------+
| id | name     | email               | created_at |
+----+----------+---------------------+------------+
| 1  | 山田太郎 | taro@example.com    | 2025-01-01 |
| 2  | 鈴木花子 | hanako@example.com  | 2025-01-02 |
| 3  | 佐藤次郎 | jiro@example.com    | 2025-01-03 |
+----+----------+---------------------+------------+

・テーブル：users（表全体）
・行（レコード）：1 人のユーザーデータ
・列（カラム）：id, name, email, created_at

主キー（Primary Key）

主キーは、行を一意に識別するための列だ。

【主キーの要件】

1. 一意性：同じ値が 2 行に存在しない
2. 非 NULL：NULL を許容しない
3. 不変性：値は変更されない

【主キーの例】

・id: 連番（1, 2, 3...）— 最も一般的
・UUID: ランダムな 128 ビット値
・自然キー：メールアドレスなど（変更リスクあり）

外部キー（Foreign Key）

外部キーは、他のテーブルの主キーを参照する列だ。テーブル間の関係を表現する。

【外部キーの例：orders テーブル】

+----+------------+----------+-------+
| id | user_id    | product  | price |
+----+------------+----------+-------+
| 1  | 1          | ノート   |   200 |
| 2  | 1          | ペン     |   100 |
| 3  | 2          | 本       |  1500 |
+----+------------+----------+-------+
       ↑
       users.id を参照

・user_id = 1 → 山田太郎の注文
・user_id = 2 → 鈴木花子の注文

データ型

各列にはデータ型が定義され、格納できる値の種類が決まる。

【主要なデータ型】

| データ型 | 説明 | 例 |
|---------|------|-----|
| INTEGER | 整数 | 1, 100, -50 |
| BIGINT | 大きな整数 | 10 億以上の値 |
| VARCHAR(n) | 可変長文字列 | "山田太郎" |
| TEXT | 長文文字列 | ブログ記事本文 |
| BOOLEAN | 真偽値 | TRUE, FALSE |
| DATE | 日付 | 2025-01-15 |
| TIMESTAMP | 日時 | 2025-01-15 10:30:00 |
| DECIMAL(p,s) | 固定小数点 | 1234.56（金利・通貨）|
| JSON | JSON データ | {"key": "value"} |

SQL——データベース言語

**SQL（Structured Query Language）**は、データベースを操作するための標準言語だ。

SELECT——データの取得

-- 全列を取得
SELECT * FROM users;

-- 特定の列だけ取得
SELECT id, name FROM users;

-- 条件付き検索（WHERE 句）
SELECT * FROM users WHERE id = 1;

-- 複数条件（AND, OR）
SELECT * FROM users
WHERE age >= 20 AND city = 'Tokyo';

-- 部分一致（LIKE）
SELECT * FROM users
WHERE name LIKE '%山田%';

-- 排序（ORDER BY）
SELECT * FROM users
ORDER BY created_at DESC;

-- 件数制限（LIMIT）
SELECT * FROM users
LIMIT 10 OFFSET 20;  -- 21 件目から 10 件

INSERT——データの挿入

-- 1 行挿入
INSERT INTO users (name, email)
VALUES ('田中新', 'tanaka@example.com');

-- 複数行挿入
INSERT INTO users (name, email)
VALUES
  ('中村美咲', 'nakamura@example.com'),
  ('小林健', 'kobayashi@example.com');

-- 挿入した行の ID を取得（PostgreSQL）
INSERT INTO users (name, email)
VALUES ('高橋優', 'takahashi@example.com')
RETURNING id;

UPDATE——データの更新

-- 1 行を更新
UPDATE users
SET email = 'new@example.com'
WHERE id = 1;

-- 複数カラムを更新
UPDATE users
SET name = '山田新太郎', age = 31
WHERE id = 1;

-- 条件付き更新
UPDATE users
SET status = 'inactive'
WHERE last_login < '2024-01-01';

-- ⚠️ WHERE 句を忘れると全行更新される！
UPDATE users SET status = 'active';  -- 危険！

DELETE——データの削除

-- 1 行を削除
DELETE FROM users WHERE id = 1;

-- 条件付き削除
DELETE FROM users
WHERE created_at < '2020-01-01';

-- ⚠️ WHERE 句を忘れると全行削除される！
DELETE FROM users;  -- 危険！

JOIN——複数テーブルの結合

-- 内部結合（INNER JOIN）
-- 一致する行だけを取得
SELECT
  users.name,
  orders.product,
  orders.price
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- 左外部結合（LEFT JOIN）
-- 左側の全行 + 一致する右側
SELECT
  users.name,
  orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
-- 注文がないユーザーも NULL で表示される

-- 3 テーブル結合
SELECT
  users.name,
  orders.product,
  categories.name AS category_name
FROM users
INNER JOIN orders ON users.id = orders.user_id
INNER JOIN categories ON orders.category_id = categories.id;

集計関数

-- 件数（COUNT）
SELECT COUNT(*) FROM users;

-- 合計（SUM）
SELECT SUM(price) FROM orders;

-- 平均（AVG）
SELECT AVG(price) FROM orders;

-- 最大・最小（MAX, MIN）
SELECT MAX(price), MIN(price) FROM orders;

-- グループ別集計（GROUP BY）
SELECT
  user_id,
  COUNT(*) AS order_count,
  SUM(price) AS total_amount
FROM orders
GROUP BY user_id;

-- 集計結果でフィルタ（HAVING）
SELECT
  user_id,
  SUM(price) AS total_amount
FROM orders
GROUP BY user_id
HAVING SUM(price) > 10000;

トランザクション——データ整合性の保証

**トランザクション（Transaction）**は、複数の操作を「すべて実行する」か「すべて実行しない」のどちらかに保証する仕組みだ。

ACID 特性

トランザクションは、以下の 4 つの特性を保証する（ACID 特性）。

【ACID 特性】

1. 原子性（Atomicity）
   - トランザクションは全て実行されるか、全く実行されないか
   - 部分的な適用は発生しない

2. 一貫性（Consistency）
   - トランザクション前後もデータ整合性が保たれる
   - 制約違反は発生しない

3. 分離性（Isolation）
   - 並行トランザクションは互いに干渉しない
   - 同時に実行されても、直列実行と同じ結果

4. 永続性（Durability）
   - コミット後の変更は失われない
   - 停電でもデータは保存される

トランザクションの使用例

-- 銀行振込の例
BEGIN;  -- トランザクション開始

-- A さんの口座から 10,000 円引き落とし
UPDATE accounts
SET balance = balance - 10000
WHERE account_id = 'A';

-- B さんの口座に 10,000 円入金
UPDATE accounts
SET balance = balance + 10000
WHERE account_id = 'B';

-- 両方の更新が成功したらコミット
COMMIT;

-- エラーが発生したらロールバック
-- （すべての変更が取り消される）
ROLLBACK;

トランザクション分離レベル

並行トランザクションの干渉度合いを調整する設定だ。

【分離レベルと現象】

| レベル | _dirty read_ | _non-repeatable read_ | _phantom read_ |
|--------|-------------|----------------------|----------------|
| READ UNCOMMITTED | 発生 | 発生 | 発生 |
| READ COMMITTED | 防止 | 発生 | 発生 |
| REPEATABLE READ | 防止 | 防止 | 発生 |
| SERIALIZABLE | 防止 | 防止 | 防止 |

【各現象の説明】

・Dirty Read: 未コミットの変更を読んでしまう
・Non-repeatable Read: 同じ行を 2 回読んで結果が違う
・Phantom Read: 同じ条件で 2 回検索して行数が違う

【デフォルト設定】
・PostgreSQL: READ COMMITTED
・MySQL (InnoDB): REPEATABLE READ
・Oracle: READ COMMITTED

インデックス——高速検索の仕組み

**インデックス（Index）**は、本の索引のように、特定のデータを高速に検索するための仕組みだ。

インデックスの仕組み

【インデックスなしの場合】

SELECT * FROM users WHERE email = 'test@example.com';

テーブル全体をスキャン（総当たり検索）：
行 1: test1@example.com ← 違う
行 2: test2@example.com ← 違う
行 3: test3@example.com ← 違う
...
行 100 万：test@example.com ← 見つかった！

→ 100 万回の比較が必要

【インデックスありの場合】

CREATE INDEX idx_users_email ON users(email);

B ツリー構造で検索：
        email
       /     \
  m@...     t@...
             /   \
        ta@...  te@...
                  \
                   test@example.com → 行 100 万

→ log₂(100 万) ≈ 20 回の比較で完了

インデックスの効果

-- インデックス使用クエリ（高速）
SELECT * FROM users WHERE email = 'test@example.com';
-- idx_users_email を使用、O(log n)

-- インデックス不使用クエリ（低速）
SELECT * FROM users WHERE name LIKE '%太郎%';
-- 前方一致ではないためインデックス使えない、O(n)

-- インデックス使用クエリ
SELECT * FROM users
WHERE created_at >= '2025-01-01'
ORDER BY created_at;
-- 範囲検索も可能

インデックス設計のベストプラクティス

【インデックスを効果的に使える場面】

1. WHERE 句で頻繁に使われる列
2. JOIN の結合条件（外部キー）
3. ORDER BY で排序される列
4. UNIQUE 制約のある列

【インデックスのデメリット】

1. 書き込み速度の低下
   - INSERT/UPDATE/DELETE 時にインデックスも更新が必要

2. ストレージ使用量の増加
   - テーブルデータの 20-30% 増

3. 過剰なインデックス
   - 1 テーブル 5-7 個が目安

【複合インデックスの順序】

・等値条件で使う列を先に
・カーディナリティ（値の種類）の高い列を先に

CREATE INDEX idx_orders_user_date
ON orders(user_id, created_at);
-- user_id でフィルタ → 日期で排序/範囲検索

カーディナリティ

カーディナリティは、列の値の種類の多さを示す。

【カーディナリティの例】

・高い：email（全員違う値）→ インデックス効果大
・中程度：city（数十種類）→ インデックス効果中
・低い：gender（2-3 種類）→ インデックス効果小

カーディナリティが低い列にインデックスを作成しても、
検索速度はあまり向上しない。

正規化——データの構造化

**正規化（Normalization）**は、データの重複を排除し、整合性を高めるためにテーブルを分割する設計手法だ。

正規化の目的

【データ重複の問題】

非正規化された注文テーブル：

| order_id | customer_name | customer_email | product | price |
|----------|---------------|----------------|---------|-------|
| 1 | 山田 | taro@example.com | ノート | 200 |
| 2 | 山田 | taro@example.com | ペン | 100 |
| 3 | 鈴木 | hanako@example.com | 本 | 1500 |

問題点：
・山田さんの情報が重複（ wasting storage）
・山田さんがメール変更 → 2 行更新が必要
・更新漏れでデータ不整合のリスク

第 1 正規形（1NF）

【1NF の要件】
・すべての列が原子値（分割できない値）
・ repeating group（繰り返しグループ）がない

❌ 1NF 違反：
| id | name | phones |
| 1 | 山田 | 090-1234, 03-5678 |

✅ 1NF 達成：
| id | name | phone |
| 1 | 山田 | 090-1234 |
| 1 | 山田 | 03-5678 |

または、別テーブルに分離：
users: | id | name |
user_phones: | user_id | phone |

第 2 正規形（2NF）

【2NF の要件】
・1NF 達成済み
・主キーの一部にのみ依存する列がない（部分従属の排除）

❌ 2NF 違反：
| order_id | product_id | quantity | product_name | price |
主キー：(order_id, product_id)

問題：product_name と price は product_id にのみ依存

✅ 2NF 達成：
orders: | order_id | product_id | quantity |
products: | product_id | product_name | price |

第 3 正規形（3NF）

【3NF の要件】
・2NF 達成済み
・主キーでない列に依存する列がない（推移従属の排除）

❌ 3NF 違反：
| user_id | name | city | prefecture |

問題：prefecture は city に依存（user_id に直接依存しない）

✅ 3NF 達成：
users: | user_id | name | city_id |
cities: | city_id | city_name | prefecture |

正規化のトレードオフ

【正規化のメリット】
・データ重複の排除
・更新異常の防止
・ストレージ効率向上

【正規化のデメリット】
・JOIN が増え、クエリが複雑
・検索性能が低下する可能性

【非正規化の判断】
・読みの割合が極めて高い（90% 以上）
・JOIN コストが性能ボトルネック
・データ不整合のリスクが許容できる

例：
・アナリティクス用途 → 非正規化（読最優先）
・基幹システム → 正規化（整合性最優先）

主要 RDBMS の比較

機能	PostgreSQL	MySQL	SQLite
ライセンス	オープンソース	オープンソース（GPL）	パブリックドメイン
特徴	機能豊富、標準 SQL 準拠	高速・シンプル、Web 向け	組み込み、サーバー不要
データ型	豊富（JSON, ARRAY, 地理情報）	標準的	最小限
トランザクション	完全にサポート	InnoDB でサポート	サポート
外部キー	サポート	サポート（デフォルト OFF）	サポート
レプリケーション	組み込み	組み込み	非対応
向いている用途	複雑なクエリ、基幹システム	Web アプリ、CMS	モバイル、組み込み、テスト

まとめ

データベースは現代アプリケーションの基盤であり、その理解はシステム開発に不可欠だ。

リレーショナルモデル: テーブル・行・列でデータを表現
SQL: データの取得・挿入・更新・削除・結合・集計
トランザクション: ACID 特性でデータ整合性を保証
インデックス: B ツリーで高速検索を実現
正規化: データ重複を排除し整合性を高める

データベース設計は、アプリケーションの性能と保守性を決定する重要な要素だ。適切な正規化、効果的なインデックス設計、トランザクションの正しい使用——これらの基礎を理解することで、信頼性の高いシステムを構築できる。

参考資料

「Database System Concepts」Abraham Silberschatz 他著
「SQL 完全入門」Mick J. 著
「PostgreSQL 公式ドキュメント」: https://www.postgresql.org/docs/
「MySQL 公式ドキュメント」: https://dev.mysql.com/doc/
「Use The Index, Luke」: https://use-the-index-luke.com/
「SQL 性能チューニング実践ガイド」O'Reilly

#データベース #RDB #SQL #トランザクション #インデックス

免責事項 — 掲載情報は執筆時点のものです。料金・機能は変更される場合があります。最新情報は各公式サイトをご確認ください。