在MySQL中使用INT和VARCHAR作为主键之间有可测量的性能差异吗?我想使用VARCHAR作为参考列表的主键(认为美国州,国家代码)和同事不会在INT AUTO_INCREMENT作为所有表的主键上让步。

我的论点是,INT和VARCHAR之间的性能差异可以忽略不计,因为每个INT外键引用都需要一个JOIN来理解引用,VARCHAR键将直接显示信息。

那么,有人对这个特殊的用例以及与之相关的性能问题有过经验吗?


当前回答

不确定性能的影响,但它似乎是一个可能的妥协,至少在开发过程中,将包括自动递增的整数“代理”键,以及您预期的唯一的“自然”键。这将使您有机会评估性能以及其他可能的问题,包括自然键的可变性。

其他回答

对于短代码,可能没有区别。当保存这些代码的表可能非常小(最多几千行)并且不经常更改(我们上一次添加新的US State是什么时候)时,这一点尤其正确。

对于键之间变化较大的大型表,这可能是危险的。例如,考虑使用user表中的电子邮件地址/用户名。如果你有几百万用户,其中一些用户有很长的名字或电子邮件地址,会发生什么?现在,任何时候你需要使用这个键来连接这个表,它就变得非常昂贵。

您提出了一个很好的观点,即可以通过使用所谓的自然键而不是代理键来避免一些连接查询。只有您才能评估这样做的好处在您的应用程序中是否显著。

也就是说,您可以测量应用程序中对快速最重要的查询,因为它们处理大量数据或执行非常频繁。如果这些查询可以从消除连接中获益,并且不会因使用varchar主键而遭受损失,那么就这样做。

不要对数据库中的所有表使用这两种策略。在某些情况下,天然键可能更好,但在其他情况下,替代键可能更好。

另一些人提出了一个很好的观点,即在实践中,自然键很少永远不会更改或有重复,因此代理键通常是值得的。

这与性能无关。这是关于什么是一个好的主键。独一无二且随时间不变。您可能认为国家代码之类的实体永远不会随着时间而改变,并且是主键的良好候选者。但痛苦的经验是,这种情况很少发生。

INT AUTO_INCREMENT满足“唯一且随时间不变”的条件。因此才会有偏好。

取决于长度..如果varchar是20个字符,而int是4,那么如果你使用int类型,你的索引在磁盘上每页索引空间的节点数将是原来的5倍……这意味着遍历索引将需要五分之一的物理和/或逻辑读取。

因此,如果性能是一个问题,如果有机会,总是为您的表使用一个整体的无意义键(称为代理),对于引用这些表中的行的外键……

同时,为了保证数据一致性,每个重要的表也应该有一个有意义的非数字替代键(或唯一索引),以确保不能插入重复的行(基于有意义的表属性的重复)。

对于您所谈论的特定用途(如状态查找),这真的无关紧要,因为表的大小是如此之小。一般来说,在小于几千行的表上建立索引对性能没有影响……

和往常一样,没有统一的答案。“这取决于!我不是在开玩笑。我对原始问题的理解是小表上的键-像Country(整数id或char/varchar代码)是潜在的大表(如地址/联系表)的外键。

当您希望从DB返回数据时,这里有两种场景。首先是一个列表/搜索类型的查询,其中您希望列出所有带有州和国家代码或名称的联系人(id没有帮助,因此需要查找)。另一个是在主键上的get场景,它显示单个联系人记录,其中需要显示州名和国家。

For the latter get, it probably does not matter what the FK is based on since we are bringing together tables for a single record or a few records and on key reads. The former (search or list) scenario may be impacted by our choice. Since it is required to show country (at least a recognizable code and perhaps even the search itself includes a country code), not having to join another table through a surrogate key can potentially (I am just being cautious here because I have not actually tested this, but seems highly probable) improve performance; notwithstanding the fact that it certainly helps with the search.

由于代码很小——国家和州通常不超过3个字符,在这种情况下使用自然键作为外键是可以的。

另一种情况是,键依赖于较长的varchar值,也可能依赖于较大的表;代理键可能具有优势。