萬盛學電腦網

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

SQL Server 表存取路徑深度解析:Table Scan、Index Scan、Index Seek 差異與實測

三大存取方式定義

方式定義比喻典型觸發條件
Table Scan從頭到尾逐行掃描堆表或叢集索引葉層翻閱整本書無索引、WHERE 條件無 SARGable、統計資訊過期導致誤判
Index Scan從頭到尾掃描整個非叢集索引只看書的索引頁查詢欄位被非叢集索引覆蓋、或範圍極大(> 30% 資料)、ORDER BY 符合索引順序
Index Seek利用 B+ 樹結構直接定位到符合條件的索引鍵範圍查索引直接翻到頁碼WHERE 條件符合索引前導欄、高選擇度、SARGable 謂詞

關鍵觀念:叢集索引掃描 = 表掃描

叢集索引葉層即資料頁,Clustered Index Scan 本質等同 Table Scan,差別在於邏輯順序遵循索引鍵。

實驗環境與資料準備

USE AdventureWorks2008R2;
GO
-- 建立測試表
SELECT * INTO dbo.SalesOrderHeader_test FROM Sales.SalesOrderHeader;
SELECT * INTO dbo.SalesOrderDetail_test FROM Sales.SalesOrderDetail;
-- 叢集索引
CREATE CLUSTERED INDEX PK_SalesOrderHeader_test ON dbo.SalesOrderHeader_test(SalesOrderID);
-- 非叢集索引
CREATE INDEX IX_SalesOrderDetail_test_SalesOrderID ON dbo.SalesOrderDetail_test(SalesOrderID);
-- 製造資料傾斜:9 筆訂單各 12 萬明細,佔 90% 資料量
DECLARE @i INT = 1;
WHILE @i <= 9 BEGIN
    INSERT INTO dbo.SalesOrderHeader_test (...) SELECT ... WHERE SalesOrderID = 75123;
    INSERT INTO dbo.SalesOrderDetail_test SELECT 75123+@i, ... FROM Sales.SalesOrderDetail;
    SET @i += 1;
END

實測案例

Case 1: Table Scan(無索引欄位)

SELECT SalesOrderDetailID, UnitPrice 
FROM dbo.SalesOrderDetail_test 
WHERE UnitPrice > 200;

計畫:Table Scan(堆表)。無 UnitPrice 索引,必須全掃描。

Table Scan 計畫

Case 2: Index Seek(高選擇度範圍)

SELECT SalesOrderID 
FROM dbo.SalesOrderDetail_test 
WHERE SalesOrderID < 43664;

計畫:Index Seek(非叢集索引)。範圍小,精準定位。

Index Seek

Case 3: Index Scan(全索引掃描)

SELECT SalesOrderID FROM dbo.SalesOrderDetail_test;

計畫:Index Scan。需讀取所有索引頁。

Index Scan

Case 4: 大範圍仍走 Seek(統計資訊準確)

DBCC FREEPROCCACHE; DBCC DROPCLEANBUFFERS;
SELECT SalesOrderID FROM dbo.SalesOrderDetail_test WHERE SalesOrderID < 80000;

計畫:Index Seek。即使返回 80% 資料,最佳化工具根據統計資訊判斷 Seek + Key Lookup 仍比 Scan 便宜。

最佳化工具決策邏輯

  1. 是否有可用索引覆蓋 WHERE/JOIN/ORDER BY 列?
  2. 估計返回行數佔表總行數比例(選擇度)。
  3. 比較 Index Seek + Key Lookup 成本 vs Index/Table Scan 成本。
  4. 考慮平行度、記憶體授予、I/O 成本模型。

DBA 可執行動作

  • 更新統計資訊:UPDATE STATISTICS 或 sp_updatestats,確保基數估計準確。
  • 建立覆蓋索引:INCLUDE 非鍵欄位,避免 Key Lookup。
  • 重寫非 SARGable 條件:WHERE YEAR(DateCol)=2024 → WHERE DateCol >= '20240101' AND DateCol < '20250101'。
  • 強制索引 (HINT):僅作臨時測試,長期依賴最佳化工具。
copyright © 萬盛學電腦網 all rights reserved