SQL Server 事务表是否应该始终具有代理主键

Question

对于已经有主键（4列的自然组合键）的大型事务表（1亿行，20GB），添加标识列并将其作为主键是否有助于性能？

当前主键（4 列的自然复合主键）可以完成这项工作，但我被告知您应该始终有一个代理键。 那么，可以通过创建标识列并将其设为主键来提高性能吗？

我正在使用 SQL Server 2008 R2 数据库。

编辑：此事务表主要连接到定义表并用于填充报告。

编辑：如果我确实添加了代理键，它就不会在任何连接中使用。将使用现有的关键字段。

编辑：该表不会有子表

Answer 1

仅添加 IDENTITY 列并为其添加新的约束和索引不太可能提高性能。该表会更大，因此扫描和查找可能需要更长的时间。还将有更多索引需要更新。当然，这完全取决于您要衡量的性能...以及您在添加新列时是否打算对代码或数据库进行其他更改。添加 IDENTITY 列而不执行其他操作可能是不明智的。

Answer 2

仅当：

• 您有更大的子表
• 您有非聚集索引

在每种情况下，表的 PK（假设聚集）将位于每个子条目/NC 条目中。因此，使聚集键更窄将会受益。

如果您只有非 NC 索引（也许有一个）并且没有子表，那么您将获得的只是

• 更宽的行（使用更多数据页）、
• 稍小的 B 树（仅占总空间的一小部分）

...但是您无论如何，仍然需要当前 4 列上的索引/约束 = 空间的增加。

如果您的 4 向键也捕获父表键（听起来很可能），那么您将失去重叠的优势。不过，新的索引/约束将涵盖这一点。

所以不，你可能不想这样做。

我们抛弃了超过 10 亿行的表上的代理键 (bigint)，转而使用实际的 11 向键，由于结构更简单（少一个索引、每页稍微多一些行等），磁盘空间减少了 65% 以上

Answer 3

鉴于您的编辑以及该问题引发的所有对话，我建议在此表中添加 IDENTITY 列将弊大于利。

Answer 4

性能受到损害的一个地方是自然键中数据的更改。然后，更改必须发布到所有子记录。例如，假设其中一个字段是公司名称，并且该公司更改了名称，那么所有相关记录（可能有数百万条）都必须更改，但如果您使用代理键，则只需更改一条记录改变。整数连接往往更快（通常比 4 列连接快得多），并且编写连接代码通常也更快。然而，另一方面，拥有重要的四个字段可能意味着不需要经常进行连接。插入性能将受到轻微影响，并且必须生成代理键并建立索引。通常，这种影响很小以至于不会引起注意，但可能性是存在的。

四列自然键通常并不像您想象的那样是唯一的，因为数据的列数往往会随着时间的推移而变化。虽然它现在是独一无二的，但随着时间的推移它会是独一无二的吗？如果您在自然键上使用了代理键和唯一索引，但后来发现它不是唯一的，那么您所要做的就是删除唯一索引。如果是PK并且有子表，则必须完全重新设计数据库。

只有您可以决定，如果这些考虑因素中的任何一个影响您的特定数据需求，则代理键对于某些应用程序更好，而对于其他应用程序则更差。

Answer 5

---编辑：
根据对问题的编辑，添加身份/代理键可能无法解决此问题。

--原始答案。

性能改进的一种情况是当您使用联接并且具有子表时。

如果没有代理键，您必须将所有 th4 4 个键复制到子表并连接 4 列。

t_parent
-------------
col1,
col2,
col3,
col4,
col5,
constraint pk_t_parent primary key (col1,col2,col3,col4)

t_child
----------
col1,
col2,
col3,
col4,
col7,
col8,
constraint pk_t_child primary key (col1,col2,col3,col4, col5),
constraint fk_parent_child foreign key (col1, col2, col3, col4) references
                                 t_parent ((col1, col2, col3, col4))

连接将包括所有 4 列。

select t2.*
  from t_parent t1, t_child t2
  where (t1.col1 = t2.col1 and 
         t1.col2 = t2.col2 and 
         t1.col3 = t2.col3 and 
         t1.col4 = t2.col4
        )

如果您使用代理键并在 4 列（现在是主键的一部分）上创建唯一约束，那么它将非常高效，并且数据仍将像以前一样进行验证。