8.3.10 Use of Index Extensions

InnoDB automatically extends each secondary index by appending the primary key columns to it. Consider this table definition:

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;

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.

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.

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:

这张表定义了主键索引（i1,i2）.也定义了一个二级索引k_d在列上（d）。但是内在的InnoDB扩展了这个索引以（d,i1,i2）方式处理。

优化器会考虑怎么使用以及是否使用二级索引的对主键的扩展。这样可以更有效的执行查询计划以及更好的查询性能

优化器能用下面的二级索引类型使用扩展特性，ref、range和index_merge索引访问、零散的索引扫描、join和排序优化以及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');

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). EXPLAIN for the query produces this result:

执行计划依赖使用使用了扩展索引

当优化器没有使用索引扩展时，仅仅用索引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

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:

当优化器使用索引扩展，对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

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_len goes from 4 bytes to 8 bytes, indicating that key lookups use columns d and i1, not just d.

• The ref value changes from const to const,const because the key lookup uses two key parts, not one.

• The rows count decreases from 5 to 1, indicating that InnoDB should need to examine fewer rows to produce the result.

• The Extra value changes from Using where; Using index to Using index. This means that rows can be read using only the index, without consulting columns in the data row.

Differences in optimizer behavior for use of extended indexes can also be seen with SHOW STATUS:

在两种情况下，key都表明使用了二级索引k_d但是，但是对于EXPLAIN执行结果，用了索引扩展执行结果展示了一些提升。

• key_len从4个字节到8个字节，表明key的查找用的是d和i1，而不是仅仅d
• ref值从一个const到const，const,因为他们用了两个key，而不是一个
• 行数由5个降低到1个，表明InnoDB需要查询更少的行就可以得到结果
• Extra值从Using where；Using index 到Using index.这意味着仅仅通过索引进行数据读取，不用查询数据

使用索引扩展不同的地方也可以通过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.

Without index extensions, SHOW STATUS produces this result:

上面的语句包括了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     |
+-----------------------+-------+

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:

有了索引扩展，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     |
+-----------------------+-------+

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:

对于是否使用InnoDB 表的二级索引扩展功能，取决于需要考虑怎么使用主键和索引。默认使用扩展索引功能是打开的。为了检测索引扩展是否能提升性能，可以使用下面语句将索引扩展功能关掉

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字节）