集索引确定表中数据的物理顺序,查询效率低

作者: www.9159.com  发布:2019-11-05

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

GUID在.Net中的排序规则

在.Net中,GUID默认的排序过段规则是按左到右的,看下面这个示例。

    var list = new List<Guid> {
        new Guid("00000000-0000-0000-0000-010000000000"),
        new Guid("00000000-0000-0000-0000-000100000000"),
        new Guid("00000000-0000-0000-0000-000001000000"),
        new Guid("00000000-0000-0000-0000-000000010000"),
        new Guid("00000000-0000-0000-0000-000000000100"),
        new Guid("00000000-0000-0000-0000-000000000001"),
        new Guid("00000000-0000-0000-0100-000000000000"),
        new Guid("00000000-0000-0000-0010-000000000000"),
        new Guid("00000000-0000-0001-0000-000000000000"),
        new Guid("00000000-0000-0100-0000-000000000000"),
        new Guid("00000000-0001-0000-0000-000000000000"),
        new Guid("00000000-0100-0000-0000-000000000000"),
        new Guid("00000001-0000-0000-0000-000000000000"),
        new Guid("00000100-0000-0000-0000-000000000000"),
        new Guid("00010000-0000-0000-0000-000000000000"),
        new Guid("01000000-0000-0000-0000-000000000000")
    };
    list.Sort();

    foreach (Guid guid in list) {
        Console.WriteLine(guid.ToString());
    }

输出结果:

00000000-0000-0000-0000-000000000001
00000000-0000-0000-0000-000000000100
00000000-0000-0000-0000-000000010000
00000000-0000-0000-0000-000001000000
00000000-0000-0000-0000-000100000000
00000000-0000-0000-0000-010000000000
00000000-0000-0000-0010-000000000000
00000000-0000-0000-0100-000000000000
00000000-0000-0001-0000-000000000000
00000000-0000-0100-0000-000000000000
00000000-0001-0000-0000-000000000000
00000000-0100-0000-0000-000000000000
00000001-0000-0000-0000-000000000000
00000100-0000-0000-0000-000000000000
00010000-0000-0000-0000-000000000000
01000000-0000-0000-0000-000000000000

通过上面的输出结果,我们可以得到排序的权重如下:

序号 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
权重 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
Value 11 11 11 11 - 22 22 - 33 33 - 44 44 - 44 44 44 44 44 44

这与数字排序规则一致,从右到左进行依次进行排序(数字越小,权重越高,排序的优先级越高)。

缺点:

  1. 在单机上是递增的,但是由于涉及到分布式环境,每台机器上的时钟不可能完全同步,也许有时候也会出现不是全局递增的情况。

 

UuidCreateSequential函数

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

示例:

Create Table TestTable
       (
         ID UniqueIdentifier Not Null Default ( NewSequentialId() ) ,
         Number Int
       );

NewSequentialId()函数只能在数据库使用,不过在 Microsoft 的 MSDN 文档中有说明,NEWSEQUENTIALID 是对 Windows UuidCreateSequential 函数的包装,。这样我们可以在C#通过非托管方法调用:

   [System.Runtime.InteropServices.DllImport("rpcrt4.dll", SetLastError = true)]
   private static extern int UuidCreateSequential(out Guid guid);

   public static Guid NewSequentialGuid()
   {
       const int RPC_S_OK = 0;

       int result = UuidCreateSequential(out var guid);
       if (result != RPC_S_OK) {
           throw new System.ComponentModel.Win32Exception(System.Runtime.InteropServices.Marshal.GetLastWin32Error());
       }

       return guid;
   }

不这个方法也存在三个问题:

  1. 这个方法涉及到安全问题,UuidCreateSequential函数依赖的计算硬件,该方法的后12位其实是网卡的MAC地址。这是我电脑生成的一组有序GUID。

    {A2A93393-C8DC-11E7-B133-2C56DC497A97}
    {A2A93394-C8DC-11E7-B133-2C56DC497A97}
    {A2A93395-C8DC-11E7-B133-2C56DC497A97}
    {A2A93396-C8DC-11E7-B133-2C56DC497A97}
    {A2A93397-C8DC-11E7-B133-2C56DC497A97}
    {A2A93398-C8DC-11E7-B133-2C56DC497A97}
    {A2A93399-C8DC-11E7-B133-2C56DC497A97}
    {A2A9339Awww.9159.com ,-C8DC-11E7-B133-2C56DC497A97}
    {A2A9339B-C8DC-11E7-B133-2C56DC497A97}
    {A2A9339C-C8DC-11E7-B133-2C56DC497A97}

    这是我电脑的网卡的MAC地址:
    www.9159.com 1

  2. 由于UuidCreateSequential函数生成的有序GUID中包括MAC地址,所以如果在服务器集群环境中,肯定存在一台服务器A上生成的有序GUID总比另一台服务器B生成要更小,服务器A产生的数据插入到数据库时,由于聚集索引的问题,总是会移动服务器B已经持久化到数据库中的数据。集群的服务器越多,产生的IO问题更严重。在服务器群集环境中,需要自行实现有序GUID。

  3. UuidCreateSequential函数生成的GUID规则与SQL Server中排序的规则存在不一致,这样仍然会导致严重的IO问题,所以需要将GUID重新排序后再持久化到数据库。例如上面列出生成的GUID列表,依次生成的数据可以看出,是第4位字节在自增长,在这与任何一个数据库的排序规则都不一致;关于该函数生成的规则,可以见此链接:。

下面的方法是将生成的GUID调整成为适合Sql Server使用的有序GUID(针对其它数据库支持,您可以按排序规则自行修改):

[System.Runtime.InteropServices.DllImport("rpcrt4.dll", SetLastError = true)]
static extern int UuidCreateSequential(byte[] buffer);

static Guid NewSequentialGuid() {

    byte[] raw = new byte[16];
    if (UuidCreateSequential(raw) != 0)
        throw new System.ComponentModel.Win32Exception(System.Runtime.InteropServices.Marshal.GetLastWin32Error());

    byte[] fix = new byte[16];

    // reverse 0..3
    fix[0x0] = raw[0x3];
    fix[0x1] = raw[0x2];
    fix[0x2] = raw[0x1];
    fix[0x3] = raw[0x0];

    // reverse 4 & 5
    fix[0x4] = raw[0x5];
    fix[0x5] = raw[0x4];

    // reverse 6 & 7
    fix[0x6] = raw[0x7];
    fix[0x7] = raw[0x6];

    // all other are unchanged
    fix[0x8] = raw[0x8];
    fix[0x9] = raw[0x9];
    fix[0xA] = raw[0xA];
    fix[0xB] = raw[0xB];
    fix[0xC] = raw[0xC];
    fix[0xD] = raw[0xD];
    fix[0xE] = raw[0xE];
    fix[0xF] = raw[0xF];

    return new Guid(fix);
}

小结:
UuidCreateSequential函数存在隐私的问题,不适合集群环境,并且需要重新排序后再提交到数据库;

优点:

  1. 解决UUID无序的问题,在其主键生成方式中提供了Comb算法(combined guid/timestamp)。保留GUID的10个字节,用另6个字节表示GUID生成的时间(DateTime)。
  2. 性能优于UUID。

  3. Twitter的snowflake算法


snowflake是Twitter开源的分布式ID生成算法,结果是一个long型的ID。其核心思想是:使用41bit作为毫秒数,10bit作为机器的ID(5个bit是数据中心,5个bit的机器ID),12bit作为毫秒内的流水号(意味着每个节点在每毫秒可以产生 4096 个 ID),最后还有一个符号位,永远是0。snowflake算法可以根据自身项目的需要进行一定的修改。比如估算未来的数据中心个数,每个数据中心的机器数以及统一毫秒可以能的并发数来调整在算法中所需要的bit数。

 

GUID排序规则

在讲解有序GUID之前,我们必须先了解一下GUID在.Net中以及各个数据库中的排序规则,排序规则不一样,生成有序GUID的规则也会随之变化。

128位的GUID主要有4部分组成:Data1, Data2, Data3, and Data4,你可以看成下面这样:

11111111-2222-3333-4444-444444444444

Data1 占4个字节, Data2 2个字节, Data3 2个字节加 Data4 8个字节。我们分别的对个字节编上序号:

序号 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
Value 11 11 11 11 - 22 22 - 33 33 - 44 44 - 44 44 44 44 44 44

优点:

  1. 简单,代码方便。
  2. 全球唯一,在遇见数据迁移,系统数据合并,或者数据库变更等情况下,可以从容应对。

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

总结

我们来解决一下:

  1. 在数据库中最好不要使用随机的GUID,它会影响性能;
  2. 在SQL Server中提供了NewSequentialId函数来生成有序GUID;
  3. 各个数据库对GUID支持的不一样,而且排序的规则也不一样;
  4. UuidCreateSequential函数存在隐私的问题,不适合集群环境,并且需要重新排序后再提交到数据库;
  5. 各ORM框架提供了有序GUID的支持,但是其实只是针对Sql Server数据库设计的;
  6. 推荐您使用SequentialGuid框架,它解决了多数据库以及集群环境的问题。

优点:

  1. 不依赖于数据库,灵活方便,且性能优于数据库。
  2. ID按照时间在单机上是递增的。

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

COMB解决方案

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

在NHibernate框架中已经实现该功能,可以在github上看到实现方式:。

在EF以及EF Core也同样实现了类似的解决方案,EF Core的实现方式:

在这里介绍一下使用的方式,由EF Core框架自动生成有序GUID的方式:

    public class SampleDbContext : DbContext
    {
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<GuidEntity>(b =>
            {
                b.Property(e => e.Id).HasValueGenerator<SequentialGuidValueGenerator>();
            });
        }
    }

但是请注意,这两个ORM的解决方案只针对Sql Server数据库,因为只保证了最后几位字节是按顺序来生成的。

优点:

  1. 简单,代码方便,性能可以接受。
  2. 数字ID天然排序,对分页或者需要排序的结果很有帮助。
create table #t
(
    id uniqueidentifier not null  default newsequentialid()
    ,name varchar(100)
)
go

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

SequentialGuid框架

SequentialGuid框架也是我要推荐给您,因为它提供了常见数据库生成有序Guid的解决方案。

关于该框架的设计思路以及针对各个数据库的性能测试,见链接:。

使用方式,建议您参考ABP框架,在ABP中使用SequentialGuid框架来生成有序GUID,关键代码链接:。

4. COMB

COMB(combine)型是数据库特有的一种设计思想,可以理解为一种改进的GUID,它通过组合GUID和系统时间,以使其在索引和检索事有更优的性能。
  数据库中没有COMB类型,它是Jimmy Nilsson在他的“The Cost of GUIDs as Primary Keys”一文中设计出来的。
  COMB数据类型的基本设计思路是这样的:既然UniqueIdentifier数据因毫无规律可言造成索引效率低下,影响了系统的性能,那么我们能不能通过组合的方式,保留UniqueIdentifier的前10个字节,用后6个字节表示GUID生成的时间(DateTime),这样我们将时间信息与UniqueIdentifier组合起来,在保留UniqueIdentifier的唯一性的同时增加了有序性,以此来提高索引效率。

 

GUID在各个数据库中的排序规则

在SQL Server数据库中,我们有一种非常简单的方式来比较两个GUID类型的大小值(其实在SQL Server数据库中称为UniqueIdentifier类型):

With UIDs As (--                         0 1 2 3  4 5  6 7  8 9  A B C D E F
            Select ID =  1, UID = cast ('00000000-0000-0000-0000-010000000000' as uniqueidentifier)
    Union   Select ID =  2, UID = cast ('00000000-0000-0000-0000-000100000000' as uniqueidentifier)
    Union   Select ID =  3, UID = cast ('00000000-0000-0000-0000-000001000000' as uniqueidentifier)
    Union   Select ID =  4, UID = cast ('00000000-0000-0000-0000-000000010000' as uniqueidentifier)
    Union   Select ID =  5, UID = cast ('00000000-0000-0000-0000-000000000100' as uniqueidentifier)
    Union   Select ID =  6, UID = cast ('00000000-0000-0000-0000-000000000001' as uniqueidentifier)
    Union   Select ID =  7, UID = cast ('00000000-0000-0000-0100-000000000000' as uniqueidentifier)
    Union   Select ID =  8, UID = cast ('00000000-0000-0000-0010-000000000000' as uniqueidentifier)
    Union   Select ID =  9, UID = cast ('00000000-0000-0001-0000-000000000000' as uniqueidentifier)
    Union   Select ID = 10, UID = cast ('00000000-0000-0100-0000-000000000000' as uniqueidentifier)
    Union   Select ID = 11, UID = cast ('00000000-0001-0000-0000-000000000000' as uniqueidentifier)
    Union   Select ID = 12, UID = cast ('00000000-0100-0000-0000-000000000000' as uniqueidentifier)
    Union   Select ID = 13, UID = cast ('00000001-0000-0000-0000-000000000000' as uniqueidentifier)
    Union   Select ID = 14, UID = cast ('00000100-0000-0000-0000-000000000000' as uniqueidentifier)
    Union   Select ID = 15, UID = cast ('00010000-0000-0000-0000-000000000000' as uniqueidentifier)
    Union   Select ID = 16, UID = cast ('01000000-0000-0000-0000-000000000000' as uniqueidentifier)
)
Select * From UIDs Order By UID, ID

例子来自Ferrari的博客How are GUIDs sorted by SQL Server?

查询结果:

ID UID
16 01000000-0000-0000-0000-000000000000
15 00010000-0000-0000-0000-000000000000
14 00000100-0000-0000-0000-000000000000
13 00000001-0000-0000-0000-000000000000
12 00000000-0100-0000-0000-000000000000
11 00000000-0001-0000-0000-000000000000
10 00000000-0000-0100-0000-000000000000
9 00000000-0000-0001-0000-000000000000
8 00000000-0000-0000-0010-000000000000
7 00000000-0000-0000-0100-000000000000
6 00000000-0000-0000-0000-000000000001
5 00000000-0000-0000-0000-000000000100
4 00000000-0000-0000-0000-000000010000
3 00000000-0000-0000-0000-000001000000
2 00000000-0000-0000-0000-000100000000
1 00000000-0000-0000-0000-010000000000

通过上面可以得于是如下结果:

  1. 先按每1-8从左到右进行排序;
  2. 接着按第9-10位从右到左进行排序;
  3. 最后按后11-16位从右到左进行排序;

通过分析,我们可得到如下权重列表:

序号 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
权重 16 15 14 13 12 11 10 9 7 8 1 2 3 4 5 6
Value 11 11 11 11 - 22 22 - 33 33 - 44 44 - 44 44 44 44 44 44

在Microsoft官方文档中,有一篇文档关于GUID与uniqueidentifier的值比较:
Comparing GUID and uniqueidentifier Values。

不同的数据库处理GUID的方式也是不同的。在SQL Server存在内置GUID类型,没有原生GUID支持的数据库通过模拟来方式来实现的。在Oracle保存为raw bytes类型,具体类型为raw(16);在MySql中通常将GUID储存为char(36)的字符串形式。

关于Oracle、MySql数据库的排序规则与.Net中排序规则,不过篇章的限制,这里不再做具体的演示,不过我在github上提供了示例SQL语句:,您可以自己进行测试。我们在这里只给出最终的结论:

小结:

  1. .Net中GUID的排序规则是从左到右依次进行排序,与数字排序规则一致;
  2. Sql Server数据库提供对GUID类型的支持,在数据库中称为UniqueIdentifier类型,但是排序规则比较复杂:
    • 先按每1-8从左到右进行排序;
    • 接着按第9-10位从右到左进行排序;
    • 最后按后11-16位从右到左进行排序;
  3. Oracle数据库未提供对GUID类型的支持,使用的是raw bytes类型保存数据raw(16),具体类型为,排序规则与GUID在.Net中规则一致;
  4. MySql数据未提供对GUID类型的支持,使用的是字符串的类型保存数据,使用是的char(36)类型,由于使用的是字符串类型,排序规则与GUID在.Net中的规则一致。

优化方案:

  1. 针对主库单点,如果有多个Master库,则每个Master库设置的起始数字不一样,步长一样,可以是Master的个数。
    比如:Master1 生成的是 1,4,7,10,Master2生成的是2,5,8,11 Master3生成的是 3,6,9,12。这样就可以有效生成集群中的唯一ID,也可以大大降低ID生成数据库操作的负载。

  2. UUID


常见的方式,128位。可以利用数据库也可以利用程序生成,一般来说全球唯一。

www.9159.com 2

背景

常见的一种数据库设计是使用连续的整数为做主键,当新的数据插入到数据库时,由数据库自动生成。但这种设计不一定适合所有场景。

随着越来越多的使用Nhibernate、EntityFramework等ORM(对象关系映射)框架,应用程序被设计成为工作单元(Unit Of Work)模式,需要在数据持久化之前生成主键,为了保证在多线程并发以及站点集群环境中主键的唯一性,最简单最常见的方式是将主键设计成为GUID类型。

工作单元是数据库应用程序经常使用的一种设计模式,简单一点来说,就是对多个数据库操作进行打包,记录对象上的所有变化,并在最后提交时一次性将所有变化通过系统事务写入数据库。目的是为了减少数据库调用次数以及避免数据库长事务。关于工作单元的知识可以在园子里面搜索到很多,在这里就不做详细的介绍了。

GUID(全球唯一标识符)也称为UUID,是一种由算法生成的二进制长度为128位的数字标识符。在理想情况下,任何计算机和计算机集群都不会生成两个相同的GUID。GUID 的总数达到了2^128(3.4×10^38)个,所以随机生成两个相同GUID的可能性非常小,但并不为0。GUID一词有时也专指微软对UUID标准的实现。

RFC 41222描述了创建标准GUID,如今大多数GUID生成算法通常是一个很长的随机数,再结合一些像网络MAC地址这种随机的本地组件信息。

GUID的优点允许开发人员随时创建新值,而无需从数据库服务器检查值的唯一性,这似乎是一个完美的解决方案。

很多数据库在创建主键时,为了充分发挥数据库的性能,会自动在该列上创建聚集索引。我们先来说一说什么是聚集索引,。集索引确定表中数据的物理顺序。聚集索引类似于电话簿,按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序,因此一个表也只能包含一个聚集索引。它能够快速查找到数据,但是如果插入数据库的主键不在列表的末尾,向表中添加新行时就非常缓慢。例如,看下面这个例子,在表中已经存在三行数据(例子来自Jeremy Todd的博客GUIDs as fast primary keys under multiple databases:

ID Name
1 Holmes, S.
4 Watson, J.
7 Moriarty, J.

此时非常简单:数据行按对应ID列的顺序储存。如果我们新添加一行ID为8的数据,不会产生任何问题,新行会追加的末尾。

ID Name
1 Holmes, S.
4 Watson, J.
7 Moriarty, J.
8 Lestrade, I.

但如果我们想插入一行的ID为5的数据:

ID Name
1 Holmes, S.
4 Watson, J.
5 Hudson, Mrs.
7 Moriarty, J.
8 Lestrade, I.

ID为7,8的数据行必须向下移动。虽然在这算什么事儿,但当您的数据量达到数百万行的级别之后,这就是个问题了。如果你还想要每秒处理上百次这种请求,那可真是难上加难了。

这就是GUID主键引发的问题:它是随机产生的,所以在数据插入时,随时都会涉及到数据的移动,导致插入会很缓慢,还会涉及大量不必要的磁盘活动。总结果有两点:

  • 空间的浪费以及由此带来的读写效率的下降;
  • 更主要的,存储的碎片化以及由此带来的读写效率严重下降。
  • 备注:摘自Crazy.Liu博客SQL SERVER下有序GUID和无序GUID作为主键&聚集索引的性能表现

GUID最关键的问题就是它是随机的。我们需要设计一种有规则的GUID生成方式,在之后生成的GUID类型总是比之前的要大,保证插入数据库的主键是在列表末尾追加的,这种我们称之为有序GUID

优化方案:

  1. 为了解决UUID不可读,可以使用UUID to Int64的方法。

  2. GUID


GUID:是微软对UUID这个标准的实现。UUID还有其它各种实现,不止GUID一种。优缺点同UUID。

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

有序GUID

有序GUID是有规则的生成GUID,保存在之后生成的GUID类型总是比之前的要大。不过在上一节中,已经提到过各个数据库对GUID支持不一样,而且排序的规则也不一样,所以我们需要为每一个数据库提供不一致的有序GUID生成规则。

参考:

  1. 分布式系统唯一ID生成方案汇总
  2. UUID 、GUID、COMB 的区别与联系
  3. UUID 和 GUID 的区别
  4. The Cost of GUIDs as Primary Keys
  5. Integer GUID和Comb做主键的效率测试(Delphi+access)(三)
  6. Twitter-Snowflake项目地址(Tags:snowflake-2010)
  7. 如何在高并发分布式系统中生成全局唯一Id
  8. Twitter-Snowflake(64位分布式ID算法)分析与JAVA实现

转载注明出处,我就不和你计较。
by Donney Young
http://www.jianshu.com/p/a0a3aa888a49

 

缺点:

  1. 没有排序,无法保证趋势递增。
  2. UUID往往是使用字符串存储,查询的效率比较低。
  3. 存储空间比较大,如果是海量数据库,就需要考虑存储量的问题。
  4. 传输数据量大
  5. 不可读。

 

主键生成策略

系统唯一ID是我们在设计一个系统的时候常常会遇见的问题,下面介绍一些常见的ID生成策略。

  • Sequence ID
  • UUID
  • GUID
  • COMB
  • Snowflake

最开始的自增ID为了实现分库分别的需求,会在自增的前提下,使用不同步长(例如DB1 生成1,4,7,10,DB2生成2,5,8,11,DB3生成3,6,9,12),但需要做数据库拓展时,极其麻烦。
  相比自增ID,UUID生成唯一主键更加方便(数据量非常大的情况下,存在重复的可能),但由于UUID的无序性,性能不如自增ID,字符串储存,储存空间大,查询效率低。
  COMB相对于UUID,增加了生成ID的有序性,插入与查询效率都有所提高。见Integer GUID和Comb做主键的效率测试(Delphi+access)(三)
  Sonwflake是Twitter主键生成策略,可以看做是COMB的一种改进,用64位的长整型代替128位的字符串。ID构成:第一位0

  • 41位的时间前缀 + 10位的节点标识 + 12位的sequence避免并发的数字。见Twitter-Snowflake(64位分布式ID算法)分析与JAVA实现

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

1. Sequence ID

数据库自增长序列或字段,最常见的方式。由数据库维护,数据库唯一。

输出结果:

缺点:

  1. 不同数据库语法和实现不同,数据库迁移的时候或多数据库版本支持的时候需要处理。
  2. 在单个数据库或读写分离或一主多从的情况下,只有一个主库可以生成。有单点故障的风险。
  3. 在性能达不到要求的情况下,比较难于扩展。
  4. 如果遇见多个系统需要合并或者涉及到数据迁移会相当痛苦。
  5. 分表分库的时候会有麻烦。

  有以下几个缺点:

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

www.9159.com 3  类似 汉字的三点水偏旁(为了好记)

  可以执行看一下

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

问题描述


 

NEWSEQUENTIALID 是对 Windows UuidCreateSequential 函数的包装。

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

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

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地址:

 

我的解决方法


 

总结上面的方法,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() 

 结果:

 www.9159.com 4

 

 多次请求:


 

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

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

select * from #t

 

 www.9159.com 5

 

git地址

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

 

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

 

 

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

    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();
        }

    }

本文由9159.com发布于www.9159.com,转载请注明出处:集索引确定表中数据的物理顺序,查询效率低

关键词: