SQL 大量資料查詢優化 30 條鐵律與 LIKE '%...%' 全掃描破解：全文索引、反向索引與應用層拆解

SQL 查詢優化 30 條實戰鐵律 + 模糊查詢終極解法

第一部分：30 條優化鐵律 (節選重點)

1. 避免全表掃描：WHERE/ORDER BY/JOIN 欄位建立索引。
2. 拒絕 IS NULL 判斷：改用預設值 (0/'N') + 索引。
3. 拒絕 != / <>：導致索引失效，改用 IN/NOT IN 或範圍查詢。
4. 拒絕 OR 連接條件：改 UNION ALL 分開走索引再合併。
5. 慎用 IN/NOT IN：連續值用 BETWEEN；子查詢改 EXISTS。
6. LIKE '%abc%' 導致全掃 → 全文索引 / 反向索引 / 應用層拆解。
7. 參數化查詢 可能導致計劃固化不佳 → OPTIMIZE FOR / 動態 SQL / 綁定窺探。
8. 避免欄位上運算/函數：col/2=100 → col=200；SUBSTR(name,1,3)='abc' → name LIKE 'abc%'。
9. 複合索引最左原則：必須包含第一欄位，順序一致。
10. 不要 SELECT *：列出需要欄位，減少 I/O 與網路傳輸。
11. 表變數 vs 暫存表：小量用表變數 (僅 PK 索引)；大量用暫存表可建索引。
12. 避免頻繁建/刪暫存表：增加系統表鎖定。
13. 大量插入暫存表：SELECT INTO 避免大量 Log；小量 CREATE + INSERT。
14. 程序結束顯式清理暫存表：TRUNCATE → DROP 釋放鎖。
15. 游標慎用：>1 萬筆改集合操作；小量可用 FAST_FORWARD。
16. 優先集合思維：SET-based 通常優於 RBAR (Row-By-Agonizing-Row)。
17. SET NOCOUNT ON：減少 DONE_IN_PROC 網路往返。
18. 避免大事務：拆分提交，提升併發。
19. 避免大結果集回傳客戶端：分頁/流式/非同步。
20. 統計資訊及時更新：DBMS_STATS.GATHER_TABLE_STATS。
21. 硬解析 vs 軟解析：使用綁定變數複用計劃。
22. 分區裁剪：分區鍵出現在 WHERE 才生效。
23. 避免隱性轉換：WHERE num_col = '123' → 隱性 TO_NUMBER 導致索引失效。
24. 適度正規化/反正規化：依查詢模式平衡 JOIN 與冗餘。
25. 物化視圖/結果快取：高頻聚合查詢預計算。
26. 索引維護成本：寫入密集表索引不超過 6 個，評估 ROI。
27. 叢集索引更新慎重：修改叢集鍵導致資料物理移動。
28. 數字型優於字串型：比較成本 O(1) vs O(N)。
29. VARCHAR 優於 CHAR：儲存省空間、掃描快。
30. EXISTS 替代 IN：EXISTS (SELECT 1 FROM b WHERE b.id=a.id) 半連接優化。

第二部分：非用 LIKE '%關鍵字%' 不可時的破解方案

效能對比 (百萬筆測試)

• LIKE '%abc%'：~12 秒 (全表掃描)
• Oracle Text：~0.3 秒
• 反向索引：~0.8 秒 (需固定長度)
• 虛擬欄位函數索引：~0.5 秒

關鍵提醒

• 全文索引語法不同於 LIKE，需用 CONTAINS/CATSEARCH。
• 反向索引不支援範圍掃描，僅等值/前綴。
• 任何索引方案都會增加寫入開銷，必須以實際負載測試為準。