查看: 1050|回复: 0

[SQLServer] SQL Server 有序GUID,SequentialGuid,

发表于 2018-4-27 08:00:05

问题描述


有序的GUID性能对比,堪比自增ID integer

一个大神告诉我NEWSEQUENTIALID() 在数据迁移的时候会有问题(感谢大神指点),所以我就深挖一下这个函数。

    关于NEWSEQUENTIALID() 的用法 参照 NEWSEQUENTIALID()

NEWSEQUENTIALID 是对 Windows UuidCreateSequential 函数的包装。

https://msdn.microsoft.com/zh-cn/library/ms189786(v=sql.120).aspx

我们系统中对UuidCreateSequential 方法的扩展是这样写的,代码如下:

  1. public static class GuidExtension
  2. {
  3. [DllImport("rpcrt4.dll", SetLastError = true)]
  4. public static extern int UuidCreateSequential(out Guid guid);
  5. private const int RPC_S_OK = 0;
  6. public static Guid CreateRpcrt4Guid()
  7. {
  8. Guid guid;
  9. int result = UuidCreateSequential(out guid);
  10. if (result == RPC_S_OK)
  11. {
  12. byte[] guidBytes = guid.ToByteArray();
  13. Array.Reverse(guidBytes, 0, 4);
  14. Array.Reverse(guidBytes, 4, 2);
  15. Array.Reverse(guidBytes, 6, 2);
  16. return new Guid(guidBytes);
  17. }
  18. else
  19. return Guid.NewGuid();
  20. }
  21. }
复制代码

  有以下几个缺点:

  1、暴漏MAC地址:NEWSEQUENTIALID函数最后6个字符是网卡的MAC地址

  可以执行看一下

  1. create table #t
  2. (
  3. id uniqueidentifier not null default newsequentialid()
  4. ,name varchar(100)
  5. )
  6. go
  7. insert into #t(name)
  8. output inserted.id
  9. values('a')
复制代码

  2、如果进行数据迁移,到另一台机器上,MAC地址改变就会引起页的争用。

    因为GUID在的SQL Server的值大小的比对是这样的:

  1. with uids as (
  2. select id = 1, uuid = cast ('00000000-0000-0000-0000-010000000000' as uniqueidentifier)
  3. union select id = 2, uuid = cast ('00000000-0000-0000-0000-000100000000' as uniqueidentifier)
  4. union select id = 3, uuid = cast ('00000000-0000-0000-0000-000001000000' as uniqueidentifier)
  5. union select id = 4, uuid = cast ('00000000-0000-0000-0000-000000010000' as uniqueidentifier)
  6. union select id = 5, uuid = cast ('00000000-0000-0000-0000-000000000100' as uniqueidentifier)
  7. union select id = 6, uuid = cast ('00000000-0000-0000-0000-000000000001' as uniqueidentifier)
  8. union select id = 7, uuid = cast ('00000000-0000-0000-0100-000000000000' as uniqueidentifier)
  9. union select id = 8, uuid = cast ('00000000-0000-0000-0010-000000000000' as uniqueidentifier)
  10. union select id = 9, uuid = cast ('00000000-0000-0001-0000-000000000000' as uniqueidentifier)
  11. union select id = 10, uuid = cast ('00000000-0000-0100-0000-000000000000' as uniqueidentifier)
  12. union select id = 11, uuid = cast ('00000000-0001-0000-0000-000000000000' as uniqueidentifier)
  13. union select id = 12, uuid = cast ('00000000-0100-0000-0000-000000000000' as uniqueidentifier)
  14. union select id = 13, uuid = cast ('00000001-0000-0000-0000-000000000000' as uniqueidentifier)
  15. union select id = 14, uuid = cast ('00000100-0000-0000-0000-000000000000' as uniqueidentifier)
  16. union select id = 15, uuid = cast ('00010000-0000-0000-0000-000000000000' as uniqueidentifier)
  17. union select id = 16, uuid = cast ('01000000-0000-0000-0000-000000000000' as uniqueidentifier)
  18. )
  19. select * from uids order by uuid desc
复制代码

输出结果:

  类似 汉字的三点水偏旁(为了好记)


从这里可以看出,MAC地址对GUID的大小有这最高的决定性,这就导致在数据迁移的时候出问题。

COMB解决方案

COMB 类型的GUID 基本设计思路是这样的:既然GUID数据生成是随机的造成索引效率低下,影响了系统的性能,那么能不能通过组合的方式,保留GUID的前10个字节,用后6个字节表示GUID生成的时间(DateTime),这样我们将时间信息与GUID组合起来,在保留GUID的唯一性的同时增加了有序性,以此来提高索引效率。

前十个字节是通过随机数生成

  1. private static readonly RNGCryptoServiceProvider RandomGenerator = new RNGCryptoServiceProvider();
  2. byte[] randomBytes = new byte[10];
  3. RandomGenerator.GetBytes(randomBytes);
复制代码

后六个字节用时间生成

  1. long timestamp = DateTime.UtcNow.Ticks / 10000L;
  2. byte[] timestampBytes = BitConverter.GetBytes(timestamp);
  3. if (BitConverter.IsLittleEndian)
  4. {
  5. Array.Reverse(timestampBytes);
  6. }
复制代码

最后组合起来

  1. byte[] guidBytes = new byte[16];
  2. Buffer.BlockCopy(randomBytes, 0, guidBytes, 0, 10);
  3. Buffer.BlockCopy(timestampBytes, 2, guidBytes, 10, 6);
  4. return new Guid(guidBytes);
复制代码

这个解决方法是被大家所认可的,唯一感觉不好的地方是,在快速获取很多的GUID的时候,时间是一样的,加上随机生成的数据,这一组数据是大小不一的。假如数据库里有很多数据,这一组数据肯定比他们都大,性能应该没有问题。

github地址:

https://github.com/nhibernate/nhibernate-core/blob/master/src/NHibernate/Id/GuidCombGenerator.cs#L25-L72

https://github.com/jhtodd/SequentialGuid/

我的解决方法


总结上面的方法,UuidCreateSequential 前面10个字节有序,后6个是MAC地址。COMBO解决方案是前面10个随机,后六个是时间。我是将这两个结合起来

前10个去UuidCreateSequential 方法的值,后6个取时间

代码:

  1. public static Guid NewSequentialGuid()
  2. {
  3. const int RPC_S_OK = 0;
  4. Guid guid;
  5. int result = UuidCreateSequential(out guid);
  6. if (result != RPC_S_OK)
  7. {
  8. throw new System.ComponentModel.Win32Exception(System.Runtime.InteropServices.Marshal.GetLastWin32Error());
  9. }
  10. else
  11. {<br>       //这里把UuidCreateSequential函数返回的数据做处理
  12. byte[] guidBytes = guid.ToByteArray();
  13. Array.Reverse(guidBytes, 0, 4);
  14. Array.Reverse(guidBytes, 4, 2);
  15. Array.Reverse(guidBytes, 6, 2);
  16. <br>       //这里用时间
  17. long timestamp = DateTime.UtcNow.Ticks / 10000L;
  18. byte[] timestampBytes = BitConverter.GetBytes(timestamp);
  19. if (BitConverter.IsLittleEndian)
  20. {
  21. Array.Reverse(timestampBytes);
  22. }<br>       //最后把时间赋值给后6位
  23. Buffer.BlockCopy(timestampBytes, 2, guidBytes, 10, 6);
  24. return new Guid(guidBytes);
  25. }
  26. }
  27. [System.Runtime.InteropServices.DllImport("rpcrt4.dll", SetLastError = true)]
  28. private static extern int UuidCreateSequential(out Guid guid);
复制代码

这里可以在程序调用,作为DBA在数据库使用的话可以将这个方法添加到程序集里,需要有些改动

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Data.SqlTypes;
  6. public class FunctionNewGuid
  7. {<br>  //这里需要添加SqlFunction属性<br>  //返回类型是数据库类型<br>
  8. [Microsoft.SqlServer.Server.SqlFunction]
  9. public static SqlGuid NewSequentialGuid()
  10. {
  11. const int RPC_S_OK = 0;
  12. Guid guid;
  13. int result = UuidCreateSequential(out guid);
  14. if (result != RPC_S_OK)
  15. {
  16. throw new System.ComponentModel.Win32Exception(System.Runtime.InteropServices.Marshal.GetLastWin32Error());
  17. }
  18. else
  19. {
  20. byte[] guidBytes = guid.ToByteArray();
  21. Array.Reverse(guidBytes, 0, 4);
  22. Array.Reverse(guidBytes, 4, 2);
  23. Array.Reverse(guidBytes, 6, 2);
  24. long timestamp = DateTime.UtcNow.Ticks / 10000L;
  25. byte[] timestampBytes = BitConverter.GetBytes(timestamp);
  26. if (BitConverter.IsLittleEndian)
  27. {
  28. Array.Reverse(timestampBytes);
  29. }
  30. Buffer.BlockCopy(timestampBytes, 2, guidBytes, 10, 6);
  31. return new SqlGuid(guidBytes);
  32. }
  33. }
  34. [System.Runtime.InteropServices.DllImport("rpcrt4.dll", SetLastError = true)]
  35. private static extern int UuidCreateSequential(out Guid guid);
  36. }
复制代码

编译生成DLL后,注册到数据库

  1. --设置数据库是可信任
  2. ALTER DATABASE TEST SET TRUSTWORTHY ON
  3. --创建程序集
  4. CREATE ASSEMBLY SQLCLR FROM 'D:\SQLCLR.DLL'
  5. WITH PERMISSION_SET = UNSAFE
  6. --用程序集方法创建函数
  7. CREATE FUNCTION func_NewSequentialGuid()
  8. RETURNS uniqueidentifier
  9. AS external name SQLCLR.FunctionNewGuid.NewSequentialGuid
复制代码

    

测试代码:

批量请求:


  1. select dbo.func_NewSequentialGuid()
  2. union
  3. select dbo.func_NewSequentialGuid()
  4. union
  5. select dbo.func_NewSequentialGuid()
  6. union
  7. select dbo.func_NewSequentialGuid()
  8. union
  9. select dbo.func_NewSequentialGuid()
复制代码

结果:

多次请求:


  1. create table #t
  2. (
  3. uuid uniqueidentifier
  4. ,id int identity
  5. )
  6. go
  7. insert into #t(uuid)
  8. values(dbo.func_NewSequentialGuid())
  9. go 10
  10. select * from #t
复制代码

git地址

https://gitee.com/wangzhanbo/cms/tree/master/Library

如果有问题,希望大家指正。。。



回复

使用道具 举报