大表關聯查詢效能優化：表變量與索引視圖實戰策略

背景：百萬級訂單與會員表關聯的效能瓶頸

在 B/S 架構專案中，高併發與海量資料是常態。某專案中單表記錄達 9 位數（訂單表 8 位數），查詢需求為「依代理商查詢某期間所有會員卡產生的訂單」。兩表結構如下：

• Order 表：含 cardNo（會員卡號）、orderTime（訂單時間）等欄位
• Member 表：含 cardNo、proxyID（代理商編號）等欄位

原始 SQL 採用 INNER JOIN 並以 IN 子句篩選代理商：

SELECT 欄位 FROM [Order]
INNER JOIN [Member] ON [Order].cardNo = [Member].cardNo
WHERE [Member].proxyID IN ('A-01', 'A-02')
  AND [Order].orderTime BETWEEN '20080101' AND '20080131';

當兩表皆超過百萬筆且欄位眾多時，即便有索引支援，查詢仍可能陷入長時間等待。

方案一：表變量替換大表關聯，縮小掃描範圍

核心思路：輸出欄位多來自 Order 表，Member 表僅提供「代理商→會員卡號」的映射關係。先將符合條件的 cardNo 取出存入表變量，再與 Order 表關聯。

DECLARE @t TABLE (cardNo INT);
INSERT @t
SELECT cardNo FROM [Member] WHERE proxyID IN ('A-01', 'A-02');

SELECT 欄位 FROM [Order]
INNER JOIN @t ON [Order].cardNo = @t.cardNo
WHERE [Order].orderTime BETWEEN '20080101' AND '20080131';

優勢：

• 表變量生命週期隨批次結束自動釋放，無需手動 DROP，減輕 tempdb 壓力。
• 將大表關聯轉為「小結果集關聯大表」，優化器更易選擇 Nested Loops Join。
• 適用於「查詢條件來自大表、輸出欄位來自另一大表」的典型場景。

注意：表變量無統計資訊，若卡號筆數極大（超過萬筆），優化器可能誤判行數，建議改用暫存表 #t 並加上 CREATE INDEX。

方案二：索引視圖預先物化關聯結果

核心思路：Member 表僅需 cardNo、proxyID 兩欄位參與查詢。建立僅含這兩欄的視圖並加上唯一叢集索引，使其物化儲存，查詢時直接掃描視圖而非基底表。

CREATE VIEW dbo.MemberView
WITH SCHEMABINDING
AS
SELECT cardNo, proxyID FROM dbo.[Member];
GO

CREATE UNIQUE CLUSTERED INDEX IX_MemberView_cardNo
ON dbo.MemberView (cardNo);
GO

查詢改為：

SELECT 欄位 FROM [Order]
INNER JOIN dbo.MemberView ON [Order].cardNo = MemberView.cardNo
WHERE MemberView.proxyID IN ('A-01', 'A-02')
  AND [Order].orderTime BETWEEN '20080101' AND '20080131';

索引視圖關鍵限制與維護成本

1. 必須加 WITH SCHEMABINDING，且引用物件需雙部分命名（schema.table）。
2. 第一個索引必須是 UNIQUE CLUSTERED，才能保證行唯一性並物化儲存。
3. 基底表 INSERT/UPDATE/DELETE 會同步維護視圖索引，寫入效能會下降，需評估讀寫比例。
4. 不支援 OUTER JOIN、UNION、子查詢等複雜邏輯。

兩方案效能比較與選型建議

建議：優先評估索引視圖，若寫入頻繁或查詢條件多變，改用表變量或暫存表。實際專案中可結合 SET STATISTICS IO/TIME ON 實測比對。