Rechercher dans le manuel MySQL
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:
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:
- (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:
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:
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:
In both cases, key
indicates that the
optimizer will use 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
:
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 will improve
performance, use this statement:
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).
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-index-extensions.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.