一、 以下情况下会导致索引查找变成索引扫描
（1） 隐式转换
这里oc_code为char(9)类型，且建了【非聚集索引】，转换为【varchar】（【char】） 时，进行的是【索引查找】,但转换为【int】类型时，走的是【索引扫描】
并不是所有隐匿转换都会导致索引查找变成索引扫描
Implicit Conversions that cause Index Scans这篇博客介绍哪些数据类型之间隐匿转换会导致【索引扫描】。
查看我的数据库的排序规则为：Chinese_PRC_CI_AS
满足的是图2的规则，
这里ID为int类型，且建了【聚集索引】,转换为【nvarchar】 时，进行的是【索引查找】,但转换为【datatime】类型时，走的是【索引扫描】
下面是在数据库从执行计划中搜索隐式转换的SQL语句，可以在项目上线sql调优用
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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谓词会导致执行计划从索引查找变为索引扫描
SARG（Searchable Arguments）又叫查询参数， 它的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值的范围内的匹配或者两个以上条件的AND连接。不满足SARG形式的语句最典型的情况就是包括非操作符的语句，如：NOT、!=、<>；、!<；、!>；NOT EXISTS、NOT IN、NOT LIKE等，另外还有像在谓词使用函数、谓词进行运算等。
- 索引字段使用函数会导致索引扫描
- 索引字段进行运算会导致索引扫描
- like模糊查询会导致索引扫描， Like语句是否属于SARG取决于所使用的通配符的类型， LIKE ‘Condition%’ 就属于SARG，走的是索引查找；LIKE ’%Condition‘ 就属于非SARG谓词操作，走的是索引扫描
- SQL查询返回数据页（Pages）达到了临界点（Tipping Point）会导致索引扫描（Index Scan）或表扫描（Table Scan）
- 谓词不是联合索引的第一列会导致索引扫描（Index Scan）
更多这方面知识请访问这篇博客。
二、强制执行计划走索引
数据表OrgCompanyContact：有ID字段（主键、自增ID、聚集索引）、oc_code（非聚集索引）、其他字段
数据量在2000多万行左右
数据库版本：Microsoft SQL Server 2014
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- 获取一页公司的更多联系方式统计（强制走索引，sqlserver优化有时会导致索引扫描） select oc_code, COUNT(oc_code) AS num from ( select oc_code from[QZOrgCompanyExt].[dbo].[OrgCompanyContact]with (nolock,index =I_OrgCompanyContact_Code) where oc_code in ( 'MA59BQ3A6','088636635' ,'MA4UKA155' ,'331461522' ,'MA59EP1T2' ,'591546741' ,'724772559' ,'751056742') and oc_type IN (1,2) AND oc_status = 1 )t GROUP BY t.oc_code select oc_code, COUNT(oc_code) AS num from ( select oc_code from[QZOrgCompanyExt].[dbo].[OrgCompanyContact]with (nolock) where oc_code in ( 'MA59BQ3A6','088636635' ,'MA4UKA155' ,'331461522' ,'MA59EP1T2' ,'591546741' ,'724772559' ,'751056742') and oc_type IN (1,2) AND oc_status = 1 )t GROUP BY t.oc_code |
运行效果
走聚集键查找的是毫秒级别
走聚集索引扫描用了10秒多
转载请注明：二十画生 » sqlserver 索引查找与索引扫描
