使用GUID作为数据库主键的测试

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

在一个较大型的应用中，如果需要用到两套以上的数据库（如：SQL Server和Oracle），是否可以把需要的sql查询全部封装在存储过程里，这样就只需要一套访问代码了，有没有更好的方法解决这个问题？
在数据库的主键的设立中（同时支持多种数据库）直接用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 dragonpro 阅读(5983) 评论(10) 编辑收藏举报