Объединить индекс покрытия с уникальным ограничением в 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 поле (из-за уникального ограничения), но было бы еще лучше иметь индекс покрытия для поля 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

Уже существует индекс для поля имени (из-за уникального ограничения), но было бы еще лучше иметь индекс покрытия для значения поля.

Нет. С уникальным ограничением вы получаете индекс для имени "бесплатно". И поскольку вы ищете только по имени, вам не нужен закрывающий (комбинированный) индекс со значением.

Вы получаете преимущества индекса по стоимости только тогда, когда используете запросы со значением в предложении where.

Объединенный индекс (имя, значение) может привести к дополнительным издержкам, когда вы никогда не будете искать значение (и поэтому никогда не будете использовать индекс). При операциях вставки / обновления индекс должен обновляться, что может привести к снижению производительности.