SQL Server - 为我的表选择主键
我有一个存储用户信息的表。
在用户表中，用户名是唯一的。您认为我应该将用户名作为主键还是应该使用 int 代理键？
使用字符串键会严重影响性能吗？
I have a table that stores user info.
In the User table, username is unique. Do you think I should make username as primarykey or should I use a surrogate key that is an int?
Would using a string key hit performance badly?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
使用代理整数键。
用户名不会经常更改，但可以。
至于性能，在您知道有问题之前不要担心。
默认情况下，SQL Server 将在主键列上创建聚集索引。如果在聚集索引中使用宽键，则所有非聚集索引也将包含该宽键。
Use a surrogate integer key.
Usernames won't change that often, but they could.
As to performance, don't worry about that until you know you have a problem.
SQL Server will create the clustered index on the Primary key column by default. If you use a wide key in the clustered index, all non-clustered indexes will also contain that wide key.
一般使用int作为主键。这部分是由于约定以及在其他表中将它们用作外键时节省空间。实际上，使用用户名字段作为主字段不会影响性能，除非您最终在使用它的多个表中拥有数千条记录。如果您认为您的桌子会保持较小，则可以根据喜好而定。
Generally using an int as the primary key. This is due in part to convention as well as saving space when using them as foreign keys in other tables. In reality, using your username field as the primary wouldn't hurt performance unless you wind up having thousands of records in multiple tables using it. If you think your tables will remain small, its up to preference.
我将使用身份代理主键并对其进行集群。聚集索引包含在所有索引中，并且应该是窄的、静态的和递增的。
就主键而言，您可以将用户名作为主键，但由于外键将引用它，因此您还希望它是静态的（用户名不是静态的）。所以我会在用户名上创建一个非聚集唯一索引。身份 PK 将自动包含在 NCI 中。
我将在同一索引中包含任何其他列（如包含的列），具体取决于主要通过用户名进行访问的使用模式 - 例如，密码哈希，可能是名称。但我会检查执行计划，使用分析器和/或索引调整向导来处理预期的工作负载。
I would use an identity surrogate primary key and cluster on that. The clustered index is included in all indexes and should be narrow, static and increasing.
As far as a primary key, you COULD make the username the primary key, BUT since foreign keys will reference it, you also want it to be static (which a username is not). So I would make a non-clustered unique index on username. The identity PK will automatically be included in the NCI.
I would include any other columns in that same index (as included columns) depending upon usage patters where access is primarily by username - for instance, the password hash, maybe the name. But I'd check the execution plans, use the profiler and/or the index tuning wizard with expected workloads.