当前位置： 首页 > 编程日记 > 正文

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

编程日记 2024-09-11 00:40:00
原文:SQL SERVER中什么情况会导致索引查找变成索引扫描

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/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'

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

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

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

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

SELECT  * FROM Person.Person WHERE  BusinessEntityID  < 250

2.3 LIKE模糊查询回导致索引扫描（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查询返回数据页（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）

上面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是使用书签查找还是全表/索引扫描。这也意味着临界点只与非覆盖、非聚集索引有关（重点）。

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

SELECT * FROM Sales.SalesOrderDetail_Tmp WHERE SalesOrderDetailID<10

参考资料：

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/


https://www.dkcj.cn/info/18185.html

相关文章：

wait跟sleep的区别

区别 wait sleep 线程 自动唤醒 object的方法只要 线程碰到wait就要等 待不能指定哪个线程等待 Thread的方法 wait用于同步 在任何地方使用 自动释放锁 不会释放锁 转载于:https://www.cnblogs.com/-try/p/3714954.html

编程日记2024/09/11 00:30:00

AMD真龙一族网络社区成立

AMD成立了一个网络社区&#xff0c;叫"真龙一族"&#xff0c;在各大厂商里这的确为一创新之举&#xff0c;看来AMD对自己的产品确实是非常的有信心。有打算买电脑的人可进去看看&#xff0c;参考一下。地址http://www.longamd.cn/default.htm转载于:https://www.cnbl…

编程日记2024/09/11 00:20:00

＜论文阅读＞CascadePSP: Toward Class-Agnostic and Very High-Resolution Segmentation via Global and...

名词解释&#xff1a; 双三次插值&#xff1a;双三次插值是一种更加复杂的插值方式&#xff0c;它能创造出比双线性插值更平滑的图像边缘。 **标题翻译&#xff1a;**通过全局和局部优化进行面向不可知类和超高像素图像的分割 CascadePSP: Toward Class-Agnostic and Very Hig…

编程日记2024/09/11 00:10:00

【转】MYSQL入门学习之十：视图的基本操作

转载地址&#xff1a;http://www.2cto.com/database/201212/176775.html 一、视图的基本介绍 www.2cto.com 视图是虚拟的表。与包含数据的表不一样&#xff0c;视图只包含使用时动态检索数据的查询。使用视图需要MySQL5及以后的版本支持。下面是视图的一些常见应用&#xff1…

编程日记2024/09/11 00:00:00

自动化运维之SaltStack，批量安装httpd实战

SaltStack原理 SaltStack由Master和Minion构成&#xff0c;Master 是服务端&#xff0c; 表示一台服务器&#xff1b;Minion是客户服务端&#xff0c;表示多台服务器。在Master上发送命令给符合条件的Minion,Minin就会执行相应的命令&#xff0c;Master和Minion之间是通过ZeroM…

编程日记2024/09/10 23:50:00

我的面试（四）补充1

突然想起来前天面试时&#xff0c;考官问我out参数和ref参数的区别。我当时答的是out是出的&#xff0c;ref可以来回传参。也就是说out参数是进不来的&#xff0c;而ref是可以进来了。今天写代码试了试。从新整理一下关于out和ref的区别。static void Function1(out int outPar…

编程日记2024/09/10 23:40:00

(C++)输入10个整数，将其中最小的数与第一个数对换，把最大的数与最后一个数对换。要求用3个函数实现，分别为输入10个数、进行处理、输出10个数。要求使用指针的方法进行处理。

#include<cstdio> //输入10个整数&#xff0c;将其中最小的数与第一个数对换&#xff0c;把最大的数与最后一个数对换。要求用3个函数实现&#xff0c;分别为输入10个数、进行处理、输出10个数。要求使用指针的方法进行处理。 //编写函数——输入10个数 //编写函数——进…

编程日记2024/09/10 23:30:00

深入biztalk消息以及消息订阅发布路由机制（二）－消息订阅【转】

一.消息订阅 订阅消息的主体叫订阅服务器&#xff0c;订阅服务器是可以订阅并消费消息的服务&#xff0c;可以作为订阅服务器的服务类型目前有四类&#xff0c;在BizTalkMgmtDb管理数据库中的adm_ServiceClass的Name字段列出了所有可以作为订阅服务器的服务类型&#xff0c;包括…

编程日记2024/09/10 23:20:00

poj1625Censored!(AC自动机+dp）

链接 第一次做这种题目&#xff0c;参考了下题解&#xff0c;相当于把树扯直了做DP&#xff0c;估计这一类题都是这个套路吧。 状态方程dp[i][next] dp[i][next]dp[i][j] ;dp[i][j]表示长度为i的第J个结点的时候满足题意的num&#xff0c;next为当前j点所能走到的下一个合法的…

编程日记2024/09/10 23:10:00

图解5G NR帧结构

子载波间隔 与LTE&#xff08;子载波间隔和符号长度&#xff09;相比&#xff0c; NR支持多种子载波间隔&#xff08;在LTE中&#xff0c;只有15 Khz这种子载波间隔&#xff09;。 在3GPP38.211中&#xff0c;有关于NR子载波间隔类型的总结。 具体的子载波间隔类型如下图所示&a…

编程日记2024/09/10 23:00:00

(C++) A+B 输入输出练习IV 每行的第一个数N，表示本行后面有N个数。 如果N=0时，表示输入结束，且这一行不要计算。

#include<cstdio>/* 4 1 2 3 4 5 1 2 3 4 5 0 */int main(){int n,a;while(scanf("%d",&n),n){int sum 0;for(int i 0;i<n;i){scanf("%d",&a);sum a;}printf("%d\n",sum); }return 0;}

编程日记2024/09/10 22:50:00

jQuery与其它库冲突的解决方法（转）

原文出处&#xff1a;http://www.jb51.net/article/24014.htm 在jQuery库中&#xff0c;几乎所有的插件都被限制在它的命名空间里。全局的对象都很好地存储在jQuery命名空间里&#xff0c;因此当把jQuery和其它javascript类库一起使用时&#xff0c;不会引起冲突. (注意&#x…

编程日记2024/09/10 22:40:00

ASP.NET 下载文件方式

protected void Button1_Click(object sender, EventArgs e){/*微软为Response对象提供了一个新的方法TransmitFile来解决使用Response.BinaryWrite下载超过400mb的文件时导致Aspnet_wp.exe进程回收而无法成功下载的问题。代码如下&#xff1a;*/Response.ContentType "a…

编程日记2024/09/10 22:30:00

ITIL管理思想的执行工具发布

E8.HelpDesk是融入ITIL管理思想&#xff0c;并结合中国企业实施ITIL的实际需求&#xff0c;成功研发ITIL管理思想的执行工具&#xff0c;全面帮助中国企业高效导入ITIL管理体系&#xff0c;提升企业战略执行力。 E8.HelpDesk支持多种服务台管理体系&#xff0c;支持事件管理、问…

编程日记2024/09/10 22:20:00

(C++)A+B 输入输出练习V 输入的第一行是一个正数N，表示后面有N行。每一行的第一个数是M，表示本行后面还有M个数。

#include<cstdio>/* 2 4 1 2 3 4 5 1 2 3 4 5 */int main(){int n,a;scanf("%d",&n);while(n--){int sum 0,m;scanf("%d",&m);for(int i 0;i<m;i){scanf("%d",&a);sum a;}printf("%d\n",sum); }return 0;}

编程日记2024/09/10 22:10:00

Linux上重启服务的正确命令

在开发环境下&#xff0c;我们经常需要部署代码&#xff0c;重启服务&#xff0c;所以会把命令写在脚本中&#xff0c;方便使用。 我们可能这么写 #!/bin/bashps -ef | grep backend-api-1.0 | grep -v "\-\-color" |awk {print $2} |xargs kill -9 sleep 1 nohup ja…

编程日记2024/09/10 22:00:00

Error: Most middleware (like bodyParser) ...

运行NodeJS时出现如下错误&#xff1a; Error: Most middleware (like bodyParser) is no longer bundled with Express and must be installed separately. 意思是 命令行中运行 npm install body-parser 回车&#xff0c;进行安装。 对源代码进行调整&#xff0c;加上 var b…

编程日记2024/09/10 21:50:00

[导入][转]常用CSS缩写语法总结

使用缩写可以帮助减少你CSS文件的大小&#xff0c;更加容易阅读。css缩写的主要规则如下&#xff1a; 颜色 16进制的色彩值&#xff0c;如果每两位的值相同&#xff0c;可以缩写一半&#xff0c;例如&#xff1a; #000000可以缩写为#000;#336699可以缩写为#369; 盒尺寸 通常有下…

编程日记2024/09/10 21:40:00

(C++)A+B 输入输出练习VI 每行的第一个数N，表示本行后面有N个数。

#include<cstdio>/* 4 1 2 3 4 5 1 2 3 4 5 */int main(){int n;while(scanf("%d",&n) ! EOF){int sum 0,a;for(int i 0;i<n;i){scanf("%d",&a);sum a;}printf("%d\n",sum); }return 0;}

编程日记2024/09/10 21:30:00

NDK 提示undefined reference to xxx“的解决办法

在Android.mk文件的 LOCAL_SRC_FILES后面加入包含该类或函数的文件&#xff0c;用\隔开&#xff0c;\后换行继续添加 例如 LOCAL_SRC_FILES : NDKTest.cpp\bncore.c\bn_error.c\bn_fast_mp_invmod.c\bn_fast_mp_montgomery_reduce.c\bn_fast_s_mp_mul_digs.c\bn_fast_s_mp_mul_…

编程日记2024/09/10 21:20:00

7. Query Expressions（查询表达式）

【返回目录】 查询表达式提供了与SQL这样的关系化和分级的查询语言相类似的语言集成的语法。一个查询表达式是以from子句开头以select或者group子句结束&#xff0c;这个初始的from子句可以在其后跟随任意多个from、let、where或者join子句。 那么查询表达式中的这些子句都是做…

编程日记2024/09/10 21:10:00

CSS完美兼容IE6/IE7/IE8/IE9/IE10的通用方法

300px!important;width /**/:340px;margin:0 10px 0 10px} &#xff0c;关于这个/**/是什么我也不太明白&#xff0c;只知道IE5和firefox都支持但IE6不支持&#xff0c;如果有人理解的话&#xff0c;请告诉我一声&#xff0c;谢了&#xff01;&#xff1a;&#xff09; 3、ul标…

编程日记2024/09/10 21:00:00

(C++)A+B 输入输出练习VII 输入包含若干行，每行输入两个整数a和b，由空格分隔。 对于每组输入，输出a和b的和，每行输出后接一个空行。

#include<stdio.h> /* 1 5 10 20 */int main() { int a,b;while(scanf("%d%d",&a,&b) ! EOF){printf("%d\n\n",ab);}return 0;}

编程日记2024/09/10 20:50:00

Address already in use: JVM_Bind错误的解决

错误原因 tomcat的8005端口号被占用了 解决办法 关闭已有的占用端口 1. cmd—>netstat -an 查看当前开启的端口号 2. netstat -ano 获得端口号的pid码 3. skill -{pid} 杀死端口进程转载于:https://www.cnblogs.com/lxq0309/p/3736899.html

编程日记2024/09/10 20:40:00

在SQL Server中如何转化长日期形式为短日期格式

convert(nvarchar(10),字段名,121)即可将时间格式转化为yyyy-mm-dd格式 convert中的121是指将datetime类型转换为char类型时获得包括世纪位数的4位年份。转载于:https://www.cnblogs.com/footleg/archive/2007/11/29/976451.html

编程日记2024/09/10 20:30:00

看看Vector源码Java 9

2019独角兽企业重金招聘Python工程师标准>>> Vector类实现了一个可增长的对象数组。像数组一样&#xff0c;它包含可以使用整数索引随机访问。但是&#xff0c;Vector的大小可以根据需要增大或缩小&#xff0c;以适应在创建Vector之后添加和删除项目。 文档里的内容…

编程日记2024/09/10 20:20:00

(C++)1016 部分A+B 正整数

#include<cstdio>int main(){ //1.读入a,Da,b,Dblong long a,b,Pa0,Pb0;int Da,Db;scanf("%lld%d%lld%d",&a,&Da,&b,&Db); //2.对于a,遍历每一位&#xff0c;加在Pa上 //2.1取余的方式遍历while(a>0){if(a%10Da){Pa Pa*10 Da;}a a/10;} …

编程日记2024/09/10 20:10:00

MySQL Innodb日志机制深入分析

1.1. Log & Checkpoint Innodb的事务日志是指Redo log&#xff0c;简称Log,保存在日志文件ib_logfile*里面。Innodb还有另外一个日志Undo log&#xff0c;但Undo log是存放在共享表空间里面的&#xff08;ibdata*文件&#xff09;。 由于Log和Checkpoint紧密相关&#xff0…

编程日记2024/09/10 20:00:00

单元测试的重要性

一些错误的认识 在实际的单元测试过程中总会有一些错误的认识左右着我们&#xff0c;使之成为单元测试最大的障碍&#xff0c;在此将其一一分析如下&#xff1a; 它太浪费时间了&#xff0c;现在要赶进度&#xff0c;时间上根本不允许&#xff0c;或者随便做做应付领导。 我是一…

编程日记2024/09/10 19:50:00

浅谈网络协议(四) IP的由来--DHCP与PXE

2019独角兽企业重金招聘Python工程师标准>>> 上一节说过&#xff0c;IP就是一台计算机的通讯地址&#xff0c;要和其他机器通讯&#xff0c;就需要一个通讯地址&#xff0c;就要给网卡配置这么一个地址。 配置 IP 那如何配置呢&#xff1f;可以使用 ifconfig&#x…

编程日记2024/09/10 19:40:00