重新索引会更新统计数据吗？

Question

重新索引会更新统计数据吗？

sql-serverindexsql-server-2012index-tuningstatistics

51

我过去一周一直在学习MS10775A课程，有一个问题是培训师无法可靠地回答的：

重新索引是否会更新统计信息？

我们在网上找到了一些讨论，有人认为会更新，也有人认为不会更新。

- Thor Erik

值得注意的是，REINDEX 在重建索引的过程中会作为副作用更新列统计信息 - 你不需要强制更新统计信息。表中的数据并没有发生变化。它仍然是相同的数据，只是 a) 它在磁盘上的位置发生了改变（当页面被重新组织时），或者 b) 它位于不同的页面上（在重新构建的情况下）。因此：重新索引确实会更新（某些）统计信息：没有必要再次进行更新。 - Ian Boyd

2个回答

9

SQL Server统计信息的Microsoft Docs页面states：

重建、碎片整理或重新组织索引等操作不会改变数据的分布。因此，在执行ALTER INDEX REBUILD、DBCC DBREINDEX、DBCC INDEXDEFRAG或ALTER INDEX REORGANIZE操作后，您无需更新统计信息。当使用ALTER INDEX REBUILD或DBCC DBREINDEX在表或视图上重建索引时，查询优化器会更新统计信息，但这个统计信息更新是重新创建索引的副产品。查询优化器不会在DBCC INDEXDEFRAG或ALTER INDEX REORGANIZE操作后更新统计信息。

- Andrii Viazovskyi

- MicSim · Accepted Answer

在关心更新统计数据时，您可以记住以下几点（摘自重建索引 vs. 更新统计数据 (Benjamin Nevarez)）：

默认情况下，UPDATE STATISTICS语句只使用表的记录样本。使用UPDATE STATISTICS WITH FULLSCAN将扫描整个表。

默认情况下，UPDATE STATISTICS语句同时更新索引和列的统计信息。使用COLUMNS选项只会更新列的统计信息。使用INDEX选项只会更新索引的统计信息。

例如，通过使用ALTER INDEX … REBUILD重建索引也会使用等效于使用WITH FULLSCAN进行索引统计信息更新，除非表是分区的，在这种情况下只对统计信息进行抽样（适用于SQL Server 2012及更高版本）。

手动创建的使用CREATE STATISTICS命令的统计信息在任何ALTER INDEX ... REBUILD操作中都不会被更新，包括ALTER TABLE ... REBUILD。如果在重建的表上定义了聚集索引，则ALTER TABLE ... REBUILD会更新聚集索引的统计信息。

例如使用ALTER INDEX ... REORGANIZE重新组织索引不会更新任何统计信息。

简短的回答是，你需要使用UPDATE STATISTICS来更新列统计信息，而索引重建只会更新索引统计信息。你可以使用UPDATE STATISTICS (tablename) WITH FULLSCAN;语法强制更新表上的所有统计信息，包括索引统计信息和手动创建的统计信息。

以下代码示例说明了上述封装的规则：

首先，我们将创建一个带有几个列和一个聚集索引的表：

USE tempdb;

IF OBJECT_ID(N'dbo.SomeTable', N'U') IS NOT NULL
DROP TABLE dbo.SomeTable;

CREATE TABLE dbo.SomeTable
(
    rn int NOT NULL IDENTITY(1,1)
        CONSTRAINT pk
        PRIMARY KEY NONCLUSTERED
    , i int NOT NULL INDEX i 
    , d sysname NOT NULL
) ON [PRIMARY] WITH (DATA_COMPRESSION = NONE);

CREATE UNIQUE CLUSTERED INDEX cx ON dbo.SomeTable (i, d);

CREATE STATISTICS d ON dbo.SomeTable (d) WITH FULLSCAN;

INSERT INTO dbo.SomeTable (d, i)
SELECT c1.name, c1.id
FROM sys.syscolumns c1;

这个查询显示了每个统计对象的最后更新日期。

SELECT ObjectName = sc.name + N'.' + o.name
    , StatsName = s.name
    , StatsDate = STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s
    INNER JOIN sys.objects o ON s.object_id = o.object_id
    INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE sc.name = N'dbo'
    AND o.name = N'SomeTable';

结果显示尚未进行任何更新，这是正确的，因为我们刚刚创建了该表：

╔═══════════════╦═══════════╦═══════════╗
║     对象名称    ║  统计名称   ║  统计日期   ║
╠═══════════════╬═══════════╬═══════════╣
║ dbo.SomeTable ║ cx        ║ NULL      ║
║ dbo.SomeTable ║ i         ║ NULL      ║
║ dbo.SomeTable ║ pk        ║ NULL      ║
║ dbo.SomeTable ║ d         ║ NULL      ║
╚═══════════════╩═══════════╩═══════════╝

让我们重新构建整个表格，看看是否更新了统计数据：

ALTER TABLE dbo.SomeTable REBUILD;

SELECT ObjectName = sc.name + N'.' + o.name
    , StatsName = s.name
    , StatsDate = STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s
    INNER JOIN sys.objects o ON s.object_id = o.object_id
    INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE sc.name = N'dbo'
    AND o.name = N'SomeTable';

对象名称统计名称统计日期 dbo.SomeTable cx 2018-09-17 14:09:13.590 dbo.SomeTable i NULL dbo.SomeTable pk NULL dbo.SomeTable d NULL

结果显示只有聚集索引统计信息被更新。

接下来，我们执行离散的UPDATE STATS操作：

UPDATE STATISTICS dbo.SomeTable(d) WITH FULLSCAN;

SELECT ObjectName = sc.name + N'.' + o.name
    , StatsName = s.name
    , StatsDate = STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s
    INNER JOIN sys.objects o ON s.object_id = o.object_id
    INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE sc.name = N'dbo'
    AND o.name = N'SomeTable';

如您所见，我们刚刚更新了d列的统计数据：

╔═══════════════╦═══════════╦═════════════════════════╗
║    对象名称     ║ 统计名称  ║         统计日期         ║
╠═══════════════╬═══════════╬═════════════════════════╣
║ dbo.SomeTable ║ cx        ║ 2018-09-17 14:09:13.590 ║
║ dbo.SomeTable ║ i         ║ NULL                    ║
║ dbo.SomeTable ║ pk        ║ NULL                    ║
║ dbo.SomeTable ║ d         ║ 2018-09-17 14:09:13.597 ║
╚═══════════════╩═══════════╩═════════════════════════╝

现在，我们将更新整个表的统计数据：

UPDATE STATISTICS dbo.SomeTable WITH FULLSCAN;

SELECT ObjectName = sc.name + N'.' + o.name
    , StatsName = s.name
    , StatsDate = STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s
    INNER JOIN sys.objects o ON s.object_id = o.object_id
    INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE sc.name = N'dbo'
    AND o.name = N'SomeTable';

如您所见，确保所有统计数据更新的唯一方法是手动更新每个统计项，或者使用UPDATE STATISTICS (table);命令更新整个表。