主页 / dba / 问题 / 127096
Accepted
user541852587
Asked: 2016-01-24 05:29:20 +0800 CST

查询优化器会忽略碎片索引吗？

  • 772

场景：我有一个带有索引的大型 OLTP 表。我在一天或更短的时间内看到许多插入、更新和删除以及大量索引碎片。在索引构建的第一天，优化器使用索引，在第二天或第三天，优化器完全跳过它。这是针对完全相同的查询。

我脑子里的问题：为什么有些查询计划会跳过索引，因为创建索引是为了帮助优化这些计划？

这篇文章的问题：优化器是否可以跳过一个高度碎片化的索引，比如我们有 10 亿条记录并建立索引的场景，然后两个小时后，所有十亿条记录都被删除，我们有五亿条新记录？

我开始认为向该表添加索引根本无济于事，因为表的性质（数据输入快，数据输出快），但只是想了解为什么有一天，优化器会在其计划中使用索引，但第二天，它不会。

sql-server sql-server-2012

2 个回答

  1. Best Answer

    AFAIK 优化器不知道索引碎片。如果它选择一个扫描碎片索引的计划，这可能是一个问题。

    不过，优化器知道分配的数据大小。如果索引页有很多可用空间（可能是由于内部碎片），这会使索引不太可能被使用。50% 的空白空间意味着要扫描的 IO 量的两倍。不过，对于随机访问而言，这在很大程度上无关紧要。

    不过，这并不是一个巨大的影响。它可能会解释你所看到的。

    如果这个小的影响将查询计划翻转为不使用索引，那么在查询优化器的眼中，索引从来就不是超级好。这可能暗示您可以改进它。

    此外，优化器似乎可以猜测缓冲池中缓存了多少索引。在 XML 执行计划中有一些对此的引用。我对此没有详细的了解。

    我开始认为向该表添加索引根本无济于事

    我不会走那么远。也许您只需要在正确的位置重建或删除 DML 创建序列？或者，也许这只是一个查询调优问题（提出一个包含实际执行计划的新问题）。

    • 6

  2. 首先想到的是过时的统计数据，而不是索引本身的碎片化。

    在（重新）构建索引之后，与索引相关的统计信息是准确的；直方图范围涵盖所有值。随着表中数据的变化，统计信息不会立即更新。我现在不记得确切的阈值，即在执行自动更新统计信息之前应该删除/插入多少行。

    我在我们的系统中观察到类似的行为。我们系统中的简化工作流程如下。

    我们有一个约 1 亿行的表，其中包含 N 天的数据。在一天中，新行被添加datetime到索引列中并具有递增的值。全天分批添加数据（通常一次添加 1K-10K）。在午夜，维护过程会删除所有早于 N 天的值并重建索引。

    此外，在一天中，每隔 10 分钟，另一个过程会汇总数据并更新另一个表中的摘要，该表包含不太详细的数据，但保存时间更长。

    我注意到早上总结过程的表现还不错，但在当天晚些时候变得越来越差。我检查了执行计划，发现它们是不同的。在早上和晚上运行的相同查询有不同的计划（我使用OPTION(RECOMPILE)）。

    因此，我添加了一个程序来全天更新相关统计数据，而不依赖于内置阈值。

    CREATE PROCEDURE [dbo].[RebuildStatisticsOnMyTable]
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        UPDATE STATISTICS [dbo].[MyTableStats] ([IX_ImportantIndex], [IX_AnotherIndex]);
    END TRY
    BEGIN CATCH
        -- handle errors
        ...
    END CATCH;
END

    通过这种全天定期更新的统计数据，汇总程序的性能良好且稳定。我不得不进行一些实验，找到了更新统计数据的合适时间段。

    这是在 SQL Server 2008 上，据我所知也适用于 2012。2014 年有一个不同的、改进的基数估计器，它（据我所知）可以有效地推断统计数据，并在添加的行具有超出统计直方图范围的增长时间戳的情况下产生不错的预测。我现在不记得我在哪里看到过这方面的详细描述。很可能是 Paul White 或 Aaron Bertrand 的博客文章。所以，很可能，如果我们升级到 2014 年，就不需要全天强制更新这些统计数据。

    • 3

相关问题