SQL ベストプラクティス

コア原則

• 一貫したフォーマットと意味のあるエイリアスを使用した、明確で読みやすい SQL を書く
• 適切なインデックスと最適化を通じてクエリパフォーマンスを優先する
• SQL インジェクションを防ぐためのセキュリティベストプラクティスを実装する
• データの整合性のためにトランザクションを適切に使用する
• 複雑なクエリにはインラインコメントを付ける

クエリ記述標準

フォーマットとスタイル

• SQL キーワード (SELECT、FROM、WHERE、JOIN) は大文字を使用する
• 読みやすさのため、各主要句を新しい行に配置する
• 意味のあるテーブルエイリアスを使用する (例: customers AS c ではなく customers AS x)
• サブクエリとネストされた条件は一貫してインデントする
• カラムリストと条件をそろえて視覚的な明確性を実現する

SELECT
    c.customer_id,
    c.customer_name,
    o.order_date,
    o.total_amount
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
    AND o.status = 'completed'
ORDER BY o.order_date DESC;

カラム選択

• 本番環境では SELECT * を避け、必要なカラムを明示的にリストアップする
• カラムエイリアスを使用して出力を明確にする: SELECT first_name AS "First Name"
• SELECT のカラム順序を論理的グループ分けのため検討する

フィルタリングと条件

• WHERE 句では最も制限的な条件を最初に配置する
• 適切な演算子を使用する: 複数の OR 条件より IN を優先する
• 存在をチェックする場合はサブクエリの IN より EXISTS を使用する
• WHERE 句では可能な限りインデックス付きカラムに関数を適用しない
• SQL インジェクションを防ぐためにパラメータ化クエリを使用する

-- 推奨: 存在チェックに EXISTS を使用
SELECT c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1 FROM orders AS o
    WHERE o.customer_id = c.customer_id
    AND o.order_date > '2024-01-01'
);

-- 避ける: インデックス付きカラムの関数
WHERE YEAR(order_date) = 2024

-- 推奨: 範囲比較
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'

JOIN ベストプラクティス

• WHERE での暗黙的な結合の代わりに、常に明示的な JOIN 構文を使用する
• JOIN タイプを明示的に指定する (INNER、LEFT、RIGHT、FULL OUTER)
• 可能な場合は最大から最小のテーブル順に結合する
• データ要件に基づいて適切な JOIN タイプを使用する
• CROSS JOIN は慎重に扱い、意図的であることを確認する

-- 明示的な結合 (推奨)
SELECT c.name, o.order_id
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id;

-- 避ける: 暗黙的な結合
SELECT c.name, o.order_id
FROM customers c, orders o
WHERE c.customer_id = o.customer_id;

パフォーマンス最適化

インデックス設計ガイドライン

• WHERE、JOIN、ORDER BY 句で使用されるカラムにインデックスを作成する
• 複数カラムのクエリについて複合インデックスを検討する
• 過度なインデックス作成を避ける。各インデックスは書き込みオーバーヘッドを追加する
• 定期的にインデックスを分析および保守する
• 頻繁に実行されるクエリにはカバリングインデックスを使用する

クエリ最適化

• EXPLAIN/EXPLAIN ANALYZE を使用してクエリ実行プランを理解する
• 全結果が必要でない場合は TOP/LIMIT で結果セットを制限する
• 大規模な結果セットにはページネーションを使用する
• 相関サブクエリは可能な限り避け、JOIN を使用する
• 頻繁に実行されるクエリについてはクエリキャッシュを検討する

-- ページネーション例
SELECT product_id, product_name, price
FROM products
ORDER BY product_id
LIMIT 20 OFFSET 40;

集約ベストプラクティス

• 可能な場合は GROUP BY の前にフィルタリングを行う (WHERE と HAVING の比較)
• 適切な集約関数を使用する (COUNT、SUM、AVG など)
• 累計合計とランキングのためウィンドウ関数を検討する

-- 効率的: 集約前にフィルタリング
SELECT category_id, COUNT(*) AS product_count
FROM products
WHERE active = true
GROUP BY category_id
HAVING COUNT(*) > 10;

トランザクション管理

• トランザクションはできるだけ短く保つ
• ユースケースに適切な分離レベルを使用する
• ROLLBACK を含めたエラーハンドリングを常に含める
• オープンなトランザクション中のユーザーインタラクションを避ける
• 複雑なマルチステップ操作にはセーブポイントを使用する

BEGIN TRANSACTION;

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

IF @@ERROR <> 0
    ROLLBACK TRANSACTION;
ELSE
    COMMIT TRANSACTION;

セキュリティベストプラクティス

• 常にパラメータ化クエリまたはプリペアドステートメントを使用する
• ユーザー入力を SQL 文字列に直接連結しない
• データベースユーザーに最小権限の原則を適用する
• 機密データアクセスを監査およびログに記録する
• 機密データを保存時および転送中に暗号化する

-- パラメータ化クエリの使用 (疑似コード)
PREPARE stmt FROM 'SELECT * FROM users WHERE username = ?';
EXECUTE stmt USING @username;

データ修正ベストプラクティス

INSERT 操作

• カラム名を常に明示的に指定する
• 可能な場合は複数行に対してバルク挿入を使用する
• insert-or-update シナリオについて MERGE/UPSERT の使用を検討する

INSERT INTO customers (customer_name, email, created_at)
VALUES
    ('John Doe', 'john@example.com', CURRENT_TIMESTAMP),
    ('Jane Smith', 'jane@example.com', CURRENT_TIMESTAMP);

UPDATE 操作

• WHERE 句を常に含める (すべての行を意図的に更新する場合を除く)
• UPDATE クエリは SELECT で先にテストする
• 重要な更新についてはトランザクションの使用を検討する

DELETE 操作

• WHERE 句を常に含める
• 復元可能なデータに対してはソフトデリート (ステータスフラグ) を使用する
• 関連テーブルへの CASCADE の影響を検討する

ネーミング規則

• テーブルとカラム名には snake_case を使用する
• テーブル名には単数形を使用する (customers ではなく customer)
• プライマリキーにテーブル名をプレフィックスとする: customer_id
• 説明的な名前を使用する: ot ではなく order_total
• ブール値カラムに適切にプレフィックスを付ける: is_active、has_shipped

ドキュメント

• クエリ内の複雑なビジネスロジックにコメントを付ける
• ストアドプロシージャを目的、パラメータ、例でドキュメント化する
• テーブルとカラムの説明のためデータディクショナリを保守する
• データベーススキーマの変更をバージョン管理する

エラーハンドリング

• ストアドプロシージャに適切なエラーハンドリングを実装する
• デバッグに十分なコンテキストを含めてエラーをログに記録する
• 呼び出し側のアプリケーションに意味のあるエラーメッセージを返す
• サポートされている場所では TRY-CATCH ブロックを使用する