[MSSQL]SQL SERVER 有序 GUID 和无序 GUID 作为主键聚集索引测试

小渣渣

需求：在使用 GUID 作为主键聚集索引的时候，官方建议使用有序的 GUID，因为无序 GUID 会造成空间的浪费以及由此带来的读写效率的下降，存储的碎片化带来的读写效率严重下降。

回顾：

SQL Server 性能优化之索引碎片
https://www.itsvse.com/thread-9508-1-1.html

【转】(MSSQL)SQL Server 数据库 int 和 guid 做主键比较
https://www.itsvse.com/thread-10605-1-1.html

数据库 GUID 值

SQL Server 中的全局唯一标识符 (GUID) 数据类型由数据类型表示uniqueidentifier，它存储一个 16 字节的二进制值。GUID 是一个二进制数，它的主要用途是作为一个标识符，该标识符在许多站点上有许多计算机的网络中必须是唯一的。

Guid和SqlGuid都有比较不同 GUID 值的方法。SqlGuid 实现使用 SQL Server 行为，值的最后六个字节是最重要的。

顺序 GUID 本质上是可以猜测的，所以不要在安全敏感的上下文中使用它们。

参考：https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/comparing-guid-and-uniqueidentifier-values

数据库有序 GUID

SQL Server数据库有一个 NewSequentialId() 函数，用于创建有序GUID。在创建表时，可以将它设置成为GUID类型字段的默认值，在插入新增数据时自动创建主键的值（该函数只能做为字段的默认值，不能直接在SQL中调用）。

示例：

登录可见。

NewSequentialId() 函数只能在数据库使用，不过在 Microsoft 的 MSDN 文档中有说明，NEWSEQUENTIALID 是对 Windows UuidCreateSequential 函数的包装。

提示：如果涉及保密问题，则不要使用该函数。 因为有可能猜到下一个生成的 GUID 的值，从而访问与该 GUID 关联的数据。

参考：https://learn.microsoft.com/zh-cn/previous-versions/sql/sql-server-2014/ms189786(v=sql.120)?redirectedfrom=MSDN
https://learn.microsoft.com/zh-cn/sql/t-sql/functions/newsequentialid-transact-sql?view=sql-server-ver16

.NET 创建有序 GUID

UuidCreateSequential 函数依赖的计算硬件，该方法的后12位其实是网卡的MAC地址。

代码如下：

登录可见。

结果如下：



缺点：

• 使用该方式需要 DllImport 调用 Windows 类库，所以无法跨平台。
• 不能在集群环境中使用，多台机器写入同一个数据库，因为生成的 GUID 将彼此不一样（函数依赖的计算硬件），从而导致索引碎片。
• 如果您的 Windows 服务器重新启动，GUID 可能会从较低的范围开始，从而导致索引碎片。
  

参考：

https://learn.microsoft.com/zh-cn/windows/win32/api/rpcdce/nf-rpcdce-uuidcreatesequential
https://learn.microsoft.com/zh-cn/archive/blogs/dbrowne/how-to-generate-sequential-guids-for-sql-server-in-net

如果使用 .NET 程序创建 SQL SERVER 有序 GUID，建议使用一个第三方类库：RT.Comb，nuget 命令如下：

登录可见。

代码如下：

登录可见。

输出结果：



参考：https://davecallan.com/generating-sequential-guids-which-sort-sql-server-in-net/

有序 GUID 和无序 GUID 主键索引碎片

首先创建两个表，脚本如下：

登录可见。

测试插入 10 万条数据，代码如下：

登录可见。

测试结果如下：

GUID	耗时（ms）	扫描密度 [最佳计数:实际计数](Scan Density [Best Count: Actual Count]),数值越高越好	逻辑扫描碎片（Logical Scan Fragmentation）,数值越低越好	平均页密度(满)（Avg. Page density (full)）,数值越高越好
无序 GUID	13367	12.58% [61:485]	98.97%	63.66%
顺序 GUID	14139	100.00% [39:39]	0.32%	99.61%

登录可见。

测试再次插入 100万条数据，无序 GUID 耗时：135203 毫秒，有序 GUID 耗时：135134 毫秒。存储空间占用如下：

无序 GUID：36.547 MB
有序 GUID: 26.609 MB



参考：https://learn.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-showcontig-transact-sql?view=sql-server-ver16


（完）

小渣渣

ABP 创建 sql server 数据库的有序GUID，源码如下：

登录可见。

小渣渣

EF Core 生成有序 GUID 代码：

登录可见。

https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.valuegeneration.sequentialguidvaluegenerator?view=efcore-8.0

小渣渣

MySQL 生成有序 GUID： https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/main/src/EFCore.MySql/ValueGeneration/Internal/MySqlSequentialGuidValueGenerator.cs

登录可见。