在Hekaton里，正确选择哈希存储桶数

今天我使用2048的桶数的哈希索引，往Hakaton里插入100万的记录，测试下在哈希桶数里，哈希冲突（Hash Collision）是如何影响Hekaton的工作量——结果非常非常有意思。首先我想介绍下什么是哈希冲突。

你可能知道（非常希望），在SQL Server 2014里，Hakaton表是以哈希索引（Hash Indexes）实现的。*对此有详细介绍，这是哈希索引的应用基础。 哈希函数将索引键映射到哈希索引中对应的 Bucket，哈希函数的结果决定你的行最终放入那个哈希桶。如果多个键值哈希到同个值，SQL Server会在那个哈希桶里插入，在那个哈希桶有多个入口链接在一起。来看下面的图示（来自*）：

从图中可以看到，键值“John Smith“和“Sandra Dee”哈希到同个桶——这里是152号桶。这意味着那2行都存在同个哈希桶里，这会影响INSERT性能，还有SELECT的查询性能。在INSERT期间，SQL Server需要维护链接列表，在SELECT查询期间，SQL Server需要扫描链接列表。

介绍完哈希冲突后，我们来用一个简单的例子演示下哈希冲突对性能的影响。我们来创建带Hekaton表的数据库：

 -- Create new database
 CREATE DATABASE HashCollisions
 GO

 --Add MEMORY_OPTIMIZED_DATA filegroup to the database.
 ALTER DATABASE HashCollisions
 ADD FILEGROUP HekatonFileGroup CONTAINS MEMORY_OPTIMIZED_DATA
 GO

 USE HashCollisions
 GO

 -- Add a new file to the previous created file group
 ALTER DATABASE HashCollisions ADD FILE
 (
     NAME = N'HekatonContainer',
     FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\HashCollisionsContainer'
 )
 TO FILEGROUP [HekatonFileGroup]
 GO

 -- Create a simple table
 CREATE TABLE TestTable
 (
     Col1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
     Col2 INT NOT NULL,
     Col3 INT NOT NULL
 )
 WITH
 (
     MEMORY_OPTIMIZED = ON,
     DURABILITY = SCHEMA_ONLY
 )
 GO

从代码里可以看到，这里我用的1024的哈希桶数——桶数并不多，然后我会往表里插入1000000的记录。接下来我会创建本机编译的存储过程，这样的话我可以用Hekaton的贼快速度：

 -- Create a native compiled Stored Procedure
 CREATE PROCEDURE InsertTestData
 WITH
     NATIVE_COMPILATION,
     SCHEMABINDING,
     EXECUTE AS OWNER
 AS
 BEGIN
     ATOMIC WITH
     (
         TRANSACTION
         ISOLATION LEVEL = SNAPSHOT,
         LANGUAGE = N'us_english'
     )

     DECLARE @i INT = 0

     WHILE @i < 1000000
     BEGIN
         INSERT INTO dbo.TestTable (Col1, Col2, Col3) VALUES (@i,@i, @i)

         SET @i += 1
     END
 END
 GO

可以看到，这里我用简单的循环来插入1000000条记录。在4核CPU，4G内存的虚拟机上，我们打开时间统计，来执行这个存储过程：

 SET STATISTICS TIME ON

 EXEC dbo.InsertTestData

执行时间差不多有42秒，这已经很慢了。我们不断翻倍桶数到1048576，你会看到随着桶数的增加，性能也得到了不断的提升。

 DROP PROCEDURE dbo.InsertTestData
 DROP TABLE dbo.TestTable

 -- Create a simple table
 CREATE TABLE TestTable
 (
     Col1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1048576),
     Col2 INT NOT NULL,
     Col3 INT NOT NULL
 )
 WITH
 (
     MEMORY_OPTIMIZED = ON,
     DURABILITY = SCHEMA_ONLY
 )
 GO

 -- Create a native compiled Stored Procedure
 CREATE PROCEDURE InsertTestData
 WITH
     NATIVE_COMPILATION,
     SCHEMABINDING,
     EXECUTE AS OWNER
 AS
 BEGIN
     ATOMIC WITH
     (
         TRANSACTION
         ISOLATION LEVEL = SNAPSHOT,
         LANGUAGE = N'us_english'
     )

     DECLARE @i INT = 0

     WHILE @i < 1000000
     BEGIN
         INSERT INTO dbo.TestTable (Col1, Col2, Col3) VALUES (@i,@i, @i)

         SET @i += 1
     END
 END
 GO

我们继续执行这个存储过程：

 SET STATISTICS TIME ON

 EXEC dbo.InsertTestData

执行同个存储过程只需要780毫秒，与第一次用1024个桶数的测试运行，这已经是天大的区别。你也可以用DMV sys.dm_db_xtp_hash_index_stats来看下在你的哈希索引里有几桶被使用：

 SELECT * FROM sys.dm_db_xtp_hash_index_stats

这个测试告诉我们什么呢？要为Hekaton的哈希索引的存储桶数，要做出正确的选择，因为它们会大大影响SQL Server的性能！最佳桶数应该是在哈希索引里不同值的个数——另外要保留一些可用空间（在不同值个数上稍加），安全起见。你也不能把选择太高的存储桶数，因为相反你就在浪费内存。在SQL Server里的几乎每个设置——都是基于你的工作量的而定，数据库收缩除外哦！

感谢关注！

参考文章：

https://www.sqlpassion.at/archive/2013/11/23/choose-your-hash-bucket-count-very-wisely-in-hekaton/