萬盛學電腦網

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

SQL Server 索引查尋轉為索引掃描的常見原因與解決方案

現象說明

執行計畫原本使用 Index Seek(索引查尋),卻變成 Index Scan(索引掃描)甚至 Table Scan,導致查詢效能大幅下降。以下整理四大主因與對策。

一、隱含轉換導致索引失效

案例

-- NationalIDNumber 為 NVARCHAR,輸入數值常數導致隱含轉換
SELECT NationalIDNumber, LoginID
FROM HumanResources.Employee
WHERE NationalIDNumber = 112457891;  -- 執行計畫顯示 Index Scan + CONVERT_IMPLICIT

隱含轉換導致 Index Scan

解決

  1. 確保比較雙方資料型別一致:WHERE NationalIDNumber = N'112457891'。
  2. 或使用顯式轉換:WHERE NationalIDNumber = CAST(112457891 AS NVARCHAR(15))。

修正後 Index Seek

並非所有隱含轉換都會導致掃描,詳細型別對照表請參考微軟文件 Implicit Conversions that cause Index Scans。

二、非 SARGable 謂詞

SARG (Search ARGument) 指優化器能利用索引查尋的條件形式。以下寫法會破壞 SARG:

WHERE SUBSTRING(col,1,3) = 'ABC'WHERE col + 10 < 100WHERE col LIKE '%ABC'WHERE col != 'X'
類型錯誤寫法正確寫法
索引欄位套用函數WHERE col LIKE 'ABC%'
索引欄位參與運算WHERE col < 90
前導萬用字元 LIKE改用全文索引或反向索引
否定運算子改寫為 WHERE col < 'X' OR col > 'X'(視資料分布)

實測對比:

-- 掃描
SELECT * FROM Person.Person WHERE BusinessEntityID + 10 < 260;
-- 查尋
SELECT * FROM Person.Person WHERE BusinessEntityID < 250;

運算導致 Scan 修正後 Seek

三、臨界點效應

當優化器預估回傳列數超過臨界點,會放棄「非叢集索引查尋 + Key Lookup」改走叢集索引掃描或表掃描。

  • 臨界點約為 資料表總頁數的 25%~33%(視版本與統計資料而定)。
  • 對策:建立覆蓋索引、篩選更精確、使用強制索引提示(謹慎使用)。

四、統計資料過期或參數嗅探

  • 統計資料過期 → 更新統計資料 UPDATE STATISTICS tbl WITH FULLSCAN。
  • 參數嗅探導致計畫不穩 → 使用 OPTION (RECOMPILE)、OPTIMIZE FOR UNKNOWN 或查詢存儲強制計畫。

自動偵測隱含轉換腳本

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @dbname SYSNAME = QUOTENAME(DB_NAME());
WITH XMLNAMESPACES (DEFAULT 'https://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    stmt.value('(@StatementText)[1]', 'varchar(max)') AS StatementText,
    t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') AS SchemaName,
    t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') AS TableName,
    t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') AS ColumnName,
    ic.DATA_TYPE AS ConvertFrom,
    t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo,
    query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY qp.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') batch(stmt)
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') n(t)
JOIN INFORMATION_SCHEMA.COLUMNS ic
    ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')
   AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)')
   AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)')
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1;

總結檢查清單

  1. 確認 WHERE/JOIN/ON 欄位資料型別一致。
  2. 避免索引欄位上使用函數、運算、前導 % LIKE。
  3. 維護統計資料、監控碎片、適度建立覆蓋索引。
  4. 善用擴充事件追蹤隱含轉換與計畫變更。
copyright © 萬盛學電腦網 all rights reserved