實務經驗顯示,劣質 SQL 多源自三大根因:**索引設計不當、聯結條件不足、WHERE 子句無法優化**。透過系統性分析與改寫,往往能帶來數十倍效能提升。本文結合實測數據(測試環境:Sybase 11.0.3,對 SQL Server 同樣適用),深度剖析三大優化方向。
場景:表 record 62 萬列,欄位 date(大量重複)、place、amount。
| 索引策略 | 查詢 1:日期範圍+金額 | 查詢 2:GROUP BY date | 查詢 3:日期+地點 IN | 分析 |
|---|---|---|---|---|
| date 非叢集 | 25 秒 | 55 秒 | 27 秒 | 範圍掃描退化為表掃描 |
| date 叢集 | 14 秒 | 28 秒 | 14 秒 | 資料依 date 物理排序,範圍查詢只掃描相關頁面 |
| (place, date, amount) 複合 | 26 秒 | 27 秒 | <1 秒 | 前導欄 place 未被查詢 1/2 使用,查詢 3 形成索引覆蓋極快 |
| (date, place, amount) 複合 | <1 秒 | 11 秒 | <1 秒 | 前導欄 date 滿足所有查詢,查詢 1/3 形成索引覆蓋,整體最優 |
場景:card 7,896 列 (card_no 非叢集);account 191,122 列 (account_no 非叢集)。
-- 不充分條件:僅用 card_no 聯結
SELECT SUM(a.amount) FROM account a, card b WHERE a.card_no = b.card_no; -- 20 秒
-- 充分條件:同時用 card_no 與 account_no
SELECT SUM(a.amount) FROM account a, card b
WHERE a.card_no = b.card_no AND a.account_no = b.account_no; -- <1 秒
SET SHOWPLAN_ON 或 SSMS「顯示預估執行計畫」觀察聯結順序、索引使用情況。| ❌ 違反 SARGable | ✅ 改寫為 SARGable | 效能 |
|---|---|---|
SUBSTRING(card_no,1,4)='5378' | card_no LIKE '5378%' | 13 秒 → <1 秒 |
amount/30 < 1000 | amount < 30000 | 11 秒 → <1 秒 |
CONVERT(char(10),date,112)='19991201' | date = '1999/12/01' | 10 秒 → <1 秒 |
鐵律:任何對欄位的函數、運算、隱含轉換,都會導致該欄位索引失效,改將運算移至常數側。
-- 原寫法:23 秒 (62 萬列達 220 秒)
SELECT COUNT(*) FROM stuff WHERE id_no IN ('0','1');
-- 拆解為兩句 UNION ALL:3 秒
SELECT COUNT(*) FROM stuff WHERE id_no = '0'
UNION ALL
SELECT COUNT(*) FROM stuff WHERE id_no = '1';
IN/OR 常被優化器轉為「OR 策略」:建立臨時工作表 → 去重 → 再聚合,完全不用索引。拆解後各句獨立用索引,效能指數級提升。
CREATE PROC count_stuff AS
DECLARE @a INT, @b INT, @c INT, @d CHAR(10);
SELECT @a = COUNT(*) FROM stuff WHERE id_no = '0';
SELECT @b = COUNT(*) FROM stuff WHERE id_no = '1';
SELECT @c = @a + @b;
SELECT @d = CONVERT(CHAR(10), @c);
PRINT @d;
預存程序減少網路往返、利用執行計畫快取、邏輯集中維護。
database.dbo.table 導致 sp_depends 失效。SET SHOWPLAN_ON 分析執行計畫。
3. 避免大交易、慎用 HOLDLOCK、減少重複存取同表、優先用 EXISTS 代替 IN/COUNT(*) 判斷存在性。