MySQL中的唯一约束
唯一约束默认是索引吗？ 如果不是，在 SELECT ... WHERE 子句中使用唯一约束时，它是否具有与索引列相同的性能结果？
谢谢
Is a unique constraint an index by default? If not, does a unique constraint has the same performance results as an indexed column when using it in the SELECT ... WHERE clause?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
唯一约束必然是索引。 您通常将其定义为“UNIQUE INDEX”。 在任何情况下都需要索引来有效地实现唯一约束，因此拥有一个索引并没有什么缺点。
A unique constraint is necessarily an index. You normally define it as "UNIQUE INDEX". An index would be required in any case to efficiently implement a unique constraint, so having one is no disadvantage.
约束实际上与索引有很大不同：它只是说 MySQL 应该为您强制执行唯一性。 然而，索引（尽管它可以是唯一的）与硬盘上的物理顺序有关，或者与允许在列上进行高效搜索的附加结构（通常是树）有关。
然而，您可能会将所有这些与定义（通常是聚集的）唯一索引的主键混淆。
A constraint is actually very different from an index: It just says that MySQL is supposed to enforce uniqueness for you. An index however (although it can be unique) is about the physical ordering on your harddisk, or about additional structures (usually a tree) that allows for efficient searching on the column.
You might however be confusing all this with primary keys which define a (usually clustered) unique index.
唯一约束是一种表达事实的方式，即某些事物（如属性值的某些组合）在整个关系（“表”）的范围内必须是唯一的。
这是逻辑设计的层面。
索引可能是帮助执行此类约束的有用方法。
这是物理设计层面的。
某些 DBMS 产品可能会根据某些逻辑设计构造（例如 UNIQUE 约束）的存在来推断某些物理设计构造（例如某些索引的存在）。 其他人可能不会。
A unique constraint is a way for expressing the fact that something (like some combination of attribute values) must be unique within the scope of an entire relation ("table")).
That is at the level of LOGICAL design.
An index is a possibly useful means in helping to enforce such a constraint.
That is at the level of PHYSICAL design.
Some DBMS products might infer certain physical design constructs, such as the presence of some index, from the presence of certain logical design constructs, such as a UNIQUE constraint. Others might not.
UNIQUE 实际上是对索引的约束，所以是的，UNIQUE 意味着您要强制执行唯一性的字段上有一个索引。
UNIQUE is actually a constraint on an index, so yes, UNIQUE implies that there is an index on the field you're enforcing uniqueness on.
在开始使用唯一约束之前检查主键约束。
主键约束相当于声明唯一约束和非空约束。 如果主键中有多个列，则每一列都会获得非空约束，但唯一约束适用于所有列。
当您声明主键时，DBMS 将为您创建一个索引。 如果您愿意，可以删除索引，但是当 DBMS 进行表扫描以检查唯一性时，您将获得糟糕的性能。
主键约束强制实体完整性，而引用（外键）约束强制引用完整性。 它们共同为确保数据完整性大有帮助。
Check out the primary key constraint before you start using unique constraints.
A primary key constraint amounts to declaring a unique constraint and a not null constraint. If there is more than one column in the primary key, each column gets a not null constraint, but the unique constraint applies to all the columns taken together.
When you declare a primary key, the DBMS will create an index for you. You can drop the index if you like, but you'll get horrible performance when the DBMS does table scans to check for uniqueness.
Primary key constraints enforce entity integrity, while REFERENCES (foreign key) constraints enforce referential integrity. Together, they go a long way towards ensuring data integrity.