Почему MySQL не может использовать частичный индекс первичного ключа?

Документация MySQL, описывающая использование расширений индекса, приводит в качестве примера следующую таблицу, за которой следует следующий запрос:

CREATE TABLE t1 (
    i1 INT NOT NULL DEFAULT 0,
    i2 INT NOT NULL DEFAULT 0,
    d DATE DEFAULT NULL,
    PRIMARY KEY (i1, i2),
    INDEX k_d (d)
) ENGINE = InnoDB;

SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';

InnoDB внутренне преобразует индекс k_d включить первичный ключ в конце. То есть фактический индекс k_d будет на (d, i1, i2), три колонны.

Документация продолжает объяснять это (выделение мое):

В этом случае оптимизатор не может использовать первичный ключ, поскольку он содержит столбцы (i1, i2), а запрос не ссылается на i2. Вместо этого оптимизатор может использовать вторичный индекс k_d для (d), и план выполнения зависит от того, используется ли расширенный индекс.

Я смущен приведенным выше утверждением. Сначала это говорит о том, что i1 недостаточно использовать индекс первичного ключа из двух столбцов (i1, i2), Затем во втором предложении говорится, что индекс k_d на (d, i1, i2) можно использовать, несмотря на то, что только d а также i1 используются, с i2 нет на месте.

Мое общее понимание индексов в MySQL и других разновидностях SQL состоит в том, что можно использовать левую часть индекса, если присутствует подмножество всех столбцов в индексе, начиная с левой.

Чем отличается индекс первичного ключа (кластеризованный) и некластеризованный вторичный индекс, который позволяет последнему использовать частичный индекс, а первый - нет?

Источник

2 ответа

Решение

Документация частично неточна на странице, на которую вы ссылаетесь.

Демо, запустить на MySQL 5.7.21:

mysql [localhost] {msandbox} (test) > CREATE TABLE t1 (
    ->     i1 INT NOT NULL DEFAULT 0,
    ->     i2 INT NOT NULL DEFAULT 0,
    ->     d DATE DEFAULT NULL,
    ->     PRIMARY KEY (i1, i2),
    ->     INDEX k_d (d)
    -> ) ENGINE = InnoDB;

mysql [localhost] {msandbox} (test) > explain SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | PRIMARY,k_d   | PRIMARY | 4       | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+

Этот запрос выбирает ПЕРВИЧНЫЙ индекс, и вы можете увидеть key_len 4, доказывая, что он будет использовать только один из 32-битных столбцов INT.

При использовании таблиц InnoDB MySQL часто предпочитает использовать индекс PRIMARY (кластерный индекс), потому что он более эффективен, чем использование вторичного индекса.

Источник

В таком случае

WHERE i1 = 3 AND d = '2000-01-01';

Я предпочитаю прямо сказать INDEX(d, i1) (или же INDEX(i1, d)). Мое обоснование заключается в том, что я говорю читателю, что подумал об индексах и понял, что это лучше всего подходит для запроса. И это будет индекс "покрытия", следовательно, немного быстрее.

Конечно, INDEX(d) Вы должны были быть эквивалентны INDEX(d, i1, i2), который должен был использоваться эффективно и действенно. Что касается того, почему, я бы предположил упущение в Оптимизаторе.

Что касается документации, есть несколько плохо сформулированных мест. Они приветствуют критику документации на bugs.mysql.com.

Источник
Другие вопросы по тегам