UNIQUE 约束是否会自动在字段上创建 INDEX?

2021-12-21 00:00:00 indexing sql mysql

我应该在 email 列上定义一个单独的索引(用于搜索目的)，还是该索引是与 UNIQ_EMAIL_USERUNIQ_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;

EDIT:按照 Corbin 的建议，我在空表上查询了 EXPLAIN SELECT * FROM customer 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.

(email, user_id) 上的 composite index 就足够了，您不需要只对 email 单独的索引 - 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.

相关文章