SQL 效能優化完整指南：查詢調校與索引策略【實戰攻略】

一個執行 10 秒的查詢，經過優化可能只需要 10 毫秒——這就是 SQL 效能優化的威力。本文將系統性介紹效能診斷流程、執行計畫分析、索引策略與查詢優化技巧，幫助您解決資料庫效能瓶頸，打造高效能的資料庫系統。

SQL 效能問題診斷流程

面對效能問題，需要有系統性的診斷方法，而非盲目調整。

效能優化五步驟

1. 識別問題 → 2. 收集資訊 → 3. 分析原因 → 4. 實施優化 → 5. 驗證效果
     ↑                                                           │
     └───────────────────────────────────────────────────────────┘

步驟一：識別問題

首先確認效能問題的範圍：

• 單一查詢慢：特定 SQL 語句效能不佳
• 整體效能下降：資料庫整體回應時間變長
• 尖峰時段問題：特定時間效能惡化
• 漸進式退化：效能隨時間逐漸變差

步驟二：收集效能資訊

-- 查看目前執行中的查詢
SELECT
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.cpu_time,
    r.total_elapsed_time,
    t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50;

-- 查看最耗資源的查詢（依 CPU 時間排序）
SELECT TOP 10
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_cpu_time DESC;

執行計畫分析

執行計畫是效能優化最重要的診斷工具，它顯示 SQL Server 如何執行您的查詢。

如何取得執行計畫

方法一：SSMS 圖形化介面

1. 在查詢編輯器中輸入 SQL
2. 按 Ctrl + M 開啟「Include Actual Execution Plan」
3. 執行查詢（F5）
4. 查看「Execution Plan」分頁

方法二：使用 SET 指令

-- 顯示預估執行計畫（不實際執行）
SET SHOWPLAN_ALL ON;
GO
SELECT * FROM Orders WHERE OrderDate > '2024-01-01';
GO
SET SHOWPLAN_ALL OFF;

-- 顯示實際執行計畫與統計資訊
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT * FROM Orders WHERE OrderDate > '2024-01-01';
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

關鍵指標解讀

執行計畫中需要關注的重要指標：

指標	說明	關注重點
Cost %	各步驟佔總成本百分比	找出最耗資源的步驟
Actual Rows	實際處理的資料列數	與 Estimated 差異大表示統計資料過時
Logical Reads	邏輯讀取次數	數值越大表示 I/O 負擔越重
Operator	執行的操作類型	Table Scan 通常需要優化

常見警示訊號

執行計畫中的警示符號代表潛在問題：

1. Table Scan（資料表掃描）

問題：掃描整個資料表，效能極差
原因：缺少適當的索引
解法：建立符合查詢條件的索引

2. Index Scan vs Index Seek

Index Scan：掃描整個索引（較慢）
Index Seek：直接定位到特定資料（較快）

目標：盡量讓查詢使用 Index Seek

3. Key Lookup（鍵值查閱）

問題：索引不包含所需欄位，需回到資料表取值
解法：建立覆蓋索引（Covering Index）

4. Sort 運算子

問題：資料需要在記憶體中排序
解法：建立已排序的索引

5. 黃色驚嘆號警告

常見警告：
- Missing Index（缺少索引）
- Implicit Conversion（隱式轉換）
- Cardinality Estimate（基數估計錯誤）

索引策略

索引是提升查詢效能最有效的方法，但錯誤的索引策略反而會拖慢系統。

Clustered Index（叢集索引）

叢集索引決定資料的實體儲存順序，每個資料表只能有一個。

-- 建立叢集索引（通常在主鍵上）
CREATE CLUSTERED INDEX IX_Orders_OrderId
ON Orders (OrderId);

-- 或在建立資料表時指定
CREATE TABLE Orders (
    OrderId INT PRIMARY KEY CLUSTERED,
    OrderDate DATE,
    CustomerId INT
);

叢集索引選擇原則：

• ✅ 唯一且遞增的欄位（如 IDENTITY）
• ✅ 經常用於範圍查詢的欄位
• ❌ 避免頻繁更新的欄位
• ❌ 避免寬度過大的欄位

Non-Clustered Index（非叢集索引）

非叢集索引建立獨立的索引結構，指向資料列位置。

-- 單欄位索引
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON Orders (CustomerId);

-- 複合索引（多欄位）
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Date
ON Orders (CustomerId, OrderDate DESC);

-- 包含篩選條件的索引
CREATE NONCLUSTERED INDEX IX_Orders_Active
ON Orders (OrderDate)
WHERE Status = 'Active';

複合索引欄位順序原則：

1. 等於條件（=）的欄位放前面
2. 範圍條件（>、<、BETWEEN）的欄位放後面
3. 考慮查詢頻率與選擇性

Covering Index（覆蓋索引）

覆蓋索引包含查詢所需的所有欄位，避免 Key Lookup。

-- 原始查詢
SELECT OrderId, OrderDate, TotalAmount
FROM Orders
WHERE CustomerId = 1001;

-- 建立覆蓋索引
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Covering
ON Orders (CustomerId)
INCLUDE (OrderDate, TotalAmount);

INCLUDE 欄位說明：

• 不參與索引排序
• 僅存放在索引葉層
• 減少 Key Lookup 開銷

索引維護

索引需要定期維護以保持效能：

-- 查看索引碎片程度
SELECT
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent AS FragmentationPercent,
    ips.page_count AS PageCount
FROM sys.dm_db_index_physical_stats(
    DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id
    AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
ORDER BY FragmentationPercent DESC;

-- 重組索引（碎片 10-30%）
ALTER INDEX IX_Orders_CustomerId ON Orders REORGANIZE;

-- 重建索引（碎片 > 30%）
ALTER INDEX IX_Orders_CustomerId ON Orders REBUILD;

-- 更新統計資料
UPDATE STATISTICS Orders;

---

CloudInsight 中場 CTA

資料庫效能問題讓您頭痛？

CloudInsight 提供專業的資料庫效能調校服務，我們的專家團隊將協助您：

• 全面效能健檢與瓶頸診斷
• 索引策略優化與重構
• 查詢語句調校與重寫
• 效能監控機制建立

預約免費效能診斷諮詢 →

---

查詢優化技巧

除了索引，查詢語句本身的寫法也大幅影響效能。

避免 SELECT *

-- 差：取得所有欄位
SELECT * FROM Orders WHERE CustomerId = 1001;

-- 好：只取需要的欄位
SELECT OrderId, OrderDate, TotalAmount
FROM Orders
WHERE CustomerId = 1001;

效益：減少 I/O、降低網路傳輸量、可能使用覆蓋索引。

避免隱式轉換

-- 差：字串與數字比較，導致隱式轉換
SELECT * FROM Customers WHERE CustomerCode = 12345;
-- CustomerCode 是 VARCHAR，會轉換整個欄位

-- 好：使用相同資料型別
SELECT * FROM Customers WHERE CustomerCode = '12345';

隱式轉換的問題：

• 無法使用索引
• 增加 CPU 負擔
• 可能產生錯誤結果

避免函數包裝欄位

-- 差：對欄位使用函數，無法使用索引
SELECT * FROM Orders
WHERE YEAR(OrderDate) = 2024 AND MONTH(OrderDate) = 6;

-- 好：使用範圍條件
SELECT * FROM Orders
WHERE OrderDate >= '2024-06-01' AND OrderDate < '2024-07-01';

-- 差：字串函數包裝
SELECT * FROM Customers WHERE UPPER(Email) = '[email protected]';

-- 好：確保資料一致性，直接比較
SELECT * FROM Customers WHERE Email = '[email protected]';

使用 EXISTS 取代 IN

處理大型子查詢時，EXISTS 通常效能較佳：

-- IN 子查詢（可能較慢）
SELECT * FROM Customers
WHERE CustomerId IN (
    SELECT CustomerId FROM Orders WHERE OrderDate > '2024-01-01'
);

-- EXISTS（通常較快）
SELECT * FROM Customers c
WHERE EXISTS (
    SELECT 1 FROM Orders o
    WHERE o.CustomerId = c.CustomerId
    AND o.OrderDate > '2024-01-01'
);

原因：EXISTS 在找到第一筆符合資料時即停止，IN 需要處理完整子查詢結果。

其他優化技巧

1. 使用 UNION ALL 取代 UNION（不需去重時）

-- UNION 會排序去重（較慢）
SELECT Name FROM Customers_A
UNION
SELECT Name FROM Customers_B;

-- UNION ALL 直接合併（較快）
SELECT Name FROM Customers_A
UNION ALL
SELECT Name FROM Customers_B;

2. 避免在 WHERE 中使用 OR（考慮改用 UNION）

-- OR 可能導致 Table Scan
SELECT * FROM Orders
WHERE CustomerId = 1001 OR OrderDate = '2024-01-15';

-- 改用 UNION（各自可用索引）
SELECT * FROM Orders WHERE CustomerId = 1001
UNION
SELECT * FROM Orders WHERE OrderDate = '2024-01-15';

3. 適當使用暫存表分段處理

-- 複雜查詢分段處理
SELECT CustomerId, COUNT(*) AS OrderCount
INTO #CustomerOrders
FROM Orders
WHERE OrderDate >= '2024-01-01'
GROUP BY CustomerId;

SELECT c.CustomerName, co.OrderCount
FROM Customers c
JOIN #CustomerOrders co ON c.CustomerId = co.CustomerId
WHERE co.OrderCount > 10;

DROP TABLE #CustomerOrders;

想了解更多預存程序開發技巧？請參考 SQL 預存程序教學。

SQL Server Profiler

SQL Server Profiler 是追蹤資料庫活動的強大工具。

基本使用方式

1. 開啟 SSMS → 工具 → SQL Server Profiler
2. 連線到目標伺服器
3. 選擇追蹤範本（如 TSQL_Duration）
4. 設定篩選條件（避免捕獲過多資料）
5. 開始追蹤

常用追蹤事件

事件	用途
SQL:BatchCompleted	捕獲完成的 SQL 批次
RPC:Completed	捕獲預存程序呼叫
Showplan XML	捕獲執行計畫
Deadlock Graph	捕獲死結資訊

替代方案：Extended Events

Extended Events 是較新且資源消耗較低的追蹤方式：

-- 建立追蹤慢查詢的 Extended Events 工作階段
CREATE EVENT SESSION [SlowQueries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (sqlserver.sql_text, sqlserver.database_name)
    WHERE duration > 5000000  -- 5 秒以上
)
ADD TARGET package0.event_file (
    SET filename = N'C:\Logs\SlowQueries.xel'
)
WITH (MAX_DISPATCH_LATENCY = 30 SECONDS);

-- 啟動工作階段
ALTER EVENT SESSION [SlowQueries] ON SERVER STATE = START;

效能監控工具

SQL Server 內建工具

1. Activity Monitor（活動監視器）

• 即時檢視資料庫活動
• 查看等待中的工作、資源等待類型
• 識別阻塞與鎖定問題

2. Query Store（查詢存放區）

-- 啟用 Query Store
ALTER DATABASE YourDatabase SET QUERY_STORE = ON;

-- 查看最耗資源的查詢
SELECT TOP 10
    q.query_id,
    qt.query_sql_text,
    rs.avg_duration,
    rs.avg_cpu_time,
    rs.count_executions
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

3. Database Engine Tuning Advisor

• 分析工作負載
• 提供索引建議
• 評估索引影響

實戰案例：查詢優化前後對比

案例背景

電商系統的訂單查詢頁面載入需要 8 秒，嚴重影響使用者體驗。

原始查詢

SELECT *
FROM Orders o
JOIN Customers c ON o.CustomerId = c.CustomerId
JOIN OrderItems oi ON o.OrderId = oi.OrderId
JOIN Products p ON oi.ProductId = p.ProductId
WHERE YEAR(o.OrderDate) = 2024
  AND c.Region = 'North'
ORDER BY o.OrderDate DESC;

效能數據：

• 執行時間：8.2 秒
• Logical Reads：125,847
• 執行計畫顯示：Table Scan on Orders

優化步驟

步驟 1：分析執行計畫

• 發現 Orders 資料表進行 Table Scan
• YEAR() 函數導致無法使用索引

步驟 2：建立適當索引

-- 在 Orders 上建立複合索引
CREATE NONCLUSTERED INDEX IX_Orders_Date_Customer
ON Orders (OrderDate DESC, CustomerId)
INCLUDE (OrderId, TotalAmount, Status);

-- 在 Customers 上建立索引
CREATE NONCLUSTERED INDEX IX_Customers_Region
ON Customers (Region)
INCLUDE (CustomerId, CustomerName);

步驟 3：改寫查詢

SELECT
    o.OrderId,
    o.OrderDate,
    o.TotalAmount,
    c.CustomerName,
    p.ProductName,
    oi.Quantity,
    oi.UnitPrice
FROM Orders o
JOIN Customers c ON o.CustomerId = c.CustomerId
JOIN OrderItems oi ON o.OrderId = oi.OrderId
JOIN Products p ON oi.ProductId = p.ProductId
WHERE o.OrderDate >= '2024-01-01' AND o.OrderDate < '2025-01-01'
  AND c.Region = 'North'
ORDER BY o.OrderDate DESC;

優化結果

指標	優化前	優化後	改善幅度
執行時間	8.2 秒	45 毫秒	99.5%
Logical Reads	125,847	1,256	99%
執行計畫	Table Scan	Index Seek	✅

結論

SQL 效能優化是一門需要持續學習與實踐的技術。掌握本文介紹的核心技巧：

1. ✅ 系統化的效能診斷流程
2. ✅ 執行計畫分析與解讀
3. ✅ 正確的索引策略
4. ✅ 查詢語句優化技巧
5. ✅ 效能監控工具使用

記住效能優化的黃金法則：先測量，再優化，後驗證。盲目優化往往事倍功半，透過執行計畫找出真正的瓶頸，才能事半功倍。

想學習更多進階技巧？請參考 SQL Window Functions 教學。

---

CloudInsight 文末 CTA

打造高效能資料庫架構

效能問題往往源自架構設計。CloudInsight 提供專業的資料庫架構諮詢服務：

• 資料庫架構評估與優化建議
• 高可用性與擴展性設計
• 雲端資料庫遷移規劃
• 長期效能監控與維運支援

預約架構設計諮詢 →

---

常見問題 FAQ

索引越多效能越好嗎？

不是。索引是雙面刃：

索引的代價：

• 寫入效能下降：每次 INSERT/UPDATE/DELETE 都需要維護索引
• 儲存空間增加：索引需要額外的磁碟空間
• 維護成本：索引需要定期重組或重建

索引策略建議：

• 針對經常查詢的欄位建立索引
• 避免在頻繁更新的欄位上建立過多索引
• 定期檢視索引使用狀況，移除未使用的索引
• OLTP 系統（交易處理）：索引數量適中
• OLAP 系統（分析報表）：可建立較多索引

-- 查看未使用的索引
SELECT OBJECT_NAME(i.object_id) AS TableName,
       i.name AS IndexName,
       ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
    ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND i.name IS NOT NULL
    AND ius.user_seeks = 0 AND ius.user_scans = 0 AND ius.user_lookups = 0;

什麼是執行計畫的 Table Scan？

Table Scan（資料表掃描）表示 SQL Server 需要讀取整個資料表的每一筆資料來尋找符合條件的結果。

為什麼 Table Scan 效能差：

• 需要讀取所有資料頁
• I/O 開銷巨大
• 隨資料量增加線性惡化

常見原因與解決方案：

原因	解決方案
缺少索引	建立適當的索引
對索引欄位使用函數	改寫查詢避免函數
隱式型別轉換	使用正確的資料型別
統計資料過時	更新統計資料
資料量太小	小資料表 Scan 可能更快（可接受）

如何找出最慢的查詢？

方法一：Query Store（推薦）

-- 啟用 Query Store
ALTER DATABASE YourDB SET QUERY_STORE = ON;

-- 查詢最慢的 SQL
SELECT TOP 10
    qt.query_sql_text,
    rs.avg_duration / 1000000.0 AS avg_duration_seconds,
    rs.count_executions
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

方法二：DMV 查詢

SELECT TOP 10
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2)+1) AS query_text,
    qs.total_elapsed_time / qs.execution_count / 1000 AS avg_elapsed_ms,
    qs.execution_count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_elapsed_ms DESC;

方法三：Extended Events 追蹤 設定追蹤執行時間超過閾值的查詢（如前文所述）。

---

延伸閱讀

• SQL 進階應用實戰技巧
• SQL 預存程序教學
• SQL Window Functions 教學
• SSMS 完整教學
• SQL Server 版本比較

---

參考資料

1. Microsoft SQL Server 效能調校官方文件
2. SQL Server 執行計畫解析指南
3. 索引設計最佳實踐白皮書

---

插圖：SQL 效能優化診斷流程圖

場景描述： 循環流程圖展示「識別問題 → 收集資訊 → 分析原因 → 實施優化 → 驗證效果」五個步驟，每步驟搭配對應圖示，強調持續優化的循環特性

視覺重點：

• 主要內容清晰呈現

必須出現的元素：

• 依據描述中的關鍵元素

需要顯示的中文字： 無

顏色調性： 專業、清晰

避免元素： 抽象圖形、齒輪、發光特效

Slug: sql-performance-tuning-workflow

---