Почему 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.
