Рассмотрим таблицу со следующими полями:
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 |
+----+-------------+----------+------+----------------+----------------+---------+-------+------+--------------------------+
Итак, как я могу использовать покрывающий индекс и при этом иметь уникальное ограничение?
