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

原文连接：http://www.cnblogs.com/jackhuclan/archive/2010/01/04/1639005.html

在数据库的设计中我们常常用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 ( 50 )  not   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 ( 50 )  not   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和徐少侠二位！

作者： Jackhuclan
出处： http://jackhuclan.cnblogs.com/ 
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。