InnoDB 自动通过在每个二级索引后追加主键列来扩展索引。考虑以下表定义：InnoDB automatically extends each secondary index by appending the primary key columns to it. 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;

此表在列 (i1, i2) 上定义了主键。它还在列 (d) 上定义了一个二级索引 k_d，但在内部 InnoDB 扩展此索引，并将其视为列 (d, i1, i2)。This table defines the primary key on columns (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).

优化器在确定如何以及是否使用扩展二级索引时，会考虑其主键列。这可以产生更高效的查询执行计划和更好的性能。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.

优化器可以使用扩展的二级索引进行 ref、range 和 index_merge 索引访问，用于松散索引扫描访问，用于连接和排序优化，以及用于 MIN()/MAX() 优化。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.

下面的示例展示了优化器是否使用扩展二级索引对执行计划的影响。假设 t1 包含以下行：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:

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.

当优化器不考虑索引扩展时，它将索引 k_d 仅视为 (d)。查询的 EXPLAIN 结果如下：When the optimizer does not consider index extensions, it treats the index k_d as only (d). EXPLAIN for the query produces this result:

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) 来生成更好的执行计划：When the optimizer takes index extensions into account, it treats k_d as (d, i1, i2). In this case, it can use the leftmost index prefix (d, i1) to produce a better execution plan:

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 输出显示了使用扩展索引带来的这些改进：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:

使用扩展索引时，优化器行为的差异也可以通过SHOW STATUS看到：Differences in optimizer behavior for use of extended indexes can also be seen with 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，用于刷新表缓存并清除状态计数器。The preceding statements include FLUSH TABLES and FLUSH STATUS to flush the table cache and clear the status counters.

没有索引扩展时，SHOW STATUS会生成如下结果：Without index extensions, SHOW STATUS produces this result:

+-----------------------+-------+
| 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，表明索引的使用更加高效：With index extensions, SHOW STATUS produces this result. The Handler_read_next value decreases from 5 to 1, indicating more efficient use of the index:

+-----------------------+-------+
| 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 是启用的。要检查禁用索引扩展的使用是否能提高性能，请使用以下语句：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. By default, use_index_extensions is enabled. To check whether disabling use of index extensions can improve performance, use this statement:

SET optimizer_switch = 'use_index_extensions=off';

优化器使用索引扩展受索引键部分数量（16）和最大键长度（3072 字节）的常规限制约束。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).