问题描述
有序的GUID性能对比,堪比自增ID integer

一个大神告诉我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
复制代码
输出结果:
类似 汉字的三点水偏旁(为了好记)
从这里可以看出,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
- {<br> //这里把UuidCreateSequential函数返回的数据做处理
- byte[] guidBytes = guid.ToByteArray();
- Array.Reverse(guidBytes, 0, 4);
- Array.Reverse(guidBytes, 4, 2);
- Array.Reverse(guidBytes, 6, 2);
- <br> //这里用时间
- long timestamp = DateTime.UtcNow.Ticks / 10000L;
- byte[] timestampBytes = BitConverter.GetBytes(timestamp);
- if (BitConverter.IsLittleEndian)
- {
- Array.Reverse(timestampBytes);
- }<br> //最后把时间赋值给后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
- {<br> //这里需要添加SqlFunction属性<br> //返回类型是数据库类型<br>
- [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()
复制代码
结果:

多次请求:
- create table #t
- (
- uuid uniqueidentifier
- ,id int identity
- )
- go
- insert into #t(uuid)
- values(dbo.func_NewSequentialGuid())
- go 10
- select * from #t
复制代码

git地址
https://gitee.com/wangzhanbo/cms/tree/master/Library
如果有问题,希望大家指正。。。
|