8.3.9 索引扩展的使用
InnoDB通过向其附加主键列来自动扩展每个辅助索引。考虑此 table 定义:
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;
该 table 在第(i1, i2)
列上定义了主键。它还在列(d)
上定义了辅助索引k_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
当优化器考虑索引扩展时,会将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
在这两种情况下,key
table 示优化器将使用辅助索引k_d
,但是EXPLAIN的输出显示了使用扩展索引的以下改进:
-
key_len
从 4 字节变为 8 字节,table 示键查找使用的是d
和i1
列,而不仅仅是d
。 -
ref
值从const
变为const,const
,因为键查找使用两个键部分,而不是一个。 -
rows
计数从 5 减少到 1,table 示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%'
前面的语句包括FLUSH TABLES和FLUSH STATUS以刷新 table 缓存并清除状态计数器。
没有索引 extensions,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 |
+-----------------------+-------+
使用索引 extensions,SHOW STATUS产生此结果。 Handler_read_next的值从 5 减少到 1,table 明索引的使用效率更高:
+-----------------------+-------+
| 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 |
+-----------------------+-------+
optimizer_switch系统变量的use_index_extensions
标志允许控制在确定如何使用InnoDB
table 的辅助索引时优化器是否考虑主键列。默认情况下,启用use_index_extensions
。要检查禁用索引扩展的使用是否可以提高性能,请使用以下语句:
SET optimizer_switch = 'use_index_extensions=off';
优化程序对索引扩展的使用受制于对索引中关键部分的数量(16)和最大密钥长度(3072 字节)的通常限制。