sql-best-practices

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 インジェクションを防ぐため、パラメーター化クエリを使用します

-- Preferred: Use EXISTS for existence checks
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'
);

-- Avoid: Function on indexed column
WHERE YEAR(order_date) = 2024

-- Preferred: Range comparison
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'

JOIN のベストプラクティス

• WHERE 句での暗黙的な結合ではなく、必ず明示的な JOIN 構文を使用します
• 結合の種類（INNER、LEFT、RIGHT、FULL OUTER）を明示的に指定します
• 可能であれば、大きいテーブルから小さいテーブルの順に結合します
• データ要件に基づいて適切な結合の種類を使用します
• CROSS JOIN は意図したものであることを確認し、慎重に使用します

-- Explicit join (preferred)
SELECT c.name, o.order_id
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id;

-- Avoid implicit join
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 を使用します
• 頻繁に実行するクエリでは、クエリキャッシュを検討します

-- Pagination example
SELECT product_id, product_name, price
FROM products
ORDER BY product_id
LIMIT 20 OFFSET 40;

集計のベストプラクティス

• 可能であれば、グループ化の前にフィルターを適用します（WHERE と HAVING の使い分け）
• 適切な集計関数（COUNT、SUM、AVG など）を使用します
• 累計や順位付けには、ウィンドウ関数を検討します

-- Efficient: Filter before aggregation
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 文字列に直接連結してはいけません
• データベースユーザーには最小権限の原則を適用します
• 機密データへのアクセスを監査し、ログに記録します
• 保存時および転送時に機密データを暗号化します

-- Use parameterized queries (pseudo-code)
PREPARE stmt FROM 'SELECT * FROM users WHERE username = ?';
EXECUTE stmt USING @username;

データ変更のベストプラクティス

INSERT 操作

• 必ず列名を明示的に指定します
• 可能であれば、複数行の挿入には一括挿入を使用します
• 挿入または更新を行う場合は、MERGE／UPSERT の使用を検討します

INSERT INTO customers (customer_name, email, created_at)
VALUES
    ('John Doe', '[email protected]', CURRENT_TIMESTAMP),
    ('Jane Smith', '[email protected]', CURRENT_TIMESTAMP);

UPDATE 操作

• （すべての行を意図的に更新する場合を除き）必ず WHERE 句を指定します
• UPDATE クエリは、最初に SELECT でテストします
• 重要な更新では、トランザクションの使用を検討します

DELETE 操作

• 必ず WHERE 句を指定します
• 復元可能なデータには、論理削除（ステータスフラグ）を使用します
• 関連テーブルへの CASCADE の影響を検討します

命名規則

• テーブル名と列名には snake_case を使用します
• テーブル名には単数形の名詞を使用します（customers ではなく customer）
• 主キーにはテーブル名を接頭辞として付けます：customer_id
• otではなくorder_totalのように、説明的な名前を使用します
• ブール型の列には適切な接頭辞を付けます：is_active、has_shipped

ドキュメント

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

エラー処理

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