在 SQL Server 中进行更多逻辑读取的索引查找结果

我有下表和数据。我有两个索引：一个只包含 where 子句中的几列，另一个包含 where 子句中的所有列。

在查询中使用不同的索引时，我对观察到的行为感到困惑。具体来说，当我使用第一个索引时，它只包含 where 子句中的几列，我注意到逻辑读取次数很少，但读取的行数却很多。相反，当我使用包含 where 子句中所有列的索引时，逻辑读取数增加而读取的行数减少。在第一种情况下，存在残留谓词，导致引擎扫描从查找操作检索到的所有行。然而，在第二种情况下，引擎直接寻找必要的值。鉴于此，我预计第二种情况下的逻辑读取次数会更少。我将不胜感激任何关于为什么会发生这种情况的见解。谢谢你！

CREATE TABLE myTable (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Column1 VARCHAR(50),
    Column2 VARCHAR(50),
    Column3 VARCHAR(50),
    Column4 VARCHAR(50),
    Column5 VARCHAR(50),
    Column6 VARCHAR(50),
    Column7 VARCHAR(50),
    Column8 VARCHAR(50),
    Column9 VARCHAR(50),
    Column10 VARCHAR(50)
)

DECLARE @i INT = 1
DECLARE @j INT = 1
DECLARE @distinct_value_count INT = 20
DECLARE @distinct_value_count_with_more_rows INT = 3
DECLARE @rows_per_distinct_value INT = (20000 - (@distinct_value_count_with_more_rows * 2000)) / (@distinct_value_count - @distinct_value_count_with_more_rows)

WHILE @i <= @distinct_value_count
BEGIN
    DECLARE @current_rows_per_value INT = @rows_per_distinct_value
    IF @i <= @distinct_value_count_with_more_rows
    BEGIN
        SET @current_rows_per_value = @rows_per_distinct_value + 2000
    END
    
    SET @j = 1
    WHILE @j <= @current_rows_per_value
    BEGIN
        INSERT INTO myTable (Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9, Column10)
        VALUES ('Value' + CAST(@i AS VARCHAR(2)),
                'Value' + CAST(@j AS VARCHAR(5)),
                'Value' + CAST(@j + 1 AS VARCHAR(5)),
                'Value' + CAST(@j + 2 AS VARCHAR(5)),
                'Value' + CAST(@j + 3 AS VARCHAR(5)),
                'Value' + CAST(@j + 4 AS VARCHAR(5)),
                'Value' + CAST(@j + 5 AS VARCHAR(5)),
                'Value' + CAST(@j + 6 AS VARCHAR(5)),
                'Value' + CAST(@j + 7 AS VARCHAR(5)),
                'Value' + CAST(@j + 8 AS VARCHAR(5)))
        SET @j = @j + 1
    END
    
    SET @i = @i + 1
END

Alter Table dbo.myTable
Add Column11 varchar(50), Column12 varchar(50)

Alter Table dbo.myTable
Add dateModified datetime

Update dbo.myTable
  set Column11 = Column1
     ,Column12 = Column1

Update Top (10) dbo.myTable
   Set Column11 = 'Value7'
  Where Column1 = 'Value1'

Update Top (10) dbo.myTable
   Set Column12 = 'Value7'
  Where Column1 = 'Value1'

Update Top (10) dbo.myTable
   Set Column11 = 'Value6'
  Where Column1 = 'Value1'

Update Top (10) dbo.myTable
   Set Column12 = 'Value6'
  Where Column1 = 'Value1'

Update Top (10) dbo.myTable
   Set Column11 = 'Value5'
  Where Column1 = 'Value1'

Update Top (10) dbo.myTable
   Set Column12 = 'Value5'
  Where Column1 = 'Value1'

Update dbo.myTable
  set dateModified = getdate() + ID

案例 1：逻辑读取数低但行数高

Create NonClustered Index Idx_col1 
On myTable(Column1, Column11)
Include (Column5, Column6,Column12, dateModified)

set Statistics io on
SELECT Column1
     , Column11
     , Column5
     , Column6
     , Column12
FROM myTable
WHERE Column1= 'Value1'
AND Column11 In( 'Value1','Value5','Value6', 'Value7')
And Column12 In ('Value1','Value6')
And dateModified > dateAdd(day,-5, getdate())

执行计划：https://www.brentozar.com/pastetheplan/? id=Sywz516Hh

IO统计：

(2803 rows affected)
Table 'myTable'. Scan count 4, logical reads 34, physical reads 1, page server reads 0, read-ahead reads 23, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

案例 2：逻辑读取高但读取的行数低

Create NonClustered Index Idx_col2 on myTable(Column1, Column11,Column12, dateModified)
Include (Column5, Column6)

set Statistics io on
SELECT Column1
     , Column11
     , Column5
     , Column6
     , Column12
FROM myTable
WHERE Column1= 'Value1'
AND Column11 In( 'Value1','Value5','Value6', 'Value7')
And Column12 In ('Value1','Value6')
And dateModified > dateAdd(day,-5, getdate())

执行计划：https://www.brentozar.com/pastetheplan/? id=HyWbi1aSh

IO统计：

(2803 rows affected)
Table 'myTable'. Scan count 8, logical reads 47, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.