欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL Server 有序GUID,SequentialGuid,

程序员文章站 2024-02-05 14:12:16
问题描述 有序的GUID性能对比,堪比自增ID integer 一个大神告诉我NEWSEQUENTIALID() 在数据迁移的时候会有问题(感谢大神指点),所以我就深挖一下这个函数。 关于NEWSEQUENTIALID() 的用法 参照 NEWSEQUENTIALID() NEWSEQUENTIAL ......

问题描述


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

SQL Server 有序GUID,SequentialGuid,

 

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

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

NEWSEQUENTIALID 是对 Windows UuidCreateSequential 函数的包装。

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

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

 

    public static class GuidExtension
    {
        [DllImport("rpcrt4.dll", SetLastError = true)]
        public static extern int UuidCreateSequential(out Guid guid);
        private const int RPC_S_OK = 0;

        public static Guid CreateRpcrt4Guid()
        {
            Guid guid;
            int result = UuidCreateSequential(out guid);
            if (result == RPC_S_OK)
            {
                byte[] guidBytes = guid.ToByteArray();
                Array.Reverse(guidBytes, 0, 4);
                Array.Reverse(guidBytes, 4, 2);
                Array.Reverse(guidBytes, 6, 2);

                return new Guid(guidBytes);
            }
            else
                return Guid.NewGuid();
        }

    }

 

  有以下几个缺点:

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

  可以执行看一下

create table #t
(
    id uniqueidentifier not null  default newsequentialid()
    ,name varchar(100)
)
go

insert into #t(name)
output inserted.id
values('a')
  

 

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

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

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

 

输出结果:

SQL Server 有序GUID,SequentialGuid,  类似 汉字的三点水偏旁(为了好记)


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

 

COMB解决方案


 

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

 

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

private static readonly RNGCryptoServiceProvider RandomGenerator = new RNGCryptoServiceProvider();

      byte[] randomBytes = new byte[10];
      RandomGenerator.GetBytes(randomBytes);

 

 

后六个字节用时间生成

      long timestamp = DateTime.UtcNow.Ticks / 10000L;
      byte[] timestampBytes = BitConverter.GetBytes(timestamp);

      if (BitConverter.IsLittleEndian)
      {
        Array.Reverse(timestampBytes);
      }

 

 

最后组合起来

    byte[] guidBytes = new byte[16];
    Buffer.BlockCopy(randomBytes, 0, guidBytes, 0, 10);
    Buffer.BlockCopy(timestampBytes, 2, guidBytes, 10, 6); 

    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个取时间

代码:

public static Guid NewSequentialGuid()
    {
        const int RPC_S_OK = 0;
        Guid guid;
        int result = UuidCreateSequential(out  guid);

        if (result != RPC_S_OK)
        {
            throw new System.ComponentModel.Win32Exception(System.Runtime.InteropServices.Marshal.GetLastWin32Error());
        }
        else
        {
       //这里把UuidCreateSequential函数返回的数据做处理 byte[] guidBytes = guid.ToByteArray(); Array.Reverse(guidBytes, 0, 4); Array.Reverse(guidBytes, 4, 2); Array.Reverse(guidBytes, 6, 2);
       //这里用时间 long timestamp = DateTime.UtcNow.Ticks / 10000L; byte[] timestampBytes = BitConverter.GetBytes(timestamp); if (BitConverter.IsLittleEndian) { Array.Reverse(timestampBytes); }
       //最后把时间赋值给后6位 Buffer.BlockCopy(timestampBytes, 2, guidBytes, 10, 6); return new Guid(guidBytes); } } [System.Runtime.InteropServices.DllImport("rpcrt4.dll", SetLastError = true)] private static extern int UuidCreateSequential(out Guid guid);

 

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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlTypes;

public class FunctionNewGuid
{
  //这里需要添加SqlFunction属性
  //返回类型是数据库类型
[Microsoft.SqlServer.Server.SqlFunction] public static SqlGuid NewSequentialGuid() { const int RPC_S_OK = 0; Guid guid; int result = UuidCreateSequential(out guid); if (result != RPC_S_OK) { throw new System.ComponentModel.Win32Exception(System.Runtime.InteropServices.Marshal.GetLastWin32Error()); } else { byte[] guidBytes = guid.ToByteArray(); Array.Reverse(guidBytes, 0, 4); Array.Reverse(guidBytes, 4, 2); Array.Reverse(guidBytes, 6, 2); long timestamp = DateTime.UtcNow.Ticks / 10000L; byte[] timestampBytes = BitConverter.GetBytes(timestamp); if (BitConverter.IsLittleEndian) { Array.Reverse(timestampBytes); } Buffer.BlockCopy(timestampBytes, 2, guidBytes, 10, 6); return new SqlGuid(guidBytes); } } [System.Runtime.InteropServices.DllImport("rpcrt4.dll", SetLastError = true)] private static extern int UuidCreateSequential(out Guid guid); }

 

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

--设置数据库是可信任
 ALTER DATABASE TEST SET TRUSTWORTHY ON

 --创建程序集
 CREATE ASSEMBLY SQLCLR FROM 'D:\SQLCLR.DLL'
 WITH PERMISSION_SET = UNSAFE


 --用程序集方法创建函数
 CREATE FUNCTION func_NewSequentialGuid()
    RETURNS uniqueidentifier
AS external name SQLCLR.FunctionNewGuid.NewSequentialGuid

 

    

测试代码:

 批量请求:


 

select dbo.func_NewSequentialGuid() 
union
select dbo.func_NewSequentialGuid() 
union
select dbo.func_NewSequentialGuid() 
union
select dbo.func_NewSequentialGuid() 
union
select dbo.func_NewSequentialGuid() 

 结果:

 SQL Server 有序GUID,SequentialGuid,

 

 多次请求:


 

create table #t
(
    uuid uniqueidentifier 
    ,id int identity
)
go

insert into #t(uuid)
values(dbo.func_NewSequentialGuid())
go 10

select * from #t

 

 SQL Server 有序GUID,SequentialGuid,

 

git地址

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

 

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