索引扩展的使用
InnoDB
通过将主键列附加到辅助索引来自动扩展每个辅助索引。考虑此表定义:
CREATE TABLE t1 ( i1 INT NOT NULLDEFAULT 0, i2 INT NOT NULLDEFAULT 0, d DATEDEFAULT 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 t1VALUES (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 t1WHERE i1 = 3 AND d = '2000-01-01'
执行计划取决于是否使用扩展索引。
当优化器不考虑索引扩展时,它将索引k_d
视为(d)
。EXPLAIN
对于查询产生以下结果:
mysql>EXPLAIN SELECT COUNT(*)FROM t1WHERE 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 Extr : Using where; Using index
当优化需要索引扩展到帐户,它把k_d
作为(d, i1, i2)
。在这种情况下,它可以使用最左边的索引前缀(d, i1)
来产生更好的执行计划:
mysql>EXPLAIN SELECT COUNT(*)FROM t1WHERE 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 Extr : Using index
在这两种情况下,都key
表明优化器将使用二级索引,k_d
但是EXPLAIN
输出显示了使用扩展索引的以下改进:
key_len
从4个字节到8个字节去,表明键查找中使用的列d
和i1
,而不仅仅是d
。- 该
ref
值从改变const
到const,const
,因为键查找使用两个关键部分,没有之一。 rows
计数降低从5到1,表明InnoDB
应该需要检查更少的行,以产生结果。- 该
Extr
值从变化Using where; Using index
到Using index
。这意味着可以仅使用索引读取行,而无需查阅数据行中的列。
使用扩展索引的优化器行为也可以通过以下方式看到SHOW STATUS
:
FLUSH TABLE t1;FLUSH STATUS ;SELECT COUNT(*)FROM t1WHERE i1 = 3 AND d = '2000-01-01';SHOW STATUS LIKE 'handler_read%'
前面的语句包括FLUSH TABLES
和FLUSH STATUS
刷新表缓存并清除状态计数器。
没有索引扩展名,将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 | +----------------------- +------- +
使用索引扩展,可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字节)的通常限制。