萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

深入理解 SQL Server 表掃描:從資料頁到執行計畫的完整解析

「表掃描」是執行計畫中最常見也最令人頭痛的運算子之一。本文從儲存引擎底層的資料頁結構出發,配合 DBCC IND、DBCC PAGE 實戰演示,帶您看透表掃描的真實運作機制,並掌握避免全表掃描的核心策略。

一、 什麼是表掃描?

當查詢無可用索引、或優化器判定掃描全表成本低於索引查找時,SQL Server 會對 Heap(堆積表) 或 Clustered Index(叢集索引)的葉層 進行逐頁、逐列掃描。執行計畫會顯示 Table Scan(堆積表)或 Clustered Index Scan(叢集索引)。

實驗環境建立

CREATE TABLE dbo.Person (
    ID INT NOT NULL,
    Name VARCHAR(20) NOT NULL
); -- 故意不建立任何索引,形成 Heap

INSERT INTO dbo.Person VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');

執行 SELECT * FROM dbo.Person WHERE Name = 'Bob';,執行計畫顯示 Table Scan。

二、 深入資料頁:表掃描的物理基礎

1. 關鍵概念:頁、IAM、槽位陣列

  • 資料頁 (Data Page):SQL Server 儲存資料的基本單位,**固定 8 KB (8192 bytes)**,包含 96 bytes 頁首、資料列、自由空間、**槽位陣列 (Slot Array)**。
  • IAM 頁 (Index Allocation Map):追蹤配置給某物件的所有區間與頁,是掃描的「導航圖」。
  • 槽位陣列:位於頁尾,每個槽位 2 bytes,儲存資料列在頁內的位移,掃描本質上就是遍歷槽位陣列。

2. 使用 DBCC IND 找出物件所屬頁面

-- 開啟追蹤旗標 3604 將輸出導向用戶端
DBCC TRACEON(3604);
GO
-- 查詢 Person 表的所有頁面
DBCC IND('TestDB', 'Person', -1);
GO

輸出關鍵欄位:

  • PageFID、PagePID:檔案號與頁號(如 1:78)。
  • IAMFID、IAMPID:對應的 IAM 頁;若為 NULL 則該列本身是 IAM 頁。
  • PageType:1 = 資料頁,10 = IAM 頁。

3. 使用 DBCC PAGE 解析資料頁內容

-- 輸出選項 3:頁首 + 每列詳細欄位值
DBCC PAGE('TestDB', 1, 78, 3);
GO

輸出三大區塊:

  1. PAGE HEADER:頁類型、物件 ID、索引 ID、自由空間位移、槽位計數等。
  2. DATA AREA:實際資料列內容(含欄位值、NULL 位圖、變長欄位位移陣列)。
  3. SLOT ARRAY:每列的位移,掃描時依序讀取。

三、 表掃描的執行流程

  1. 優化器決定掃描 → 取得 IAM 頁鏈結。
  2. 依 IAM 記錄的區間順序,逐區間、逐頁讀取資料頁至緩衝集區。
  3. 對每個資料頁:讀取頁首 → 遍歷槽位陣列 → 定位資料列 → 套用 WHERE 條件過濾 → 輸出符合列。
  4. 若為叢集索引掃描,則沿著葉層雙向鏈表順序讀取(邏輯順序 = 索引鍵順序)。

四、 如何避免/優化表掃描?

策略適用場景注意事項
建立非叢集索引覆蓋查詢欄位高選擇性 WHERE、JOIN、ORDER BY 欄位避免過度索引導致寫入放大;使用 INCLUDE 覆蓋非鍵欄位。
將 Heap 改為叢集索引大多數資料表應有叢集索引(窄、唯一、遞增)叢集索引鍵變更會導致非叢集索引維護開銷。
分區表超大資料表(億級以上),按範圍刪除/存取需配合分區函數/配置,查詢需包含分區欄位才能分區消除。
資料行存儲索引資料倉庫/OLAP 大量掃描聚合SQL Server 2012+,壓縮率高、批次模式執行,但不適合高頻點查/更新。
統計資料更新 / 重新編譯統計過期導致誤判掃描成本UPDATE STATISTICS、OPTION (RECOMPILE)、啟用自動更新統計。
查詢改寫SARGable 條件、避免函數包裹欄位WHERE YEAR(CreateDate)=2023 → WHERE CreateDate >= '2023-01-01' AND CreateDate < '2024-01-01'。

五、 進階觀察:sys.dm_db_index_physical_stats 檢測碎片與頁密度

SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count,
    ips.record_count,
    ips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.index_id > 0 -- 排除 Heap(index_id=0)
ORDER BY ips.avg_fragmentation_in_percent DESC;
  • avg_fragmentation_in_percent > 30% → ALTER INDEX ... REBUILD。
  • 5% < fragmentation < 30% → ALTER INDEX ... REORGANIZE。
  • avg_page_space_used_in_percent 過低 → 考慮調整 FILLFACTOR。

六、 小結

表掃描並非絕對之惡——小表、全表聚合、資料行存儲場景下它是最優選擇。關鍵在於識別不必要的大表掃描,透過索引設計、統計維護、查詢改寫將其轉為 Index Seek 或限制掃描範圍。掌握 DBCC IND/PAGE 與 DMV 觀察儲存層實況,是從「會寫 SQL」進階到「懂引擎」的關鍵一步。

copyright © 萬盛學電腦網 all rights reserved