在mysql中，使用UNIQUE键与NORMAL键在速度方面具有相同的效果吗？
例如，我正在执行下一个操作：
SELECT COUNT(id)
FROM users
WHERE unique_name = 'Wiliam'
// if Wiliam don't exists then...
INSERT INTO users
SET unique_name = 'Wiliam'
问题是，每次插入新用户时，我都会执行 SELECT COUNT(id) 检查，无论是否使用唯一键，所以...如果“unique_name “有一个唯一的密钥会比使用普通密钥性能更好吗？
For example, I'm doing the next action:
SELECT COUNT(id)
FROM users
WHERE unique_name = 'Wiliam'
// if Wiliam don't exists then...
INSERT INTO users
SET unique_name = 'Wiliam'
The question is, I'm doing the SELECT COUNT(id) check every time I insert a new user, despite of using an unique key or not, so... if "unique_name" has an UNIQUE key it will be better for performance than using a normal key?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您的意思是对将要更新的列的唯一约束。读取会更快，插入会慢一点。它仍然比您先检查代码然后插入值要快。只要让 mysql 做它的事情，如果值不唯一，就返回错误给你。
What you mean is a UNIQUE CONSTRAINT on the column which will be updated. Reads will be faster, Inserts will be just a bit slower. It will still be faster than your code checking first and then inserting the value though. Just let mysql do its thing and return an error to you if the value is not unique.
你没有说这是做什么的，这会有帮助。如果它是身份验证系统的一部分，那么为什么您的查询不包含用户的密码？如果不是，则用于存储名称的唯一索引列在现实系统中不会很好地工作，除非您可以接受系统中只有 1 个 Wiliam。 （那应该是 William 吗？）
如果该名称字段确实是唯一的，则不需要在查询中使用 COUNT(ID)。如果“unique_name”确实是唯一的，您要么得到查询返回的 ID 号，要么什么也得不到。
你想要这样的东西：
SELECT id FROM users WHERE unique_name = 'Wiliam'
无记录返回，无 Wiliam。
unique_name 上的索引（唯一或非唯一——我不知道你在这里追求什么）将提高性能。
您对“唯一密钥”的使用不太符合逻辑，因此我怀疑您对键、索引、它们的关系以及它们的用途感到困惑。
数据库中的键用于创建和识别数据集之间的关系。这就是关系数据库中“关系”成为可能的原因。
钥匙有两种类型：主要钥匙和外国钥匙。
主键标识表中的每一行。组成键的一个或多个值必须是唯一的。
主键可以由单列组成，也可以由多列（在这种情况下称为复合键）组成，这些列一起唯一地标识行。这里同样重要的是独特性。
我使用 MySql 的自动增量整数数据类型作为主键。
外键标识表中的哪些行与其他表中的其他行有关系。一个表中记录的外键是另一表中相关记录的主键。外键不是唯一的——在多对多关系中，根据定义存在具有相同外键的多个记录。然而，它们应该被索引。
数据库使用索引作为一种快速查找值的速记方法，而不是扫描整个表或列来查找匹配项。想想一本书后面的索引。使用书籍索引查找内容比翻页查找要容易得多。
您可能还希望对非键列建立索引，以便在搜索该列时获得更好的性能。您在 WHERE 子句中经常使用哪一列？也许应该索引它。
UNIQUE INDEX 是一个索引，其中的所有值都必须不同。具有唯一索引的列不允许您插入重复值，因为这会违反唯一约束。主键是唯一索引。但唯一索引不一定是主键，甚至不一定是键。
希望有帮助。
[为简洁起见编辑]
You didn't say what this is for, which would help. If its part of an authentication system, then why doesn't your query include the user's password as well? If it's not, a unique indexed column used to store names isn't going to work very well in a real-world system unless you are OK with having just 1 and only Wiliam in your system. (Was that supposed to be William?)
And if that name field is really unique you do not need to use COUNT(ID) in your query. If 'unique_name' is truly unique you either get an id number returned from your query or you get nothing.
You'd want something like this:
SELECT id FROM users WHERE unique_name = 'Wiliam'
No record return, no Wiliam.
An index (unique or non-unique -- I don't know what you're after here) on unique_name will improve the performance.
Your use of 'unique key' isn't very logical so I suspect you are getting confused about the nomenclature of keys, indexes, their relationships, and the purposes for them.
KEYS in a database are used to create and identify relationships between sets of data. This is what makes the 'relational' possible in a relational database.
Keys come in 2 flavors: Primary and foreign.
PRIMARY KEYS identify each row in a table. The value or values that comprise the key must be unique.
Primary keys can be made from a single column or made of several columns (in which case it is called a composite key) that together uniquely identifies the row. Again the important thing here is uniqueness.
I use MySql's auto-increment integer data type for my primary keys.
FOREIGN KEYS identify which rows in a table have a relationship with other rows in other tables. A foreign key of a record in one table is the primary key of the related record in the other table. A foreign key is not unique -- in many-to-many relationships there are by definition multiple records with the same foreign key. They should however be indexed.
INDEXES are used by the database as a sort of short-hand method to quickly look up values, as opposed to scanning the entire table or column for a match. Think of the index in the back of a book. Much easier to find something using a book's index than by flipping through the pages looking for it.
You may also want to index a non-key column for better performance when searching on that column. What column do you use frequently in a WHERE clause? Probably should index it then.
UNIQUE INDEX is an index where all the values in it must be distinct. A column with a unique index will not let you insert a duplicate value, because it would violate the unique constraint. Primary keys are unique indexes. But unique indexes do not have to be primary keys, or even a key.
Hope that helps.
[edited for brevity]
拥有唯一约束是一件好事，因为它可以防止插入重复条目，以防您的程序有错误（您的 select 语句中是否缺少“for update”子句？）或者有人插入不使用您的应用程序的数据。
但是，您不应在应用程序中依赖它来进行正常操作。假设 unique_name 是用户可以指定的输入字段。您的应用程序应检查名称是否唯一。如果是，请将其插入。如果不是，请告诉用户。
在所有情况下都尝试插入并查看是否成功是一个坏主意：它将在数据库服务器日志中创建错误，从而使查找真正的错误变得更加困难。这将使您当前的交易变得无用，这可能是一个问题，具体取决于情况
Having a unique constraint is a good thing because it prevents insertion of duplicated entries in case your program is buggy (are you missing a "for update" clause in your select statement?) or in case someone inserts data not using your application.
You should, however, not depend on it in your application for normal operation. Lets assume unique_name is an input field a user can specify. Your application should check whether the name is unique. If it is, insert it. If it was not, tell the user.
It is a bad idea to just try the insert in all cases and see if it was successful: It will create errors in the database server logs that makes it more difficult to find real errors. And it will render your current transaction useless, which may be an issue depending on the situation