在SQL SERVER中導(dǎo)致索引查找變成索引掃描的問題分析
SQL Server 中什么情況會導(dǎo)致其執(zhí)行計劃從索引查找(Index Seek)變成索引掃描(Index Scan)呢? 下面從幾個方面結(jié)合上下文具體場景做了下測試、總結(jié)、歸納。
1:隱式轉(zhuǎn)換會導(dǎo)致執(zhí)行計劃從索引查找(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:使用強制轉(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è)計和代碼規(guī)范(前期)
2:對發(fā)布腳本進行Rreview(中期)
3:通過腳本查詢隱式轉(zhuǎn)換的SQL(后期)
下面是在數(shù)據(jù)庫從執(zhí)行計劃中搜索隱式轉(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í)行計劃從索引查找(Index Seek)變?yōu)樗饕龗呙瑁↖ndex Scan)
SARG(Searchable Arguments)又叫查詢參數(shù), 它的定義:用于限制搜索的一個操作,因為它通常是指一個特定的匹配,一個值的范圍內(nèi)的匹配或者兩個以上條件的AND連接。不滿足SARG形式的語句最典型的情況就是包括非操作符的語句,如:NOT、!=、<>;、!<;、!>;NOT EXISTS、NOT IN、NOT LIKE等,另外還有像在謂詞使用函數(shù)、謂詞進行運算等。
2.1:索引字段使用函數(shù)會導(dǎo)致索引掃描(Index Scan)
SELECT nationalidnumber, loginid FROM humanresources.employee WHERE SUBSTRING(nationalidnumber,1,3) = '112'
2.2索引字段進行運算會導(dǎo)致索引掃描(Index Scan)
對索引字段字段進行運算會導(dǎo)致執(zhí)行計劃從索引查找(Index Seek)變成索引掃描(Index Scan):
SELECT * FROM Person.Person WHERE BusinessEntityID + 10 < 260
一般要盡量避免這種情況出現(xiàn),如果可以的話,盡量對SQL進行邏輯轉(zhuǎn)換(如下所示)。雖然這個例子看起來很簡單,但是在實際中,還是見過許多這樣的案例,就像很多人知道抽煙有害健康,但是就是戒不掉!很多人可能了解這個,但是在實際操作中還是一直會犯這個錯誤。道理就是如此!
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查詢返回數(shù)據(jù)頁(Pages)達到了臨界點(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)于臨界點(Tipping Point),我們下面先不糾結(jié)概念了,先從一個鮮活的例子開始吧:
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
如上所示,當我們查詢OBJECT_ID=1的數(shù)據(jù)時,優(yōu)化器使用索引查找(Index Seek)
上面OBJECT_ID=1的數(shù)據(jù)只有一條,如果OBJECT_ID=1的數(shù)據(jù)達到全表總數(shù)據(jù)量的20%會怎么樣? 我們可以手工更新2001條數(shù)據(jù)。此時SQL的執(zhí)行計劃變成全表掃描(Table Scan)了。
UPDATE TEST SET OBJECT_ID =1 WHERE OBJECT_ID<=2000; UPDATE STATISTICS TEST WITH FULLSCAN; SELECT * FROM TEST WHERE OBJECT_ID= 1
臨界點決定了SQL Server是使用書簽查找還是全表/索引掃描。這也意味著臨界點只與非覆蓋、非聚集索引有關(guā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)計信息缺失或不正確會導(dǎo)致索引掃描(Index Scan)
統(tǒng)計信息缺失或不正確,很容易導(dǎo)致索引查找(Index Seek)變成索引掃描(Index Scan)。 這個倒是很容易理解,但是構(gòu)造這樣的案例比較難,一時沒有想到,在此略過。
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;
下面這個SQL語句得到的結(jié)果是一致的,但是第二個SQL語句由于謂詞不是聯(lián)合索引第一列,導(dǎo)致索引掃描
SELECT * FROM Sales.SalesOrderDetail_Tmp WHERE SalesOrderID=43659 AND SalesOrderDetailID<10
SELECT * FROM Sales.SalesOrderDetail_Tmp WHERE SalesOrderDetailID<10
相關(guān)文章
sp_executesql 使用復(fù)雜的Unicode 表達式錯誤的解決方法
sp_executesql 使用復(fù)雜的Unicode 表達式錯誤的解決方法,需要的朋友可以參考下2012-01-01SQL?server數(shù)據(jù)庫declare和set用法技巧小結(jié)
這篇文章主要給大家介紹了關(guān)于SQL?server數(shù)據(jù)庫declare和set用法技巧的相關(guān)資料,在SQL Server中,DECLARE用于聲明變量和存儲過程中的參數(shù),文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2023-08-08sql?server如何去除數(shù)據(jù)中的一些無用的空格
這篇文章主要介紹了sql?server去除數(shù)據(jù)中的一些無用的空格,本文給大家提到了一些常用的函數(shù),結(jié)合示例代碼給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2023-05-05按日期選擇最晚日期與最早日期對應(yīng)的數(shù)的差值
想得到當天的最早時間與最晚時間的number的差值,需要的朋友可以參考下。2009-11-11在SQL Server中將數(shù)據(jù)導(dǎo)出為XML和Json的方法
這篇文章主要介紹了在SQL Server中將數(shù)據(jù)導(dǎo)出為XML和Json的方法,需要的朋友可以參考下2015-02-02SqlServer 基礎(chǔ)知識 數(shù)據(jù)檢索、查詢排序語句
SqlServer 基礎(chǔ)知識 數(shù)據(jù)檢索、查詢排序語句,需要的朋友可以參考下。2011-10-10使用綠色版SQLServer2008R2出現(xiàn)的問題解析
這篇文章主要介紹了使用綠色版SQLServer2008R2出現(xiàn)的問題,本文給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2023-03-03