sql基礎から設計・最適化・運用・保守まで完全ガイド

SQL Serverで日本語を正しく並べ替える照合順序の扱い方と、Dell AppSyncによるSQL Server統合運用を解説。DBコピーの作成・復元・マウント/アンマウント、トランザクションログバックアップ、Always On連携まで把握でき、ソート不備やバックアップ/復旧の悩みを解決。実務に役立つ具体手順も。

1 SQLの基礎と役割
2 基本文法とクエリ設計
3 データ定義と整合性担保
- 3.1 データ型と制約の設計指針
  - 3.1.1 NOT NULL/UNIQUE/PRIMARY KEY/FOREIGN KEY/CHECK
  - 3.1.2 既定値・生成列の活用
- 3.2 スキーマ変更（CREATE/ALTER/DROP）の基本
4 パフォーマンス最適化
- 4.1 インデックス設計の基本原則
  - 4.1.1 B-Treeとカバリングインデックス
  - 4.1.2 統計情報とクエリプランの関係
- 4.2 クエリチューニングの手順
  - 4.2.1 実行計画の読み方とボトルネック特定
  - 4.2.2 不要なソートやN+1の回避手法
5 トランザクションと同時実行制御
- 5.1 ACID特性と分離レベルの選択
- 5.2 ロック/ラッチとブロッキング
  - 5.2.1 デッドロックの検出と対処
6 セキュリティと権限管理
- 6.1 ユーザー/ロール設計と権限付与（GRANT/REVOKE）
- 6.2 暗号化・マスキング・監査の実践
7 バックアップとリカバリ戦略（SQL Server中心）
8 国際化対応と文字列処理
- 8.1 照合順序と並び替え規則の理解
  - 8.1.1 日本語のソート順を正しく設定する方法
  - 8.1.2 大文字小文字・アクセントの区別設定
- 8.2 文字コードとサロゲートペアの注意点
9 自動化と運用効率化
- 9.1 バックアップ・メンテナンスの定期実行自動化
- 9.2 スキーマ管理とCI/CDパイプライン連携
  - 9.2.1 マイグレーションの自動テスト
  - 9.2.2 スキーマ差分の管理手法
10 クラウド環境でのSQL活用
- 10.1 マネージドRDBの特徴と設計上の考慮点
- 10.2 サーバーレス/自動スケール時のパフォーマンス最適化
11 トラブルシューティング
- 11.1 クエリ遅延の診断ステップ
- 11.2 接続エラーやタイムアウトの原因切り分け

SQLの基礎と役割

SQL（Structured Query Language）は、リレーショナルデータベースに対して「何をしたいか」を宣言的に伝えるための標準言語です。業務アプリケーションから分析基盤、データ連携（ETL/ELT）まで広く使われ、データの定義・操作・制御を一貫して扱えるのが最大の特徴です。ISO/IEC 9075として標準化されており、実装間の差異はあるものの、sqlは依然としてデータ活用の共通言語としての役割を担い続けています。

データ定義の役割: エンティティや関係をスキーマとして定義し、データ品質を制約で守る。
データ操作の役割: セット志向のクエリで大量データを効率よく抽出・更新する。
アクセス制御の役割: ユーザーやロールに対する権限を細かく管理し、監査性を高める。
相互運用の役割: 多様なRDBMS・ツール・クラウドで共通に利用でき、システム間の橋渡しとなる。

SQLの概要とDML・DDL・DCLの違い

SQLは目的に応じて大きく3種類に分かれます。用途と副作用の違いを理解することで、安全かつ効率的なデータ操作が可能になります。

DML（Data Manipulation Language）: 既存データの取得・変更に用います。代表例はSELECT/INSERT/UPDATE/DELETE、実装によってはMERGEも含みます。通常はトランザクションの対象で、COMMIT/ROLLBACKで確定や取り消しが可能です。
DDL（Data Definition Language）: スキーマやオブジェクト（テーブル、ビューなど）の作成・変更・削除を行います。CREATE/ALTER/DROP/TRUNCATEなどが該当します。多くの実装でDDLは暗黙的コミットや制限付きのトランザクション動作となるため、本番環境では適用タイミングと影響範囲の管理が重要です。
DCL（Data Control Language）: アクセス権限を付与・剥奪するために使用します。GRANT/REVOKEが中心で、一部実装ではDENYなどの拡張があります。最小権限の原則に基づき、操作とオブジェクト単位で精緻に設計します。

補足: COMMIT/ROLLBACK/SAVEPOINTなどのTCL（Transaction Control）をDMLとは別枠で扱う文脈もあります。いずれにせよ、DML・DDL・DCLの境界とトランザクションとの関係性を押さえることが、運用リスクの低減につながります。

リレーショナルモデルの基本要素（テーブル・主キー・外部キー）

リレーショナルモデルは、「表（テーブル）」と「関係」を数学的に扱う枠組みです。SQLはこのモデルを操作するための言語として設計されています。基礎要素は次の通りです。

テーブル: 行（タプル）と列（属性）からなる集合です。各列は定義域（データ型）を持ち、表内の行順序に意味はありません（論理的には無順序）。
主キー（Primary Key）: 各行を一意に識別する列（または列の組）。NULL不可かつ重複不可で、候補キーの中から1つを主キーとして選びます。自然キー（業務上意味のある値）とサロゲートキー（人工的に採番した値）の使い分けが設計の要点です。
外部キー（Foreign Key）: 別テーブルの主キー（または候補キー）を参照し、整合性（参照整合性）を保証します。ON DELETE/ON UPDATEの動作（RESTRICT/NO ACTION/SET NULL/SET DEFAULT/CASCADE）を定義することで、親子関係の変更時の振る舞いを明確にできます。

例えば「顧客」と「注文」の関係では、注文テーブルに顧客IDの外部キーを設けることで、「存在しない顧客に紐づく注文」を防げます。主キー・外部キーの適切な設計は、データ品質の担保とクエリの明確性を高める基盤です。

標準SQLと主要実装の差分ポイント

SQLは標準仕様がある一方で、RDBMSごとに拡張や挙動差があります。移植性やベストプラクティスを考える際、代表的な差分を把握しておくと有利です。

論点	標準SQL	PostgreSQL	MySQL	SQL Server	Oracle Database
識別子の引用	“name”	“name”	`name`（バッククォート）	[name] または “name”	“name”
オートインクリメント	SEQUENCE/IDENTITY	SERIAL/IDENTITY/SEQUENCE	AUTO_INCREMENT	IDENTITY	SEQUENCE（IDENTITY列も可）
アップサート	MERGE（SQL:2003）	INSERT … ON CONFLICT	INSERT … ON DUPLICATE KEY UPDATE	MERGE	MERGE
行数制限	FETCH FIRST n ROWS ONLY	LIMIT / FETCH	LIMIT	TOP / OFFSET FETCH	FETCH FIRST
ブール型	BOOLEAN	boolean あり	BOOLEANあり（実体はTINYINT(1)）	BIT/BOOLEAN相当	BOOLEAN（バージョンにより実装差）
空文字とNULL	区別する	区別する	区別する	区別する	空文字”をNULLとして扱う挙動あり
DDLのトランザクション性	規定なし	多くがトランザクション対応	多くが暗黙コミット	多くがトランザクション可能（制限あり）	DDL前後で暗黙コミット
JSON機能	標準規定は限定的	json/jsonb型と豊富な演算	JSON型と関数	JSONサポート関数	JSONデータ型/関数

移植性を高めるコツは、標準SQLの構文を優先しつつ、実装依存の機能（方言）を使う箇所を明確に分離することです。公式ドキュメントで最新の仕様を必ず確認しましょう: PostgreSQL / MySQL / SQL Server / Oracle Database。

基本文法とクエリ設計

sql（SQL）はリレーショナルデータを宣言的に操作する言語です。本節では、日常的なデータ抽出・整形で欠かせない基本文法と、意図どおり・読みやすく・再利用しやすいクエリ設計の考え方を、具体例を交えて整理します。

SELECTの基本とWHEREの条件指定

SELECTは「どの列を」「どの表から」「どの条件で」取得するかを宣言します。最低限の構成は SELECT … FROM … WHERE … です。WHERE は行単位の絞り込み（真の行のみ通過）に使います。

-- 例: 直近30日で合計金額が1万円以上の注文を取得
SELECT
  o.order_id,
  o.customer_id,
  o.order_date,
  o.total_amount
FROM orders AS o
WHERE
  o.order_date >= CURRENT_DATE - INTERVAL '30' DAY
  AND o.total_amount >= 10000
  AND (o.status = 'CONFIRMED' OR o.status = 'SHIPPED');

論理演算子は AND が OR より先に評価されます。複雑な条件はカッコで明示して誤読を防ぎます。
LIKE はパターン一致（%: 任意長、_: 1文字）。ワイルドカードを文字として扱う場合は ESCAPE でエスケープを指定します。
BETWEEN は境界値を含みます（閉区間）。日付の上限境界は「< 次日」など半開区間で表現すると意図が明確になります。

列選択・重複排除・並び替え（SELECT, DISTINCT, ORDER BY）

SELECT 句では列名だけでなく、式や関数、別名（AS）を定義できます。DISTINCT は選択した列の組み合わせの重複を取り除きます。ORDER BY は結果セットの並び順を制御します。

-- 例: 顧客ごとの国（重複排除）を国名昇順、顧客数降順で表示
SELECT DISTINCT
  c.country AS country_name
FROM customers AS c
ORDER BY country_name ASC;

-- 例: 複数キーでの並び替えと列の別名・式
SELECT
  o.order_id,
  o.customer_id,
  o.order_date,
  o.total_amount,
  o.total_amount * 0.1 AS tax
FROM orders AS o
ORDER BY
  o.customer_id ASC,
  o.order_date DESC;

DISTINCT はSELECT句の全列に対して作用します。特定列だけの重複排除はサブクエリやGROUP BYを検討します。
ORDER BY では別名を使うと可読性が上がります。列番号での指定は意図が伝わりにくいため避けます。
NULL の並び位置は明示的に制御したい場合、NULLS FIRST / NULLS LAST を使います。

NULLと三値論理の正しい扱い

SQLの真理値は TRUE / FALSE / UNKNOWN（不明）の三値です。NULL を含む比較は多くが UNKNOWN となり、WHERE では除外されます（TRUE のみ通過）。

-- NULL比較は = ではなく IS NULL / IS NOT NULL
SELECT * FROM customers WHERE phone IS NULL;

-- COUNT(*), COUNT(column) の違い
SELECT
  COUNT(*)            AS row_count,      -- 全行をカウント
  COUNT(phone)        AS phone_count     -- NULLを除外してカウント
FROM customers;

-- NULL安全な比較
SELECT
  CASE WHEN a IS NOT DISTINCT FROM b THEN 'equal' ELSE 'not equal' END AS eq_null_safe
FROM t;

-- NOT IN の落とし穴：リスト/サブクエリに NULL を含むと UNKNOWN になり全行が落ちることがある
-- 安全に書くには NOT EXISTS を使う
SELECT c.*
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM blacklist b WHERE b.email = c.email
);

COALESCE(expr, default) でNULLに既定値を与えると、並び替え・比較・表示の意図が明確になります。
注意: x IN (1, NULL) は x=1 以外で UNKNOWN となり WHERE では落ちます。NULL混在の集合比較は EXISTS/NOT EXISTS を選びます。

集約とグループ化の実践

集約は複数行を1つの要約値に畳み込みます。GROUP BY は集約の粒度（キー）を定義し、HAVING はグループ単位の条件で絞り込みます。

GROUP BYとHAVINGの使い方

-- 例: 月次・顧客別の売上サマリと閾値フィルタ
SELECT
  DATE_TRUNC('month', o.order_date) AS month,
  o.customer_id,
  COUNT(*)                          AS order_cnt,
  SUM(o.total_amount)               AS sales
FROM orders AS o
WHERE o.order_date >= DATE_TRUNC('year', CURRENT_DATE)  -- 行の前処理はWHERE
GROUP BY
  DATE_TRUNC('month', o.order_date), o.customer_id
HAVING
  SUM(o.total_amount) >= 50000                           -- グループ後の条件はHAVING
ORDER BY month, sales DESC;

SELECT に出す非集約列は、原則として GROUP BY に含めます。
WHERE は行レベル、HAVING は集約後のグループレベルの条件です。計算コスト削減のため、可能な条件はWHEREに置きます（意味上の区別が最優先）。
代表値の取り出しには MIN/MAX を用いると決定的な結果になります。

ウィンドウ関数の基礎

ウィンドウ関数は「行を潰さずに」周辺行を参照する分析機能です。OVER(PARTITION BY … ORDER BY …) で分析の単位と順序を定義します。

-- 例1: 顧客ごとの累計売上と順位
SELECT
  o.customer_id,
  o.order_date,
  o.total_amount,
  SUM(o.total_amount) OVER (
    PARTITION BY o.customer_id
    ORDER BY o.order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  ROW_NUMBER() OVER (
    PARTITION BY o.customer_id
    ORDER BY o.order_date DESC
  ) AS recent_rank
FROM orders o;

-- 例2: 移動平均（直近7行）
SELECT
  o.order_date,
  AVG(o.total_amount) OVER (
    ORDER BY o.order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS avg_7
FROM orders o;

GROUP BY と違い、結果行数は維持されます。集約とウィンドウ関数は併用可能です。
フレーム（ROWS/RANGE）は集計対象の範囲を定義します。並び替えのキーと一貫させます。

結合とサブクエリの設計

複数テーブルを関連づけるにはJOIN、集合から集合を導出するにはサブクエリ/CTEを使います。関係や意図が読み取れる構造にすることがsql設計の要です。

INNER/LEFT/RIGHT/FULL JOINの使い分け

-- 例: 注文に顧客情報を結合（基本はINNER JOIN）
SELECT
  o.order_id, o.order_date, o.customer_id,
  c.name AS customer_name
FROM orders o
INNER JOIN customers c
  ON c.customer_id = o.customer_id;

-- 左外部結合：片方にしかない行も残す（不足側はNULL）
SELECT
  c.customer_id, c.name, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id;

-- 右外部結合：RIGHT は LEFT の左右を入れ替えたもの
-- FULL 外部結合：双方にしかない行を含めて結合（一致しない側はNULL）
SELECT
  a.key, a.val_a, b.val_b
FROM A a
FULL JOIN B b
  ON b.key = a.key;

結合条件は必ず ON 句に書き、外部結合の意味を壊す WHERE でのフィルタは注意して扱います（結合後に NULL を落とさない）。
1対多の関係を結合すると結果が増える（重複する）ことがあります。集計前に意図した粒度か確認します。
一致しない行を残したい側を基準に LEFT/RIGHT を選びます。二方向とも残す必要がある場合は FULL を使います。

サブクエリとCTE（WITH句）の活用

サブクエリはクエリ内に埋め込む派生テーブル・スカラー値・存在判定として使えます。CTE（WITH句）は一時的な名前付き結果を定義し、複雑なロジックを段階的に表現できます。

-- スカラーサブクエリ：単一値を列として埋め込む
SELECT
  c.customer_id,
  (SELECT MAX(order_date) FROM orders o WHERE o.customer_id = c.customer_id) AS last_order_date
FROM customers c;

-- EXISTS：存在判定（最短で評価される）
SELECT c.*
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

-- CTE：読みやすく段階的に定義
WITH recent_orders AS (
  SELECT *
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '90' DAY
),
ranked AS (
  SELECT
    customer_id,
    total_amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rnk
  FROM recent_orders
)
SELECT customer_id, total_amount
FROM ranked
WHERE rnk = 1;

IN/EXISTS は意味が異なります。集合の包含は IN、存在の有無は EXISTS が明確です（NULL を含む場合は EXISTS が安全）。
CTE を使うと同じ派生結果を複数回参照でき、クエリの意図が伝わりやすくなります。再帰的な階層展開も表現できます。

データ定義と整合性担保

アプリケーションの信頼性は、sqlで表現されるスキーマ設計と制約により大きく左右されます。データ型の選択とデータ整合性を担保するルール（制約）をDDLで明示化することで、アプリ側のバグやヒューマンエラーを未然に防ぎます。本章では、ビジネス要件を壊さない「堅いスキーマ」を作るための指針と、代表的な制約・既定値・生成列、そして基本的なスキーマ変更の手順を整理します。

データ型と制約の設計指針

「入ってはいけないデータが入らない」「曖昧な値を許さない」ことが、可観測性・保守性・パフォーマンスよりもまず先に来る基本です。sqlのDDLでデータ型と制約を正しく組み合わせ、アプリに依存しない一貫性の壁を作ります。

適切な最小型を選ぶ: 例）金額はFLOATではなくDECIMAL(p,s)。フラグはBOOLEAN/ビット型。日付・時刻は文字列ではなくDATE/TIME/TIMESTAMP。
文字列型は長さを根拠付ける: 例）郵便番号や国コードはCHAR固定長、可変文字はVARCHARに上限を設定。ユニコードを扱う場合は対応型を選択。
NULL方針を先に決める: 「不明」と「存在しない」は別概念。原則NOT NULL、真に不明が起こり得る列のみNULLを許容。
識別子の選定: 業務的に安定しない自然キーは避け、数値ID（IDENTITY/SEQUENCE）などのサロゲートキーを主キーに据えるのが無難。業務上の一意性は別途UNIQUEで担保。
列間の意味的整合性はCHECKで表現: 範囲・相関（例：開始日 ≤ 終了日）などはアプリではなくスキーマで防御。
列挙は参照整合性を優先: 値候補が業務マスタにあるなら外部キーで紐付け、固定的で小さい集合ならCHECKで列挙。

-- 例：堅牢な基本テーブル
CREATE TABLE accounts (
  account_id       INTEGER GENERATED ALWAYS AS IDENTITY,
  account_code     VARCHAR(32)  NOT NULL,
  account_name     VARCHAR(200) NOT NULL,
  is_active        BOOLEAN      NOT NULL DEFAULT TRUE,
  opened_at        DATE         NOT NULL,
  closed_at        DATE,
  -- 業務上一意なコードで重複禁止
  CONSTRAINT uq_accounts_code UNIQUE (account_code),
  -- 終了日は開始日以上、未設定も許容
  CONSTRAINT chk_accounts_period CHECK (closed_at IS NULL OR closed_at >= opened_at),
  CONSTRAINT pk_accounts PRIMARY KEY (account_id)
);

NOT NULL/UNIQUE/PRIMARY KEY/FOREIGN KEY/CHECK

制約は「モデル化したビジネスルール」をsqlで機械可読にする仕組みです。意味と使い分けを明確にしましょう。

NOT NULL: 欠損を許さない。必須入力の宣言。NULLが入るとアプリ側のロジックが複雑化します。
UNIQUE: 重複禁止。複合列にも設定可能。多くの実装ではNULLは「互いに異なる」と見なされ複数行許容される点に注意。
PRIMARY KEY: 行の同一性を一意に識別。暗黙にNOT NULLかつUNIQUE。更新頻度や意味変化のある自然キーは避けます。
FOREIGN KEY: 親子関係の整合性を保証。ON DELETE/UPDATEの動作（RESTRICT/NO ACTION/SET NULL/CASCADE）でライフサイクルを統制。
CHECK: 列単独・列間の論理条件で禁止値を排除。クロステーブルの検証は外部キーやアプリのトランザクション設計で扱います。

-- 例：受注と明細（参照整合性＋複合一意性）
CREATE TABLE orders (
  order_id     BIGINT GENERATED BY DEFAULT AS IDENTITY,
  customer_id  BIGINT NOT NULL,
  ordered_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status       VARCHAR(20) NOT NULL,
  CONSTRAINT pk_orders PRIMARY KEY (order_id),
  -- 受注状態の列挙（小規模ドメインならCHECKで）
  CONSTRAINT chk_orders_status CHECK (status IN ('NEW','CONFIRMED','SHIPPED','CANCELLED'))
);

CREATE TABLE order_items (
  order_id     BIGINT NOT NULL,
  line_no      INTEGER NOT NULL,
  product_id   BIGINT NOT NULL,
  quantity     INTEGER NOT NULL,
  unit_price   DECIMAL(12,2) NOT NULL,
  -- 明細の主キーを複合に
  CONSTRAINT pk_order_items PRIMARY KEY (order_id, line_no),
  -- 同じ商品を同一受注内で重複登録させない
  CONSTRAINT uq_order_items_per_product UNIQUE (order_id, product_id),
  -- 数量と単価の下限
  CONSTRAINT chk_qty CHECK (quantity > 0),
  CONSTRAINT chk_price CHECK (unit_price >= 0),
  -- 親テーブルに必ず存在
  CONSTRAINT fk_items_order FOREIGN KEY (order_id)
    REFERENCES orders(order_id)
      ON DELETE CASCADE,        -- 受注削除で明細も削除
  CONSTRAINT fk_items_product FOREIGN KEY (product_id)
    REFERENCES products(product_id)  -- productsは別定義
);

実務では制約にわかりやすい名前（接頭辞pk/uq/fk/chk）を付け、アプリのエラー処理でその識別子をもとにユーザー向けメッセージを出し分けると運用が安定します。

既定値・生成列の活用

DEFAULTと生成列（計算列）は、入力負担を軽減しつつ一貫性と可読性を高めます。冪等な既定値はクライアント差異を吸収し、生成列は「派生値の単一真実源」を提供します。

DEFAULT: 入力省略時の自動値。例）作成日時のCURRENT_TIMESTAMP、フラグのFALSE/TRUE、国コードの’JP’など。
IDENTITY/SEQUENCE: 連番の自動採番。業務キーと分離し、主キーの安定性を確保。
生成列（仮想/永続）: 他列から計算される値。正規化を崩さず集計・検索を簡潔化。永続（STORED）は書き込み時に計算して保存、仮想（VIRTUAL）は読み取り時に計算。

CREATE TABLE invoices (
  invoice_id     BIGINT GENERATED ALWAYS AS IDENTITY,
  customer_id    BIGINT  NOT NULL,
  subtotal       DECIMAL(12,2) NOT NULL,
  tax_rate       DECIMAL(5,4)  NOT NULL DEFAULT 0.10, -- 10%を既定
  issued_at      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  -- 税額と合計を生成列で一意に定義
  tax_amount     DECIMAL(12,2) GENERATED ALWAYS AS (ROUND(subtotal * tax_rate, 2)) STORED,
  total_amount   DECIMAL(12,2) GENERATED ALWAYS AS (subtotal + tax_amount) STORED,
  CONSTRAINT pk_invoices PRIMARY KEY (invoice_id),
  CONSTRAINT fk_invoices_customer FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
);

注意点として、DEFAULTはINSERT時にのみ適用され、UPDATEでは再評価されません。更新時の自動計算が必要なら生成列（またはトリガー等）で表現します。また、ON DELETE SET NULLを使う場合は対象列がNULL許容である必要があります。

スキーマ変更（CREATE/ALTER/DROP）の基本

スキーマは一度作って終わりではありません。sqlのCREATE/ALTER/DROPを用いた進化的変更では、整合性を壊さずに段階的に移行することが重要です。推奨される基本パターンは「拡張→移行→収束（削除）」の三段階です。

拡張（非破壊）: 新列・新テーブル・新制約の追加。既存クエリと互換にするため、新列はNULL許容またはDEFAULTを設定し、アプリは段階的に書き換えます。
移行（両対応）: バックフィルでデータを埋め、アプリを新列/新制約に切替。切替完了まで旧構造と併存させます。
収束（破壊）: 旧列・旧制約・旧テーブルをDROP。参照関係を確認し、必要に応じてCASCADEを使わず順序良く削除します。

-- 例：メール列の追加と一意性導入（段階移行）
-- 1) 拡張：NULL許容で追加し、既定値も設定可
ALTER TABLE users ADD COLUMN email VARCHAR(320);
-- 2) 移行：既存データをバックフィル（アプリ/スクリプトで）
--    バックフィル完了後にNOT NULL化とUNIQUE付与
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);
-- 3) 収束：旧列を削除
ALTER TABLE users DROP COLUMN old_email;

CREATEは依存順（親→子）の順序で、DROPは逆順（子→親）で行います。ALTERで制約を強める際（NULL許容→NOT NULL、CHECK追加、FK追加）は、事前のデータ整備と検証を必ず行い、可能ならトランザクションで原子的に適用します。ミスを最小化するため、すべてのDDLに明確なロールバック手順（逆操作のsql）を用意しておくと安全です。

パフォーマンス最適化

sql のパフォーマンスは、スキーマとクエリの両輪で最適化します。本章では、インデックス設計と統計情報、実行計画の読み解き方、そして日常的に効くクエリチューニング手順を、再現性のある手法と具体例で整理します。

インデックス設計の基本原則

適切なインデックスは、I/O を減らし、ソートや結合コストを下げ、安定した sql 実行時間をもたらします。やみくもに作成せず、ワークロードから逆算して最小限・最大効果を狙います。

選択性重視: 高選択性（フィルタで多くを除外できる列）からキー順に配置。複合インデックスは「左端プレフィックス」を意識。
アクセスパターン駆動: よく使う WHERE、JOIN、ORDER BY、GROUP BY を洗い出し、それらを満たすインデックスを設計。
書き込みコストの考慮: インデックスはINSERT/UPDATE/DELETEの負担。読み取り改善幅と書き込み増のトレードオフを評価。
重複回避: 似た複合インデックスの乱立は避け、共通プリフィックスをまとめる。
関数・式の最適化: 関数適用列を検索するなら、機能（関数）インデックス/計算列＋インデックスを用意。
カーディナリティの旬度: 統計が古いと誤ったプランに。自動更新に頼り切らず定期 ANALYZE/UPDATE STATISTICS を。

B-Treeとカバリングインデックス

B-Tree はほぼ全てのRDBでデフォルトのインデックス構造です。等価検索や範囲検索、整列に強く、O(log N) の探索でページアクセスを抑えます。一方、先頭がワイルドカードのLIKE（例: ‘%abc’）や高頻度更新では効果が薄いことがあります。

複合キーの並び: 一般に「等価条件列 → 範囲条件列 → 並び替え列」の順が有利。
ORDER BY 最適化: インデックス順と同じ並び（かつ同方向）ならソートを省略できる。

関数/大文字小文字の正規化: LOWER(email) で検索するなら、その式をキー化する。

-- PostgreSQL: 機能インデックス
CREATE INDEX idx_users_email_lower ON users ((lower(email)));

-- SQL Server: 計算列＋インデックス（永続化の利用を検討）
ALTER TABLE dbo.Users ADD EmailLower AS LOWER(Email) PERSISTED;
CREATE INDEX IX_Users_EmailLower ON dbo.Users(EmailLower);

カバリングインデックス（Covering Index）は、クエリが必要とする列をすべてインデックスから読み切る設計です。テーブルへの追加アクセス（Lookup）を防ぎ、レイテンシを大幅に削減します。

実装例:

-- SQL Server: INCLUDE 句で非キー列を付与
CREATE INDEX IX_Orders_Cover ON dbo.Orders (CustomerId, OrderDate DESC)
INCLUDE (TotalAmount, Status);

-- PostgreSQL: INCLUDE 付き B-Tree
CREATE INDEX idx_orders_cover ON orders (customer_id, order_date DESC)
INCLUDE (total_amount, status);

-- MySQL/InnoDB: セカンダリインデックスはPKを含む。
-- クエリが参照する列をインデックス列に揃えるとカバリングになりやすい。
CREATE INDEX idx_orders_cust_date_amount ON orders (customer_id, order_date, total_amount);

ポイント: 非キー列を含めすぎるとサイズ増・更新負荷増。読み取り頻度と効果を測定して取捨選択。

統計情報とクエリプランの関係

オプティマイザは統計情報（ヒストグラム、行数、相関）を元に結合順序やアクセス手法を決定します。古い統計は行数見積もりを外し、誤った結合戦略や不必要なソートを招きます。

更新の基本:

-- PostgreSQL
ANALYZE orders;            -- テーブル統計の更新
SET default_statistics_target = 100;  -- 複雑な分布向けに精度を上げる

-- SQL Server
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;
ALTER DATABASE SCOPED CONFIGURATION SET AUTO_CREATE_STATISTICS = ON;
ALTER DATABASE SCOPED CONFIGURATION SET AUTO_UPDATE_STATISTICS = ON;

-- MySQL
ANALYZE TABLE orders;      -- インデックス統計の再計算

相関・スキュー対策: マルチカラム統計や高度なヒストグラムを活用（DB実装依存）。頻出値の偏りが強い列は特に注意。
しきい値: 大量ロード後やパーティション切替後は手動で統計更新。自動更新の閾値に達しない微増でも性能影響が出る場合あり。

クエリチューニングの手順

属人的な勘頼みではなく、再現性のあるプロセスで sql を磨きます。

現状把握: 実行時間、CPU、論理/物理I/O、メモリ使用、行数を計測。代表データで再現。
ボトルネック特定: 実行計画と実測（実行回数、実行時間の長いオペレーター、スピルや大規模ソート）を突き合わせる。
最小変更から試行: 述語の書き換え、不要列の削除、JOIN順序の見直し、適切なインデックス追加。
検証: 検証環境でプランと実測比較。統計更新後も再計測。
退路確保: 変更は小さくコミット。必要ならロールバック可能に。
ヒントは最終手段: オプティマイザの選択を恒久的に固定しない。ヒントは退避策として局所適用。

実行計画の読み方とボトルネック特定

実行計画は「何をどの順にどれだけ」処理したかを示します。推定と実測の乖離、重い演算子、I/O集中箇所を見つけます。

主要オペレーターの目安:
- Table/Seq Scan: 広範囲読み。高選択性の条件で出現していたらインデックスを検討。
- Index Seek/Range Scan: 望ましいパターン。残差述語が多いと戻りアクセスが増える。
- Nested Loop: 少量→大量の結合で有効。外部側の行数増で急激に悪化。
- Hash Join/Aggregate: 大量データに強いがメモリあふれでディスクスピルすると激遅。
- Merge Join: ソート済みの入力に強力。インデックス順を活用して前処理ソートを省く。
- Sort: 大きなソートはメモリ使用とスピルの要因。ORDER BY の必然性を見直す。
乖離チェック: 推定行数 vs 実測行数の倍率。大きく外れていれば統計更新・述語書き換え・インデックス見直し。

取得方法の例:

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;

-- MySQL
EXPLAIN ANALYZE SELECT ...;           -- 8.0+
EXPLAIN FORMAT=JSON SELECT ...;

-- SQL Server
SET STATISTICS IO, TIME ON;
-- 実際の実行計画を表示してクエリ実行（SSMS/ADS）

注意: 実行計画はデータ量・統計・パラメータに依存して変わる。代表パラメータでの評価と、パラメータスニッフィング対策を検討。

不要なソートやN+1の回避手法

多くの遅さは「余計なソート」と「N+1 クエリ」から生まれます。設計段階で避けると劇的に速くなります。

ソートの回避:
- インデックスで並びを満たす: ORDER BY に対応した複合インデックスを作る（方向も合わせる）。
```
-- 例: 顧客別・新しい順の上位N件取得をソートなしで
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date DESC);
SELECT order_id, order_date
FROM orders
WHERE customer_id = ?
ORDER BY order_date DESC
LIMIT 20;
```
- 不要な ORDER BY を削除: 集約結果やページネーションで無意味な整列を排除。
- 安易な DISTINCT/UNION は避ける: キー設計やJOIN条件の見直しで重複生成を元から抑制。
- 大規模ソートのスピル監視: ワークメモリ設定や並列度、事前フィルタの強化で入力行数を削減。
N+1 の回避:
- 一括取得に集約: 子テーブルをJOIN＋集約でまとめて取得。
```
-- 親ごとに子件数を一括で
SELECT p.id, p.title, COUNT(c.id) AS comment_count
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
WHERE p.status = 'published'
GROUP BY p.id, p.title;
```
- IN/EXISTS を活用: 子行の存在確認は EXISTS が効率的（インデックス前提）。
```
SELECT *
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM order_items i
  WHERE i.order_id = o.id AND i.sku = ?
);
```
- プリフェッチ/バッチ読み: アプリ側で親IDをまとめ、子を一括取得してメモリで関連付け。
- 集約列の前計算: 頻出メトリクスはマテリアライズドビューや集計テーブルで更新遅延と引き換えに高速化。
- 配列/JSONの過剰組立に注意: 文字列・JSONの全件連結は重い。必要最小限を選択し、インデックスでフィルタを先に。

これらの原則を、実行計画と統計の整合を取りながら適用すると、sql のパフォーマンスは予測可能かつ持続的に向上します。

トランザクションと同時実行制御

アプリケーションの正しさとパフォーマンスは、SQLのトランザクション設計と同時実行制御に大きく左右されます。本章では、ACID特性と分離レベル、ロック/ラッチによるブロッキング、そしてデッドロックの検出と対処を体系的に解説します。実運用で起こりがちな症状と対策を結び付け、sqlクエリの安定稼働に直結する実践知をまとめます。

ACID特性と分離レベルの選択

トランザクションは以下のACID特性を満たすことで、データの整合性を守ります。

Atomicity（原子性）：全処理が「すべて成功」か「すべて取り消し」になる。
Consistency（一貫性）：制約を破らない状態に遷移させる。
Isolation（分離性）：並行実行でも互いの中間結果を見ない（見せない）。
Durability（永続性）：コミット後の結果は障害後も保持される。

Isolationを具体化するのが「分離レベル」です。選択により許容する異常（アノマリ）が変わり、性能と一貫性のバランスを取ります。

READ UNCOMMITTED：Dirty Read・Non-repeatable Read・Phantomを許容。計測・バッチなど限定用途で。
READ COMMITTED：Dirty Readを防止。多くのOLTPの既定。Non-repeatable/Phantomは発生し得る。
REPEATABLE READ：同一行の再読で値が変わらない。Phantomは理論上発生し得る（実装により軽減される場合あり）。
SERIALIZABLE：直列化相当。Phantomも防止。最も強いが競合が多い。
SNAPSHOT/RC-SI（MVCC）：行バージョンで読取と更新の衝突を低減。読み取りはブロックされにくいが、書き込み競合時に失敗や再試行が必要になる。

選び方の指針:

一般的な業務OLTP：READ COMMITTED（MVCC有効ならスナップショット系）を起点に、要件に応じてピンポイントで強化。
財務・在庫の厳密整合：REPEATABLE READまたはSERIALIZABLEを検討。ホットスポットではロック粒度や再試行設計も同時に。
レポート／分析：スナップショット系でブロッキングを避ける。最新性要件に応じて整合性担保の仕組みを補う。

分離レベルの設定例（データベースにより文法は異なります）。

-- ANSI/一般的な例
START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- ここにSQLを書く
COMMIT;

-- セッション単位で設定（例：PostgreSQL）
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- SQL Server 例（トランザクション開始前に設定）
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
-- ここにSQL
COMMIT;

ロック/ラッチとブロッキング

同時実行制御では「ロック」と「ラッチ」の役割を区別することが重要です。ロックはトランザクション整合性のための論理的な排他、ラッチはメモリ内構造を保護する軽量な同期メカニズムで、性質も寿命も異なります。

ロック（Lock）：
- 目的：行・ページ・テーブル等のリソースの読み書き整合を守る。
- 種類：共有（S）・排他（X）・意図ロック・更新（U）など。
- 粒度：行/キー、ページ、テーブル。実装によりエスカレーション（大量取得時に粗粒度へ昇格）あり。
- 互換性：S同士は共有可能、Xとは非互換など。非互換がブロッキング（待ち）を生む。
ラッチ（Latch）：
- 目的：インデックスノード等のメモリ構造を短時間保護。
- 特性：極短時間で自動解放。トランザクションとは独立し、デッドロック検出の対象外が一般的。

ブロッキングを減らす実践ポイント:

トランザクションは短く保ち、ユーザー操作待ちを含めない（不要な長時間ロックを回避）。
適切なインデックスでアクセス範囲を絞り、広範囲のレンジロックを避ける。
一貫したロック順序（テーブルやキーの取得順）を設計し、競合を局所化。
読み取り負荷が高い場合は、スナップショット系分離や行バージョニングの活用を検討。
必要に応じてタイムアウトを設定（例：lock_timeout/LOCK_TIMEOUT/innodb_lock_wait_timeout）。
更新はバッチ化し、ホットスポット（同一行・同一キー）を分散する。

デッドロックの検出と対処

デッドロックは「AがBのロックを待ち、BがAのロックを待つ」といった待ちの循環で、全員が待ち続ける状態です。主要なRDBMSは検出器で周期的に検出し、被害が小さいトランザクションを「犠牲（ロールバック）」にします。

検出と診断：
- エラー通知：代表例としてSQLSTATE 40P01（deadlock detected）、エラー1205（SQL Server）、ER_LOCK_DEADLOCK（InnoDB）など。
- ログ/メトリクス：デッドロックグラフや関与SQLをログ出力できる機能が各製品に用意されています。
- 可視化：ロック待ちビューやステータス出力から「誰がどのリソースを待っているか」を確認します。
再発防止の設計原則：
- ロック順序を統一（同じ資源を同じ順に取得）。
- 検索条件に合ったインデックスでレンジロックを短縮・限定。
- トランザクションを極力短くし、更新前に必要なデータを先に読み込む。
- 分離レベルの適正化：不要に強い分離を避ける、またはスナップショット分離で読取/更新の衝突を低減。
- NOWAIT/SKIP LOCKED/READPAST等の非ブロッキング取得をジョブ系で活用（要要件適合）。
リトライ戦略（重要）：
- デッドロックはゼロにできない前提で、アプリ層に「安全な再試行」ループを実装。
- 指数バックオフ＋最大試行回数を設定。副作用のないようにトランザクション境界内にのみ副作用を収める。

再試行の擬似コード例：

-- 疑似コード（アプリ側制御）
for attempt in 1..max_retries:
  BEGIN TRANSACTION;
    -- ここに更新系SQL
  COMMIT;
  break
catch error as e:
  if e.sqlstate in ('40P01','40001') or e.code in (1205):
    ROLLBACK;
    sleep(backoff(attempt));
    continue
  else:
    ROLLBACK;
    raise

要点は、sqlトランザクションを短く、競合を予測できる形に設計し、例外時は機械的に安全にやり直せるようにしておくことです。これにより、同時実行性と可用性を高い次元で両立できます。

セキュリティと権限管理

データベースの安全性は、アプリの堅牢性やコンプライアンスの基礎です。sqlレベルのセキュリティは「誰が・何に・どの操作をできるか」を明確化し、暗号化や監査で「守る・見張る」を徹底します。ここでは、運用で実効性の高いユーザー/ロール設計とGRANT/REVOKE、さらに暗号化・マスキング・監査の実践を、主要RDBMSを横断できる観点で整理します。

ユーザー/ロール設計と権限付与（GRANT/REVOKE）

最小権限（Least Privilege）と職務分掌（Separation of Duties）を軸に、ユーザーよりロール中心（RBAC）で設計します。権限はロールに集約し、ユーザー/サービスアカウントはロールを付与されるだけにします。これにより、配属変更や契約終了時の剝奪、監査が容易になります。

要件整理: データ資産（スキーマ/テーブル/ビュー/関数）と操作（SELECT/INSERT/UPDATE/DELETE/EXECUTE）を棚卸しし、職務ごとに必要最小の権限マトリクスを作る。
ロールの階層化: ベースロール（reader, writer, executor）を用意し、アプリ固有のロールはそれらを組み合わせる。強力な権限（DDL、所有者権限）は分離。
スキーマ分離: アプリごと・環境ごとにスキーマを分離し、スキーマ単位でGRANTを基本とする。
デフォルト拒否: 明示的にGRANTされたもののみ許可。暗黙の権限や「ANY/ALL」系の広すぎる権限は避ける。
列・行単位の制御: 機微列は列権限やビュー経由で公開。行レベルはRLS（製品機能）やビューで制限。
ライフサイクル: 入社・異動・退職・委託終了にあわせた付与/剝奪フローと定期棚卸し（権限レビュー）を運用化。

代表的なsqlによるロール設計とGRANT/REVOKEの例です（実装により細部は異なります）。

-- PostgreSQL: ロール中心、スキーマ単位で付与
CREATE ROLE app_reader NOLOGIN;
CREATE ROLE app_writer NOLOGIN;

GRANT USAGE ON SCHEMA public TO app_reader, app_writer;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reader;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_writer;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO app_reader;

-- ユーザーはロールを付与されるだけ
CREATE USER report_user WITH PASSWORD '********';
GRANT app_reader TO report_user;

-- 列単位の付与（機微列は非公開）
GRANT SELECT (id, name, city) ON public.customers TO app_reader;

-- 不要になった権限の剝奪
REVOKE UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM app_writer;

-- SQL Server: スキーマ/オブジェクト単位のGRANT、ロールに集約
CREATE ROLE app_reader;
CREATE ROLE app_writer;

GRANT SELECT ON SCHEMA::dbo TO app_reader;
GRANT INSERT, UPDATE, DELETE ON SCHEMA::dbo TO app_writer;

CREATE USER [report_user] FOR LOGIN [report_login];
ALTER ROLE app_reader ADD MEMBER [report_user];

-- REVOKEの例
REVOKE DELETE ON SCHEMA::dbo FROM app_writer;

-- MySQL: ロールを作り、ユーザーに付与
CREATE ROLE 'app_reader';
CREATE ROLE 'app_writer';

GRANT SELECT ON `appdb`.* TO 'app_reader';
GRANT INSERT, UPDATE, DELETE ON `appdb`.* TO 'app_writer';

CREATE USER 'report_user'@'%' IDENTIFIED BY '********';
GRANT 'app_reader' TO 'report_user'@'%';
SET DEFAULT ROLE 'app_reader' TO 'report_user'@'%';

-- REVOKEの例
REVOKE INSERT, UPDATE ON `appdb`.* FROM 'app_writer';

所有者と権限チェーン: オブジェクト所有者ロールは運用者専用に分離。アプリ実行ロールに所有者を兼務させない。
権限の一時付与: 障害対応などの一時的昇格は有効期限付き（セッション限定、承認ワークフロー、監査必須）。
行レベル制御（RLS）: 製品機能（例: PostgreSQL RLS, SQL Server Row-Level Security）を活用し、テナント/部署などの属性でフィルタ。機能がない場合はビュー＋ポリシー関数で代替。
実行権限の委譲: ストアド/関数で必要な最小権限のみ「代理実行」（SECURITY DEFINER/EXECUTE AS）を使う。乱用は禁物。

暗号化・マスキング・監査の実践

機微データ保護は「通信の暗号化」「保存時暗号化」「列レベル暗号化（必要箇所）」「マスキング」「監査」を組み合わせ、鍵管理と運用まで含めて完成します。sqlの機能とプラットフォームの機能を適材適所で使い分けます。

通信の暗号化（TLS）: サーバ側で証明書を設定し、クライアント接続で強制。古い暗号スイートは無効化。
保存時暗号化（TDE/ディスク暗号化）: 透過的暗号化でバックアップも保護。鍵はKMS/HSMで分離・ローテーション。
列レベル暗号化: 住所/カード情報など、最小限に限定。インデックス/検索性・ソート・集約への影響を評価。
データマスキング: 本番の機微情報を開発・分析に持ち出さない。静的/動的を使い分け。
監査: 誰がいつ何をしたか（ログイン、GRANT/REVOKE、SELECT/変更、DDL）を記録し、改ざん耐性のある保管先へ転送。

通信と保存時の暗号化の例（製品別ポイント）。

PostgreSQL: サーバでssl=on、pg_hba.confはhostssl。クライアントはsslmode=require以上。保存時はOS/ストレージ暗号化や外部ツール、列暗号は拡張（pgcrypto）。
MySQL: require_secure_transport=ON、GRANT … REQUIRE SSLの利用。保存時はテーブルスペース暗号化（エディション/プラグイン依存）。
SQL Server: 接続文字列でEncrypt=True/TrustServerCertificate=False。保存時はTDE、列暗号にAlways Encrypted（アプリ側で鍵保持）。
Oracle Database: ネットワーク暗号化（Native Network Encryption/TLS）、TDE、列暗号・Data Redaction等。

-- SQL Server: 透過的データ暗号化（TDE）の最小例
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '********';
CREATE CERTIFICATE TdeCert WITH SUBJECT = 'TDE Cert';
USE YourDb;
CREATE DATABASE ENCRYPTION KEY
  WITH ALGORITHM = AES_256
  ENCRYPTION BY SERVER CERTIFICATE TdeCert;
ALTER DATABASE YourDb SET ENCRYPTION ON;

-- PostgreSQL: 列レベル暗号（pgcrypto）
-- インストール: CREATE EXTENSION IF NOT EXISTS pgcrypto;
INSERT INTO customers (name, card_token)
VALUES ('Alice', pgp_sym_encrypt('4111-****-****-1111', 'key-rotation-2025'));

-- 復号は必要なロールだけが実行
SELECT name, pgp_sym_decrypt(card_token, 'key-rotation-2025') AS card_no
FROM customers
WHERE id = 1;

動的/静的マスキングの実践。

動的マスキング: 実行時に機微列を隠す。SQL ServerのDynamic Data MaskingやOracle Data Redactionを活用。機能がない場合はビュー＋ロール判定で代替。
静的マスキング: 本番のデータを別環境に配布する前に不可逆加工。代表値置換、トークナイゼーション、シャッフル、ノイズ追加等をデータ分類に沿って適用。

-- SQL Server: 動的データマスキング（例）
ALTER TABLE dbo.Customers
  ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

-- ビューでの擬似マスキング（RDBMS共通パターン）
CREATE VIEW v_customers AS
SELECT id,
       CASE WHEN CURRENT_USER IN ('sec_auditor','priv_reader')
            THEN email
            ELSE CONCAT(LEFT(email, 3), '***@***') END AS email
FROM dbo.Customers;
GRANT SELECT ON v_customers TO app_reader;
REVOKE SELECT ON dbo.Customers FROM app_reader;

監査の設計と実装の要点。

何を記録するか: ログイン成功/失敗、権限変更（GRANT/REVOKE）、DDL、機微テーブルのSELECT/変更、バックアップ操作。
どこに保管するか: 変更不可/改ざん検知可能なストレージへ集約し、SIEMと相関分析。保持期間は規程に準拠。
性能とプライバシー: サンプリングや対象絞り込みで過負荷を回避。秘匿情報はハッシュ化やトークン化してログに残さない。

-- SQL Server: 監査（権限変更とログインを記録）
CREATE SERVER AUDIT PermissionAudit TO FILE (FILEPATH = 'D:\Audit\');
ALTER SERVER AUDIT PermissionAudit WITH (STATE = ON);

USE YourDb;
CREATE DATABASE AUDIT SPECIFICATION DbAuditSpec
FOR SERVER AUDIT PermissionAudit
  ADD (DATABASE_PERMISSION_CHANGE_GROUP),
  ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
  ADD (SUCCESSFUL_LOGIN_GROUP);
ALTER DATABASE AUDIT SPECIFICATION DbAuditSpec WITH (STATE = ON);

-- PostgreSQL: pgaudit の例（設定ファイルで有効化後）
-- postgresql.conf: shared_preload_libraries = 'pgaudit'
--                  pgaudit.log = 'role, ddl, read, write'
-- DB内:
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- MySQL: 監査ログプラグイン（例）
-- インストール方法は環境依存。以下は概念例。
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET GLOBAL audit_log_policy = ALL;

実践チェックリスト（抜粋）

ロール中心設計で最小権限。広域権限（*ANY*）は原則禁止、DDLは限定。
機微データは列暗号化/マスキングし、平文を永続化しない。鍵はKMS/HSMで分離管理しローテーション。
TLS強制、古いプロトコル無効化。接続文字列/ドライバの暗号化設定を統一。
GRANT/REVOKEやログインを監査し、アラートと定期レビューを運用化。
権限変更・暗号鍵更新・マスキングルールはInfrastructure as Codeで記録し、ステージングで検証後に本番適用。

セキュリティは一度の設定で終わりではありません。sqlによる権限・暗号・監査を「設計→実装→監視→見直し」のサイクルで継続し、組織のポリシーと規制要件に適合させていきましょう。

バックアップとリカバリ戦略（SQL Server中心）

業務停止やデータ損失を最小限に抑えるには、SQL Serverのバックアップとリストアを「設計→運用→検証」のサイクルで標準化することが不可欠です。RPO（許容できるデータ損失）とRTO（復旧に要する時間）を数値で定義し、フル/差分/ログの組み合わせ、復旧モデル、可用性機能、そして自動化を連動させることで、堅牢かつ現実的なリカバリ戦略を構築できます。以下では、sql 運用現場で実用性の高いプラクティスを中心に整理します。

トランザクションログのバックアップ設計

トランザクションログ（T-Log）はポイントインタイム復元を支える中核です。ログチェーンを維持し、適切な頻度でログバックアップを取得することで、RPOを厳密に達成します。

ログチェーンの維持: フル（または差分）→ログ→ログ…の連鎖が切れないようにする。アドホックなフルバックアップは必ずWITH COPY_ONLYを使用。
バックアップ頻度: 目標RPOから逆算。例）RPO=15分なら、ログバックアップを5～15分間隔で設定。
ログ肥大化対策: 長期取引・未コミットトランザクション・レプリケーション/可用性の遅延を監視。VLFの分割過多も性能劣化要因。
信頼性オプション: WITH CHECKSUMで破損を早期検知、RESTORE VERIFYONLYで検証、圧縮（WITH COMPRESSION）でスループット向上とコスト削減。
保管戦略: 3-2-1（複数媒体・オフサイト）を基本に、ネットワーク分離や暗号化（バックアップ暗号化＋鍵管理）を実施。

-- 代表的なログバックアップ（RPO 15分想定）
BACKUP LOG [MyDB]
TO DISK = N'\\backup\MyDB\MyDB_log_20250101_101500.trn'
WITH COMPRESSION, CHECKSUM, STATS = 5;

フル/差分/ログの組み合わせ最適化

復元時間（RTO）と運用負荷のバランスを取りつつ、最短経路で復旧できる鎖を作ります。

一般的な型: 毎日フル＋毎時差分＋5〜15分ログ。多くのワークロードでRTO/RPOのバランスが良い。
高更新率DB: フルを短サイクル（日次→12時間→6時間へ）または差分の頻度を上げて復元ステップ数を減らす。
大規模DB: ストライピング（複数ファイル宛先）で高速化。圧縮と合わせてI/Oを最適化。
フルの取り方: 可用性グループ環境では、差分ベースの更新に影響するため、セカンダリでのフルはCOPY_ONLY推奨。
テールログ: 障害直後はテールログバックアップを取得して最新までの変更を保全。

-- フル、差分、ログ（COPY_ONLYとCHECKSUMを推奨）
BACKUP DATABASE [MyDB]
TO DISK = N'\\backup\MyDB\MyDB_full_20250101_000000.bak'
WITH COMPRESSION, CHECKSUM, STATS = 5;

BACKUP DATABASE [MyDB]
TO DISK = N'\\backup\MyDB\MyDB_diff_20250101_010000.dif'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;

BACKUP LOG [MyDB]
TO DISK = N'\\backup\MyDB\MyDB_log_20250101_011500.trn'
WITH COMPRESSION, CHECKSUM;

復旧モデルの選定基準

復旧モデルはRPO/RTOと処理特性で決めます。運用中の切替は慎重に行い、ログチェーンや監査要件に与える影響を理解しましょう。

FULL: ポイントインタイム復元が必要な本番DBの基本。ログバックアップ必須。
SIMPLE: 一定のデータ損失を許容し、ログ管理を簡素化したい検証・一時DB向け。
BULK_LOGGED: バルクロードやインデックス再構築等の大量処理でログ量を抑制。期間限定の適用が原則。該当ログが含まれる区間はSTOPATでの細粒度復元が制限され得る点に注意。

-- 復旧モデルの確認と切替
SELECT name, recovery_model_desc FROM sys.databases WHERE name = N'MyDB';
ALTER DATABASE [MyDB] SET RECOVERY FULL;  -- ポイントインタイム復元を有効化

データベースコピーと復元のベストプラクティス

本番環境の安全を最優先し、コピーやリストアは再現性と検証可能性を重視して自動化します。パス差異や権限、互換性レベルにも留意します。

COPY_ONLYフルで鎖に影響を与えずコピー用バックアップを作成。
復元時はWITH MOVEで環境差のあるファイルパスへ移設。
RESTORE VERIFYONLYとCHECKSUMで媒体健全性を事前確認。
復元順序の厳守: フル→差分→ログ（時系列）。最後にWITH RECOVERY。
セキュリティ: 復元先のログイン/ユーザーのマッピング（孤立ユーザー）を想定して修復。

-- コピー用のフル（差分ベース維持のためCOPY_ONLY）
BACKUP DATABASE [MyDB]
TO DISK = N'\\backup\MyDB\MyDB_full_copyonly.bak'
WITH COPY_ONLY, COMPRESSION, CHECKSUM;

-- 復元先での検証と復元（ファイル移動）
RESTORE VERIFYONLY
FROM DISK = N'\\backup\MyDB\MyDB_full_copyonly.bak'
WITH CHECKSUM;

RESTORE DATABASE [MyDB_Copy]
FROM DISK = N'\\backup\MyDB\MyDB_full_copyonly.bak'
WITH MOVE N'MyDB' TO N'D:\Data\MyDB_Copy.mdf',
     MOVE N'MyDB_log' TO N'D:\Log\MyDB_Copy.ldf',
     RECOVERY, STATS = 5;

ポイントインタイムリストアの手順

具体的な日時（またはLSN）へ戻す標準手順です。障害発生直後はテールログを確保します。

テールログの確保（可能なら）

BACKUP LOG [MyDB]
TO DISK = N'\\backup\MyDB\MyDB_tail_20250101_120305.trn'
WITH NORECOVERY, COMPRESSION, CHECKSUM;  -- DBをRESTORING状態に

フル→差分→ログを順にNORECOVERYで適用

RESTORE DATABASE [MyDB]
FROM DISK = N'\\backup\MyDB\MyDB_full_20250101_000000.bak'
WITH NORECOVERY, STATS = 5;

RESTORE DATABASE [MyDB]
FROM DISK = N'\\backup\MyDB\MyDB_diff_20250101_110000.dif'
WITH NORECOVERY, STATS = 5;

-- 目的時刻直前までのログを適用
RESTORE LOG [MyDB]
FROM DISK = N'\\backup\MyDB\MyDB_log_20250101_111500.trn'
WITH NORECOVERY;

目的時刻にSTOPATで停止し、RECOVERY

RESTORE LOG [MyDB]
FROM DISK = N'\\backup\MyDB\MyDB_log_20250101_120000.trn'
WITH STOPAT = '2025-01-01T11:58:30',  -- タイムゾーンを明確化
     RECOVERY, STATS = 5;

テスト用コピーのマウント/アンマウント

読み書き検証が必要ならバックアップからの復元、読み取り専用検証ならスナップショットの併用が効率的です。運用中DBのデタッチ/アタッチは原則避けます。

推奨（復元方式）: バックアップ→別名でRESTORE WITH MOVE→検証後DROP DATABASEでアンマウント。
読み取り専用: データベーススナップショットで瞬時に作成し、検証後DROPで破棄。
デタッチ/アタッチ: 本番では非推奨（可用性・監査上のリスク）。テスト環境でのみ使用。

-- 読み取り専用スナップショット（本番のクイック検証用）
CREATE DATABASE [MyDB_SSNAP_20250101]
AS SNAPSHOT OF [MyDB];

-- 検証後
DROP DATABASE [MyDB_SSNAP_20250101];

-- テストコピーのアンマウント（不要になったら）
DROP DATABASE [MyDB_Copy];

可用性機能との連携

可用性グループやレプリカ構成とバックアップポリシーを連携させ、負荷分散と復旧性を両立します。sql ベースのガードレールをジョブに組み込み、実行先を動的に制御します。

Always Onによる高可用性構成

バックアップ実行先の方針: PRIMARY/SECONDARY優先などをAG設定とジョブで一致させる。
セカンダリでのログ/差分バックアップ: 一般に実施可能。フルをセカンダリで取得する場合はCOPY_ONLYを推奨（差分ベース影響の回避）。
システムDB: AGの対象外。個別にバックアップジョブを用意。
監視: レプリカ状態/遅延とバックアップ成否を合わせてダッシュボード化。

-- AGのバックアップ優先レプリカでのみ実行するジョブ例
DECLARE @db sysname = N'MyDB';
IF sys.fn_hadr_backup_is_preferred_replica(@db) = 1
BEGIN
  BACKUP LOG [MyDB]
  TO DISK = N'\\backup\MyDB\MyDB_log_$(ESCAPE_NONE(DATE)).trn'
  WITH COMPRESSION, CHECKSUM, STATS = 5;
END
ELSE
BEGIN
  RAISERROR('Not preferred replica for backup.', 10, 1);
END

自動化ツールとの統合運用

人手を介さずに一貫性を保つには、スケジューラとスクリプトの標準化が鍵です。失敗時の通知と定期的なリストア検証を同じ仕組みに組み込みます。

SQL Server Agent: RPO/RTOを反映したジョブとスケジュール、オペレータ通知（メール/Teams等）。
標準スクリプト: Ola Hallengren氏のMaintenance Solutionの採用やT-SQL/PowerShell（SqlServerモジュールのBackup-SqlDatabase）で統一。
バックアップ整合性の可視化: msdb.dbo.backupset/backupmediafamily から最新取得時刻・サイズ・成否をレポート。
クリーンアップ: 世代管理と暗号鍵のバックアップ、古いファイルの自動削除（保持ポリシー厳守）。
復元テストの自動化: セカンダリ/検証用サーバーに定期的にリストアしてCHECKDBを実行、監査証跡を保存。

-- 直近バックアップ健全性チェック（例）
SELECT
  bs.database_name,
  bs.type,  -- D=フル, I=差分, L=ログ
  bs.backup_start_date,
  bs.backup_finish_date,
  bmf.physical_device_name
FROM msdb.dbo.backupset AS bs
JOIN msdb.dbo.backupmediafamily AS bmf
  ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = N'MyDB'
ORDER BY bs.backup_finish_date DESC;

これらを組み合わせ、計画（RPO/RTO）→取得（フル/差分/ログ）→検証（VERIFYONLY・自動復元テスト）→保管（暗号化・オフサイト）→監視（ジョブ/アラート）のサイクルを回すことで、SQL Serverのバックアップとリカバリ戦略は初めて「実用に耐える」状態になります。

国際化対応と文字列処理

グローバル対応のデータベースでは、SQLでの文字列比較・並び替え・検索の結果が言語や地域の規則に沿うことが重要です。特に日本語は「ひらがな/カタカナ」「全角/半角」「濁点・半濁点」「長音記号」などの扱いが結果に影響します。本章では、照合順序（Collation）とソート規則の考え方、日本語ソートの具体的設定、大小・アクセント・かな/幅の区別、そして文字コードとサロゲートペアの注意点を、主要RDBの実装の違いに触れながら整理します。

照合順序と並び替え規則の理解

照合順序は、SQLで文字列を比較・ソート・等価判定する際の規則を定めます。設定は「データベース」「テーブル/列」「式（クエリ内）」の各レベルで指定でき、結果の正しさとインデックスの利用可否の双方に影響します。代表的なポイントは以下の通りです。

感度の種類: ケース（大文字小文字）、アクセント（ダイアクリティカル）、かな（ひらがな/カタカナ）、幅（全角/半角）、言語固有規則（辞書順、部首画数など）
主な実装差:
- SQL Server: Collation名のサフィックスで挙動を指定（例: _CI/_CS=Case Insensitive/Sensitive、_AI/_AS=Accent Insensitive/Sensitive、_KS=Kana Sensitive、_WS=Width Sensitive、_SC=Supplementary Characters対応）。UTF-8コレーションも利用可。
- MySQL: 8.0以降はICUベースの言語別コレーション（例: utf8mb4_ja_0900_as_cs）。_ai_ciや_as_csのサフィックスでアクセント/ケース感度を指定。
- PostgreSQL: OSロケールまたはICUを用いたコレーションを作成・指定。ICUの拡張キー（例: -u-co-、-u-kn-、-u-kf-、-u-ks-）で細かく制御可能。
列・式のコレーションとインデックスは整合させる: 検索条件と列のコレーションが不一致だと、インデックスが使われない/結果が期待と異なることがあります。

-- SQL Server: 式単位でコレーション指定してソート
SELECT name
FROM dbo.People
ORDER BY name COLLATE Japanese_100_CI_AS_KS_WS;

-- MySQL: 式単位でコレーション指定
SELECT name
FROM people
ORDER BY name COLLATE utf8mb4_ja_0900_as_cs;

-- PostgreSQL: ICUコレーションを作成して利用
CREATE COLLATION ja (provider = icu, locale = 'ja-JP', deterministic = true);
SELECT name FROM people ORDER BY name COLLATE "ja";

参考: SQL Server Collation and Unicode / MySQL Character Sets and Collations / PostgreSQL Collation Support

日本語のソート順を正しく設定する方法

日本語の正しい並び替えには、言語固有の規則を理解した上でコレーションを選ぶことが不可欠です。多くの業務では「五十音順（読み順）」を期待しますが、漢字は部首画数、カタカナ長音「ー」、反復記号「々/ゝゞ」、濁点の有無などの扱いが実装により異なります。代表的な設定例と運用指針は以下の通りです。

SQL Server:
- 基本: Japanese_100_CI_AS（Unicode規則v100）を起点に、必要に応じて_KS（かな区別）や_WS（幅区別）を付与。
- 絵文字など補助文字を含む場合は_SC付き（例: Japanese_100_CI_AS_SC）で比較・ソートの一貫性を確保。
- 列定義例: NVARCHAR(100) COLLATE Japanese_100_CI_AS_KS_WS
MySQL:
- utf8mb4を必ず使用（日本語+絵文字の完全対応）。
- 並び替えにはutf8mb4_ja_0900_as_cs等、言語指定コレーションを採用。
PostgreSQL:
- ICUコレーションでja-JPを作成し、ORDER BYで明示指定。
- 漢字を部首画数で並べたい場合はICUのcoタイプにunihanを選択（用途は限定的）。
読み順の安定化:
- 実務では「読み」専用列（ひらがな/カタカナへ正規化）を持ち、そこでソート・検索するのが最も堅牢。
- 同音異字・長音「ー」の扱いは要件定義で明示。必要なら正規化ルール（例: 長音の除去/同一視）をアプリ層で適用。

-- SQL Server: 読み列での安定ソート
CREATE TABLE dbo.Customer (
  Name      NVARCHAR(100) COLLATE Japanese_100_CI_AS_KS_WS,
  NameYomi  NVARCHAR(100) COLLATE Japanese_100_CI_AS_KS_WS
);
SELECT Name FROM dbo.Customer ORDER BY NameYomi;

-- MySQL: テーブル/列のコレーションを日本語に
ALTER TABLE customers
  MODIFY name VARCHAR(100)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_ja_0900_as_cs;

-- PostgreSQL: ICUで日本語コレーションを作成
CREATE COLLATION ja_yomi (provider = icu, locale = 'ja-JP');
SELECT name FROM customers ORDER BY name COLLATE "ja_yomi";

大文字小文字・アクセントの区別設定

日本語データにもアルファベットや記号が混在します。SQLの照合順序で「何を区別するか」を明示し、要件に応じて使い分けます。

ケース感度（Aとaを同一視するか）:
- SQL Server: _CI（同一視）/ _CS（区別）
- MySQL: _ci（同一視）/ _cs（区別）
- PostgreSQL: ICUで-u-ks-primary等の強度を調整、または用途によりcitext拡張を検討
アクセント感度（é と e を同一視するか）:
- SQL Server: _AI/_AS
- MySQL: _ai/_as
- PostgreSQL: ICUの強度（-u-ks-）で調整
かな感度（ひらがな/カタカナを区別するか）:
- SQL Server: _KSで区別。未指定なら同一視される場合あり。
- MySQL/PostgreSQL: 言語コレーションに依存。要件次第でBINARY比較や事前正規化（ひらがなに統一）を併用。
幅感度（全角/半角を区別するか）:
- SQL Server: _WSで区別。
- MySQL/PostgreSQL: 同上。要件次第で正規化（NFKC等）を適用。

-- SQL Server: アクセントと大文字は区別し、かな・幅も区別
SELECT *
FROM Users
WHERE display_name = N'ﾊｼﾓﾄ'
  COLLATE Japanese_100_CS_AS_KS_WS;

-- MySQL: 大文字小文字・アクセントを区別
SELECT *
FROM users
WHERE display_name COLLATE utf8mb4_ja_0900_as_cs = 'Hashimoto';

-- PostgreSQL: アクセント/ケースを同一視する日本語コレーション（ICU強度をprimaryに）
CREATE COLLATION ja_ai_ci (provider = icu, locale = 'ja-JP-u-ks-primary');
SELECT * FROM users WHERE display_name COLLATE "ja_ai_ci" = 'école';

要件が「検索は大文字小文字・アクセントを同一視、表示や監査は原文を保持」の場合、保存は原文、検索はコレーションや正規化済みの補助列を用いる二段構えが実務的です。

文字コードとサロゲートペアの注意点

国際化対応では、SQLエンジンの文字コードと関数の単位（バイト/コードユニット/文字）の違いを正しく理解しておく必要があります。特に絵文字などの補助文字（サロゲートペア）は落とし穴になりがちです。

文字コードの選択:
- MySQL: utf8（3バイト）は避け、必ずutf8mb4を使用。
- PostgreSQL: サーバエンコーディングはUTF8推奨（クラスタ作成時に指定）。SHOW SERVER_ENCODING;で確認。
- SQL Server: 伝統的にNVARCHAR/NCHARはUTF-16相当。2019以降はUTF-8コレーションを指定するとVARCHAR/CHARでもUTF-8利用可（例: Japanese_100_CI_AS_SC_UTF8）。
長さ・切り詰め・関数の単位:
- MySQL: LENGTH()=バイト数、CHAR_LENGTH()=文字数。utf8mb4では絵文字は1文字・4バイト。
- PostgreSQL: octet_length()=バイト数、length()/char_length()=文字数。
- SQL Server: DATALENGTH()=バイト数、LEN()=末尾空白を除く「コードユニット数」に近い挙動。UTF-16サロゲートは2単位として数えられるため、絵文字1つでもLENは2を返し得ます。
サロゲートペアの注意点（特にSQL Server）:
- NVARCHAR(n)のnはUTF-16のコードユニット数ベース。絵文字は2ユニットを消費するため、NVARCHAR(1)には格納不可。
- LEFT()/SUBSTRING()で中途半端に切ると、片方のサロゲートだけが残ることがあり不正な文字列になる恐れ。補助文字を多用する列を切り詰める処理はアプリ層でグラフェム単位（ユーザー知覚文字）に配慮して行うのが安全。
- _SC付きコレーションを選ぶと、補助文字の比較・ソートの妥当性が向上します（ただし格納長の単位は変わりません）。

-- MySQL: 文字数/バイト数の違い
SELECT CHAR_LENGTH('😀'), LENGTH('😀'); -- 1, 4（utf8mb4）

-- PostgreSQL: 同様
SELECT char_length('😀'), octet_length('😀'); -- 1, 4

-- SQL Server: サロゲートは2ユニット
SELECT LEN(N'😀') AS units, DATALENGTH(N'😀') AS bytes; -- 2, 4

-- SQL Server: UTF-8コレーションのデータベース作成例
CREATE DATABASE AppDb COLLATE Japanese_100_CI_AS_SC_UTF8;

等価比較の一貫性確保には、Unicode正規化（NFC/NFKC等）を事前適用し、格納時と検索時で同一ルールを徹底するのが定石です。正規化は多くのRDBで組み込み関数が限定的なため、アプリケーション層やETLで実施する設計を推奨します。

参考: SQL Server Unicodeと補助文字 / MySQL utf8mb4 / PostgreSQL 多バイト文字サポート

自動化と運用効率化

運用は「繰り返し」と「確実性」が本質です。SQLによるバックアップやスキーマ変更を手作業に頼ると、人的ミスや抜け漏れが発生します。本章では、定期実行の自動化とスキーマ管理のCI/CD連携によって、可用性と品質を両立しながら運用効率を最大化する実践手法を解説します。

バックアップ・メンテナンスの定期実行自動化

バックアップとメンテナンスの自動化は、RPO/RTOの遵守とコスト最適化の土台です。DB製品の機能と外部オーケストレーションを組み合わせ、計画的に実行・監視・保全まで一気通貫で設計します。

設計原則
- 再実行可能性：SQLスクリプトは冪等（idempotent）に。リトライと中断再開を考慮。
- 可観測性：ジョブ結果をメトリクス化（成功/失敗、所要時間、バックアップサイズ）し、アラート連携。
- 分離：バックアップI/Oはピーク帯を避け、リソース制御（帯域/CPU）を設定。
- 保全：暗号化、整合性チェック（例：リストア検証）、保管期間とライフサイクル（オブジェクトストレージ併用）。
- カタログ：取得点のメタデータ（開始/終了時刻、対象、チェックサム）をSQLテーブルで管理しクエリ可能に。

タスク	例	スケジューラ	監視
バックアップ	完全/増分/ログ	SQL Server Agent, cron, Kubernetes CronJob	ジョブ履歴＋ストレージ残量
整合性	チェックサム・検証	SQLジョブ＋週次	検証失敗で即アラート
メンテ	統計更新・インデックス再構成	製品機能 or 外部	所要時間・ブロッキング

例1：SQL Server Agentでのジョブ作成（抜粋）。バックアップの詳細設計は別章に委ね、ここでは自動化の型だけ示します。

-- 例: バックアップジョブ（概略）
BEGIN TRAN;
EXEC msdb.dbo.sp_add_job @job_name = N'Nightly Backup';
EXEC msdb.dbo.sp_add_jobstep
  @job_name=N'Nightly Backup',
  @step_name=N'Full Backup',
  @subsystem=N'TSQL',
  @command=N'-- BACKUP DATABASE ... WITH CHECKSUM, COPY_ONLY;',
  @retry_attempts=3, @retry_interval=5;
EXEC msdb.dbo.sp_add_schedule
  @schedule_name=N'Everyday-02:00', @freq_type=4, @freq_interval=1,
  @active_start_time=020000;
EXEC msdb.dbo.sp_attach_schedule @job_name=N'Nightly Backup', @schedule_name=N'Everyday-02:00';
EXEC msdb.dbo.sp_add_jobserver @job_name=N'Nightly Backup';
COMMIT;

例2：Linuxのcronでのバックアップ＋世代管理（PostgreSQLの一例）。

# /etc/cron.d/db_backup
# 02:00にカスタムフォーマットで出力、90日で削除
0 2 * * * postgres pg_dump -Fc -f /backups/db_$(date +\%F).dump "$PGURL" >/var/log/db_backup.log 2>&1
0 3 * * * postgres find /backups -name 'db_*.dump' -mtime +90 -delete

監視の基本指標（SQLで可視化できるよう保持）：

成功/失敗回数、連続失敗回数
所要時間の移動中央値・95/99パーセンタイル
バックアップサイズ、圧縮率、ストレージ使用率
検証（テストリストアやチェックサム）の結果

注意：自動化に「削除」や「CLEAN」操作を含める場合は、環境ガード（本番での実行禁止フラグ）と多段承認を必須にしてください。

スキーマ管理とCI/CDパイプライン連携

スキーマはアプリケーションと同等に「コード」です。SQLのDDL/DMLをGitでバージョン管理し、CI/CDで検証・配布することで、変更の可視性と再現性を確保します。

バージョン管理：DDL/DMLマイグレーションをディレクトリ構造と命名規則（タイムスタンプや連番）で管理。
静的検査：SQLリンタ（例：SQLFluff）でフォーマット・アンチパターンを検出。
エフェメラルDB：CIでDockerコンテナのDBを起動し、マイグレーション適用とテストを実施。
差分レポート：想定スキーマと適用後スキーマの差分を出力し、レビューに供する。
ステージング展開：データ量に近い環境でパフォーマンステスト。ロック時間やログ量を計測。
本番展開：メンテナンスウィンドウ、トランザクション境界、ロールフォワード/ロールバック戦略を事前定義。
秘密情報：接続情報はシークレットマネージャ連携（OIDCやマネージドID）で注入、SQL内に直書きしない。

GitHub Actionsを用いた最小構成の例（Flywayを想定）。

name: db-migration-ci
on: [pull_request]
jobs:
  test-migration:
    runs-on: ubuntu-latest
    services:
      db:
        image: postgres:15
        env:
          POSTGRES_PASSWORD: pass
        ports: ["5432:5432"]
        options: >-
          --health-cmd="pg_isready -U postgres"
          --health-interval=5s --health-timeout=5s --health-retries=10
    steps:
      - uses: actions/checkout@v4
      - name: Lint SQL
        run: pipx run sqlfluff lint sql/
      - name: Run Flyway
        uses: docker://flyway/flyway:9
        with:
          args: -url=jdbc:postgresql://db:5432/postgres -user=postgres -password=pass -locations=filesystem:./sql migrate info
      - name: Post-migration assertions
        run: |
          psql postgresql://postgres:pass@localhost:5432/postgres \
            -c "SELECT 1 FROM information_schema.tables WHERE table_name='orders';"

マイグレーションの自動テスト

信頼できるリリースは「テスト可能なマイグレーション」から生まれます。SQLの変更はスキーマだけでなくデータにも影響するため、以下を自動化します。

プリコンディション：対象オブジェクトの存在・非存在、空き容量、権限の検査。
適用テスト：DDLの適用、制約・インデックスの存在確認、ビュー/ストアドの依存性検査。
データ移行テスト：列分割/型変更時にデータ損失がないことをサンプルデータで検証。
リグレッション：主要クエリの実行成功と計画の退行がないか（実行時間のしきい値チェック）。
ロールバック/リラン：失敗時に再実行可能か、ダウンスクリプトが機能するか。

簡易的なテストハーネス例（psql）。

#!/usr/bin/env bash
set -euo pipefail
DB="postgresql://postgres:pass@localhost:5432/app"
# 前提確認
psql "$DB" -v ON_ERROR_STOP=1 -c "SELECT current_setting('server_version');"
# 事前状態
psql "$DB" -c "CREATE TABLE IF NOT EXISTS t(id int primary key, v text); INSERT INTO t VALUES (1,'x') ON CONFLICT DO NOTHING;"
# マイグレーション適用
psql "$DB" -f sql/V202508010900__add_new_column.sql
# 事後アサーション
psql "$DB" -c "\d+ t"
psql "$DB" -c "SELECT assert_true((SELECT COUNT(*) FROM t)=1, 'row count mismatch');" 2>&1 | true
# ロールバック検証（ある場合）
test -f sql/U202508010900__add_new_column.sql && psql "$DB" -f sql/U202508010900__add_new_column.sql

長時間ロック・ダウンタイム対策として、オンラインDDLや非ブロッキング手法（例：同義カラム追加→両書き→カットオーバーのExpand/Contract戦略）をテストに組み込みます。

スキーマ差分の管理手法

スキーマ差分は「どう作るか」だけでなく「どう統制するか」が重要です。代表的手法は次の3つです。

マイグレーションベース（変更履歴主導）
- ツール例：Flyway、Sqitch。手続き的にSQLを積み上げ、順序と依存性を明示。
- 利点：履歴の可読性、データ移行ロジックを同梱可。欠点：分岐/マージで競合が起きやすい。
ステートベース（望ましい最終状態の宣言）
- ツール例：Liquibase（diff-changelog）、SSDT/DACPAC（SqlPackage）、Atlas。
- 利点：現在と望ましい状態の差分生成が自動。欠点：データ移行の意図を別途記述が必要。
ハイブリッド
- 定型DDLは差分生成、複雑なデータ移行は手書きSQLで組み合わせ。

差分生成とドリフト検知の実装例：

# Liquibaseで本番とモデルの差分を生成（レビュー用）
liquibase \
  --url=<jdbc-url> --username=<user> --password=<secret> \
  --changelog-file=changelog.sql diff-changelog

# Atlas（宣言）でローカル定義とDBの差分プランを作成
atlas migrate diff --env dev --to "file://schema.hcl" --format '{{ sql . }}'

ブランチ競合と破壊的変更の統制：

命名規則：タイムスタンプベースの連番により並びを明確化。PRマージ時にリベースで整理。
ガードレール：DROPや列削除はデフォルト禁止。レビューラベルやフラグで明示承認。
ドリフト検知：定期ジョブで本番スキーマのハッシュ/ダンプを取得し、Gitの宣言との差異を通知。
安全装置：大規模テーブルのALTERにしきい値（推定ロック時間/行数）を設け、超過時に手動承認。

ポイント：スキーマ差分は「見える化」して初めて運用改善が進みます。差分レポートを必ずPRに添付し、SQLの変更意図（パフォーマンス目的、整合性強化、互換性維持策）を記述する運用ルールを定着させましょう。

クラウド環境でのSQL活用

クラウドでは、アプリケーションのデータ層にSQLを使う際の前提がオンプレミスとは大きく変わります。インフラ運用をサービス側に委ねられる一方で、スケールの仕組み・権限・ネットワーク・可用性モデルなどに起因する設計上の注意点が増えます。ここでは、マネージドRDBの特徴と設計の勘所、そしてサーバーレスや自動スケール特有のパフォーマンス最適化ポイントに絞って整理します。

マネージドRDBの特徴と設計上の考慮点

代表的なクラウドのマネージドRDB（例: Amazon RDS/Aurora、Google Cloud SQL、Azure SQL Database）は、バックアップ、パッチ適用、監視、フェイルオーバーなどをマネージド化し、SQLを使った開発に集中できる環境を提供します。その反面、スーパーユーザー権限やOSレベル操作の制限、拡張機能の制約、ネットワークやスケール挙動に依存したアプリ設計が求められます。

可用性・フェイルオーバー
- マルチAZ/ゾーン冗長構成や自動フェイルオーバーが標準機能として提供されます。アプリ側では接続再試行やアイドポテンシー（同じSQLを再実行しても整合が崩れない設計）を前提にしてください。
- 読み取りレプリカやリージョン間レプリカは整合性が最終確定になる場合があります。読み取り直後の整合性要件が厳しい処理はプライマリで扱うなど、クエリの振り分け設計が必要です。
権限・拡張の制約
- スーパーユーザーや一部のDDL/DCLが制限され、PostgreSQL拡張などもホワイトリスト方式で提供されます。採用予定のSQL機能・拡張のサポート状況を事前に棚卸ししましょう。
- OSアクセスやファイルI/Oを伴う機能（例: 外部ファイルの直接読み書き）は使えない前提で、ストレージサービス経由やETL基盤の併用を検討します。
スケーリングモデルの違い
- 垂直スケール（vCPU/メモリの増減）、読み取りレプリカによる水平スケール、サーバーレス/バースト型など、サービスごとに特徴が異なります。ピーク時・平常時のSQLワークロードを計測し、適したSKU/ティアを選択します。
- スケール操作やメンテナンスに伴う短時間の接続断が起こり得ます。コネクションプールの再接続戦略、トランザクションの再実行方針を定義してください。
ネットワークとセキュリティ
- Private Endpoint/VPC Peeringなどでプライベート接続を基本にし、TLSで暗号化します。認証はユーザー/パスワードに加え、クラウドのIAMやAAD連携が利用できる場合は活用します。
- アプリとDBのリージョン/ゾーンの近接配置でレイテンシを抑制。クロスリージョン通信は遅延とコストの両面を考慮します。
接続・同時実行の上限
- エンジンやサイズごとに接続数の上限があります。アプリ側は接続プールを適切に設定し、1リクエスト1コネクションのような都度接続は避けます。
- チャットネスの高いSQL（大量の小クエリ連発）はRTTの影響を受けやすいので、セット志向のSQLにまとめる・バッチ化するなどの対策を行います。
ストレージ/IOPS/一時領域
- IOPSやスループットはプラン依存です。大規模ソート、ハッシュ結合、一時テーブル多用など一時領域を多く使うクエリは最小化し、必要に応じて適切なインデックス設計とクエリ再設計を行います。
監視・運用
- CPU/メモリ/接続数/ディスク待ち/スロークエリ/レプリカ遅延などのメトリクスとログを活用し、しきい値アラートを設定します。SQLレベルの監査やクエリログは個人情報保護ポリシーに沿って取り扱います。
- 計画外のメジャーアップグレードを避けるため、メンテナンスウィンドウと検証環境での事前テストをルール化します。

参考例（実在サービス）: AWSのAmazon RDS/AuroraはMulti-AZやRDS Proxy、IAM DB認証（MySQL/PostgreSQL）を提供。Google Cloud SQLはAuth Proxyによる安全な接続や高可用性構成を提供。Azure SQL Databaseはゾーン冗長やAzure AD認証、サーバーレスコンピュートなどを提供します。各サービスで名称や詳細は異なるため、採用前に公式ドキュメントでSQL機能と非機能要件の適合を確認しましょう。

サーバーレス/自動スケール時のパフォーマンス最適化

サーバーレスや自動スケール型のマネージドRDBは、ワークロードに応じてコンピュートが伸縮します。便利な一方、スケールの瞬間や最小キャパシティ時にSQLの待ち時間が増えがちです。スパイクに強く、スループットを最大化するための実践ポイントをまとめます。

コネクションプール/プロキシの徹底
- アプリ側で接続プールを使い、リクエストごとの都度接続をやめます。接続上限・アプリの同時実行数・DBワーカー数のバランスを見直します。
- マネージドの接続プロキシ（例: RDS Proxy）や軽量プール（例: PgBouncerのtransaction pooling）を併用し、スパイク時のコネクション風船化を抑制します。
短いトランザクションと小さなバッチ
- 自動スケール環境では長時間ロックやMVCCの肥大化がスケール効率を落とします。コミット間隔を短くし、1トランザクションの更新件数を適度に分割します。
- 読み取り専用クエリは可能ならREAD COMMITTED/スナップショット分離で実行し、書き込みと干渉しにくいようにします。
計画的なスパイク吸収
- アプリ側でキューイングやレートリミット、エクスポネンシャルバックオフを実装し、スケール反応時間内にDBへ過負荷が集中しないようにします。
- 重い分析系SQLはオフピークに移動、もしくはDWH/専用分析基盤へオフロードします。
プラン安定性とパラメータ化
- パラメータ化クエリでプランキャッシュの効果を高め、スケール時のコンパイル負荷を低減します。ベンダーが提供するプラン安定化機能（例: プラン固定/クエリストア相当機能）があれば活用します。
- 統計情報の自動更新に依存しすぎず、更新頻度や対象テーブルのサイズに応じてメンテナンスを計画します。
I/Oと一時領域の削減
- ORDER BYやGROUP BY、ハッシュ結合で大きなワークファイルが発生するSQLは、適切なインデックスや結合順序の見直しでI/Oを削減します。
- 一時テーブル・マテリアライズの多用は最小限にし、可能ならCTEやウィンドウ関数を用いたストリーム処理的な書き方を検討します。
読み取りスケールと整合性設計
- 読み取りレプリカ/読み取り専用エンドポイントへSELECTを逃がし、書き込みをプライマリに集中。レプリカ遅延を踏まえ、ダッシュボード等は近似リアルタイムで許容するなどUX設計を調整します。
ミニマムキャパシティとスケールしきい値
- 最小コンピュートを設定できる場合は、日中のベース負荷を下回らない値を設定し、頻繁なスケールスラッシングを防ぎます。
- コールドスタートが避けられないサービスでは、業務バッチの開始前にウォームアップジョブを計画的に行うなど、ビジネススケジュールに合わせた対策をします。
障害時の振る舞いをあらかじめ定義
- 接続断・タイムアウト・スロットリング（スロットル）時の再試行回数や待ち時間、キャンセル条件を標準化し、SQLの副作用が二重適用されないように設計します。

これらはエンジン非依存の原則であり、SQLの書き方（セット志向・パラメータ化・一時領域の最小化）とクラウドの特性（接続管理・スケール挙動・ネットワークレイテンシ）を両輪で最適化することが、クラウド時代の高信頼・高効率なデータ基盤づくりの鍵となります。

トラブルシューティング

本番・開発を問わず、sqlの運用で最も多い問い合わせは「クエリが遅い」「接続できない/タイムアウトする」の2つです。場当たり的に対応すると再発しがちなので、事実の採取→原因の切り分け→応急処置→恒久対策という順序で進めます。以下に、現場でそのまま使える診断ステップをまとめます。

クエリ遅延の診断ステップ

遅延の正体は「どこで待っているか」を突き止めることに尽きます。まずスコープと事実を固め、実行計画と待機イベントでボトルネックを特定し、最後に入力（データ分布）・統計・インデックス・ロック・リソースのどれが原因かを切り分けます。

事象のスコープ確定（いつから/どのsql/どの環境）
- 単発か恒常か（直近デプロイやデータ量増加、ピーク時間帯の変化の有無）。
- 遅いのは特定のsqlか全体か（ダッシュボードやAPM、DBメトリクスで確認）。
- ベースライン（通常時の実行時間/IO/行数）と現在を比較できる材料を集める。

遅いsqlの特定（現行/履歴）

PostgreSQL: pg_stat_statements と pg_stat_activity

-- Top10 by total time
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- 実行中セッションと待機
SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state <> 'idle';

MySQL: Slow Query Log / performance_schema

-- 直近遅いステートメント
SELECT EVENT_NAME, TIMER_WAIT, SQL_TEXT
FROM performance_schema.events_statements_history_long
ORDER BY TIMER_WAIT DESC
LIMIT 10;

SQL Server: Query Store / DMVs

SELECT TOP 10 qs.total_elapsed_time, qt.[text]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_elapsed_time DESC;

実行計画の取得と検証
- PostgreSQL: EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
```
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <your SQL>;
```
- MySQL: EXPLAIN / EXPLAIN ANALYZE（8.0+）
- SQL Server: 実実行プラン（Include Actual Execution Plan）または SET STATISTICS IO/TIME ON
着目点：フルスキャン、重いNested Loop、巨大なSort/Hash、推定行数と実行行数の乖離、リモート/一時オブジェクトへのアクセス。
待機イベント/ロックの把握（CPU待ちかIO待ちかロック待ちか）
- PostgreSQL: pg_locks と待機イベント
```
SELECT a.pid, a.wait_event_type, a.wait_event, l.locktype, l.mode, a.query
FROM pg_stat_activity a
LEFT JOIN pg_locks l ON a.pid = l.pid
WHERE a.state <> 'idle';
```
- MySQL: performance_schema.events_waits*, sys.innodb_lock_waits
- SQL Server: sys.dm_os_waiting_tasks, sys.dm_tran_locks, ブロッキングチェーン
典型：LOCK/LSN/TRANSACTIONで待っている場合はブロッキングの親を特定し、まずは長時間トランザクションや「idle in transaction」を解消します。
原因の切り分け（再現実験と最小化）
- 入力依存: WHERE条件やパラメータ値を変えると速さが変わる→選択度/ヒストグラム/パラメータスニッフィングが疑わしい。
- スキーマ/統計: 統計の古さ、欠落インデックス、非選択的インデックス。
- クエリ形状: 関数で列をラップ（index無効化）、暗黙の型変換、不要なSELECT *, 不要なORDER BY、広いJOIN。
- リソース: CPU飽和、ディスクIO待ち、メモリスピル（Temp/Spill to disk）、ネットワーク遅延。
応急処置と恒久対策
- 応急: 統計更新、ヒントでプラン固定、ブロッカーのkill、ステートメントタイムアウト設定、対象パーティション限定。
- 恒久: カバリング/選択的なインデックス追加、クエリの再設計（CTE/サブクエリの簡素化、不要なソート排除）、バッチ処理化、パラメータ化戦略の見直し、適切なメンテナンス（統計/再編成）。

クイックチェックリスト
- データ量が増えたテーブルに対して統計更新は最新か。
- フィルタ列に関数・計算を当てていないか（索引利用不可）。
- 結合キーの型が一致しているか（暗黙変換で全表走査）。
- 大規模ソート/集約がメモリしきい値を超えディスクにスピルしていないか。
- 長時間トランザクションがロックを保持していないか。

TIP: MySQLはSlow Query Log、PostgreSQLはauto_explain、SQL ServerはQuery Storeを常時有効化すると、遅延発生時に履歴から直ちに遡れます。

接続エラーやタイムアウトの原因切り分け

「接続できない/タイムアウト」は、接続前（ネットワーク/認証）か、接続後（コマンド実行中）のどちらで止まっているかを区別するのが第一歩です。メッセージと層（DNS→TCP→TLS→認証→DB内部→アプリ）で切り分けます。

タイムアウトの種類を判別
- Connect/Login Timeout（接続確立までに失敗）：DNS、FW、ポート、TLS、認証。
- Command/Statement Timeout（接続後の実行が遅い）：クエリ遅延側の問題（前節の手順へ）。
- Idle Timeout（アイドル切断）：サーバ/プロキシ/ロードバランサのアイドル設定かKeepalive不整合。
ネットワーク層の確認（最短1分でできる基本動作確認）
- 名前解決: nslookup/dig でFQDN→IPが正しいか。
- 到達性: nc -vz <host> 5432|3306|1433 でポート疎通（ICMPが閉じていてもTCPで確認）。
- TLS: openssl s_client -connect host:port -servername host で証明書/プロトコル交渉を確認。
- 経路: traceroute/mtr で途中の遅延/ドロップを確認。

サーバ側の上限/状態を確認

PostgreSQL:

SHOW max_connections;
SHOW superuser_reserved_connections;
SELECT COUNT(*) FROM pg_stat_activity;
-- ログ: "too many connections", "password authentication failed"

MySQL:

SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
-- エラー例: "Too many connections", "Host is not allowed to connect"

SQL Server:

SELECT COUNT(*) FROM sys.dm_exec_sessions;
SELECT * FROM sys.configurations WHERE name = 'user connections';
-- エラー例: "Login failed for user", "Timeout expired"

管理型（AWS RDS/Aurora, Azure SQL, Cloud SQL）はFW/セキュリティグループ/承認済みネットワークを再確認。

認証・暗号化の切り分け
- ユーザー/パスワード/ロールの誤り、期限切れ、ロック。
- TLS必須設定とクライアント側の証明書/CAチェーン不備（特にAzure SQL, Cloud SQL）。
- 接続文字列のオプション（sslmode/Encrypt/TrustServerCertificateなど）の整合性。
コネクションプールの健全性
- 上限値（max pool size）に到達し枯渇→リクエスト待ちでタイムアウト。
- リーク（借りた接続を返さない）、短すぎるタイムアウト（connectTimeout/loginTimeout/socketTimeout）。
- pgbouncer/ProxySQL/Azure Gateway等の中間層でのアイドル切断・キュー滞留。
- 推奨の初期値：コア数×並列度を超えない範囲でpoolを設定し、「1リクエスト=1接続」は避ける。
典型エラーと一次対応
- Too many connections: 一時的に上限引き上げ/不要セッションの切断、恒久対策としてプール調整とクエリ短縮。
- Connection refused / timeout: サーバ停止/ポート閉塞/SG・FW誤設定。直近のネットワーク変更のロールバックも検討。
- SSL handshake failed: 証明書更新漏れ/CA不一致。新しいCAバンドルを配布し、サーバ名表示（SNI）を有効化。
- Login failed: 資格情報、ロール、接続元制限、データベース名の誤りを是正。
- Idle timeout: LB/プロキシ/DBのアイドル時間設定とTCP keepaliveを一致させる。

ドライバ別代表的タイムアウト設定
- PostgreSQL（psql/Npgsql/JDBC）: connectTimeout/loginTimeout、socketTimeout、statement_timeout（サーバ側）。
- MySQL Connector/J: connectTimeout、socketTimeout、enabledTLSProtocols、requireSSL。
- SQL Server JDBC/ODBC: loginTimeout、queryTimeout/CommandTimeout、Encrypt/TrustServerCertificate。

TIP: アプリサーバと同一ネットワーク内からCLI（psql/mysql/sqlcmd）で接続し、同じ接続文字列で再現するかを確かめると、アプリ側かDB/ネットワーク側かの切り分けが一気に進みます。ログ（DB・ドライバ・プロキシ）を時刻同期させて突き合わせるのも有効です。