10.3.10 索引扩展的使用
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;
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:
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:
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:
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 columnsd
andi1
, not justd
. -
The
ref
value changes fromconst
toconst,const
because the key lookup uses two key parts, not one. -
The
rows
count decreases from 5 to 1, indicating thatInnoDB
should need to examine fewer rows to produce the result. -
The
Extra
value changes fromUsing where; Using index
toUsing 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
:
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:
+-----------------------+-------+
| 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:
+-----------------------+-------+
| 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:
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).