SQL 06 実務の注意(インデックス・トランザクション・SQLi・AI開発)

更新日: 2026-10-05

← SQL_05_データ操作と設計(INSERT・UPDATE・DELETE・CREATE) / 入口 → SQL

1. インデックス = 検索を速くする索引

本の索引と同じ

分厚い技術書で「トランザクション」の載っているページを探すとき:

• 索引なし → 1ページ目から全部めくる(= フルスキャン)
• 索引あり → 巻末の「た行」を見て p.234 に直接飛ぶ

SELECT * FROM orders WHERE user_id = 1;

• インデックスなし:100万行あれば100万行すべてを確認。行が増えるほど遅くなる
• インデックスあり:user_id 順に並んだ索引をたどって該当行に直接たどり着く。100万行でも数回たどるだけ

中身のイメージ(B-tree)

多くのDBは B-tree という木構造で、値を常に並べ替えた状態で持っている。

              [50]
           /        \
      [20, 35]     [70, 90]
      /  |  \       /  |  \
   …1〜19 … 36〜49 …   …91〜
            ↓
       各値 → 実際の行の場所

辞書を真ん中から開いて「前か後ろか」で絞り込むのと同じやり方。

作り方

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);  -- 複合インデックス

速くなる場面

• WHERE user_id = 1(絞り込み)
• JOIN … ON o.user_id = u.id(テーブルの結合)
• ORDER BY created_at(並べ替え。索引はすでに並んでいるので速い)
• UNIQUE 制約の重複チェック

デメリット

→ 全部の列に貼るのは逆効果。WHERE / JOIN ON / ORDER BY でよく使う列に絞って貼る。

覚えておくこと

• 主キー(id)と UNIQUE を付けた列 には自動で作られる
• 外部キー列(user_id など)は自動では作られないことが多い → 自分で作るのが定番
• 数百〜数千行なら索引なしでも気にならないことが多い。目安として 数万〜数十万行 を超えたあたりで差がはっきり出る

「指定しておけば自動で使われる」は半分正解

• 事前に「この列に索引を作る」と指定する(CREATE INDEX)
• クエリ側は何も変えなくてよい。WHERE user_id = 1 と普通に書くだけ
• 索引の中身もDBが自動で最新に保つ(追加・更新のたびに更新される)

ただし 必ず使われるわけではない。使うかどうかは DB の中の オプティマイザ(実行計画を立てる係) がクエリごとに判断する。索引は「使える道具を用意しておく」もので、使う・使わないは DB 任せ。

複合インデックスは「左から順に」使われる

CREATE INDEX idx ON orders(user_id, created_at);

電話帳が「名字 → 名前」の順に並んでいるのと同じ。名字が分かれば探せるが、名前だけでは探せない。

まとめ:索引を用意しておけば、使えそうな場面で DB が自動で選んでくれる。ただし 索引が使われる書き方になっているかは、クエリを書く側が気にする必要がある。

遅い原因を調べる(実行計画)

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 1;   -- SQLite / D1
EXPLAIN ANALYZE    SELECT * FROM orders WHERE user_id = 1;   -- PostgreSQL

SCAN(全件)ではなく SEARCH … USING INDEX と出ていれば索引が使われている。

2. トランザクション = 全部成功 or 全部なかったこと

送金のように「片方だけ成功すると困る」処理をまとめる。

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;      -- 確定。途中で問題があれば ROLLBACK; で全部取り消し

• 手作業で本番データを触るときも BEGIN; → 実行 → SELECT で確認 → COMMIT; / ROLLBACK;
• Cloudflare D1 は BEGIN を直接使えず、db.batch([...]) でまとめた文が1トランザクションになる
• 同時更新の競合(在庫の取り合いなど)は UPDATE … SET stock = stock - 1 WHERE stock > 0 のように 1文で条件付き更新 すると安全

3. SQL インジェクション(SQLi)対策 ― 最重要

ユーザー入力を 文字列連結で SQL に埋め込む と、SQL そのものを書き換えられる。

// ✗ 絶対ダメ
db.query(`SELECT * FROM users WHERE email = '${email}'`);
// email に  ' OR '1'='1  と入力されると → 全ユーザーが返る
// '; DROP TABLE users; -- なら → テーブルが消える
 
// ○ プレースホルダ(パラメータ化クエリ / プリペアドステートメント)
db.query('SELECT * FROM users WHERE email = ?', [email]);           // MySQL / SQLite
db.query('SELECT * FROM users WHERE email = $1', [email]);          // PostgreSQL
env.DB.prepare('SELECT * FROM users WHERE email = ?').bind(email);  // Cloudflare D1

• 値は 必ず ? / $1 で渡す。自前のエスケープ処理に頼らない
• ORM(Prisma・Drizzle)は基本安全だが、$queryRawUnsafe や sql.raw() など "raw" / "unsafe" と名のつく関数 に入力を入れると同じ穴があく
• テーブル名・列名・ORDER BY の向きはプレースホルダにできない → 許可リスト から選ぶ

  const col = ['name', 'created_at'].includes(sort) ? sort : 'created_at';

• 詳しくは Webセキュリティ対策

4. その他の実務ポイント

• 最小権限:アプリ用の DB ユーザーには必要な権限だけ(DROP できないなど)
• 接続情報は秘密情報:接続文字列・パスワードは .env / シークレットへ(APIキー・シークレット管理)
• N+1 に注意:ループの中でクエリを投げない。JOIN か IN (…) でまとめる
• 本番 DB を直接いじらない:まずローカル/ステージングで試す。マイグレーション経由で変更する
• バックアップと復元手順を一度は試す:「取れているつもり」が一番危ない
• 時刻は UTC で保存、表示するときに日本時間へ変換

5. 生成AI に SQL を書かせるときのコツ

AI が書いた SQL のチェックリスト

• UPDATE / DELETE に WHERE があり、条件が想定どおりか
• ユーザー入力はすべてプレースホルダ経由か
• JOIN の ON 条件が正しいか(行数が爆発・重複していないか)
• LEFT JOIN なのに WHERE で右側を絞って INNER 化していないか
• SELECT * で不要・秘密の列まで返していないか
• NULL の扱い(= NULL、NOT IN、SUM が NULL)は大丈夫か
• 検索条件の列にインデックスがあるか
• ループの中でクエリを投げていないか(N+1)