資料庫查詢優化技術：從理論到實戰的系統性指南

前言

查詢操作佔數據庫操作最大比重，SELECT 是成本最高的 SQL 語句。百萬級表全掃描耗時數十分鐘，優化後可降至分鐘級。本文結合 Informix/Oracle/SQL Server 實戰，系統闡述查詢優化核心技術。

誤區澄清

「查詢優化是 DBMS 的事，與我寫的 SQL 無關」——**錯誤**。優化器基於用戶 SQL 生成計劃，糟糕的 SQL 經優化器仍難變高效。用戶 SQL 質量是優化基石。

DBMS 處理查詢流程

1. 詞法/語法檢查
2. 提交查詢優化器
3. 代數優化 + 存取路徑優化
4. 預編譯模塊生成查詢計劃
5. 執行並返回結果

主流產品（Oracle、Sybase、SQL Server、Informix）均採用**基於代價的優化 (CBO)**，根據系統字典統計信息估算代價選擇最優計劃。

改善用戶查詢計劃的五大實戰方案

1. 合理使用索引

2. 避免或簡化排序

優化器避免排序的條件：

• 索引包含所有排序列
• GROUP BY/ORDER BY 列順序與索引順序一致
• 排序列來自同一表

不可避免時簡化： 縮小排序列範圍、合併表（犧牲正規化換效率）。

3. 消除大表順序存取

嵌套查詢中順序存取災難性：3 層嵌套 × 1000 行 = 10 億行掃描。

核心對策： **連接列必須建索引**。

並集優化： 某 WHERE 條件強制順序掃描時，改用 UNION ALL 拆分：

-- 強制順序掃描的原語句
SELECT * FROM orders 
WHERE (customer_num = 104 AND order_num > 1001) OR order_num = 1008

-- 優化：拆分為兩個索引可用的查詢再合併
SELECT * FROM orders WHERE customer_num = 104 AND order_num > 1001
UNION ALL
SELECT * FROM orders WHERE order_num = 1008

4. 優化子查詢與關聯子查詢

• IN / EXISTS 選擇：外表大、內表小 → EXISTS；外表小、內表大且有索引 → IN
• 避免關聯子查詢逐行執行，改用 JOIN 或 APPLY (SQL Server)
• NOT IN 含 NULL 陷阱 → 改用 NOT EXISTS

5. 統計信息維護

• UPDATE STATISTICS / sp_updatestats 定期執行
• 大量數據變更後必須更新，否則優化器誤判代價選錯計劃
• 啟用 AUTO_UPDATE_STATISTICS（默認開啟），但大表需手動配合全掃描更新

工具與驗證

• 執行計劃： SET SHOWPLAN_ALL ON / SSMS 圖形化計劃
• STATISTICS IO / STATISTICS TIME 量化 I/O 與 CPU
• DBCC SHOWCONTIG / sys.dm_db_index_physical_stats 檢查碎片

總結

查詢優化無銀彈，核心是：**寫出友好於優化器的 SQL（索引友好、避免排序、消除順序掃描）+ 維護準確統計信息**。以執行計劃為鏡，以業務負載為標，持續迭代。