MS SQL 效能調教 - Using the SQL Execution Plan for Query Performance Tuning

MS SQL 效能調教 - Using the SQL Execution Plan for Query Performance Tuning

前言

相信大家都有過這個經驗, 隨著資料庫儲存的資料量越來越大時, 原本查詢的所耗的時間也越來越多, 此時腰做的就是想辦法讓提升查詢效率

Execution Plan

想要調校查詢的效率首先必須要知道每次查詢的bottleneck在哪, 最常見的方式就是去看Execution Plan

假設情境：

這個例子的查詢是從訂單 (Orders) 資料表和客戶 (Customers) 資料表中選擇訂單編號 (OrderID)、訂單日期 (OrderDate) 和客戶名稱 (CustomerName)。該查詢使用了 INNER JOIN 來將這兩個資料表連接起來，並使用 WHERE 條件篩選出訂單日期在 '2023-01-01' 和 '2023-12-31' 之間的記錄。

sql
SELECT o.OrderID, o.OrderDate, c.CustomerName

FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID

WHERE o.OrderDate BETWEEN '2023-01-01' AND '2023-12-31';

對於這個查詢，Execution Plan 中會出現以下操作：

Scan Operators:

對於 Orders 資料表和 Customers 資料表，可能會出現 Scan Operators，這表示 SQL Server 將從這兩個資料表中讀取資料。如果這兩個資料表沒有適當的索引，可能會看到全表掃描 (Table Scan)。

Join Operators:

INNER JOIN 操作將兩個資料表 (Orders 和 Customers) 進行連接。在 Execution Plan 中，會顯示 JOIN 操作的類型 (Nested Loops Join、Merge Join 或 Hash Join)，以及連接的條件 (o.CustomerID = c.CustomerID)。

Filter Operators:

WHERE 條件中的 Filter Operators 會在 Join 之後進行。這個操作會過濾出符合指定訂單日期範圍的記錄。

Filter Operators:

最終的選擇操作將決定查詢結果的輸出，包括選擇需要的欄位 (OrderID、OrderDate 和 CustomerName)。

根據資料量和索引設置，Execution Plan 可能會有所不同。如果 Orders 和 Customers 資料表有適當的索引，你可能會看到索引掃描 (Index Scan) 或索引搜尋 (Index Seek) 的操作，這將有助於提高查詢效能。而如果缺乏適當的索引，可能會出現全表掃描等效能較低的操作。

優化查詢

針對以上的問題，我們可以考慮以下優化方法：

１．創建索引：

確保 Orders 和 Customers 資料表上的 CustomerID 欄位有索引，以加速 JOIN 操作。

２．改變查詢方式：

根據 Execution Plan，可以考慮使用不同的 JOIN 類型或重新撰寫 WHERE 條件，以降低操作成本。如果右側的資料集較小且具有適合的索引，LOOP JOIN 可能成為成本較低的 JOIN 操作。這是因為 LOOP JOIN 的原理是對於左側的每一行，在右側的資料集中進行一次迴圈搜索匹配的行。因此，如果右側的資料集較小且有適當的索引，LOOP JOIN 的效能可能會比較好。相反，如果右側的資料集較大，LOOP JOIN 可能導致效能問題。

sql
-- 創建索引

CREATE INDEX idx_CustomerID ON Orders (CustomerID);

CREATE INDEX idx_CustomerID ON Customers (CustomerID);



-- 改變查詢方式

SELECT o.OrderID, o.OrderDate, c.CustomerName

FROM Orders o

INNER LOOP JOIN Customers c ON o.CustomerID = c.CustomerID -- 使用 LOOP JOIN
WHERE o.OrderDate >= '2023-01-01' AND o.OrderDate < '2024-01-01'; -- 修改 WHERE 條件