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

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

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


当前回答

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

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

其他回答

代理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,更可读。

我也面临着同样的困境。我用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调优和集群工作。

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

至于主键(Primary Key),任何物理上使行唯一的元素都应该被确定为主键。

对于作为外键的引用,使用自动递增的整数作为代理是一个好主意,主要有两个原因。 -首先,通常在连接中产生的开销更少。 -其次,如果你需要更新包含唯一varchar的表,那么更新必须级联到所有的子表,并更新所有的子表以及索引,而使用int代理,它只需要更新主表及其索引。

使用代理的缺点是,你可能会允许更改代理的含义:

ex.
id value
1 A
2 B
3 C

Update 3 to D
id value
1 A
2 B
3 D

Update 2 to C
id value
1 A
2 C
3 D

Update 3 to B
id value
1 A
2 C
3 B

这完全取决于您在结构中真正需要担心的是什么,以及什么最重要。

请允许我说,考虑到性能范围(开箱即用定义),肯定有区别:

1-在应用程序中使用代理int更快,因为你不需要在你的代码或查询中使用ToUpper(), ToLower(), ToUpperInvarient()或ToLowerInvarient(),这4个函数有不同的性能基准。请参阅关于此的Microsoft性能规则。(申请的表现)

2-使用代理int保证不随时间改变键。甚至国家代码也可能发生变化,请参阅维基百科ISO代码如何随时间变化。这将花费大量时间来更改子树的主键。(数据维护的表现)

3- ORM解决方案似乎有问题,比如当PK/FK不是int时NHibernate。开发人员(性能)