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

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介:

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 

clipboard

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

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

    2:使用强制转换(explicit conversion)方式。

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

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

clipboard[1]

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

clipboard[2]

clipboard[3]

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

    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'

clipboard[4]

 

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

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

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

clipboard[5]

 

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

SELECT  * FROM Person.Person WHERE  BusinessEntityID  < 250

clipboard[6]

 

2.3 LIKE模糊查询回导致索引扫描(Index Scan)

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

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

clipboard[7]

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

clipboard[8]

 

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)

clipboard[9]

上面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

clipboard[10]

clipboard[11]

临界点决定了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

clipboard[12]

SELECT * FROM Sales.SalesOrderDetail_Tmp WHERE SalesOrderDetailID<10

clipboard[13]

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
2月前
|
SQL Oracle 关系型数据库
SQL优化-使用联合索引和函数索引
在一次例行巡检中,发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句,首先分析了 where 条件中各列的选择性,并创建了不同类型的索引,包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划,最终确定了使用复合索引(包含函数表达式)能够显著降低查询成本,提高执行效率。
|
2月前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引:详细技巧与方法
在数据库管理中,索引是提高SQL查询性能的重要手段
|
2月前
|
SQL 存储 关系型数据库
SQL默认索引是什么:深入解析与技巧
在SQL数据库中,索引是一种用于提高查询性能的重要数据结构
|
2月前
|
SQL 存储 关系型数据库
SQL默认索引是什么
在SQL数据库中,索引是一种用于提高查询性能的数据结构
|
2月前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引
在数据库管理和优化过程中,确认SQL查询是否使用了索引是一个至关重要的步骤
|
2月前
|
SQL 关系型数据库 MySQL
如何确认SQL查询是否使用了索引:详细步骤与技巧
在数据库管理和优化中,确认SQL查询是否有效利用了索引是提升性能的关键步骤
|
2月前
|
索引
SQL_创建和管理索引
SQL_创建和管理索引
28 0
|
3月前
|
SQL 数据库
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
SQL Server附加数据库出现错误823,附加数据库失败。数据库没有备份,无法通过备份恢复数据库。 SQL Server数据库出现823错误的可能原因有:数据库物理页面损坏、数据库物理页面校验值损坏导致无法识别该页面、断电或者文件系统问题导致页面丢失。
104 12
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
|
20天前
|
SQL 存储 Linux
从配置源到数据库初始化一步步教你在CentOS 7.9上安装SQL Server 2019
【11月更文挑战第16天】本文介绍了在 CentOS 7.9 上安装 SQL Server 2019 的详细步骤,包括配置系统源、安装 SQL Server 2019 软件包以及数据库初始化,确保 SQL Server 正常运行。
|
29天前
|
SQL 存储 Linux
从配置源到数据库初始化一步步教你在CentOS 7.9上安装SQL Server 2019
【11月更文挑战第8天】本文介绍了在 CentOS 7.9 上安装 SQL Server 2019 的详细步骤,包括系统准备、配置安装源、安装 SQL Server 软件包、运行安装程序、初始化数据库以及配置远程连接。通过这些步骤,您可以顺利地在 CentOS 系统上部署和使用 SQL Server 2019。