首页 ›存档› 技术 › 查看内容

SQLSERVER中什么情况会导致索引查找变成索引扫描

2018-3-30 13:00 |来自: 互联网 361 0

摘要: （点击上方蓝字，快速关注我们) 作者：潇湘隐者网址：http://www.cnblogs.com/kerrycode/p/4806236.html SQL Server 中什么情况会导致其执行计划从索引查找（Index Seek）变成索引扫描（Index Scan）呢？下面 ...

（点击上方蓝字，快速关注我们)

作者：潇湘隐者

网址：http://www.cnblogs.com/kerrycode/p/4806236.html

SQL Server 中什么情况会导致其执行计划从索引查找（Index Seek）变成索引扫描（Index Scan）呢？下面从几个方面结合上下文具体场景做了下测试、总结、归纳。

1：隐式转换会导致执行计划从索引查找（Index Seek）变为索引扫描（Index 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数据库的HumanResources.Employee表，由于NationalIDNumber字段类型为NVARCHAR，下面SQL发生了隐式转换，导致其走索引扫描（Index Scan）

SELECT NationalIDNumber, LoginID

FROM HumanResources.Employee

WHERE NationalIDNumber = 112457891

我们可以通过两种方式避免SQL做隐式转换：

1：确保比较的两者具有相同的数据类型。

2：使用强制转换（explicit conversion）方式。

我们通过确保比较的两者数据类型相同后，就可以让SQL走索引查找（Index Seek），如下所示

SELECT nationalidnumber,

       loginid

FROM   humanresources.employee

WHERE  nationalidnumber = N'112457891'

注意：并不是所有的隐式转换都会导致索引查找（Index Seek）变成索引扫描（Index Scan），Implicit Conversions that cause Index Scans 博客里面介绍了那些数据类型之间的隐式转换才会导致索引扫描（Index Scan）。如下图所示，在此不做过多介绍。

避免隐式转换的一些措施与方法

1：良好的设计和代码规范(前期)

2：对发布脚本进行Review（中期）

3：通过脚本查询隐式转换的SQL（后期）

下面是在数据库从执行计划中搜索隐式转换的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/Stmt**') 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谓词会导致执行计划从索引查找（Index Seek）变为索引扫描（Index Scan）

SARG（Searchable Arguments）又叫查询参数，它的定义：用于**搜索的一个操作，因为它通常是指一个特定的匹配，一个值的范围内的匹配或者两个以上条件的AND连接。不满足SARG形式的语句最典型的情况就是包括非操作符的语句，如：NOT、!=、

声明：文章版权归原作者所有部分文章转自互联网如有侵权请联系 [邮箱地址] 删除

路过

雷人

握手

鲜花

鸡蛋

收藏分享邀请

上一篇：关系型数据库表结构的两个设计技巧物理主键作为关联的外键下一篇：SQLServer中WITH(NOLOCK)浅析

SQLSERVER中什么情况会导致索引查找变成索引扫描

相关分类