索引扩展
2020-04-07 23:02 abce 阅读(394) 评论(0) 编辑 收藏 举报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)上定义了主键。它还在列(d)上定义了二级索引k_d,但是InnoDB在内部扩展了该索引并将其视为列(d,i1,i2)。
在确定如何以及是否使用该索引时,优化器会考虑扩展二级索引的主键列。这可以导致更有效的查询执行计划和更好的性能。
优化器可以将扩展的辅助索引用于ref,range和index_merge索引访问,松散索引扫描访问(Loose Index Scan access),join和排序优化以及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');
对于查询语句:
select count(*) from t1 where i1 = 3 and d = '2000-01-01';
当优化器不考虑索引扩展时,它将索引k_d仅视为(d)。查询的EXPLAIN产生以下结果:
>SET optimizer_switch = 'use_index_extensions=off'; Query OK, 0 rows affected (0.01 sec) >explain select count(*) from t1 where i1 = 3 and d = '2000-01-01'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ref possible_keys: PRIMARY,k_d key: PRIMARY key_len: 4 ref: const rows: 5 filtered: 20.00 Extra: Using where 1 row in set, 1 warning (0.01 sec)
当优化器考虑索引扩展时,它将k_d视为(d,i1,i2)。 在这种情况下,它可以使用最左边的索引前缀(d,i1)来制定更好的执行计划:
>SET optimizer_switch = 'use_index_extensions=on'; Query OK, 0 rows affected (0.00 sec) >explain select count(*) from t1 where i1 = 3 and d = '2000-01-01'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ref possible_keys: PRIMARY,k_d key: k_d key_len: 8 ref: const,const rows: 1 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec)
在这两种情况下,key都表明优化器将使用二级索引k_d,但是EXPLAIN输出显示了使用扩展索引的这些改进:
·key_len从4字节变为8字节,这表明键查找使用的是列d和i1,而不仅仅是d。
·ref值从const变为const,const,因为键查找使用两个键部分,而不是一个。
·rows从5减少到1,这表明InnoDB应该需要检查更少的行就能产生结果。
·Extra从Using where; Using index 改为 Using index。 这意味着可以仅使用索引读取行,而无需查阅数据行中的列。
也可以通过show status命令来查看是否开启索引扩展的区别:
#开启 >SET optimizer_switch = 'use_index_extensions=on'; >FLUSH TABLE t1; >FLUSH STATUS; >SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'; >SHOW STATUS LIKE 'handler_read%'; +-----------------------+-------+ | 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 | +-----------------------+-------+ #关闭 >SET optimizer_switch = 'use_index_extensions=off'; >FLUSH TABLE t1; >FLUSH STATUS; >SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'; >SHOW STATUS LIKE 'handler_read%'; +-----------------------+-------+ | 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 | +-----------------------+-------+