blocks|key|1462778|text|对于较短的代码，可能没有区别。这一点尤其正确，因为包含这些代码的表可能非常小(最多几千行)，并且不会经常更改(何时是我们最后一次添加新的美国州)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1462779|对于键之间有较大变化的较大表，这可能是危险的。例如，考虑使用用户表中的电子邮件地址/用户名。当您有几百万用户，其中一些用户具有长名称或电子邮件地址时，会发生什么情况。现在，任何时候你需要使用这个键连接这个表，它都会变得非常昂贵。|1462780|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

For short codes, there's probably no difference. This is especially true as the table holding these codes are likely to be very small (a couple thousand rows at most) and not change often (when is the last time we added a new US State).

For larger tables with a wider variation among the key, this can be dangerous. Think about using e-mail address/user name from a User table, for example. What happens when you have a few million users and some of those users have long names or e-mail addresses. Now any time you need to join this table using that key it becomes much more expensive.

blocks|key|1677983|text|这与性能无关。这是关于什么才是一个好的主键。独一无二且不随时间变化。您可能认为像国家代码这样的实体永远不会随着时间的推移而改变，它将是主键的一个很好的候选者。但痛苦的经验是，这种情况很少发生。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1677984|INT+AUTO_INCREMENT满足“唯一且不随时间变化”的条件。因此才有了这种偏好。|1677985|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

It's not about performance. It's about what makes a good primary key. Unique and unchanging over time. You may think an entity such as a country code never changes over time and would be a good candidate for a primary key. But bitter experience is that is seldom so.

INT AUTO_INCREMENT meets the "unique and unchanging over time" condition. Hence the preference.

blocks|key|1678032|text|绝对不是。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1678033|我做了几个..。有几个。INT、VARCHAR和CHAR之间的性能检查。|1678034|具有主键(唯一的和集群的)的1000万记录表具有完全相同的速度和性能(以及子树成本)，无论我使用这三种表中的哪一种。|1678035|话虽如此..。使用最适合您的应用程序的任何工具。不要担心性能。|1678036|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

Absolutely not.

I have done several... several... performance checks between INT, VARCHAR, and CHAR.

10 million record table with a PRIMARY KEY (unique and clustered) had the exact same speed and performance (and subtree cost) no matter which of the three I used.

That being said... use whatever is best for your application. Don't worry about the performance.

blocks|key|1462929|text|至于主键，任何物理上使行唯一的东西都应该被确定为主键。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1462930|对于作为外键的引用，使用自动递增整数作为代理是一个很好的想法，主要有两个原因。|1462931|首先，在join+usually.Second中产生的开销较少，如果需要更新包含惟一varchar的表，则更新必须级联到所有子表并更新所有子表和索引，而使用int代理时，它只需更新主表及其索引。|1462932|使用代理的好处是，您可能允许更改代理的含义：|1462933|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|code-block|syntax|javascript|1462934|这一切都取决于你在你的结构中真正需要担心的是什么，以及什么是最重要的。|1462935|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|S|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|U|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

As for Primary Key, whatever physically makes a row unique should be determined as the primary key. 

For a reference as a foreign key, using an auto incrementing integer as a surrogate is a nice idea for two main reasons. 
 - First, there's less overhead incurred in the join usually. 
 - Second, if you need to update the table that contains the unique varchar then the update has to cascade down to all the child tables and update all of them as well as the indexes, whereas with the int surrogate, it only has to update the master table and it's indexes.

The drawaback to using the surrogate is that you could possibly allow changing of the meaning of the surrogate:

<pre><code>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
</code></pre>

It all depends on what you really need to worry about in your structure and what means most.

blocks|key|1462998|text|不确定性能的影响，但似乎可能的折衷，至少在开发期间，将包括自动递增的整数“代理”键，以及您想要的，唯一的，“自然”键。这将使您有机会评估性能以及其他可能的问题，包括自然密钥的可变性。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1462999|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Not sure about the performance implications, but it seems a possible compromise, at least during development, would be to include both the auto-incremented, integer "surrogate" key, as well as your intended, unique, "natural" key. This would give you the opportunity to evaluate performance, as well as other possible issues, including the changeability of natural keys.

blocks|key|1678230|text|在HauteLook中，我们更改了许多表以使用自然键。我们确实体验到了真实世界中性能的提升。正如您所提到的，我们的许多查询现在使用更少的连接，这使得查询的性能更高。我们甚至会使用复合主键，如果它有意义的话。也就是说，如果有些表有代理键，那么它们就更容易使用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1678231|此外，如果您让人们向您的数据库编写接口，代理键可能会很有帮助。第三方可以依赖这样一个事实，即代理键只有在非常罕见的情况下才会改变。|1678232|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

At HauteLook, we changed many of our tables to use natural keys. We did experience a real-world increase in performance. As you mention, many of our queries now use less joins which makes the queries more performant. We will even use a composite primary key if it makes sense. That being said, some tables are just easier to work with if they have a surrogate key.

Also, if you are letting people write interfaces to your database, a surrogate key can be helpful. The 3rd party can rely on the fact that the surrogate key will change only in very rare circumstances.

blocks|key|1463156|text|我也面临着同样的困境。我做了一个有3个事实表的DW+(星座模式)，道路事故，事故中的车辆和事故中的伤亡。数据包括1979年至2012年在英国记录的所有事故，以及60个维度表。加起来，大概有2000万条记录。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1463157|事实表关系：|1463158|%2B----------%2B++++++++++%2B---------%2B
%7C+Accident+%7C>--------<%7C+Vehicle+%7C
%2B-----v----%2B+1++++++*+%2B----v----%2B
+++++1%7C++++++++++++++++++++%7C1
++++++%7C++++%2B----------%2B++++%7C
++++++%2B---<%7C+Casualty+%7C>---%2B
+++++++++*+%2B----------%2B+*|code-block|syntax|javascript|1463159|RDMS:+MySQL+5.6|1463160|事故索引本身就是一个varchar(数字和字母)，有15位数字。我试着不使用替代键，一旦意外指数永远不会改变。在i7(8核)计算机中，DW变得太慢，无法根据维度查询1200万条负载记录。经过大量的重做和添加bigint代理键，我获得了平均20%25的速度性能提升。虽然性能提升不大，但有效的尝试。我从事MySQL调优和集群工作。|1463161|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I faced the same dilemma. I made a DW (Constellation schema) with 3 fact tables, Road Accidents, Vehicles in Accidents and Casualties in Accidents. Data includes all accidents recorded in UK from 1979 to 2012, and 60 dimension tables. All together, about 20 million records.

<h3>Fact tables relationships:</h3>

<pre><code>+----------+ +---------+
| Accident |&gt;--------&lt;| Vehicle |
+-----v----+ 1 * +----v----+
 1| |1
 | +----------+ |
 +---&lt;| Casualty |&gt;---+
 * +----------+ *
</code></pre>

RDMS: MySQL 5.6

Natively the Accident index is a varchar(numbers and letters), with 15 digits. I tried not to have surrogate keys, once the accident indexes would never change.
In a i7(8 cores) computer, the DW became too slow to query after 12 million records of load depending of the dimensions.
After a lot of re-work and adding bigint surrogate keys I got a average 20% speed performance boost.
Yet to low performance gain, but valid try. Im working in MySQL tuning and clustering.

blocks|key|1463244|text|像往常一样，没有包罗万象的答案。“这要看情况！”我不是在开玩笑。我对原始问题的理解是针对较小表上的键-例如Country+(整型id或char/varchar代码)是指向可能很大的表(如address/contact表)的外键。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1463245|这里有两种情况，当您想要从DB取回数据时。首先是一种列表/搜索类型的查询，您想要列出具有州和国家代码或姓名的所有联系人(+is将不起作用，因此需要查找)。另一个是主键上的get场景，它显示单个联系人记录，其中需要显示州和国家的名称。|1463246|对于后者的get，FK基于什么可能并不重要，因为我们将单个记录或几个记录的表集中在一起，并按键读取。前一种情况(搜索或列表)可能会受到我们选择的影响。由于需要显示国家(至少是一个可识别的代码，甚至搜索本身也可能包括国家代码)，因此不必通过代理键连接另一个表可以潜在地提高性能(我在这里只是谨慎，因为我没有实际测试过，但似乎很有可能)；尽管它确实有助于搜索。|1463247|由于代码很小-通常不超过3个字符，用于国家和州，在这种情况下使用自然键作为外键可能是可以的。|1463248|另一种情况是键依赖于更长的varchar值，也可能依赖于更大的表；代理键可能更有优势。|1463249|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

As usual, there are no blanket answers. 'It depends!' and I am not being facetious. My understanding of the original question was for keys on small tables - like Country (integer id or char/varchar code) being a foreign key to a potentially huge table like address/contact table.

There are two scenarios here when you want data back from the DB. First is a list/search kind of query where you want to list all the contacts with state and country codes or names (ids will not help and hence will need a lookup). The other is a get scenario on primary key which shows a single contact record where the name of the state, country needs to be shown.

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.

As codes are small in size - not more than 3 chars usually for country and state, it may be okay to use the natural keys as foreign keys in this scenario.

The other scenario where keys are dependent on longer varchar values and perhaps on larger tables; the surrogate key probably has the advantage.

blocks|key|1678534|text|请允许我说，考虑到性能的范围(开箱即用的定义)，肯定是有区别的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1678535|1-在应用程序中使用surrogate+int更快，因为您不需要在代码或查询中使用ToUpper()、ToLower()、ToUpperInvarient()或ToLowerInvarient()，并且这4个函数具有不同的性能基准。有关这一点，请参阅Microsoft性能规则。(应用程序的性能)|1678536|2-使用surrogate+int可以保证键不会随着时间的推移而改变。即使是国家代码也可能发生变化，请参阅维基百科ISO代码是如何随时间变化的。这将花费大量的时间来更改子树的主键。(数据维护性能)|1678537|3-看起来对象关系管理解决方案有问题，例如当PK/FK不是整型时的NHibernate。(开发者表现)|1678538|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

Allow me to say yes there is definitely a difference, taking into consideration the scope of performance (Out of the box definition):

1- Using surrogate int is faster in application because you do not need to use ToUpper(), ToLower(), ToUpperInvarient(), or ToLowerInvarient() in your code or in your query and these 4 functions have different performance benchmarks. See Microsoft performance rules on this. (performance of application)

2- Using surrogate int guarantees not changing the key over time. Even country codes may change, see Wikipedia how ISO codes changed over time. That would take lots of time to change the primary key for subtrees. (performance of data maintenance)

3- It seems there are issues with ORM solutions, such as NHibernate when PK/FK is not int. (developer performance)

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. 

My argument, as detailed <a href="http://database-programmer.blogspot.com/2008/01/database-skills-sane-approach-to.html#rule1" rel="noreferrer">here</a>, 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?

Is there a REAL performance difference between INT and VARCHAR primary keys?

数据库

MySQL

在MySQL中，使用INT和VARCHAR作为主键之间是否存在可测量的性能差异？我希望使用VARCHAR作为引用列表的主键(考虑美国国家和国家代码)，并且同事不会使用INT AUTO_INCREMENT作为所有表的主键。正如详细介绍的一样，我的论点是INT和VARCHAR键之间的性能差异可以忽略不计，因为每个INT外键...

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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