InnoDB
通过将主键列附加到它来自动扩展每个二级索引。考虑这个表定义:
Press CTRL+C to copyCREATE 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
填充了这些行:
Press CTRL+C to copyINSERT 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');
现在考虑这个查询:
Press CTRL+C to copyEXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'
执行计划取决于是否使用扩展索引。
当优化器不考虑索引扩展时,它只将索引k_d
视为(d)
.
EXPLAIN
因为查询产生了这个结果:
Press CTRL+C to copymysql> 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)
来生成更好的执行计划:
Press CTRL+C to copymysql> 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
表示优化器使用二级索引k_d
,但
EXPLAIN
输出显示使用扩展索引的这些改进:
key_len
从 4 个字节变为 8 个字节,表明键查找使用列d
和i1
,而不仅仅是d
。ref
值从 变为const
因为const,const
键查找使用两个键部分,而不是一个。计数从5
rows
减少到 1,表明InnoDB
应该需要检查更少的行来生成结果。该
Extra
值从Using where; Using index
变为Using index
。这意味着可以仅使用索引读取行,而无需查询数据行中的列。
还可以通过以下方式看到使用扩展索引的优化器行为的差异SHOW
STATUS
:
Press CTRL+C to copyFLUSH 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
刷新表缓存并清除状态计数器。
没有索引扩展,SHOW
STATUS
产生这个结果:
Press CTRL+C to copy+-----------------------+-------+ | 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,表示更有效地使用索引:
Press CTRL+C to copy+-----------------------+-------+ | 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
启用。要检查禁用索引扩展是否可以提高性能,请使用以下语句:
Press CTRL+C to copySET optimizer_switch = 'use_index_extensions=off';
优化器对索引扩展的使用受到索引中键部分数量 (16) 和最大键长度(3072 字节)的通常限制。