最近「おうちで学べるデータベースのきほん」という本を読み、DB周りの基礎知識を体系的に学べたので、アウトプットもかねて記事にしていきます！
- ビューについて
- ビュー (
VIEW) がメモリを消費しない理由 - 副問い合わせ（サブクエリ）
- トランザクション分離レベル（Read Uncommitted, Read Committed, Repeatable Read, Serializable）
- 読み取りに関する現象（Dirty Read, Fuzzy Read, Phantom）
- ロックタイムアウトとデッドロック
- 正規化（第一正規形～第三正規形）
- ER図とIE記法によるリレーショナル設計
私が勉強になった部分のみ抜粋して記載していきます。
1. ビューについて
概要
ビューとは、基になるテーブルに対する仮想的なテーブルのことです。
- 利点：複雑なクエリを隠蔽し、利用者がシンプルな SELECT 文でデータにアクセスできるようにする
- セキュリティ：特定のカラムだけを公開することで、機密情報の保護にも利用できる
- 抽象化：アプリケーションからはテーブル構造の変更を隠蔽できる
利用例
たとえば、従業員情報と部署情報が別テーブルにある場合、以下のようなビューを作成できます。
CREATE VIEW employee_details AS
SELECT e.id, e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;
これにより、従業員情報を参照する際に JOIN を毎回記述する必要がなくなります。
2. ビュー (VIEW) がメモリを消費しない理由
理論的背景
ビューは物理的にデータを保持しているわけではなく、定義された SQL クエリが実行されるたびに基になるテーブルからデータを取得するため、以下の点が挙げられます。
- ストレージ上のコストはゼロ：ビュー自体は定義情報（メタデータ）しか保持しない
- 実行時にのみ評価ク：エリ実行時に実際のデータを取得するため、キャッシュが利用されることはあるが、定義自体がメモリに常駐するわけではない
注意点
ただし、複雑なビューの場合、クエリ最適化や実行計画の構築において一時的にメモリを使用する可能性はあります。しかし、ビューそのものが追加のデータをストレージ上に持たないため、常駐する「データ量」としては消費しません。
3. 副問い合わせ（サブクエリ）
概要
副問い合わせは、SQL 文の中に入れ子状に書かれる SELECT 文です。
- 主目的：複雑な条件のデータ抽出や集計結果を利用してフィルタリングを行う
- 種類：単一行、副問い合わせ、相関副問い合わせなど
利用例
たとえば、従業員テーブルから、各部署の最高給与を得ている従業員を抽出する場合：
SELECT *
FROM employees e
WHERE salary = (
SELECT MAX(salary)
FROM employees
WHERE department_id = e.department_id
);
この例では、相関副問い合わせが用いられており、各従業員ごとに所属部署の最高給与を比較しています。
メリット・デメリット
- メリット：複雑なロジックを単一のクエリにまとめられる
- デメリット：パフォーマンスの低下につながることがあるため、インデックスや適切なクエリ最適化を検討する必要がある
4. トランザクション分離レベル
(Read Uncommitted, Read Committed, Repeatable Read, Serializable)
概要
トランザクション分離レベルは、複数のトランザクションが同時に実行される際の「干渉」をどの程度防止するかを規定するものです。
- Read Uncommitted：最も低い隔離レベル。未コミットの変更も読み込むため、Dirty Read が発生する可能性がある。
- Read Committed：コミットされたデータのみ読み込む。多くのデータベースのデフォルト。
- Repeatable Read：一度読み込んだデータは、同一トランザクション内で再度読み込んだ際に変化しない。
- Serializable：最も高い隔離レベル。全トランザクションを順序立てた実行に見せるため、競合は発生しないが、スループットは低下する可能性がある。
各レベルの特徴
| 分離レベル | 特徴・メリット | デメリット |
|---|---|---|
| Read Uncommitted | パフォーマンス重視。 | Dirty Read や不整合データの可能性がある。 |
| Read Committed | 一般的な利用。 | Fuzzy Read（再実行で違う結果になる可能性） |
| Repeatable Read | 同一トランザクション内のデータ一貫性保証。 | ファントムリードが発生する場合がある。 |
| Serializable | 完全な一貫性を保証。 | 並行性が低くなり、パフォーマンスが低下する。 |
5. 読み取りに関する現象（Dirty Read, Fuzzy Read, Phantom）
Dirty Read
- 定義：他のトランザクションが未コミットの変更を読み取ってしまう現象
- 発生条件：分離レベルが Read Uncommitted の場合に発生する可能性がある
Fuzzy Read（Non-repeatable Read）
- 定義: 同一トランザクション内で同じクエリを実行した際、結果が異なる現象
- 発生条件: Read Committed の場合、他のトランザクションのコミットにより値が変わることがある
Phantom Read
- 定義: 同一トランザクション内でクエリを実行した際、前回は存在しなかった行が現れる現象
- 発生条件: Repeatable Read でも、範囲検索において新たな行が挿入された場合に発生する
- 対策: Serializable 分離レベルを用いるか、適切なロックを活用する
6. ロックタイムアウト
概要
ロックタイムアウトは、トランザクションがロックを取得しようとして一定時間待機したが成功しなかった場合に、処理を打ち切る仕組みです。
- 目的：長時間の待機やデッドロックの回避、システム全体のパフォーマンス低下防止
-
実装例：多くの RDBMS で設定可能（例: SQL Server の
LOCK_TIMEOUT、MySQL の innodb_lock_wait_timeout など）
注意点
- 適切なタイムアウト値の設定が必要
- タイムアウトが頻発する場合は、トランザクションの分割やインデックスの見直し、クエリの改善を検討する
7. デッドロック
概要
デッドロックは、複数のトランザクションがお互いのロックを待っている状態となり、結果的に進行不能になる現象です。
発生例
- トランザクション A がテーブル1の行をロックし、テーブル2のロックを要求
- 同時に、トランザクション B がテーブル2の行をロックし、テーブル1のロックを要求
- 結果として、双方が互いのリソース待ちとなり、デッドロックが発生する
解決策
- タイムアウトの設定：ロックタイムアウトによって一定時間経過後に自動ロールバックする
- ロックの順序の統一：すべてのトランザクションで同じ順序でリソースにアクセスする
- 細粒度ロック：できるだけ小さな範囲でロックをかける
8. 正規化（第一正規形～第三正規形）
第一正規形 (1NF)
- 定義：テーブルの各フィールドが原子値（分割不可能な値）であること
- 目的：重複するデータの排除、単一の値でデータを管理する
第二正規形 (2NF)
- 定義：第一正規形を満たし、かつ部分関数従属が存在しないこと
- 対象：複合主キーを持つテーブルに適用
- 目的：主キーの一部に依存する非キー属性を別テーブルに分割する
第三正規形 (3NF)
- 定義：第二正規形を満たし、非キー属性が他の非キー属性に依存していないこと
- 目的：推移的関数従属を排除し、テーブルをよりシンプルに保つ
実例
例えば、従業員テーブルに「部署名」が含まれている場合、部署情報が重複して記録される可能性があります。
- 1NF：従業員テーブルは、各フィールドが原子値である。
- 2NF：部署ID と 従業員ID の複合キーの場合、部署名が部署IDに依存するため、部署テーブルに分割する。
- 3NF：もし部署テーブル内で、部署名以外の情報がさらに他の属性に依存しているなら、適宜テーブルを分割する。
9. ER図とIE記法によるリレーショナル設計
ER図 (Entity-Relationship Diagram) の概要
- 目的: システムのデータ構造を視覚的に表現し、エンティティ（実体）間の関係を明確にする
- 構成要素: エンティティ、リレーションシップ、属性
IE記法とは
IE（Information Engineering）記法は、ER図を記述する際の表記法のひとつです。
- エンティティ：長方形で表し、エンティティ名は大文字で記述
- リレーションシップ：線で結び、線上にカーディナリティ（1:1, 1:N, N:M）を記述
- 属性：楕円形で表し、エンティティと線で結ぶ
ER図作成の手順
- エンティティの洗い出し：システムで扱う主要なオブジェクト（例：従業員、部署、プロジェクトなど）を抽出
- リレーションシップの定義：エンティティ間の関連性を定義（例：従業員は部署に所属する）
- 属性の割り当て：各エンティティに必要な属性を決定
- 正規化との連携：作成した ER 図が正規化の原則に沿っているか確認
ツールの紹介
- draw.io や Lucidchart などのオンラインツールを活用すると、視覚的な ER 図の作成が容易になります。
まとめ
本記事では、データベースにおける主要な概念について以下の内容をアウトプットしました。
- ビュー：仮想テーブルの役割とそのメリット、実行時のメモリ消費の仕組み
- 副問い合わせ：複雑なクエリの実現手段とパフォーマンスへの配慮
- トランザクション分離レベル：Read Uncommitted から Serializable までの各レベルの特徴と注意点
- 読み取り現象：Dirty Read、Fuzzy Read、Phantom の発生条件と対策
- ロック：ロックタイムアウト、デッドロックの概要と解決策
- 正規化：第一～第三正規形の基本原則と実践例
- ER図とIE記法：リレーショナル設計のための基本ツールと記法