SQL

概要
1. SQLとは何か・なぜ必要か
2. リレーショナルモデルの基礎
3. データ定義言語(DDL)
4. データ操作言語(DML)
5. SELECT文の構造
6. WHERE句と条件
7. JOIN
8. GROUP BYと集約関数
9. サブクエリ
10. CTE(Common Table Expression)
11. ウィンドウ関数
12. インデックス
13. 実行計画(EXPLAIN)
14. トランザクションとACID
15. 分離レベル
16. 制約とトリガー
17. ビューとマテリアライズドビュー
18. ストアドプロシージャと関数
19. データ型と型の選択
20. 正規化と非正規化
21. SQLの方言(MySQL / PostgreSQL / SQLite / SQL Server / Oracle)
22. パフォーマンスチューニング
23. SQLアンチパターンと安全性
24. NoSQLとの比較
25. SQL標準の進化
26. よくある落とし穴FAQ
27. 実践クエリパターン集
28. 学習ロードマップ(30日)
29. 用語集
発展: クエリと設計
実践: 運用とクラウドDB
実例: スキーマ設計
参照: PostgreSQLとJSON
補遺: 実践クエリ集
応用: 大規模データと将来
PostgreSQL 18 の最新機能とMVCC詳細
SQLiteの実装とB-tree
MySQLのストレージエンジン比較
インデックス設計の高度なパターン
実行計画(EXPLAIN)の読み方
まとめ
参考文献

概要

まず、この章の中心構造を図で確認します。細部に入る前に、どの概念がどこへつながるかをつかむための地図です。

flowchart LR A["SQL"] --> B["SELECT"] B --> C["FROM"] C --> D["JOIN"] D --> E["WHERE"] E --> F["GROUP BY"] F --> G["HAVING"] G --> H["ORDER BY"] H --> I["結果セット"] C --> J["実行計画"] J --> K["インデックス"]

コード例の読み方

コード例は、そのまま写すためだけのものではありません。直前の本文で「何を確かめる例か」を押さえ、直後の説明で「どの性質が見えるか」を確認してください。実務では、ここに入力の境界、失敗時の挙動、依存する実行環境を足して読むと判断しやすくなります。

要点

SQL(Structured Query Language)は、リレーショナルデータベースを操作する宣言的問い合わせ言語です。1970年代にIBMの研究から生まれ、ANSI / ISOで標準化された後、PostgreSQL / MySQL / SQLite / SQL Server / Oracleなどの主要DBMSが独自拡張を加えて実装しています。

このページでは、リレーショナルモデル、SELECT/JOIN/GROUP BY、CTE、ウィンドウ関数、インデックス、実行計画、トランザクション、分離レベル、方言の差を整理します。

1. SQLとは何か・なぜ必要か

このセクションでは「SQLがなぜ生まれたのか」「なぜ50年経っても主役なのか」「リレーショナルとは何か」を整理します。

SQLは 「集合論に基づく宣言的問い合わせ言語」。「何が欲しいか」を書けば、DBMSが「どう取るか」を自動的に決めます。

SQL = 集合論的なデータ操作 + 宣言的記述 + 標準化

1-1. リレーショナルデータベースの誕生

1970年、IBMの研究者 Edgar F. Codd が論文「A Relational Model of Data for Large Shared Data Banks」を発表。「データを表(リレーション)として扱い、集合論で問い合わせる」というモデルを提唱しました。

それまでのデータベースは 階層型・ネットワーク型で、プログラマがポインタを辿って明示的にナビゲートする必要がありました。Coddのリレーショナルモデルは「データの物理的配置に依存しない論理的問い合わせ」を可能にした画期でした。

SEQUELからSQLへ

1973  IBMのSystem Rプロジェクト開始
1974  Donald Chamberlin / Raymond BoyceがSEQUELを設計
1979  Oracleが初の商用RDBMSをリリース
1986  ANSIがSQL標準(SQL-86)を制定
1989  ISOがSQL標準を採択
1992  SQL-92(ベースライン)
2003  SQL:2003(ウィンドウ関数)
2016  SQL:2016(行パターン認識・JSON)
2023  SQL:2023(プロパティグラフ)

「SEQUEL」は商標問題で「SQL」に変更(読みは「シークエル」または「エスキューエル」)。

1-2. なぜSQLは今も主役か

50年以上経ってもSQLは健在です。理由は次の通り。

宣言的: 「何を」だけ書けば「どう」はDBMSが決める
数学的基盤: リレーショナル代数による厳密な意味
標準化: ANSI / ISOで多くのDBMSが共通言語
オプティマイザ: クエリを書き換えて高速化(人間が書くより賢い)
トランザクション: ACID保証
エコシステム: 数千のDBMS、ツール、人材

「NoSQLの流行」もありましたが、結局PostgreSQL / MySQL / SQLiteが今も主流です。Spanner / CockroachDB / TiDB など分散DBもSQLインターフェースを採用しています。

1-3. SQLの特性

- 宣言的(手続き的でない)
- 集合志向(行ごとループしない)
- スキーマ駆動(型・制約を事前定義)
- ACIDトランザクション
- 標準 + 各DBMS独自拡張

SQLは 言語というより「DBMSへの問い合わせ仕様」。同じSQLでもDBMSによって実装が異なります。

1-4. このセクションのまとめ

- 1970年Coddの論文がリレーショナルモデルの基礎
- 1986年ANSI標準化、その後継続的に拡張
- 「何が欲しいか」を書く宣言的言語
- リレーショナル代数の集合論的基盤
- 50年経ってもDBMSの主役

2. リレーショナルモデルの基礎

2-1. リレーション(テーブル)

usersテーブル:
+----+-------+-------+-----+
| id | name  | email | age |
+----+-------+-------+-----+
|  1 | Alice | a@b.c | 30  |
|  2 | Bob   | b@b.c | 25  |
|  3 | Carol | c@b.c | 28  |
+----+-------+-------+-----+

テーブル = リレーション
行(row / tuple) = レコード
列(column / attribute) = フィールド
主キー(primary key): 行を一意に識別
外部キー(foreign key): 他テーブルの主キーを参照

2-2. リレーショナル代数の基本演算

σ (selection)        WHERE条件で行を絞る
π (projection)       特定の列だけ抽出
× (cartesian product) 全組み合わせ
⨝ (join)             条件付き結合
∪ (union)            和集合
∩ (intersection)     積集合
- (difference)       差集合

これらがSQLの各句にマッピングされます:

SELECT (π) ... FROM ... WHERE (σ) ... 
SELECT ... FROM A JOIN (⨝) B ...
A UNION B (∪) / A INTERSECT B / A EXCEPT B

2-3. NULLの特殊性

NULL = NULL          -- NULL(trueではない！)
NULL <> NULL         -- NULL
NULL + 1             -- NULL
WHERE x = NULL       -- 何にもマッチしない
WHERE x IS NULL      -- 正しい書き方

NULLは 「不明」を表す3値論理(true / false / null)。集計関数はNULLを無視します。

2-4. このセクションのまとめ

- テーブル = 行と列の集合
- 主キー / 外部キーで参照関係
- リレーショナル代数がSQLの数学的基礎
- NULLは3値論理、IS NULLで判定

3. データ定義言語(DDL)

3-1. CREATE TABLE

CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL,
    age         INTEGER CHECK (age >= 0),
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

PostgreSQLの例。BIGSERIAL はMySQLでは BIGINT AUTO_INCREMENT。

主な制約

PRIMARY KEY主キー
UNIQUE一意制約
NOT NULL            NULL禁止
CHECK (...)         任意条件
DEFAULT valueデフォルト値
REFERENCES (FK)     外部キー

3-2. ALTER TABLE

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users ALTER COLUMN age SET NOT NULL;
ALTER TABLE users ADD CONSTRAINT email_unique UNIQUE (email);
ALTER TABLE users ADD FOREIGN KEY (department_id) REFERENCES departments(id);

3-3. DROP / TRUNCATE

DROP TABLE users;          -- 完全削除
TRUNCATE TABLE users;       -- 中身だけ削除(高速)

3-4. インデックス

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_age ON users(age) WHERE age >= 18;     -- 部分インデックス
CREATE INDEX idx_users_name_lower ON users(LOWER(name));     -- 関数インデックス
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
DROP INDEX idx_users_email;

詳しくは第12章。

3-5. このセクションのまとめ

- CREATE / ALTER / DROP / TRUNCATE
- 制約: PRIMARY KEY / UNIQUE / NOT NULL / CHECK / FOREIGN KEY / DEFAULT
- インデックスで検索高速化
- 各DBMSで型名が少しずつ違う

4. データ操作言語(DML)

4-1. INSERT

INSERT INTO users (name, email, age) VALUES ('Alice', 'a@b.c', 30);

-- 複数行
INSERT INTO users (name, email) VALUES
    ('Alice', 'a@b.c'),
    ('Bob', 'b@b.c');

-- SELECT結果から
INSERT INTO users_archive (id, name)
    SELECT id, name FROM users WHERE deleted = TRUE;

-- RETURNING(PostgreSQL)
INSERT INTO users (name, email) VALUES ('Alice', 'a@b.c')
RETURNING id, created_at;

-- ON CONFLICT(PostgreSQL)
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

-- INSERT IGNORE / ON DUPLICATE KEY UPDATE(MySQL)
INSERT IGNORE INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);

4-2. UPDATE

UPDATE users SET name = 'Alice', age = 31 WHERE id = 1;

UPDATE users SET age = age + 1;     -- 全行(WHERE必須を意識)

UPDATE orders SET total = total * 1.1
WHERE status = 'pending';

-- 別テーブル参照(PostgreSQLのUPDATE FROM)
UPDATE orders SET user_name = u.name
FROM users u WHERE orders.user_id = u.id;

4-3. DELETE

DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE created_at < NOW() - INTERVAL '1 year';
DELETE FROM users;                  -- 全削除(注意)

-- USINGで別テーブル参照
DELETE FROM orders USING users
WHERE orders.user_id = users.id AND users.deleted = TRUE;

4-4. UPSERT(MERGE)

-- PostgreSQL流
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

-- 標準SQL (SQL:2003)
MERGE INTO users AS u
USING (VALUES (1, 'Alice')) AS new (id, name)
ON u.id = new.id
WHEN MATCHED THEN UPDATE SET name = new.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (new.id, new.name);

「あれば更新、なければ挿入」。

4-5. このセクションのまとめ

- INSERT / UPDATE / DELETE
- WHEREを忘れると全行操作(事故の元)
- RETURNING(PostgreSQL)で結果取得
- ON CONFLICT / MERGEでUPSERT

5. SELECT文の構造

5-1. 基本構文

SELECT column_list
FROM table
WHERE condition
GROUP BY column
HAVING condition
ORDER BY column
LIMIT n OFFSET m;

論理的処理順序

書く順序と 実行順序が違うことに注意:

1. FROMテーブルを決定
2. WHERE行を絞る
3. GROUP BYグループ化
4. HAVINGグループに対する絞り込み
5. SELECT列を選択(エイリアス定義)
6. DISTINCT重複除去
7. ORDER BY並び替え
8. LIMIT行数制限

WHERE で SELECT のエイリアスが使えないのはこのため。

5-2. SELECT句

SELECT *                                    -- 全列
SELECT id, name                              -- 特定列
SELECT id AS user_id, name AS user_name      -- エイリアス
SELECT DISTINCT department                    -- 重複除去
SELECT COUNT(*), AVG(age) FROM users        -- 集約

-- 計算式
SELECT name, age * 12 AS age_in_months FROM users;

-- 文字列連結
SELECT first_name || ' ' || last_name AS full_name FROM users;  -- 標準
SELECT CONCAT(first_name, ' ', last_name) FROM users;            -- MySQL

5-3. ORDER BY

SELECT * FROM users ORDER BY age;                       -- 昇順
SELECT * FROM users ORDER BY age DESC;                  -- 降順
SELECT * FROM users ORDER BY age DESC, name ASC;        -- 複数列
SELECT * FROM users ORDER BY 2;                         -- 2列目(非推奨)
SELECT * FROM users ORDER BY age NULLS LAST;            -- NULLの扱い

5-4. LIMIT / OFFSET

SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;     -- 21〜30件目(ページング)

-- SQL:2008標準
SELECT * FROM users
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

5-5. このセクションのまとめ

- SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT
- 論理処理順序: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
- DISTINCTで重複除去
- ASでエイリアス

6. WHERE句と条件

6-1. 比較演算子

WHERE age = 30
WHERE age <> 30        -- 標準(!= でもOK)
WHERE age > 18 AND age <= 65
WHERE name LIKE 'Al%'
WHERE name ILIKE 'al%'  -- 大文字小文字無視(PostgreSQL)
WHERE age BETWEEN 18 AND 65
WHERE id IN (1, 2, 3)
WHERE id NOT IN (1, 2, 3)
WHERE email IS NULL
WHERE email IS NOT NULL
WHERE name SIMILAR TO '(Al|Bo)%'    -- PostgreSQL正規表現
WHERE name ~ '^A.*'                  -- PostgreSQL POSIX正規表現

6-2. 論理演算子

WHERE age >= 18 AND status = 'active'
WHERE department = 'sales' OR department = 'marketing'
WHERE NOT deleted
WHERE (age >= 18 AND status = 'active') OR is_admin

6-3. NULLの扱い

WHERE email IS NULL              -- ○
WHERE email = NULL                -- × (何にもマッチしない)
WHERE email IS NOT NULL          -- ○
WHERE email <> 'a@b.c'           -- NULLの行は除外される！

-- COALESCEでNULLをデフォルト値に
SELECT name, COALESCE(email, 'no email') FROM users;

-- NULLIF(特定値をNULLに)
SELECT NULLIF(name, '');

6-4. パターンマッチ

WHERE name LIKE 'Al%'        -- "Al" で始まる
WHERE name LIKE '%ice'        -- "ice" で終わる
WHERE name LIKE '%li%'        -- "li" を含む
WHERE name LIKE '_lice'       -- 任意1文字 + lice

-- エスケープ
WHERE name LIKE '50\%' ESCAPE '\';

6-5. CASE WHEN

SELECT name,
    CASE
        WHEN age < 18 THEN 'minor'
        WHEN age < 65 THEN 'adult'
        ELSE 'senior'
    END AS age_group
FROM users;

-- 簡潔形
CASE department
    WHEN 'sales' THEN 1
    WHEN 'marketing' THEN 2
    ELSE 0
END;

6-6. このセクションのまとめ

- =, <>, <, >, BETWEEN, IN, IS NULL, LIKE
- AND / OR / NOT
- NULLは特別、IS NULLで判定
- COALESCE / NULLIF
- CASE WHENで条件分岐式

7. JOIN

7-1. INNER JOIN

「両方にマッチする」行のみ。

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

INNER は省略可能。

7-2. LEFT / RIGHT OUTER JOIN

-- 全userとその注文(注文がなくてもuserは表示)
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 逆方向(あまり使わない、LEFTで書き直すのが一般的)
SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

7-3. FULL OUTER JOIN

「どちらかにあれば」表示。両方ない行はない。MySQLは標準サポートなし。

SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

7-4. CROSS JOIN

「全組み合わせ」(直積)。

SELECT * FROM colors CROSS JOIN sizes;
-- 各color × 各sizeの全組み合わせ

7-5. SELF JOIN

-- 上司情報を結合
SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

同じテーブルを2回参照。エイリアス必須。

7-6. JOINの落とし穴

N+1問題

-- アプリ側で:
SELECT * FROM users;                          -- 1クエリ
-- 各userで:
SELECT * FROM orders WHERE user_id = ?;      -- Nクエリ

-- 正解: JOINまたはIN
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

重複の発生

-- 1 userに注文が3つあるとuser情報が3回出る
SELECT u.id, u.name, o.id
FROM users u
JOIN orders o ON u.id = o.user_id;

集約やDISTINCTで対処。

7-7. このセクションのまとめ

- INNER JOIN: 両方マッチ
- LEFT JOIN: 左側全て + マッチした右
- FULL JOIN: どちらかにあれば
- CROSS JOIN: 全組み合わせ
- SELF JOIN: 同じテーブルを再利用
- N+1問題はJOINで解決

8. GROUP BYと集約関数

8-1. 基本

SELECT department, COUNT(*) AS num_users, AVG(age) AS avg_age
FROM users
GROUP BY department;

8-2. 集約関数

COUNT(*)         全行数
COUNT(col)        非NULLのcolの数
COUNT(DISTINCT col)  ユニーク数
SUM(col)
AVG(col)
MIN(col) / MAX(col)
STRING_AGG(col, ',')   -- PostgreSQL(文字列結合)
ARRAY_AGG(col)         -- PostgreSQL(配列化)
JSON_AGG(col)          -- PostgreSQL(JSON配列)
GROUP_CONCAT(col, ',') -- MySQL

8-3. HAVING(集約後の絞り込み)

SELECT department, COUNT(*) AS n
FROM users
GROUP BY department
HAVING COUNT(*) > 5;

WHERE は行に対する絞り込み、HAVING は グループに対する絞り込み。

8-4. GROUPING SETS / ROLLUP / CUBE(高度な集約)

-- 部門ごと、性別ごと、両方ごと、合計
SELECT department, gender, COUNT(*)
FROM users
GROUP BY ROLLUP (department, gender);

-- 部門 × 性別 の全組み合わせ
GROUP BY CUBE (department, gender);

-- 任意の組み合わせ
GROUP BY GROUPING SETS ((department), (gender), ());

8-5. このセクションのまとめ

- GROUP BYでグループ化
- COUNT / SUM / AVG / MIN / MAX
- HAVINGはグループ後の絞り込み
- ROLLUP / CUBE / GROUPING SETSで多次元集約

9. サブクエリ

9-1. スカラーサブクエリ(1値を返す)

SELECT name,
    (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;

9-2. INサブクエリ

SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

9-3. EXISTSサブクエリ

SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = u.id);

-- NOT EXISTS(注文を持たないuser)
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE user_id = u.id);

EXISTS は 存在確認のみで値を返さないため、IN より高速なことが多い。

9-4. インラインVIEW(FROM内サブクエリ)

SELECT department, max_age
FROM (
    SELECT department, MAX(age) AS max_age
    FROM users
    GROUP BY department
) AS t
WHERE max_age > 50;

9-5. 相関サブクエリ

-- 各部門で最高年齢のuser
SELECT * FROM users u1
WHERE age = (
    SELECT MAX(age) FROM users u2 WHERE u2.department = u1.department
);

外側のクエリの値を内側で参照。遅くなりがちなのでCTE / JOINで書き換えを検討。

9-6. このセクションのまとめ

- スカラー / IN / EXISTS / FROM / 相関
- EXISTSは存在確認のみで高速
- 相関サブクエリは遅いことがある
- 多くはCTEやJOINで書き換え可能

10. CTE(Common Table Expression)

WITH 句で 「名前付きの一時テーブル」を定義できる機能。SQL:1999で標準化。

10-1. 基本

WITH active_users AS (
    SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE age >= 18;

「サブクエリに名前を付ける」感覚。可読性が向上。

10-2. 複数CTE

WITH
adults AS (SELECT * FROM users WHERE age >= 18),
their_orders AS (
    SELECT a.id, COUNT(o.id) AS order_count
    FROM adults a
    LEFT JOIN orders o ON o.user_id = a.id
    GROUP BY a.id
)
SELECT * FROM their_orders WHERE order_count > 5;

10-3. 再帰CTE

-- 階層構造(部下、部下の部下、...)
WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL                     -- 起点
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id, s.depth + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id    -- 再帰
)
SELECT * FROM subordinates ORDER BY depth, name;

組織図、ファイルツリー、グラフ走査などで使用。

フィボナッチ数列

WITH RECURSIVE fib(n, a, b) AS (
    SELECT 1, 0, 1
    UNION ALL
    SELECT n + 1, b, a + b FROM fib WHERE n < 10
)
SELECT a FROM fib;

10-4. このセクションのまとめ

- WITH句で名前付き一時テーブル
- 可読性が大幅向上
- 複数CTEで段階的処理
- 再帰CTEで階層・グラフ
- 多くのDBMSでサポート(MySQLは8+)

11. ウィンドウ関数

SQL:2003で標準化された強力な機能。「集約せずにグループ内の計算」ができる。

11-1. 基本構文

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

OVER 句で ウィンドウ(計算範囲)を指定。PARTITION BY でグループ化、GROUP BY と違って 行は集約されない。

11-2. ランキング系

SELECT
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
    RANK()       OVER (ORDER BY salary DESC) AS rk,        -- 同順位はギャップあり
    DENSE_RANK() OVER (ORDER BY salary DESC) AS drk,        -- ギャップなし
    NTILE(4)     OVER (ORDER BY salary DESC) AS quartile    -- 4分位
FROM employees;

「部門ごとのランキング」:

SELECT
    name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

11-3. 値参照系

SELECT
    date,
    sales,
    LAG(sales)  OVER (ORDER BY date) AS prev_day,        -- 前の行
    LEAD(sales) OVER (ORDER BY date) AS next_day,        -- 次の行
    FIRST_VALUE(sales) OVER (ORDER BY date) AS first,
    LAST_VALUE(sales)  OVER (ORDER BY date
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last
FROM daily_sales;

11-4. 移動平均

SELECT
    date,
    sales,
    AVG(sales) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_7day_avg
FROM daily_sales;

「直近7日の平均」を1クエリで。

11-5. 累計

SELECT
    date,
    sales,
    SUM(sales) OVER (ORDER BY date) AS cumulative_sum
FROM daily_sales;

11-6. WINDOW句で再利用

SELECT
    name,
    salary,
    AVG(salary) OVER w AS dept_avg,
    RANK()       OVER w AS dept_rank
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

11-7. このセクションのまとめ

- OVER (PARTITION BY ... ORDER BY ...) で計算範囲
- ROW_NUMBER / RANK / DENSE_RANK / NTILE
- LAG / LEADで行参照
- ROWS BETWEEN ... で範囲指定
- 移動平均・累計・部門ランクが1クエリで
- 「集約しないで集約計算」の革命的機能

12. インデックス

12-1. インデックスとは

「特定列での検索を高速化する補助データ構造」。本の索引と同じ発想。

CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'a@b.c';   -- インデックス利用

12-2. B-treeインデックス

最も一般的。範囲検索(<, >, BETWEEN)と等価検索(=)の両方に効く。

CREATE INDEX idx ON table(col);

複合インデックス

CREATE INDEX idx ON users(department, age);

-- 効く: departmentのみ、department + age
WHERE department = 'sales';
WHERE department = 'sales' AND age > 30;

-- 効かない(または効きにくい): ageのみ
WHERE age > 30;

「列の順序が重要」。最も絞り込める列を先頭に。

12-3. 他のインデックス種別

B-tree汎用、デフォルト
Hash等価検索のみ、PostgreSQLのhash index
GINフルテキスト、JSON、配列
GiST地理空間、フルテキスト
BRIN超大規模、範囲が物理ソート済み
SP-GiST特殊な木構造

12-4. インデックスが効く・効かない条件

-- 効く
WHERE name = 'Alice'
WHERE age BETWEEN 18 AND 65
WHERE name LIKE 'Al%'           -- 前方一致

-- 効きにくい
WHERE LOWER(name) = 'alice'      -- 関数 → 関数インデックスを別途
WHERE name LIKE '%ice'            -- 後方一致
WHERE name <> 'Alice'             -- 否定
WHERE name + '_x' = 'Alice_x'   -- 計算

12-5. インデックスのコスト

利点:
  - SELECTが高速

欠点:
  - INSERT / UPDATE / DELETEが遅くなる(インデックス更新)
  - ディスク容量
  - メモリ

「読み取り重視ならインデックス、書き込み重視なら最小限」。

12-6. このセクションのまとめ

- B-treeインデックスが基本
- 複合インデックスは列順が重要
- 関数や否定はインデックスが効きにくい
- INSERT/UPDATEのコストが増える
- EXPLAINで確認しながら設計

13. 実行計画(EXPLAIN)

13-1. EXPLAINの使い方

EXPLAIN SELECT * FROM users WHERE email = 'a@b.c';
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@b.c';   -- 実際に実行
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;               -- 詳細

13-2. 主要なノード(PostgreSQL)

Seq Scan全表スキャン(インデックスなし)
Index Scanインデックス利用
Index Only Scanインデックスだけで完結(covering)
Bitmap Heap Scan複数インデックス結合
Nested Loop小さい × 大きいJOIN
Hash JoinハッシュテーブルベースJOIN
Merge Joinソート済み集合のJOIN
Sort
Aggregate / GroupAggregate / HashAggregate

13-3. 読み方

Seq Scan on users (cost=0.00..1234.56 rows=1000 width=64)
  Filter: (age > 18)

cost: 推定コスト(最小..最大)
rows: 推定行数
width: 行のバイト数

EXPLAIN ANALYZE で 実際の時間と行数も得られる。

13-4. クエリチューニングの定石

1. EXPLAINで計画を見る
2. Seq Scanが多い → インデックスを検討
3. 行数の推定が大きく外れている → 統計を更新(ANALYZE table)
4. JOINの順序が悪い → CTEで順序を制御
5. ネステッドループが遅い → 結合キーにインデックス

13-5. このセクションのまとめ

- EXPLAIN / EXPLAIN ANALYZE
- Seq Scan / Index Scan / Index Only Scan
- 推定コストと実測時間
- インデックス + 統計が肝

14. トランザクションとACID

14-1. ACID

A - Atomicity原子性、全部成功or全部失敗
C - Consistency一貫性、制約を守る
I - Isolation分離性、並行実行の結果が直列化と等価
D - Durability永続性、コミット後はクラッシュしても残る

14-2. BEGIN / COMMIT / ROLLBACK

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 全部成功ならCOMMIT
COMMIT;

-- 何かおかしいならROLLBACK
-- ROLLBACK;

14-3. SAVEPOINT

BEGIN;
INSERT INTO ...;
SAVEPOINT sp1;
UPDATE ...;
ROLLBACK TO sp1;       -- sp1まで戻す(INSERTは残る)
COMMIT;

14-4. SELECT FOR UPDATE

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;    -- ロック取得
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

「読み取って書き込む」パターンで競合を防ぐ。

14-5. このセクションのまとめ

- ACID 4つの性質
- BEGIN / COMMIT / ROLLBACK
- SAVEPOINTで部分ロールバック
- SELECT FOR UPDATEで行ロック

15. 分離レベル

ANSI SQLの4段階。各DBMSで実装が違う。

READ UNCOMMITTEDダーティリード可(ほぼ使わない)
READ COMMITTEDコミット済みのみ読む(PostgreSQL/Oracleのデフォルト)
REPEATABLE READ同じトランザクション内で同じ読み取り(MySQLのデフォルト)
SERIALIZABLE直列化と等価(最も厳しい)

各レベルで起きうる現象

                Dirty   Non-repeatable  Phantom
                Read    Read            Read
READ UNCOMMITTED ○      ○              ○
READ COMMITTED   ×      ○              ○
REPEATABLE READ  ×      ×              ○ (MySQL InnoDBは ×)
SERIALIZABLE    ×      ×              ×

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

通常は READ COMMITTED で十分。金融など厳密性が必要ならSERIALIZABLE。

16. 制約とトリガー

16-1. 制約

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    total DECIMAL(10, 2) NOT NULL CHECK (total >= 0),
    status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'cancelled')),
    UNIQUE (user_id, created_at)
);

外部キーのON DELETE / ON UPDATE

NO ACTIONエラー(デフォルト)
RESTRICTエラー
CASCADE連鎖削除/更新
SET NULL     NULLに設定
SET DEFAULTデフォルトに設定

16-2. トリガー

CREATE OR REPLACE FUNCTION update_modified()
RETURNS TRIGGER AS $
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_updated
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_modified();

UPDATE 時に updated_at を自動更新。

16-3. このセクションのまとめ

- PRIMARY KEY / UNIQUE / NOT NULL / CHECK / DEFAULT / FK
- ON DELETE CASCADEで連鎖削除
- トリガーで自動処理
- 制約とトリガーは慎重に(性能・デバッグ性に影響)

17. ビューとマテリアライズドビュー

17-1. VIEW

CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE status = 'active';

SELECT * FROM active_users;     -- 普通のテーブルのように使える

「保存されたクエリ」。実体はなく、毎回基底テーブルから計算。

17-2. MATERIALIZED VIEW

CREATE MATERIALIZED VIEW user_stats AS
SELECT department, COUNT(*) AS n, AVG(age) AS avg_age
FROM users
GROUP BY department;

-- 更新(定期実行)
REFRESH MATERIALIZED VIEW user_stats;
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;   -- 読みながら

実体を持つキャッシュビュー。重い集計を毎回実行せずに済む。PostgreSQLでサポート。

17-3. このセクションのまとめ

- VIEW: 仮想テーブル、毎回再計算
- MATERIALIZED VIEW: 実体化、REFRESHで更新
- ビューにインデックスは普通張れない(マテリアライズドなら可)

18. ストアドプロシージャと関数

18-1. PostgreSQLの関数

CREATE OR REPLACE FUNCTION add_user(p_name TEXT, p_email TEXT)
RETURNS BIGINT AS $
DECLARE
    new_id BIGINT;
BEGIN
    INSERT INTO users (name, email) VALUES (p_name, p_email)
    RETURNING id INTO new_id;
    RETURN new_id;
END;
$ LANGUAGE plpgsql;

SELECT add_user('Alice', 'a@b.c');

18-2. ストアドプロシージャvs関数

関数(FUNCTION):
  - 値を返す
  - SELECT内で使える
  
プロシージャ(PROCEDURE):
  - 値を返さない
  - CALLで呼ぶ
  - トランザクション制御可能(COMMIT/ROLLBACK内蔵)

18-3. このセクションのまとめ

- 関数 / プロシージャでDB内ロジック
- 各DBMSで言語が違う(PL/pgSQL, T-SQL, PL/SQL)
- アプリ層でロジックを書くのが現代の主流
- 大量データ処理だけDB内で

19. データ型と型の選択

19-1. 主要型(PostgreSQL)

整数:
  SMALLINT (2 bytes), INTEGER (4), BIGINT (8)
  SMALLSERIAL / SERIAL / BIGSERIAL(自動連番)

数値:
  NUMERIC(precision, scale)(任意精度、金融用)
  REAL (4), DOUBLE PRECISION (8)

文字列:
  CHAR(n)(固定長), VARCHAR(n)(可変長), TEXT(無制限)

日付:
  DATE, TIME, TIMESTAMP, TIMESTAMPTZ(推奨), INTERVAL

その他:
  BOOLEAN
  UUID
  JSON / JSONB(PostgreSQL)
  ARRAY(PostgreSQL)
  BYTEA(バイナリ)
  ENUM

19-2. 型選択の指針

金額:    NUMERIC(DOUBLEは誤差発生)
日時:    TIMESTAMPTZ(タイムゾーン対応)
ID:      BIGSERIAL or UUID
真偽:    BOOLEAN
JSON:    JSONB(PostgreSQL、検索可能)
列挙:    ENUMまたはNOT NULL CHECK
バイナリ: BYTEA / BLOB

19-3. JSON操作(PostgreSQL)

CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    data JSONB
);

INSERT INTO events (data) VALUES
    ('{"type": "click", "user": {"id": 1, "name": "Alice"}}');

-- アクセス
SELECT data->>'type' FROM events;              -- TEXT
SELECT data->'user'->>'name' FROM events;      -- "Alice"
SELECT data#>>'{user,name}' FROM events;        -- 同等

-- 検索
SELECT * FROM events WHERE data @> '{"type": "click"}';
SELECT * FROM events WHERE data ? 'type';

-- インデックス
CREATE INDEX idx_events_data ON events USING GIN (data);

19-4. このセクションのまとめ

- 整数: INTEGER / BIGINT
- 数値: NUMERIC(金融)/ REAL/DOUBLE(科学)
- 文字列: TEXTが無難(PostgreSQL)
- 日時: TIMESTAMPTZ
- JSON / 配列も柔軟(PostgreSQL)

20. 正規化と非正規化

20-1. 正規形

1NF: 各セルが原子値、繰り返しなし
2NF: 1NF + 主キー全体に関数従属
3NF: 2NF + 非キー属性間の従属なし
BCNF: 3NF + すべての関数従属の決定子が候補キー
4NF: 多値従属を排除
5NF: 結合従属を排除

実用上は 3NF で十分なことが多い。

20-2. 非正規化

「性能のため意図的に冗長を持つ」アプローチ。

-- 正規化(読み取り時にJOIN)
SELECT u.name, COUNT(o.id) FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- 非正規化(読み取りが速い、書き込み時に整合性維持)
ALTER TABLE users ADD COLUMN order_count INTEGER DEFAULT 0;
-- 注文時にトリガーで更新

「読み多い」場合に検討。

20-3. このセクションのまとめ

- 1NF / 2NF / 3NF / BCNF
- 通常は3NFで十分
- 非正規化は読み性能を稼ぐ最終手段
- マテリアライズドビューも代替手段

21. SQLの方言(MySQL / PostgreSQL / SQLite / SQL Server / Oracle)

21-1. PostgreSQL

+ 標準SQLに最も忠実
+ 拡張機能が豊富(GIS、JSON、配列、UUID、再帰CTE、ウィンドウ)
+ オープンソース、強力
+ 「DBのスイスアーミーナイフ」
- やや学習コストあり

21-2. MySQL

+ 普及度が圧倒的(特にWeb)
+ 軽量、高速(読み取り)
+ レプリケーションが簡単
- 標準SQLからの逸脱が多い
- 8.0で多くの機能が追加されたが、PostgreSQLに追従中

21-3. SQLite

+ サーバ不要、ファイル1つ
+ 組み込み・モバイル・テスト用に最適
+ 驚くほど機能が豊富
- 並行書き込みが弱い

21-4. SQL Server

+ Microsoft、エンタープライズ
+ T-SQL(拡張)、優秀なGUIツール
- 商用ライセンス
- Azure SQLでクラウド化

21-5. Oracle

+ エンタープライズの定番
+ PL/SQL、機能豊富
- 高価
- 独自拡張が多い

21-6. 主な方言の差

連番:   PostgreSQL: BIGSERIAL / IDENTITY / SEQUENCE
        MySQL:      AUTO_INCREMENT
        SQLite:     INTEGER PRIMARY KEY
        SQL Server: IDENTITY

LIMIT:  PostgreSQL/MySQL/SQLite: LIMIT n OFFSET m
        SQL Server: TOP n / OFFSET m FETCH NEXT n
        Oracle:    FETCH FIRST n ROWS ONLY

文字列連結: 標準: ||
        MySQL: CONCAT()
        SQL Server: +

日付:   各DBMSで関数名が違う
        NOW() / CURRENT_TIMESTAMP / SYSDATE / GETDATE()

UPSERT:  PostgreSQL/SQLite: ON CONFLICT
        MySQL:               ON DUPLICATE KEY UPDATE
        SQL Server/Oracle:   MERGE

21-7. このセクションのまとめ

- PostgreSQL: 機能と標準準拠、第一推奨
- MySQL: 普及度、Webの定番
- SQLite: 組み込み・モバイル・テスト
- SQL Server: エンタープライズMicrosoft
- Oracle: エンタープライズ商用
- 方言差を意識して書く

22. パフォーマンスチューニング

22-1. 計測

EXPLAIN ANALYZE SELECT ...;
\timing                              -- psqlで時間表示
SET log_min_duration_statement = '1s';   -- 遅いクエリをログ

22-2. 高速化のチェックリスト

1. EXPLAINを読む
2. 必要な列だけSELECT(SELECT * は遅い)
3. WHERE条件に効くインデックス
4. JOIN順序の検討
5. 統計の更新(ANALYZE)
6. 正規化の見直し
7. パーティショニング(巨大テーブル)
8. キャッシュ(マテリアライズドビュー、Redis)
9. 接続プーリング(pgbouncer等)
10. 設定チューニング(shared_buffers, work_mem等)

22-3. インデックスの落とし穴

- 多すぎる: 書き込みが遅くなる
- 使われない: ALTERで削除
- 関数を避ける: WHERE LOWER(col) = ... → 関数インデックスを別途
- 順序: 複合インデックスは選択性の高い順

22-4. このセクションのまとめ

- EXPLAIN ANALYZEが出発点
- インデックスは諸刃の剣
- 統計が古いと計画が悪化
- 接続プーリング・キャッシュも組み合わせる

23. SQLアンチパターンと安全性

23-1. SQLインジェクション

-- 危険！
"SELECT * FROM users WHERE name = '" + userInput + "'"

-- ユーザが ' OR '1'='1を入力 → 全行取得

対策: パラメータ化クエリ

# Python (psycopg2)
cur.execute("SELECT * FROM users WHERE name = %s", (user_input,))

// Node.js (pg)
client.query('SELECT * FROM users WHERE name = $1', [user_input])

絶対に文字列連結でSQLを組み立てない。

23-2. その他のアンチパターン

- SELECT *: 不要な列で帯域・メモリ浪費
- LIKE '%xxx%': 後方一致はインデックス効かない
- ORDER BY RAND(): 全行ソート、超遅い
- NOT IN (NULLを含むサブクエリ): 全部NULLになる
- カラムにJSONを詰めて検索: NoSQLを別途検討
- EAVモデル(Entity-Attribute-Value): クエリが複雑化
- 過度な正規化(5NF等): JOIN多すぎ

23-3. このセクションのまとめ

- パラメータ化クエリでSQL Injection防止
- SELECT * を避ける
- LIKE '%...%' は遅い、別途インデックス
- ORDER BY RAND() は禁忌
- NULLの扱いに注意

24. NoSQLとの比較

RDBMS:
  + ACID、強い整合性
  + JOIN、複雑なクエリ
  + 標準SQLで互換性
  - スケールアウトが難しい(最近は分散SQLで改善)

NoSQL:
  + スケールアウトしやすい
  + スキーマレス、柔軟
  - 結合が貧弱
  - 各ストア独自API

ドキュメントDB:    MongoDB, CouchDB
キーバリュー:      Redis, DynamoDB, Memcached
カラムナ:           Cassandra, HBase, BigTable
グラフ:             Neo4j, ArangoDB
時系列:             InfluxDB, TimescaleDB
全文検索:           Elasticsearch, Solr
ベクトル:           Pinecone, Weaviate, pgvector

最近は 「PostgreSQL + 拡張で大半カバー」が主流(JSON、全文検索、ベクトル、時系列)。

25. SQL標準の進化

SQL-86 (1986)   ANSI初版
SQL-89軽い修正
SQL-92ベースライン、JOIN、トランザクション分離
SQL:1999        OLAP(CUBE/ROLLUP)、CTE、トリガー、user-defined types
SQL:2003ウィンドウ関数、XML、MERGE、IDENTITY
SQL:2006        XML強化
SQL:2008        OFFSET/FETCH
SQL:2011時間列、ウィンドウ拡張
SQL:2016        JSON、行パターン認識
SQL:2019多次元配列
SQL:2023プロパティグラフ

26. よくある落とし穴FAQ

Q1. NULL = NULLは？

NULL(trueではない！)。IS NULL を使う。

Q2. `SELECT *` は何が悪い？

不要な列を読む(帯域・メモリ)、インデックスのカバリングが効かない、スキーマ変更で挙動変化。

Q3. `LIKE '%abc%'` が遅い

前方ワイルドカードはインデックス効かない。全文検索(PostgreSQL tsvector、Elasticsearch)を検討。

Q4. JOINすると行が増える

1対多関係の自然な挙動。集約(GROUP BY)か DISTINCT で対処。

Q5. UNIONとUNION ALL

UNION は重複除去(遅い)、UNION ALL は重複含む(速い)。可能ならALL。

Q6. INNER JOINの代わりにWHEREで結合

SELECT * FROM a, b WHERE a.id = b.a_id は古いスタイル。JOIN を使う。

Q7. インデックスを張ったのに使われない

統計が古い(ANALYZE)、選択性が低い、関数を使っている、型の暗黙変換、など。EXPLAIN で確認。

Q8. トランザクションが終わらない

長時間の BEGIN がロックを保持。アプリのコードで COMMIT/ROLLBACK を確実に。

Q9. ORDER BYなしの順序

保証されない。ORDER BY を必ず指定。

Q10. COUNT(*) とCOUNT(col) の違い

COUNT(*) は全行、COUNT(col) はcolがNULLでない行。

27. 実践クエリパターン集

27-1. ページング

-- 単純(小さなオフセットでOK)
SELECT * FROM posts ORDER BY id LIMIT 10 OFFSET 20;

-- カーソルベース(大きなページでも速い)
SELECT * FROM posts WHERE id > $last_seen_id ORDER BY id LIMIT 10;

27-2. 重複行の削除

DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id) FROM users GROUP BY email
);

27-3. ピボット

SELECT
    department,
    SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male,
    SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female
FROM employees
GROUP BY department;

-- PostgreSQLのcrosstab / FILTER
SELECT
    department,
    COUNT(*) FILTER (WHERE gender = 'M') AS male,
    COUNT(*) FILTER (WHERE gender = 'F') AS female
FROM employees
GROUP BY department;

27-4. ランニングトータル

SELECT
    date,
    sales,
    SUM(sales) OVER (ORDER BY date) AS running_total
FROM daily_sales;

27-5. 「最新の各userの注文」

SELECT DISTINCT ON (user_id) user_id, id, total, created_at
FROM orders
ORDER BY user_id, created_at DESC;

PostgreSQL独自の DISTINCT ON。標準SQLでは:

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders
) t
WHERE rn = 1;

27-6. 親子階層

WITH RECURSIVE tree AS (
    SELECT id, name, parent_id, 0 AS depth, ARRAY[name] AS path
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, t.depth + 1, t.path || c.name
    FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT REPEAT('  ', depth) || name FROM tree ORDER BY path;

27-7. このセクションのまとめ

- カーソルベースページング
- ピボット(CASE / FILTER)
- ウィンドウ関数で累計・ランキング
- DISTINCT ONまたはROW_NUMBERで「最新の各〜」
- 再帰CTEで階層

28. 学習ロードマップ(30日)

Week 1: 基礎

DBMSインストール(PostgreSQL or SQLite)
DDL: CREATE TABLE / ALTER / DROP
DML: INSERT / UPDATE / DELETE
SELECT基本

Week 2: 中級

JOIN(INNER / LEFT / FULL)
GROUP BY / HAVING
サブクエリ
インデックス基礎

Week 3: 上級

CTE / 再帰
ウィンドウ関数
EXPLAIN読解
トランザクション / 分離レベル

Week 4: 実践

自分のアプリで本格SQL
性能チューニング
ER図設計
マイグレーション運用

29. 用語集

あ行

ACID: Atomicity / Consistency / Isolation / Durability
インデックス: 検索高速化補助構造(B-treeが標準)
オプティマイザ: 実行計画を決めるDB内コンポーネント

か行

カーディナリティ: 列のユニーク値数
クエリプラン: 実行計画
クラスタリング: テーブルの物理ソート / インデックス

さ行

正規化: データの重複を排除する設計
整合性制約: PRIMARY KEY / FK / UNIQUE / CHECK等

た行

トランザクション: 一連の操作を原子的に
トリガー: イベント駆動の自動処理

な行

ネステッドループジョイン: ループで結合

は行

ハッシュジョイン: ハッシュ表ベース
パーティショニング: テーブルを分割
ビュー: 仮想テーブル

ま行

マテリアライズドビュー: 実体化ビュー

A〜Z

ACID: トランザクション特性
B-tree: 標準インデックス
CTE: Common Table Expression
DDL/DML/DCL: Data Definition / Manipulation / Control Language
GIN/GiST: 拡張インデックス(PostgreSQL)
OLAP / OLTP: 分析用 / トランザクション用
ORM: Object-Relational Mapping
RDBMS: Relational Database Management System
WAL: Write-Ahead Log

発展: クエリと設計

ここからはSQLの各機能を 実例とともに深掘り。実行計画、インデックス設計、パフォーマンスチューニング、レプリケーション、シャーディング、現代的な拡張機能まで詳細に。

31. インデックス設計の深掘り

31-1. B-treeインデックスの内部

B-tree:
  - balanced tree(高さがほぼ均一)
  - 各ノードに複数のキー
  - O(log n) で検索・挿入・削除

PostgreSQL B-tree:
  - 各ページ8KB
  - 高さ通常3〜5(数十億行でも)

範囲スキャン(<, >, BETWEEN)も等価検索(=)も両方効く。

31-2. 複合インデックスの選択性

CREATE INDEX idx ON orders (user_id, status, created_at);

「選択性の高い列を先頭」が原則。

効くWHERE条件:
  user_id = ?
  user_id = ? AND status = ?
  user_id = ? AND status = ? AND created_at > ?
  
効きにくい:
  status = ?              (user_idがないと範囲スキャン)
  created_at > ?

Index Skip Scan(PostgreSQL 17+ / Oracle)

「先頭列を飛ばしたインデックス利用」。一部DBがサポートし始めた高度な最適化。

31-3. カバリングインデックス

CREATE INDEX idx ON users (email) INCLUDE (name, created_at);

「インデックスだけでクエリが完結」する。テーブル本体を読まなくていい。Index Only Scan で超高速。

EXPLAIN SELECT name, created_at FROM users WHERE email = 'a@b.c';
-- Index Only Scan using idx ...

31-4. 部分インデックス(Partial Index)

-- アクティブユーザのみインデックス(テーブル全体より小さい)
CREATE INDEX idx_active_users ON users (last_login) WHERE active = TRUE;

「条件を満たすデータだけ」のインデックス。サイズ削減 + 速度向上。PostgreSQLの強み。

31-5. 関数インデックス

-- LOWER(name) で検索したいとき
CREATE INDEX idx_users_name_lower ON users (LOWER(name));

SELECT * FROM users WHERE LOWER(name) = 'alice';   -- このインデックスを使う

31-6. このセクションのまとめ

- B-treeが標準、O(log n)
- 複合インデックスは選択性順
- INCLUDEでカバリング
- WHERE句で部分インデックス
- 関数インデックスで式マッチ

32. EXPLAINの読み方

32-1. PostgreSQLの典型的な計画

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 10;

Limit  (cost=1234.56..1234.59 rows=10 width=...)
  ->  Sort  (cost=1234.56..1235.06 rows=200 width=...)
        Sort Key: (count(o.id)) DESC
        Sort Method: top-N heapsort
        ->  HashAggregate (cost=...)
              Group Key: u.id, u.name
              ->  Hash Right Join (cost=...)
                    Hash Cond: (o.user_id = u.id)
                    ->  Seq Scan on orders o (cost=...)
                    ->  Hash (cost=...)
                          ->  Index Scan using idx_users_created on users u
                                Index Cond: (created_at >= '2024-01-01')

32-2. 重要な指標

cost=startup..total推定コスト
rows推定行数
width                    1行のバイト数
actual time実際の時間(ANALYZE時)
loops実行回数

Key操作:
  Seq Scan:       全表スキャン(インデックスなし)
  Index Scan:     インデックス利用
  Index Only Scan: カバリング
  Bitmap Scan:    複数インデックス結合
  Nested Loop:    入れ子ループJOIN
  Hash Join:      ハッシュJOIN(大量データ向き)
  Merge Join:     ソート済みのマージ
  Sort:           並び替え
  HashAggregate / GroupAggregate: GROUP BY

32-3. ボトルネックの見つけ方

1. 一番下のノード(実行順序の最初)から見る
2. costとactual timeが乖離 → 統計古い → ANALYZE
3. Seq Scanで大量行 → インデックス追加
4. Nested Loopで外側が大量 → Hash Join検討
5. Sortでmemory不足 → work_mem増加
6. rows推定が大きく外れている → 統計問題

32-4. このセクションのまとめ

- EXPLAIN ANALYZEで実行
- cost / rows / actual timeの差を見る
- Seq Scanが多い → インデックス
- 統計古い → ANALYZE table
- 推定が外れる → ANALYZE / VACUUM

33. JOINアルゴリズム詳細

33-1. Nested Loop Join

for each row in outer:
    for each row in inner:
        if match: output

時間計算量: O(n × m)
インデックスがあれば: O(n × log m)

外側が 小さいテーブルのとき最適。

33-2. Hash Join

1. 内側テーブルからハッシュ表を構築
2. 外側を1行ずつスキャンしてハッシュ表を引く

時間計算量: O(n + m)
メモリ: O(m)

両方とも大きいテーブル向き。

33-3. Merge Join

両方のテーブルをソート済みにして、マージ

時間計算量: O(n + m)(ソート済みなら)
ソートコスト: O(n log n + m log m)

ソート列で結合する場合に最適。

33-4. オプティマイザの選択

DBMSが 統計情報から最適なアルゴリズムを自動選択。SET enable_hashjoin = OFF; などで一時的に無効化して比較できる(PostgreSQL)。

33-5. このセクションのまとめ

- Nested Loop: 外側小さいときに最適
- Hash Join: 大量データに最適
- Merge Join: ソート済みデータ
- 自動選択、ヒントは原則不要

34. トランザクションとMVCC

34-1. MVCC(Multi-Version Concurrency Control)

PostgreSQL / Oracle / MySQL InnoDBが採用。「書き込みは新しい行バージョンを作る」仕組み。

従来のロック: 読み取りも書き込みもロック
MVCC: 読み取りはロックなし、書き込みのみロック

これにより 「読み取りは書き込みをブロックしない」「書き込みは読み取りをブロックしない」を実現。並行性が劇的に向上。

34-2. PostgreSQLのMVCC

各行にxmin(作成トランザクション)とxmax(削除トランザクション)
読み取り時に現在のトランザクションから「見える行」だけ表示

VACUUMの必要性

古い行バージョンが溜まる → VACUUMで清掃

VACUUM users;            -- 死んだ行を回収
VACUUM ANALYZE users;    -- + 統計更新
VACUUM FULL users;       -- 完全再構築(テーブルロック)

PostgreSQLはautovacuumが自動実行。

34-3. ロック種別(PostgreSQL)

ACCESS SHARE        SELECT
ROW SHARE           SELECT FOR UPDATE / SHARE
ROW EXCLUSIVE       INSERT / UPDATE / DELETE
SHARE UPDATE EXCLUSIVE  VACUUM / CREATE INDEX CONCURRENTLY
SHARE              CREATE INDEX
SHARE ROW EXCLUSIVE
EXCLUSIVE
ACCESS EXCLUSIVE   ALTER TABLE / DROP TABLE

34-4. デッドロック検出

-- セッション1
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 待機...
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;

-- セッション2
BEGIN;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
-- 待機...
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- → 自動的にデッドロック検出、片方を中止

DBMSが 自動検出して片方をrollback。

34-5. このセクションのまとめ

- MVCCで読み書き並行
- VACUUMで古いバージョン清掃
- ロックは行単位 / テーブル単位
- デッドロックは自動検出

35. 高度なクエリパターン

35-1. 階層クエリ(再帰CTE)

-- 部下の部下の部下...全部を取る
WITH RECURSIVE descendants AS (
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE id = 1                      -- 起点
    UNION ALL
    SELECT e.id, e.name, e.manager_id, d.depth + 1
    FROM employees e
    JOIN descendants d ON e.manager_id = d.id
)
SELECT * FROM descendants ORDER BY depth, name;

35-2. ピボット(行→列)

-- PostgreSQLのcrosstab(tablefunc拡張)
SELECT * FROM crosstab(
    'SELECT department, gender, COUNT(*) FROM employees GROUP BY 1, 2 ORDER BY 1, 2',
    'VALUES (''M''), (''F'')'
) AS ct (department TEXT, male INT, female INT);

-- 標準SQL(FILTER句、PostgreSQL)
SELECT
    department,
    COUNT(*) FILTER (WHERE gender = 'M') AS male,
    COUNT(*) FILTER (WHERE gender = 'F') AS female
FROM employees
GROUP BY department;

35-3. ギャップを埋める時系列

-- 1日も売上がない日も0で表示
WITH days AS (
    SELECT generate_series('2024-01-01'::date, '2024-01-31'::date, '1 day'::interval)::date AS day
)
SELECT
    days.day,
    COALESCE(SUM(o.total), 0) AS daily_sales
FROM days
LEFT JOIN orders o ON o.created_at::date = days.day
GROUP BY days.day
ORDER BY days.day;

35-4. ウィンドウ関数の応用

-- 移動平均(直近7日)
SELECT
    date,
    sales,
    AVG(sales) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_7day_avg,
    
    -- ランニングトータル
    SUM(sales) OVER (ORDER BY date) AS cumulative,
    
    -- 前日比
    sales - LAG(sales) OVER (ORDER BY date) AS day_over_day,
    
    -- 順位
    RANK() OVER (PARTITION BY EXTRACT(YEAR FROM date) ORDER BY sales DESC) AS yearly_rank
FROM daily_sales;

35-5. データ更新の最適化

-- 大量UPDATEはLIMIT付きで分割
UPDATE users SET status = 'archived'
WHERE id IN (
    SELECT id FROM users
    WHERE last_login < NOW() - INTERVAL '1 year'
    LIMIT 1000
);
-- これを繰り返す

巨大テーブルの一括更新で ロック時間短縮。

35-6. このセクションのまとめ

- 再帰CTEで階層
- FILTERでピボット風集計
- generate_series + LEFT JOINでギャップ埋め
- ウィンドウ関数で移動平均・累計・前日比
- LIMIT付きUPDATEで大量更新を分割

36. ストアドプロシージャと関数

36-1. PostgreSQLの関数

CREATE OR REPLACE FUNCTION add_user(
    p_name TEXT,
    p_email TEXT
) RETURNS BIGINT AS $
DECLARE
    v_id BIGINT;
BEGIN
    INSERT INTO users (name, email) VALUES (p_name, p_email)
    RETURNING id INTO v_id;
    
    INSERT INTO audit_log (action, target_id) VALUES ('user_created', v_id);
    
    RETURN v_id;
EXCEPTION
    WHEN unique_violation THEN
        RAISE EXCEPTION 'Email already exists';
END;
$ LANGUAGE plpgsql;

SELECT add_user('Alice', 'a@b.c');

36-2. PL/pgSQLの制御フロー

DO $
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT id FROM users LOOP
        UPDATE users SET visited = visited + 1 WHERE id = rec.id;
    END LOOP;
END $;

36-3. ストアドプロシージャvs関数

関数(FUNCTION):
  - 値を返す
  - SELECT内で使える
  - トランザクション制御不可

プロシージャ(PROCEDURE):
  - 値を返さない(OUTパラメータは可)
  - CALLで呼ぶ
  - COMMIT / ROLLBACK可能

36-4. このセクションのまとめ

- DBMS内ロジックを関数 / プロシージャ
- PL/pgSQL(PostgreSQL)/ T-SQL(SQL Server)/ PL/SQL(Oracle)
- 大量データ処理はDB内が速い
- アプリ層との分担を考える

37. パーティショニング

巨大テーブルを 論理的・物理的に分割して管理する手法。

37-1. 範囲パーティション

CREATE TABLE orders (
    id BIGSERIAL,
    user_id BIGINT,
    created_at TIMESTAMPTZ,
    total DECIMAL
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

メリット

クエリで関係するパーティションだけ走査(partition pruning)
古いデータの一括削除がDROP PARTITIONで瞬時
インデックスサイズ縮小

37-2. リストパーティション

CREATE TABLE users (
    id BIGSERIAL,
    region TEXT
) PARTITION BY LIST (region);

CREATE TABLE users_us PARTITION OF users FOR VALUES IN ('us', 'ca');
CREATE TABLE users_eu PARTITION OF users FOR VALUES IN ('uk', 'de', 'fr');

37-3. ハッシュパーティション

CREATE TABLE orders (
    id BIGSERIAL,
    user_id BIGINT
) PARTITION BY HASH (user_id);

CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_p1 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 1);
-- ...

37-4. このセクションのまとめ

- 範囲 / リスト / ハッシュパーティション
- 巨大テーブルでメリット
- partition pruningで高速化
- 古いデータの一括削除が楽

38. レプリケーション

38-1. ストリーミングレプリケーション(PostgreSQL)

Primary(書き込み)
  ↓ WAL(Write-Ahead Log)をストリーム送信
Replica 1(読み取り専用)
Replica 2(読み取り専用)

特徴:
  - 物理的なバイト単位コピー
  - Replicaは完全な同一コピー
  - 同期 / 非同期を選択可

38-2. 論理レプリケーション

-- Primary側
CREATE PUBLICATION my_pub FOR TABLE users, orders;

-- Replica側
CREATE SUBSCRIPTION my_sub CONNECTION 'host=primary' PUBLICATION my_pub;

「特定テーブル・特定操作のみレプリケート」。バージョン違いのDB間でも可。

38-3. レプリケーションの読み書き分離

書き込み → Primary
読み取り → Replica

「読み取りスケール」が大きく改善。注意点:

- レプリケーション遅延(数ms 〜 数秒)
- 書き込み直後はPrimaryを読むなど配慮

38-4. このセクションのまとめ

- ストリーミング(物理)/ 論理レプリケーション
- 同期 / 非同期
- 読み書き分離で性能向上
- 遅延は常に意識

39. シャーディング

「水平分割」(行を複数DBに分散)。レプリケーションが「コピー」、シャーディングは「分割」。

全user 100M
  ↓ user_idでハッシュ
Shard 1: user_id % 4 = 0
Shard 2: user_id % 4 = 1
Shard 3: user_id % 4 = 2
Shard 4: user_id % 4 = 3

課題

クロスシャードJOINが困難
グローバル一意性
再シャーディング時の移行

現代の選択肢

分散SQL(自動シャーディング):
  CockroachDB / TiDB / Spanner / YugabyteDB
  → SQLを書くだけで分散して動く

アプリ層シャーディング:
  Vitess(MySQL)/ Citus(PostgreSQL)
  → 既存RDBMSを分散化

40. NoSQLとの比較と現代の選択

RDBMS:
  + ACID、強い整合性
  + 標準SQL、汎用性
  + 複雑なクエリ
  + 30年の運用知識
  - スケールアウトが難しい

NoSQL:
  + スケールアウト容易
  + スキーマレス(柔軟)
  - 複雑クエリが困難
  - 整合性が弱め
  - ベンダーロックイン

現代の選択:
  - PostgreSQL + 拡張で大半カバー
    JSONB / 全文検索 / ベクトル / 時系列 / 配列
  - 分散SQL(CockroachDB等)でスケール
  - 特殊用途のみNoSQL(Redis、Elasticsearch)

41. PostgreSQLの現代的拡張機能

41-1. JSONB

CREATE TABLE events (
    id BIGSERIAL,
    data JSONB
);

INSERT INTO events (data) VALUES
    ('{"type": "click", "user": {"id": 1, "name": "Alice"}, "tags": ["important"]}');

-- アクセス
SELECT data->>'type' FROM events;
SELECT data->'user'->>'name' FROM events;
SELECT data#>>'{user,name}' FROM events;

-- 検索
SELECT * FROM events WHERE data @> '{"type": "click"}';
SELECT * FROM events WHERE data ? 'type';
SELECT * FROM events WHERE data @? '$.tags[*] ? (@ == "important")';

-- インデックス
CREATE INDEX idx_events_data_gin ON events USING GIN (data);
CREATE INDEX idx_events_type ON events ((data->>'type'));

「スキーマレスなMongoDBをPostgreSQLで」。多くのNoSQL用途を代替。

41-2. 全文検索(pg_trgm / tsvector)

-- tsvectorで本格的な全文検索
CREATE INDEX idx_articles_search ON articles
    USING GIN (to_tsvector('japanese', title || ' ' || body));

SELECT * FROM articles
WHERE to_tsvector('japanese', title || ' ' || body) @@ to_tsquery('japanese', '機械学習 & 深層');

41-3. pgvector(ベクトル検索)

CREATE EXTENSION vector;

CREATE TABLE items (
    id BIGSERIAL,
    embedding VECTOR(1536)
);

CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops);

-- 類似検索
SELECT * FROM items ORDER BY embedding <-> '[0.1, 0.2, ...]' LIMIT 10;

「RAG / セマンティック検索をPostgreSQLで」。

41-4. TimescaleDB(時系列)

CREATE EXTENSION timescaledb;

CREATE TABLE metrics (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INT,
    value DOUBLE PRECISION
);

SELECT create_hypertable('metrics', 'time');

-- 時系列に最適化された格納

41-5. PostGIS(地理空間)

CREATE EXTENSION postgis;

CREATE TABLE places (
    id SERIAL PRIMARY KEY,
    name TEXT,
    location GEOGRAPHY(POINT, 4326)
);

-- 5km以内
SELECT * FROM places
WHERE ST_DWithin(location, ST_GeogFromText('POINT(139.7 35.6)'), 5000);

41-6. このセクションのまとめ

- JSONBでMongoDB代替
- tsvectorで全文検索
- pgvectorでAI用ベクトル検索
- TimescaleDBで時系列
- PostGISで地理空間
- 「PostgreSQLですべて」が現代の主流

42. クエリチューニング実例

42-1. ケース1: SELECT * を避ける

-- Bad: 全列取得
SELECT * FROM users WHERE id = 1;

-- Good: 必要な列だけ
SELECT id, name, email FROM users WHERE id = 1;

帯域削減 + Index Only Scanの可能性。

42-2. ケース2: COUNT(*) の最適化

-- 完全なcount: 遅い(全行スキャン)
SELECT COUNT(*) FROM users;

-- 概算でOKなら推定値
SELECT reltuples::BIGINT FROM pg_class WHERE relname = 'users';

-- 条件付きcount
SELECT COUNT(*) FROM users WHERE active = TRUE;
-- ↑ 部分インデックスがあれば高速

42-3. ケース3: NOT INの落とし穴

-- NULLを含むサブクエリだと全部NULLに
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned);

-- 解決
SELECT * FROM users WHERE id NOT IN (
    SELECT user_id FROM banned WHERE user_id IS NOT NULL
);

-- またはNOT EXISTS
SELECT * FROM users u WHERE NOT EXISTS (
    SELECT 1 FROM banned WHERE user_id = u.id
);

42-4. ケース4: ORをUNIONで

-- 遅い
SELECT * FROM users WHERE email = ? OR phone = ?;

-- 速い(インデックスが効く)
SELECT * FROM users WHERE email = ?
UNION
SELECT * FROM users WHERE phone = ?;

42-5. このセクションのまとめ

- SELECT * は避ける
- COUNT(*) は重い、推定で済むならpg_class
- NOT INはNULL注意、NOT EXISTSが安全
- ORをUNIONで書き換えると速いことも

43. データベース設計パターン

43-1. ソフトデリート

-- DELETEしないで論理削除
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;

-- 部分インデックスで「生きている」だけ
CREATE INDEX idx_users_active ON users (id) WHERE deleted_at IS NULL;

-- ビューで便利に
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;

43-2. 監査ログ

CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    record_id BIGINT NOT NULL,
    action TEXT NOT NULL,    -- 'INSERT', 'UPDATE', 'DELETE'
    old_data JSONB,
    new_data JSONB,
    user_id BIGINT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION audit_trigger() RETURNS TRIGGER AS $
BEGIN
    INSERT INTO audit_log (table_name, record_id, action, old_data, new_data)
    VALUES (
        TG_TABLE_NAME,
        COALESCE(NEW.id, OLD.id),
        TG_OP,
        row_to_json(OLD)::JSONB,
        row_to_json(NEW)::JSONB
    );
    RETURN COALESCE(NEW, OLD);
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_audit
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_trigger();

43-3. オプティミスティックロック

ALTER TABLE products ADD COLUMN version INTEGER DEFAULT 0;

UPDATE products
SET name = 'New', version = version + 1
WHERE id = 1 AND version = 5;    -- 取得時のversion

-- 0行更新ならコンフリクト → アプリ側で再試行

43-4. このセクションのまとめ

- ソフトデリートで履歴保持
- 監査ログトリガーで全変更記録
- バージョン列でオプティミスティックロック
- イベントソーシングは別パラダイム

44. SQLアンチパターン集

1. SELECT * を本番で
2. ORDER BYなしの順序依存
3. LIKE '%abc%' を巨大テーブルで
4. ORDER BY RAND()
5. EAVモデル(Entity-Attribute-Value)
6. IDのVARCHAR化(INTで十分)
7. 文字列での日付保存
8. 浮動小数点での金額
9. インデックスなしの巨大テーブル
10. 過度な正規化(5NF等)
11. トリガー多用
12. テーブル名 / 列名の命名不統一
13. NULLの意味曖昧
14. SELECT INTO outfile(脆弱性)
15. クライアントでJOIN
16. N+1問題
17. 誤ったcharset
18. ENUMの乱用(変更困難)
19. UNIONの代わりにORの乱用
20. EXISTSの代わりにCOUNT > 0

45. SQL学習リソース

書籍

『SQLアンチパターン』Bill Karwin
『Use The Index, Luke!』Markus Winand(オンライン無料)
『High Performance MySQL』Schwartz et al.
『PostgreSQL 14内部構造』

Web

use-the-index-luke.com(インデックス)
pgexercises.com(PostgreSQL演習)
sqlzoo.net(基礎演習)
sql-bolt.com(インタラクティブ)

コミュニティ

/r/sql(Reddit)
/r/postgresql
DB Weekly newsletter
pgconf各種カンファレンス

実践: 運用とクラウドDB

47. SQL各方言の詳細

47-1. PostgreSQLの独自機能

-- DISTINCT ON
SELECT DISTINCT ON (user_id) user_id, created_at, total
FROM orders
ORDER BY user_id, created_at DESC;

-- 配列型
CREATE TABLE posts (id BIGSERIAL, tags TEXT[]);
INSERT INTO posts (tags) VALUES (ARRAY['tech', 'sql']);
SELECT * FROM posts WHERE tags @> ARRAY['sql'];
SELECT * FROM posts WHERE 'sql' = ANY(tags);

-- LATERAL JOIN
SELECT u.name, recent.*
FROM users u,
LATERAL (
    SELECT * FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 5
) recent;

-- RETURNING
INSERT INTO users (name) VALUES ('Alice') RETURNING id, created_at;
UPDATE users SET active = TRUE WHERE id = 1 RETURNING *;

-- generate_series
SELECT generate_series(1, 10);
SELECT generate_series('2024-01-01'::date, '2024-12-31', '1 day');

-- 範囲型
CREATE TABLE bookings (room INT, period TSTZRANGE);
INSERT INTO bookings VALUES (101, '[2024-01-01 09:00, 2024-01-01 11:00)');
SELECT * FROM bookings WHERE period && '[2024-01-01 10:00, 2024-01-01 12:00)';

47-2. MySQLの独自機能

-- AUTO_INCREMENT
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));

-- ON DUPLICATE KEY UPDATE
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- LIMIT 1, 10(OFFSET 1, LIMIT 10)
SELECT * FROM users LIMIT 1, 10;

-- ストレージエンジン
CREATE TABLE t (...) ENGINE=InnoDB;

-- 全文検索(MyISAM/InnoDB)
CREATE FULLTEXT INDEX idx ON articles(body);
SELECT * FROM articles WHERE MATCH(body) AGAINST('SQL');

-- JSON関数
SELECT JSON_EXTRACT(data, '$.user.name') FROM events;

47-3. SQLiteの独自

-- 型は緩い(type affinity)
CREATE TABLE t (id INTEGER, name TEXT);
INSERT INTO t VALUES ('not-a-number', 42);  -- 通る！

-- WITHOUT ROWID(通常は内部rowidを持つ)
CREATE TABLE t (k INTEGER PRIMARY KEY, v TEXT) WITHOUT ROWID;

-- ATTACH DATABASE
ATTACH 'other.db' AS other;
SELECT * FROM other.users;

47-4. SQL Serverの独自

-- TOP
SELECT TOP 10 * FROM users ORDER BY created_at DESC;

-- T-SQLの制御文
DECLARE @count INT;
SELECT @count = COUNT(*) FROM users;
PRINT @count;

-- TRY...CATCH
BEGIN TRY
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE();
END CATCH;

-- IDENTITY
CREATE TABLE users (id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(100));

47-5. Oracleの独自

-- ROWNUM(疑似列)
SELECT * FROM (SELECT * FROM users ORDER BY id) WHERE ROWNUM <= 10;

-- DUAL(1行のダミー表)
SELECT SYSDATE FROM DUAL;
SELECT 'Hello' FROM DUAL;

-- NUMBER型
CREATE TABLE prices (amount NUMBER(10, 2));

-- PL/SQL
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM users;
    DBMS_OUTPUT.PUT_LINE(v_count);
END;
/

47-6. このセクションのまとめ

- PostgreSQL: 機能豊富、標準準拠
- MySQL: AUTO_INCREMENT / ON DUPLICATE KEY
- SQLite: 型がゆるい
- SQL Server: TOP / T-SQL
- Oracle: ROWNUM / DUAL / PL/SQL

48. 性能監視と運用

48-1. 遅いクエリの発見

-- PostgreSQLのpg_stat_statements
CREATE EXTENSION pg_stat_statements;

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- ログ設定
SET log_min_duration_statement = '1s';   -- 1秒以上のクエリをログ

48-2. ロック確認

-- PostgreSQLのロック確認
SELECT
    pid,
    locktype,
    relation::regclass,
    mode,
    granted
FROM pg_locks
WHERE NOT granted;

-- 長時間トランザクション
SELECT
    pid,
    age(clock_timestamp(), xact_start),
    state,
    query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY xact_start;

48-3. テーブルサイズ

SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

48-4. 統計情報の更新

ANALYZE users;                           -- 統計更新
ANALYZE VERBOSE users;
VACUUM (VERBOSE, ANALYZE) users;          -- VACUUM + ANALYZE

48-5. このセクションのまとめ

- pg_stat_statementsで遅いクエリ
- pg_locksでロック状況
- pg_stat_activityでアクティブセッション
- ANALYZEで統計更新(オプティマイザに重要)

49. SQLのDevOps

49-1. マイグレーション

ツール:
  - Liquibase
  - Flyway
  - sqitch
  - Alembic(Python)
  - ActiveRecord Migration(Ruby)
  - Prisma Migrate(Node)
  - golang-migrate(Go)
  - dbmate

-- V1__create_users.sql
CREATE TABLE users (...);

-- V2__add_email_index.sql
CREATE INDEX idx_users_email ON users(email);

49-2. オンラインスキーマ変更

-- 巨大テーブルへのALTER TABLEは危険(ロック)
-- pg_repack / gh-ost / pt-online-schema-changeを使う

-- PostgreSQLのCREATE INDEX CONCURRENTLY
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- ロックを最小化

49-3. バックアップ

# PostgreSQL
pg_dump -U user dbname > backup.sql
pg_dump -F c dbname > backup.dump
pg_restore -U user -d dbname backup.dump

# MySQL
mysqldump -u user -p dbname > backup.sql
mysql -u user -p dbname < backup.sql

# 物理バックアップ
pg_basebackup -D /backup -U replica -P

49-4. このセクションのまとめ

- マイグレーションツールでスキーマバージョン管理
- 巨大テーブルへのALTERは要注意
- CREATE INDEX CONCURRENTLYでロック最小化
- 論理 / 物理バックアップ

50. クラウドDB

PostgreSQL互換:
  AWS RDS PostgreSQL
  AWS Aurora PostgreSQL(高可用 + 高性能)
  Google Cloud SQL PostgreSQL
  Azure Database for PostgreSQL
  Supabase(PostgreSQLベースのBaaS)
  Neon(serverless PostgreSQL)
  CockroachDB(分散SQL、PostgreSQL互換)

MySQL互換:
  AWS RDS / Aurora MySQL
  Google Cloud SQL MySQL
  PlanetScale(serverless MySQL)

その他:
  Snowflake(DWH)
  BigQuery(DWH)
  Redshift(DWH)
  ClickHouse(OLAP)
  TiDB(HTAP)

51. NoSQLとの使い分け(再考)

PostgreSQLで済むケース:
  - 中小規模アプリ
  - 複雑なクエリが必要
  - ACIDが必要
  - JSONでも十分(JSONB)
  - 全文検索(tsvector)
  - ベクトル検索(pgvector)

NoSQLが向くケース:
  - 単純なKV(Redis)
  - グローバル分散(DynamoDB)
  - 全文検索特化(Elasticsearch)
  - グラフDB(Neo4j)
  - 時系列特化(InfluxDB)
  - メッセージング(Kafka)

「まずPostgreSQL、必要ならNoSQLを補助に」が現代の主流。

52. SQLの未来

進化の方向:
  - 分散SQL(Spanner、CockroachDB、TiDB)
  - HTAP(OLTP + OLAP融合)
  - ベクトル検索の標準化
  - JSON / グラフ / 時系列の統合
  - LLM連携(自然言語 → SQL)

新しいSQL機能:
  - MATCH_RECOGNIZE(行パターン)
  - JSON演算子の標準化
  - プロパティグラフ(SQL:2023)

SQLの未来は「古い言語が置き換わる」方向ではなく、「SQLの外にあったデータ形態をSQLが飲み込む」方向に進んでいる。JSON、時系列、地理空間、全文検索、ベクトル検索、ストリーム処理、グラフ探索が、専用エンジンだけでなくSQLインターフェースから扱えるようになってきた。

これは実務上大きい。分析者、アプリケーション開発者、データエンジニア、機械学習エンジニアが、完全に別々の言語ではなくSQLを共通語として使えるからである。もちろん、すべてをSQLに寄せればよいわけではない。低レイテンシの探索、複雑なグラフアルゴリズム、巨大な機械学習パイプラインでは専用ツールが必要になる。

今後のSQL学習では、標準SQLだけでなく、PostgreSQL、MySQL、SQLite、BigQuery、Snowflake、DuckDBのような実装ごとの差も読む必要がある。標準を知ることは移植性の土台になり、実装固有機能を知ることは現場の生産性につながる。

53. SQLチートシート

-- 基本
SELECT * FROM t WHERE c = ?;
INSERT INTO t (c1, c2) VALUES (?, ?);
UPDATE t SET c = ? WHERE id = ?;
DELETE FROM t WHERE id = ?;

-- JOIN
SELECT * FROM a JOIN b ON a.id = b.a_id;
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id;

-- 集計
SELECT category, COUNT(*), AVG(price)
FROM products GROUP BY category HAVING COUNT(*) > 5;

-- ウィンドウ
SELECT name, salary,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk
FROM employees;

-- CTE
WITH cte AS (SELECT ... FROM t WHERE ...)
SELECT * FROM cte WHERE ...;

-- 再帰
WITH RECURSIVE r AS (
    SELECT id, parent FROM t WHERE parent IS NULL
    UNION ALL
    SELECT t.id, t.parent FROM t JOIN r ON t.parent = r.id
)
SELECT * FROM r;

-- UPSERT(PostgreSQL)
INSERT INTO t (id, v) VALUES (1, 'a')
ON CONFLICT (id) DO UPDATE SET v = EXCLUDED.v;

実例: スキーマ設計

55. 実プロジェクトのスキーマ設計

55-1. ECサイト

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    category_id BIGINT REFERENCES categories(id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_products_category ON products(category_id);

CREATE TABLE categories (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id BIGINT REFERENCES categories(id)
);

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    status VARCHAR(20) NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    total DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_orders_user ON orders(user_id, created_at DESC);
CREATE INDEX idx_orders_status ON orders(status) WHERE status IN ('pending', 'paid');

CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price DECIMAL(10, 2) NOT NULL,
    UNIQUE (order_id, product_id)
);

55-2. SNS

CREATE TABLE users (...);

CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    author_id BIGINT NOT NULL REFERENCES users(id),
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_posts_author_created ON posts(author_id, created_at DESC);

CREATE TABLE follows (
    follower_id BIGINT NOT NULL REFERENCES users(id),
    followee_id BIGINT NOT NULL REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (follower_id, followee_id),
    CHECK (follower_id != followee_id)
);

CREATE INDEX idx_follows_followee ON follows(followee_id);

CREATE TABLE likes (
    user_id BIGINT NOT NULL REFERENCES users(id),
    post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (user_id, post_id)
);

-- タイムライン取得
SELECT p.* FROM posts p
JOIN follows f ON f.followee_id = p.author_id
WHERE f.follower_id = $1
ORDER BY p.created_at DESC
LIMIT 20;

55-3. 監査ログ

CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    record_id BIGINT NOT NULL,
    action VARCHAR(20) NOT NULL,
    old_data JSONB,
    new_data JSONB,
    user_id BIGINT,
    occurred_at TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY RANGE (occurred_at);

-- 月次パーティション
CREATE TABLE audit_log_2024_01 PARTITION OF audit_log
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

55-4. このセクションのまとめ

- 主キー / 外部キー / インデックスを設計
- 適切な型・制約
- 複合インデックスの順序
- パーティショニングで巨大データ

56. ER図と正規化の実例

56-1. 第3正規形(3NF)

1NF: 各セル原子値
2NF: 1NF + 主キー全体に従属
3NF: 2NF + 推移的従属なし

例：usersテーブル
  Bad: (id, name, dept_id, dept_name)  -- dept_nameはdept_idに従属
  Good:
    users (id, name, dept_id)
    departments (id, name)

56-2. 非正規化のタイミング

推奨される非正規化:
  - 読み取り頻度が圧倒的
  - 集計が重い(counterキャッシュ)
  - 変更頻度が低い

例:
  users.followers_count  -- 集計をキャッシュ
  posts.like_count
  
  トリガーやbatchで更新

56-3. このセクションのまとめ

- 通常3NFで十分
- 性能のための非正規化は最終手段
- マテリアライズドビューも代替

57. SQL学習ロードマップ詳細

Week 1: 基礎

DBMSインストール(PostgreSQL)
DDL(CREATE TABLE)
DML(INSERT/UPDATE/DELETE)
基本SELECT

Week 2: クエリ

WHERE / ORDER BY / LIMIT
JOIN(INNER/LEFT/FULL)
GROUP BY / HAVING
サブクエリ

Week 3: 上級

CTE / 再帰CTE
ウィンドウ関数
インデックス基礎
EXPLAIN読解

Week 4: 実践

自分のアプリで設計
マイグレーション運用
性能測定
大量データ処理

Week 5: 運用

バックアップ / リストア
レプリケーション
監視(pg_stat_statements)
セキュリティ

Week 6: 高度

パーティショニング
全文検索 / JSON / pgvector
ストアドプロシージャ
TimescaleDB / PostGIS

58. データベースの世界観

58-1. CAP定理

分散システムはConsistency / Availability / Partition tolerance
の3つを同時には保証できない(2つまで)

CP: 整合性優先(PostgreSQLレプリ、HBase)
AP: 可用性優先(Cassandra、DynamoDB)
CA: 単一サーバ(実用的にはない)

58-2. ACID vs BASE

ACID(伝統的RDBMS):
  Atomicity / Consistency / Isolation / Durability

BASE(一部NoSQL):
  Basically Available / Soft state / Eventually consistent

58-3. このセクションのまとめ

- CAP定理を理解
- ACIDとBASEの使い分け
- 強整合性vs結果整合性
- 業務要件で選択

参照: PostgreSQLとJSON

60. 主要関数リファレンス(PostgreSQL)

60-1. 文字列関数

LENGTH('hello')                    -- 5
CHAR_LENGTH('日本語')               -- 3
UPPER('hello')                     -- 'HELLO'
LOWER('HELLO')                     -- 'hello'
INITCAP('hello world')             -- 'Hello World'
TRIM(' hello ')                    -- 'hello'
LTRIM(' hello')
RTRIM('hello ')
REPLACE('hello', 'l', 'L')         -- 'heLLo'
SUBSTRING('hello' FROM 2 FOR 3)    -- 'ell'
POSITION('l' IN 'hello')           -- 3
CONCAT('a', 'b', 'c')              -- 'abc'
'a' || 'b' || 'c'                  -- 'abc'
LPAD('5', 3, '0')                  -- '005'
RPAD('5', 3, '0')                  -- '500'
SPLIT_PART('a,b,c', ',', 2)        -- 'b'
STRING_TO_ARRAY('a,b,c', ',')      -- {a,b,c}
ARRAY_TO_STRING(ARRAY['a','b'], ',')  -- 'a,b'
REGEXP_REPLACE('a1b2', '\d', 'X', 'g')  -- 'aXbX'
REGEXP_MATCH('abc123', '(\w+?)(\d+)')   -- {abc,123}

60-2. 数値関数

ABS(-5)                            -- 5
CEIL(3.2)                          -- 4
FLOOR(3.7)                         -- 3
ROUND(3.567, 2)                    -- 3.57
TRUNC(3.567, 2)                    -- 3.56
MOD(10, 3)                         -- 1
POWER(2, 10)                       -- 1024
SQRT(16)                           -- 4
EXP(1)                             -- 2.718...
LOG(100)                           -- 2 (log10)
LN(2.718)                          -- ~1
RANDOM()                           -- 0 < x < 1

60-3. 日時関数

NOW()                              -- 現在時刻(タイムゾーン付)
CURRENT_TIMESTAMP
CURRENT_DATE
CURRENT_TIME
LOCALTIMESTAMP                     -- TZなし

AGE(TIMESTAMP '2020-01-01')        -- 経過期間
EXTRACT(YEAR FROM NOW())           -- 2024
DATE_PART('hour', NOW())           -- 14
DATE_TRUNC('month', NOW())         -- 月初

NOW() + INTERVAL '1 day'
NOW() - INTERVAL '7 days'
NOW() + '1 year 2 months'::interval

TO_CHAR(NOW(), 'YYYY-MM-DD')       -- '2024-04-29'
TO_DATE('2024-04-29', 'YYYY-MM-DD')

60-4. 集約関数

COUNT(*) / COUNT(col) / COUNT(DISTINCT col)
SUM(col)
AVG(col)
MIN(col) / MAX(col)
STDDEV(col) / VARIANCE(col)
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col)  -- 中央値
MODE() WITHIN GROUP (ORDER BY col)
STRING_AGG(col, ', ')
ARRAY_AGG(col)
JSON_AGG(col)

60-5. このセクションのまとめ

- 文字列・数値・日時・集約の組み込み関数が豊富
- DBMSによって名前が違うので注意
- 正規表現はPOSIX準拠

61. JSONB完全活用

-- 基本
SELECT data->>'name' FROM events;
SELECT data->'user'->>'id' FROM events;
SELECT data#>>'{user,profile,bio}' FROM events;

-- 検索
WHERE data @> '{"type": "click"}'
WHERE data ? 'key'
WHERE data ?| ARRAY['a', 'b']      -- aまたはb
WHERE data ?& ARRAY['a', 'b']      -- aかつb

-- 配列
SELECT jsonb_array_elements(data->'tags') FROM events;
SELECT jsonb_array_length(data->'items') FROM events;

-- 操作
SELECT data || '{"new": "value"}'::jsonb     -- マージ
SELECT jsonb_set(data, '{user,name}', '"Alice"'::jsonb)
SELECT data - 'remove_this_key'

-- インデックス
CREATE INDEX ON events USING GIN (data);
CREATE INDEX ON events USING GIN (data jsonb_path_ops);    -- @> 専用、小さい
CREATE INDEX ON events ((data->>'type'));

62. 完全な実例：マイクロブログDB

-- ユーザ
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    bio TEXT,
    avatar_url VARCHAR(500),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

CREATE INDEX idx_users_active ON users(id) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_username ON users(LOWER(username));

-- 投稿
CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    author_id BIGINT NOT NULL REFERENCES users(id),
    content TEXT NOT NULL CHECK (LENGTH(content) <= 280),
    reply_to_id BIGINT REFERENCES posts(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

CREATE INDEX idx_posts_author ON posts(author_id, created_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX idx_posts_search ON posts USING GIN (to_tsvector('english', content));

-- フォロー
CREATE TABLE follows (
    follower_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    followee_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (follower_id, followee_id),
    CHECK (follower_id != followee_id)
);

CREATE INDEX idx_follows_followee ON follows(followee_id);

-- いいね
CREATE TABLE likes (
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (user_id, post_id)
);

CREATE INDEX idx_likes_post ON likes(post_id);

-- マテリアライズドビュー(人気投稿)
CREATE MATERIALIZED VIEW trending_posts AS
SELECT p.*, COUNT(l.user_id) AS like_count
FROM posts p
LEFT JOIN likes l ON l.post_id = p.id
WHERE p.created_at > NOW() - INTERVAL '24 hours'
  AND p.deleted_at IS NULL
GROUP BY p.id
ORDER BY like_count DESC, p.created_at DESC
LIMIT 100;

CREATE UNIQUE INDEX ON trending_posts(id);
-- 定期REFRESH
-- REFRESH MATERIALIZED VIEW CONCURRENTLY trending_posts;

-- タイムラインクエリ
SELECT p.*, u.username, u.avatar_url,
       COUNT(l.user_id) AS like_count
FROM posts p
JOIN users u ON p.author_id = u.id
JOIN follows f ON f.followee_id = p.author_id
LEFT JOIN likes l ON l.post_id = p.id
WHERE f.follower_id = $1
  AND p.deleted_at IS NULL
GROUP BY p.id, u.id
ORDER BY p.created_at DESC
LIMIT 20;

63. クライアントからSQLを呼ぶ

63-1. Python(psycopg2)

import psycopg2

conn = psycopg2.connect("dbname=mydb user=postgres")
cur = conn.cursor()

# パラメータ化クエリ(SQL Injection防止)
cur.execute("SELECT * FROM users WHERE id = %s", (1,))
user = cur.fetchone()

# 一括挿入
cur.executemany(
    "INSERT INTO logs (level, message) VALUES (%s, %s)",
    [("INFO", "started"), ("ERROR", "failed")]
)
conn.commit()
cur.close()
conn.close()

63-2. Node.js(pg)

const { Client } = require('pg');
const client = new Client({ connectionString: process.env.DATABASE_URL });
await client.connect();

const res = await client.query('SELECT * FROM users WHERE id = $1', [1]);
console.log(res.rows);

await client.end();

63-3. Go(database/sql + pq)

import (
    "database/sql"
    _ "github.com/lib/pq"
)

db, _ := sql.Open("postgres", url)
defer db.Close()

var name string
err := db.QueryRow("SELECT name FROM users WHERE id = $1", 1).Scan(&name)

63-4. このセクションのまとめ

- どの言語からもSQLを呼べる
- パラメータ化クエリでSQL Injection防止
- Connection Poolを使う
- ORMかQuery Builderを選ぶ

65. SQL拡張FAQ(追加)

Q21. プリペアドステートメントとは

PREPARE q (int) AS SELECT * FROM users WHERE id = $1;
EXECUTE q(1);
DEALLOCATE q;

クエリを コンパイル済みで保持。同じ形のクエリが多いと高速。アプリのDBドライバが自動で使うことが多い。

Q22. インデックスを多くつけすぎると？

書き込みのコストが増える。読み取り頻度vs書き込み頻度で判断。

Q23. NULLを埋めるべきか

NULLは「不明」を意味する。デフォルト値で埋めると意味が変わる。意味的にNULLが正しいならNULL。

Q24. EXISTSとIN

WHERE EXISTS (SELECT 1 FROM ...)   -- 早期終了、速いことが多い
WHERE id IN (SELECT id FROM ...)    -- サブクエリ全評価

NULLの挙動も違うのでEXISTSが安全。

Q25. UNIONとUNION ALL

UNION       -- 重複除去(ソート発生、遅い)
UNION ALL   -- 重複そのまま(速い)

可能ならALL。

Q26. シーケンスとは

CREATE SEQUENCE my_seq;
SELECT NEXTVAL('my_seq');
SELECT CURRVAL('my_seq');

-- BIGSERIALは内部でシーケンス

Q27. ロックがかかる操作

ALTER TABLE              -- ACCESS EXCLUSIVE(テーブル全体)
CREATE INDEX             -- SHARE
CREATE INDEX CONCURRENTLY -- 軽いロック(推奨)
TRUNCATE                 -- ACCESS EXCLUSIVE
DROP TABLE              -- ACCESS EXCLUSIVE

巨大テーブルへのALTERは慎重に。

Q28. WITHOUT ROWIDの意味

SQLiteで主キーをそのまま物理キーに(rowidを持たない)。検索性能向上、サイズ削減。

Q29. 接続プールが必要か

必須。1リクエストで接続作成・破棄するとオーバーヘッド大。pgbouncer / アプリ側プールを使う。

Q30. DBAになるためのスキル

- SQL完全マスター
- インデックス・実行計画
- バックアップ / リストア
- レプリケーション
- パーティショニング
- 監視 / アラート
- セキュリティ
- 容量計画
- パフォーマンスチューニング

66. SQLリファレンス編の補足

50年経ってもSQLは主役。あなたのSQLの旅が、データに裏付けされた価値を生み出すものでありますように。

COMMIT;

この補足で強調したいのは、SQLの学習は文法暗記で終わらないという点である。SELECT, JOIN, GROUP BY を書けるだけでは、実務のデータ問題には届かない。スキーマ設計、インデックス、トランザクション、実行計画、権限、移行、監査、バックアップまで含めて、初めて「データを扱える」と言える。

リファレンスとして使う場合は、構文を調べたあとに必ず「このクエリはどのデータ量で、どのインデックスで、どの分離レベルで動くか」を考える。SQLは宣言的なので、書いた順番と実行順序が一致しない。実行計画を読む習慣が、初級者と実務者を分ける。

補遺: 実践クエリ集

67. ビジネス系クエリ集

-- 月別売上
SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS order_count,
    SUM(total) AS total_sales,
    AVG(total) AS avg_order_value
FROM orders
WHERE status = 'paid'
GROUP BY 1
ORDER BY 1;

-- リテンション分析(コホート)
WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(created_at)) AS cohort_month
    FROM orders
    GROUP BY user_id
),
activities AS (
    SELECT
        c.cohort_month,
        DATE_TRUNC('month', o.created_at) AS activity_month,
        COUNT(DISTINCT o.user_id) AS active_users
    FROM cohorts c
    JOIN orders o ON o.user_id = c.user_id
    GROUP BY c.cohort_month, DATE_TRUNC('month', o.created_at)
)
SELECT * FROM activities ORDER BY cohort_month, activity_month;

-- top N(部門ごとに上位3名)
SELECT * FROM (
    SELECT
        name, department, salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
) t WHERE rn <= 3;

-- 最大の連続日数
WITH daily_login AS (
    SELECT user_id, DATE(created_at) AS day
    FROM logins
    GROUP BY user_id, DATE(created_at)
),
groups AS (
    SELECT
        user_id, day,
        day - INTERVAL '1 day' * (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day)) AS grp
    FROM daily_login
)
SELECT user_id, COUNT(*) AS streak
FROM groups
GROUP BY user_id, grp
ORDER BY streak DESC LIMIT 10;

68. 分析系クエリ集

-- パーセンタイル
SELECT
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY response_time) AS median,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_time) AS p95,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY response_time) AS p99
FROM api_logs;

-- ABテスト結果
SELECT
    variant,
    COUNT(*) AS users,
    AVG(converted::int) AS conversion_rate,
    STDDEV(converted::int) / SQRT(COUNT(*)) AS standard_error
FROM ab_test_results
GROUP BY variant;

-- 移動平均
SELECT
    date,
    sales,
    AVG(sales) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS sma_7,
    AVG(sales) OVER (
        ORDER BY date
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS sma_30
FROM daily_sales;

69. メンテナンス系クエリ

-- テーブルサイズ
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS indexes
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- 使われていないインデックス
SELECT
    schemaname, tablename, indexname,
    idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- 重複インデックス
SELECT
    indrelid::regclass AS table,
    array_agg(indexrelid::regclass) AS indexes
FROM pg_index
GROUP BY indrelid, indkey
HAVING COUNT(*) > 1;

-- ロング・ランニング・クエリ
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > INTERVAL '1 minute'
ORDER BY duration DESC;

70. SQLの哲学

SQLを学ぶことは:
  - 集合論を学ぶこと
  - 宣言的プログラミングを体験すること
  - データに対する考え方を磨くこと
  - 30年以上使えるスキルを得ること

SQLの不変な真実:
  - データはテーブル
  - クエリは集合操作
  - インデックスで高速化
  - トランザクションで整合性
  - レプリケーション / シャーディングでスケール

SQLの哲学は、手続きではなく関係を記述することにある。「この順番でループして取り出す」ではなく、「どの集合から、どの条件で、どの形の結果を得たいか」を宣言する。実行方法はオプティマイザに委ねる。この分業が、SQLを長寿命な言語にしている。

同時に、宣言的であることは責任が軽いという意味ではない。曖昧な条件、意図しない重複、不正確なJOIN、NULLの扱い、暗黙の型変換は、見た目には正しい結果を返してしまうことがある。SQLでは「動くクエリ」と「意味が正しいクエリ」を分けて考える必要がある。

良いSQLを書く人は、データの意味を読む。テーブル名、キー、制約、履歴の持ち方、削除の扱い、時刻の意味、集計単位を理解してからクエリを書く。SQLはデータベースとの対話であると同時に、業務の前提を検証する道具でもある。

応用: 大規模データと将来

72. SQLチューニングのケーススタディ

72-1. ケース：注文一覧1,000倍高速化

-- Before(10秒)
SELECT
    o.id, o.created_at, u.name,
    (SELECT SUM(quantity * price) FROM order_items WHERE order_id = o.id) AS total
FROM orders o
JOIN users u ON o.user_id = u.id
ORDER BY o.created_at DESC
LIMIT 100;

-- 問題: スカラサブクエリが各行で実行される

-- After(10ms)
SELECT
    o.id, o.created_at, u.name,
    SUM(oi.quantity * oi.price) AS total
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.created_at, u.name
ORDER BY o.created_at DESC
LIMIT 100;

-- + INDEX(orders.created_at) で更に高速

72-2. ケース：N+1問題の解決

-- 悪い例(1 + Nクエリ)
SELECT * FROM users WHERE active = TRUE;
-- アプリで各userに対して:
SELECT COUNT(*) FROM orders WHERE user_id = ?;

-- 良い例(1クエリ)
SELECT u.*, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = TRUE
GROUP BY u.id;

72-3. ケース：ページング高速化

-- OFFSETベース(オフセットが大きいと遅い)
SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 100000;

-- カーソルベース(高速)
SELECT * FROM posts WHERE id > $last_seen_id ORDER BY id LIMIT 20;

72-4. このセクションのまとめ

- スカラサブクエリ → JOIN + GROUP BY
- N+1 → JOIN
- OFFSET → カーソルベース
- 必ずEXPLAIN ANALYZEで確認

73. 大規模データへの戦略

73-1. データ量別アプローチ

〜100万行:        単純なPostgreSQLでOK
〜1億行:          インデックス + パーティション
〜10億行:         アーカイブ戦略 + 読み取りレプリカ
10億行〜:        分散SQL(CockroachDB / Spanner)またはNoSQL

73-2. アーカイブ戦略

-- 古いデータを別テーブルへ
INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < NOW() - INTERVAL '2 years';
DELETE FROM orders WHERE created_at < NOW() - INTERVAL '2 years';

-- またはパーティションをDROP
ALTER TABLE orders DETACH PARTITION orders_2022_01;

73-3. このセクションのまとめ

- 適切なツールをデータ量で選ぶ
- アーカイブで古いデータを退避
- 分散SQLで水平スケール

74. SQLを超えて

SQLの延長:
  - GraphQL(APIレイヤー)
  - PostgREST(PostgreSQL → REST)
  - Hasura(PostgreSQL → GraphQL)
  - Supabase(PostgreSQLベースBaaS)
  - Prisma(型安全なSQL)
  - dbt(データ変換)
  - Airbyte / Fivetran(データ同期)

新しいSQL:
  - SQL on Big Data(Athena、BigQuery、Snowflake)
  - SQL on streams(Materialize、ksqlDB)
  - SQL on AI(DuckDB + LLM)

SQLの外側には、API、ORM、BI、ETL、Reverse ETL、データカタログ、ワークフローエンジンが広がっている。これらはSQLを隠すこともあれば、SQLを生成することもある。重要なのは、ツールが何を抽象化し、どこでSQLに戻る必要があるかを理解することだ。

ORMはアプリケーション開発を楽にするが、N+1、過剰なJOIN、意図しないトランザクション境界を生むことがある。BIツールは分析を民主化するが、指標定義が散らばると部署ごとに数字が変わる。dbtのような変換ツールはSQLを開発資産として扱いやすくするが、依存関係とテストを整えなければ複雑さは増える。

SQLを超えるとは、SQLを捨てることではない。SQLが得意な領域を中心に置きつつ、アプリケーション、分析、機械学習、運用の境界で適切な道具を選ぶことである。

75. SQLの永続性

50年経って今も主役のSQL。今後50年も主役であり続ける可能性が高い。

理由:
  - 数学的基盤の強さ
  - 標準化の威力
  - ユーザベースの巨大さ
  - 既存資産の膨大さ
  - LLMがSQLを生成しやすい

SQLが長く残る理由は、互換性だけではない。リレーショナルモデルは、業務データの多くを表現するのに十分に強く、同時に人間が読める形を保っている。表、キー、制約、結合、集計という道具は、会計、物流、ユーザー管理、監査、分析のような領域に非常によく合う。

また、SQLは組織の記憶でもある。長年蓄積されたスキーマ、レポート、ビュー、ストアドプロシージャ、ダッシュボード、運用手順は、簡単には置き換えられない。新しいデータ基盤が登場しても、SQLインターフェースを持つことが採用の条件になりやすい。

LLM時代にもSQLの価値は残る。自然言語からクエリを生成できても、正しいテーブルを選び、指標の意味を確認し、結果を検証する力は必要である。むしろSQLを理解している人ほど、生成されたクエリの危うさを見抜き、データに基づく判断を守れる。

SELECT 'SQL is forever!' AS prediction;

PostgreSQL 18 の最新機能とMVCC詳細

PostgreSQL 18.3 リリースの新機能

PostgreSQL公式ドキュメント(postgresql.org)では、PostgreSQL 18.3(2026年2月26日リリース)の機能が詳述されています。

NOTE

PostgreSQL は4ヶ月ごとにマイナーバージョンを更新し、メジャーバージョンは1年ごとにリリースされます(postgresql.org)。

PostgreSQL 18での主要な改善

EXPLAIN コマンドの強化: 実行計画の出力がさらに詳細に
ロック機構の最適化: SELECT FOR UPDATE でのスケーラビリティ向上
トランザクション分離: より厳密な SERIALIZABLE の実装
インデックス: B-treeインデックスの圧縮機能

MVCC(Multi-Version Concurrency Control)の詳細

PostgreSQLの強力な点は、読み取りと書き込みが互いをブロックしない MVCCモデルです。

トランザクション1          トランザクション2
├─ BEGIN                   
├─ SELECT users ...  (T=100)
│  読み取り: 行v1            
│                        ├─ BEGIN
│                        ├─ UPDATE users SET age=31 WHERE id=1
│                        │  新バージョンv2を作成
│                        │  v1は読めるが更新
│                        ├─ COMMIT
│                        │  v2がコミット
│ SELECT users ... (T=200)
│ 読み取り: まだv1(time < v2 commit時刻)
└─ COMMIT

PostgreSQL内部では各行(tuple)に以下のメタデータを持ちます:

xmin: その行を作成したトランザクションID
xmax: その行を削除/更新したトランザクションID (0なら有効)

クエリ実行時は、各行が「このトランザクションから見えるか」を xmin / xmax と実行時刻で判定します。

PostgreSQLの明示的ロック(Explicit Locking)

postgresql.org で詳述されるロック機構:

-- Table-level locks (最強)
LOCK TABLE users IN EXCLUSIVE MODE;

-- Row-level locks
SELECT * FROM users WHERE id = 1 FOR UPDATE;          -- 排他ロック
SELECT * FROM users WHERE id = 1 FOR UPDATE NOWAIT;   -- ロック待たず即エラー
SELECT * FROM users WHERE id = 1 FOR SHARE;           -- 共有ロック

-- Advisory locks (アプリ定義)
SELECT pg_advisory_lock(12345);
SELECT pg_advisory_unlock(12345);

トランザクション分離レベルで必要なロック強度が自動的に決まります。

トランザクション分離レベルの厳密な実装

PostgreSQL(postgresql.org)の実装:

分離レベル	Dirty Read	Non-repeatable Read	Phantom	実装
READ UNCOMMITTED	なし	あり	あり	READ COMMITTED と同じ
READ COMMITTED	なし	あり	あり	デフォルト。各SQL実行時のスナップショット
REPEATABLE READ	なし	なし	なし	標準。トランザクション開始時のスナップショット
SERIALIZABLE	なし	なし	なし	SSI(Serializable Snapshot Isolation)

PostgreSQL 13.2 以降、SERIALIZABLE は Serializable Snapshot Isolation で実装され、真のシリアライザビリティが保証されます。

SQLiteの実装とB-tree

SQLiteの設計思想

SQLite(sqlite.org)は「ゼロ設定」「シングルファイルDB」「埋め込み用」として設計されました。

NOTE

SQLite は、サーバー型DBMs(PostgreSQL, MySQL) と異なり、ライブラリとして組み込まれます(sqlite.org)。

SQLiteのアーキテクチャ

SQL Parser
    ↓
Query Planner/Optimizer
    ↓
Virtual Machine (VDBE: Virtual DataBase Engine)
    ↓
Backend (Page Cache, B-tree, Lock Manager)
    ↓
File on Disk

SQLiteの VDBE は、SQL を最大数百個のバイトコード命令にコンパイルし、スタック機械として実行します。

B-tree インデックスと実装

SQLite は全テーブルと全インデックスを B-tree で保存します。

CREATE INDEX idx_users_email ON users(email);

すると、新しい B-tree が作成されます:

[Key: email]        [Rowid: users行のID]
    ↓                   ↓
"a@b.c"      -----→    1
"b@b.c"      -----→    2
"c@b.c"      -----→    3

検索は B-tree を辿って O(log N)。

SQLiteのロック機構

SQLiteはプロセス単位でファイルロックを使うため、複数プロセスからのアクセスが限定的です。

状態遷移:

UNLOCKED
  ↓
SHARED (読み取り可能)
  ↓
RESERVED (次の書き込みに備える)
  ↓
PENDING (既存readersの終了を待つ)
  ↓
EXCLUSIVE (書き込み中)

複数ライターが同時アクセスしようとすると、後発は RESERVED → EXCLUSIVE へ進めず BUSY エラー。

WAL(Write-Ahead Logging)モードでは、readers と writer が同時存在できます。

SQLiteの仮想テーブル(Virtual Table)

SQLiteは、任意のデータソースを「テーブル」として扱える仮想テーブル機構があります。

// CSV ファイルを SQL で問い合わせる例
SELECT * FROM csv('./data.csv') WHERE value > 100;

FTS(Full-Text Search)、JSON 拡張、R-treeなどが仮想テーブルとして実装されています。

MySQLのストレージエンジン比較

ストレージエンジンの役割

MySQLはストレージエンジンを選択できます(MySQL公式ドキュメント):

エンジン	トランザクション	全文検索	キャッシュ	用途
InnoDB	あり	あり	バッファプール	標準推奨
MyISAM	なし	あり(遅い)	なし	読み取り専用、分析
Memory	なし	なし	メモリ全体	セッション、テンポラリ
Archive	なし	なし	あり	圧縮履歴保存

MySQL 5.5 以降、デフォルトは InnoDB。

InnoDBのトランザクション処理

START TRANSACTION;
  INSERT INTO orders VALUES (...);
  UPDATE inventory SET qty = qty - 1 WHERE item_id = 123;
COMMIT;

InnoDB の MVCC により:

トランザクション間の読み取りは干渉しない
WAL(redo log) にて durability を保証
Undo log で ROLLBACK を実現

クラスタードインデックス(Clustered Index)

InnoDBは 主キーをクラスタードインデックス として保存します。

PRIMARY KEY = id の場合:

B-tree Leaf:
    id | name | email | ... (全データ)
    ↓
    1  | Alice | a@b.c | ...
    2  | Bob   | b@b.c | ...
    3  | Carol | c@b.c | ...

セカンダリインデックス(email)は:

B-tree Leaf:
    email | PRIMARY KEY
    ↓
    a@b.c | → 1 (主キーを指す)
    b@b.c | → 2

セカンダリインデックス経由のアクセスは「インデックス + 主キー参照」の2段階になり、インデックスには主キーを含めると効率化できます(covered index):

CREATE INDEX idx_email_id ON users(email, id);
-- email でマッチ → id は同じインデックスから取得 (テーブル参照なし)

インデックス設計の高度なパターン

部分インデックス(Partial Index)

-- PostgreSQL
CREATE INDEX idx_active_users ON users(id) WHERE deleted = FALSE;

-- SQLite
CREATE INDEX idx_active_users ON users(id) WHERE is_active = 1;

-- MySQL (カバリングインデックス + WHERE で代替)
CREATE INDEX idx_active_users ON users(id, is_active) WHERE is_active = 1;

削除されたレコードのインデックス領域を節約できます。

複合インデックスの列順序

-- クエリパターン1: name で検索、age で絞り込み
SELECT * FROM users WHERE name = 'Alice' AND age > 18;

-- インデックス定義: よく検索される列を最初に
CREATE INDEX idx_users_name_age ON users(name, age);

B-tree は左から順に比較するため:

WHERE name = 'Alice': インデックス有効(最初の列で絞れる)
WHERE age > 18: インデックス無効(最初の列なし)

CREATE INDEX idx_users_name_age ON users(name, age);

なら、WHERE name = 'Alice' AND age > 18 は高速ですが、 WHERE age > 18 だけでは無効です。

インデックス圧縮とプレフィックス圧縮

PostgreSQL 17 以降、B-tree インデックスの圧縮が標準機能になります。

-- 従来(非圧縮)
CREATE INDEX idx_users_email ON users(email);

-- 圧縮有効(PostgreSQL 17+)
-- 自動的に実施される

テキストインデックスで重複プレフィックスを圧縮し、メモリ効率を向上。

実行計画(EXPLAIN)の読み方

EXPLAIN ANALYZE の出力

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) 
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

出力例:

Aggregate (cost=1000.50..1000.51 rows=100)
  ->  Hash Left Join (cost=100.00..900.00 rows=10000)
        Hash Cond: (o.user_id = u.id)
        ->  Seq Scan on orders o (cost=0.00..500.00 rows=10000)
        ->  Hash (cost=50.00..50.00 rows=100)
              ->  Seq Scan on users u (cost=0.00..50.00 rows=100)

読み方:

cost=100.00..900.00: 推定開始コスト…終了コスト
rows=10000: 推定出力行数
実際の行数が見積もりと大きく異なれば、ANALYZE対象テーブルを更新

インデックススキャンの判定

EXPLAIN SELECT * FROM users WHERE email = 'a@b.c';

Index Scan using idx_users_email on users
  Index Cond: (email = 'a@b.c')

高速。一方:

EXPLAIN SELECT * FROM users WHERE email LIKE '%@example.com';

Seq Scan on users
  Filter: (email ~~ '%@example.com'::text)

前方一致でないため、インデックス使えず全テーブルスキャン。

まとめ

SQLは、データを手続きではなく集合として扱うための言語です。SELECT文の構文だけでなく、正規化、JOIN、集約、インデックス、トランザクション、実行計画を一体で理解すると、正しく速いデータ操作に近づけます。

参考文献

公式・標準

解説・補助

Use The Index, Luke!