sql-code-review

SQL 程式碼審查

對 ${selection}（若未選取則為整個專案）進行深入的 SQL 程式碼審查，重點關注安全性、效能、可維護性與資料庫最佳實務。

🔒 安全性分析

SQL 注入防護

-- ❌ 嚴重：SQL 注入漏洞
query = "SELECT * FROM users WHERE id = " + userInput;
query = f"DELETE FROM orders WHERE user_id = {user_id}";

-- ✅ 安全：參數化查詢
-- PostgreSQL/MySQL
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
EXECUTE stmt USING @user_id;

-- SQL Server
EXEC sp_executesql N'SELECT * FROM users WHERE id = @id', N'@id INT', @id = @user_id;

存取控制與權限

• 最小權限原則：僅授予所需的最低權限
• 角色型存取：使用資料庫角色而非直接使用者權限
• 綱要安全性：適當的綱要擁有權與存取控制
• 函式/程序安全性：檢視 DEFINER 與 INVOKER 權限

資料保護

• 敏感資料暴露：避免對包含敏感欄位的資料表使用 SELECT *
• 稽核日誌：確保敏感操作被記錄
• 資料遮罩：使用檢視或函式遮罩敏感資料
• 加密：確認敏感資料已加密儲存

⚡ 效能最佳化

查詢結構分析

-- ❌ 不良：低效的查詢模式
SELECT DISTINCT u.* 
FROM users u, orders o, products p
WHERE u.id = o.user_id 
AND o.product_id = p.id
AND YEAR(o.order_date) = 2024;

-- ✅ 良好：最佳化結構
SELECT u.id, u.name, u.email
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.order_date >= '2024-01-01' 
AND o.order_date < '2025-01-01';

索引策略檢視

• 缺少索引：識別需要建立索引的欄位
• 過度索引：找出未使用或冗餘的索引
• 複合索引：為複雜查詢建立多欄位索引
• 索引維護：檢查索引是否破碎或過時

聯結最佳化

• 聯結類型：確認使用適當的聯結類型（INNER vs LEFT vs EXISTS）
• 聯結順序：優先處理較小的結果集
• 笛卡兒乘積：識別並修正缺少的聯結條件
• 子查詢 vs 聯結：選擇最有效的方式

彙總與視窗函式

-- ❌ 不良：低效的彙總
SELECT user_id, 
       (SELECT COUNT(*) FROM orders o2 WHERE o2.user_id = o1.user_id) as order_count
FROM orders o1
GROUP BY user_id;

-- ✅ 良好：高效的彙總
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id;

🛠️ 程式碼品質與可維護性

SQL 風格與格式化

-- ❌ 不良：格式與風格不佳
select u.id,u.name,o.total from users u left join orders o on u.id=o.user_id where u.status='active' and o.order_date>='2024-01-01';

-- ✅ 良好：清晰可讀的格式
SELECT u.id,
       u.name,
       o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
  AND o.order_date >= '2024-01-01';

命名慣例

• 一致命名：資料表、欄位、條件約束遵循一致模式
• 描述性名稱：資料庫物件使用清晰有意義的名稱
• 保留字：避免使用資料庫保留字作為識別碼
• 大小寫敏感性：綱要中大小寫使用一致

綱要設計檢視

• 正規化：適當的正規化程度（避免過度或不足正規化）
• 資料型別：選擇最佳資料型別以兼顧儲存與效能
• 條件約束：正確使用 PRIMARY KEY、FOREIGN KEY、CHECK、NOT NULL
• 預設值：為欄位設定適當的預設值

🗄️ 資料庫特定最佳實務

PostgreSQL

-- 使用 JSONB 處理 JSON 資料
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 為 JSONB 查詢建立 GIN 索引
CREATE INDEX idx_events_data ON events USING gin(data);

-- 使用陣列型別處理多值欄位
CREATE TABLE tags (
    post_id INT,
    tag_names TEXT[]
);

MySQL

-- 使用適當的儲存引擎
CREATE TABLE sessions (
    id VARCHAR(128) PRIMARY KEY,
    data TEXT,
    expires TIMESTAMP
) ENGINE=InnoDB;

-- 針對 InnoDB 最佳化
ALTER TABLE large_table 
ADD INDEX idx_covering (status, created_at, id);

SQL Server

-- 使用適當的資料型別
CREATE TABLE products (
    id BIGINT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    created_at DATETIME2 DEFAULT GETUTCDATE()
);

-- 為分析用途建立 Columnstore 索引
CREATE COLUMNSTORE INDEX idx_sales_cs ON sales;

Oracle

-- 使用序列實現自動遞增
CREATE SEQUENCE user_id_seq START WITH 1 INCREMENT BY 1;

CREATE TABLE users (
    id NUMBER DEFAULT user_id_seq.NEXTVAL PRIMARY KEY,
    name VARCHAR2(255) NOT NULL
);

🧪 測試與驗證

資料完整性檢查

-- 驗證參考完整性
SELECT o.user_id 
FROM orders o 
LEFT JOIN users u ON o.user_id = u.id 
WHERE u.id IS NULL;

-- 檢查資料一致性
SELECT COUNT(*) as inconsistent_records
FROM products 
WHERE price < 0 OR stock_quantity < 0;

效能測試

• 執行計畫：檢視查詢執行計畫
• 負載測試：使用真實資料量測試查詢
• 壓力測試：驗證並發負載下的效能
• 迴歸測試：確保最佳化不破壞功能

📊 常見反模式

N+1 查詢問題

-- ❌ 不良：應用程式碼中的 N+1 查詢
for user in users:
    orders = query("SELECT * FROM orders WHERE user_id = ?", user.id)

-- ✅ 良好：單一最佳化查詢
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

過度使用 DISTINCT

-- ❌ 不良：DISTINCT 掩蓋聯結問題
SELECT DISTINCT u.name 
FROM users u, orders o 
WHERE u.id = o.user_id;

-- ✅ 良好：不使用 DISTINCT 的正確聯結
SELECT u.name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

WHERE 子句中誤用函式

-- ❌ 不良：函式導致無法使用索引
SELECT * FROM orders 
WHERE YEAR(order_date) = 2024;

-- ✅ 良好：範圍條件可使用索引
SELECT * FROM orders 
WHERE order_date >= '2024-01-01' 
  AND order_date < '2025-01-01';

📋 SQL 審查檢查清單

安全性

• [ ] 所有使用者輸入皆已參數化
• [ ] 無使用字串串接的動態 SQL 建構
• [ ] 適當的存取控制與權限
• [ ] 敏感資料已妥善保護
• [ ] SQL 注入攻擊向量已消除

效能

• [ ] 常用查詢欄位已建立索引
• [ ] 無不必要的 SELECT * 陳述式
• [ ] 聯結已最佳化並使用適當類型
• [ ] WHERE 子句具選擇性並使用索引
• [ ] 子查詢已最佳化或轉換為聯結

程式碼品質

• [ ] 一致的命名慣例
• [ ] 適當的格式化與縮排
• [ ] 複雜邏輯附有意義的註解
• [ ] 使用適當的資料型別
• [ ] 已實作錯誤處理

綱要設計

• [ ] 資料表已適當正規化
• [ ] 條件約束確保資料完整性
• [ ] 索引支援查詢模式
• [ ] 已定義外部鍵關聯
• [ ] 預設值適當

🎯 審查輸出格式

問題範本

## [優先級] [類別]：[簡短描述]

**位置**：[資料表/檢視/程序名稱與行號（如適用）]
**問題**：[問題的詳細說明]
**安全風險**：[如適用 - 注入風險、資料暴露等]
**效能影響**：[查詢成本、執行時間影響]
**建議**：[具體修正方式與程式碼範例]

**修改前**：
```sql
-- 有問題的 SQL

修改後：

-- 改善後的 SQL

預期改善：[效能提升、安全性效益]

#### 總結評估
- **安全性分數**：[1-10] - SQL 注入防護、存取控制
- **效能分數**：[1-10] - 查詢效率、索引使用
- **可維護性分數**：[1-10] - 程式碼品質、文件
- **綱要品質分數**：[1-10] - 設計模式、正規化

#### 前三優先事項
1. **[重大安全性修正]**：處理 SQL 注入漏洞
2. **[效能最佳化]**：新增缺少的索引或最佳化查詢
3. **[程式碼品質]**：改善命名慣例與文件

重點在於提供可操作的、資料庫無關的建議，同時強調特定平台的最佳化與最佳實務。