主页 / dba / 问题 / 253192
Accepted
Click Ok
Asked: 2019-11-14 08:30:02 +0800 CST

为什么不使用此日期时间索引？

  • 772

我有一张这样的桌子：

CREATE TABLE TestTable
(
    [TestTableID] [int] IDENTITY(1,1) NOT NULL,
    [IntField1] [int] NOT NULL,
    [IntField2] [int] NOT NULL,
    [IntField3] [int] NOT NULL,
    [IntField4] [int] NOT NULL,
    [IntField5] [int] NOT NULL,
    [DateField1] [datetime] NOT NULL,
    [IntField6] [int] NOT NULL,
    [IntField7] [int] NOT NULL,
    [TextField1] [nvarchar](300) NULL,
    [DateField2] [datetime] NULL,
    [TextField2] [nvarchar](300) NULL,
    [DateField3] [datetime] NULL,
    [BoolField1] [bit] NULL
)

我创建了一个这样的索引：

CREATE NONCLUSTERED INDEX IX_TestTable_DateField1
ON TestTable(DateField1);

现在我有这个查询：

    DECLARE @startDate DATETIME = '20190101'
          , @endDate   DATETIME = '20200101'

    SELECT [TestTableID], 
           [IntField1], 
           [IntField2], 
           [IntField3], 
           [IntField4], 
           [IntField5], 
           [DateField1], 
           [IntField6], 
           [IntField7], 
           [TextField1], 
           [DateField2], 
           [TextField2],
           [DateField3], 
           [BoolField1]
      FROM TestTable
     WHERE DateField1 >= @startDate
       AND DateField1 < @endDate

这张表有近1000万条记录，本次查询将返回近10000条记录。

现在，我希望查询至少使用我的索引IX_TestTable_DateField1（索引扫描 + 键查找），但它正在执行聚集索引扫描（在 PK 字段上）。我认为这是因为查询返回了表的所有字段。

我之前的想法是：

  1. 如果索引已包含所有字段，则 SqlServer 将执行 Index Seek；
  2. 如果没有包含所有字段，但如果该字段用于 WHERE 或 ORDER 中，则会使用 Index Scan + Key Lookup；
  3. 如果既不是 1 也不是 2，则进行 Clustered Index Scan；

这个对吗？为什么没有发生“索引扫描 + 键查找”？

sql-server index

3 个回答

  1. SQL server 绝对可以先进行索引查找，然后再进行查找，即使您没有覆盖查询。

    优化器不知道变量中有什么值（这就是变量的工作方式）。所以它必须猜测选择性。您可以查看实际的执行计划并查看它猜测的行数。显然它猜测了这么多行，所以它决定最好进行表扫描（cl ix scan）。

    如果您在查询末尾添加 OPTION(RECOMPILE)，您应该会看到不同的估计选择性，以及索引的潜在使用情况（所有这些都基于您最终拥有的选择性）。

    此外，如果您有文字（值已知）或存储过程参数（值被嗅探），您将看到它如何以不同的方式估计。

    • 4
  2. Best Answer

    这是微软所说的优化 SELECT 语句

    SQL Server 查询优化器是一个基于成本的优化器。每个可能的执行计划在使用的计算资源量方面都有相关的成本。查询优化器必须分析可能的计划并选择估计成本最低的计划。

    [...]

    SQL Server 查询优化器不会只选择资源成本最低的执行计划；它选择以合理的资源成本向用户返回结果并且返回结果最快的计划。例如，并行处理查询通常比串行处理使用更多的资源，但完成查询的速度更快。

    如您所见，查询优化器将选择它期望获得最有效执行的计划。有时使用 (Index Scan + Key Lookup) 并不是最有效的方法。

    作为测试，您可以将您现在获得的执行计划STATISTICS TIME和STATISTICS IO结果与为您的查询生成的结果进行比较，从而强制它使用带有查询提示的索引。请注意，我不建议您将此提示用作解决方案，而是作为比较执行性能的一种方式，如果它按照您的意愿使用索引。

    为了进一步阅读，Benjamin Nevarez 的文章带来了一些很好的信息：SQL Server 查询优化器

    • 2

  3. 您观察到的问题与 SQL Server 确定执行查询的最佳方式的方式有关，称为参数嗅探。

    参考阅读

    什么？

    当您第一次执行查询时，SQL Serer 查询优化器在 Cardinality Estimator 的帮助下使用可用索引的统计信息来确定检索您当时请求的数据的方式。

    现在，如果您传递给变量的值最初如下所示：

    DECLARE @startDate DATETIME = '19010101'
      , @endDate   DATETIME = '20200101'

    ..然后查询优化器很快确定它需要扫描索引中的所有数据IX_TestTable_DateField1以检索所有行（或者甚至可能略多于整个数据的 50%）以有效地执行语句。

    查询优化器没有使用IX_TestTable_DateField1索引来检索所有记录以匹配您的查询，而是选择读取聚集索引，因为聚集索引实际上是数据。（当您可以读取聚集索引并且已经拥有数据时，为什么要读取非聚集索引然后检索数据）。

    因为这是第一次运行，查询优化器将执行计划（针对初始值进行了优化）存储在计划缓存中。

    每当一个新的查询（具有不同的值）到达服务器时，QO 将看到它已经有一个满足查询要求的执行计划。

    主要的挫折是查询计划针对初始值进行了优化，当您为参数提供新值时，QO 不会创建新的查询计划，因为那是“昂贵的”。

    解决方案

    如果您希望您的查询使用索引，那么您将不得不：

    • 执行WITH RECOMPILE
    • 执行查询WITH OPTIMIZE FOR @startDate = '<value>', @endDate = '<value>'
    • 执行查询WITH OPTIMIZE FOR UNKNOWN
    • 从计划缓存中删除查询计划

    之后，您可能会观察到使用索引检索数据。

    参考阅读

    • 2

相关问题