InnoDB automatically extends each secondary index by appending the primary key columns to it. InnoDB通过向每个辅助索引添加主键列来自动扩展它。Consider this table definition:考虑这个表定义：

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;

This table defines the primary key on columns (i1, i2). 此表定义了列(i1, i2)上的主键。It also defines a secondary index k_d on column (d), but internally InnoDB extends this index and treats it as columns (d, i1, i2).它还定义了列(d)上的二级索引k_d，但InnoDB在内部扩展了该索引并将其视为列(d, i1, i2)。

The optimizer takes into account the primary key columns of the extended secondary index when determining how and whether to use that index. 优化器在确定如何以及是否使用该索引时，会考虑扩展辅助索引的主键列。This can result in more efficient query execution plans and better performance.这可以产生更高效的查询执行计划和更好的性能。

The optimizer can use extended secondary indexes for ref, range, and index_merge index access, for Loose Index Scan access, for join and sorting optimization, and for MIN()/MAX() optimization.优化器可以将扩展的二级索引用于ref、range和index_merge索引访问、松散索引扫描访问、连接和排序优化以及MIN()/MAX()优化。

The following example shows how execution plans are affected by whether the optimizer uses extended secondary indexes. 下面的示例显示了优化器是否使用扩展的二级索引对执行计划的影响。Suppose that t1 is populated with these rows:假设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');

Now consider this query:现在考虑这个问题：

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

The execution plan depends on whether the extended index is used.执行计划取决于是否使用扩展索引。

When the optimizer does not consider index extensions, it treats the index k_d as only (d). 当优化器不考虑索引扩展时，它只将索引k_d视为(d)。EXPLAIN for the query produces this result: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

When the optimizer takes index extensions into account, it treats k_d as (d, i1, i2). 当优化器考虑索引扩展时，它将k_d视为(d, i1, i2)。In this case, it can use the leftmost index prefix (d, i1) to produce a better execution plan:在这种情况下，它可以使用最左边的索引前缀(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

In both cases, key indicates that the optimizer uses secondary index k_d but the EXPLAIN output shows these improvements from using the extended index:在这两种情况下，key表示优化器使用二级索引k_d，但EXPLAIN输出显示了使用扩展索引的这些改进：

Differences in optimizer behavior for use of extended indexes can also be seen with SHOW STATUS:使用扩展索引时优化器行为的差异也可以通过SHOW STATUS看到：

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

The preceding statements include FLUSH TABLES and FLUSH STATUS to flush the table cache and clear the status counters.前面的语句包括FLUSH TABLES和FLUSH STATUS，用于刷新表缓存和清除状态计数器。

Without index extensions, SHOW STATUS produces this result:如果没有索引扩展，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     |
+-----------------------+-------+

With index extensions, SHOW STATUS produces this result. 通过索引扩展，SHOW STATUS生成此结果。The Handler_read_next value decreases from 5 to 1, indicating more efficient use of the index: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     |
+-----------------------+-------+

The use_index_extensions flag of the optimizer_switch system variable permits control over whether the optimizer takes the primary key columns into account when determining how to use an InnoDB table's secondary indexes. optimizer_switch系统变量的use_index_extensions标志允许控制优化器在确定如何使用InnoDB表的二级索引时是否考虑主键列。By default, use_index_extensions is enabled. 默认情况下，启用use_index_extensions。To check whether disabling use of index extensions can improve performance, use this statement:要检查禁用索引扩展是否可以提高性能，请使用以下语句：

SET optimizer_switch = 'use_index_extensions=off';

Use of index extensions by the optimizer is subject to the usual limits on the number of key parts in an index (16) and the maximum key length (3072 bytes).优化器对索引扩展的使用受索引中键部分数量（16）和最大键长度（3072字节）的通常限制。