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 Index Scan, для оптимизации объединения и сортировки, а также для оптимизации 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 байта).