INT和VARCHAR主键之间是否存在真正的性能差异？-Java 学习之路

140

使用INT与VARCHAR作为MySQL中的主键之间是否存在可衡量的性能差异？我想使用VARCHAR作为参考列表的主键（想想美国国家代码），并且同事不会将INT AUTO_INCREMENT作为所有表的主键 .

我的论点，如详细here，是INT和VARCHAR之间的性能差异可以忽略不计，因为每个INT外键引用都需要JOIN来理解引用，VARCHAR键将直接呈现信息 .

那么，是否有人有过这个特定用例的经验以及与之相关的性能问题？

14 回答

30

问题是关于MySQL，所以我说有一个显着的差异 . 如果它是关于Oracle（将数字存储为字符串 - 是的，我一开始不相信它）那么差别不大 .

表中的存储不是问题，但更新和引用索引是 . 涉及根据主键查找记录的查询很频繁 - 您希望它们尽可能快地发生，因为它们经常发生 .

事情是CPU在硅片中自然处理4字节和8字节整数 . 它比较两个整数真的很快 - 它发生在一个或两个时钟周期 .

现在看一个字符串 - 它由很多字符组成（这些天每个字符超过一个字节） . 比较两个字符串的优先级不能在一个或两个周期内完成 . 相反，必须迭代字符串的字符，直到找到差异 . 我确信有些技巧可以让它在某些数据库中更快，但这在这里是无关紧要的，因为int比较是由CPU自然而然地在硅片中快速完成的 .

我的一般规则 - 每个主键应该是一个自动增量INT，特别是在使用ORM（Hibernate，Datanucleus，无论如何）的OO应用程序中，对象之间有很多关系 - 它们通常总是被实现为一个简单的FK和它的能力快速解决这些问题的数据库对您应用的响应能力非常重要 .

回复于 2024-04-18T15:38:38+08:00
11

取决于长度..如果varchar将是20个字符，并且int是4，那么如果使用int，则索引将在磁盘上每页索引空间的节点数为FIVE ...这意味着遍历索引将需要五分之一的物理和/或逻辑读取 .

因此，如果性能是一个问题，给定机会，总是为表使用一个完整的无意义键（称为代理），以及引用这些表中的行的外键...

同时，为了保证数据的一致性，每个重要的表应该 also 有一个有意义的非数字备用密钥（或唯一索引），以确保不能插入重复的行（基于有意义的表属性重复） .

对于您正在讨论的特定用途（如状态查找），它确实无关紧要，因为表的大小非常小 . 通常，对少于几千行的表的索引的性能没有影响 . ..

回复于 2024-04-18T15:38:38+08:00
66
对于主键，无论物理上使行唯一，都应确定为主键 .

对于作为外键的引用，使用自动递增整数作为代理是一个好主意，主要有两个原因 .
- 首先，通常在连接中产生的开销较少 .
- 其次，如果需要更新包含唯一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
```
这一切都取决于你真正需要担心的结构和最重要的意义 .
回复于 2024-04-18T15:38:38+08:00
6
我面临同样的困境 . 我制作了一个DW（星座图），有3个事实表，道路交通事故，事故车辆和事故伤亡人员 . 数据包括1979年至2012年在英国记录的所有事故，以及60个维度表 . 总共约有2000万条记录 .

事实表关系：
```
+----------+          +---------+
| Accident |>--------<| Vehicle |
+-----v----+ 1      * +----v----+
     1|                    |1
      |    +----------+    |
      +---<| Casualty |>---+
         * +----------+ *
```
RDMS：MySQL 5.6

本机事故索引是一个varchar（数字和字母），有15位数字 . 一旦事故索引永远不会改变，我尽量不要使用代理键 . 在i7（8核）计算机中，根据维度，在1200万个负载记录之后，DW变得太慢而无法查询 . 经过大量的重新工作并添加bigint代理键后，我的平均速度提升了20％ . 然而，低性能增益，但有效的尝试 . 我正在从事MySQL调优和集群工作 .
回复于 2024-04-18T15:38:38+08:00
2

不确定性能的影响，但似乎可能的妥协，至少在开发过程中，可能包括自动递增的整数“代理”键，以及您想要的，独特的“自然”键 . 这会给你的评估性能以及其他可能问题的机会，包括自然键的可变性 .

回复于 2024-04-18T15:38:38+08:00
0

在HauteLook，我们改变了许多 table 以使用自然键 . 我们确实经历了实际的性能提升 . 正如您所提到的，我们的许多查询现在使用较少的连接，这使查询更具性能 . 如果有意义，我们甚至会使用复合主键 . 话虽这么说，如果有一个代表键，一些表更容易使用 .

此外，如果您让人们为您的数据库编写接口，代理键可能会有所帮助 . 第三方可以依赖于代理键仅在非常罕见的情况下才会改变的事实 .

回复于 2024-04-18T15:38:38+08:00
69
代理 AUTO_INCREMENT 伤害的常见情况：

常见的架构模式是 many-to-many mapping ：
```
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中，可以避免一个方向 .
- 辅助索引是"covering"，因此不需要额外的查找 .
- 由于摆脱 id 和一个索引，该表较小 .
另一个案例（ country ）：
```
country_id INT ...
-- versus
country_code CHAR(2) CHARACTER SET ascii
```
新手常常将country_code规范化为4字节 INT 而不是使用'natural' 2字节，几乎不变的2字节字符串 . 更快，更小，更少JOIN，更易读 .
回复于 2024-04-18T15:38:38+08:00

我对这个在线缺乏基准测试感到有些恼火，所以我自己做了一个测试 .

Note though that I don't do it on a regular basic, so please check my setup and steps for any factors that could have influenced the results unintentionally, and post your concerns in comments.

设置如下：

英特尔®酷睿™i7-7500U CPU @ 2.70GHz×4
15.6 GiB RAM，我确保大约8 GB在测试期间是免费的 .
148.6 GB SSD驱动器，具有充足的可用空间 .
Ubuntu 16.04 64位
MySQL Ver 14.14 Distrib 5.7.20，适用于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));
对于其他表，与上述相同， 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秒

关于表和索引大小，这里是 show table status from 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 | 
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|

My conclusion is that there's no performance difference for this particular use case.

回复于 2024-04-18T15:38:38+08:00

33

对于短代码，可能没有区别 . 这一点尤其如此，因为持有这些代码的表可能非常小（最多几千行）并且不经常更改（我们最后一次添加新的美国州时） .

对于密钥中变化较大的较大表，这可能很危险 . 例如，考虑使用User表中的电子邮件地址/用户名 . 当您拥有数百万用户且其中一些用户拥有长名称或电子邮件地址时会发生什么 . 现在，只要您需要使用该密钥加入此表，它就会变得更加昂贵 .

回复于 2024-04-18T15:38:38+08:00
0

像往常一样，没有一揽子答案 . '这取决于！'而且我不是很滑稽 . 我对原始问题的理解是针对小型表上的键 - 比如Country（整数id或char / varchar代码）是一个可能很大的表（如地址/联系表）的外键 .

当您需要从数据库返回数据时，有两种情况 . 首先是列表/搜索类型的查询，您要列出所有具有州和国家/地区代码或名称的联系人（ID不会有帮助，因此需要查找） . 另一个是主键上的get方案，它显示单个联系人记录，其中需要显示州，国家/地区的名称 .

对于后者来说，FK基于什么并不重要，因为我们将表格汇集到一个记录或几个记录和关键读取 . 前一个（搜索或列表）方案可能会受到我们的选择的影响 . 由于需要显示国家（至少是一个可识别的代码，甚至搜索本身可能包含国家代码），因此不必通过代理键加入另一个表（我在这里只是谨慎，因为我还没有真正测试过这，但似乎极有可能）提高性能;尽管它确实有助于搜索 .

由于代码很小 - 通常对于国家和州不超过3个字符，因此在这种情况下使用自然键作为外键可能没问题 .

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

回复于 2024-04-18T15:38:38+08:00
0

绝对不 .

我已经完成了几个......几个......表现检查INT，VARCHAR和CHAR .

具有PRIMARY KEY（唯一和群集）的1000万记录表具有完全相同的速度和性能（和子树成本），无论我使用哪三个 .

话虽如此......使用最适合您应用的东西 . 不要担心性能 .

回复于 2024-04-18T15:38:38+08:00
1

您可以通过使用所谓的natural key而不是surrogate key来避免一些已连接的查询 . 只有您可以评估其在您的应用中的好处是否重要 .

也就是说，您可以在应用程序中测量最快速最重要的查询，因为它们可以处理大量数据，也可以非常频繁地执行 . 如果这些查询从消除连接中受益，并且不使用varchar主键，那么请执行此操作 .

不要对数据库中的所有表使用任一策略 . 在某些情况下，自然键可能更好，但在其他情况下，代理键更好 .

其他人提出一个很好的观点，即在实践中很少有自然键永远不会改变或有重复，所以代理键通常是值得的 .

回复于 2024-04-18T15:38:38+08:00
2

这与表现无关 . 这是关于什么是一个好主键 . 随着时间的推移，独特而不变 . 您可能认为诸如国家/地区代码之类的实体从未随时间而变化，并且是主键的良好候选者 . 但痛苦的经历是很少见到的 .

INT AUTO_INCREMENT符合“独特且不变的时间”条件 . 因此偏好 .

回复于 2024-04-18T15:38:38+08:00
0

请允许我说是肯定存在差异，考虑到性能范围（开箱即用的定义）：

1-使用surrogate int在应用程序中更快，因为您不需要在代码或查询中使用ToUpper（），ToLower（），ToUpperInvarient（）或ToLowerInvarient（），这4个函数具有不同的性能基准 . 请参阅Microsoft性能规则 . （申请表现）

2-使用surrogate int保证不随时间改变密钥 . 即使国家/地区代码可能会发生变化，请参阅维基百科，ISO代码如这将花费大量时间来更改子树的主键 . （数据维护的表现）

3-似乎ORM解决方案存在问题，例如当PK / FK不是int时NHibernate . （开发人员表现）

回复于 2024-04-18T15:38:38+08:00

INT和VARCHAR主键之间是否存在真正的性能差异？

14 回答

事实表关系：

相关问题