确定哈希索引的正确存储桶计数

2017-06-13

创建内存优化表时，必须为参数指定值 BUCKET_COUNT 。本主题建议确定参数的 BUCKET_COUNT 相应值。如果无法确定正确的桶数，可以改用非聚集索引。不正确的 BUCKET_COUNT 值（尤其是太低的值）可能会显著影响工作负荷性能以及数据库的恢复时间。最好过高估计桶计数。

重复索引键可能会降低哈希索引的性能，因为键被哈希处理到同一存储桶，从而导致该存储桶的链增加。

有关非聚集哈希索引的详细信息，请参阅哈希索引和Memory-Optimized 表上使用索引的准则。

为内存优化表上的每个哈希索引分配一个哈希表。为索引分配的哈希表的大小由 BUCKET_COUNTCREATE TABLE（Transact-SQL）或 CREATE TYPE（Transact-SQL）中的参数指定。存储桶计数将在内部向上舍入到二的下个幂次。例如，指定存储桶计数 300,000 将导致实际存储桶计数为 524,288。

有关存储桶计数的文章和视频的链接，请参阅如何确定哈希索引的正确存储桶计数（In-Memory OLTP）。

建议

在大多数情况下，存储桶计数应该为索引键中不同值数量的 1 到 2 倍。如果索引键包含大量重复值，则每个索引键值的平均行数超过 10 行，请改用非聚集索引

可能并不总是能够预测特定索引键可能具有或将具有的值数。如果 BUCKET_COUNT 的值在实际键值数量的 5 倍之内，则性能应该是可以接受的。

若要确定现有数据中唯一索引键的数目，请使用类似于以下示例的查询：

主键和唯一索引

由于主键索引是唯一的，因此键中的非重复值数对应于表中的行数。在 AdventureWorks 数据库的 Sales.SalesOrderDetail 表中，（SalesOrderID, SalesOrderDetailID）作为主键的一个示例，请发出以下查询来计算唯一主键值的数量，这些值对应于表中的行数：

SELECT COUNT(*) AS [row count]   
FROM Sales.SalesOrderDetail

此查询显示行计数 121,317。如果行计数不会显著更改，则使用存储桶计数为 240,000。如果表中的销售订单数预期为四倍，则使用桶计数为 480,000。

非唯一索引

对于其他索引，例如（SpecialOfferID，ProductID）上的多列索引，发出以下查询来确定唯一索引键值的数目：

SELECT COUNT(*) AS [SpecialOfferID_ProductID index key count]  
FROM   
   (SELECT DISTINCT SpecialOfferID, ProductID   
    FROM Sales.SalesOrderDetail) t

此查询返回 (SpecialOfferID, ProductID) 的索引键计数为 484，表明应选择使用非聚集索引而非非聚集哈希索引。

确定重复项数

若要确定索引键值的平均重复值数，请用唯一索引键数除以总行数。

对于（SpecialOfferID， ProductID）的示例索引，这会导致121317 / 484 = 251。这意味着索引键值的平均值为 251，因此这应该是非聚集索引。

对存储桶计数进行故障排除

若要排查内存优化表中的存储桶计数问题，请使用 sys.dm_db_xtp_hash_index_stats（Transact-SQL）获取有关空存储桶和行链长度的统计信息。以下查询可用于获取有关当前数据库中所有哈希索引的统计信息。如果数据库中有大型表，查询可能需要几分钟才能运行。

SELECT   
   object_name(hs.object_id) AS 'object name',   
   i.name as 'index name',   
   hs.total_bucket_count,  
   hs.empty_bucket_count,  
   floor((cast(empty_bucket_count as float)/total_bucket_count) * 100) AS 'empty_bucket_percent',  
   hs.avg_chain_length,   
   hs.max_chain_length  
FROM sys.dm_db_xtp_hash_index_stats AS hs   
   JOIN sys.indexes AS i   
   ON hs.object_id=i.object_id AND hs.index_id=i.index_id

哈希索引健康状况的两个关键指标是：

空桶百分比
empty_bucket_percent 指示哈希索引中的空存储桶数。

如果 empty_bucket_percent 小于 10%，则存储桶数量可能太低。理想情况下， empty_bucket_percent 应为 33% 或更大。如果存储桶计数与索引键值的数量匹配，则由于哈希分布，大约 1/3 的存储桶为空。

平均链长度
avg_chain_length 指示哈希存储桶中行链的平均长度。

如果 avg_chain_length 大于 10 且 empty_bucket_percent 大于 10%，则可能有许多重复的索引键值，并且非聚集索引更合适。平均链长度为 1 是理想的。

有两个因素会影响链长度：

重复;所有重复行都是哈希索引中同一链的一部分。
多个键值映射到同一存储桶。桶的数量越少，越多存储桶会被映射多个值。

例如，请考虑以下表格和脚本，用于在表格中插入示例行。

CREATE TABLE [Sales].[SalesOrderHeader_test]  
(  
   [SalesOrderID] [uniqueidentifier] NOT NULL DEFAULT (newid()),  
   [OrderSequence] int NOT NULL,  
   [OrderDate] [datetime2](7) NOT NULL,  
   [Status] [tinyint] NOT NULL,  
  
PRIMARY KEY NONCLUSTERED HASH ([SalesOrderID]) WITH ( BUCKET_COUNT = 262144 ),  
INDEX IX_OrderSequence HASH (OrderSequence) WITH ( BUCKET_COUNT = 20000),  
INDEX IX_Status HASH ([Status]) WITH ( BUCKET_COUNT = 8),  
INDEX IX_OrderDate NONCLUSTERED ([OrderDate] ASC),  
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )  
GO  
  
DECLARE @i int = 0  
BEGIN TRAN  
WHILE @i < 262144  
BEGIN  
   INSERT Sales.SalesOrderHeader_test (OrderSequence, OrderDate, [Status]) VALUES (@i, sysdatetime(), @i % 8)  
   SET @i += 1  
END  
COMMIT  
GO

该脚本在表中插入 262,144 行。它在主键索引和IX_OrderSequence中插入唯一值。它在索引IX_Status中插入大量重复值：脚本仅生成 8 个不同的值。

BUCKET_COUNT故障排除查询的输出如下所示：

索引名称	总桶计数	空桶数量	空桶百分比	平均链长度	max_chain_length (最大链长度)
IX_Status	8	4	50	65536	65536
IX_OrderSequence	32768	13	0	8	26
PK_SalesOrd_B14003C3F8FB3364	262144	96319	36	1	8

请考虑此表上的三个哈希索引：

IX_Status：50% 的存储桶为空，这是好的。但是，平均链长度非常高（65,536）。这表示大量重复值。因此，在这种情况下，使用非聚集哈希索引是不合适的。应改用非聚集索引。
IX_OrderSequence：0% 的存储桶为空，这一比例太低。此外，平均链长度为 8。由于此索引中的值是唯一的，这意味着平均有 8 个值映射到每个存储桶。应增加桶数。由于索引键具有 262,144 个唯一值，因此存储桶计数应至少为 262,144。如果预计未来增长，该数字应更高。
主键索引（PK__SalesOrder...）：36% 的存储桶为空，这很好。此外，平均链长度为 1，这也不错。无需更改。

有关排查内存优化哈希索引问题的详细信息，请参阅排查 Memory-Optimized 哈希索引的常见性能问题。

进一步优化的详细注意事项

本部分概述了优化存储桶计数的进一步注意事项。

若要实现哈希索引的最佳性能，请平衡分配给哈希表的内存量和索引键中非重复值的数量。点查询和表扫描的性能之间也存在平衡：

存储桶计数值越高，索引中将存在空存储桶越多。这会影响内存使用量（每个存储桶 8 个字节）和表扫描的性能，因为每个存储桶作为表扫描的一部分进行扫描。
存储桶计数越低，分配给单个存储桶的值越多。这降低了点查找和插入的性能，因为 SQL Server 可能需要遍历单个存储桶中的多个值来查找搜索谓词指定的值。

如果存储桶计数明显低于唯一索引键的数量，则许多值将映射到每个存储桶。这会降低大多数 DML 操作的性能，尤其是点查询（单个索引键的查找）和插入操作。例如，你可能会看到 SELECT 查询的性能不佳，以及在 WHERE 子句中与索引键列匹配的相等谓词的 UPDATE 和 DELETE 操作性能不佳。低存储桶计数也会影响数据库的恢复时间，因为数据库启动时会重新创建索引。

重复索引键值

重复值可以提高哈希冲突的性能影响。如果每个索引键具有少量重复项，这通常不是问题。但是，如果唯一索引键数与表中行数的差异变得非常大，这可能会成为一个问题。

具有相同索引键的所有行都将进入同一重复链。如果多个索引键由于哈希冲突而位于同一存储桶中，索引扫描程序始终需要扫描第一个值的完整重复链，然后才能找到对应于第二个值的第一行。重复键也使垃圾收集更难定位行。例如，如果某个键有 1,000 个重复项，并且删除了其中一行，垃圾回收器需要扫描这些重复项的链条，以解除索引中的行链接。即使找到删除的查询使用更高效的索引（主键索引）查找行，这种情况依然如此，因为垃圾回收器需要从每个索引解除链接。

对于哈希索引，可通过两种方法减少由重复索引键值引起的工作：

请改用非聚集索引。可以通过将列添加到索引键来减少重复项，而无需对应用程序进行任何更改。
为索引指定非常高的桶数量。例如，唯一索引键数的 20 到 100 倍。这将减少哈希冲突。

小型表

对于较小的表，内存利用通常不用担心，因为与数据库的总体大小相比，索引的大小会较小。

现在必须根据所需的性能类型做出选择：

如果索引上的性能关键操作主要是点查找和/或插入操作，那么提高桶数量会降低哈希冲突的可能性。行数增加到三倍甚至更多是最好的选择。
如果完整索引扫描是性能关键操作，请使用接近实际索引键值数量的桶数。

大型表格

对于大型表，内存利用率可能会成为一个值得关注的问题。例如，对于包含 4 个哈希索引的 2.5 亿行表，每个哈希表的存储桶计数为 10 亿，哈希表的开销为 4 个索引 * 10 亿桶 * 8 个字节 = 32 GB 的内存利用率。选择每个索引项的桶数量为2.5亿个时，哈希表的总开销将为8千兆字节。请注意，在此方案中，每个索引都会为每一行增加 8 个字节的内存使用量，总共为 8 GB（4 个索引 * 8 个字节 * 2.5 亿行）。

完整表扫描通常不在 OLTP 工作负荷的性能关键路径中。因此，选择在于内存利用率与点查找和插入操作性能之间。

如果内存利用率是一个关注点，请选择接近索引键值数量的桶数量。存储桶计数不应明显低于索引键值的数量，因为这会影响大多数 DML操作，并且会增加服务器重启后恢复数据库所需的时间。
优化点查找性能时，适当地将桶数提高到唯一索引值数量的两倍甚至三倍。更高的存储桶计数意味着内存使用率增加，同时完整索引扫描所需的时间也增加。

另请参阅

Memory-Optimized 表上的索引

通过