萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

資料庫效能調優三大死穴:索引設計、聯結條件、WHERE 子句優化實戰

實務經驗顯示,劣質 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 形成索引覆蓋,整體最優

索引設計黃金法則

  1. 大量重複值 + 範圍查詢/ORDER BY/GROUP BY → 考慮叢集索引。
  2. 2. 常同時存取多欄且各有重複值 → 考慮複合索引。 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 秒

原理

  • 優化器依聯結條件列舉可能的「外層表/內層表」組合,估算 I/O 成本 = 外層頁數 + 外層列數 × 內層每次查找頁數。
  • 第一句:外層 account (22,541 頁) × 內層 card 索引查找 (3 頁) ≈ 59 萬 I/O。
  • 第二句:外層 card (1,944 頁) × 內層 account 索引查找 (4 頁) ≈ 3 萬 I/O,降低 95%。

實務建議

  • 聯結條件務必包含「有索引的欄位」與「大表的鍵值」。
  • 用 SET SHOWPLAN_ON 或 SSMS「顯示預估執行計畫」觀察聯結順序、索引使用情況。

三、WHERE 子句:避免「不可優化」的寫法

1. 欄位運算導致索引失效(掃表)

❌ 違反 SARGable✅ 改寫為 SARGable效能
SUBSTRING(card_no,1,4)='5378'card_no LIKE '5378%'13 秒 → <1 秒
amount/30 < 1000amount < 3000011 秒 → <1 秒
CONVERT(char(10),date,112)='19991201'date = '1999/12/01'10 秒 → <1 秒

鐵律:任何對欄位的函數、運算、隱含轉換,都會導致該欄位索引失效,改將運算移至常數側。

2. IN / OR 觸發工作表策略

-- 原寫法: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 策略」:建立臨時工作表 → 去重 → 再聚合,完全不用索引。拆解後各句獨立用索引,效能指數級提升。

3. 善用預存程序封裝邏輯

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;

預存程序減少網路往返、利用執行計畫快取、邏輯集中維護。

綜合優化檢查清單(開發人員自查)

  1. 跨庫存取務必在當前庫建立 View,避免 database.dbo.table 導致 sp_depends 失效。
  2. 2. 提交 SP 前必須跑 SET SHOWPLAN_ON 分析執行計畫。 3. 避免大交易、慎用 HOLDLOCK、減少重複存取同表、優先用 EXISTS 代替 IN/COUNT(*) 判斷存在性。
copyright © 萬盛學電腦網 all rights reserved