在SQL SERVER中導(dǎo)致索引查找變成索引掃描的問題分析

SQL Server 中什么情況會導(dǎo)致其執(zhí)行計(jì)劃從索引查找（Index Seek）變成索引掃描（Index Scan）呢？ 下面從幾個(gè)方面結(jié)合上下文具體場景做了下測試、總結(jié)、歸納。

1：隱式轉(zhuǎn)換會導(dǎo)致執(zhí)行計(jì)劃從索引查找（Index Seek）變?yōu)樗饕龗呙瑁↖ndex Scan）

Implicit Conversion will cause index scan instead of index seek. While implicit conversions occur in SQL Server to allow data evaluations against different data types, they can introduce performance problems for specific data type conversions that result in an index scan occurring during the execution.  Good design practices and code reviews can easily prevent implicit conversion issues from ever occurring in your design or workload. 

如下示例，AdventureWorks2014數(shù)據(jù)庫的HumanResources.Employee表，由于NationalIDNumber字段類型為NVARCHAR，下面SQL發(fā)生了隱式轉(zhuǎn)換，導(dǎo)致其走索引掃描（Index Scan）

SELECT NationalIDNumber, LoginID 
FROM HumanResources.Employee 
WHERE NationalIDNumber = 112457891 

我們可以通過兩種方式避免SQL做隱式轉(zhuǎn)換：

    1：確保比較的兩者具有相同的數(shù)據(jù)類型。

    2：使用強(qiáng)制轉(zhuǎn)換（explicit conversion）方式。

我們通過確保比較的兩者數(shù)據(jù)類型相同后，就可以讓SQL走索引查找（Index Seek），如下所示

SELECT nationalidnumber,
    loginid
FROM  humanresources.employee
WHERE nationalidnumber = N'112457891' 

注意：并不是所有的隱式轉(zhuǎn)換都會導(dǎo)致索引查找（Index Seek）變成索引掃描（Index Scan），Implicit Conversions that cause Index Scans 博客里面介紹了那些數(shù)據(jù)類型之間的隱式轉(zhuǎn)換才會導(dǎo)致索引掃描（Index Scan）。如下圖所示，在此不做過多介紹。

避免隱式轉(zhuǎn)換的一些措施與方法

    1：良好的設(shè)計(jì)和代碼規(guī)范(前期)

    2：對發(fā)布腳本進(jìn)行Rreview（中期）

    3：通過腳本查詢隱式轉(zhuǎn)換的SQL（后期）

下面是在數(shù)據(jù)庫從執(zhí)行計(jì)劃中搜索隱式轉(zhuǎn)換的SQL語句

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @dbname SYSNAME 
SET @dbname = QUOTENAME(DB_NAME());
WITH XMLNAMESPACES 
  (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
SELECT 
  stmt.value('(@StatementText)[1]', 'varchar(max)'), 
  t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)'), 
  t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)'), 
  t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)'), 
  ic.DATA_TYPE AS ConvertFrom, 
  ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength, 
  t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo, 
  t.value('(@Length)[1]', 'int') AS ConvertToLength, 
  query_plan 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt) 
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t) 
JOIN INFORMATION_SCHEMA.COLUMNS AS 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

2：非SARG謂詞會導(dǎo)致執(zhí)行計(jì)劃從索引查找（Index Seek）變?yōu)樗饕龗呙瑁↖ndex Scan）

    SARG（Searchable Arguments）又叫查詢參數(shù)， 它的定義：用于限制搜索的一個(gè)操作，因?yàn)樗ǔＪ侵敢粋€(gè)特定的匹配，一個(gè)值的范圍內(nèi)的匹配或者兩個(gè)以上條件的AND連接。不滿足SARG形式的語句最典型的情況就是包括非操作符的語句，如：NOT、!=、>；、!；、!>；NOT EXISTS、NOT IN、NOT LIKE等，另外還有像在謂詞使用函數(shù)、謂詞進(jìn)行運(yùn)算等。

2.1：索引字段使用函數(shù)會導(dǎo)致索引掃描（Index Scan）

SELECT nationalidnumber,
    loginid
FROM  humanresources.employee
WHERE SUBSTRING(nationalidnumber,1,3) = '112'

2.2索引字段進(jìn)行運(yùn)算會導(dǎo)致索引掃描（Index Scan）

    對索引字段字段進(jìn)行運(yùn)算會導(dǎo)致執(zhí)行計(jì)劃從索引查找（Index Seek）變成索引掃描（Index Scan）：

SELECT * FROM Person.Person WHERE BusinessEntityID + 10  260

一般要盡量避免這種情況出現(xiàn)，如果可以的話，盡量對SQL進(jìn)行邏輯轉(zhuǎn)換（如下所示）。雖然這個(gè)例子看起來很簡單，但是在實(shí)際中，還是見過許多這樣的案例，就像很多人知道抽煙有害健康，但是就是戒不掉！很多人可能了解這個(gè)，但是在實(shí)際操作中還是一直會犯這個(gè)錯(cuò)誤。道理就是如此！

SELECT * FROM Person.Person WHERE BusinessEntityID  250

2.3 LIKE模糊查詢回導(dǎo)致索引掃描（Index Scan）

    Like語句是否屬于SARG取決于所使用的通配符的類型， LIKE 'Condition%' 就屬于SARG、LIKE '%Condition'就屬于非SARG謂詞操作

SELECT * FROM Person.Person WHERE LastName LIKE 'Ma%'

SELECT * FROM Person.Person WHERE LastName LIKE '%Ma%'

3：SQL查詢返回?cái)?shù)據(jù)頁（Pages）達(dá)到了臨界點(diǎn)（Tipping Point）會導(dǎo)致索引掃描（Index Scan）或表掃描（Table Scan）

What is the tipping point?
It's the point where the number of rows returned is "no longer selective enough". SQL Server chooses NOT to use the nonclustered index to look up the corresponding data rows and instead performs a table scan.

    關(guān)于臨界點(diǎn)（Tipping Point），我們下面先不糾結(jié)概念了，先從一個(gè)鮮活的例子開始吧：

SET NOCOUNT ON;
DROP TABLE TEST
CREATE TABLE TEST (OBJECT_ID INT, NAME VARCHAR(8));
CREATE INDEX PK_TEST ON TEST(OBJECT_ID)
DECLARE @Index INT =1;
WHILE @Index = 10000
BEGIN
  INSERT INTO TEST
  SELECT @Index, 'kerry';
  SET @Index = @Index +1;
END
UPDATE STATISTICS TEST WITH FULLSCAN;
SELECT * FROM TEST WHERE OBJECT_ID= 1

如上所示，當(dāng)我們查詢OBJECT_ID=1的數(shù)據(jù)時(shí)，優(yōu)化器使用索引查找（Index Seek）

上面OBJECT_ID=1的數(shù)據(jù)只有一條，如果OBJECT_ID=1的數(shù)據(jù)達(dá)到全表總數(shù)據(jù)量的20%會怎么樣？ 我們可以手工更新2001條數(shù)據(jù)。此時(shí)SQL的執(zhí)行計(jì)劃變成全表掃描（Table Scan）了。

UPDATE TEST SET OBJECT_ID =1 WHERE OBJECT_ID=2000;
UPDATE STATISTICS TEST WITH FULLSCAN;
SELECT * FROM TEST WHERE OBJECT_ID= 1

臨界點(diǎn)決定了SQL Server是使用書簽查找還是全表/索引掃描。這也意味著臨界點(diǎn)只與非覆蓋、非聚集索引有關(guān)（重點(diǎn)）。

Why is the tipping point interesting?
It shows that narrow (non-covering) nonclustered indexes have fewer uses than often expected (just because a query has a column in the WHERE clause doesn't mean that SQL Server's going to use that index)
It happens at a point that's typically MUCH earlier than expected… and, in fact, sometimes this is a VERY bad thing!
Only nonclustered indexes that do not cover a query have a tipping point. Covering indexes don't have this same issue (which further proves why they're so important for performance tuning)
You might find larger tables/queries performing table scans when in fact, it might be better to use a nonclustered index. How do you know, how do you test, how do you hint and/or force… and, is that a good thing?

4：統(tǒng)計(jì)信息缺失或不正確會導(dǎo)致索引掃描（Index Scan）

     統(tǒng)計(jì)信息缺失或不正確，很容易導(dǎo)致索引查找（Index Seek）變成索引掃描（Index Scan）。 這個(gè)倒是很容易理解，但是構(gòu)造這樣的案例比較難，一時(shí)沒有想到，在此略過。

5：謂詞不是聯(lián)合索引的第一列會導(dǎo)致索引掃描（Index Scan）

SELECT * INTO Sales.SalesOrderDetail_Tmp FROM Sales.SalesOrderDetail;
CREATE INDEX PK_SalesOrderDetail_Tmp ON Sales.SalesOrderDetail_Tmp(SalesOrderID, SalesOrderDetailID);
UPDATE STATISTICS  Sales.SalesOrderDetail_Tmp WITH FULLSCAN;

下面這個(gè)SQL語句得到的結(jié)果是一致的，但是第二個(gè)SQL語句由于謂詞不是聯(lián)合索引第一列，導(dǎo)致索引掃描

SELECT * FROM Sales.SalesOrderDetail_Tmp
WHERE SalesOrderID=43659 AND SalesOrderDetailID10

SELECT * FROM Sales.SalesOrderDetail_Tmp WHERE SalesOrderDetailID10