您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 索引設計核心原則與效能優化實戰解析

編輯：關於SqlServer

前言

SQL 效能優化的核心在於「正確結果前提下，減少 I/O、利用索引、避免掃描」。不良 SQL 多源於：不當索引設計、不充分連接條件、不可優化 WHERE 子句。本文透過實測案例深度解析三大優化面向。

測試環境基準

主機：HP LH II / 330MHz / 128MB RAM
OS：OpenServer 5.0.4
DB：Sybase 11.0.3 (原理通用於 SQL Server)
測試表 record：620,000 列

一、索引設計：從非叢集到組合索引的演進

案例查詢

Q1: SELECT COUNT(*) FROM record WHERE date > '19991201' AND date < '19991214' AND amount > 2000
Q2: SELECT date, SUM(amount) FROM record GROUP BY date
Q3: SELECT COUNT(*) FROM record WHERE date > '19990901' AND place IN ('BJ','SH')

方案對比實測

索引策略	Q1	Q2	Q3	分析
date 非叢集索引	25秒	55秒	27秒	大量重複值，範圍查詢需掃描全表
date 叢集索引	14秒	28秒	14秒	資料物理有序，範圍查詢只掃描相關頁面
(place, date, amount) 組合索引	26秒	27秒	<1秒	前導欄 place 未被 Q1/Q2 使用，索引失效；Q3 形成索引覆蓋
(date, place, amount) 組合索引	<1秒	11秒	<1秒	前導欄 date 被所有查詢使用，Q1/Q3 形成索引覆蓋，最優

索引設計黃金法則

叢集索引選擇：大量重複值 + 範圍查詢 (BETWEEN, >, <, >=, <=) + ORDER BY/GROUP BY → 考慮叢集索引
組合索引前導欄：最常用的查詢欄位放最前，確保核心查詢能用上索引
索引覆蓋：查詢欄位全包含在索引中 → 避免回表查詢，極大提升效能
預設非叢集不一定最好：依查詢模式選擇叢集/非叢集/組合

二、連接條件不足導致錯誤執行計畫

案例

card 表 7,896 列 (card_no 非叢集索引)，account 表 191,122 列 (account_no 非叢集索引)

SQL 對比


SELECT SUM(a.amount) FROM account a, card b WHERE a.card_no = b.card_no  -- 20秒


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 (191k列)	card (索引查找 3頁)	22541 + 191122×3 = 595,907	慢
條件充分	card (7,896列)	account (索引查找 4頁)	1944 + 7896×4 = 33,528	快 18倍

核心原則

優化器依連接條件列出候選方案，選擇系統開銷最小者
充分連接條件 = 讓優化器看到真實的最佳路徑
外層表匹配列數 × 內層表每次查找成本 = 總成本，乘積最小者勝出
使用 SET SHOWPLAN ON 或 DBCC TRACEON(3604, 310, 302) 檢視計畫

三、不可優化的 WHERE 子句（SARGable 問題）

欄位運算導致索引失效

不可優化寫法	耗時	優化寫法	耗時	原理
WHERE SUBSTRING(card_no,1,4)='5378'	13秒	WHERE card_no LIKE '5378%'	<1秒	函數運算導致逐列計算，無法用索引搜尋
WHERE amount/30 < 1000	11秒	WHERE amount < 1000*30	<1秒	運算移至常數側，保持欄位「乾淨」
WHERE CONVERT(char(10),date,112)='19991201'	10秒	WHERE date = '1999/12/01'	<1秒	避免隱含轉換與函數包裹

IN / OR 導致工作表與索引失效

-- 原始：23秒 (62萬列達 220秒！)
SELECT COUNT(*) FROM stuff WHERE id_no IN ('0','1');

-- 拆分後：3秒
SELECT COUNT(*) FROM stuff WHERE id_no = '0';
SELECT COUNT(*) FROM stuff WHERE id_no = '1';
-- 應用層加總

原因：IN 邏輯等價 OR，優化器採用「OR 策略」：建立工作表 → 去重 → 計算，未使用索引。

解決方案

拆分子句：不產生大量重複值時，拆為多條 SQL 或 UNION ALL
存儲程序封裝：在程序內分別查詢、加總、回傳，單次往返
改寫為 EXISTS：WHERE EXISTS (SELECT 1 FROM ... WHERE id_no = stuff.id_no AND id_no IN ('0','1')) (視版本優化器支援度)

總結：SQL 優化三大支柱

面向	核心原則	關鍵動作
索引設計	依查詢模式設計，前導欄正確，追求覆蓋	分析查詢負載 → 設計叢集/組合索引 → 驗證覆蓋
連接條件	充分條件讓優化器找到真實最佳路徑	補全 PK/FK 關聯欄位、避免單一連接欄位
WHERE 子句	保持欄位「可搜尋」(SARGable)	運算移至常數側、拆分 IN/OR、善用存儲程序