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;
该表定义列的主键(i1,
i2)
。它还
k_d
在列上定义了二级索引(d)
,但在内部InnoDB
扩展了该索引并将其视为列(d, i1, i2)
。
在确定如何以及是否使用该索引时,优化器会考虑扩展二级索引的主键列。这可以产生更高效的查询执行计划和更好的性能。
优化器可以将扩展二级索引用于
ref
、range
和
index_merge
索引访问、松散索引扫描访问、连接和排序优化以及
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');
现在考虑这个查询:
EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'
执行计划取决于是否使用扩展索引。
当优化器不考虑索引扩展时,它只将索引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:
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 |
+-----------------------+-------+
使用索引扩展,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 |
+-----------------------+-------+
系统变量的use_index_extensions
标志允许控制优化器在确定如何使用表的二级索引optimizer_switch
时是否考虑主键列
。InnoDB
默认情况下,use_index_extensions
启用。要检查禁用索引扩展是否会提高性能,请使用以下语句:
SET optimizer_switch = 'use_index_extensions=off';
优化器对索引扩展的使用受到索引中键部分数量 (16) 和最大键长度(3072 字节)的通常限制。