InnoDB автоматически расширяет каждый вторичный индекс, добавляя к нему столбцы первичного ключа. Рассмотрим определение таблицы:

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;

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

Оптимизатор учитывает столбцы первичного ключа расширенного вторичного индекса при определении того, как и следует ли использовать этот индекс. Это может привести к более эффективным планам выполнения запросов и лучшей производительности.

Оптимизатор может использовать расширенные вторичные индексы для ref, range, и index_merge доступа к индексу, для доступа с помощью сканирования индекса Loose, для оптимизации объединения и сортировки, и для оптимизации MIN()/MAX().

Следующий пример показывает, как планы выполнения запросов зависят от того, использует ли оптимизатор расширенные вторичные индексы. Предположим, что t1 заполнена следующими строками:

INSERT INTO t1 VALUES
(1, 1, '1998-01-01'), (1, 2, '1999-01-01'),
(1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
(1, 5, '2002-01-01'), (2, 1, '1998-01-01'),
(2, 2, '1999-01-01'), (2, 3, '2000-01-01'),
(2, 4, '2001-01-01'), (2, 5, '2002-01-01'),
(3, 1, '1998-01-01'), (3, 2, '1999-01-01'),
(3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
(3, 5, '2002-01-01'), (4, 1, '1998-01-01'),
(4, 2, '1999-01-01'), (4, 3, '2000-01-01'),
(4, 4, '2001-01-01'), (4, 5, '2002-01-01'),
(5, 1, '1998-01-01'), (5, 2, '1999-01-01'),
(5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
(5, 5, '2002-01-01');

Теперь рассмотрим этот запрос:

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

План выполнения запроса зависит от того, используется ли расширенный индекс.

Когда оптимизатор не учитывает расширения индексов, он рассматривает индекс k_d как только (d). EXPLAIN для запроса даёт такой результат:

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 4
          ref: const
         rows: 5
        Extra: Using where; Using index

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

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 8
          ref: const,const
         rows: 1
        Extra: Using index

В обоих случаях, key указывает, что оптимизатор использует вторичный индекс k_d, но вывод EXPLAIN показывает эти улучшения, полученные от использования расширенного индекса:

Различия в поведении оптимизатора при использовании расширенных индексов также могут быть видны с помощью SHOW STATUS:

FLUSH TABLE t1;
FLUSH STATUS;
SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
SHOW STATUS LIKE 'handler_read%'

В предыдущих операторах включены FLUSH TABLES и FLUSH STATUS для очистки кэша таблиц и сброса счётчиков состояния.

Без расширения индексов SHOW STATUS даёт такой результат:

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 5     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

С расширением индексов SHOW STATUS даёт такой результат. Значение Handler_read_next уменьшается с 5 до 1, что указывает на более эффективное использование индекса:

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 1     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

Флаг use_index_extensions переменной системы optimizer_switch позволяет управлять тем, учитывает ли оптимизатор столбцы первичного ключа при определении того, как использовать вторичные индексы таблицы InnoDB. По умолчанию use_index_extensions включён. Чтобы проверить, улучшает ли отключение использования расширенных индексов производительность, используйте этот оператор:

SET optimizer_switch = 'use_index_extensions=off';

Использование расширенных индексов оптимизатором ограничено обычными ограничениями на количество частей ключа в индексе (16) и максимальную длину ключа (3072 байта).