SKILL.md
唯讀
名稱
sql-pro
描述
最佳化 SQL 查詢、設計資料庫結構、並排除效能問題。當使用者詢問查詢為何緩慢、需要協助撰寫複雜的 JOIN 或彙總、提及資料庫效能問題、或想要設計或遷移結構時使用。適用於複雜查詢、視窗函數、CTE、索引策略、查詢計畫分析、涵蓋索引建立、遞迴查詢、EXPLAIN/ANALYZE 解讀、查詢前後效能基準測試、或在不同資料庫方言（PostgreSQL、MySQL、SQL Server、Oracle）之間遷移查詢。
SQL Pro
核心工作流程
- 結構分析 - 檢視資料庫結構、索引、查詢模式、效能瓶頸
- 設計 - 使用 CTE、視窗函數、適當的 JOIN 建立基於集合的操作
- 最佳化 - 分析執行計畫、實作涵蓋索引、消除資料表掃描
- 驗證 - 執行
EXPLAIN ANALYZE並確認大型資料表上沒有循序掃描；若查詢未達到低於 100 毫秒的目標，則在繼續前反覆調整索引選擇或重寫查詢 - 文件化 - 提供查詢說明、索引理由、效能指標
參考指南
根據上下文載入詳細指引：
| 主題 | 參考文件 | 載入時機 |
|---|---|---|
| 查詢模式 | references/query-patterns.md |
JOIN、CTE、子查詢、遞迴查詢 |
| 視窗函數 | references/window-functions.md |
ROW_NUMBER、RANK、LAG/LEAD、分析函數 |
| 最佳化 | references/optimization.md |
EXPLAIN 計畫、索引、統計資料、調校 |
| 資料庫設計 | references/database-design.md |
正規化、鍵值、約束條件、結構 |
| 方言差異 | references/dialect-differences.md |
PostgreSQL vs MySQL vs SQL Server 特定差異 |
快速參考範例
CTE 模式
-- 隔離昂貴的子查詢邏輯以利重複使用和可讀性
WITH ranked_orders AS (
SELECT
customer_id,
order_id,
total_amount,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
WHERE status = 'completed' -- 在 JOIN 之前提早過濾
)
SELECT customer_id, order_id, total_amount
FROM ranked_orders
WHERE rn = 1; -- 每位客戶最新的已完成訂單
視窗函數模式
-- 分割區內的累計總和與排名 — 無需自我 JOIN
SELECT
department_id,
employee_id,
salary,
SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS running_payroll,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;
EXPLAIN ANALYZE 解讀
-- PostgreSQL：務必使用 ANALYZE 查看實際資料列數與估計值的差異
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > NOW() - INTERVAL '30 days';
輸出中需檢查的重點：
- 大型資料表的 Seq Scan → 新增或修正索引
- actual rows ≫ estimated rows → 執行
ANALYZE <table>以更新統計資料 - Buffers: shared hit vs read →
read計數高表示缺少快取/索引
最佳化前後範例
-- 最佳化前：關聯子查詢，每列執行一次（慢）
SELECT order_id,
(SELECT SUM(quantity) FROM order_items oi WHERE oi.order_id = o.id) AS item_count
FROM orders o;
-- 最佳化後：單一彙總 JOIN（快）
SELECT o.order_id, COALESCE(agg.item_count, 0) AS item_count
FROM orders o
LEFT JOIN (
SELECT order_id, SUM(quantity) AS item_count
FROM order_items
GROUP BY order_id
) agg ON agg.order_id = o.id;
-- 支援的涵蓋索引（包含查詢觸及的所有欄位）
CREATE INDEX idx_order_items_order_qty
ON order_items (order_id)
INCLUDE (quantity);
限制
必須執行
- 在建議最佳化前分析執行計畫
- 使用基於集合的操作而非逐列處理
- 在查詢執行中儘早套用過濾（在可能的情況下於 JOIN 之前）
- 使用 EXISTS 而非 COUNT 進行存在性檢查
- 在比較和彙總中明確處理 NULL
- 為頻繁查詢建立涵蓋索引
- 使用正式環境規模的資料量進行測試
禁止執行
- 在正式環境查詢中使用 SELECT *
- 在基於集合的操作可行時使用游標
- 在針對特定方言時忽略平台特定的最佳化
- 在未考慮資料量和基數的情況下實作解決方案
輸出範本
實作 SQL 解決方案時，提供：
- 含行內註解的最佳化查詢
- 必要的索引及其理由
- 執行計畫分析
- 效能指標（最佳化前/後）
- 平台特定注意事項（若適用）
