为什么此 SQL 会导致索引扫描而不是索引查找？

Question

有人可以帮我调整这个 SQL 查询吗？

SELECT  a.BuildingID, a.ApplicantID, a.ACH, a.Address, a.Age, a.AgentID, a.AmenityFee, a.ApartmentID, a.Applied, a.AptStatus, a.BikeLocation, a.BikeRent, a.Children, 
        a.CurrentResidence, a.Email, a.Employer, a.FamilyStatus, a.HCMembers, a.HCPayment, a.Income, a.Industry, a.Name, a.OccupancyTimeframe, a.OnSiteID,
        a.Other, a.ParkingFee, a.Pets, a.PetFee, a.Phone, a.Source, a.StorageLocation, a.StorageRent, a.TenantSigned, a.WasherDryer, a.WasherRent, a.WorkLocation, 
        a.WorkPhone, a.CreationDate, a.CreatedBy, a.LastUpdated, a.UpdatedBy
FROM    dbo.NPapplicants AS a INNER JOIN
        dbo.NPapartments AS apt ON a.BuildingID = apt.BuildingID AND a.ApartmentID = apt.ApartmentID
WHERE   (apt.Offline = 0)
AND     (apt.MA = 'M')

。

执行计划如下所示：

。

我不明白的是为什么我要对 NP 申请人进行索引扫描。我有一个涵盖 BuildingID 和 ApartmentID 的索引。不应该用这个吗？

Answer 1

这是因为预计比赛将返回近 10K 条记录。使用 10K 键返回到数据以检索其他列相当于仅扫描 100K 记录（至少）并使用哈希匹配进行过滤的性能。

至于对另一个表的访问，查询优化器已确定您的索引有用（可能针对 Offline 或 MA），因此它正在寻找该索引以获取连接键。

然后将这两者进行 HASH 匹配以产生最终输出。

Answer 2

B-Tree 索引中的查找成本是表扫描（每条记录）的几倍。

此外，应在聚集索引中进行另一次查找以检索其他列的值。

如果预计大部分记录都匹配，那么扫描聚集索引会更便宜。

为了确保优化器选择了最佳方法，您可以运行以下命令：

SET STATISTICS IO ON
SET STATSTICS TIME ON

SELECT  a.BuildingID, a.ApplicantID, a.ACH, a.Address, a.Age, a.AgentID, a.AmenityFee, a.ApartmentID, a.Applied, a.AptStatus, a.BikeLocation, a.BikeRent, a.Children, 
        a.CurrentResidence, a.Email, a.Employer, a.FamilyStatus, a.HCMembers, a.HCPayment, a.Income, a.Industry, a.Name, a.OccupancyTimeframe, a.OnSiteID,
        a.Other, a.ParkingFee, a.Pets, a.PetFee, a.Phone, a.Source, a.StorageLocation, a.StorageRent, a.TenantSigned, a.WasherDryer, a.WasherRent, a.WorkLocation, 
        a.WorkPhone, a.CreationDate, a.CreatedBy, a.LastUpdated, a.UpdatedBy
FROM    dbo.NPapplicants AS a INNER JOIN
        dbo.NPapartments AS apt ON a.BuildingID = apt.BuildingID AND a.ApartmentID = apt.ApartmentID
WHERE   (apt.Offline = 0)
AND     (apt.MA = 'M')

SELECT  a.BuildingID, a.ApplicantID, a.ACH, a.Address, a.Age, a.AgentID, a.AmenityFee, a.ApartmentID, a.Applied, a.AptStatus, a.BikeLocation, a.BikeRent, a.Children, 
        a.CurrentResidence, a.Email, a.Employer, a.FamilyStatus, a.HCMembers, a.HCPayment, a.Income, a.Industry, a.Name, a.OccupancyTimeframe, a.OnSiteID,
        a.Other, a.ParkingFee, a.Pets, a.PetFee, a.Phone, a.Source, a.StorageLocation, a.StorageRent, a.TenantSigned, a.WasherDryer, a.WasherRent, a.WorkLocation, 
        a.WorkPhone, a.CreationDate, a.CreatedBy, a.LastUpdated, a.UpdatedBy
FROM    dbo.NPapplicants WITH (INDEX (index_name)) AS a
INNER JOIN
        dbo.NPapartments AS apt ON a.BuildingID = apt.BuildingID AND a.ApartmentID = apt.ApartmentID
WHERE   (apt.Offline = 0)
AND     (apt.MA = 'M')

将 index_name 替换为索引的实际名称，并比较执行时间和 I/O 数量代码>操作（如消息选项卡中所示）