Day 10: 數據庫工作流：ChatGPT 自動生成高效 SQL Query、Migration 與 Index 優化

18th鐵人賽

蔡

2026-09-01 09:13:27

57 瀏覽

分享至

Day 10: 數據庫工作流：ChatGPT 自動生成高效 SQL Query、Migration 與 Index 優化 (Database SQL Migration Index)

本日核心價值 (Core Focus): 讓模型產出「可審查的 PostgreSQL 變更包」，而不是一段可直接在 production 執行的 SQL。強制四件套：EXPLAIN 意圖、$1 參數化、index 建議、migration up/down。對照 N+1 與有 index 的查詢。LLM SQL 未經審查不得執行；生成階段只用 read-only 角色。

概念說明與實戰情境 (Overview)

訂單列表頁慢，常是應用層對每筆訂單再查一次 order_items（N+1），或缺了 (customer_id, created_at) 這類複合 index。把 Schema 丟進 ChatGPT 並說「優化」會得到沒參數的 SQL、或不可逆的 DROP。工作流應相反：先給真實 Schema 與慢查詢症狀，Prompt 鎖死輸出格式；人審過後才在 staging 跑 EXPLAIN (ANALYZE, BUFFERS)。生成與解釋用 read-only 角色；migration 套用與 LLM 呼叫分離。本日 Schema 為 orders / order_items。

關鍵操作與範例 (Implementation & Example)

先固定 Schema（PostgreSQL 14+）。這是 Prompt 的唯一事實來源，不要讓模型「補欄位」。欄位名、型別、CHECK 約束都要貼原文；模型一旦自行加入 orders.total 或 order_items.warehouse_id，後面的 index 與 migration 會建在不存在的欄上。生成用的連線角色禁止 DDL，即使模型輸出 CREATE INDEX 也跑不了——那是特性，不是阻礙。

CREATE TABLE orders (
    id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id   bigint NOT NULL,
    status        text NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id    bigint NOT NULL REFERENCES orders (id),
    sku         text NOT NULL,
    qty         integer NOT NULL CHECK (qty > 0),
    unit_price  numeric(12, 2) NOT NULL
);

反例：N+1（應用偽碼 + SQL）。 列表 50 筆訂單會變成 1 + 50 次 round-trip，就算每次都走 PK 也會被延遲與連線佔用打死。

# BAD: N+1 — 不要用於 production
orders = db.execute("SELECT id, status FROM orders WHERE customer_id = %s", [cid]).fetchall()
for o in orders:
    o.items = db.execute(
        "SELECT sku, qty, unit_price FROM order_items WHERE order_id = %s",
        [o.id],
    ).fetchall()

正例：一次 JOIN（或兩次查詢 + 記憶體 group），參數用 PostgreSQL $1。 ORM 請開 selectinload / JOIN FETCH；本日示範 SQL。

-- GOOD: 單一 statement，呼叫端綁 $1 = customer_id
SELECT o.id AS order_id,
       o.status,
       o.created_at,
       i.sku,
       i.qty,
       i.unit_price
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.id
WHERE o.customer_id = $1
ORDER BY o.created_at DESC, i.id;

若列表只要最近 20 張單，先限訂單再 JOIN，避免把該顧客全部歷史列掃進來：

WITH recent AS (
    SELECT id, status, created_at
    FROM orders
    WHERE customer_id = $1
    ORDER BY created_at DESC
    LIMIT 20
)
SELECT r.id AS order_id, r.status, r.created_at, i.sku, i.qty, i.unit_price
FROM recent AS r
JOIN order_items AS i ON i.order_id = r.id
ORDER BY r.created_at DESC, i.id;

強制 Prompt（生成階段）。 輸出必須是 JSON，方便 Codex / CI 做機械檢查：有沒有 $1、有沒有 DOWN。六個鍵的審查意義如下：explain_intent 用來對人溝通「為什麼這樣寫」，不能代替真實 EXPLAIN；read_sql 是唯一准許在 staging 預跑的語句，且必須參數化；params 讓應用層用位置綁定，而不是靠模型把數字嵌進字串；index_suggestions 是提案不是 DDL；migration 才是可進版控的 up/down；risks 專門標 CONCURRENTLY 與寫入放大。缺任何一鍵就當生成失敗。

你是 PostgreSQL 14 查詢與 migration 審查助理。只根據我提供的 Schema 與症狀作答。
禁止發明欄位、禁止 SELECT *、禁止字串拼接 SQL、禁止 DROP TABLE。

症狀：依 customer_id 列出最近訂單與品項時，應用對每張訂單再查 order_items（N+1），p95 升高。

你必須輸出一個 JSON 物件（不要 Markdown），鍵如下：
1. explain_intent: 用中文說明執行計畫預期（Seq Scan / Index Scan、JOIN 策略、排序是否能用 index）。這是意圖，不是實際 EXPLAIN 輸出。
2. read_sql: 一條參數化 SELECT，placeholder 必須是 $1、$2… 對應 params 陣列。
3. params: [{ "name": "customer_id", "pg_type": "bigint", "position": 1 }]
4. index_suggestions: [{ "name": string, "table": string, "using": "btree", "columns": [string], "reason": string }]
5. migration: { "version": "20260817_01", "up": string, "down": string }
6. risks: [string]  // 例如寫入放大、部分 index、CONCURRENTLY 不能包在交易

硬性規則：
- read_sql 出現任何字面 customer_id / 訂單編號數字就失敗。
- migration.up 只准 CREATE INDEX；若用 CREATE INDEX CONCURRENTLY，在 risks 註明不可包 BEGIN/COMMIT。
- migration.down 必須能還原本次 up（DROP INDEX），不可刪資料。
- 若需要部分 index，寫明 WHERE 條件與為何不用完整 index。

模型可能建議的 index（審查後再套）：列表過濾 + 排序通常要 customer_id 領先，created_at 在後。order_items.order_id 已有 FK，但仍建議顯式 index，因為 PostgreSQL 不會為 FK 自動建 index。

-- migration UP (20260817_01)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created
    ON orders (customer_id, created_at DESC);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_order_items_order_id
    ON order_items (order_id);

-- migration DOWN
DROP INDEX CONCURRENTLY IF EXISTS idx_order_items_order_id;
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer_created;

CONCURRENTLY 避免長時間鎖表，但不能放在交易區塊；migration runner 要關掉自動 transaction（Flyway / golang-migrate / 部分 EF 設定都要查文件）。小表或維護窗可用一般 CREATE INDEX 包在交易裡，讓 up/down 原子化。

角色要拆成三條連線，不要共用一個 DATABASE_URL：

生成服務：chatgpt_explain，只有 SELECT（加 EXPLAIN 所需權限），拿來驗證「這條 SQL 能否 parse」，不可跑 DDL。
審查者 / staging：DBA 或開發者本機，跑 EXPLAIN (ANALYZE, BUFFERS) 與對照舊查詢的 p95。
migration 套用：CI 或 DBA 帳號，只能在通過 checklist 後執行 up。production 寫入帳號的密碼不得出現在 LLM 服務的環境變數清單。

「生成與執行分離」比「Prompt 寫請勿刪除資料」有效。模型輸出什麼都可以；跑得動的只有你允許的角色。

生成後的人審 + 機器審 checklist：

read_sql 含 $1 且 params 對得上。
DOWN 存在且只撤銷本次 index。
在 staging 用 read-only 角色跑：

EXPLAIN (ANALYZE, BUFFERS)
-- 把 $1 換成 PREPARE/EXECUTE，不要在 psql 裡貼業務 ID 進聊天紀錄
PREPARE order_list (bigint) AS
SELECT o.id, o.status, o.created_at, i.sku, i.qty, i.unit_price
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.customer_id = $1
ORDER BY o.created_at DESC, i.id;

EXECUTE order_list (42);

預期：orders 走 idx_orders_customer_created，order_items 走 idx_order_items_order_id（或 PK 若以 id 查找）。若仍是 Seq Scan 且 rows 很大，先確認統計資訊（ANALYZE orders）與實際 customer_id 選擇性，不要急著再加 index。

Python 只負責「叫模型 + 驗證 JSON 形狀」，連線用唯讀使用者。不要在同一 process 執行 migration.up。

"""sql_review_gate.py — 生成後機械檢查，不執行 SQL"""
import json
import re

def validate_bundle(doc: dict) -> None:
    sql = doc["read_sql"]
    if re.search(r"customer_id\s*=\s*\d+", sql, re.I):
        raise ValueError("literal customer_id is forbidden")
    if "$1" not in sql:
        raise ValueError("read_sql must use $1")
    if "DROP TABLE" in doc["migration"]["up"].upper():
        raise ValueError("up must not drop tables")
    if "DROP INDEX" not in doc["migration"]["down"].upper():
        raise ValueError("down must drop the index created by up")

注意事項與常見失敗 (Pitfalls)

把 LLM SQL 直接 cursor.execute： 這是資料毀滅路徑。生成、審查、套用要分角色；production 寫入角色的連線字串不要給生成服務。即使只是 SELECT，沒有參數化的語句也不該自動跑。
生成階段用 superuser： 模型若輸出 UPDATE/DELETE 也跑得動。專用 chatgpt_explain 角色：CONNECT + SELECT，必要時 pg_read_all_stats 看 EXPLAIN，沒有 INSERT/UPDATE/DELETE/DDL。權限用 REVOKE 寫明白，不要只靠「我們約定不執行」。
N+1 被「加 cache」掩蓋： 正確修法是一次取回 items 或 JOIN；cache 不能當 Schema / index 的替代。快取還會讓你看不見真正的 p95，問題會在 cache miss 或新顧客時爆開。
只在 orders(customer_id) 建單欄 index： ORDER BY created_at DESC 仍可能 extra sort。複合 (customer_id, created_at DESC) 才對得上本日查詢。加 index 前用 EXPLAIN 看有沒有 Sort 節點，不要憑欄位名稱猜測。
CREATE INDEX CONCURRENTLY 包在 transaction： PostgreSQL 會報錯。CI migration 要對這類語句關自動交易。文件裡要把「此檔不可包 BEGIN」寫在 migration 註解，避免下一位直接複製模板。
Prompt 沒禁止字面值： 模型愛寫 WHERE customer_id = 123。用 JSON gate 拒絕；這也避免 SQL injection 與把真實 ID 寫進文章/log。審查清單把「字面數字」當成阻擋項，與「有沒有 DOWN」同一級。
相信模型的 explain_intent 當實際計畫： 那是意圖說明。要以 staging 的 EXPLAIN (ANALYZE, BUFFERS) 為準。資料量差兩個數量級時，Seq Scan 可能才是對的；不要為了「模型說要 Index Scan」硬加用不到的 index。

本日總結 (Takeaways)

數據庫工作流的交付物是「參數化 SQL + index 建議 + 可逆 migration + 意圖說明」，不是一段可執行的神奇 query。
N+1 用 JOIN 或「先限訂單再 JOIN」一次取回；order_items(order_id) 與 orders(customer_id, created_at DESC) 是本日的預設 index 方向。
Placeholder 用 $1；字面 ID 視為生成失敗。
CREATE INDEX CONCURRENTLY 與交易互斥；down 必須能 DROP INDEX。
永遠先人審再執行；生成步驟綁 read-only 角色，與 DDL 套用分離。

明日預告 (Next)

明日進入 單元測試自動化 (Unit Testing Workflow)：利用 Codex 自動覆蓋 Edge Cases，把本日的參數化 SQL 與失敗契約變成可重複跑的測試。