C
データベース 一覧
前へ4 / 6次へ
データベース/トランザクション インデックス/Lesson 04

トランザクション + インデックス — ACID・分離レベル・MVCC・B+Tree

60分·theory

トランザクション + インデックス — ACID・分離レベル・MVCC・B+Tree

🎯 このレッスンを読み終えたら

このレッスンをすべて読み終えたら、以下の3つを自信を持って説明できるようになります。

  • ▸✅ ACIDの4つの性質 + 分離レベルの4段階 (RU/RC/RR/Serializable)
  • ▸✅ B-Treeインデックスの動作 + 複合インデックスのLeftmost Prefixルール
  • ▸✅ 楽観的ロック vs 悲観的ロックの選択基準

これらの学習目標をチェックリストとして手元に置き、すべてに答えられるようになったらレッスンを閉じてください。

トランザクションとは — *すべて成功か、すべて取り消し*

一言でいうと

トランザクション (Transaction) = 論理的にひとまとまりの作業単位。内部のすべての操作が成功して初めて変更が確定(コミット)され、ひとつでも失敗すればすべてが取り消し(ロールバック)されます。

なぜ必要か — 送金の例

友人に1万円を送金するとしましょう。データベースがやるべきことは:

1. 自分の口座から1万円を差し引く
2. 友人の口座に1万円を加える

もし1番が成功した後、システム障害で2番が失敗したら? 自分の1万円が消えてなくなります — 友人は受け取れず、自分は失っています。

トランザクションがあれば、両方が成功して初めてcommit(確定)され、どちらかが失敗すればrollback(取り消し) — 何事もなかったかのようになります。

sql
BEGIN;
  UPDATE accounts SET balance = balance - 100000 WHERE id = 1;  -- 送金者から差し引き
  UPDATE accounts SET balance = balance + 100000 WHERE id = 2;  -- 受取人に入金
COMMIT;
-- エラー時: ROLLBACK;

ACID — トランザクションの4つの約束

覚えておく価値があります。面接の定番テーマです。

A — Atomicity (原子性): 全部成功 OR 全部失敗。上記の送金例のとおりです。部分的な成功はありません。

C — Consistency (一貫性): トランザクションの前後でデータベースのルール(外部キー・UNIQUE・CHECK制約)が破られることはありません。「残高は負になれない」といった制約は常に守られます。

I — Isolation (分離性): 同時に実行される他のトランザクションと互いに干渉しません。2人が同時に同じ商品を購入しても、在庫は正確に差し引かれます。

D — Durability (永続性): コミットされたデータは電源が切れても消えません。ディスクに確実に保存されてから初めて成功として応答します。

この4つがあって初めて、お金を扱うシステムを作れます。決済・在庫・予約はすべて、ACIDなしでは危険です。

分離レベル — どこまで厳格に分離するか

ACIDの「I (分離性)」を完全に実現するのが最も安全ですが遅くなります。そのため、どこまで厳格にするかを4段階で選べます。

  • ▸READ UNCOMMITTED: ほぼ分離なし。他者がまだコミットしていないデータも見えます。ほとんど使われません。
  • ▸READ COMMITTED: PostgreSQL・Oracleのデフォルト。コミット済みのデータのみ見えます。最も一般的。
  • ▸REPEATABLE READ: MySQLのデフォルト。トランザクション内で同じクエリは常に同じ結果を返します。
  • ▸SERIALIZABLE: 最も厳格。トランザクションが順番に実行されたかのように保証されます。安全だが遅い。

レベルが上がるほど 安全性 ↑ 並行性 ↓。一般的なWebサービスならREAD COMMITTEDで十分。銀行・決済のような厳密な一貫性が求められる場合はSERIALIZABLEまで上げます。

MVCC — 現代データベースの並行性の秘密

古いデータベースは読み取りと書き込みが互いをブロックしていました。誰かが行を書き込んでいると、他の人は読み取りすらできませんでした。

現代のPostgreSQL・Oracle・MySQL InnoDBはMVCC (Multi-Version Concurrency Control) を採用しています。各行に複数のバージョンを持つことで:

  • ▸書き込む人 → 新しいバージョンを追加
  • ▸読み取る人 → 古いバージョンを参照(変更の影響を受けない)

互いにブロックしません。並行性が飛躍的に向上します。ただし、古いバージョンが蓄積するとディスクを無駄遣いするため、autovacuumのようなバックグラウンドクリーンアップ処理が必要です。

よくある落とし穴

1. トランザクションを長く開けすぎる: BEGINの後10秒間他の作業をすると、その間ロックを保持したまま他のプロセスが待ち続けます。トランザクションは短く。

2. トランザクション外でのコミット: Springの@Transactionalを内部呼び出しのメソッドに付けても適用されません(プロキシの制限)。外部からpublicメソッドとして呼び出す必要があります。

3. 無限ロック待ち: デッドロックや遅いクエリで永遠に待ち続けます。SET LOCAL lock_timeout = '3s';のようなタイムアウト設定が必須です。

まとめ

トランザクションはお金を扱うシステムの基礎。ACIDの4つを理解し、分離レベルはREAD COMMITTEDがデフォルト、MVCCが並行性を実現するという点が最低限の知識です。実際のコードはSpringの@Transactional一行で済みますが、原理を理解した上で使うことと知らずに使うことでは、デバッグ時に大きな差が出ます。

インデックス — *本の索引のようなもの*

インデックスとは

図書館で本を1冊探すとき、最初から最後まで棚を総なめにしたら? 1万冊なら1万回。カード索引を見れば? 一発で場所がわかります。

DBのインデックスも同じ原理です。テーブルのカラムにインデックスを作成しておくと、そのカラムでの検索速度が劇的に向上します。100万行の検索が0.001秒 vs 10秒 — 1万倍の差です。

B+Tree — 最も一般的なインデックス構造

ほとんどのDBはB+Treeというデータ構造を使います。平衡木(バランスツリー) — どの検索も同じ深さを通るため、一定のO(log n)時間を保証します。

code
        [50]              ← ルート
       /    \
    [25]    [75]          ← 中間ノード
    / \    / \
  ...  ...  ...  ...      ← 葉ノード（実際のデータへのポインタ）

葉ノードは互いに連結されているため、範囲検索（例: 25〜75）も高速です。最初の位置を見つけたら横に辿るだけで完了します。

いつインデックスを追加するか

  • ▸✅ WHERE句に頻繁に登場するカラム — WHERE email = ?
  • ▸✅ JOINの結合条件 — ON orders.user_id = users.id
  • ▸✅ ORDER BYのカラム — インデックスがすでにソート済みの順序を提供
  • ▸❌ 書き込みが非常に多いテーブル — インデックスも毎回更新が必要（低速化）
  • ▸❌ カーディナリティが低い（例: 性別）— インデックスの効果が薄い

複合インデックス — 順序が重要

複数のカラムをまとめてインデックスにする:

sql
CREATE INDEX idx_orders ON orders(user_id, created_at);

このインデックスは左から順にマッチします (Leftmost Prefixルール)。

  • ▸WHERE user_id = ? ✅ 使用
  • ▸WHERE user_id = ? AND created_at > ? ✅ 使用
  • ▸WHERE created_at > ? ❌ 不使用（user_idがない）

電話帳を思い浮かべてください。姓・名の順で並んでいるので、姓がわからないとすばやく探せません。名前だけでは最初から調べるしかありません。

インデックスが使われない5つの落とし穴

これを知らないと、インデックスを作っても効果がありません。

1. 関数の使用 — WHERE UPPER(email) = '[email protected]'。インデックスは元の値でソートされているため、関数の結果は不明です。

2. 左側のワイルドカード — WHERE name LIKE '%ホン%'。先頭からマッチできないため無意味。LIKE 'ホン%'はOK。

3. 型の不一致 — カラムが数値型なのにWHERE user_id = '42'（文字列）で検索。暗黙的な型変換が起きるとインデックスが使われません。
4. NOT / != — 否定条件は通常インデックスを使えません。定義上、ほとんどの行が該当するからです。

5. OR句 — WHERE a = 1 OR b = 2。両方にインデックスがあっても、片方だけまたは両方とも使われないことが多いです。UNIONで分割すると改善できます。

> 💡 EXPLAIN ANALYZEで確認する習慣が、クエリパフォーマンスデバッグの出発点です。

EXPLAIN — クエリの実行計画を見る

DBがどのようにクエリを実行するかを事前に確認できます。

sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

結果で確認すべきポイント:

  • ▸Seq Scan = テーブル全件スキャン。大きなテーブルでは危険信号。
  • ▸Index Scan = インデックスを使用。✅
  • ▸rows=100 推定値 vs actual rows=10000 = 統計情報のズレ。ANALYZE <テーブル名>;で更新が必要です。

EXPLAINの結果を読める能力こそ、SQLパフォーマンスデバッグの基礎です。

N+1問題 — ORMの定番の落とし穴

python
users = User.query.all()           # クエリ1回
for u in users:
    print(u.orders.count())        # ユーザーごとに1回 → N回追加

ユーザーが100人いれば101回のクエリが発生。ページが数十倍遅くなります。

解決策: ORMのEagerロード機能を使う:

  • ▸JPA: @Query + JOIN FETCH
  • ▸Django: prefetch_related
  • ▸SQLAlchemy: joinedload
  • ▸Prisma: include

1回のJOINクエリですべてのデータを取得し、N+1を1に削減。

まとめ

インデックスはクエリ速度の要。ただし、むやみに追加すると書き込みパフォーマンスが低下します。EXPLAINで確認しながら、必要な箇所にだけ追加を。N+1のようなORMの落とし穴も、実際のクエリを見て初めて気づけます。

💾 実践デモ1 — インデックス効果 (EXPLAIN ANALYZE)

インデックス適用*前後*の実行計画の違いを確認してください。▶ 実行で結果を確認できます。
📥 입력 DB 서버로 전송되는 쿼리
SELECT FROM: users length: 410 chars 
-- 100万行テーブルで email を検索

-- ❌ インデックスなし (Seq Scan)
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = '[email protected]';
-- Seq Scan on users  (cost=0..18334)
-- Execution Time: 850 ms

-- ✅ インデックス追加後
CREATE INDEX idx_users_email ON users(email);

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = '[email protected]';
-- Index Scan using idx_users_email  (cost=0..8)
-- Execution Time: 0.5 ms

-- → 1700倍高速化
⚙️ 처리 단계 DB 엔진 내부 동작
📋
1. 파싱
SQL 문법 분석 → AST 생성
10ms
🧠
2. 옵티마이저
인덱스·조인 순서 최적화 (통계 기반)
25ms
⚙️
3. 실행
Storage Engine이 데이터 읽기
21ms
📊
4. 결과 반환
버퍼에 모아서 클라이언트에 전달
8ms
총 64ms
📤 출력
✓ 2 rows 3 cols 64ms
stageplantime_ms
インデックスなしSeq Scan (1,000,000 rows)850
インデックスありIndex Scan (1 row)0.5
💡 학습 포인트: 실제 DB는 파싱 → 옵티마이저 → 실행 → 반환 4단계를 거칩니다. 인덱스가 있으면 '옵티마이저' 단계가 수천배 빨라집니다.
🌐 본인 환경(PostgreSQL·MySQL 등)에서 직접 실행해 ms 차이를 체감해보세요.

💾 実践デモ2 — トランザクション・ロールバック（送金シナリオ）

残高不足時にトランザクションが*自動的にロールバック*される様子を確認します。
📥 입력 DB 서버로 전송되는 쿼리
SELECT FROM: accounts length: 310 chars 
-- 初期状態: 口座1 = 50,000円、口座2 = 0円

BEGIN;

-- 引き落とし
UPDATE accounts SET balance = balance - 100000 WHERE id = 1;

-- 残高の非負検証 (CHECK制約)
-- balance >= 0 違反 → エラー
-- → 自動 ROLLBACK

-- 通過した場合は入金
UPDATE accounts SET balance = balance + 100000 WHERE id = 2;

COMMIT;

-- 結果確認
SELECT * FROM accounts WHERE id IN (1, 2);
⚙️ 처리 단계 DB 엔진 내부 동작
📋
1. 파싱
SQL 문법 분석 → AST 생성
10ms
🧠
2. 옵티마이저
인덱스·조인 순서 최적화 (통계 기반)
25ms
⚙️
3. 실행
Storage Engine이 데이터 읽기
21ms
📊
4. 결과 반환
버퍼에 모아서 클라이언트에 전달
8ms
총 64ms
📤 출력
✓ 2 rows 3 cols 64ms
idbalancestatus
150000(ロールバック済み、元の状態に復元)
20(ロールバック済み、元の状態に復元)
💡 학습 포인트: 실제 DB는 파싱 → 옵티마이저 → 실행 → 반환 4단계를 거칩니다. 인덱스가 있으면 '옵티마이저' 단계가 수천배 빨라집니다.
🌐 본인 환경(PostgreSQL·MySQL 등)에서 직접 실행해 ms 차이를 체감해보세요.

💾 実践デモ3 — N+1 vs JOIN FETCH（パフォーマンス比較）

100人のユーザーの注文件数を取得 — N+1 vs 単一のJOIN。
📥 입력 DB 서버로 전송되는 쿼리
SELECT FROM: users length: 274 chars 
-- ❌ N+1パターン
-- 1回: SELECT * FROM users;
-- N回: SELECT COUNT(*) FROM orders WHERE user_id = ?   (100回)
-- → 101クエリ、5秒

-- ✅ 単一JOIN
SELECT
  u.id,
  u.name,
  COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;
-- → 1クエリ、30ms
⚙️ 처리 단계 DB 엔진 내부 동작
📋
1. 파싱
SQL 문법 분석 → AST 생성
10ms
🧠
2. 옵티마이저
인덱스·조인 순서 최적화 (통계 기반)
25ms
⚙️
3. 실행
Storage Engine이 데이터 읽기
21ms
📊
4. 결과 반환
버퍼에 모아서 클라이언트에 전달
8ms
총 64ms
📤 출력
✓ 2 rows 3 cols 64ms
patternqueriestime
N+1 (旧ORMのデフォルト)1015,000 ms
JOIN FETCH130 ms
💡 학습 포인트: 실제 DB는 파싱 → 옵티마이저 → 실행 → 반환 4단계를 거칩니다. 인덱스가 있으면 '옵티마이저' 단계가 수천배 빨라집니다.
🌐 본인 환경(PostgreSQL·MySQL 등)에서 직접 실행해 ms 차이를 체감해보세요.
💻 📌 よく使うインデックス・トランザクションコード（参考） 
-- ============================================
-- インデックス作成・管理
-- ============================================
CREATE INDEX idx_users_email ON users(email);              -- 単一
CREATE UNIQUE INDEX idx_users_email_uniq ON users(email);  -- ユニーク
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);  -- 複合
CREATE INDEX idx_orders_paid ON orders(user_id) WHERE status='PAID';    -- 部分
CREATE INDEX CONCURRENTLY idx_new ON users(email);         -- ロックなし (運用環境必須)

DROP INDEX CONCURRENTLY idx_old;

-- インデックス使用統計 (使われていないインデックスを探す)
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE schemaname='public' AND idx_scan = 0;

-- ============================================
-- トランザクション
-- ============================================
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- または ROLLBACK;

-- 隔離レベル明示
BEGIN ISOLATION LEVEL READ COMMITTED;
-- ...
COMMIT;

-- タイムアウト (永遠に待たないように)
SET LOCAL lock_timeout = '3s';
SET LOCAL statement_timeout = '30s';

インデックス設計の実践 — カーディナリティ・複合インデックス

カーディナリティ (Cardinality) — 値の種類の数

  • ▸高いカーディナリティ: email（ほぼ全員が異なる）。インデックス効果は絶大。
  • ▸低いカーディナリティ: gender（M/Fの2種類のみ）。インデックス効果はほぼなし。作らない方がよい。
  • ▸中程度: country（数百種類）。ケースバイケース。

カーディナリティが低いカラムにインデックスを作成しても、ディスクを無駄に占有するだけで効果はありません。

インデックス設計の5つの原則

1. WHERE句に頻繁に使われるカラム

sql
SELECT * FROM users WHERE email = ?;
→ CREATE INDEX idx_email ON users(email);

2. JOINキー（外部キーは通常自動生成）

sql
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
→ orders.user_id にインデックス（外部キーで自動作成）

3. ORDER BY・GROUP BYのカラム

インデックスがあればソートステップを省略できます。

4. 複合インデックス — 順序が重要

sql
CREATE INDEX idx_user_status ON orders(user_id, status, created_at);

最もよく使われるカラムを先頭に。このインデックスで対応できるクエリ:

  • ▸WHERE user_id = ? ✅
  • ▸WHERE user_id = ? AND status = ? ✅
  • ▸WHERE user_id = ? AND status = ? AND created_at > ? ✅
  • ▸WHERE status = ? ❌（先頭カラムが欠けている）
  • ▸WHERE created_at > ? ❌

最左のカラムから順番に条件が揃っていなければインデックスを使えません。これをLeftmost Prefixと呼びます。

5. 作りすぎない

インデックスが多すぎるとINSERT/UPDATE/DELETEが遅くなります — すべてのインデックスを更新しなければならないため。読み取り頻度と書き込み頻度のバランスが重要です。

避けるべきインデックスの5パターン

1. カーディナリティが低いカラム — is_deleted、gender単独
2. 非常に長いカラム — TEXTを直接インデックス化（プレフィックスインデックスまたは全文検索を検討）
3. 使用頻度の低いカラム — 作成前にEXPLAINで必要性を検証
4. 関数ベースインデックス未使用 — WHERE YEAR(date) = 2026はインデックスを使わない。WHERE date >= '2026-01-01'に書き直す
5. 重複インデックス — (a)と(a, b)がある場合、(a)は冗長

インデックス統計の確認

sql
SHOW INDEX FROM users;
SELECT * FROM information_schema.statistics WHERE table_name = 'users';

Cardinalityカラムの値が総行数に近いほど、良いインデックスです。

🤖 AIへのリクエスト例

このレッスンの概念を知れば、AIに具体的かつ明確な指示が出せます。漠然とした「直して」ではなく、語彙を持ったリクエスト — それがトークン節約の出発点です。

  • ▸「このクエリ (WHERE user_id + status + created_at) に合う複合インデックスを作って」
  • ▸「この処理を分離レベルREAD_COMMITTEDでトランザクションで囲んで」
  • ▸「デッドロックの可能性があるので、INDEXとロック順序の観点から分析して」

なぜトークンを節約できるのか

概念を知らないとAIの回答を受け取っても「それって何ですか?」と再度聞かなければなりません。その「再質問」がトークンを消費します。概念を一度理解しておけば、やり取りが一回で完結します。

先に読むとよい概念: SQL 実習 — テーブル作成からデータ挿入・照会まで
次のおすすめ: NoSQL 基礎 — Redis・MongoDB をいつ使うか
前へ一覧次へ
トランザクション + インデックス — ACID・分離レベル・MVCC・B+Tree - データベース