SQL Server NEWSEQUENTIALID（）-澄清超快速.net核心实现 [英] SQL Server NEWSEQUENTIALID() - clarification for super fast .net core implementation
问题描述
当前，我正在尝试在.NET Core 2.2中编写SQL Server NEWSEQUENTIALID（），它应该运行得非常快，并且还应该分配尽可能少的内存，但是我需要澄清如何计算uuid版本以及何时计算（将其放置在哪个字节或需要什么移位）。因此，现在我已经生成了时间戳，检索了mac地址，并从某个基本随机生成的guid复制了字节8和9，但是肯定会丢失某些内容，因为结果与原始算法的输出不匹配。
Currently I'm trying to write SQL Server NEWSEQUENTIALID() in .NET Core 2.2 that should be running really fast and also it should allocate minimum possible amount memory but I need clarification how calculate uuid version and when (which byte to place it or what bit shift is needed). So now I have generated timestamp, retrieved mac address and copied bytes 8 and 9 from some base random generated guid but surely I'm missing something because results doesn't match with output of original algorithm.
byte[16] guidArray;
// mac
guidArray[15] = macBytes[5];
guidArray[14] = macBytes[4];
guidArray[13] = macBytes[3];
guidArray[12] = macBytes[2];
guidArray[11] = macBytes[1];
guidArray[10] = macBytes[0];
// base guid
guidArray[9] = baseGuidBytes[9];
guidArray[8] = baseGuidBytes[8];
// time
guidArray[7] = ticksDiffBytes[0];
guidArray[6] = ticksDiffBytes[1];
guidArray[5] = ticksDiffBytes[2];
guidArray[4] = ticksDiffBytes[3];
guidArray[3] = ticksDiffBytes[4];
guidArray[2] = ticksDiffBytes[5];
guidArray[1] = ticksDiffBytes[6];
guidArray[0] = ticksDiffBytes[7];
var guid = new Guid(guidArray);
当前基准测试结果：
Method | Mean | Error | StdDev | Ratio | RatioSD | Gen 0 | Gen 1 | Gen 2 | Allocated |
|--------------------------- |----------:|---------:|---------:|------:|--------:|-------:|------:|------:|----------:|
| SqlServerNewSequentialGuid | 37.31 ns | 0.680 ns | 0.636 ns | 1.00 | 0.00 | 0.0127 | - | - | 80 B |
| Guid_Standard | 63.29 ns | 0.435 ns | 0.386 ns | 1.70 | 0.03 | - | - | - | - |
| Guid_Comb | 299.57 ns | 2.902 ns | 2.715 ns | 8.03 | 0.13 | 0.0162 | - | - | 104 B |
| Guid_Comb_New | 266.92 ns | 3.173 ns | 2.813 ns | 7.16 | 0.11 | 0.0162 | - | - | 104 B |
| MyFastGuid | 70.08 ns | 1.011 ns | 0.946 ns | 1.88 | 0.05 | 0.0050 | - | - | 32 B |
更新：
这是基准测试的最新结果用.net核心编写的id生成器。
Update: Here are the latest results of benchmarking common id generators written in .net core.
如您所见，我的实现NewSequentialGuid_PureNetCore的性能最高是最糟糕的2倍，然后是rpcrt4.dll的包装器（是我的基准），但我的实现消耗的内存更少（30B）。
As u can see my implementation NewSequentialGuid_PureNetCore is at most 2x worst performing then wrapper around rpcrt4.dll (which was my baseline) but me implementation eats less memory (30B).
以下是示例前10个向导的序列：
Here are a sequence of sample first 10 guids:
492bea01-456f-3166-0001-e0d55e8cb96a < br>
492bea01-456f-37a5-0002-e0d55e8cb96a
492bea01-456f-aca5-0003-e0d55e8cb96a
492bea01-456f-bba5-0004-e0d55e8cb96a
492bea01-456f-c5a5-0005-e0d55e8cb96a
492bea01-456f-cea5-0006-e0d55e8cb96a
492bea01-456f-d7a5-0007-e0d55e8cb96a
$ b 492bea01-456f-dfa5-0008-e0d55e8cb96a
492bea01-456f-e8a5-0009-e0d55e8cb96a
492bea01-456f-f1a5-000a-e0d55e8cbb96a
492bea01-456f-3166-0001-e0d55e8cb96a
492bea01-456f-37a5-0002-e0d55e8cb96a
492bea01-456f-aca5-0003-e0d55e8cb96a
492bea01-456f-bba5-0004-e0d55e8cb96a
492bea01-456f-c5a5-0005-e0d55e8cb96a
492bea01-456f-cea5-0006-e0d55e8cb96a
492bea01-456f-d7a5-0007-e0d55e8cb96a
492bea01-456f-dfa5-0008-e0d55e8cb96a
492bea01-456f-e8a5-0009-e0d55e8cb96a
492bea01-456f-f1a5-000a-e0d55e8cb96a
如果您想要代码，请给我一个符号;）
If u want code then give me a sign ;)
推荐答案
官方文档非常清楚地说明了这一点：
The official documentation states it quite clearly:
NEWSEQUENTIALID是Windows UuidCreateSequential
函数的包装，并应用了一些字节改组。
NEWSEQUENTIALID is a wrapper over the Windows UuidCreateSequential function, with some byte shuffling applied.
引用的段落中也有一些您可能感兴趣的链接。但是，考虑到原始代码是用C / C ++编写的，我某种程度上怀疑.NET是否能胜过它，因此，重用相同的方法可能是一个更为谨慎的选择（即使它涉及非托管调用）。
There are also links in the quoted paragraph which might be of interest for you. However, considering that the original code is written in C / C++, I somehow doubt that .NET can outperform it, so reusing the same approach might be a more prudent choice (even though it would involve unmanaged calls).
我已经很真诚地希望您在决定采用这种方法之前已经研究了该功能的行为并考虑了其所有副作用。我当然希望您不要将此输出用作表的聚集索引。在文档中也提到了这个原因（作为警告，同样如此）：
Having said that, I sincerely hope that you have researched the behaviour of this function and considered all its side effects before deciding to pursue this approach. And I certainly hope you aren't going to use this output as a clustered index for your table(s). The reason for this is also mentioned in the docs (as a warning, no less):
UuidCreateSequential函数具有硬件依赖性。在SQL
服务器上，将数据库（例如
包含的数据库）移至其他计算机时，可以形成顺序值的群集。在数据库上使用
Always On并在SQL数据库上使用时，如果数据库故障转移到另一台计算机，则可能会生成连续值群集。
The UuidCreateSequential function has hardware dependencies. On SQL Server, clusters of sequential values can develop when databases (such as contained databases) are moved to other computers. When using Always On and on SQL Database, clusters of sequential values can develop if the database fails over to a different computer.
基本上，该函数仅在数据库位于同一宿主环境中时才生成单调序列。何时：
Basically, the function generates a monotonous sequence only while the database is in the same hosting environment. When:
- 网卡在裸机上（或功能依赖的其他参数）被更换，或者
- 将备份还原到其他位置（请考虑按产品到设备的刷新，或者只是进行产品迁移/升级），或者
- 发生故障转移，无论是在群集中还是在在AlwaysOn配置
中，新的SQL Server实例将具有自己的生成值范围，该范围不应与其他计算机上的其他实例。如果该新范围位于现有值之前，那么您将毫无理由地面临碎片问题。哦， top（1）来获取最新值不再有效。
, the new SQL Server instance will have its own range of generated values, which is supposed not to overlap the ranges of other instances on other machines. If that new range comes "before" the existing values, you'll end up with fragmentation issues for absolutely no good reason. Oh, and top (1) to get the latest value won't work anymore.
实际上，如果有的话您需要的是一个非穷尽的单调序列，请遵循Greg Low的建议并坚持使用bigint。它的宽度是它的一半，不，您不可能用尽它。
Indeed, if all you need is a non-exhaustible monotonous sequence, follow the Greg Low's advice and just stick to bigint. It's half as wide, and no, you can't possibly exhaust it.
这篇关于SQL Server NEWSEQUENTIALID（）-澄清超快速.net核心实现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
