首页 / MSSQL / SQL SERVER中什么情况会导致索引查找变成索引扫描

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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了SQL SERVER中什么情况会导致索引查找变成索引扫描，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7306字，纯文字阅读大概需要11分钟。

内容图文

NationalIDNumber, LoginID

FROM HumanResources.Employee

WHERE NationalIDNumber = 112457891

SQL SERVER中什么情况会导致索引查找变成索引扫描 - 文章图片

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

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

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

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

SELECT nationalidnumber,

       loginid

FROM   humanresources.employee

WHERE  nationalidnumber = N‘112457891‘

SQL SERVER中什么情况会导致索引查找变成索引扫描 - 文章图片

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

SQL SERVER中什么情况会导致索引查找变成索引扫描 - 文章图片

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

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

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

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/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谓词会导致执行计划从索引查找（Index Seek）变为索引扫描（Index Scan）

SARG（Searchable Arguments）又叫查询参数，它的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值的范围内的匹配或者两个以上条件的AND连接。不满足SARG形式的语句最典型的情况就是包括非操作符的语句，如：NOT、!=、<>；、!<；、!>；NOT EXISTS、NOT IN、NOT LIKE等，另外还有像在谓词使用函数、谓词进行运算等。

2.1：索引字段使用函数会导致索引扫描（Index Scan）

SELECT nationalidnumber,

       loginid

FROM   humanresources.employee

WHERE  SUBSTRING(nationalidnumber,1,3) = ‘112‘

SQL SERVER中什么情况会导致索引查找变成索引扫描 - 文章图片

2.2索引字段进行运算会导致索引扫描（Index Scan）

对索引字段字段进行运算会导致执行计划从索引查找（Index Seek）变成索引扫描（Index Scan）：

SELECT * FROM Person.Person WHERE BusinessEntityID + 10 < 260

SQL SERVER中什么情况会导致索引查找变成索引扫描 - 文章图片

一般要尽量避免这种情况出现，如果可以的话，尽量对SQL进行逻辑转换（如下所示）。虽然这个例子看起来很简单，但是在实际中，还是见过许多这样的案例，就像很多人知道抽烟有害健康，但是就是戒不掉！很多人可能了解这个，但是在实际操作中还是一直会犯这个错误。道理就是如此！

SELECT * FROM Person.Person WHERE BusinessEntityID < 250

SQL SERVER中什么情况会导致索引查找变成索引扫描 - 文章图片

2.3 LIKE模糊查询回导致索引扫描（Index Scan）

Like语句是否属于SARG取决于所使用的通配符的类型， LIKE ‘Condition%‘ 就属于SARG、LIKE ’%Condition‘就属于非SARG谓词操作

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

SQL SERVER中什么情况会导致索引查找变成索引扫描 - 文章图片

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

SQL SERVER中什么情况会导致索引查找变成索引扫描 - 文章图片

3：SQL查询返回数据页（Pages）达到了临界点（Tipping Point）会导致索引扫描（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.

关于临界点（Tipping Point），我们下面先不纠结概念了，先从一个鲜活的例子开始吧：

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的数据时，优化器使用索引查找（Index Seek）

SQL SERVER中什么情况会导致索引查找变成索引扫描 - 文章图片

上面OBJECT_ID=1的数据只有一条，如果OBJECT_ID=1的数据达到全表总数据量的20%会怎么样？我们可以手工更新2001条数据。此时SQL的执行计划变成全表扫描（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中什么情况会导致索引查找变成索引扫描 - 文章图片

临界点决定了SQL Server是使用书签查找还是全表/索引扫描。这也意味着临界点只与非覆盖、非聚集索引有关（重点）。

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：统计信息缺失或不正确会导致索引扫描（Index Scan）

统计信息缺失或不正确，很容易导致索引查找（Index Seek）变成索引扫描（Index Scan）。这个倒是很容易理解，但是构造这样的案例比较难，一时没有想到，在此略过。

5：谓词不是联合索引的第一列会导致索引扫描（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语句得到的结果是一致的，但是第二个SQL语句由于谓词不是联合索引第一列，导致索引扫描

SELECT * FROM Sales.SalesOrderDetail_Tmp

WHERE SalesOrderID=43659 AND SalesOrderDetailID<10

SQL SERVER中什么情况会导致索引查找变成索引扫描 - 文章图片

SELECT * FROM Sales.SalesOrderDetail_Tmp WHERE SalesOrderDetailID<10

SQL SERVER中什么情况会导致索引查找变成索引扫描 - 文章图片

参考资料：

https://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/

http://stackoverflow.com/questions/6528906/why-is-this-an-index-scan-and-not-a-index-seek

http://pramodsingla.com/2011/05/16/cause-of-index-scan/

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/82f49db8-0c77-4bce-b26c-1ad0a4af693b/index-scan-on-a-table-join-why-not-index-seek?forum=sqldatabaseengine

http://stackoverflow.com/questions/6528906/why-is-this-an-index-scan-and-not-a-index-seek

https://www.sqlpassion.at/archive/2013/06/12/sql-server-tipping-games-why-non-clustered-indexes-are-just-ignored/

http://www.sqlskills.com/blogs/kimberly/the-tipping-point-query-answers/

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

标签：

本文系统来源：http://www.cnblogs.com/kerrycode/p/4806236.html

内容总结

以上是互联网集市为您收集整理的SQL SERVER中什么情况会导致索引查找变成索引扫描全部内容，希望文章能够帮你解决SQL SERVER中什么情况会导致索引查找变成索引扫描所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/457223.html

来源：【匿名】

【上一篇】SQL server从入门精通----表的复制与多表连接查询【下一篇】一个odbc连mssql分页的类

更多 ►

【SQL SERVER中什么情况会导致索引查找变成索引扫描】教程文章相关的互联网学习教程文章

MSSQL查询所有数据库表，指定数据库的字段、索引

--查询所有数据库USE mastergoselect [name] from [sysdatabases] order by [name]--查询其中一个数据库test，就可以得到这个数据库中的所有的表名了，语句如下：USE testgoselect [id], [name] from [sysobjects] where [type] = ‘u‘ order by [name]--查出v_lyb这个表中所有的字段select [name] from [syscolumns] where [id] = 21575115 order by [colid]--得到该表中的记录条数前提这个表中要建立索引select [o].[id], [o].[...

MSSQL2008 全文索引的创建【代码】【图】

从MSSQL2008开始，全文索引推荐的创建方式已经与2005不同了。对于字符类型的数据库，可以直接创建。 CREATE UNIQUE INDEX hr_job_idx ON hr_job_database(id);CREATE FULLTEXT CATALOG ft AS DEFAULT;CREATE FULLTEXT INDEX ON hr_job_database(contents ) KEY INDEX hr_job_idx WITH STOPLIST = SYSTEM; 但是对于二进制创建的，必须指定扩展名列。例如docs列以imgage或者binary二进制存放word，excel，pdf等类型那么可以按照如...

聚集索引: 三级阶梯SQL Server索引

原文链接：http://www.sqlservercentral.com/articles/Stairway+Series/72351/ 聚集索引:三级阶梯SQL Server索引通过大卫·杜兰特,2013/01/25(第一次出版:2011/06/22)该系列本文是楼梯系列的一部分:SQL Server的阶梯索引索引数据库设计的基础,告诉开发人员使用数据库设计者的意图。不幸的是索引时往往是后加上的性能问题出现。终于在这里是一个简单的系列文章,应该让任何数据库专业迅速“加速”前面的水平在这个楼梯概述了非聚集索...

SQL Server 索引中包含查询字段（INCLUDE索引）

这种索引无法通过数据的设计视图进行设计（也可能是没找到方法！） [sql] view plain copy CREATE INDEX IX_ContractDetail_Quantity ON ContractDetail (CostID, SubStatus, Note ) INCLUDE (Quantity); 和普通的索引唯一的区别就是后面加个了 Include标示表示索引中包含的字段，单不会再上面建立查询索引。也就是说可以直接通过 CostId,SubStatus,Note三个字段查找Quantity的值，而不用去查询ContactDetail表效...

SQLServer覆盖索引【代码】【图】

为了更好地理解覆盖索引，在正式介绍覆盖索引之前，首先稍微来谈一谈有关索引的一些基础知识。数据页和索引页　　在SQLServer中，数据存储的基本单位是页，一页的大小为8KB，分别由页首，数据行和行偏移量组成，如下图结构：　　页首固定占用96个字节，用来存储相关的页面系统信息，例如所属的数据库表对象Id等。数据行是真实数据的存储区域，每一行的大小是不固定的。行偏移量是一个数组，数组的每个位置占2个字节，用来存储数据行...

SQL Server 聚集索引和非聚集索引的区别分析

聚集索引:物理存储按照索引排序非聚集索引:物理存储不按照索引排序优势与缺点聚集索引：插入数据时速度要慢（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入）查询数据比非聚集数据的速度快汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的...

MSSql Server 索引'uq_f_userName' 依赖于列'f_userName'。由于一个或多个对象访问此列，ALTER TABLE ALTER COLUMN f_userName 失败

--需求有变，需要往t_login表的f_userName字段添加外国人名，之前设置的varchar(10)不够，商议决定改成varchar(30)，执行的时候，提示消息索引‘uq_f_userName‘ 依赖于列‘f_userName‘。由于一个或多个对象访问此列，ALTER TABLE ALTER COLUMN f_userName 失败。--原来，之前为了防止f_userName重复，添加了唯一索引uq_f_userName。--进行如下操作后，问题妥妥解决--表名：t_login（登录表）--字段：f_userName（用户名）--执行...

SQL Server 索引维护（1）——系统常见的索引问题【图】

前言：在很多系统中，比如本人目前管理的数据库，索引经常被滥用，甚至使用DTA（数据库引擎优化顾问）来成批创建索引（DTA目前个人认为它的真正用处应该是在发现缺失的统计信息，在以前的项目中，用过一次DTA，里面提示了很多列缺少统计信息，后来在不改动其他操作的前提下，把这些统计信息手动建上去，性能提升非常明显。关于统计信息将另开文章介绍）。一个表甚至有20多个索引（索引的数量并没有标准，但是要尽量合理，每个索引都...

SQL Server中如何识别、查找未使用的索引(unused indexes）【代码】

在SQL Server中，索引是优化SQL性能的一大法宝。但是由于各种原因，索引会被当做“银弹”滥用，一方面有些开发人员（甚至是部分数据库管理员）有一些陋习，不管三七二十一，总是根据所谓的"感觉"或“经验”先增加一些索引，而不管这些索引是否未被使用或是否合理。另外一方面在数据库的生命周期中，需求总是在变化，业务也在变化，有些当初创建的有效索引可能已经变成了unused index了。变成了数据库性能的累赘；另外，部分数据库...

提升SQL Server速度整理索引碎片

凭经验，这是索引碎片问题。检查索引碎片DBCC SHOWCONTIG(表)，得到如下结果： DBCC SHOWCONTIG 正在扫描 ‘A‘ 表... 表: ‘A‘（884198200）；索引 ID: 1，数据库 ID: 13 已执行 TABLE 级别的扫描。 - 扫描页数.....................................: 3127 - 扫描扩展盘区数...............................: 403 - 扩展盘区开关数...............................: 1615 - 每个扩展盘区上的平均页数......................

MSSQL - 最热教程

sqlServer 查询特定的表（字段或者文字...sqlserver建表及注释 sql server 2008出现远程过程调用失败 Window Server2019安装SqlServer2012管...SQLserver使用truncate语句报错：找不...sql server导出到excel错误：未在本地计...SQL Server 2008 下载及安装教程 SQL SERVER将多行数据合并成一行（转）SQL SERVER 中设置字段默认值为当前时间 php Sql Server连接失败问题及解决办法

首页 / MSSQL / SQL SERVER中什么情况会导致索引查找变成索引扫描

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

内容导读

内容图文

内容总结

内容备注

内容手机端

【SQL SERVER中什么情况会导致索引查找变成索引扫描】教程文章相关的互联网学习教程文章

MSSQL查询所有数据库表，指定数据库的字段、索引

MSSQL2008 全文索引的创建【代码】【图】

聚集索引: 三级阶梯SQL Server索引

SQL Server 索引中包含查询字段（INCLUDE索引）

SQLServer覆盖索引【代码】【图】

SQL Server 聚集索引和非聚集索引的区别分析

MSSql Server 索引'uq_f_userName' 依赖于列'f_userName'。由于一个或多个对象访问此列，ALTER TABLE ALTER COLUMN f_userName 失败

SQL Server 索引维护（1）——系统常见的索引问题【图】

SQL Server中如何识别、查找未使用的索引(unused indexes）【代码】

提升SQL Server速度整理索引碎片

SQL Server 索引和视图【代码】

SQL Server 2016新特性：列存储索引新特性

Sql Server 索引之唯一索引和筛选索引

sql server中使用组合索引需要注意的地方

修正sqlserver索引错误

SERVER - 相关标签

SQL - 相关标签

MSSQL - 最新教程

MSSQL - 最热教程