避開效能暗礁：常見 SQL Anti-Patterns 解析與重構實踐

在資料庫驅動的應用系統中，隨著資料量的指數級增長，SQL 查詢往往會從「系統的核心驅動力」轉變為「整體效能的致命瓶頸」。許多在開發初期運作良好的查詢邏輯，本質上潛藏著未考慮資料庫底層執行機制（如索引遍歷、執行計畫生成、I/O 吞吐與記憶體緩衝區配置）的設計缺陷。

本文聚焦於效能層面，深入剖析 6 種最常見且具破壞力的 SQL Anti-Patterns，拆解其背後導致效能衰退的硬體與架構機制，並提供符合現代資料庫實踐的重構方案。

1. 濫用 SELECT *（過度擷取）

在開發或快速除錯時使用 SELECT * 相當直覺，但在生產環境中，這往往是造成無謂 I/O 與網路延遲的首要元兇。

效能問題機制

• 阻礙覆蓋索引（Covering Index）： 若查詢僅需特定的幾個欄位，而這些欄位皆存在於某個複合索引中，資料庫引擎只需掃描 B-Tree 索引頁（Index-Only Scan），無需回表（Table Lookup / Bookmark Lookup）讀取主資料檔。使用 SELECT * 會強制資料庫執行高成本的回表讀取。
• 增加 I/O 與 Buffer Pool 壓力： 讀取包含巨大文字（TEXT、BLOB）或不必要的欄位會耗盡資料庫的快取記憶體（Buffer Cache），擠出其他高頻查詢所需的熱資料頁。
• 網路頻寬消耗： 傳輸序列化後的額外資料會直接拉長 API 的 TTFB（Time to First Byte）。

重構方式

明確宣告所需欄位，並為高頻讀取設計適當的覆蓋索引：

-- ❌ Anti-Pattern: 全欄位檢索，強制回表
SELECT * 
FROM orders 
WHERE customer_id = 1042 AND status = 'COMPLETED';

--  Best Practice: 僅讀取業務所需欄位，搭配複合索引 (customer_id, status, order_id, total_amount)
SELECT order_id, total_amount, created_at 
FROM orders 
WHERE customer_id = 1042 AND status = 'COMPLETED';

2. 條件欄位運算導致索引失效（Non-Sargable Queries）

SARGable（Search Argument Able）代表查詢條件能夠有效利用索引進行範圍掃描（Index Range Scan）或點查（Index Seek）。對條件欄位進行函數運算或型態轉換是開發中最常見的效能陷阱。

效能問題機制

B-Tree 索引是根據原始欄位值進行排序存儲的。一旦在 WHERE 條件中對索引欄位施加函數運算（如 YEAR(date)）或字串比對（如 SUBSTRING(code, 1, 3)），查詢優化器（Query Optimizer）將無法預先得知函數計算後的排序關係，只能被迫退化為全表掃描（Full Table Scan）或全索引掃描（Index Full Scan），對每一筆記錄逐一計算。

重構方式

將計算邏輯移至常數端，保持欄位本身的純粹性：

-- ❌ Anti-Pattern: 對索引欄位施加函數，導致全表掃描
SELECT id, transaction_amount 
FROM transactions 
WHERE YEAR(created_at) = 2026 AND MONTH(created_at) = 8;

--  Best Practice: 轉換為開閉區間的常數比對，觸發 Index Seek
SELECT id, transaction_amount 
FROM transactions 
WHERE created_at >= '2026-08-01 00:00:00' 
  AND created_at < '2026-09-01 00:00:00';

注意： 隱式型別轉換（Implicit Type Conversion，例如在字串型別的 phone 欄位上使用數字比對 WHERE phone = 0912345678）底層會自動包裹型別轉換函數，同樣會徹底破壞索引。

3. 前置模糊搜尋（Leading Wildcard Like）

在文字搜尋情境中，直接使用萬用字元 % 開頭進行比對是效能崩潰的經典來源。

效能問題機制

標準 B-Tree 索引按照字元的前綴字母順序構建。使用前置萬用字元（如 LIKE '%keyword' 或 LIKE '%keyword%'）會使前綴資訊完全丟失，資料庫無法利用 B-Tree 的二分搜尋特性定位節點，僅能執行高成本的全表循序掃描。

重構方式

• 若業務需求僅為前綴匹配，移除開頭的萬用字元：

-- ❌ Anti-Pattern: 全表掃描
SELECT id, email FROM users WHERE email LIKE '%@example.com';

--  Best Practice (若支援後綴查詢情境): 
-- 1. 改為精準前綴比對
SELECT id, username FROM users WHERE username LIKE 'dev_%';

-- 2. 或建立反轉字串索引（Reverse Index）優化後綴比對
SELECT id, email FROM users WHERE reversed_email LIKE 'moc.elpmaxe@%';

• 若必須支援任意子字串全文搜尋，應引入全文檢索（Full-Text Search, FTS）引擎（如 PostgreSQL GIN 索引、MySQL Full-Text，或外部分散式搜尋引擎 Elasticsearch / OpenSearch），而非依賴關聯式資料庫的 LIKE 運算。

4. 深度分頁的 OFFSET 效能衰退（Deep Pagination）

傳統的分頁方式常採用 LIMIT offset, count 語法，但在巨量資料集下，頁數越深，查詢耗時會呈線性甚至指數級增長。

效能問題機制

當執行 LIMIT 1000000, 20 時，資料庫並非「跳過」前 100 萬筆資料，而是必須先掃描並依序讀取 1,000,020 筆記錄到記憶體中，排序後捨棄前 1,000,000 筆，僅返回最後 20 筆。這產生了極其龐大且完全無效的磁碟與記憶體 I/O。

重構方式：Seek Method / Keysize Pagination

採用游標分頁（Cursor-based Pagination），利用唯一且具遞增性的索引鍵（如 id 或時間戳記）作為錨點：

-- ❌ Anti-Pattern: 深度分頁造成大量無效掃描
SELECT id, title, created_at 
FROM articles 
ORDER BY id DESC 
LIMIT 20 OFFSET 1000000;

--  Best Practice: 利用上一頁最後一筆 ID 進行條件篩選，直接由索引定位
SELECT id, title, created_at 
FROM articles 
WHERE id < 984520  -- 上一頁最後一筆記錄的 ID
ORDER BY id DESC 
LIMIT 20;

5. 迴圈中的 N+1 查詢（N+1 Query Problem）

此問題常伴隨著 ORM（Object-Relational Mapping）的關聯讀取機制發生，例如在遍歷主資料表的每一行時，內部獨立觸發一次次要資料表的查詢。

效能問題機制

即使單次查詢極快（例如 1 毫秒），當主集合有 $N$ 筆記錄時，系統會額外執行 $N$ 次 SQL 呼叫。這會急遽放大網路往返延遲（Network Round-Trip Time, RTT）、連線池排隊時間與資料庫執行緒上下文切換（Context Switching）成本。

重構方式

利用單次關聯查詢（JOIN）或批次聚合（IN (...)）取代多次往返：

-- ❌ Anti-Pattern (偽代碼): 1 次取列表 + N 次取關聯資料
-- Query 1: SELECT id FROM users WHERE role = 'ENGINEER';
-- Loop (N 次): SELECT * FROM user_profiles WHERE user_id = ?;

--  Best Practice 1: 單次 JOIN 聚合
SELECT u.id, u.email, p.bio, p.avatar_url
FROM users u
LEFT JOIN user_profiles p ON u.id = p.user_id
WHERE u.role = 'ENGINEER';

--  Best Practice 2: 批次 IN 檢索（ORM 常用的 Eager Loading 機制）
SELECT * FROM user_profiles WHERE user_id IN (101, 102, 103, ...);

6. 在子查詢中使用低效的 NOT IN

在進行集合排他性過濾時，NOT IN 語法不僅可讀性脆弱，在包含 NULL 值的資料集中更容易產生災難性的效能表現。

效能問題機制

• 三值邏輯（Three-Valued Logic）陷阱： SQL 中的 NULL 代表未知（Unknown）。若 NOT IN (Subquery) 的子查詢結果集中包含任何一個 NULL，整體比對結果將永遠評估為 UNKNOWN/FALSE。
• 無法優化為 Anti-Join： 為了遵循 NULL 的語言規範，查詢優化器往往無法將 NOT IN 轉換為高效的 Hash Anti-Join 或 Merge Anti-Join，而被迫退化為低效的相關子查詢嵌套循環（Correlated Nested Loop），複雜度高達 $O(M \times N)$。

重構方式

改用 NOT EXISTS 或 LEFT JOIN ... WHERE ... IS NULL，讓優化器能夠安全採用 Anti-Join 演算法：

-- ❌ Anti-Pattern: NOT IN 遇到 NULL 會產生語意問題且通常無法優化
SELECT id, name 
FROM customers 
WHERE id NOT IN (SELECT customer_id FROM blacklist);

--  Best Practice: 使用 NOT EXISTS（短路運算，且對 NULL 免疫）
SELECT c.id, c.name 
FROM customers c 
WHERE NOT EXISTS (
    SELECT 1 
    FROM blacklist b 
    WHERE b.customer_id = c.id
);

效能重構對照表

結語

SQL 效能優化的核心原則，始終在於減少無效的資料塊讀取（Logical/Physical Reads）與充分利用索引的排序結構。在撰寫查詢時，多利用資料庫提供的 EXPLAIN ANALYZE 或執行計畫工具，檢視是否有非預期的 Seq Scan、Temporary Files 或過高的成本預估，從架構與語法源頭杜絕 Anti-Patterns，才能確保系統在高併發與海量資料下依舊維持穩定的低延遲回應。