表索引性能。关于选择主键的选项
我对选择表的主键有疑问。 假设我们有 3 个相关表,如下所述:
TBL_A
{
'A1' varchar (64), // assume this is the unique attribute.
'A2' varchar (64),
}
TBL_B
{
'B1' varchar (64), // assume this is the unique attribute.
'B2' varchar (64),
}
TBL_C
{
'C1' varchar (64), // assume this is the unique attribute.
'C2' varchar (64),
}
关系:
- TBL_A 和 TBL_B 之间为多对多,
- TBL_AB 和 TBL_C 之间为一对多
问题是:
哪个更好: 选项 A:设置 的唯一属性每个表都要成为表的主键?或选项B:创建另一个属性作为id(自动编号)并使用唯一约束设置表的唯一属性。? (将应用于所有这三个表)。
然后 TBL_AB 将需要从 TBL_A 和 TBL_B 复制主键作为引用键。问题又像我之前的问题一样,我们是否应该将这两个引用的键保留为 TBL_AB 的主键?或者最好创建一个新的唯一属性(自动编号)作为 TBL_AB 的主键,并使两个引用的属性具有唯一约束。?
因为od TBL_C将引用TBL_AB的主键,所以我们在前两个问题中选择的选项当然会生效。如果我们选择第一个选项,将有两个引用的属性,但如果我们选择第二个选项,我们将只有一个引用的属性。你觉得怎么样,.?
这个想法是,当我们处于搜索情况时,查询整数(或数字)类型主键会比查询 varchar 类型主键执行得更快吗? 哪一个更好,.?当然,如果你知道“为什么”。
感谢您的每一个回答和建议。 问候,
i have questions about choosing a table's primary key.
assume that we have 3 related tables as described below:
TBL_A
{
'A1' varchar (64), // assume this is the unique attribute.
'A2' varchar (64),
}
TBL_B
{
'B1' varchar (64), // assume this is the unique attribute.
'B2' varchar (64),
}
TBL_C
{
'C1' varchar (64), // assume this is the unique attribute.
'C2' varchar (64),
}
relationships:
- between TBL_A and TBL_B as Many to Many,
- between TBL_AB and TBL_C as ONE to Many
the questions are:
which one is better: OPTION A: set the unique attribute of each table to be the table's primary key? or OPTION B: create another attribute to be an id (auto number) and set the table's unique attribute with unique constraint,.? (will be applied to all those three tables).
then TBL_AB will need to copy both primary key from TBL_A and TBL_B as referenced key. the question is again like my previous one, should we keep those two referenced key to be TBL_AB's primary key,.? or it is much better to create a new unique attribute (auto number) to be the TBL_AB's primary key and make the two referenced attributes with unique constraint,.?
because od TBL_C will refers to TBL_AB's primary key, of course the option we choose in the two previous questions will take effect. if we choose the first option, there will be two referenced attributes, but if we choose the second option we will have only one referenced attribute. what do you think,.?
the idea is when we are in a search situation, will querying an integer (or numeric) type primary key perform faster than varchar type primary key,.?
which one is better,.? and of course if you have the 'why',.
thank you for every answer and suggestion,.
regards,
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
我不会太担心主键是 varchar。使用自动编号作为主键的单独字段时的一点点速度增加(如果有的话)可以通过现在需要将 varchar 添加到索引来抵消,或者如果没有,则每次查找都需要去访问表当你需要的时候获取 varchar 。
除非您的表有数百万条记录,否则只需按照自然方式设置主键即可。
I wouldn't worry too much about the primary key being a varchar. The little speed increase (if any) in using an auto number as a separate field for primary key could be offset by now needing to either add the varchar to the index, or if not, each lookup would need to go and access the table to get the varchar when you need it.
Unless your tables are millions of records, just go the natural way and set the primary keys as what they are.