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

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

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

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

122800 次浏览
小开

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

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

小开

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

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

小开

绝对不是。

我做过几次……几个……INT, VARCHAR和CHAR之间的性能检查。

有一个PRIMARY KEY(唯一的和聚集的)的1000万条记录表，无论我使用哪一个都具有完全相同的速度和性能(以及子树成本)。

话虽如此……使用最适合您的应用程序的任何东西。不要担心性能。

小开

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

对于引用作为外键，使用一个自动递增的整数作为代理是一个不错的想法，主要有两个原因 -首先，通常join的开销更少 -其次，如果你需要更新包含唯一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

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

小开

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

小开
最佳答案

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

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

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

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

小开

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

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

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

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

小开

在HauteLook，我们将许多表改为使用自然键。我们确实体验到了真实世界的性能提升。正如您所提到的，我们的许多查询现在使用更少的连接，这使得查询的性能更高。如果有意义，我们甚至会使用复合主键。话虽如此，有些表如果有代理键就更容易使用。

另外，如果您让人们编写到您的数据库的接口，代理键可能会很有帮助。第三方可以依赖代理键只在非常罕见的情况下才会更改这一事实。

小开

我也面临着同样的困境。我用3个事实表做了一个DW(星座模式)，道路事故，事故中的车辆和事故中的伤亡。数据包括1979年至2012年英国所有事故记录，以及60个维度表。总共大约有2000万条记录。

事实表关系:

+----------+          +---------+
| Accident |>--------<| Vehicle |
+-----v----+ 1      * +----v----+
1|                    |1
|    +----------+    |
+---<| Casualty |>---+
* +----------+ *

RDMS: MySQL 5.6

本机Accident索引是一个varchar(数字和字母)，有15个数字。我试着不使用代理键，一旦事故索引将永远不会改变。 在i7(8核)计算机中，根据不同的维度，在负载记录达到1200万条之后，DW的查询速度变得太慢。 经过大量的重新工作和添加bigint代理键后，我得到了平均20%的速度性能提升。 虽然性能增益较低，但有效尝试。我在MySQL调优和集群工作
小开

代理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) );

为什么?

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

另一种情况(国家):

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

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

小开

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

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

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

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

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

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

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

对于后一种get, FK基于什么可能并不重要，因为我们将单个记录或几个记录以及关键读取的表合并在一起。前一种情况(搜索或列表)可能会受到我们的选择的影响。因为它需要显示国家(至少是一个可识别的代码，甚至搜索本身可能包括一个国家代码)，不需要通过代理键连接另一个表可以潜在地(这里我只是谨慎，因为我没有实际测试过，但似乎很有可能)提高性能;尽管这对搜索很有帮助。

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

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

小开

我对网上缺乏基准有点恼火，所以我自己做了一个测试。

不过请注意，我并没有定期这样做，所以请检查我的设置和步骤，以找出任何可能无意中影响结果的因素，并在评论中提出您的担忧。

设置如下:

  • 英特尔®酷睿™i7-7500U CPU @ 2.70GHz × 4
  • 15.6 GiB RAM，我确保在测试期间大约8gb是空闲的。
  • 148.6 GB SSD硬盘，空闲空间充足。
  • Ubuntu 16.04 64位
  • MySQL version 14.14 Distrib 5.7.20, for Linux (x86_64)

表:

create table jan_int (data1 varchar(255), data2 int(10), myindex tinyint(4)) ENGINE=InnoDB;
create table jan_int_index (data1 varchar(255), data2 int(10), myindex tinyint(4), INDEX (myindex)) ENGINE=InnoDB;
create table jan_char (data1 varchar(255), data2 int(10), myindex char(6)) ENGINE=InnoDB;
create table jan_char_index (data1 varchar(255), data2 int(10), myindex char(6), INDEX (myindex)) ENGINE=InnoDB;
create table jan_varchar (data1 varchar(255), data2 int(10), myindex varchar(63)) ENGINE=InnoDB;
create table jan_varchar_index (data1 varchar(255), data2 int(10), myindex varchar(63), INDEX (myindex)) ENGINE=InnoDB;

然后，我用一个PHP脚本在每个表中填充了1000万行，其本质是这样的:

$pdo = get_pdo();


$keys = [ 'alabam', 'massac', 'newyor', 'newham', 'delawa', 'califo', 'nevada', 'texas_', 'florid', 'ohio__' ];


for ($k = 0; $k < 10; $k++) {
for ($j = 0; $j < 1000; $j++) {
$val = '';
for ($i = 0; $i < 1000; $i++) {
$val .= '("' . generate_random_string() . '", ' . rand (0, 10000) . ', "' . ($keys[rand(0, 9)]) . '"),';
}
$val = rtrim($val, ',');
$pdo->query('INSERT INTO jan_char VALUES ' . $val);
}
echo "\n" . ($k + 1) . ' millon(s) rows inserted.';
}

对于int表，位($keys[rand(0, 9)])被替换为rand(0, 9)，而对于varchar表，我使用了完整的美国州名，没有将它们删减或扩展为6个字符。generate_random_string()生成一个10个字符的随机字符串。

然后在MySQL中运行:

  • SET SESSION query_cache_type=0;
    • 对于jan_int表:
    • SELECT count(*) FROM jan_int WHERE myindex = 5;
    • SELECT BENCHMARK(1000000000, (SELECT count(*) FROM jan_int WHERE myindex = 5));
      • 李< / ul > < / >
    • 对于其他表，如上所述，myindex = 'califo'用于char表，myindex = 'california'用于varchar表。

    每个表上BENCHMARK查询的次数:

    • Jan_int: 21.30秒
    • Jan_int_index: 18.79秒
    • Jan_char: 21.70秒
    • Jan_char_index: 18.85秒
    • Jan_varchar: 21.76秒
    • Jan_varchar_index: 18.86秒

    关于table &索引大小，下面是show table status from janperformancetest;的输出(w/一些列没有显示):

    |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Name              | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Collation              |
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| jan_int           | InnoDB |      10 | Dynamic    | 9739094 |             43 |   422510592 |               0 |            0 |   4194304 |           NULL | utf8mb4_unicode_520_ci |
| jan_int_index     | InnoDB |      10 | Dynamic    | 9740329 |             43 |   420413440 |               0 |    132857856 |   7340032 |           NULL | utf8mb4_unicode_520_ci |
| jan_char          | InnoDB |      10 | Dynamic    | 9726613 |             51 |   500170752 |               0 |            0 |   5242880 |           NULL | utf8mb4_unicode_520_ci |
| jan_char_index    | InnoDB |      10 | Dynamic    | 9719059 |             52 |   513802240 |               0 |    202342400 |   5242880 |           NULL | utf8mb4_unicode_520_ci |
| jan_varchar       | InnoDB |      10 | Dynamic    | 9722049 |             53 |   521142272 |               0 |            0 |   7340032 |           NULL | utf8mb4_unicode_520_ci |
| jan_varchar_index | InnoDB |      10 | Dynamic    | 9738381 |             49 |   486539264 |               0 |    202375168 |   7340032 |           NULL | utf8mb4_unicode_520_ci |
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|

    我的结论是，对于这个特定的用例，没有性能差异。

小开

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

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

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

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


提交答案