Почему 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 будет k_d (d, i1, i2), три столбца.

В документации далее объясняется, что (внимание мое):

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

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

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

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

Ответ 1

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

Демо, работайте на 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 |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+

Этот запрос выбирает индекс PRIMARY, и вы можете видеть, что key_len равен 4, доказывая, что он будет использовать только один из 32-битных столбцов INT.

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

Ответ 2

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

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

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

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

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