主页 / dba / 问题 / 23509
Accepted
Nate
Asked: 2012-09-01 06:49:11 +0800 CST

为什么这个查询不使用我的非聚集索引，我该怎么做？

  • 772

作为这个关于提高查询性能的问题的后续行动，我想知道是否有办法让我的索引默认使用。

此查询运行大约 2.5 秒：

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31';

这个运行大约 33 毫秒：

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31' 
ORDER BY [DateEntered], [DeviceID];

[ID] 字段 (pk) 上有一个聚集索引，[DateEntered],[DeviceID] 上有一个非聚集索引。第一个查询使用聚集索引，第二个查询使用我的非聚集索引。我的问题是两个部分：

  • 为什么，因为两个查询在 [DateEntered] 字段上都有 WHERE 子句，服务器是否在第一个而不是第二个上使用聚集索引？
  • 即使没有orderby，如何在此查询中默认使用非聚集索引？（或者我为什么不想要这种行为？）
sql-server sql-server-2008-r2

2 个回答

  1. 使用不同的语法表达查询有时可以帮助您将使用非聚集索引的愿望传达给优化器。您应该会发现下面的表格为您提供了您想要的计划：

    SELECT
    [ID],
    [DeviceID],
    [IsPUp],
    [IsWebUp],
    [IsPingUp],
    [DateEntered]
FROM [dbo].[Heartbeats]
WHERE
    [ID] IN
(
    -- Keys
    SELECT TOP (1000)
        [ID]
    FROM [dbo].[Heartbeats]
    WHERE 
        [DateEntered] >= CONVERT(datetime, '2011-08-30', 121)
        AND [DateEntered]  < CONVERT(datetime, '2011-08-31', 121)
);

    查询计划

    将该计划与使用提示强制非聚集索引时生成的计划进行比较：

    SELECT TOP (1000) 
    * 
FROM [dbo].[Heartbeats] WITH (INDEX(CommonQueryIndex))
WHERE 
    [DateEntered] BETWEEN '2011-08-30' and '2011-08-31';

    强制索引提示计划

    这些计划本质上是相同的（Key Lookup 只不过是在聚集索引上寻找）。两种计划形式都只会在非聚集索引上执行一次查找，并且最多对聚集索引执行 1000 次查找。

    重要的区别在于 Top 操作员的位置。Top 位于两个查找之间，可防止优化器用聚集索引的逻辑等效扫描替换两个查找操作。优化器通过用等效的关系操作替换部分逻辑计划来工作。Top 不是关系运算符，因此重写会阻止转换为聚集索引扫描。如果优化器能够重新定位 Top 运算符，由于成本估算的工作方式，它仍然更喜欢扫描而不是 seek + 查找。

    扫描和搜索的成本计算

    在非常高的水平上，优化器的扫描和搜索成本模型非常简单：它估计 320 次随机搜索的成本与扫描 1350 个页面的成本相同。这可能与任何特定现代 I/O 系统的硬件功能几乎没有相似之处，但它作为一个实用模型确实工作得相当好。

    该模型还做了许多简化假设，其中一个主要假设是假设每个查询都以没有数据或索引页已经在缓存中开始。这意味着每个 I/O 都会产生一个物理 I/O——尽管在实践中很少会出现这种情况。即使使用冷缓存，预取和预读也意味着在查询处理器需要时，所需的页面实际上很可能已经在内存中。

    另一个考虑是，对不在内存中的行的第一次请求将导致从磁盘获取整个页面。对同一页上的行的后续请求很可能不会产生物理 I/O。成本计算模型确实包含一些考虑此类影响的逻辑，但它并不完美。

    所有这些（以及更多）意味着优化器倾向于比可能更早地切换到扫描。如果产生物理操作，随机 I/O 仅比“顺序”I/O“贵得多”——访问内存中的页面确实非常快。即使在需要物理读取的情况下，由于碎片，扫描也可能根本不会导致顺序读取，并且可以配置查找以使模式基本上是顺序的。再加上现代 I/O 系统（尤其是固态）不断变化的性能特征，整个事情开始看起来非常不稳定。

    行目标

    计划中顶级运营商的存在会修改成本计算方法。优化器足够聪明，知道使用扫描查找 1000 行可能不需要扫描整个聚集索引 - 一旦找到 1000 行，它就会停止。它在 Top 运算符处设置了 1000 行的“行目标”，并使用统计信息从那里返回以估计它期望从行源中需要多少行（在本例中为扫描）。我在这里写了这个计算的细节。

    此答案中的图像是使用SQL Sentry Plan Explorer创建的。

    • 21
  2. Best Answer

    第一个查询根据我之前解释的阈值进行表扫描：是否可以在具有数百万行的窄表上提高查询性能？

    （很可能没有该TOP 1000子句的查询将返回超过 46k 行。或者在 35k 和 46k 之间。（灰色区域 ;-)）

    第二个查询，必须订购。由于您的 NC 索引按您想要的顺序排序，因此优化器使用该索引更便宜，然后对聚集索引进行书签查找以获取丢失的列，与进行聚集索引扫描然后需要订购。

    反转子句中列的顺序，ORDER BY您将返回到聚集索引扫描，因为 NC INDEX 是无用的。

    编辑忘记了第二个问题的答案，为什么你不想要这个

    使用非聚集非覆盖索引意味着在 NC 索引中查找 rowID，然后必须在聚集索引中查找丢失的列（聚集索引包含表的所有列）。在聚集索引中查找缺失列的 IO 是随机 IO。

    这里的关键是随机的。因为对于在 NC 索引中找到的每一行，访问方法都必须在聚集索引中查找新页面。这是随机的，因此非常昂贵。

    现在，另一方面，优化器也可以进行聚集索引扫描。它可以使用分配映射来查找扫描范围，然后开始读取大块的聚集索引。这是连续的，而且便宜得多。（只要你的表没有碎片:-)）缺点是，需要读取整个聚集索引。这对您的缓冲区和潜在的大量 IO 不利。但仍然是顺序 IO。

    在您的情况下，优化器决定了 35k 到 46k 行之间的某个位置，完全聚集索引扫描的成本较低。是的，这是错误的。并且在很多情况下，对于非选择性WHERE子句或大型表的窄非聚集索引，这会出错。（你的桌子更糟，因为它也是一张很窄的桌子。）

    现在，添加ORDER BY会使扫描完整聚集索引然后对结果进行排序变得更加昂贵。相反，优化器假设使用已排序的 NC 索引更便宜，然后为书签查找支付随机 IO 损失。

    因此，您的 order by 是一种完美的“查询提示”解决方案。但是，在某个时刻，一旦您的查询结果如此之大，书签查找随机 IO 的惩罚就会如此之大，它会变得更慢。我假设优化器会在此之前将计划更改回聚集索引扫描，但您永远无法确定。

    在您的情况下，只要您的插入按entereddate 排序，如聊天和上一个问题（请参阅链接）中所述，您最好在enteredDate 列上创建聚集索引。

    • 10

相关问题