8.2.1.7 Use of Index Extensions 索引扩展适用

8.2.1.7 Use of Index Extensions 索引扩展适用

InnoDB 自动扩展每个secondary index 通过添加主键列到secondary index ,考虑这个表定义:

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),也定义了一个secondary index k_d 在列(d)，

但是内部的InnoDB 扩展这个index 对待为列(d,i1,i2)

在MySQL 5.6.9之前, 优化器不考虑把primary key 列加入到扩展 secondary index

当决定如何和是否使用那个索引

在5.6.9, 优化器把primary key 列加进去,从而更有效的查询执行计划和更好的性能。

优化器可以使用 extended secondary indexes 用于ref,range,和index_merge 索引访问,

对于松散的index scans, 对于关联和排序优化, MIN()/MAX() optimization.

下面的例子显示执行计划如何被影响通过是否优化器使用extended secondary indexes.

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’

优化器不能使用主键在这种情况下,因为组合列(i1,i2),但是查询不涉及i2.

代替的是,优化器可以使用secondary index k_d on (d), 执行计划取决于是否extended index被使用:

当优化器不考虑 index extensions, 它对待 index k_d 只作为 (d),查询计划如下:

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

当优化器使用 index extensions ,它对待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

在这两种情况下, 索引表明优化师使用secondary index k_d但是在优化器输出改进通过使用扩展索引；

key_len 从4字节到8字节, 表明索引查找使用列 d和i1,不止是d

ref 值随着常数改变而改变,常值因为key 寻找使用两个key，而不是一个。

行记录从5降到1, 表明InnoDB 应该需要检测更少的记录来产生结果.

额外值随着使用WHERE 而改变, 使用index来使用index.这意味着 记录可以被读取只使用一个index，

而不需要访问表数据的记录。