INT 和 VARCHAR 主键之间是否存在真正的性能差异? [英] Is there a REAL performance difference between INT and VARCHAR primary keys?

问题描述

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

Is there a measurable performance difference between using INT vs. VARCHAR as a primary key in MySQL? I'd like to use VARCHAR as the primary key for reference lists (think US States, Country Codes) and a coworker won't budge on the INT AUTO_INCREMENT as a primary key for all tables.

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

My argument, as detailed here, is that the performance difference between INT and VARCHAR is negligible, since every INT foreign key reference will require a JOIN to make sense of the reference, a VARCHAR key will directly present the information.

那么，有没有人体验过这个特定的用例以及与之相关的性能问题?

So, does anyone have experience with this particular use-case and the performance concerns associated with it?

推荐答案

您提出了一个很好的观点，即通过使用所谓的 自然键 而不是 代理键.只有您可以评估这样做的好处在您的应用程序中是否重要.

You make a good point that you can avoid some number of joined queries by using what's called a natural key instead of a surrogate key. Only you can assess if the benefit of this is significant in your application.

也就是说，您可以衡量应用程序中最重要的查询，因为它们处理大量数据或执行非常频繁.如果这些查询因消除连接而受益，并且不会因使用 varchar 主键而受到影响，那么就去做吧.

That is, you can measure the queries in your application that are the most important to be speedy, because they work with large volumes of data or they are executed very frequently. If these queries benefit from eliminating a join, and do not suffer by using a varchar primary key, then do it.

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

Don't use either strategy for all tables in your database. It's likely that in some cases, a natural key is better, but in other cases a surrogate key is better.

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

Other folks make a good point that it's rare in practice for a natural key to never change or have duplicates, so surrogate keys are usually worthwhile.

这篇关于INT 和 VARCHAR 主键之间是否存在真正的性能差异?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！