JSONクエリパターンからSQLインデックスを生成する

インデックスの生成

インデックスは、テーブル全体をスキャンせずに行を検索できるようにすることで、クエリパフォーマンスを向上させます。コンバーターは、一般的にフィルタ、ソート、結合されるカラムに対してCREATE INDEX文を生成します。

JSON例

{
  "id": 1,
  "user_id": 42,
  "status": "active",
  "created_at": "2024-06-15T10:30:00Z",
  "email": "alice@example.com",
  "total_amount": 299.99
}

生成されるSQL

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  status VARCHAR(50) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  email VARCHAR(255) NOT NULL,
  total_amount DECIMAL(10, 2) NOT NULL
);

CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_created_at ON orders (created_at);
CREATE INDEX idx_orders_email ON orders (email);

インデックスが生成されるカラム

コンバーターは以下に対してインデックスを生成します：

1. 外部キー — _idで終わるカラム（JOIN性能に不可欠）。
2. ステータス/状態カラム — WHERE句で使用される低カーディナリティカラム。
3. タイムスタンプカラム — 日付範囲クエリとソートで頻繁に使用される。
4. メール/ユーザー名 — ルックアップで一般的に使用されるカラム。

インデックスの種類

種類	エンジン	使用場面
B-tree（デフォルト）	すべて	汎用、範囲クエリ、ソート
Hash	PostgreSQL、MySQL	完全一致ルックアップのみ
GIN	PostgreSQL	全文検索、JSONB、配列
GiST	PostgreSQL	幾何学型、範囲型、最近傍検索

複合インデックス

複数のカラムでフィルタするクエリには、複合インデックスがより効率的です：

CREATE INDEX idx_orders_user_status ON orders (user_id, status);

カラムの順序が重要です — 最も選択性の高いカラムを最初に、範囲条件で使用されるカラムを最後に配置してください。

部分インデックス

CREATE INDEX idx_orders_active ON orders (created_at)
  WHERE status = 'active';

このより小さなインデックスはアクティブな注文のみをカバーし、高速でディスクスペースの使用量も少なくなります。

過剰インデックスの警告

すべてのインデックスはINSERT、UPDATE、DELETE操作を遅くし、ストレージを消費します。コンバーターは単一テーブルに5つ以上のインデックスを生成する場合に警告コメントを追加します。これは確認と統合が必要なサインです。

命名規則

通常のインデックスにはidx_{テーブル}_{カラム}、ユニークインデックスにはuq_{テーブル}_{カラム}を使用してください。一貫した命名により、メンテナンスとデバッグが大幅に容易になります。