反驳 吕震宇的“小议数据库主键选取策略（原创）”

反驳 吕震宇的“小议数据库主键选取策略（原创）”
Posted on 2004-07-19 15:05被遗弃的小指 阅读(2206)评论(19)  编辑 收藏引用网摘 所属分类:其他微软相关
原文章请参见http://www.cnblogs.com/zhenyulu/articles/25326.aspx
其实你们注意“主键”的同时而忽略了另外一个很重要的东西====〉“索引”
当我们建立一个主键的时候，系统会默认在这个主键上建立一个索引（这里说明一下，我是以MS Sql Server2000为例，其他厂商的数据库我不熟悉，不知道怎么样的），这个索引默认是CLUSTERED索引，也就是聚集索引（聚簇索引）
聚集索引对于数据都会进行排序，然后在索引的页面里面，分别按照数据页里面的索引字建立索引
如下图所示：

但是在数据库中还有另外一种索引：NOCLUSTERED，它是利用一种类似于hashtable的方法，把索引和数据对应起来，非聚簇索引的图示如下：

所以，对于不需要排序的数据，我们使用非聚簇索引相对来说比较好些。
那么对于主键，采用GUID类型的时候，我们应该设置这个主键的索引类型是NOCLUSTERED的，效果就会提高上去
我做了个试验：

CREATE TABLE [tabInt] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [myValue] [int] NULL ,
    CONSTRAINT [PK_tabInt] PRIMARY KEY  CLUSTERED
    (
        [ID]
    )  ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [tabUnq] (
    [ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tabUnq_ID] DEFAULT (newid()),
    [myValue] [int] NULL ,
    CONSTRAINT [PK_tabUnq] PRIMARY KEY  CLUSTERED
    (
        [ID]
    )  ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [tabComb] (
    [ID] [uniqueidentifier] NOT NULL ,
    [myValue] [int] NULL ,
    CONSTRAINT [PK_tabComb] PRIMARY KEY  CLUSTERED
    (
        [ID]
    )  ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [tabUnq2] (
    [ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tabUnq2_ID] DEFAULT (newid()),
    [myValue] [int] NULL ,
    CONSTRAINT [PK_tabUnq2] PRIMARY KEY  NONCLUSTERED
    (
        [ID]
    )  ON [PRIMARY]
) ON [PRIMARY]
GO



declare @i bigint
declare @v int
declare @s datetime
declare @e datetime
set @i=0
set @s=getdate()
print ‘int类型为主键的表插入开始时间：‘ + cast(@s as nvarchar)
while @i<10000
begin
    set @v=rand()
    insert into tabInt ([myValue]) values (@v)
    set @i=@i+1
end

set @e =getdate()
print ‘int类型为主键的表插入结束时间：‘ + cast(@e as nvarchar)
print ‘int类型为主键的表插入共用时间：‘ + cast (DATEDIFF(ms, @s, @e) as nvarchar) + ‘毫秒‘

print ‘ ‘
print ‘ ‘


set @i=0
set @s=getdate()
print ‘GUID(聚簇索引)类型为主键的表插入开始时间：‘ + cast(@s as nvarchar)
while @i<10000
begin
    set @v=rand()
    insert into tabUnq ([myValue]) values (@v)
    set @i=@i+1
end

set @e =getdate()
print ‘GUID类型(聚簇索引)为主键的表插入结束时间：‘ + cast(@e as nvarchar)
print ‘GUID类型(聚簇索引)为主键的表插入共用时间：‘+ cast (DATEDIFF(ms, @s, @e) as nvarchar) + ‘毫秒‘

print ‘ ‘
print ‘ ‘


set @i=0
set @s=getdate()
print ‘GUID(非聚簇索引)类型为主键的表插入开始时间：‘ + cast(@s as nvarchar)
while @i<10000
begin
    set @v=rand()
    insert into tabUnq2 ([myValue]) values (@v)
    set @i=@i+1
end

set @e =getdate()
print ‘GUID类型(非聚簇索引)为主键的表插入结束时间：‘ + cast(@e as nvarchar)
print ‘GUID类型(非聚簇索引)为主键的表插入共用时间：‘+ cast (DATEDIFF(ms, @s, @e) as nvarchar) + ‘毫秒‘

print ‘ ‘
print ‘ ‘


DECLARE @aGuid UNIQUEIDENTIFIER


set @i=0
set @s=getdate()
print ‘COMB类型为主键的表插入开始时间：‘ + cast(@s as nvarchar)
while @i<10000
begin
    SET @aGuid = CAST(CAST(NEWID() AS BINARY(10)) + CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER)
    set @v=rand()
    insert into tabComb ([ID],[myValue]) values (@aGuid,@v)
    set @i=@i+1
end

set @e =getdate()
print ‘COMB类型为主键的表插入结束时间：‘ + cast(@e as nvarchar)
print ‘COMB类型为主键的表插入共用时间：‘ + cast (DATEDIFF(ms, @s, @e) as nvarchar) + ‘毫秒‘

建立了4个表，分别用int（聚簇索引），GUID（聚簇索引），GUID（聚簇索引），以及COMB类型来做为主键
然后分别插入10000条数据
结果如下：
int类型为主键的表插入开始时间：07 19 2004  2:14PM
int类型为主键的表插入结束时间：07 19 2004  2:14PM
int类型为主键的表插入共用时间：7750毫秒
GUID(聚簇索引)类型为主键的表插入开始时间：07 19 2004  2:14PM
GUID类型(聚簇索引)为主键的表插入结束时间：07 19 2004  2:14PM
GUID类型(聚簇索引)为主键的表插入共用时间：8193毫秒
GUID(非聚簇索引)类型为主键的表插入开始时间：07 19 2004  2:14PM
GUID类型(非聚簇索引)为主键的表插入结束时间：07 19 2004  2:14PM
GUID类型(非聚簇索引)为主键的表插入共用时间：7540毫秒
COMB类型为主键的表插入开始时间：07 19 2004  2:14PM
COMB类型为主键的表插入结束时间：07 19 2004  2:14PM
COMB类型为主键的表插入共用时间：7880毫秒
我们会看到，在这里，采用GUID来做主键（非聚簇索引），它的速度是最快的
当然，采用非聚簇索引也有不方便的地方，那就是不能排序了。
MSDN中对于非聚簇索引的应用场景建议如下： 包含大量非重复值的列，如姓氏和名字的组合（如果聚集索引用于其它列）。如果只有很少的非重复值，如只有 1 和 0，则大多数查询将不使用索引，因为此时表扫描通常更有效。 不返回大型结果集的查询。 返回精确匹配的查询的搜索条件（WHERE 子句）中经常使用的列。 经常需要联接和分组的决策支持系统应用程序。应在联接和分组操作中使用的列上创建多个非聚集索引，在任何外键列上创建一个聚集索引。 在特定的查询中覆盖一个表中的所有列。这将完全消除对表或聚集索引的访问。
而对于聚簇索引，建议的应用场景如下：
包含大量非重复值的列。 使用下列运算符返回一个范围值的查询：BETWEEN、>、>=、< 和 <=。 被连续访问的列。 返回大型结果集的查询。 经常被使用联接或 GROUP BY 子句的查询访问的列；一般来说，这些是外键列。对 ORDER BY 或 GROUP BY 子句中指定的列进行索引，可以使 SQL Server 不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能。 OLTP 类型的应用程序，这些程序要求进行非常快速的单行查找（一般通过主键）。应在主键上创建聚集索引。
所以，在这里，如果只是为了需要一个唯一标示符来做为主键，并且在这个字段上不需要做什么排序呀，范围对比等操作的话，那么我们可以放心大胆的使用GUID来做为主键，但是记得要把它设置成为非聚簇索引。
Feedback
# re: 反驳 吕震宇的“小议数据库主键选取策略（原创）”  回复  更多评论
2004-07-19 15:59 bykwklover
夷～～
关注，但两片文章都没有细看，不过有争议是好事哦!
# re: 反驳 吕震宇的“小议数据库主键选取策略（原创）”  回复  更多评论
2004-07-19 16:12 by
我认为还是手动的int很好，性能损失几乎可以不用考虑。
# re: 反驳 吕震宇的“小议数据库主键选取策略（原创）”  回复  更多评论
2004-07-19 16:23 bytwodays
手动用int型的话，正如吕震宇所说，需要再次查询数据库，会影响心能。。。。
这还不是主要的。。。
主要的是，为了优化数据库，必须要在表上加上索引，那么你的索引是什么样子的也对你的数据库性能有很大影响，这也是我这篇帖子想要说明的一个问题
# re: 反驳 吕震宇的“小议数据库主键选取策略（原创）”  回复  更多评论
2004-07-19 16:26 byscript
哦，下两篇仔细看看，有争鸣是最好的
# re: 反驳 吕震宇的“小议数据库主键选取策略（原创）”  回复  更多评论
2004-07-19 16:27 by
呵呵，雷俊强这么说没有什么理由，只是“觉得”。
顶楼做了测试，帖了数据，看来都是白做了。
# re: 反驳 吕震宇的“小议数据库主键选取策略（原创）”  回复  更多评论
2004-07-19 18:27 byunruledboy（灵感之源）
我不认为该数据是正确的。
# re: 反驳 吕震宇的“小议数据库主键选取策略（原创）”  回复  更多评论
2004-07-19 20:21 by
我也怀疑过
然后把rand()拿掉了　直接插入1
并且用set nocount off
可是事实上　COMB　还是比GUID　快
见鬼了
# re: 反驳 吕震宇的“小议数据库主键选取策略（原创）”  回复  更多评论
2004-07-19 21:52 byunruledboy（灵感之源）
我觉得应该是速度int>COMB>GUID。
# re: 反驳 吕震宇的“小议数据库主键选取策略（原创）”  回复  更多评论
2004-07-19 21:56 bytwodays
奇怪。。。我也郁闷了。。。。。
刚才看到上面两位说数据可能有问题
我就在家里的机器上面又重新做实验
结果。。。正如progame所说。。。。
COMB始终是比GUID快
-___-
我甚至后来将tabunq2中定义里面的ID列的缺省值去掉
然后在插入数据的时候制定一个GUID来插进去居然还是比COMB慢。。。。
我实在是想不通了。。。。。。
难道今天中午的数据是因为在公司，机器的环境不稳定造成的？
等我明天去公司在试试看看。。。。。。。
# re: 反驳 吕震宇的“小议数据库主键选取策略（原创）”  回复  更多评论
2004-07-20 08:46 bytwodays
今天早上在公司的运行结果：
int类型为主键的表插入开始时间：07 20 2004  8:48AM
int类型为主键的表插入结束时间：07 20 2004  8:48AM
int类型为主键的表插入共用时间：7210毫秒
GUID(聚簇索引)类型为主键的表插入开始时间：07 20 2004  8:48AM
GUID类型(聚簇索引)为主键的表插入结束时间：07 20 2004  8:48AM
GUID类型(聚簇索引)为主键的表插入共用时间：10003毫秒
GUID(非聚簇索引)类型为主键的表插入开始时间：07 20 2004  8:48AM
GUID类型(非聚簇索引)为主键的表插入结束时间：07 20 2004  8:48AM
GUID类型(非聚簇索引)为主键的表插入共用时间：5056毫秒
COMB类型为主键的表插入开始时间：07 20 2004  8:48AM
COMB类型为主键的表插入结束时间：07 20 2004  8:48AM
COMB类型为主键的表插入共用时间：6360毫秒
还是非聚簇索引最快。。。。。奇怪。。。。为什么呢？
# re: 反驳 吕震宇的“小议数据库主键选取策略（原创）”  回复  更多评论
2004-07-20 08:51 bytwodays
第二次，我将4张表全部drop掉，然后重新建立表
这次居然COMB类型最快，其次GUID，最慢的是int型。。。
结果如下：
int类型为主键的表插入开始时间：07 20 2004  8:52AM
int类型为主键的表插入结束时间：07 20 2004  8:52AM
int类型为主键的表插入共用时间：6090毫秒
GUID(聚簇索引)类型为主键的表插入开始时间：07 20 2004  8:52AM
GUID类型(聚簇索引)为主键的表插入结束时间：07 20 2004  8:53AM
GUID类型(聚簇索引)为主键的表插入共用时间：6016毫秒
GUID(非聚簇索引)类型为主键的表插入开始时间：07 20 2004  8:53AM
GUID类型(非聚簇索引)为主键的表插入结束时间：07 20 2004  8:53AM
GUID类型(非聚簇索引)为主键的表插入共用时间：5596毫秒
COMB类型为主键的表插入开始时间：07 20 2004  8:53AM
COMB类型为主键的表插入结束时间：07 20 2004  8:53AM
COMB类型为主键的表插入共用时间：5260毫秒
# re: 反驳 吕震宇的“小议数据库主键选取策略（原创）”  回复  更多评论
2004-07-20 09:01 by
是否可以这样理解
做10000次的comb生成 自增字段获取 guid生成　都是非常快的
这种速度损耗相当于insert的编译　执行都是差了几个数据级的
是否可以这样测试：
使用存储过程　并开启事务，
对于上述表做100000次的insert操作
# re: 反驳 吕震宇的“小议数据库主键选取策略（原创）”  回复  更多评论
2004-07-20 09:02 bytwodays
这次我将表先Drop掉
然后重新建立
然后将运算次数延长到100000，结果还是非聚簇式GUID最快
drop table tabint
drop table tabunq
drop table tabunq2
drop table tabcomb


CREATE TABLE [tabInt] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [myValue] [int] NULL ,
    CONSTRAINT [PK_tabInt] PRIMARY KEY  CLUSTERED
    (
        [ID]
    )  ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [tabUnq] (
    [ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tabUnq_ID] DEFAULT (newid()),
    [myValue] [int] NULL ,
    CONSTRAINT [PK_tabUnq] PRIMARY KEY  CLUSTERED
    (
        [ID]
    )  ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [tabComb] (
    [ID] [uniqueidentifier] NOT NULL ,
    [myValue] [int] NULL ,
    CONSTRAINT [PK_tabComb] PRIMARY KEY  CLUSTERED
    (
        [ID]
    )  ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [tabUnq2] (
    [ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tabUnq2_ID] DEFAULT (newid()),
    [myValue] [int] NULL ,
    CONSTRAINT [PK_tabUnq2] PRIMARY KEY  NONCLUSTERED
    (
        [ID]
    )  ON [PRIMARY]
) ON [PRIMARY]
GO
set nocount on
declare @i bigint
declare @v int
declare @s datetime
declare @e datetime
set @i=0
set @s=getdate()
print ‘int类型为主键的表插入开始时间：‘ + cast(@s as nvarchar)
while @i<100000
begin
    set @v=rand()
    insert into tabInt ([myValue]) values (@v)
    set @i=@i+1
end

set @e =getdate()
print ‘int类型为主键的表插入结束时间：‘ + cast(@e as nvarchar)
print ‘int类型为主键的表插入共用时间：‘ + cast (DATEDIFF(ms, @s, @e) as nvarchar) + ‘毫秒‘

print ‘ ‘
print ‘ ‘


set @i=0
set @s=getdate()
print ‘GUID(聚簇索引)类型为主键的表插入开始时间：‘ + cast(@s as nvarchar)
while @i<100000
begin
    set @v=rand()
    insert into tabUnq ([myValue]) values (@v)
    set @i=@i+1
end

set @e =getdate()
print ‘GUID类型(聚簇索引)为主键的表插入结束时间：‘ + cast(@e as nvarchar)
print ‘GUID类型(聚簇索引)为主键的表插入共用时间：‘+ cast (DATEDIFF(ms, @s, @e) as nvarchar) + ‘毫秒‘

print ‘ ‘
print ‘ ‘


set @i=0
set @s=getdate()
print ‘GUID(非聚簇索引)类型为主键的表插入开始时间：‘ + cast(@s as nvarchar)
while @i<100000
begin
    set @v=rand()
    insert into tabUnq2 ([myValue]) values (@v)
    set @i=@i+1
end

set @e =getdate()
print ‘GUID类型(非聚簇索引)为主键的表插入结束时间：‘ + cast(@e as nvarchar)
print ‘GUID类型(非聚簇索引)为主键的表插入共用时间：‘+ cast (DATEDIFF(ms, @s, @e) as nvarchar) + ‘毫秒‘

print ‘ ‘
print ‘ ‘


DECLARE @aGuid UNIQUEIDENTIFIER


set @i=0
set @s=getdate()
print ‘COMB类型为主键的表插入开始时间：‘ + cast(@s as nvarchar)
while @i<100000
begin
    SET @aGuid = CAST(CAST(NEWID() AS BINARY(10)) + CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER)
    set @v=rand()
    insert into tabComb ([ID],[myValue]) values (@aGuid,@v)
    set @i=@i+1
end

set @e =getdate()
print ‘COMB类型为主键的表插入结束时间：‘ + cast(@e as nvarchar)
print ‘COMB类型为主键的表插入共用时间：‘ + cast (DATEDIFF(ms, @s, @e) as nvarchar) + ‘毫秒‘

最后结果是：
int类型为主键的表插入开始时间：07 20 2004  8:55AM
int类型为主键的表插入结束时间：07 20 2004  8:56AM
int类型为主键的表插入共用时间：48170毫秒
GUID(聚簇索引)类型为主键的表插入开始时间：07 20 2004  8:56AM
GUID类型(聚簇索引)为主键的表插入结束时间：07 20 2004  8:57AM
GUID类型(聚簇索引)为主键的表插入共用时间：40016毫秒
GUID(非聚簇索引)类型为主键的表插入开始时间：07 20 2004  8:57AM
GUID类型(非聚簇索引)为主键的表插入结束时间：07 20 2004  8:57AM
GUID类型(非聚簇索引)为主键的表插入共用时间：34230毫秒
COMB类型为主键的表插入开始时间：07 20 2004  8:57AM
COMB类型为主键的表插入结束时间：07 20 2004  8:58AM
COMB类型为主键的表插入共用时间：34570毫秒
# re: 反驳 吕震宇的“小议数据库主键选取策略（原创）”  回复  更多评论
2004-07-20 11:38 by吕震宇
再议《反驳 吕震宇的“小议数据库主键选取策略（原创）” 》
# re: 反驳 吕震宇的“小议数据库主键选取策略（原创）”  回复  更多评论
2005-04-01 23:20 by
cluster的非自动递增字段,只是insert变慢,但在select上则是快了一个数据量级,如果你的插入速度比select速度还重要,那就不要做任何索引了.
# re: 反驳 吕震宇的“小议数据库主键选取策略（原创）”  回复  更多评论
2005-08-09 08:44 by
何必呢，在原来那篇文章里用第一种方法返回@@Identifier然后在同一个存储过程里面跟着插入OrderDetailes不就得了，一次数据库交互，省事业挺快。
# re: 反驳 吕震宇的“小议数据库主键选取策略（原创）”  回复  更多评论
2005-12-19 11:04 byenjoyo
Integer GUID和Comb做主键的效率测试(Delphi+access)
http://sinoprise.com/read.php?tid=859&fpage=1
# re: 反驳 吕震宇的“小议数据库主键选取策略（原创）”  回复  更多评论
2006-03-23 03:20 by
在sql server 里面 比较 GUID类型的方式 之比较最后的六位 也就是说comb实际上比GUID方式有更好的数据顺序（时间顺序）
反过来说 实际上 上面的代码中比较的数据不是很公平的
GUID 方式生成的数据比COMB方式生成的数据更混乱那么sql server当然好花费更多的时间去干活了 呵呵
所以不是谁快的问题 而是GUID 的数据更混乱 当然不好管理啦
结论：选取一个PK的话我是不会选取一个乱序的序列的 ：-）
# re: 反驳 吕震宇的“小议数据库主键选取策略（原创）”  回复  更多评论
2006-03-24 13:31 by被遗弃的小指
to 素食：
看样子你还是没有理解什么叫做聚簇索引和非聚簇索引，呵呵~