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

Question

在 MySQL 中使用 INT 与 VARCHAR 作为主键之间是否存在可测量的性能差异？我想使用 VARCHAR 作为引用列表的主键(想想美国各州、国家/地区代码)，并且同事不会在 INT AUTO_INCRMENT 作为所有表的主键上让步。

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

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

Answer 1

我对网上缺乏基准测试感到有点恼火，所以我自己进行了测试。

请注意，我不会定期执行此操作，因此请检查我的设置和步骤，了解是否有任何可能无意中影响结果的因素，并在评论中发表您的疑虑。

设置如下:

• 英特尔® 酷睿™ i7-7500U CPU @ 2.70GHz × 4
• 15.6 GiB RAM，我在测试期间确保其中大约 8 GB 可用。
• 148.6 GB SSD 驱动器，具有充足的可用空间。
• Ubuntu 16.04 64 位
• MySQL 版本 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 table :
  • 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 | 
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|

我的结论是，此特定用例没有性能差异。