mysql - 将覆盖索引与 MySQL 中的唯一约束相结合

Question

考虑一个包含以下字段的表:

mysql> DESCRIBE my_table;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| pk    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(20)  | NO   | UNI |         |       |
| value | varchar(255) | NO   |     |         |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

请注意，字段名称具有唯一约束。

假设我想优化以下查询:

SELECT name, value
FROM my_table
WHERE name = 'my_name'

name 字段已经有一个索引(由于 unique 约束)，但是如果为字段 有一个覆盖索引会更好>值(value)也是如此。

只有一个索引用于唯一约束，当我运行 EXPLAIN 命令时，没有什么奇怪的事情发生:

mysql> EXPLAIN
    -> SELECT name, value
    -> FROM my_table
    -> WHERE name = "my_name";
+----+-------------+----------+-------+---------------+------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | my_table | const | name          | name | 62      | const |    1 |       |
+----+-------------+----------+-------+---------------+------+---------+-------+------+-------+

现在，如果我尝试添加一个覆盖索引，

ALTER TABLE my_table ADD INDEX idx_name_value (name, value);

它显示为查询的候选者，但未被选中!

mysql> EXPLAIN
    -> SELECT name, value
    -> FROM my_table
    -> WHERE name = "my_name";
+----+-------------+----------+-------+---------------------+------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys       | key  | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+---------------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | my_table | const | name,idx_name_value | name | 62      | const |    1 |       |
+----+-------------+----------+-------+---------------------+------+---------+-------+------+-------+

请注意，如果我删除唯一约束，

ALTER TABLE my_table DROP INDEX name;

覆盖索引按预期工作:

mysql> EXPLAIN
    -> SELECT name, value
    -> FROM my_table
    -> WHERE name = "my_name";
+----+-------------+----------+------+----------------+----------------+---------+-------+------+--------------------------+
| id | select_type | table    | type | possible_keys  | key            | key_len | ref   | rows | Extra                    |
+----+-------------+----------+------+----------------+----------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | my_table | ref  | idx_name_value | idx_name_value | 62      | const |    1 | Using where; Using index |
+----+-------------+----------+------+----------------+----------------+---------+-------+------+--------------------------+

那么如何在使用覆盖索引的同时仍然具有唯一性约束呢？

Answer 1

There already is an index for the name field (due to the unique constraint), but it would be even better to have a covering index for the field value as well.

没有。使用唯一约束，您可以“免费”获得名称索引。并且因为您仅使用名称进行搜索，所以不需要包含值的覆盖(组合)索引。

只有在 where 子句中使用带有值的查询时，您才能从值索引中受益。

当您从不搜索值(因此从不使用索引)时，组合索引(名称、值)甚至会产生开销。在插入/更新操作中，必须更新索引，这可能会降低性能。