复合唯一键是否在 MySQL 中建立索引?- 数据库管理系统 [英] Are composite unique keys indexed in MySQL? - DBMS
问题描述
我有一个 UserSkills
表,它有三列:id
(PK)、userId
(FK) 和 skillId
(FK).
我想对 userId
和 skillId
的组合实施复合唯一性约束.为了更快地查找,我希望对 (userId, SkillId)
进行复合索引.
I want to enforce a composite uniqueness constraint on a combination of userId
and skillId
. And for faster lookups, I want composite indexing on (userId, skillId)
.
据我所知,MySQL 会自动索引唯一的列.但是,到目前为止,我所看到的所有示例和我自己的实现都涉及单列唯一约束.我想知道 MySQL 是否也索引复合唯一键.如果是,是否将其视为正常的多列索引.
As far as I know, MySQL automatically indexes unique columns. But, all the examples I have seen and my own implementations so far involve single-column unique constraints. I want to know if MySQL indexes composite unique keys as well. And if it does, is it treated as a normal multi-column index or not.
对于普通的多列索引,根据 MySQL 参考手册,
For normal multi-column indexes, according to MySQL reference manual,
如果表有一个多列索引,任何最左边的前缀优化器可以使用索引来查找行.例如,如果你在 (col1, col2, col3) 上有一个三列索引,你已经索引(col1)、(col1, col2) 和 (col1, col2, col3) 的搜索功能.
If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).
简而言之,我想知道是否应该在声明上述涉及它们的多列唯一约束后创建 (userId, SkillId) 的多列索引,或者这只是多余的,因此不需要.
In a nutshell, I want to find out if I should create a multi-column index of (userId, skillId) after declaring the aforementioned multi-column unique constraint involving them or would that be simply redundant and hence not required.
推荐答案
所以对于你的字段,id
(PK), userId
(FK) 和 skillId
(FK),mysql 将自动在 (id)
(唯一)上创建索引,并在 (userId)
(非唯一)和 (skillId)<上创建索引/code>(非唯一).
So for your fields, id
(PK), userId
(FK) and skillId
(FK),
mysql will automatically create an index on (id)
(unique), and index on (userId)
(non-unique) and an index on (skillId)
(non-unique).
您仍然需要一个额外的关于 (userId, SkillId)
的唯一索引.
You still need an additional unique index on (userId, skillId)
.
这可以替换 (userId)
上的非唯一索引,因为优化器可以在需要通过 (userId, SkillId)
索引>userId.但是,当索引被 mysql 中的外键使用时,创建和删除索引可能很麻烦,因此您可能只想添加复合唯一索引.
This could replace the non-unique index on (userId)
because the optimizer can use the (userId, skillId)
index whenever it needs to look up by userId
. However, creating and dropping indexes when the index is used by a foreign key in mysql can be cumbersome, so you might just want to add the composite unique index.
这篇关于复合唯一键是否在 MySQL 中建立索引?- 数据库管理系统的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!