blocks|key|1418462|text|从技术上讲是这样的，但是如果字符串作为主键是有意义的，那么您可能应该使用它。这完全取决于要创建的表的大小和将作为主键的字符串的长度(更长的字符串==更难比较)。对于有数百万行的表，我不一定要使用字符串，但是在较小的表上使用字符串所带来的性能减慢与使用一个与数据无关的整数所带来的麻烦是微不足道的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1418463|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|$]]

Technically yes, but if a string makes sense to be the primary key then you should probably use it. This all depends on the size of the table you're making it for and the length of the string that is going to be the primary key (longer strings == harder to compare). I wouldn't necessarily use a string for a table that has millions of rows, but the amount of performance slowdown you'll get by using a string on smaller tables will be minuscule to the headaches that you can have by having an integer that doesn't mean anything in relation to the data.

blocks|key|1633231|text|使用字符串作为主键的另一个问题是，因为索引总是按顺序放在一起，所以当创建一个新的键时，必须对索引进行重新排序。如果使用自动编号整数，则新键仅添加到索引的末尾。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1633232|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|$]]

Another issue with using Strings as a primary key is that because the index is constantly put into sequential order, when a new key is created that would be in the middle of the order the index has to be resequenced... if you use an auto number integer, the new key is just added to the end of the index.

blocks|key|1418928|text|插入到具有聚集索引的表中，如果插入发生在序列的中间，则不会导致重写索引。它不会导致组成数据的页面被重写。如果页面上有空间，行将被放置在该页面中。单个页面将被重新格式化，以将行放置在页面中的正确位置。当页面已满时，将发生页面拆分，页面上的一半行转到一页，另一半行转到另一页。然后将这些页重新链接到包含具有聚集索引的表数据的页的链接列表中。至多，您将最终编写2页的数据库。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1418929|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|$]]

Inserts to a table having a clustered index where the insertion occurs in the middle of the sequence DOES NOT cause the index to be rewritten. It does not cause the pages comprising the data to be rewritten. If there is room on the page where the row will go, then it is placed in that page. The single page will be reformatted to place the row in the right place in the page. When the page is full, a page split will happen, with half of the rows on the page going to one page, and half going on the other. The pages are then relinked into the linked list of pages that comprise a tables data that has the clustered index. At most, you will end up writing 2 pages of database.

blocks|key|1633372|text|字符串在连接中速度较慢，在现实生活中，它们很少是真正唯一的(即使它们应该是唯一的)。唯一的好处是，如果您只是为了获得名称而连接到主表，则它们可以减少连接的数量。但是，字符串也经常会发生更改，从而产生这样的问题:当公司名称更改或个人结婚时，必须修复所有相关记录。这可能会对性能造成巨大的影响，如果所有本应相关的表都不相关(这种情况发生的频率比您想象的要高)，那么您也可能会遇到数据不匹配的问题。无论从数据完整性的角度还是从性能的角度来看，在记录的整个生命周期内都不会发生变化的整数是更安全的选择。自然键对于数据的维护通常不是很好。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1633373|我还想指出的是，这两种情况的最佳效果通常是使用自动增量键(或在某些特殊情况下，GUID)作为PK，然后将唯一索引放在自然键上。你得到了更快的联接，你不会得到重复的记录，你也不需要因为公司名称的改变而更新一百万个子记录。|1633374|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|$]]

Strings are slower in joins and in real life they are very rarely really unique (even when they are supposed to be). The only advantage is that they can reduce the number of joins if you are joining to the primary table only to get the name. However, strings are also often subject to change thus creating the problem of having to fix all related records when the company name changes or the person gets married. This can be a huge performance hit and if all tables that should be related somehow are not related (this happens more often than you think), then you might have data mismatches as well. An integer that will never change through the life of the record is a far safer choice from a data integrity standpoint as well as from a performance standpoint. Natural keys are usually not so good for maintenance of the data.

I also want to point out that the best of both worlds is often to use an autoincrementing key (or in some specialized cases, a GUID) as the PK and then put a unique index on the natural key. You get the faster joins, you don;t get duplicate records, and you don't have to update a million child records because a company name changed.

blocks|key|1633327|text|使用什么作为主键并不重要，只要它是唯一的即可。如果您关心速度或良好的数据库设计，请使用int，除非您计划复制数据，然后使用GUID。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1633328|如果这是一个access数据库或一些小应用程序，那么谁会真正关心呢。我认为，我们大多数开发人员之所以在前面使用旧的int或guid，是因为项目对我们有一种增长的方式，而你想让自己有增长的选择。|1633329|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 doesn't matter what you use as a primary key so long as it is UNIQUE. If you care about speed or good database design use the int unless you plan on replicating data, then use a GUID.

If this is an access database or some tiny app then who really cares. I think the reason why most of us developers slap the old int or guid at the front is because projects have a way of growing on us, and you want to leave yourself the option to grow.

blocks|key|1633607|text|不要担心性能，直到你有了一个简单而合理的设计，它符合数据描述的主题，并与数据的预期用途很好地匹配。然后，如果出现性能问题，您可以通过调整系统来处理它们。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1633608|在这种情况下，使用字符串作为自然主键几乎总是更好，前提是您可以信任它。如果它是一个字符串，不要担心，只要字符串相当短，比如最多25个字符。在性能方面，您不会付出很大代价。|1633609|数据录入人员或自动数据源是否总是为假定的自然键提供值，或者有时会被省略？是否偶尔会在输入数据中出现错误？如果是，如何检测和纠正错误？|1633610|指定查询的程序员和交互式用户是否能够使用自然键来获得他们想要的东西？|1633611|如果你不能信任自然密钥，那就发明一个代理。如果你发明了一个代理，你也可以发明一个整数。然后，您必须考虑向用户社区隐藏代理的位置。一些没有隐藏代理键的开发人员开始后悔。|1633612|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|$]]

Don't worry about performance until you have got a simple and sound design that agrees with the subject matter that the data describes and fits well with the intended use of the data. Then, if performance problems emerge, you can deal with them by tweaking the system.

In this case, it's almost always better to go with a string as a natural primary key, provide you can trust it. Don't worry if it's a string, as long as the string is reasonably short, say about 25 characters max. You won't pay a big price in terms of performance.

Do the data entry people or automatic data sources always provide a value for the supposed natural key, or is sometimes omitted? Is it occasionally wrong in the input data? If so, how are errors detected and corrected? 

Are the programmers and interactive users who specify queries able to use the natural key to get what they want? 

If you can't trust the natural key, invent a surrogate. If you invent a surrogate, you might as well invent an integer. Then you have to worry about whther to conceal the surrogate from the user community. Some developers who didn't conceal the surrogate key came to regret it.

blocks|key|1633183|text|索引意味着有很多比较。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1633184|通常，字符串比整数长，并且可以应用排序规则进行比较，因此比较字符串通常比比较整数需要更多的计算。|1633185|但是，有时使用字符串作为主键要比使用string+to+numerical+id表进行额外的连接更快。|offset|length|style|CODE|1633186|entityMap^0|0|0|I|M|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|@$F|O|G|P|H|I]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

Indices imply lots of comparisons.

Typically, strings are longer than integers and collation rules may be applied for comparison, so comparing strings is usually more computationally intensive task than comparing integers. 

Sometimes, though, it's faster to use a string as a primary key than to make an extra join with a <code>string to numerical id</code> table.

blocks|key|1633298|text|是的，但除非您希望有数百万行，否则不使用基于字符串的键，因为它较慢，通常是“过早的优化”。毕竟，字符串存储为大数字，而数字键通常存储为较小的数字。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1633299|但是，需要注意的一件事是，如果您在Any键上有聚集索引，并且在索引中执行大量非顺序插入。写入的每一行都会导致索引重写。如果你正在做批量插入，这真的会减慢进程。|1633300|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|$]]

Yes, but unless you expect to have millions of rows, not using a string-based key because it's slower is usually "premature optimization." After all, strings are stored as big numbers while numeric keys are usually stored as smaller numbers.

One thing to watch out for, though, is if you have clustered indices on a any key and are doing large numbers of inserts that are non-sequential in the index. Every line written will cause the index to re-write. if you're doing batch inserts, this can really slow the process down.

blocks|key|1418795|text|从性能的角度来看--与使用整数(+PK+)相比，字符串(PK)会降低性能，其中PK->主键。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1418796|从需求的角度来看-尽管这不是你问题的一部分，但我还是想提一下。当我们处理跨不同表的海量数据时，我们通常会查找可以为特定表设置的可能的键集。这主要是因为有许多表，并且大多数情况下，每个或某些表将通过某种关系(外键的概念)相互关联。因此，我们真的不能总是选择一个整数作为主键，而是选择3、4或5个属性的组合作为该表的主键。当我们将记录与其他表关联时，这些键可以用作外键。这使得在需要时跨不同表关联记录非常有用。|1418797|因此，为了优化使用-我们总是将1或2个整数与1或2个字符串属性组合在一起，但只有在需要时才会再次使用。|1418798|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

From performance standpoint - Yes string(PK) will slow down the performance when compared to performance achieved using an integer(PK), where PK ---> Primary Key.

From requirement standpoint - Although this is not a part of your question still I would like to mention. When we are handling huge data across different tables we generally look for the probable set of keys that can be set for a particular table. This is primarily because there are many tables and mostly each or some table would be related to the other through some relation ( a concept of Foreign Key ). Therefore we really cannot always choose an integer as a Primary Key, rather we go for a combination of 3, 4 or 5 attributes as the primary key for that tables. And those keys can be used as a foreign key when we would relate the records with some other table. This makes it useful to relate the records across different tables when required.

Therefore for Optimal Usage - We always make a combination of 1 or 2 integers with 1 or 2 string attributes, but again only if it is required.

blocks|key|1633425|text|对于数据库中的字符串可能会有很大的误解。几乎每个人都认为数字的数据库表示比字符串的表示更紧凑。他们认为，在db-s中，数字表示为内存中的数字。但事实并非如此。在大多数情况下，数字表示比其他表示更接近于字符串表示。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1633426|使用数字或字符串的速度更多地依赖于索引而不是类型本身。|1633427|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|$]]

There could be a very big misunderstanding related to string in the database are. Almost everyone has thought that database representation of numbers are more compact than for strings. They think that in db-s numbers are represented as in the memory. BUT it is not true. In most cases number representation is more close to A string like representation as to other. 

The speed of using number or string is more dependent on the indexing then the type itself.

blocks|key|1418991|text|我可能会使用一个整数作为您的主键，然后将您的字符串(我假设它是某种ID)作为单独的列。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1418992|create+table+sample+(
++sample_pk+++++++++++++INT+NOT+NULL+AUTO_INCREMENT,
++sample_id+++++++++++++VARCHAR(100)+NOT+NULL,
++...
++PRIMARY+KEY(sample_pk)
);|code-block|syntax|javascript|1418993|您始终可以在字符串(ID)列(其中sample_id+=+...)上有条件地执行查询和连接。|1418994|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I would probably use an integer as your primary key, and then just have your string (I assume it's some sort of ID) as a separate column.
<pre><code>create table sample (
 sample_pk INT NOT NULL AUTO_INCREMENT,
 sample_id VARCHAR(100) NOT NULL,
 ...
 PRIMARY KEY(sample_pk)
);
</code></pre>
You can always do queries and joins conditionally on the string (ID) column (where sample_id = ...).

I am not very familiar with databases and the theories behind how they work. Is it any slower from a performance standpoint (inserting/updating/querying) to use Strings for Primary Keys than integers?

Strings as Primary Keys in SQL Database

数据完整性

数据库

我不太熟悉数据库和它们工作原理背后的理论。从性能的角度来看(插入/更新/查询)，使用字符串作为主键比使用整数慢吗？

问字符串作为SQL数据库中的主键
EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问字符串作为SQL数据库中的主键EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问字符串作为SQL数据库中的主键
EN