代码之家 › 专栏 › 技术社区 › Andy White

SQL-多对多表主键

many-to-many primary-key sql

111

Andy White · 技术社区 · 16 年前

此问题在阅读此问题中的评论后出现:

Database Design

当您创建多对多表时,您应该在两个外键列上创建一个复合主键,还是创建一个自动递增的代理项“id”主键,并只在两个FK列上放置索引(可能是唯一约束)?在每种情况下,插入新记录/重新索引对性能有什么影响?

基本上,这是:

PartDevice
----------
PartID (PK/FK)
DeviceID (PK/FK)

与此相比:

PartDevice
----------
ID (PK/auto-increment)
PartID (FK)
DeviceID (FK)

评论者说:

使这两个ID成为pk意味着表在磁盘上物理排序按这样的顺序。所以如果我们插入 (第1部分/设备1),(第1部分/设备2) (第2部分/设备3),然后(第1部分/设备3) 数据库必须中断分开并插入最后一个在条目2和3之间。对于许多记录,这变得很有问题因为这涉及到数百人的洗牌, 数千或数百万条记录每次添加一个。相比之下, 自动增加的pk允许要附加到结尾的记录。

我之所以问这个问题,是因为我一直倾向于在没有代理自动递增列的情况下使用复合主键,但我不确定代理键是否实际上更具性能。

5 回复 | 直到 16 年前

paxdiablo 11 年前

使用一个简单的两列多对多映射,我认为拥有一个代理键没有真正的优势。打开主键 (col1,col2) 保证独一无二(假设 col1 和 col2 引用表中的值是唯一的)和 (col2,col1) 将捕获执行相反顺序更快的情况。代理是浪费空间。

您将不需要对单个列进行索引,因为该表只应用于将两个引用的表连接在一起。

在我看来,你在这个问题中提到的评论不值得它使用电子。听起来好像作者认为表存储在一个数组中,而不是一个非常高性能的平衡多路径树结构。

首先,不需要存储或访问桌子排序,只是索引。指数不会是存储按顺序,它将以有效的方式存储,以便能够快速检索。

此外,绝大多数数据库表都是读取的远的比写的更频繁。这使得您在选择端所做的任何事情都比在插入端所做的任何事情更相关。

Community Mohan Dere 9 年前

链接表不需要代理键。

您只需要在(col1,col2)上使用一个pk,在(col2,col1)上使用另一个唯一索引。

除非您使用的ORM无法应付并为您指定DB设计…

编辑:我在这里回答了同样的问题: SQL: Do you need an auto-incremental primary key for Many-Many tables?

Jronny 14 年前

如果引用了表,则可能需要增量主键。在多对多表中可能有一些细节需要使用增量主键从另一个表中提取出来。

例如

PartDevice
----------
ID (PK/auto-increment)
PartID (FK)
DeviceID (FK)
Other Details

使用partdevice.id作为fk很容易提取“其他详细信息”。因此需要使用增量主键。

Bernhard Hofmann 16 年前

我能回答您问题的最短和最直接的方法是说,如果您所链接的两个表没有连续的主键,则会对性能产生影响。正如您所说/所引用的,链接表的索引要么变得支离破碎,要么如果链接表没有自己的顺序主键,DBMS将更加努力地插入记录。这就是大多数人将按顺序递增的主键放在链接表上的原因。

michael kosak 7 年前

所以,如果唯一的任务是链接这两个表,那么最好的pk应该是双列pk。

但是,如果它有其他用途,那么添加另一个ndx作为带有外键和第二个唯一索引的pk。

索引或pk是确保没有重复项的最佳方法。pk允许像Microsoft Management Studio这样的工具为您完成一些工作(创建视图)。