図1: 学習全体の地図
| 順番 | 章 | 何を学ぶか | 到達目標 |
|---|---|---|---|
| 1 | データベース | DBMS、リレーショナルモデル、データモデル | SQLが前提にしている世界観を理解する |
| 2 | SQL I | DDL、SELECT、集約、JOIN | 基本的なデータ取得とテーブル定義ができる |
| 3 | トランザクション | ACID、分離性、分離レベル | 複数処理を安全に扱う考え方を理解する |
| 4 | データベース設計 | ERモデル、論理設計、正規化 | 要件をテーブル構造へ落とし込める |
| 5 | インデックス | B+Tree、カーディナリティ、カバリングインデックス | 検索高速化の基本を理解する |
| 6 | 実行計画 | クエリ評価、EXPLAIN、統計情報 | 遅いSQLを調査する入口を知る |
| 7 | SQL II | サブクエリ、CASE式 | SQLで条件分岐や複雑な問い合わせを書ける |
1. データベースの基礎
データベースは、実世界のある側面をモデル化した、秩序ある相互に関連したデータの集合です。DBMSは、アプリケーションがデータを保存・検索・更新しやすくするためのソフトウェアです。
初期のDBMSでは、アプリケーションが物理ファイルの構造を強く意識しなければならず、ファイル形式が変わるとアプリケーション側も変更が必要でした。この問題を解消するために、論理的なデータ表現と物理的な保存形式を分離する考え方が重要になります。
リレーショナルモデルは、データをリレーションとして表現し、高水準な言語で操作するモデルです。SQLはその代表的な操作言語です。
図2: 論理層と物理層の分離
| 層 | 役割 | 例 |
|---|---|---|
| アプリケーション | 業務処理を実行する | 学生名簿アプリ、購買システム |
| 論理層 | テーブルや列としてデータを見る | 学生、部活、所属 |
| DBMS | 論理操作を物理アクセスへ変換する | MySQL、Oracle、SQLite |
| 物理層 | 実際の保存形式 | ファイル、ページ、インデックス |
ポイントは、アプリケーションが物理ファイルを直接扱わず、テーブルという抽象化を通してデータにアクセスできることです。
2. SQL I: テーブル定義と基本問い合わせ
SQLには複数のサブ言語があります。
| 分類 | 役割 | 代表例 |
|---|---|---|
| DDL | データ構造を定義する | CREATE TABLE, ALTER TABLE, DROP TABLE |
| DML | データを操作する | SELECT, INSERT, UPDATE, DELETE |
| DCL | 権限を制御する | GRANT, REVOKE |
| TCL | トランザクションを制御する | BEGIN, COMMIT, ROLLBACK |
テーブル定義の基本
CREATE TABLE student (
sid INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER,
major TEXT
);主キーは行を一意に識別する列です。外部キーは、別テーブルの主キーを参照し、テーブル間の整合性を保つために使います。
CREATE TABLE club_member (
sid INTEGER,
cid INTEGER,
joined_at DATE,
PRIMARY KEY (sid, cid),
FOREIGN KEY (sid) REFERENCES student(sid),
FOREIGN KEY (cid) REFERENCES club(cid)
);基本的に主キーと外部キーは設定しておくべきです。これにより、重複や参照先のないデータを防ぎやすくなります。
SELECT文の基本
SELECT はデータを取得するための文です。
SELECT sid, name, age
FROM student
WHERE major = '計算機科学'
ORDER BY age DESC
LIMIT 10;よく使う句は次の通りです。
| 句 | 役割 |
|---|---|
SELECT |
出力する列や式を指定する |
FROM |
対象テーブルを指定する |
WHERE |
行を絞り込む |
GROUP BY |
グループ化する |
HAVING |
グループ化後に絞り込む |
ORDER BY |
並び替える |
LIMIT |
返す行数を制限する |
図3: SELECT文の論理的な評価順序
| 順番 | 処理 | 例 |
|---|---|---|
| 1 | FROM |
どのテーブルを読むか |
| 2 | JOIN |
どのテーブルを結合するか |
| 3 | WHERE |
どの行を残すか |
| 4 | GROUP BY |
どの単位で集約するか |
| 5 | HAVING |
集約後にどのグループを残すか |
| 6 | SELECT |
何を出力するか |
| 7 | ORDER BY |
どう並べるか |
| 8 | LIMIT |
何行返すか |
SQLは書く順番と評価される順番が異なります。特に、WHERE は集約前、HAVING は集約後に効く点が重要です。
集約とGROUP BY
集約関数は複数行の値から1つの値を返します。
SELECT COUNT(*)
FROM student
WHERE major = '計算機科学';SELECT major, AVG(age), COUNT(*)
FROM student
GROUP BY major;GROUP BY を使うと、個々の行ではなくグループ単位の属性を扱います。そのため、集約されていない列を不用意に SELECT に出すと、意味が曖昧になります。
JOIN
JOINは複数のテーブルを組み合わせて問い合わせる仕組みです。
| JOIN | 意味 |
|---|---|
CROSS JOIN |
直積。すべての組み合わせを作る |
INNER JOIN |
条件に一致する行だけ残す |
LEFT OUTER JOIN |
左テーブルの行を残し、一致しない右側はNULLにする |
SELECT s.name, c.name AS club_name
FROM student s
INNER JOIN club_member cm ON s.sid = cm.sid
INNER JOIN club c ON cm.cid = c.cid;JOINを書くときは、どの列同士が同じ意味を持つのかを明確にすることが重要です。
3. トランザクション
トランザクションは、複数の読み書きを論理的な1つの単位としてまとめる仕組みです。代表例は口座振込です。
BEGIN;
UPDATE account
SET balance = balance - 10000
WHERE account_id = 'A';
UPDATE account
SET balance = balance + 10000
WHERE account_id = 'B';
COMMIT;途中で失敗した場合は ROLLBACK します。トランザクションは、全体として成功するか、全体として失敗するかのどちらかです。
図4: ACID特性
| 特性 | 意味 | 学習上のポイント |
|---|---|---|
| Atomicity | 原子性 | 全部成功するか、全部なかったことにする |
| Consistency | 一貫性 | 処理の前後でデータの制約が壊れない |
| Isolation | 分離性 | 並行実行中のトランザクションが互いに悪影響を与えない |
| Durability | 永続性 | コミットした変更は障害後も残る |
一貫性はDBMSだけでなく、アプリケーション側の設計にも関わります。DBMSが提供する制約だけでなく、業務ルールをどう守るかも重要です。
分離性と読み取り異常
並行実行では、他のトランザクションの影響で読み取り結果が変わることがあります。
| 現象 | 内容 |
|---|---|
| ダーティリード | コミットされていない変更を読んでしまう |
| ファジーリード | 同じ行を読み直したら値が変わる |
| ファントムリード | 同じ条件で検索したのに行が増減する |
分離レベルを上げるほど安全になりますが、並行性や性能とのトレードオフがあります。
4. データベース設計
データベース設計は、要求分析、概念設計、論理設計、物理設計の流れで進みます。
| 段階 | 目的 |
|---|---|
| 要求分析 | 管理したいデータや使われ方を整理する |
| 概念設計 | ERモデルなどで業務世界を表現する |
| 論理設計 | テーブル、主キー、外部キーへ変換する |
| 物理設計 | インデックスや保存方式を決める |
ERモデル
ERモデルでは、実体、属性、関連を使って対象世界を表します。
| 概念 | 意味 | 例 |
|---|---|---|
| 実体 | 管理したい対象 | 学生、部活、科目 |
| 属性 | 実体が持つ性質 | 氏名、年齢、部活名 |
| 関連 | 実体間の関係 | 学生は部活に所属する |
| カーディナリティ | 関係の数の制約 | 1対多、多対多 |
| 参加制約 | 必ず参加するか | 各部活には部長が必要 |
多対多の関連は、そのままテーブルにしにくいため、中間テーブルへ変換します。
CREATE TABLE club_member (
sid INTEGER,
cid INTEGER,
joined_at DATE,
PRIMARY KEY (sid, cid)
);正規化
正規化の目的は、リレーションから冗長性を排除し、更新時異常を起こりにくくすることです。
| 正規形 | 目的 |
|---|---|
| 1NF | 各属性が分解不能な値を持つ |
| 2NF | 主キーの一部だけに依存する非キー属性を排除する |
| 3NF | 非キー属性から非キー属性への依存を排除する |
| BCNF | すべての決定項が候補キーになるようにする |
正規化は万能ではありません。冗長性を減らす一方で、JOINが増える場合もあります。まずは不整合を防げる構造を作り、その後に性能要件を見て物理設計で調整します。
図5: 正規化の見方
| 見つけたい問題 | 典型的な依存 | 対応 |
|---|---|---|
| 同じ値が何度も出る | キーの一部 → 非キー属性 | 2NFへ分解 |
| ある列から別の列が決まる | 非キー属性 → 非キー属性 | 3NFへ分解 |
| キーでない列が決定項になる | 非キー属性 → キーの一部 | BCNFを検討 |
正規化は、データの意味と依存関係を見ながら進めます。単に表を細かく分ける作業ではありません。
5. インデックス
インデックスは検索を高速化するためのデータ構造です。RDBではB+Treeが代表的です。
| ポイント | 内容 |
|---|---|
| 等価検索 | WHERE id = 10 のような検索に効きやすい |
| 範囲検索 | BETWEEN, <, > のような検索にも使える |
| カーディナリティ | 値の種類が多い列ほど効きやすい傾向 |
| 更新コスト | INSERT/UPDATE/DELETE時にインデックス更新が必要 |
インデックスは貼れば貼るほど速くなるものではありません。必要な検索条件に合わせて設計します。
CREATE INDEX idx_student_major ON student (major);カバリングインデックス
検索条件と取得列がインデックスだけで足りると、テーブル本体を読まずに済む場合があります。
CREATE INDEX idx_club_member_covering
ON club_member (cid, joined_at);
SELECT joined_at
FROM club_member
WHERE cid = 10;ただし、DBMSや実行計画によって使われるかは変わります。インデックスを作った後は、実行計画で確認します。
6. 実行計画
実行計画は、DBMSがSQLをどのような手順で処理するかを表したものです。MySQLでは EXPLAIN を使います。
EXPLAIN
SELECT s.name, s.age, c.name
FROM student s
INNER JOIN club_member cm ON s.sid = cm.sid
INNER JOIN club c ON cm.cid = c.cid;| 項目 | 見ること |
|---|---|
table |
アクセス対象テーブル |
type |
アクセス方法 |
possible_keys |
利用可能なインデックス |
key |
実際に選ばれたインデックス |
key_len |
読み取ったインデックス長 |
rows |
スキャン見積もり行数 |
実行計画は推定に基づくため、統計情報が古いと実態とずれることがあります。
図6: 遅いSQLを改善する流れ
| ステップ | やること |
|---|---|
| 1 | 遅いSQLを特定する |
| 2 | EXPLAIN で実行計画を見る |
| 3 | フルスキャン、JOIN順序、利用インデックスを確認する |
| 4 | WHERE句、JOIN条件、SELECT列を見直す |
| 5 | 必要ならインデックスを追加・変更する |
| 6 | 再度 EXPLAIN と実測で確認する |
SQLチューニングは、推測でインデックスを貼るのではなく、計画を見て仮説検証することが重要です。
7. SQL II: サブクエリとCASE式
サブクエリは、SQLの中に別のSQLを入れる書き方です。
SELECT s.name, s.major
FROM student s
WHERE s.sid IN (
SELECT cm.sid
FROM club_member cm
WHERE cm.cid = 10
);CASE式はSQL内で条件分岐を書くために使います。
SELECT
major,
SUM(CASE WHEN age >= 22 THEN 1 ELSE 0 END) AS over_22,
SUM(CASE WHEN age >= 20 AND age < 22 THEN 1 ELSE 0 END) AS between_20_and_21
FROM student
GROUP BY major;CASE式は集計と組み合わせると、条件別の件数集計に便利です。
学習時の重要ポイント
- SQLは書く順番ではなく、論理的な評価順序で理解する。
- JOINは「どの関係を復元しているのか」を意識する。
- トランザクションは、複数の処理を安全な1単位にする仕組み。
- 設計では、ERモデルで意味を整理し、正規化で冗長性を減らす。
- インデックスは検索を速くするが、更新コストも増える。
- 遅いSQLは、必ず実行計画を見てから改善する。
- サブクエリとCASE式は、複雑な問い合わせを表現するための道具。
最後に
この資料は、単なるSQL構文集ではなく、SQLを支えるデータベース理論と実務上の性能改善まで一通り扱っています。初学者はまず SELECT, JOIN, GROUP BY を手で書けるようにし、その後でトランザクション、設計、インデックス、実行計画へ進むと理解しやすくなります。
SQLは「データを取る文法」だけではなく、「データをどう表現し、どう守り、どう速く扱うか」を含む技術です。
コメント