替换 SQL Server SELECT 查询中索引查找上的索引扫描

Question

第一个查询适用于索引查找(https://www.brentozar.com/pastetheplan/?id=B1XMM_YeW(：

SELECT *
FROM dbo.MessageLast c
    WHERE c.Stat_Date >= '2017-04-25' 
                AND c.Stat_Date <= '2017-04- 26' 
                AND c.partner_id IN (111294, 100001, 111295, 111435, 111296, 118155,111498, 118397);

第二个查询与 idex 扫描错误 (https://www.brentozar.com/pastetheplan/?id=H1uvEdFgZ(：

CREATE TABLE #t (partner_id Int primary key clustered);
INSERT INTO  #t(partner_id) 
values  (111294), (100001), (111295), (111435), (111296), (118155),(111498), (118397);
SELECT *
FROM dbo.MessageLast c
    where  c.Stat_Date >= '2017-04-25' 
                and c.Stat_Date <= '2017-04- 26' 
                and c.partner_id in (select partner_id from #t);

使用 EXISTS 的第三个查询也是错误的(https://www.brentozar.com/pastetheplan/?id=ryk3IntgZ(：

 SELECT *
 FROM   dbo.MessageLast c with (forceseek)
 WHERE  c.Stat_Date >= '2017-04-25'
        and c.Stat_Date <= '2017-04-26'
        and exists(select
                       * 
                   from  #d as d where  c.partner_id = d.partner_id)

更改表变量或标准表上的临时表#t无济于事 - 与第二个查询的计划相同。

简单的演示脚本：

create table _tBig (
  dt Date
, id1 Int
, id2 Int
, id3 Int
, id4 Int
, txt Varchar(500)
) 
create clustered index PK_tBig  on _tBig (id4,dt)

--truncate table _tBig
declare @i Int = 1
set nocount on
while @i < 10000
begin
    insert  into _tBig with (tablock)
            (dt
           , id1
           , id2
           , id3
           , id4
           , txt
            )
    select top (1000)
            DateAdd(day, Rand() * 365 + 1, GetDate())
          , Row_Number() over (order by O.[object_id])
          , Rand() * 10000000 + 1
          , Rand() * 10000000 + 1
          , Rand() * 10000000 + 1
          , NewId()
    from    sys.all_objects O
    set @i += 1
end 

create table _tId (id1 Int)

--truncate table _tId
insert  into _tId
        (id1
        )
select top (5)
        Row_Number() over (order by O.[object_id])
from    sys.all_objects O

select  *
from    _tBig as tb
where   tb.dt >= '20170517'
        and tb.dt < '20170519'
        and tb.id1 in (1, 2, 3, 4, 5)
create index IX_Big on _tBig (dt,id1)
select  *
from    _tBig as tb
inner join _tId as ti on ti.id1 = tb.id1
where   tb.dt >= '20170517'
        and tb.dt < '20170519'

create index IX_Big2 on _tBig (id1,dt)
select  *
from    _tBig as tb
inner join _tId as ti on ti.id1 = tb.id1
where   tb.dt >= '20170517'
        and tb.dt < '20170519'
--drop table dbo._tBig
--drop table dbo._tId

Answer 1

发生这种情况是因为索引没有覆盖：您正在选择表中的所有字段。

当索引未涵盖查询SQL服务器时，应执行2个步骤：

1( 索引寻求查找数学谓词的 PK 值(并包含列(

2(对每一行进行键查找(在聚集索引中查找(，以查找未包含在索引中的列的值。

因此，在某些情况下，查询优化器决定全表扫描比这两个步骤更快。

您可以将SELECT *替换为SELECT dt(进行索引覆盖(，您将收到索引查找。以下是更多信息：什么是涵盖指数？

Answer 2

你应该试试：

SELECT *
FROM dbo.MessageLast c
        JOIN #t t on c.partner_id = t.partner_id
where  c.Stat_Date >= '2017-04-25' 
                and c.Stat_Date <= '2017-04- 26'