sql - 哪种数据类型最适合通过事务复制发布的表的聚集索引？

Question

我们有一个将数据存储在SQL Server数据库中的应用程序。 (当前，我们支持SQL Server 2005及更高版本)。我们的数据库有400多个表。数据库的结构并不理想。最大的问题是，我们有很多带有GUID(NEWID())作为主聚集键的表。当我问我们的主要数据库架构师“为什么？”时，他说:“这是因为复制”。我们的数据库应支持事务复制。最初，所有主键都是INT IDENTITY(1,1)簇。但是后来涉及复制支持时，该字段被UNIQUEIDENTIFIER DEFAULT NEWID()替换。他说:“否则，处理复制是一场噩梦”。当时SQL 7/2000不支持NEWSEQUENTIALID()。现在，我们有了具有以下结构的表:

CREATE TABLE Table1(
        Table1_PID uniqueidentifier DEFAULT NEWID() NOT NULL,
        Field1 varchar(50) NULL,
        FieldN varchar(50) NULL,
        CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED (Table1_PID)
    )
    GO

CREATE TABLE Table2(
    Table2_PID uniqueidentifier DEFAULT NEWID() NOT NULL,
    Table1_PID uniqueidentifier NULL,
    Field1 varchar(50) NULL,
    FieldN varchar(50) NULL,
    CONSTRAINT PK_Table2 PRIMARY KEY CLUSTERED (Table2_PID),
    CONSTRAINT FK_Table2_Table1 FOREIGN KEY (Table1_PID) REFERENCES Table1 (Table1_PID)
)
GO

实际上，所有表都具有很多字段(最多35个)和最多15个非聚集索引。

我知道一个非顺序的GUID-就像在客户端(使用.NET)中生成其值或通过NEWID()SQL函数(例如在我们的情况下)生成的GUID一样，对于它的聚集索引而言，这是非常糟糕的选择两个原因:

碎片

大小

我也知道，良好的聚类键是:

唯一，

狭窄，

静态，

不断增加，

不可为空，

和定宽

有关其背后原因的更多详细信息，请观看以下精彩视频: http://technet.microsoft.com/en-us/sqlserver/gg508879.aspx。

因此，INT IDENTITY确实是最佳选择。 BIGINT IDENTITY也很好，但是通常对于大多数表来说，具有2+十亿行的INT就足够了。

当我们的客户开始遭受碎片困扰时，决定将主键设置为非群集。结果，这些表仍然没有聚簇索引。换句话说，这些表已变成HEAPS。我个人不喜欢这种解决方案，因为我确信堆表不是好的数据库设计的一部分。请检查此SQL Server最佳实践文章: http://technet.microsoft.com/en-us/library/cc917672.aspx。

当前，我们考虑两种选择来改善数据库结构:

第一个选项是用主集群键的DEFAULT NEWSEQUENTIALID()替换DEFAULT NEWID():

CREATE TABLE Table1_GUID (
  Table1_PID uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
  Field1 varchar(50) NULL,
  FieldN varchar(50) NULL,
  CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED (Table1_PID)
)
GO

第二个选项是将INT IDENTITY列添加到每个表中，并使其成为CLUSTERED UNIQUE索引，而主键未聚类。因此，Table1将如下所示:

CREATE TABLE Table1_INT (
  Table1_ID int IDENTITY(1,1) NOT NULL,
  Table1_PID uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
  Field1 varchar(50) NULL,
  FieldN varchar(50) NULL,
  CONSTRAINT PK_Table1 PRIMARY KEY NONCLUSTERED (Table1_PID),
  CONSTRAINT UK_Table1 UNIQUE CLUSTERED (Table1_ID)
)
GO

Table1_PID将用于复制(这就是我们将其保留为PK的原因)，而Table1_ID根本不会被复制。

长话短说，在运行基准测试以查看哪种方法更好之后，我们发现两种解决方案都不好:

第一种方法(Table1_GUID)揭示了以下缺点:尽管顺序GUID绝对比常规随机GUID好很多，但它们仍比INT(16字节对4字节)大4倍，这是我们的一个因素，因为我们的表中有很多行(最多6000万行)，并且该表上有很多非聚集索引(最多15个)。聚簇键被添加到每个非聚簇索引，因此显着增加了大小为16到4字节的负面影响。字节越多意味着磁盘上和SQL Server RAM中的页面越多，因此磁盘I/O越多，SQL Server的工作就越多。

更准确地说，在我向每个表中插入2500万行实际数据并在每个表上创建15个非聚集索引之后，我发现表所使用的空间存在很大差异:

EXEC sp_spaceused 'Table1_GUID' -- 14.85 GB
EXEC sp_spaceused 'Table1_INT' -- 11.68 GB

此外，测试表明，对Table1_GUID的插入比对Table1_INT的插入要慢一些。

第二种方法(Table1_INT)显示，在大多数查询(SELECT)中，将Table1_INT.Table1_PID = Table2_INT.Table1_PID上的两个表联接在一起的情况变得更糟，因为出现了附加的键查找运算符。

现在的问题是:我相信应该为我们的问题找到更好的解决方案。如果您可以向我推荐一些东西或为我提供好的资源，我将不胜感激。先感谢您。

更新了:

让我给您一个SELECT语句的示例，其中出现其他的Key Lookup运算符:

--Create 2 tables with int IDENTITY(1,1) as CLUSTERED KEY.
--These tables have one-to-many relationship.
CREATE TABLE Table1_INT (
    Table1_ID int IDENTITY(1,1) NOT NULL,
    Table1_PID uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
    Field1 varchar(50) NULL,
    FieldN varchar(50) NULL,
    CONSTRAINT PK_Table1_INT PRIMARY KEY NONCLUSTERED (Table1_PID),
    CONSTRAINT UK_Table1_INT UNIQUE CLUSTERED (Table1_ID)
)
GO

CREATE TABLE Table2_INT(
    Table2_ID int IDENTITY(1,1) NOT NULL,
    Table2_PID uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
    Table1_PID uniqueidentifier NULL,
    Field1 varchar(50) NULL,
    FieldN varchar(50) NULL,
    CONSTRAINT PK_Table2_INT PRIMARY KEY NONCLUSTERED (Table2_PID),
    CONSTRAINT UK_Table2_INT UNIQUE CLUSTERED (Table2_ID),
    CONSTRAINT FK_Table2_Table1_INT FOREIGN KEY (Table1_PID) REFERENCES Table1_INT (Table1_PID)
)
GO

并创建其他两个表格进行比较:

--Create the same 2 tables, BUT with uniqueidentifier NEWSEQUENTIALID() as CLUSTERED KEY.
CREATE TABLE Table1_GUID (
    Table1_PID uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
    Field1 varchar(50) NULL,
    FieldN varchar(50) NULL,
    CONSTRAINT PK_Table1_GUID PRIMARY KEY CLUSTERED (Table1_PID),
)
GO

CREATE TABLE Table2_GUID(
    Table2_PID uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
    Table1_PID uniqueidentifier NULL,
    Field1 varchar(50) NULL,
    FieldN varchar(50) NULL,
    CONSTRAINT PK_Table2_GUID PRIMARY KEY CLUSTERED (Table2_PID),
    CONSTRAINT FK_Table2_Table1_GUID FOREIGN KEY (Table1_PID) REFERENCES Table1_GUID (Table1_PID)
)
GO

现在运行以下选择语句，并查看执行计划进行比较:

SELECT T1.Field1, T2.FieldN
FROM Table1_INT T1 
    INNER JOIN Table2_INT T2 
        ON T1.Table1_PID = T2.Table1_PID;

SELECT T1.Field1, T2.FieldN
FROM Table1_GUID T1 
    INNER JOIN Table2_GUID T2 
        ON T1.Table1_PID = T2.Table1_PID;

Answer 1

我个人将INT IDENTITY用于大多数主键和群集键。

您需要分开主键，这是一个逻辑结构-它唯一地标识您的行，它必须是唯一且稳定的NOT NULL。 GUID对于主键也很有效-因为它保证是唯一的。如果使用SQL Server复制，则将GUID作为主键是一个不错的选择，因为在这种情况下，无论如何您都需要一个唯一标识的GUID列。

SQL Server中的群集键是用于对数据进行物理排序的物理结构，很难正确处理。通常，SQL Server上的索引皇后Kimberly Tripp还需要一个良好的群集 key ，以使其唯一，稳定，尽可能窄，并且在理想情况下不断增加(即INT IDENTITY)。

在这里查看她关于索引的文章:

GUIDs as PRIMARY KEYs and/or the clustering key

The Clustered Index Debate Continues...

Ever-increasing clustering key - the Clustered Index Debate..........again!

Disk space is cheap - that's not the point!

还可以看到吉米·尼尔森(Jimmy Nilsson)的 The Cost of GUIDs as Primary Key
GUID对于群集键来说确实是一个糟糕的选择，因为它很宽，完全是随机的，因此会导致糟糕的索引碎片和较差的性能。同样，集群键行也存储在每个非集群(附加)索引的每个条目中，因此您真的要保持较小的大小- GUID为16字节，而 INT为4字节，并且带有几个非聚集索引和几百万行，这带来了巨大的差异。

在SQL Server中，默认情况下，您的主键是集群键-但这不是必须的。您可以轻松地将 GUID用作您的非集群主键，并使用 INT IDENTITY作为您的集群键-只需一点了解即可。