MySQL UNIQUE 约束是否会自动在字段上创建 INDEX？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9764120/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does a UNIQUE constraint automatically create an INDEX on the field(s)?
提问by gremo
Should I define a separate indexon the emailcolumn (for searching purposes), or is the index is "automatically" added along with UNIQ_EMAIL_USERconstraint?
我应该在列上定义一个单独的索引email（用于搜索目的），还是索引是与UNIQ_EMAIL_USER约束一起“自动”添加的？
CREATE TABLE IF NOT EXISTS `customer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`first` varchar(255) NOT NULL,
`last` varchar(255) NOT NULL,
`slug` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQ_SLUG` (`slug`),
UNIQUE KEY `UNIQ_EMAIL_USER` (`email`,`user_id`),
KEY `IDX_USER` (`user_id`)
) ENGINE=InnoDB;
EDIT: as suggested by Corbin i queried for EXPLAIN SELECT * FROM customer WHERE email = 'address'on empty table. This is the result, i don't know how to interpret it:
编辑：正如 Corbin 所建议的那样，我EXPLAIN SELECT * FROM customer WHERE email = 'address'在空桌子上查询。这是结果，我不知道如何解释它：
id select_type type possible_keys key key_len ref rows Extra
1 SIMPLE ALL NULL NULL NULL NULL 1 Using where
While adding an IXD_EMAIL to the table the same query shows:
在向表中添加 IXD_EMAIL 时，相同的查询显示：
id select_type type possible_keys key key_len ref rows Extra
1 SIMPLE ref IDX_EMAIL IDX_EMAIL 257 const 1 Using where
回答by piotrm
A unique keyis a special case of index, acting like a regular index with added checking for uniqueness. Using SHOW INDEXES FROM customeryou can see your unique keys are in fact B-tree type indexes.
一个独特的关键是指数的一个特例，像个与独特性增加检查的常规指标。使用SHOW INDEXES FROM customer您可以看到您的唯一键实际上是 B 树类型的索引。
A composite indexon (email, user_id)is enough, you don't need a separate index on email only - MySQL can use leftmost parts of a composite index. There may be some border cases where the size of an index can slow down your queries, but you should not worry about them until you actually run into them.
一个复合索引就(email, user_id)足够了，你不需要一个单独的电子邮件索引 - MySQL 可以使用复合索引的最左边部分。在某些情况下，索引的大小可能会减慢您的查询速度，但在您真正遇到它们之前，您不必担心它们。
As for testing index usage you should first fill your table with some data to make optimizer think it's actually worth to use that index.
至于测试索引使用情况，您应该首先用一些数据填充您的表，以使优化器认为使用该索引实际上是值得的。
