完美的统计信息直方图只有几个阶梯数(steps)
2025-03-27 22:40 潇湘隐者 阅读(72) 评论(0) 收藏 举报本文是翻译官方博客Perfect statistics histogram in just few steps[1],如有翻译不当或错误的地方,敬请指正或提醒.翻译这篇文章,是因为它解答了我的一个困惑,为什么使用fullscan 更新统计信息后,统计信息的直方图的阶梯数反而变得更少了? 传统观念中,直方图的阶梯数似乎是越多越精确. 如果你也发现了这些细节现象，那么就看译文吧！
本周，团队两次遇到了有关统计信息的类似问题:"为什么使用完全扫描更新统计信息会导致直方图的阶梯数/步长数比抽样扫描(这里只指按少于100%的采样比例更新统计信息)更新统计信息时的少呢？ 答案是：直方图阶梯/步长(histogram steps)的数量可以比字段不同值(distinct values)的数量更少"。 对于具有完美分布（frequency* = 1）的直方图，合并直方图将少到只有三个阶梯/步长，因为这是准确描绘底层数据分布所需的阶梯数。
频率(Frequency)通过将行数和密度相乘来计算(计算公式为row count * density)。 密度计算为 1/不同值计数。有关更多信息，请参阅统计信息的官方文档[2]页面 。
让我们来看几个可以观察到这种情况的例子：使用 IDENTITY （或任何不可重复的整数）和使用 GUID（特别是 NEWID ）：
创建一个以 UNIQUEIDENTIFIER 列或 IDENTITY 作为主键的新表。两者在设计上都是独一无二的。
IF NOT EXISTS (SELECT [object_id] FROM sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID(N'[CustomersTableGuid]') AND [type] IN (N'U'))
CREATE TABLE CustomersTableGuid
(
ID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50)
)
GO
IF NOT EXISTS (SELECT [object_id] FROM sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID(N'[CustomersTableIdent]') AND [type] IN (N'U'))
CREATE TABLE CustomersTableIdent
(
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50)
)
GO
插入 100 万条记录。
SET NOCOUNT ON;
DECLARE @i INT = 0
WHILE (@i <= 1000000)
BEGIN
INSERT INTO CustomersTableGuid (FirstName, LastName)
VALUES ('FirstName' + CAST(@i AS VARCHAR),'LastName' + CAST(@i AS VARCHAR))
INSERT INTO CustomersTableIdent (FirstName, LastName)
VALUES ('FirstName' + CAST(@i AS VARCHAR),'LastName' + CAST(@i AS VARCHAR))
SET @i +=1
END
GO
让我们使用 FULLSCAN 更新统计数据。
UPDATE STATISTICS CustomersTableGuid WITH FULLSCAN
GO
UPDATE STATISTICS CustomersTableIdent WITH FULLSCAN
GO
现在，让我们查找统计信息：
验证抽样记录数和直方图阶梯数/步长，每一条统计数据有3个阶梯数/步长，抽样率为100%（rows = rows_sampled）。
SELECT OBJECT_NAME(stat.[object_id]) AS [TableName], sp.stats_id, name,
last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.[object_id], stat.stats_id) AS sp
WHERE stat.[object_id] = OBJECT_ID('CustomersTableGuid')
OR stat.[object_id] = OBJECT_ID('CustomersTableIdent');
GO
注释:原文链接的图裂了,个人实验略有区别,有一条统计信息直方图的阶梯数为4,不是3,可能跟实验环境有关系(SQL Server 2019),如下截图所示:
查看ID列的直方图，我们可以看到每个统计数据都有3个阶梯数。SQL Server 会将多个直方图阶梯压缩为尽可能少的阶梯/步长， 而不会损失直方图质量。正如预期的那样，一个完美的数据分布的全表扫描(fullscan)，因此可以将直方图压缩为仅3个阶梯数。
SELECT OBJECT_NAME(stat.[object_id]) AS [TableName], stat.name, sh.stats_id,
sh.range_high_key, sh.range_rows, sh.equal_rows
FROM sys.stats AS stat
INNER JOIN sys.stats_columns AS sc
ON stat.stats_id = sc.stats_id AND stat.[object_id] = sc.[object_id]
INNER JOIN sys.all_columns AS ac
ON ac.column_id = sc.column_id AND ac.[object_id] = sc.[object_id]
CROSS APPLY sys.dm_db_stats_histogram(stat.[object_id], stat.stats_id) AS sh
WHERE (stat.[object_id] = OBJECT_ID('CustomersTableGuid')
OR stat.[object_id] = OBJECT_ID('CustomersTableIdent'))
AND ac.name = 'ID';
GO
让我们以表CustomersTableIdent的ID字段的直方图的阶梯数为例:
第一阶梯(step)的值为 1；
第二阶梯(step)有999,998行记录没有重复(它的密度为1)；
第三阶梯(step)的最后一行为 1,000,001。
现在让我们用一个较小的采样比例更新统计数据（可以尝试任何你想要的值），并查看统计信息的数据。SQL Server 提取页面数据，然后将其推断为完整分布。因此，正如预期的那样，抽样分布只是近似的，并且作为推断，这就是为什么我们看到的频率接近1，但不完全是 1。
原文图片裂了，下面是我的实验截图。
UPDATE STATISTICS CustomersTableGuid WITH SAMPLE 90 PERCENT
GO
UPDATE STATISTICS CustomersTableIdent WITH SAMPLE 90 PERCENT
GO
总之，我们在这里所做的是将 1,000,001 个唯一键插入表中。全扫描的直方图有3个阶梯/步长，反映了这种完美的数据分布。另一方面，使用采样统计时，SQL Server会随机抽取页面值的数据，然后推断其分布。
更重要的是，统计信息对象中的阶梯数越多并不总是意味着有更好的键值覆盖，以及更好的估计/预估。在统计信息[3]文档页面上的中可以找到更多信息，特别是统计信息是如何构建的。
关于GUID的一点建议：我的建议是不要将它们用作谓词，或者用于任何需要对范围扫描进行良好估计的地方。如果你真的必须将它们用作表的主键以保持唯一性，从而尽可能利用它来进行单例查找，那么GUID就足够好了。请使用NEWSEQUENTIALID生成而不是NEWID，将主键创建为非聚集索引，并获得一个可以满足良好聚集键要求的替代键。 。
1: https://techcommunity.microsoft.com/blog/sqlserver/perfect-statistics-histogram-in-just-few-steps/385734
[2]2: https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16#densityvector
[3]3: https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16
浙公网安备 33010602011771号