在MySQL中使用INT和VARCHAR作为主键之间有可测量的性能差异吗?我想使用VARCHAR作为参考列表的主键(认为美国州,国家代码)和同事不会在INT AUTO_INCREMENT作为所有表的主键上让步。
我的论点是,INT和VARCHAR之间的性能差异可以忽略不计,因为每个INT外键引用都需要一个JOIN来理解引用,VARCHAR键将直接显示信息。
那么,有人对这个特殊的用例以及与之相关的性能问题有过经验吗?
在MySQL中使用INT和VARCHAR作为主键之间有可测量的性能差异吗?我想使用VARCHAR作为参考列表的主键(认为美国州,国家代码)和同事不会在INT AUTO_INCREMENT作为所有表的主键上让步。
我的论点是,INT和VARCHAR之间的性能差异可以忽略不计,因为每个INT外键引用都需要一个JOIN来理解引用,VARCHAR键将直接显示信息。
那么,有人对这个特殊的用例以及与之相关的性能问题有过经验吗?
当前回答
绝对不是。
我做过几次……几个……INT, VARCHAR和CHAR之间的性能检查。
有一个PRIMARY KEY(唯一的和聚集的)的1000万条记录表,无论我使用哪一个都具有完全相同的速度和性能(以及子树成本)。
话虽如此……使用最适合您的应用程序的任何东西。不要担心性能。
其他回答
绝对不是。
我做过几次……几个……INT, VARCHAR和CHAR之间的性能检查。
有一个PRIMARY KEY(唯一的和聚集的)的1000万条记录表,无论我使用哪一个都具有完全相同的速度和性能(以及子树成本)。
话虽如此……使用最适合您的应用程序的任何东西。不要担心性能。
至于主键(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
这完全取决于您在结构中真正需要担心的是什么,以及什么最重要。
代理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,更可读。
和往常一样,没有统一的答案。“这取决于!我不是在开玩笑。我对原始问题的理解是小表上的键-像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值,也可能依赖于较大的表;代理键可能具有优势。
这与性能无关。这是关于什么是一个好的主键。独一无二且随时间不变。您可能认为国家代码之类的实体永远不会随着时间而改变,并且是主键的良好候选者。但痛苦的经验是,这种情况很少发生。
INT AUTO_INCREMENT满足“唯一且随时间不变”的条件。因此才会有偏好。