随笔-74  评论-364  文章-17  trackbacks-6
使用GUID作为数据库主键的测试

今天听了MSDN的WebCast，是关于Entlib的数据访问的讲座，末尾我问了两个自己所关心的问题：

  1. 在一个较大型的应用中，如果需要用到两套以上的数据库（如：SQL Server和Oracle），是否可以把需要的sql查询全部封装在存储过程里，这样就只需要一套访问代码了，有没有更好的方法解决这个问题？
  2. 在数据库的主键的设立中（同时支持多种数据库）直接用GUID作为主键来得简单，但是在查询的时候影响性能的因素大不大，还有没有更好的解决方法？

以上两个问题，由于时间的关系吧，微软的工程师解答的比较简略，第一个应该需要针对具体的应用来考虑，但是第二个问题，性能影响肯定是有的，但是影响大不大呢，带着这个问题，我做了这个小试验。

注：如果您有更好的建议不防贡献出来大家探讨探讨^_^！

测试环境：

  • Dell笔记本电脑 迅驰1.5G
  • Win XP professional
  • 512MB DDR RAM
  • SQL Server 2000 个人版

测试方法：

  • 建立有10个字段的数据库[test_GUID]，使用GUID作为主键，以及其他常用的字段类型，模拟现实中的使用情况，建表的SQL代码如下：

    CREATE TABLE [dbo].[Test_GUID] (
     [GUID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
     [test1] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
     [test2] [datetime] NULL ,
     [test3] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
     [test4] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
     [test5] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
     [test6] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
     [test7] [text] COLLATE Chinese_PRC_CI_AS NULL ,
     [test8] [int] NULL ,
     [test9] [int] NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[Test_GUID] WITH NOCHECK ADD
     CONSTRAINT [PK_Test_GUID] PRIMARY KEY  CLUSTERED
     (
      [GUID]
     )  ON [PRIMARY]
    GO

  • 建立有10个字段的数据库[test_IIDD]，使用IIDD作为主键，以及其他常用的字段类型，模拟现实中的使用情况，建表的SQL代码如下：

    CREATE TABLE [dbo].[Test_IIDD] (
     [IIDD] [numeric] (9) IDENTITY(1,1) NOT NULL ,
     [test1] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
     [test2] [datetime] NULL ,
     [test3] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
     [test4] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
     [test5] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
     [test6] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
     [test7] [text] COLLATE Chinese_PRC_CI_AS NULL ,
     [test8] [int] NULL ,
     [test9] [int] NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[Test_IIDD] WITH NOCHECK ADD
     CONSTRAINT [PK_Test_IIDD] PRIMARY KEY  CLUSTERED
     (
      [IIDD]
     )  ON [PRIMARY]
    GO

  • 可以看到，第一个表使用全局唯一标识(GUID)来作为主键，而第二个表使用普通numeric（类似Int型）的数据类型来作为主键，关于GUID这里做一个小小介绍：
    GUID，全局唯一标识，常用在COM组件的标识里，因为此几乎不可能生成重复的两个值，所以在各个领域经常用到，具体的值如：“A89C9547-032B-4860-ABB5-6EAEAVE934D5”所示，你一定看到过类似的字符串吧，^_^，在SQL Server2000 中使用newid()函数来获取一个唯一的GUID
  • 分别运行如下两个SQL语句对两个表分别插入10万条语句，我所关心大数据量的情况下的效果，所以不要怪我开始点选择10万条数据的情况^_^。

    declare @num int
    set @num = 0
    while(@num < 100000)
    begin

    insert into test_Guid
    values(
    newid(),
    'X222222222222222222',
    getdate(),
    'AAAAAAAAAAAAAAAAAA',
    'BBBBBBBBBBBBBBBB',
    'CCCCCCCCCCCCCCCCCCCCCC',
    'DDDDDDDDDDDDDDDDD',
    '479C8AAD-3040-4FC5-B53A-D6AF085AD38A
    479C8AAD-3040-4FC5-B53A-D6AF085AD38A
    479C8AAD-3040-4FC5-B53A-D6AF085AD38A
    479C8AAD-3040-4FC5-B53A-D6AF085AD38A
    479C8AAD-3040-4FC5-B53A-D6AF085AD38A
    479C8AAD-3040-4FC5-B53A-D6AF085AD38A
    479C8AAD-3040-4FC5-B53A-D6AF085AD38A
    479C8AAD-3040-4FC5-B53A-D6AF085AD38A
    ',
    '1',
    '0'
    )

    set @num = @num+1
    end

    declare @num int
    set @num = 0
    while(@num < 100000)
    begin

    insert into test_IIDD
    values(
    'X222222222222222222',
    getdate(),
    'AAAAAAAAAAAAAAAAAA',
    'BBBBBBBBBBBBBBBB',
    'CCCCCCCCCCCCCCCCCCCCCC',
    'DDDDDDDDDDDDDDDDD',
    '479C8AAD-3040-4FC5-B53A-D6AF085AD38A
    479C8AAD-3040-4FC5-B53A-D6AF085AD38A
    479C8AAD-3040-4FC5-B53A-D6AF085AD38A
    479C8AAD-3040-4FC5-B53A-D6AF085AD38A
    479C8AAD-3040-4FC5-B53A-D6AF085AD38A
    479C8AAD-3040-4FC5-B53A-D6AF085AD38A
    479C8AAD-3040-4FC5-B53A-D6AF085AD38A
    479C8AAD-3040-4FC5-B53A-D6AF085AD38A
    ',
    '1',
    '0'
    )

    set @num = @num+1
    end

  • 开始测试，测试代码及显示结果如下：
    ＃测试一 （GUID）

    --------------------
    declare @times  datetime
    set @times = getdate()
    --------------------
    select * from test_guid
    where
    guid='A89C9547-032B-4860-ABB5-6EAEA0E934D5' or
    guid='FFFA8619-BC9F-4B76-ACE8-B3324105BBDE' or
    guid='FFFC26D5-6ECF-479D-838D-0D3E23AC7D2D' or
    guid='FFF9FA53-E115-450A-A52D-B0AET36FF539' or
    guid='A89C9547-032B-4860-ABB5-6EAEAVE934D5' or
    guid='FFF90A0B-CB5B-446F-81FC-CFA661D03CF8' or
    guid='FFF85F4A-4554-491F-9D1A-05C8BA3C1266' or
    guid='FFFF354A-ED3E-4C3A-A033-3406F229EB34'
    order by guid desc

    ---------------------
    select datediff(second,@times,getdate()) as 秒,datediff(ms,@times,getdate()) as 毫秒
    ---------------------

    0秒，0毫秒，有时会有10毫秒的情况
    ＃测试二 （IIDD）

    --------------------
    declare @times  datetime
    set @times = getdate()
    --------------------
    select * from test_IIDD
    where
    IIDD='1' or
    IIDD='2' or
    IIDD='200' or
    IIDD='8000' or
    IIDD='8900' or
    IIDD='3' or
    IIDD='8' or
    IIDD='10000'
    order by IIDD desc

    ---------------------
    select datediff(second,@times,getdate()) as 秒,datediff(ms,@times,getdate()) as 毫秒
    ---------------------

    0秒，0毫秒，有时会有10毫秒的情况
  • 可以看到在10万条数据的情况下，普通Select查询的时候效率影响还不大
    ＃测试三 （GUID）
    --------------------
    declare @times  datetime
    set @times = getdate()
    --------------------
    select count(*) from test_guid
    ---------------------
    select datediff(second,@times,getdate()) as 秒,datediff(ms,@times,getdate()) as 毫秒
    ---------------------
    29秒，28793毫秒，效果不好啊！
    ＃测试四（IIDD）
    --------------------
    declare @times  datetime
    set @times = getdate()
    --------------------
    select count(*) from test_IIDD
    ---------------------
    select datediff(second,@times,getdate()) as 秒,datediff(ms,@times,getdate()) as 毫秒
    ---------------------
    第一次运行3秒，第二次运行1秒，第三次运行0秒，50毫秒，my god！
  • 这可如何是好，GUID在没有where子句的聚合运算时吃大亏了
    ＃测试五 （GUID）
    --------------------
    declare @times  datetime
    set @times = getdate()
    --------------------
    select count(*) from test_guid
    where
    test2 > '2005-06-03 21:05:33.330'
    ---------------------
    select datediff(second,@times,getdate()) as 秒,datediff(ms,@times,getdate()) as 毫秒
    ---------------------
    29秒，29093毫秒，尽管查询出来只有200多条数据但速度没有变化！
    ＃测试六（IIDD）
    --------------------
    declare @times  datetime
    set @times = getdate()
    --------------------
    select count(*) from test_IIDD
    where
    test2 > '2005-06-03 21:05:33.330'
    ---------------------
    select datediff(second,@times,getdate()) as 秒,datediff(ms,@times,getdate()) as 毫秒
    ---------------------
    第一次运行2秒，第二次运行0秒，160毫秒，比没有Where的情况稍慢
  • 如结果所示，效果很不理想
    ＃测试七 （GUID）
    把test_GUID这个表的test2这一列（datetime）添加为索引列

    运行【测试三】0秒，50毫秒，原来如此。。。

    运行【测试五】0秒，0毫秒，非常明显了吧。

    ＃测试八（IIDD）
    把test_IIDD这个表的test2这一列（datetime）添加为索引列

    运行【测试四】0秒，40毫秒

    运行【测试六】0秒，40毫秒

  • 上面的测试七和测试八在返回值方面不尽相同造成一些微小的差别这个可以忽略（因为我测试了在相同返回值的情况下差别是很小的）
  • 可以看出在以GUID作为主键的表中加一个时间类型或是Int类型的索引可以弥补以GUID作为主键带来的性能损失。

总结：

此次测试由于时间的关系，测试的比较片面也很肤浅，还望能有高手把不足和疏漏的地方进行补充和改进，在这次测试后我想我还会做更多的关于性能方面的测试，有精力再做吧。

此次测试就只得出这么一点肤浅的东西，希望没有浪费您宝贵的时间^_^！

 


 

posted on 2005-06-03 23:46 Felix 阅读(2158) 评论(10)  编辑 收藏 所属分类: 数据库

评论:
#1楼  2005-06-09 11:22 | 吕震宇      
我想这个测试还存在一些问题，不是三言两语能说清楚的。挑几个我认为比较关键的说一说：

1、设计表时为什么用[GUID] [varchar] (50) ，是否出于兼容Oracle考虑？SQL Server中有UniqueIdentifier类型。

2、测试结论有问题“在以GUID作为主键的表中加一个时间类型或是Int类型的索引可以弥补以GUID作为主键带来的性能损失”在SQL Server中，如果在一个有聚簇索引的表上再建立其它索引，那么其它索引链接的就不是页节点了，而是聚簇索引节点。也就是说，一个普通索引上的查询先检索普通索引，然后索引会告诉你对应数据的聚簇索引是什么，然后聚簇索引再告诉你数据再哪里。（可以参考微软SQL Server培训教程）。不过这并不是问题的关键。关键在下面：

3、在上面的测试中，测试命令是：select count(*) from ... where test2 > '2005-06-03 21:05:33.330'。问题发生在了count(*)上面。这里的查询只是计数，因此我们管它叫做索引覆盖查询，也就是只查时间索引就可以得到计数值，聚簇索引根本没有派上用场，也就是说根本没有比较聚簇索引的效率，所以你得到了速度一致的结论。这里，测试设计上有问题。你可以试试select *替换select count(*) ，我想结果差异应当非常明显。关于索引可以参考http://www.cnblogs.com/zhenyulu/articles/25794.html

希望楼主再实验一下。
  回复  引用  查看    
#2楼  2005-06-09 16:26 | dragonpro      
非常感谢能得到高手的指点，这些疏漏和错误我想在这里给点补充

1、Oracle我不太熟悉，但我想它应该没有UniqueIdentifier类型，所以我采用varchar(50),目的就是为了兼容Oracle，我想这样比用UniqueIdentifier类型肯性能上有所下降，我观察了一下，如果用UniqueIdentifier类型作为聚集索引，数据的排序是按照最后一组数据进行的，可能数据库针对此数据类型做了不少工作，我想知道用varchar作为主键需要注意些什么。

2、对这个问题，我也是这么理解的，但是一直我不敢确认这样理解是否正确，因为我曾经遇到过使用聚集索引比不使用慢很多，经过我苦苦思索得出可能数据库会检索非聚集索引后再通过聚集索引字段来定位记录，看来正是这个问题。我还想明白一点，如果数据表里没有聚集索引那么用什么来定位索引所找到的记录，是否是用指针，如果指针这么好用，为什么建立了聚集索引后就要通过聚集索引而不是通过指针来定位呢。用Int型的非重复的主键进行聚集索引这样会不会影响检索效率呢。

3、这样做是为了模拟现实使用情况，当然这样很片面，因为在平时使用过程中，经常用到分页，计算数据总数，目的是看看在如果在表中不另外加索引会不会影响计算效率，看来用GUID作为主键必须要另外加索引才能保证入count这样的计算不至于消耗太多时间，但是用int类型好像就不会消耗太多时间的，所以这样一比较就看出，如果用varchar字段类型做主键的表，表内一定需要个非聚集索引。

  回复  引用  查看    
#3楼  2005-06-09 16:36 | dragonpro      
我非常想用GUID做主键用在我们开发的系统里面，但是这涉及到的问题也是需要充分考虑的，为了这些问题，特别是性能问题，我都考虑很久了，希望能有个满意的处理方式，我的系统希望支持至少两种数据库，特别需要支持Oracle。

但在做表的时候，如果在表里不使用另外的非聚集索引，我想很多查询都会比较慢，那就比较可怕了。
  回复  引用  查看    
#4楼  2005-06-09 17:22 | dragonpro      
又做了下测试，用UniqueIdentifier类型的话跟Int型的在查询方面相差不大，但是用varchar类型者需要在其他字段建立非聚集索引来为查询优化创造条件
不知道这样认为是否合适。
再有，在插入数据的时候如果GUID字段为聚集索引的话，由于字段值是随机的，我插入的数据并不知道要放在什么位置，这样是否也需要选择新记录插入的位置而消耗操作时间，所以我想索性指定一个日期型字段来作为聚集索引，这样增加记录基本上都是在末尾，这样是否能有效减少了数据操作时间呢
  回复  引用  查看    
#5楼  2005-06-09 22:18 | 吕震宇      
非常佩服楼主的敬业精神。我还想说两句，不知楼主是否赞同我的看法：

1、"看来用GUID作为主键必须要另外加索引才能保证入count这样的计算不至于消耗太多时间"，在这里另外的索引必须是你的Where短语中用到的字段，否则是不会带来性能提升的。

2、“我插入的数据并不知道要放在什么位置，这样是否也需要选择新记录插入的位置而消耗操作时间”，我以前也一直是这么想的，但感觉自己想法有问题。我猜测加入GUID的聚簇索引主键时不会为选择新的插入位置消耗太多的时间。因为聚簇索引的页节点是数据节点，因此完全可以在枝节点上做文章以减少系统的消耗。这只是我的猜测。所以用GUID与用时间做聚簇索引性能应当差别不大。当然这也是我的猜测。

3、我不太赞同用时间做聚集索引，说不出为什么，感觉不太好。似乎没有做到“专职专责”。
  回复  引用  查看    
#6楼  2005-06-10 14:11 | dragonpro      
感谢：吕震宇，我赞同您的观点，也了解了些SQL Server的一些查询原理。

反复看了几遍你的那些文章，说得很好，要设计好一个系统我想必需耐心分析，特别这些底层的东西，不设计好可能误大事，希望我能找到一个比较完善的设计方案。

另外我对comb数据类型任然有点担心会重复的问题，因为我们的系统可能涉及到分布式的应用，还有数据移植等等诸多问题。用comb实现到没什么，应用在SQL Server上是非常好的，但也局限于SQL Server的UniqueIdentifier类型，用varchar类型这种优势就体现不了了。

如果主键是GUID的类型，那么不适合做聚集索引，其他的时间也没有聚集索引，那么数据的排序按照什么方式进行呢，再理解ing...

  回复  引用  查看    
#7楼  2005-06-10 15:31 | dragonpro      
#8楼  2005-12-19 10:59 | enjoyo [未注册用户]
Integer GUID和Comb做主键的效率测试(Delphi+access)

http://sinoprise.com/read.php?tid=859&fpage=1
  回复  引用    
#9楼 [楼主] 2005-12-26 13:08 | Dragonpro      
我也在想把Comb的数据后面的提到前面来就可以兼容没有GUID类型的数据库了，看来这样是合适的


  回复  引用  查看    
#10楼  2008-05-10 03:59 | IMART 创意集市 [未注册用户]
讨论很精彩!学习了,谢谢!
  回复  引用    

标题  
姓名  
主页
Email (博主才能看到) 
验证码 *  看不清,换一张 [登录][注册]
内容(请不要发表任何与政治相关的内容)  
 博客园首页

新闻频道

社区

小组

博问

网摘

闪存
  登录  使用高级评论  新用户注册  返回页首  恢复上次提交      
该文被作者在 2006-01-14 10:34 编辑过
成果网帮您增加网站收入


相关链接:

最新IT新闻:
Google 10周岁生日
祝Google 10周岁生日快乐
Google十年市值达1500亿美元 创造奇迹
GMail:回过头来支持IE6
Google十年大变样
 





Powered by: 博客园 模板提供：沪江博客 Copyright ©2008 Felix