UNIQUE约束是否会自动在字段上创建INDEX？ [英] Does a UNIQUE constraint automatically create an INDEX on the field(s)?

问题描述

我应该在电子邮件列上定义单独的索引（用于搜索目的），或者是否自动添加索引 UNIQ_EMAIL_USER 约束？

Should I define a separate index on the email column (for searching purposes), or is the index is "automatically" added along with UNIQ_EMAIL_USER constraint?

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;

编辑：根据Corbin的建议我查询 EXPLAIN SELECT * FROM客户WHERE email ='address'在空表上。这是结果，我不知道如何解释它：

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:

id select_type type possible_keys key  key_len ref  rows Extra
1  SIMPLE      ALL  NULL          NULL NULL    NULL 1    Using where

将IXD_EMAIL添加到表格中查询显示：

While adding an IXD_EMAIL to the table the same query shows:

id select_type type possible_keys key       key_len ref   rows Extra
1  SIMPLE      ref  IDX_EMAIL     IDX_EMAIL 257     const 1    Using where

推荐答案

唯一键是索引的特例，代理像常规索引一样，增加了对唯一性的检查。使用 SHOW INDEXES FROM customer ，您可以看到您的唯一键实际上是B树类型索引。

A unique key is a special case of index, acting like a regular index with added checking for uniqueness. Using SHOW INDEXES FROM customer you can see your unique keys are in fact B-tree type indexes.

复合index（email，user_id）就足够了，你不需要单独的电子邮件索引 - mysql可以使用复合索引的最左边部分。可能存在一些边界情况，其中索引的大小可能会减慢您的查询速度，但在实际遇到它们之前，您不必担心它们。

A composite index on (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.

至于测试索引使用你首先应该用一些数据填充你的表，以使优化器认为使用该索引实际上是值得的。

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.

这篇关于UNIQUE约束是否会自动在字段上创建INDEX？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！