在候选密钥中选择主键的最佳方法是什么？
我担心在候选密钥中选择哪种候选键 。
假设使用MySQL数据库（InnoDB）。假设我们有一个独特的价值，学生编号和一个唯一的价值， id编号（例如社会安全号码）。
学生ID号和ID号可以分别是候选密钥。 在这种情况下，即使考虑自动插入新列，我也应该将什么值设置为主要密钥？
我的猜测是InnoDB（MySQL）使用主键创建聚类索引。因此，使用我需要找到特定范围的列是正确的，因为它具有能够找到范围的优势？
谢谢你！！
I am concerned about which candidate key to select as the primary key among the candidate keys.
Assume that using mysql database(innoDB). Suppose we have a unique value, Student Number, and a unique value, ID Number(eg Social Security Number).
Student ID number and ID number can each be a candidate key.
In this case, what value should I set as the primary key even considering auto-increment new column?
My guess is that innoDB(mysql) use primary key to create the clustering index. So, is it right to use a column where I need to find a specific range, since it has the advantage of being able to find a range?
Thank you!!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
首先，您应该注意，美国社会安全号码不是唯一的< /a>。
正确的是，InnoDB总是将主要键视为聚类索引。您不必猜测，它已在手册中进行了记录： https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html
您不一定需要使用主键来查找特定范围。您也可以使用辅助索引。您甚至可以使用一个非索引列，但是它会导致桌扫，除非表非常小，否则会导致性能差。
考虑到搜索聚类索引与辅助索引之间的选择，搜索聚类索引的优化程度更高。
指南有例外，我们不知道您的查询是否是这些例外之一，因为您没有描述任何查询。
这提出了一个更广泛的观点：如果不知道需要优化的特定查询，您将无法选择最佳的优化策略。
First, you should be aware that the US Social Security Number is not unique.
You're correct that InnoDB always treats the primary key as the clustering index. You don't have to guess, it's documented in the manual: https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html
You don't necessarily need to use the primary key to find a specific range. You could use a secondary index as well. You could even use a non-indexed column, but it would result in a table-scan which causes poor performance unless the table is very small.
Given the choice between searching the clustered index versus a secondary index, it's a little bit more optimized to search the clustered index.
There are exceptions to the guideline, and we can't know if your query is one of those exceptions because you haven't described any of your queries.
This brings up a broader point: you can't choose the best optimization strategy without knowing the specific queries you need to optimize.