InnoDBautomatically 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;

This table defines the primary key on columns(i1, i2). It also defines a secondary indexk_don column(d), but internallyInnoDBextends 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 forref,range, andindex_mergeindex access, for Loose Index Scan access, for join and sorting optimization, and forMIN()/MAX()optimization.

The following example shows how execution plans are affected by whether the optimizer uses extended secondary indexes. Suppose thatt1is 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.

When the optimizer does not consider index extensions, it treats the indexk_das only(d).EXPLAINfor 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

When the optimizer takes index extensions into account, it treatsk_das(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

In both cases,keyindicates that the optimizer uses secondary indexk_dbut theEXPLAINoutput shows these improvements from using the extended index:

Differences in optimizer behavior for use of extended indexes can also be seen withSHOW 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 includeFLUSH TABLESandFLUSH STATUSto flush the table cache and clear the status counters.

Without index extensions,SHOW STATUSproduces 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 | +-----------------------+-------+

With index extensions,SHOW STATUSproduces this result. TheHandler_read_nextvalue 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 | +-----------------------+-------+

Theuse_index_extensionsflag of theoptimizer_switchsystem variable permits control over whether the optimizer takes the primary key columns into account when determining how to use anInnoDBtable's secondary indexes. By default,use_index_extensionsis enabled. To check whether disabling use of index extensions can improve performance, use this statement:

SET optimizer_switch = 'use_index_extensions=off';

优化器使用索引扩展”t to the usual limits on the number of key parts in an index (16) and the maximum key length (3072 bytes).