我有两个表
- 用户 - 角色
我正在创建一个新表
- UserRole 它有两个字段 RoleId 和 UserID
现在我应该:
- 创建UserRoleID作为PK,并将UserID和RoleID设置为FK - 将PK设置为UserID和RoleID,并将它们设置为FK - 其他
我想知道每个选项的性能问题以及推荐的最佳实践是什么。
对于这些情况的标准程序是使用两个索引。唯一的主键是由两个字段组成的复合主键,第一个字段是基数较大的字段,即UserID;第二个索引只包含第二个字段,即RoleID。
然后根据可能涉及更多多记录结果集的字段进行聚簇(例如,如果查询每个用户的多个角色或每个角色的多个用户)。
将PK声明为(UserID, RoleID)。(注意:顺序很重要)
声明UserID作为一个外键,引用到Users表。 声明RoleID作为一个外键,引用到Roles表。
幸运的是,您的DBMS会按照(UserID, RoleID)的顺序给您提供一个复合索引。
有了这个复合索引,可以加速用户和角色之间的连接。对于没有限制条件的连接,一个好的DBMS会给您提供一种合并连接。如果角色数量很少,三向连接的速度也应该很快。
当您在不加入用户连接的情况下连接UserRoles和Roles时,可能会发现速度令人失望。在这种情况下,执行此操作的频率有多高?速度有多重要?如果很重要,您可以只在RoleID上创建一个索引。
这取决于您是否希望将特定用户具有特定角色的事实赋予其他含义。如果没有,那么只需创建一个跨越两个字段的聚集主键。
为两个字段添加外键并在第二个字段上添加索引。请考虑字段出现的顺序。您更可能检索用户所属角色的一组角色还是特定角色中的一组用户?
这取决于你如何使用它们。大多数情况下,我将主键设置为UserId和RoleId,以确保它们是唯一的。这意味着同一用户不能拥有相同的角色。
现在这就是“取决于”的地方。如果您要将UserRole表链接到另一个表,则需要创建UserRoleId主键。并将UserId和RoleId设置为唯一约束。
之所以这样做,是因为在不需要链接到用户表和角色表的情况下,不希望在引用UserRole的表上同时具有UserId和RoleId。