INT和VARCHAR主键之间有真正的性能差异吗?

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

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

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

当前回答

这个问题是关于MySQL的，所以我说有很大的区别。如果是关于Oracle(它将数字存储为字符串——是的，一开始我不敢相信)，那么没有太大区别。

表中的存储不是问题，更新和引用索引才是问题。涉及基于主键查找记录的查询非常频繁——您希望它们尽可能快地出现，因为它们经常发生。

问题是CPU自然地处理4字节和8字节的整数，在硅中。它比较两个整数真的很快——它发生在一个或两个时钟周期内。

现在看一个字符串——它由很多字符组成(现在每个字符超过一个字节)。比较两个字符串的优先级不能在一个或两个循环中完成。相反，字符串的字符必须迭代，直到发现差异。我确信在某些数据库中有一些技巧可以让它更快，但在这里这无关紧要，因为在硅中，CPU会自然地进行int比较，并且速度很快。

我的一般规则-每个主键都应该是一个自动递增的INT，特别是在使用ORM (Hibernate, Datanucleus，等等)的OO应用程序中，对象之间有很多关系-它们通常总是作为一个简单的FK实现，DB快速解析这些关系的能力对你的应用程序很重要。响应性。

其他回答

代理AUTO_INCREMENT有害的常见情况:

常见的模式模式是多对多映射:

CREATE TABLE map (
    id ... AUTO_INCREMENT,
    foo_id ...,
    bar_id ...,
    PRIMARY KEY(id),
    UNIQUE(foo_id, bar_id),
    INDEX(bar_id) );

这种模式的性能要好得多，特别是在使用InnoDB时:

CREATE TABLE map (
    # No surrogate
    foo_id ...,
    bar_id ...,
    PRIMARY KEY(foo_id, bar_id),
    INDEX      (bar_id, foo_id) );

Why?

InnoDB二级键需要额外的查找;通过将配对移动到PK中，这在一个方向上是避免的。二级索引是“覆盖”的，因此不需要额外的查找。这个表变小了，因为去掉了id和一个索引。

另一个案例(国家):

country_id INT ...
-- versus
country_code CHAR(2) CHARACTER SET ascii

新手经常将country_code规范化为4字节INT，而不是使用“自然的”2字节，几乎不变的2字节字符串。更快、更小、更少的join，更可读。

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

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

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

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

这个问题是关于MySQL的，所以我说有很大的区别。如果是关于Oracle(它将数字存储为字符串——是的，一开始我不敢相信)，那么没有太大区别。

表中的存储不是问题，更新和引用索引才是问题。涉及基于主键查找记录的查询非常频繁——您希望它们尽可能快地出现，因为它们经常发生。

问题是CPU自然地处理4字节和8字节的整数，在硅中。它比较两个整数真的很快——它发生在一个或两个时钟周期内。

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

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

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

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

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

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

推荐文章