[食譜好菜] 在 SQL Server 中利用 Indexed View（索引檢視表）來加快複雜查詢的速度

2022-03-25

3039
0
SQL Server

我們或多或少應該都有遇過一種情況，就是某一些資料表 JOIN 起來的查詢較為緩慢，面對這類執行時間較長的查詢，我們第一步是先進行資料表本身的效能調校，調校之後如果查詢還是需要一點時間，又剛好我們的資料庫系統是使用 SQL Server 的話，那我們可以考慮為查詢建立 Indexed View 試試看。

一般 View 儲存的是一段查詢語句，需要查詢結果就去執行這段查詢語句，而 Indexed View 則是會將查詢結果儲存起來，減少查詢的 I/O 及運算時間，加快回傳查詢結果。

這是一種以空間換取時間的做法，可想而知它有一些不適合的情境：

頻繁 INSERT、UPDATE、DELETE 的資料表
未包含 Aggregation 或 JOIN 的查詢語句
低度重複性的 GROUP BY 索引鍵
查詢結果比基底資料表的資料還大

建立 Indexed View

如果我們經過評估之後，Indexed View 適合我們的使用情境，那麼我們就可以開始著手來建立了，建立 Indexed View 的限制很多，其中下面幾項是比較重要的：

以兩段式名稱來指定參考的資料庫物件（例如：schema.tablename）
建立檢查表要加上 WITH SCHEMABINDING 提示
需要建立唯一的叢集索引
如果有 GROUP BY，查詢語句必須包含 COUNT_BIG(*)，而且不能包含 HAVING。

如果想要知道更詳細的限制條件，可以參考官方文件 - 建立索引檢視表。

我的實驗環境是這樣的，有兩個資料表：Member、MemberAddress，要撈出會員資料並且包含地址資訊，就得把這兩個資料表 JOIN 起來查詢，為了加快查詢速度，所以我們為這個 JOIN 的查詢語句建立 Indexed View，語法如下：

CREATE VIEW dbo.vw_Member WITH SCHEMABINDING
AS
SELECT
    m.Id
   ,m.[Name]
   ,m.Birthday
   ,ma.[Address]
FROM dbo.Member m
INNER JOIN dbo.MemberAddress ma
    ON m.Id = ma.Id
GO

CREATE UNIQUE CLUSTERED INDEX CIX_vw_Member ON dbo.vw_Member (Id)
GO

我們比較一下建立 Indexed View 前後的執行計劃、I/O 及執行時間，可以看到整體的效能提昇不少。

建立 Indexed View 的非叢集索引

既然 Indexed View 都能建叢集索引了，非叢集索引當然也可以了，沒有問題。

不同 SQL Server 版本的差異

如果我們到網路上去搜尋 SQL Server Indexed View 的相關資訊，可能會搜到一則訊息是說 Indexed View 要評估版、開發版、企業版才能使用，這則訊息有對有錯，其實 Indexed View 每個 SQL Server 都是可以建立的，差別只在於前面提到的那幾個版本，QO（Query Optimizer）在分析的時候預設會把 Indexed View 考慮進來。

如果有把 Indexed View 考慮進來，則執行計劃就可能可以用到 Indexed View 的索引。

如果沒有把 Indexed View 考慮進來，即使直接查詢 Indexed View，執行計劃還是會被展開。

那除了評估版、開發版、企業版之外，其他版本能不能使用 Indexed View 的索引？　答案是可以的，加上 WITH (NOEXPAND) 就可以了。

SQL Server 的 Indexed View 我認為還是有一些可以進步的空間，由於只要基底資料表的資料有增刪改，就會馬上同步到 Indexed View，所以增刪改的效能就會被降低，而且這個特性沒辦法調整，不像 Oracle 的 Materialized View 它有比較多的刷新選項可以設定，因此 Indexed View 使用起來限制就比較多，這個只能期待微軟哪一天去想到了，以上 SQL Server Indexed View 大家參考看看。

軟體主廚的程式料理廚房

軟體跟料理一樣，都變成了每人每天的生活必需品。