在MySQL中使用INT和VARCHAR作为主键之间有可测量的性能差异吗?我想使用VARCHAR作为参考列表的主键(认为美国州，国家代码)和同事不会在INT AUTO_INCREMENT作为所有表的主键上让步。
我的论点是，INT和VARCHAR之间的性能差异可以忽略不计，因为每个INT外键引用都需要一个JOIN来理解引用，VARCHAR键将直接显示信息。
那么，有人对这个特殊的用例以及与之相关的性能问题有过经验吗?
当前回答
和往常一样，没有统一的答案。“这取决于!我不是在开玩笑。我对原始问题的理解是小表上的键-像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值，也可能依赖于较大的表;代理键可能具有优势。
其他回答
对于短代码，可能没有区别。当保存这些代码的表可能非常小(最多几千行)并且不经常更改(我们上一次添加新的US State是什么时候)时，这一点尤其正确。
对于键之间变化较大的大型表，这可能是危险的。例如，考虑使用user表中的电子邮件地址/用户名。如果你有几百万用户，其中一些用户有很长的名字或电子邮件地址，会发生什么?现在，任何时候你需要使用这个键来连接这个表，它就变得非常昂贵。
这与性能无关。这是关于什么是一个好的主键。独一无二且随时间不变。您可能认为国家代码之类的实体永远不会随着时间而改变，并且是主键的良好候选者。但痛苦的经验是，这种情况很少发生。
INT AUTO_INCREMENT满足“唯一且随时间不变”的条件。因此才会有偏好。
和往常一样，没有统一的答案。“这取决于!我不是在开玩笑。我对原始问题的理解是小表上的键-像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值，也可能依赖于较大的表;代理键可能具有优势。
我对网上缺乏基准有点恼火，所以我自己做了一个测试。
不过请注意，我并没有定期这样做，所以请检查我的设置和步骤，以找出任何可能无意中影响结果的因素，并在评论中提出您的担忧。
设置如下:
英特尔®酷睿™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 count(*) FROM jan_int WHERE myindex = 5); 对于其他表，如上所示，对于char表使用myindex = 'california'，对于varchar表使用myindex = 'california'。
每个表上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秒
关于表和索引大小，下面是来自janperformancetest的显示表状态的输出;(有几列没有显示):
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 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 |
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
我的结论是，对于这个特定的用例，没有性能差异。
在HauteLook，我们将许多表改为使用自然键。我们确实体验到了真实世界的性能提升。正如您所提到的，我们的许多查询现在使用更少的连接，这使得查询的性能更高。如果有意义，我们甚至会使用复合主键。话虽如此，有些表如果有代理键就更容易使用。
另外，如果您让人们编写到您的数据库的接口，代理键可能会很有帮助。第三方可以依赖代理键只在非常罕见的情况下才会更改这一事实。
