多列聚集索引查询未含前导列时是否命中？如何验证索引使用情况

基础信息梳理 #

现有SQL表 deadpool，字段定义如下：

cisid       int
afid        int
ccid        int
entity      int
dtime       int
guid        uniqueidentifier
addFlags    int

表上已创建三列组合索引（同时是主键聚簇索引），索引列顺序为：

cisid, afid, ccid 

待执行的DELETE语句通过关联表变量@tempT匹配删除数据（原语句存在表别名笔误，以执行计划中的实际逻辑为准）：

DELETE  FROM deadpool
FROM    @tempT AS temp
WHERE   temp.afid = deadpool.afid
    AND temp.ccid = deadpool.ccid

已获取的执行计划如下：

DELETE  FROM deadpool FROM    @tempT AS temp   WHERE   temp.afid = deadpool.afid    AND temp.ccid = deadpool.ccid
       |--Clustered Index Delete(OBJECT:([Magneto].[dbo].[deadpool].[deadpool_cis_afid_ccid_PK]))
            |--Nested Loops(Left Semi Join, WHERE:(@tempT.[afid] as [temp].[afid]=[Magneto].[dbo].[deadpool].[afId] AND @tempT.[ccid] as [temp].[ccid]=[Magneto].[dbo].[deadpool].[ccid]))
                 |--Clustered Index Scan(OBJECT:([Magneto].[dbo].[deadpool].[deadpool_cis_afid_ccid_PK]))
                 |--Table Scan(OBJECT:(@tempT AS [temp]))

三列组合索引是否会被本次查询使用 #

该组合索引不会被有效利用做快速定位查找，当前执行逻辑为全聚簇索引扫描，原因如下：

• 组合索引遵循最左前缀匹配规则：B+树结构的组合索引会严格按照索引定义的列顺序逐层排序，即先按第一列cisid排序，cisid值相同的区间内再按afid排序，最后按ccid排序。如果查询过滤条件中没有包含索引最左侧的cisid列的等值/范围过滤条件，数据库无法直接通过索引的有序性定位到符合afid、ccid要求的数据区间，只能扫描整个索引的所有数据逐行匹配。
• 从提供的执行计划可以直接验证：对deadpool表的访问操作是Clustered Index Scan（聚簇索引全扫描），即遍历整个聚簇索引（也就是整张表的所有数据）逐行和@tempT做半连接匹配，没有用到索引的快速查找能力。

注：这里的聚簇索引扫描确实是在访问你创建的三列主键索引，但这种全扫描不属于我们日常说的“索引命中”——通常说的索引命中是指利用索引的有序性做Seek（查找），仅读取符合条件的少量数据，避免全表扫描的开销。

验证索引是否被实际调用的常用方法 #

• 查看执行计划：执行语句时开启「包括实际执行计划」，如果目标表的对应操作是Index Seek（非聚簇索引查找）/Clustered Index Seek（聚簇索引查找），且操作指向的对象是你创建的目标索引，说明索引被有效用于快速定位；如果操作是Index Scan/Clustered Index Scan，说明是全索引扫描，没有有效利用索引的查找能力。
• 开启IO统计验证：执行语句前运行SET STATISTICS IO ON，执行完成后查看消息栏输出的逻辑读次数。如果命中索引做Seek，逻辑读数值会远低于全表扫描的数值；如果逻辑读数值和表数据占用的总页数基本持平，说明是全扫描，没有用到索引查找。
• 查询索引使用统计视图：通过系统动态管理视图sys.dm_db_index_usage_stats可以查看索引的使用计数，执行目标语句后，如果对应索引的user_seeks计数增长，说明索引被用于查找定位；如果只有user_scans计数增长，说明仅发生了全索引扫描，没有做精准查找。

优化建议 #

如果该删除操作是高频业务逻辑，建议调整索引顺序，将过滤条件中用到的afid、ccid放到索引最左侧，或者单独创建(afid, ccid)顺序的索引，才能让语句走索引查找，避免全表扫描带来的性能开销。如果@tempT中存储的数据量较大，也可以为表变量/临时表的afid、ccid列创建索引，提升关联匹配的效率。

内容的提问来源于stack exchange，提问作者user11247278