使用Guid做主键和int做主键性能比较

  在数据库的设计中我们常常用Guid或int来做主键,根据所学的知识一直感觉int做主键效率要高,但没有做仔细的测试无法

说明道理。碰巧今天在数据库的优化过程中,遇到此问题,于是做了一下测试。

 

测试环境:

  台式电脑 Pentiun(R) 4 Cpu 3.06GHz
  Win XP professional
  1.5G DDR RAM
  SQL Server 2005 个人版  

 

测试过程:
首先创建测试数据库Test
1.创建Test_Guid表,创建Test_Int表

代码
-------------------------------------------
--
创建Test_Guid表
--
-------------------------------------------
USE  Test
GO

IF OBJECT_ID('Test_Guid''U'IS NOT NULL
  
DROP TABLE Test_Guid
GO

CREATE TABLE Test_Guid
(
    Guid 
varchar(50not null,
    TestId 
int not null,
    TestText 
ntext not null,
    TestDateTime 
datetime default getdate(),
    
CONSTRAINT PK_Guid PRIMARY KEY (Guid)
)
GO
---------------------------------------------
--
创建Test_Int表
--
-------------------------------------------
USE  Test
GO

IF OBJECT_ID('Test_Int''U'IS NOT NULL
  
DROP TABLE Test_Int
GO

CREATE TABLE Test_Int
(
    Id 
int not null identity(1,1),
    TestId 
int not null,
    TestText 
ntext not null,
    TestDateTime 
datetime default getdate(),
    
CONSTRAINT PK_Id PRIMARY KEY (Id)
)
GO

 

2.创建Test_Guid子表:Test_Guid_Detail和创建Test_Int子表:Test_Int_Detail,用来做连接查询

代码
--创建Test_Guid子表:Test_Guid_Detail
USE  Test
GO

IF OBJECT_ID('Test_Guid_Detail''U'IS NOT NULL
  
DROP TABLE Test_Guid_Detail
GO

CREATE TABLE Test_Guid_Detail
(
    Guid 
varchar(50not null,--Guid是Test_Guid的外键
    TestId int not null,
    TestText 
ntext not null,
    TestDateTime 
datetime default getdate()--,
    --CONSTRAINT PK_Guid PRIMARY KEY (Guid)
)
GO
--创建Test_Int子表:Test_Int_Detail
USE  Test
GO

IF OBJECT_ID('Test_Int_Detail''U'IS NOT NULL
  
DROP TABLE Test_Int_Detail
GO

CREATE TABLE Test_Int_Detail
(
    Id 
int not null,--Id是Test_Int的外键
    TestId int not null,
    TestText 
ntext not null,
    TestDateTime 
datetime default getdate()--,
    --CONSTRAINT PK_Guid PRIMARY KEY (Guid)
)
GO

 

3.开始测试
测试1:测试Insert:向Test_Guid表中插入10万条记录

代码
---------------------------------------------
--
测试Insert:向Test_Guid表中插入10万条记录
--
-------------------------------------------
declare @num int
declare @startTime datetime
set @num=0;
set @startTime=getdate()
while(@num<100000)
begin
    
insert into Test_Guid
    
values(newid(),@num,'测试guid',getdate())
    
set @num=@num+1
end
select datediff(second,@startTime,getdate()) as 秒,datediff(ms,@startTime,getdate()) as 毫秒

 

测试2:测试Insert:向Test_Int表中插入10万条记录

代码
---------------------------------------------
--
测试Insert:向Test_Int表中插入10万条记录
--
-------------------------------------------
declare @num int
declare @startTime datetime
set @num=0;
set @startTime=getdate()
while(@num<100000)
begin
    
insert into Test_Int
    
values(@num,'测试int',getdate())
    
set @num=@num+1
end
select datediff(second,@startTime,getdate()) as 秒,datediff(ms,@startTime,getdate()) as 毫秒

 

测试3:测试Select:查找Test_Guid表中所有记录

代码
---------------------------------------------
--
测试Select:查找Test_Guid表中所有记录
--
-------------------------------------------
declare @startTime datetime
set @startTime=getdate()
select * from Test_Guid
select datediff(second,@startTime,getdate()) as 秒,datediff(ms,@startTime,getdate()) as 毫秒

 

测试4:测试Select:查找Test_Int表中所有记录

代码
---------------------------------------------
--
测试Select:查找Test_Int表中所有记录
--
-------------------------------------------
declare @startTime datetime
set @startTime=getdate()
select * from Test_Int
select datediff(second,@startTime,getdate()) as 秒,datediff(ms,@startTime,getdate()) as 毫秒

 

测试5:聚合查询:查找Test_Guid表中所有记录数

代码
---------------------------------------------
--
聚合查询:查找Test_Guid表中所有记录
--
-------------------------------------------
declare @startTime datetime
set @startTime=getdate()
select count(*from Test_Guid
select datediff(second,@startTime,getdate()) as 秒,datediff(ms,@startTime,getdate()) as 毫秒

 

测试6:聚合查询:查找Test_Int表中所有记录数

代码
---------------------------------------------
--
聚合查询:查找Test_Int表中所有记录
--
-------------------------------------------
declare @startTime datetime
set @startTime=getdate()
select count(*from Test_Int
select datediff(second,@startTime,getdate()) as 秒,datediff(ms,@startTime,getdate()) as 毫秒

 

测试7:测试带where条件的Select查询:查找Test_Int表中所有记录,都查找10000到50000之间的4万条记录

代码
----------------------------------------------------------------------------------------
--
测试带where条件的Select查询:查找Test_Int表中所有记录,都查找10000到50000之间的4万条记录
--
--------------------------------------------------------------------------------------
declare @startTime datetime
set @startTime=getdate()
select * from Test_Guid where TestId between 10000 and 50000 
select datediff(second,@startTime,getdate()) as 秒,datediff(ms,@startTime,getdate()) as 毫秒

 

测试8:测试带where条件的Select查询:查找Test_Int表中所有记录,都查找10000到50000之间的4万条记录

代码
----------------------------------------------------------------------------------------
--
测试带where条件的Select查询:查找Test_Int表中所有记录,都查找10000到50000之间的4万条记录
--
--------------------------------------------------------------------------------------
declare @startTime datetime
set @startTime=getdate()
select * from Test_Int where TestId between 10000 and 50000 
select datediff(second,@startTime,getdate()) as 秒,datediff(ms,@startTime,getdate()) as 毫秒

 

测试9:测试Test_Guid关联查询inner join

首先以Test_Guid中第一个Guid为外键,向Test_Guid_Detail中插入1万条记录

代码
---------------------------------------------
--
向Test_Guid子表:Test_Guid_Detail中插入1万条记录
--
-------------------------------------------
declare @num int
declare @topGuid nvarchar(50)
set @num=0;
select top 1 @topGuid=Guid from Test_Guid
while(@num<10000)
begin
    
insert into Test_Guid_Detail
    
values(@topGuid,@num,'测试guid的子表',getdate())
    
set @num=@num+1
end

 

然后开始测试:

代码
---------------------------------------------
--
测试连接查询:查找Test_Guid表和Test_Guid_Detail所有关联的记录
--
-------------------------------------------
declare @startTime datetime
set @startTime=getdate()
select T.* from Test_Guid T
inner join Test_Guid_Detail T1 on T.Guid=T1.Guid
select datediff(second,@startTime,getdate()) as 秒,datediff(ms,@startTime,getdate()) as 毫秒

测试10:测试Test_Int关联查询inner join

首先以Test_Int中第一个id为外键,向Test_Int_Detail中插入1万条记录

代码
---------------------------------------------
--
向Test_Int子表:Test_Int中插入1万条记录
--
-------------------------------------------
declare @num int
declare @topInt int
set @num=0;
select top 1 @topInt=Id from Test_Int
while(@num<10000)
begin
    
insert into Test_Int_Detail
    
values(@topInt,@num,'测试int的子表',getdate())
    
set @num=@num+1
end

 

然后开始测试:

代码
---------------------------------------------
--
测试连接查询:查找Test_Int表和Test_Int_Detail所有关联的记录
--
-------------------------------------------
declare @startTime datetime
set @startTime=getdate()
select T.* from Test_Int T
inner join Test_Int_Detail T1 on T.id=T1.id
select datediff(second,@startTime,getdate()) as 秒,datediff(ms,@startTime,getdate()) as 毫秒

测试11:测试Update:更新Test_Guid表中所有记录

代码
---------------------------------------------
--
测试Update:查找Test_Guid表中所有记录
--
-------------------------------------------
declare @startTime datetime
set @startTime=getdate()
update  Test_Guid set TestText='测试guid更新'
select datediff(second,@startTime,getdate()) as 秒,datediff(ms,@startTime,getdate()) as 毫秒

 

测试12:测试Update:更新Test_Int表中所有记录

代码
---------------------------------------------
--
测试Update:查找Test_Int表中所有记录
--
-------------------------------------------
declare @startTime datetime
set @startTime=getdate()
update Test_Int set TestText='测试int更新'
select datediff(second,@startTime,getdate()) as 秒,datediff(ms,@startTime,getdate()) as 毫秒

 

测试13:测试Delete:删除Test_Guid表中所有记录

代码
---------------------------------------------
--
测试Delete:查找Test_Guid表中所有记录
--
-------------------------------------------
declare @startTime datetime
set @startTime=getdate()
delete from Test_Guid
select datediff(second,@startTime,getdate()) as 秒,datediff(ms,@startTime,getdate()) as 毫秒
delete from Test_Guid_Detail

 

测试14:测试Delete:删除Test_Int表中所有记录

代码
---------------------------------------------
--
测试Delete:查找Test_Int表中所有记录
--
-------------------------------------------
declare @startTime datetime
set @startTime=getdate()
delete from Test_Int
select datediff(second,@startTime,getdate()) as 秒,datediff(ms,@startTime,getdate()) as 毫秒
delete from Test_int_Detail

 

测试结果如下:

综上所述,使用int做主键比用guid做主键各中情况下效率均有提高,特别是在有连接查询和删除记录效率提升明显。

而且本人今日在guid做主键的数据查询中因为嵌套几个子查询结果屡屡出现查询超时。因此本人赞同用int做主键,不赞同guid做主键。
以上观点代表个人观点,欢迎大家各抒己见,说明guid和int各自做主键的优劣所在。

附上测试脚本供大家测试:http://files.cnblogs.com/jackhuclan/guid.rar 

 

后续测试:
 经过各位兄弟的提醒,今天在两个子表添加了非聚集索引:

CREATE NONCLUSTERED INDEX Index_Detail_Guid on Test_Guid_Detail(Guid) 
CREATE NONCLUSTERED INDEX Index_Detail_id on Test_Int_Detail(id) 

 

然后进行内连接查询,发现如@徐少侠说所的,效率确实不至于提示50%以上明显,基本只有23%左右的提升,这个还是可以接受的。

因此建议

1.在经常需要做数据迁移的系统中,建议用Guid。并且在相应的外键字段,也就是用来做连接查询的字段添加非聚集索引,对于改善性能有极大的好处。where条件的字段也可以适当添加非聚集索引。

2.在使用Guid类型作为主键时,数据类型应为uniqueidentifier,并且一定要记得取消主键的“聚集索引”

3.对于不需要做迁移,或小型系统,用int做主键还是很方便的,并且在效率方面还是有一定提升的。

对各位发表评论的各位兄弟,一并表示感谢,尤其感谢SW515和徐少侠二位!
posted @ 2010-01-04 16:47  落叶潇潇雨  阅读(8400)  评论(55编辑  收藏