在MySQL中使用INT和VARCHAR作为主键之间有可测量的性能差异吗?我想使用VARCHAR作为参考列表的主键(认为美国州,国家代码)和同事不会在INT AUTO_INCREMENT作为所有表的主键上让步。
我的论点是,INT和VARCHAR之间的性能差异可以忽略不计,因为每个INT外键引用都需要一个JOIN来理解引用,VARCHAR键将直接显示信息。
那么,有人对这个特殊的用例以及与之相关的性能问题有过经验吗?
在MySQL中使用INT和VARCHAR作为主键之间有可测量的性能差异吗?我想使用VARCHAR作为参考列表的主键(认为美国州,国家代码)和同事不会在INT AUTO_INCREMENT作为所有表的主键上让步。
我的论点是,INT和VARCHAR之间的性能差异可以忽略不计,因为每个INT外键引用都需要一个JOIN来理解引用,VARCHAR键将直接显示信息。
那么,有人对这个特殊的用例以及与之相关的性能问题有过经验吗?
当前回答
代理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,更可读。
其他回答
在HauteLook,我们将许多表改为使用自然键。我们确实体验到了真实世界的性能提升。正如您所提到的,我们的许多查询现在使用更少的连接,这使得查询的性能更高。如果有意义,我们甚至会使用复合主键。话虽如此,有些表如果有代理键就更容易使用。
另外,如果您让人们编写到您的数据库的接口,代理键可能会很有帮助。第三方可以依赖代理键只在非常罕见的情况下才会更改这一事实。
不确定性能的影响,但它似乎是一个可能的妥协,至少在开发过程中,将包括自动递增的整数“代理”键,以及您预期的唯一的“自然”键。这将使您有机会评估性能以及其他可能的问题,包括自然键的可变性。
绝对不是。
我做过几次……几个……INT, VARCHAR和CHAR之间的性能检查。
有一个PRIMARY KEY(唯一的和聚集的)的1000万条记录表,无论我使用哪一个都具有完全相同的速度和性能(以及子树成本)。
话虽如此……使用最适合您的应用程序的任何东西。不要担心性能。
这个问题是关于MySQL的,所以我说有很大的区别。如果是关于Oracle(它将数字存储为字符串——是的,一开始我不敢相信),那么没有太大区别。
表中的存储不是问题,更新和引用索引才是问题。涉及基于主键查找记录的查询非常频繁——您希望它们尽可能快地出现,因为它们经常发生。
问题是CPU自然地处理4字节和8字节的整数,在硅中。它比较两个整数真的很快——它发生在一个或两个时钟周期内。
现在看一个字符串——它由很多字符组成(现在每个字符超过一个字节)。比较两个字符串的优先级不能在一个或两个循环中完成。相反,字符串的字符必须迭代,直到发现差异。我确信在某些数据库中有一些技巧可以让它更快,但在这里这无关紧要,因为在硅中,CPU会自然地进行int比较,并且速度很快。
我的一般规则-每个主键都应该是一个自动递增的INT,特别是在使用ORM (Hibernate, Datanucleus,等等)的OO应用程序中,对象之间有很多关系-它们通常总是作为一个简单的FK实现,DB快速解析这些关系的能力对你的应用程序很重要。 响应性。
我也面临着同样的困境。我用3个事实表做了一个DW(星座模式),道路事故,事故中的车辆和事故中的伤亡。数据包括1979年至2012年英国所有事故记录,以及60个维度表。总共大约有2000万条记录。
事实表关系:
+----------+ +---------+
| Accident |>--------<| Vehicle |
+-----v----+ 1 * +----v----+
1| |1
| +----------+ |
+---<| Casualty |>---+
* +----------+ *
旋风:MySQL
Accident索引本身是一个varchar(数字和字母),有15个数字。我试着不使用代理键,一旦事故索引将永远不会改变。 在i7(8核)计算机中,根据不同的维度,在负载记录达到1200万条之后,DW的查询速度变得太慢。 经过大量的重新工作和添加bigint代理键后,我得到了平均20%的速度性能提升。 虽然性能增益较低,但有效尝试。我在MySQL调优和集群工作。