OLTP Anti-Patterns

部分內容由 LLM 生成，尚未經過人工驗證。

OLTP（Online Transaction Processing）系統的常見反模式與最佳實踐。

  flowchart LR
    subgraph Bad["反模式"]
        A[逐筆處理]
        B[長交易]
        C[全表掃描]
    end
    subgraph Good["最佳實踐"]
        D[批量處理]
        E[短交易]
        F[索引查詢]
    end
    A -.-> D
    B -.-> E
    C -.-> F

查詢效率

反模式	問題	最佳實踐
DML 嵌套查詢	每次 DML 都執行子查詢，效能極差	使用 JOIN 或先查詢後批次處理
逐筆游標處理	逐行處理大量資料，效率低下	使用批量操作或 set-based 邏輯
過度動態 SQL	難以優化、易受 SQL injection	使用參數化查詢與預編譯語句

DML 嵌套查詢範例

反模式：

UPDATE orders
SET status = 'processed'
WHERE customer_id IN (
    SELECT id FROM customers WHERE region = 'APAC'
);
-- 子查詢每次執行時都重新掃描 customers 表

最佳實踐：

-- 使用 JOIN
UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.status = 'processed'
WHERE c.region = 'APAC';

游標處理 vs 批量操作

反模式（逐筆處理）：

DECLARE cur CURSOR FOR SELECT id FROM orders WHERE status = 'pending';
OPEN cur;
FETCH NEXT FROM cur INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE orders SET status = 'processing' WHERE id = @id;
    FETCH NEXT FROM cur INTO @id;
END;

最佳實踐（批量處理）：

UPDATE orders SET status = 'processing' WHERE status = 'pending';

交易管理

反模式	問題	最佳實踐
大量資料單一交易	Lock 持有時間長、undo log 膨脹	分批提交，控制每批筆數
無效事務邊界	業務邏輯未正確包在交易中	明確定義交易範圍與隔離層級
不使用批次處理	大量 INSERT/UPDATE 逐筆執行	使用 bulk insert、batch update

分批提交範例

反模式：

BEGIN TRANSACTION;
DELETE FROM logs WHERE created_at < '2020-01-01';
-- 可能影響數百萬筆，鎖表時間過長
COMMIT;

最佳實踐：

WHILE EXISTS (SELECT 1 FROM logs WHERE created_at < '2020-01-01')
BEGIN
    BEGIN TRANSACTION;
    DELETE TOP (10000) FROM logs WHERE created_at < '2020-01-01';
    COMMIT;
END;

索引設計

反模式	問題	最佳實踐
缺乏索引	頻繁全表掃描，查詢緩慢	根據查詢模式建立適當索引
過度索引	寫入效能下降、維護成本高	定期審視索引使用率，移除冗餘
過度正規化	頻繁 JOIN 影響效能	適度反正規化或使用物化視圖

索引設計考量

評估索引需求：

-- 檢查慢查詢
SELECT * FROM sys.dm_exec_query_stats
ORDER BY total_elapsed_time DESC;

-- 檢查缺失索引建議（SQL Server）
SELECT * FROM sys.dm_db_missing_index_details;

-- 檢查未使用索引
SELECT * FROM sys.dm_db_index_usage_stats
WHERE user_seeks = 0 AND user_scans = 0;

複合索引順序原則：

選擇性高的欄位在前
WHERE 條件常用欄位優先
考慮覆蓋索引（covering index）

其他問題

反模式	問題	最佳實踐
觸發器濫用	隱藏業務邏輯、除錯困難、效能問題	將邏輯移至應用層或使用明確的 stored procedure

觸發器的適當使用場景

適合使用觸發器：

稽核日誌（audit logging）
自動更新時間戳
強制資料完整性（無法用約束實現時）

應避免使用觸發器：

複雜業務邏輯
跨表連鎖更新
需要交易控制的操作

OLAP Anti-Patterns ORM Anti-Patterns