LabHub

ブログ

SQL 実務チートシート — 毎日使うコマンド総まとめ

2026-03-02 · 57 分で読めます · #database#sql#mysql#postgresql#select#join#indexing#performance#cheatsheet#2026-03

한국어English日本語

SQL Cheatsheet

このチートシートが基準にしているエンジン

チートシートをコピーして使う前に、どのエンジン基準かを知る必要があります。この記事の例は PostgreSQL 18 を基準に書かれています。文法を見れば分かります。ON CONFLICT ... DO UPDATE、FULL OUTER JOIN、GROUP BY ROLLUP、INTERVAL '30 days'、created_at::date のキャスト、|| の文字列結合、DATE_TRUNC、WITH RECURSIVE、WHERE 句のついた部分インデックス — すべて PostgreSQL 系の文法です。

以下の節で扱う次の機能は、PostgreSQL 公式ドキュメントで確認した PostgreSQL の機能です。

MySQL を使っているなら、これらの節はそのまま適用できません。特に実行計画は出力形式そのものが違います。本文の EXPLAIN FORMAT=JSON の例がその証拠で、後述の「実行計画の読み方」は完全に PostgreSQL の出力を前提に説明しています。MySQL 側のロックレベルやオンライン DDL の挙動も異なるので、MySQL のマニュアルで別途確認してください。本文にすでにある UPSERT と JOIN UPDATE / JOIN DELETE は両エンジンの文法が並記されているので、そのまま参考にできます。

基本 CRUD

SELECT（検索）

-- 基本検索
SELECT * FROM users WHERE age >= 20 ORDER BY created_at DESC LIMIT 10;

-- 特定カラムのみ
SELECT id, name, email FROM users WHERE status = 'active';

-- エイリアス（別名）
SELECT
    u.name AS user_name,
    COUNT(o.id) AS order_count,
    SUM(o.amount) AS total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

-- DISTINCT（重複排除）
SELECT DISTINCT department FROM employees;

-- BETWEEN, IN, LIKE
SELECT * FROM products
WHERE price BETWEEN 10000 AND 50000
  AND category IN ('electronics', 'books')
  AND name LIKE '%Galaxy%';

-- NULL 処理
SELECT name, COALESCE(phone, '未登録') AS phone
FROM users
WHERE email IS NOT NULL;

INSERT（挿入）

-- 単件挿入
INSERT INTO users (name, email, age) VALUES ('Kim Youngju', 'yj@example.com', 30);

-- 複数件挿入
INSERT INTO users (name, email, age) VALUES
    ('Hong Gildong', 'hong@example.com', 25),
    ('Lee Sunsin', 'lee@example.com', 35),
    ('King Sejong', 'sejong@example.com', 45);

-- SELECT結果をINSERT（テーブルコピー）
INSERT INTO users_backup (name, email, age)
SELECT name, email, age FROM users WHERE status = 'active';

-- UPSERT（あればUPDATE、なければINSERT）
-- PostgreSQL
INSERT INTO users (email, name, login_count)
VALUES ('yj@example.com', 'Kim Youngju', 1)
ON CONFLICT (email)
DO UPDATE SET
    login_count = users.login_count + 1,
    last_login = NOW();

-- MySQL
INSERT INTO users (email, name, login_count)
VALUES ('yj@example.com', 'Kim Youngju', 1)
ON DUPLICATE KEY UPDATE
    login_count = login_count + 1,
    last_login = NOW();

UPDATE（更新）

-- 基本更新
UPDATE users SET status = 'inactive' WHERE last_login < '2025-01-01';

-- 複数カラムの同時更新
UPDATE products
SET price = price * 1.1,           -- 10% 値上げ
    updated_at = NOW()
WHERE category = 'electronics';

-- JOIN UPDATE（他テーブルを参照して更新）
-- PostgreSQL
UPDATE orders o
SET status = 'cancelled'
FROM users u
WHERE o.user_id = u.id
  AND u.status = 'banned';

-- MySQL
UPDATE orders o
JOIN users u ON o.user_id = u.id
SET o.status = 'cancelled'
WHERE u.status = 'banned';

-- CASEを使った条件付きUPDATE
UPDATE employees
SET salary = CASE
    WHEN department = 'engineering' THEN salary * 1.15
    WHEN department = 'sales' THEN salary * 1.10
    ELSE salary * 1.05
END
WHERE hire_date < '2024-01-01';

-- 注意: WHEREなしのUPDATEは全行更新！
-- 必ず先にSELECTで確認！
SELECT * FROM users WHERE last_login < '2025-01-01';  -- まず確認
UPDATE users SET status = 'inactive' WHERE last_login < '2025-01-01';

DELETE（削除）

-- 基本削除
DELETE FROM sessions WHERE expired_at < NOW();

-- JOIN DELETE
-- PostgreSQL
DELETE FROM orders
USING users
WHERE orders.user_id = users.id AND users.status = 'deleted';

-- MySQL
DELETE o FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'deleted';

-- TRUNCATE（全件削除、高速、AUTO_INCREMENTリセット）
TRUNCATE TABLE logs;

-- ソフトデリートパターン（推奨）
UPDATE users SET deleted_at = NOW() WHERE id = 123;
-- 検索時:
SELECT * FROM users WHERE deleted_at IS NULL;

JOIN（結合）

-- INNER JOIN（両方に存在するもののみ）
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN（左テーブル全部 + 右テーブルのマッチ）
SELECT u.name, COALESCE(COUNT(o.id), 0) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;
-- → 注文のないユーザーも含まれる（order_count = 0）

-- RIGHT JOIN（右テーブル全部 + 左テーブルのマッチ）
-- あまり使わない、LEFT JOINを反転させた方が可読性が良い

-- FULL OUTER JOIN（両方全部）
SELECT u.name, o.amount
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- CROSS JOIN（全組み合わせ、デカルト積）
SELECT s.size, c.color
FROM sizes s CROSS JOIN colors c;
-- 3 sizes × 4 colors = 12 combinations

-- SELF JOIN（自己結合）
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

JOINダイアグラム:

INNER JOIN:    A ∩ B（積集合のみ）
LEFT JOIN:     A + (A ∩ B)
RIGHT JOIN:    (A ∩ B) + B
FULL OUTER:    A ∪ B（和集合）

GROUP BY + 集計関数

-- 基本集計
SELECT
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000000  -- 平均給与5千万以上の部署のみ
ORDER BY avg_salary DESC;

-- ROLLUP（小計 + 総計）
SELECT
    COALESCE(department, '=== 合計 ===') AS department,
    COALESCE(position, '--- 小計 ---') AS position,
    COUNT(*) AS count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY ROLLUP(department, position);

サブクエリ（Subquery）

-- WHERE サブクエリ
SELECT * FROM users
WHERE id IN (
    SELECT user_id FROM orders
    WHERE amount > 1000000
);

-- FROM サブクエリ（インラインビュー）
SELECT dept_name, avg_salary
FROM (
    SELECT department AS dept_name, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) sub
WHERE avg_salary > 60000000;

-- EXISTS（存在確認、大量データではINより高速）
SELECT u.name
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.status = 'completed'
);

-- スカラーサブクエリ（SELECT句内）
SELECT
    name,
    salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

CTE（Common Table Expression）— 可読性の王様

-- 基本CTE
WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE status = 'active' AND last_login > NOW() - INTERVAL '30 days'
),
user_orders AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total
    FROM orders
    WHERE created_at > NOW() - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT
    au.name,
    au.email,
    COALESCE(uo.order_count, 0) AS orders,
    COALESCE(uo.total, 0) AS total_spent
FROM active_users au
LEFT JOIN user_orders uo ON au.id = uo.user_id
ORDER BY total_spent DESC;

-- 再帰CTE（組織図、カテゴリツリー）
WITH RECURSIVE org_tree AS (
    -- ベースケース: CEO（manager_idがNULL）
    SELECT id, name, manager_id, 1 AS level
    FROM employees WHERE manager_id IS NULL

    UNION ALL

    -- 再帰ケース: 部下を探索
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT REPEAT('  ', level - 1) || name AS org_chart, level
FROM org_tree
ORDER BY level, name;

ウィンドウ関数（Window Functions）

-- ROW_NUMBER（連番）
SELECT
    name, department, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank_all,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_dept
FROM employees;

-- RANK vs DENSE_RANK
-- RANK: 1, 2, 2, 4（同順位2位の後は4位）
-- DENSE_RANK: 1, 2, 2, 3（同順位2位の後は3位）

-- LAG / LEAD（前/次の行を参照）
SELECT
    date,
    revenue,
    LAG(revenue) OVER (ORDER BY date) AS prev_day,
    revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY date))
        / LAG(revenue) OVER (ORDER BY date) * 100, 1
    ) AS change_pct
FROM daily_sales;

-- 累積合計（Running Total）
SELECT
    date, amount,
    SUM(amount) OVER (ORDER BY date) AS running_total,
    AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

-- NTILE（分位に分割）
SELECT
    name, salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
    -- 1=上位25%, 2=25~50%, 3=50~75%, 4=下位25%
FROM employees;

インデックス（Index）戦略

-- インデックス作成
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- 複合インデックスの順序が重要！
-- idx(a, b, c) の場合:
--   WHERE a = 1                    使用される
--   WHERE a = 1 AND b = 2         使用される
--   WHERE a = 1 AND b = 2 AND c = 3 使用される
--   WHERE b = 2                    使用されない！（先頭カラムなし）
--   WHERE a = 1 AND c = 3         aのみ使用（bスキップ）

-- 部分インデックス（PostgreSQL）
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- カバリングインデックス（テーブルアクセスなしでインデックスのみで検索）
CREATE INDEX idx_covering ON orders(user_id, status, amount);
SELECT status, SUM(amount) FROM orders WHERE user_id = 123 GROUP BY status;
-- → インデックスのみ読み取り！（テーブルI/Oなし）

実行計画（EXPLAIN）

-- PostgreSQL
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.name;

-- 読み方:
-- Seq Scan: フルテーブルスキャン（インデックスが必要？）
-- Index Scan: インデックス使用
-- Index Only Scan: カバリングインデックス
-- Nested Loop: 少量データのJOIN
-- Hash Join: 大量データのJOIN
-- Sort: ORDER BY（メモリ超過時はディスク使用）
-- Bitmap Heap Scan: 複数インデックスの組み合わせ

-- MySQL
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed';

実行計画の読み方

上の節はコマンドだけを見せています。本当に大事なのは出力の読み方です。

内側から読みます

実行計画は木構造です。インデントの深いノードが先に実行され、その結果が外側のノードへ上がっていきます。だから上から読むと順序が逆に見えます。いちばん深いところでどうやって行を取り出しているかを確認し、その行がどこで絞られ、どう結合されるかを追って上がっていけばよいのです。

ドキュメントが明示している重要な性質が 1 つあります。上位ノードのコストは下位ノードのコストをすべて含みます。だから最上段の数字だけを見て「このノードが高い」とは言えません。親と子の差を見て初めて、そのノードが実際にいくら使ったかが分かります。

括弧の中の 4 つの数字

-- 出力例: EXPLAIN (ANALYZE, BUFFERS) の結果
 Hash Join  (cost=1082.00..3418.55 rows=4821 width=48) (actual time=8.412..41.903 rows=4795.00 loops=1)
   Hash Cond: (o.user_id = u.id)
   Buffers: shared hit=1204 read=812
   ->  Seq Scan on orders o  (cost=0.00..2015.00 rows=100000 width=24) (actual time=0.011..12.204 rows=100000.00 loops=1)
         Buffers: shared hit=200 read=815
   ->  Hash  (cost=1021.00..1021.00 rows=4880 width=32) (actual time=8.301..8.302 rows=4795.00 loops=1)
         Buckets: 8192  Batches: 1  Memory Usage: 384kB
         ->  Seq Scan on users u  (cost=0.00..1021.00 rows=4880 width=32) (actual time=0.019..7.104 rows=4795.00 loops=1)
               Filter: (status = 'active'::text)
               Rows Removed by Filter: 45205
               Buffers: shared hit=1004
 Planning Time: 0.312 ms
 Execution Time: 43.115 ms

1 行ずつ見ます。

推定行数と実測行数の差が 1 番の信号です

ドキュメントが直接こう書いています。「通常もっとも重要なのは、推定行数が現実に十分近いかどうかである」。

プランナが計画を選ぶ根拠はすべてこの推定値です。推定が大きく外れれば、その後の判断がすべて狂います。100 行が出ると思って Nested Loop を選んだのに実際は 100 万行なら、100 万回のインデックス参照が起きます。逆に 100 万行を見込んで Hash Join を選んだのに実際は 10 行なら、ハッシュテーブルを作るだけ無駄です。

だから計画を見るときに最初にやることは、各ノードの rows= の推定値と actual ... rows= の実測値を並べて比べることです。数倍の差は無視してよく、桁が変わるほど開いているノードを見つければそこが問題の起点です。そのノードでなぜ推定が外れたのかを見ます。たいていは統計が古いか、列同士の相関をプランナが知らないか、条件が関数で包まれていて選択率を推定できないかのどれかです。

大きなテーブルに選択的な条件なのに Seq Scan が出たら

上の例の users のスキャンがまさにその形です。5 万行を読んで 4795 行を残しました。この程度の選択率ならインデックスが勝つ可能性があります。逆に 5 万行のうち 4 万行が残る条件なら、インデックスを使うほうが損です。インデックスを使うとインデックスとテーブルを行き来するランダムアクセスになりますが、どうせほとんどのページを読むなら順に読み切るほうが速いからです。

だから Seq Scan を見た瞬間に反射的にインデックスを作ってはいけません。判断材料は 2 つです。テーブルが実際に大きいか（Buffers の読み取りブロック数を見ます）、そして条件が実際に選択的か（Rows Removed by Filter と残った rows の比を見ます）。

EXPLAIN ANALYZE はクエリを本当に実行します

これを見落とすと事故になります。ドキュメントの表現どおり、EXPLAIN ANALYZE はクエリを実際に実行するので副作用も通常どおり起きます。結果行は捨てられますが、UPDATE、DELETE、INSERT は本当にデータを変えます。

データを変えずにデータ変更クエリの計画を見るには、トランザクションで包んでロールバックします。ドキュメントが勧めているのがこの方法です。

BEGIN;

EXPLAIN ANALYZE
UPDATE orders SET status = 'cancelled' WHERE created_at < '2025-01-01';

ROLLBACK;

本番で実行計画を見ることがあるなら、この習慣を体に付けておいてください。EXPLAIN だけなら実行せず推定値だけを見せますが、それでは実測行数が得られず、上で述べた 1 番の信号を確認できません。

インデックスを作ったのになぜ使われないのか

インデックス戦略の節で作ったインデックスが計画に現れないとき、原因はほぼ必ず次の 4 つのどれかです。順に確認すれば速いです。

1. 複合インデックスの先頭列ルール

上のインデックスの節にすでに表で整理されている内容です。3 列のインデックスは左から連続して使われます。先頭列が条件になければ、そのインデックスは候補から外れます。計画にインデックス名がまったく出てこないなら、ここから疑ってください。

2. インデックス列を関数やキャストで包んだ

もっとも多く、もっとも目につきにくい原因です。

-- インデックスは email 列の上にあるのに
CREATE INDEX idx_users_email ON users(email);

-- 条件は lower(email) の上にかかります → インデックスを使えません
SELECT * FROM users WHERE lower(email) = 'yj@example.com';

-- 日付も同じ。created_at のインデックスがあってもキャストすると使えません
SELECT * FROM orders WHERE created_at::date = '2026-08-16';

-- 解決 1) 式インデックスを作る
CREATE INDEX idx_users_email_lower ON users(lower(email));
-- ドキュメントの例: upper(col) の上のインデックスがあれば
-- WHERE upper(col) = 'JIM' の条件がそのインデックスを使えます。

-- 解決 2) 条件側を範囲に書き換えて列を裸に戻す
SELECT * FROM orders
WHERE created_at >= '2026-08-16' AND created_at < '2026-08-17';

原理は単純です。インデックスは列の値を並べ替えて保存します。その値に関数をかけた結果は並び順が保たれる保証がないので、インデックスを使えません。関数をかけた結果そのものをインデックスにすれば（式インデックス）また使えるようになります。

3. 選択率が低くて、順次スキャンが本当に正しい計画である

status = 'active' の行が全体の 80% なら、インデックスを使うほうが損です。これはバグではなくプランナが正しく判断した結果です。上の「大きなテーブルに選択的な条件」の節と同じ話です。

このとき使えるカードが部分インデックスです。インデックスの節にすでに例があります。全体ではなくよく使う一部だけをインデックス化すればインデックスが小さくなり、その条件で来るクエリでは選択率が上がります。

4. 統計が古い

プランナが計画を選ぶ根拠はテーブル内容についての統計です。ドキュメントの表現どおり「合理的に正確な統計を持つことが重要であり、そうでなければ計画の選択を誤ってデータベース性能を低下させうる」のです。この統計を集めるコマンドが ANALYZE で、VACUUM の任意のステップとしても実行されます。

autovacuum デーモンは、テーブルの内容が十分に変化すると自動的に ANALYZE を実行します。しかし大量ロード直後のようにデータが一度に大きく変わった状況では、自動実行を待つ間は古い統計で計画が立てられます。

-- 大量ロード直後は手動で回します
ANALYZE orders;

-- そのあと計画が変わるか再確認
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

診断の順序としてまとめるとこうです。計画にインデックス名がまったく出ないなら 1 番と 2 番を、インデックスが候補には挙がるのに選ばれないなら 3 番と 4 番を見ます。4 番かどうかの確認は簡単です。ANALYZE を回して計画が変われば統計の問題でした。

実務パターン集

ページネーション

-- OFFSET方式（シンプルだが大量データでは遅い）
SELECT * FROM posts ORDER BY id DESC LIMIT 20 OFFSET 40;

-- カーソルベース（大量データ推奨！）
SELECT * FROM posts
WHERE id < 12345  -- 最後に見たid
ORDER BY id DESC
LIMIT 20;

重複排除

-- 重複行の検出
SELECT email, COUNT(*) as cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- 重複のうち最古のもののみ残して削除
DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id) FROM users GROUP BY email
);

-- またはCTEで
WITH ranked AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
    FROM users
)
DELETE FROM users WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

日付関連

-- 今日/今月/今年
SELECT * FROM orders WHERE created_at::date = CURRENT_DATE;
SELECT * FROM orders WHERE DATE_TRUNC('month', created_at) = DATE_TRUNC('month', NOW());

-- 直近7日間の日別統計
SELECT
    DATE(created_at) AS date,
    COUNT(*) AS orders,
    SUM(amount) AS revenue
FROM orders
WHERE created_at >= NOW() - INTERVAL '7 days'
GROUP BY DATE(created_at)
ORDER BY date;

-- 時間帯別分布
SELECT
    EXTRACT(HOUR FROM created_at) AS hour,
    COUNT(*) AS count
FROM orders
GROUP BY hour
ORDER BY hour;

ロック（Lock）に関する注意

-- SELECT FOR UPDATE（悲観的ロック）
BEGIN;
SELECT * FROM products WHERE id = 1 FOR UPDATE;  -- 他のトランザクションは待機
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- 楽観的ロック（version カラム）
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;  -- versionが一致する場合のみ更新
-- 影響行数 = 0 の場合 → 他の人が先に更新済み！

キーセットページネーション

上のページネーションのパターンをきちんと説明すると、この記事でいちばん使う節になります。

OFFSET はなぜ後ろへ行くほど遅くなるのか

ドキュメントの 1 行で終わります。「OFFSET 句がスキップする行もサーバー内部では計算されなければならないので、大きな OFFSET は非効率になりうる」。

つまり OFFSET 100000 は 10 万行を飛ばすのではなく、10 万行を作ってから捨てます。1 ページ目は 20 行作ればよいのに、5001 ページ目は 100020 行を作らなければなりません。コストがページ番号に比例して線形に増えます。一覧画面の後ろのページが遅いという報告は、ほぼ必ずこれです。

シーク方式に書き換える

-- OFFSET 方式: 5001 ページ目を見るのに 100020 行を作って 100000 行を捨てます
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

-- キーセット（シーク）方式: 最後に見た行の値から続けて読みます
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ('2026-05-01 12:00:00', 84213)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- このインデックスがあって初めて意味があります
CREATE INDEX idx_posts_created_id ON posts(created_at DESC, id DESC);

肝は 同点処理用の列 です。created_at だけで並べ替えると同じ時刻の行同士の順序が決まらず、ページ境界で行が抜けたり重複したりします。ドキュメントもこの点を明記しています。「LIMIT を使うときは、結果行を一意な順序に制約する ORDER BY を使うことが重要である。そうしないとクエリの行の予測不可能な部分集合を得ることになる」。

だから並べ替えキーの末尾に一意な列、ふつうは主キーを付けます。上の例の id がその役割です。タプル比較 (created_at, id) < (...) を使えば 2 列を一度に比較でき、上の複合インデックスと並び順が噛み合います。

計画はどう変わるか

-- 出力例: OFFSET 方式
 Limit  (cost=8421.55..8423.24 rows=20 width=48) (actual time=182.401..182.408 rows=20.00 loops=1)
   ->  Index Scan Backward using idx_posts_created_id on posts
         (cost=0.42..84210.33 rows=1000000 width=48)
         (actual time=0.028..170.552 rows=100020.00 loops=1)
 Execution Time: 182.443 ms

-- 出力例: キーセット方式
 Limit  (cost=0.42..2.11 rows=20 width=48) (actual time=0.031..0.052 rows=20.00 loops=1)
   ->  Index Scan Backward using idx_posts_created_id on posts
         (cost=0.42..84210.33 rows=899980 width=48)
         (actual time=0.029..0.047 rows=20.00 loops=1)
         Index Cond: (ROW(created_at, id) < ROW('2026-05-01 12:00:00'::timestamp, 84213))
 Execution Time: 0.081 ms

どちらの計画も同じインデックスを使っています。違いは内側ノードの actual ... rows にあります。OFFSET 側は 100020 行を実際に取り出し、キーセット側は 20 行だけ取り出しました。Index Cond があるかないかがその差を生みます。条件がインデックスの中まで降りれば開始地点へ直接ジャンプでき、なければ最初から数えながら来ることになります。

キーセットの代償

タダではありません。任意のページ番号へジャンプできません。「1, 2, 3 ... 5001」のようなページ番号 UI は作れず、「もっと見る」または「次へ」方式だけが可能です。総ページ数を見せるには別の COUNT クエリが必要ですが、それは後述の落とし穴の節で扱う問題につながります。

だから判断基準は単純です。ユーザーは実際に後ろのページへジャンプするのか。ほとんどの一覧画面では誰も 5001 ページへは行きません。それなら、キーセットが正解です。

ロックとスキーマ変更 — 実務で本当に危ないところ

上のロックの節は SELECT FOR UPDATE と楽観的ロックしか扱っていません。障害を生むのはそちらではありません。

WHERE なしの UPDATE / DELETE がすること

行が消えることだけが問題ではありません。UPDATE、DELETE、INSERT、MERGE は対象テーブルに ROW EXCLUSIVE ロックを取ります。このモード自体は互いに衝突しないので、他の書き込みと並行して進みます。問題は行レベルです。WHERE なしの UPDATE はテーブルの全行に行ロックを取り、そのトランザクションが終わるまで、それらの行に触ろうとするすべてのトランザクションが待たされます。100 万行のテーブルなら事実上テーブル全体が止まります。

そして ROW EXCLUSIVE は SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE と衝突します。つまりその長い UPDATE が回っている間、CREATE INDEX（SHARE ロック）やほとんどの ALTER TABLE（ACCESS EXCLUSIVE ロック）は開始すらできません。

本当の問題は長いトランザクションです

ロックはトランザクションが終わって初めて解放されます。だから 5 分のトランザクションは 5 分のロックです。ここにもう 1 つ乗ります。PostgreSQL は MVCC のために UPDATE や DELETE が古い行バージョンを即座に消しません。ドキュメントの表現どおり「他のトランザクションからまだ見える可能性がある間は、その行バージョンを削除してはならない」からです。長く開いたままのトランザクションが 1 つでもあると、その間に溜まった死んだ行を片づけられず、テーブルとインデックスが膨らみ続けます。

だから「接続を開いたまま人間が考える」パターン、アプリケーションがトランザクションの中から外部 API を呼ぶパターン、バッチが 1 トランザクションで全件を処理するパターンが、どれも危険です。

誰が塞いでいるかを見つける

-- 現在待機中のセッションと、それを塞いでいる PID
SELECT
    a.pid,
    a.state,
    now() - a.xact_start AS xact_age,
    now() - a.query_start AS query_age,
    a.wait_event_type,
    a.wait_event,
    pg_blocking_pids(a.pid) AS blocked_by,
    left(a.query, 120) AS query
FROM pg_stat_activity a
WHERE a.backend_type = 'client backend'
ORDER BY xact_age DESC NULLS LAST;

pg_blocking_pids(integer) は、指定したプロセスがロックを取得するのを妨げているセッションのプロセス ID の配列を返します。妨げているものがなければ空配列です。衝突するロックを実際に保持している場合（ハードブロック）と、衝突するロックを待っていて待ち行列で前にいる場合（ソフトブロック）の両方を含みます。ドキュメントは、この関数がロックマネージャの共有状態に短時間の排他アクセスを必要とするため、頻繁な呼び出しは性能に影響しうると警告しています。監視から毎秒呼ぶ関数ではありません。

xact_age を降順に並べているのには理由があります。ほとんどのロック事故で犯人はいちばん長く開いているトランザクションで、そのトランザクションは待機中ではなく idle in transaction のまま遊んでいることが多いのです。

デッドロックを防ぐルール

ドキュメントの処方は 1 文です。「データベースを使うすべてのアプリケーションが、複数のオブジェクトに対して一貫した順序でロックを取得するようにすることが、デッドロックへの最善の防御である」。

これにもう 1 つ付きます。「あるオブジェクトに対してトランザクション内で最初に取得するロックは、そのオブジェクトに対して必要になる最も強いモードであるべきである」。つまり後で UPDATE する行を先に素の SELECT で読んでおき、あとからロックを上げるパターンがデッドロックを作ります。最初から SELECT ... FOR UPDATE で取るべきです。

事前に検証できないなら、デッドロックで中断したトランザクションを再試行する形で処理せよ、とドキュメントは付け加えます。実務では両方やります。順序を固定し、それでも出るデッドロックは再試行します。

インデックス作成 — CREATE INDEX は書き込みを止めます

-- これは SHARE ロックを取ります。他のトランザクションは読めますが
-- INSERT / UPDATE / DELETE はインデックス作成が終わるまでブロックされます。
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- これは SHARE UPDATE EXCLUSIVE ロックを取ります。書き込みを止めません。
CREATE INDEX CONCURRENTLY idx_orders_user_date ON orders(user_id, created_at DESC);

-- 失敗すると invalid なインデックスが残ります。psql の \d では INVALID と表示されます。
SELECT indexrelid::regclass AS index_name, indrelid::regclass AS table_name
FROM pg_index
WHERE NOT indisvalid;

-- 推奨される復旧方法は、削除して作り直すことです。
DROP INDEX CONCURRENTLY idx_orders_user_date;

CONCURRENTLY はタダではありません。ドキュメントによれば、テーブルを 2 回スキャンする必要があり、インデックスを変更または使用する可能性のある既存トランザクションがすべて終わるのを待たなければなりません。だからはるかに時間がかかります。また通常の CREATE INDEX と違い、トランザクションブロックの中では実行できません。マイグレーションツールがすべての DDL を 1 つのトランザクションで包む場合、これが障害になります。

失敗したときの挙動も知っておくべきです。スキャン中にデッドロックや一意性違反などの問題が起きるとコマンドは失敗しますが、「invalid」なインデックスが残ります。このインデックスは不完全かもしれないため検索には使われませんが、更新のオーバーヘッドはそのまま食います。つまり何の得もなく書き込みだけが遅くなります。上のクエリで定期的に確認し、見つけたら削除して作り直してください。

ALTER TABLE — 既定は ACCESS EXCLUSIVE です

これがいちばん重要な 1 行です。ドキュメントが直接こう書いています。「明示的に言及されていない限り ACCESS EXCLUSIVE ロックが取得される」。

ACCESS EXCLUSIVE はすべてのモードと衝突します。素の SELECT が取る ACCESS SHARE とも衝突します。だから ALTER TABLE がロックを待っている間、その後ろに到着したすべてのクエリが待ち行列に積み上がります。DDL 1 つがサービス全体を止める古典的な障害はこの構造です。入口で 1 人が立ち止まると後ろに行列ができるのと同じです。

ドキュメントが例外として明示している、より弱いロックを取る形です。

テーブルの書き換えが起きるかどうかも別に見る必要があります。ロックレベルとは別に、書き換えが起きればその間ロックが長く保持されます。

安全な制約追加のパターン

-- 悪い方法: 大きなテーブル全体をスキャンする間、他の更新がすべてロックアウトされます
ALTER TABLE orders ADD CONSTRAINT orders_amount_positive CHECK (amount > 0);

-- 良い方法 1 段階目: NOT VALID で即座にコミット（テーブルスキャンなし）
ALTER TABLE orders ADD CONSTRAINT orders_amount_positive CHECK (amount > 0) NOT VALID;

-- 良い方法 2 段階目: あとで検証（SHARE UPDATE EXCLUSIVE ロックだけを取ります）
ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_positive;

ドキュメントが説明する原理はこうです。大きなテーブルをスキャンして新しい外部キー、チェック、not-null 制約を検証するには時間がかかり、ALTER TABLE ADD CONSTRAINT がコミットされるまでそのテーブルへの他の更新がロックアウトされます。NOT VALID オプションの主な目的は、まさにこの影響を減らすことです。NOT VALID を付けると ADD CONSTRAINT はテーブルをスキャンせず、即座にコミットできます。

そのあと VALIDATE CONSTRAINT で既存行が制約を満たすかを確認します。この検証ステップは同時更新をロックアウトする必要がありません。以降に他のトランザクションが挿入・更新する行にはすでに制約が適用されているので、既存行だけを確認すればよいからです。だから SHARE UPDATE EXCLUSIVE ロックだけを取ります。制約が外部キーなら、参照されるテーブルに ROW SHARE ロックも必要です。

失敗事例と落とし穴

1. 昨日まで速かったクエリが今日は遅い

症状: コードもデータ量も大きく変わっていないのに、急に応答が数倍遅くなりました。

診断の順序:

  1. 今の実行計画を取ります。昨日の速かったころの計画を保存していないなら、今からでも保存してください。
  2. 各ノードの推定行数と実測行数を比べます。大きく開いたノードがあれば統計の問題です。
  3. ANALYZE を回して計画を取り直します。元の形に戻れば原因の確定です。
  4. 計画が変わらないなら、結合順序やスキャン方式が変わったかを見ます。データ分布がしきい値を越えて、プランナが Nested Loop から Hash Join へ（またはその逆へ）乗り換えた場合です。これは正常な動作で、真の原因はたいていインデックスがないか、条件がインデックスを使えないことです。

バインドパラメータを使うクエリなら、値によって良い計画が違うのにキャッシュされた計画が再利用される場合があります。パラメータをリテラルに置き換えて EXPLAIN を取れば、計画が違うかどうかがすぐ分かります。

2. SELECT * が結合で行の幅を膨らませる

症状: 結合結果の行数は少ないのにクエリが遅く、ソートがディスクへあふれます。

診断: 計画の width= を見ます。この値はそのノードが出力する行の平均バイト数の推定値です。3 つのテーブルを結合してすべて SELECT * で取ると、不要な列まで全部ついてきます。ソートやハッシュのノードはその行をメモリに保持しなければならないので、行の幅が大きいと作業メモリを超えてディスクを使い始めます。

直し方: 必要な列だけを並べます。副作用としてカバリングインデックスが可能になることも多いです。インデックスの節の Index Only Scan の話がそれです。

3. NOT IN のサブクエリに NULL が 1 つあって結果が 0 件

いちばん静かで、いちばん危ない落とし穴です。エラーは出ず、ただ空の結果が返ります。

原因 は、ドキュメントの表現そのままです。「左辺の式が NULL を返すか、等しい右辺の値がなく、かつ右辺の行の少なくとも 1 つが NULL を返す場合、NOT IN 構文の結果は真ではなく NULL になる。これは NULL 値のブール結合に関する SQL の通常の規則に従ったものである」。

-- 危険: orders.user_id に NULL が 1 件でもあると結果は 0 件です
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

-- 安全 1) NOT EXISTS に書き換える（推奨）
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 安全 2) NOT IN を維持するなら NULL を明示的に除外する
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

NOT EXISTS は行が返るかどうかだけを見るので、NULL につまずきません。習慣的に NOT EXISTS を使うほうが安全です。

4. 暗黙の型キャスト

症状: インデックスのある列なのに計画に Seq Scan が出ます。条件も単純です。

診断: 計画の Filter: または Index Cond: の行にキャストが見えるかを確認します。上の例の計画の Filter: (status = 'active'::text) のように ::型 が付いていれば型変換が起きています。列側にキャストが付いているなら、上の「インデックスを作ったのになぜ使われないのか」の 2 番と同じ状況です。定数側だけなら、たいてい問題ありません。

直し方: アプリケーションが送るパラメータの型を列の型に合わせます。数値列に文字列を送る、timestamp 列に date を送る、といったケースがよくあります。

5. 大きなテーブルの COUNT

症状: 一覧画面の全件数表示のせいでページの読み込みが遅いです。

原因: 条件なしの COUNT は結局すべての行を数えなければなりません。計画を取れば順次スキャンかインデックス全体スキャンになっているのが見えます。上のキーセットページネーションの節と一緒に出てくる問題なのはそのためです。

選択肢:

  1. 全件数を本当に見せる必要があるかを問い直します。ほとんどの一覧画面で正確な総数は誰も見ていません。「次のページがあるか」だけあれば十分です。それは LIMIT 21 で 21 行取ってきて 21 行目があるかを見れば済みます。
  2. おおよその値で十分なら、プランナの推定値を使います。EXPLAIN 出力の最上段の rows= がその値です。
  3. 正確な値がどうしても必要で頻繁に参照されるなら、別のカウンタテーブルを置いて更新します。これはスキーマ設計の問題であって、クエリチューニングの問題ではありません。

使わないほうがよい場合

チートシートの正直な境界はこうです。

参考資料

クイズ — SQL実務（クリックして確認！）

Q1. LEFT JOIN と INNER JOIN の違いは？ ||LEFT JOIN: 左テーブルの全行を含み、右側にマッチがなければNULL。INNER JOIN: 両方にマッチする行のみ返す||

Q2. UPSERT を PostgreSQL と MySQL でそれぞれどう書くか？ ||PostgreSQL: INSERT ... ON CONFLICT (key) DO UPDATE SET ... MySQL: INSERT ... ON DUPLICATE KEY UPDATE ...||

Q3. 複合インデックス idx(a, b, c) で WHERE b = 2 のみ使うと？ ||インデックスは使用されない。複合インデックスは左から順に使用される。先頭カラム（a）なしでは機能しない||

Q4. ROW_NUMBER と DENSE_RANK の違いは？ ||ROW_NUMBER: 常に連番（同順位なし）。DENSE_RANK: 同順位は同じ順位で、次は直後の番号（1,2,2,3）。RANKは同順位後にスキップ（1,2,2,4）||

Q5. OFFSETページネーションが大量データで遅い理由は？ ||OFFSET Nは N行を読んで破棄する。OFFSET 100万なら100万行を読んでから結果を返す。カーソルベースはインデックスで開始点に直接アクセス||

Q6. カバリングインデックスとは？ ||クエリに必要な全カラムがインデックスに含まれ、テーブルアクセスなしでインデックスのみで結果を返すこと。Index Only Scan||

Q7. SELECT FOR UPDATE の用途と注意点は？ ||悲観的ロック — 選択した行を他のトランザクションが変更できないようにロック。注意: トランザクションが長引くと他のトランザクションが待機するためデッドロックの危険||

Q8. WHERE なしで UPDATE を実行すると？ ||テーブルの全行が更新される！必ずSELECTで対象を確認してからUPDATEを実行。本番環境ではトランザクションで囲み、確認後にCOMMIT||

クイズ

Q1: 「SQL 実務チートシート — 毎日使うコマンド総まとめ」の主なトピックは何ですか？

SELECT、UPDATE、INSERT、DELETEからサブクエリ、ウィンドウ関数、CTE、インデックス戦略、実行計画の分析まで。実務で毎日使うSQLパターンを一箇所にまとめました。コピーしてすぐ使えます。

Q2: 基本 CRUDとは何ですか？ SELECT（検索） INSERT（挿入） UPDATE（更新） DELETE（削除）

Q3: 実務パターン集の核心的な概念を説明してください。 ページネーション 重複排除 日付関連 ロック（Lock）に関する注意 Q1. LEFT JOIN と INNER JOIN の違いは？ Q2. UPSERT を PostgreSQL と MySQL でそれぞれどう書くか？ Q3. 複合インデックス idx(a, b, c) で WHERE b = 2 のみ使うと？ Q4. ROW_NUMBER と DENSE_RANK の違いは？ Q5. OFFSETページネーションが大量データで遅い理由は？ Q6. カバリングインデックスとは？ Q7. SELECT FOR UPDATE の用途と注意点は？ Q8.

Q4: 実行計画を見るとき、まず確認すべき信号は何ですか？ 各ノードの推定行数と実測行数の差です。ドキュメントも「通常もっとも重要なのは、推定行数が現実に十分近いかどうかである」と書いています。プランナのすべての判断がこの推定値から出るので、大きく外れたノードが問題の起点です。

Q5: EXPLAIN ANALYZE で UPDATE の計画を見るときの注意点は？ EXPLAIN ANALYZE はクエリを実際に実行するので副作用もそのまま起きます。データを変えずに計画だけを見るには、BEGIN で始めて ROLLBACK で終わるトランザクションで包む必要があります。

Q6: インデックスのある列を関数で包むとなぜインデックスを使えず、どう直しますか？ インデックスは列の値の並び順を保存しますが、関数をかけた結果はその順序が保たれる保証がないからです。直す方法は 2 つです。関数をかけた結果そのものに式インデックスを作るか、条件を範囲比較に書き換えて列から関数を剥がすことです。

Q7: OFFSET ページネーションが後ろへ行くほど遅くなる理由と、キーセット方式の成立条件は？ OFFSET がスキップする行もサーバー内部では計算されてから捨てられるので、コストがページ番号に比例して増えます。キーセット方式は最後に見た行の値から続けて読みますが、並び順が一意でなければならないため、並べ替えキーの末尾に主キーのような同点処理用の列が必要で、その並び順に合う複合インデックスも必要です。

Q8: CREATE INDEX と CREATE INDEX CONCURRENTLY はそれぞれどのロックを取りますか？ 通常の CREATE INDEX は SHARE ロックを取り、インデックス作成中は挿入・更新・削除をブロックします。CONCURRENTLY は SHARE UPDATE EXCLUSIVE ロックを取り書き込みを止めませんが、テーブルを 2 回スキャンし既存トランザクションの終了を待つため時間がかかり、トランザクションブロックの中では実行できません。

原文を読む: https://www.youngju.dev/blog/database/2026-03-03-sql-practical-cheatsheet.ja

コメント

まだコメントはありません。

ログインするとコメントできます