使用INT与VARCHAR作为MySQL中的主键之间是否存在可衡量的性能差异？我想使用VARCHAR作为参考列表的主键(想想美国,国家代码),并且同事不会将INT AUTO_INCREMENT作为所有表的主键.
我的论点,详见这里,是INT和VARCHAR之间的性能差异可以忽略不计,因为每个INT外键引用将需要一个JOIN,使参考的意义上说,VARCHAR键则直接呈现的信息.
那么,有没有人有这个特定用例的经验以及与之相关的性能问题？
这与表现无关.这是关于什么是一个好主键.随着时间的推移,独特而不变.您可能认为诸如国家/地区代码之类的实体从未随时间而变化,并且是主键的良好候选者.但痛苦的经历是很少见到的.
INT AUTO_INCREMENT符合"独特且不变的时间"条件.因此偏好.
您可以通过使用所谓的自然键而不是代理键来避免一些已连接的查询.只有您可以评估其在您的应用中的好处是否重要.
也就是说,您可以在应用程序中测量最快速最重要的查询,因为它们可以处理大量数据,也可以非常频繁地执行.如果这些查询从消除连接中受益,并且不使用varchar主键,那么就这样做.
不要对数据库中的所有表使用任一策略.在某些情况下,自然键可能更好,但在其他情况下,代理键更好.
其他人提出了一个很好的观点,即在实践中很少有自然键永远不会改变或有重复,所以代理键通常是值得的.
取决于长度..如果varchar将是20个字符,并且int是4,那么如果使用int,则索引将在磁盘上每页索引空间的节点数为FIVE ...这意味着遍历索引将需要五分之一的物理和/或逻辑读取.
因此,如果性能是一个问题,给定机会,总是为表使用一个完整的无意义键(称为代理),以及引用这些表中的行的外键...
同时,为了保证数据的一致性,每一个地方事务应该表也有一个有意义的非数字备用键(或唯一索引),以确保重复的行不能插入(重复基于有意义的表属性).
对于您正在讨论的特定用途(如状态查找),它确实无关紧要,因为表的大小非常小.通常,对少于几千行的表的索引的性能没有影响. ..
我对网上缺乏基准测试感到有些恼火,所以我自己做了一个测试.
请注意,我不会在常规基础上执行此操作,因此请检查我的设置和步骤,了解可能会无意中影响结果的任何因素,并在评论中发布您的问题.
设置如下:
英特尔®酷睿™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)])被替换为just 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;(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 | |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
我的结论是,这个特定的用例没有性能差异.
绝对不.
我在INT,VARCHAR和CHAR之间做了几次......几次......性能检查.
具有PRIMARY KEY(唯一和群集)的1000万记录表具有完全相同的速度和性能(和子树成本),无论我使用哪三个.
话虽如此......使用最适合您应用的东西.不要担心性能.
对于短代码,可能没有区别.这尤其如此,因为持有这些代码的表可能非常小(最多几千行)并且不经常更改(我们最后一次添加新的美国州时).
对于键中变化较大的较大表,这可能很危险.例如,考虑使用User表中的电子邮件地址/用户名.当您拥有数百万用户且其中一些用户拥有长名称或电子邮件地址时会发生什么.现在,只要您需要使用该密钥加入此表,它就会变得更加昂贵.
对于主键,无论物理上使行唯一,都应确定为主键.
对于作为外键的引用,使用自动递增整数作为代理是一个好主意,主要有两个原因.
- 首先,通常在连接中产生的开销较少.
- 其次,如果需要更新包含唯一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
这一切都取决于你真正需要担心的结构和最重要的意义.
京公网安备 11010802040832号 | 京ICP备19059560号-6 