详解MySQL InnoDB的索引扩展
索引扩展,innodb通过将主键列附加到每个辅助索引中来自动扩展该索引。创建如下表结构:
mysql> 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; query ok, 0 rows affected (0.14 sec)
表t1在列(i1,i2)上定义了主键。同时也在列(d)上定义了一个辅助索引,但innodb扩展了这个索引并且将它视为(d,i1,i2)来处理。
在决定如何使用以及是否使用该索引时,优化器会考虑扩展辅助索引的主键列。这可以产生更高效的查询执行计划和更好的性能。
优化器可以使用扩展的二级索引来进行ref、range和index_merge索引访问,进行松散索引扫描,进行连接和排序优化,以及进行min()/max()优化。
下面的示例将显示优化器是否使用扩展辅助索引来影响执行计划 向表t1插入以下数据:
mysql> 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'); query ok, 25 rows affected (0.05 sec) records: 25 duplicates: 0 warnings: 0
假设执行下面的查询:
set optimizer_switch = 'use_index_extensions=off'; explain select count(*) from t1 where i1=3 and d= '2000-01-01' ;
在这种情况下,优化器不能使用主键,因为主键包含列(i1、i2),并且查询没有引用i2。相反,优化器可以使用列(d)上的辅助索引k_d,执行计划取决于是否使用扩展索引。
当优化器不考虑索引扩展时,它将索引k_d仅视为(d)
mysql> set optimizer_switch = 'use_index_extensions=off'; query ok, 0 rows affected (0.00 sec) mysql> 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.00 sec)
当优化器考虑到索引扩展时,它将k_d视为(d, i1, i2)。在这种情况下,它可以使用最左边的索引前缀(d, i1)来生成更好的执行计划
mysql> set optimizer_switch = 'use_index_extensions=on'; query ok, 0 rows affected (0.00 sec) mysql> 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来查看优化器在使用与不使用扩展索引时的差异:
mysql> flush table t1; query ok, 0 rows affected (0.01 sec) mysql> flush status; query ok, 0 rows affected (0.03 sec)
上面的flush table和flush status语句用来清除表的缓存和清除状数据统计数据。
不使用索引扩展时show status产生的结果如下:
mysql> set optimizer_switch = 'use_index_extensions=off'; query ok, 0 rows affected (0.01 sec) mysql> select count(*) from t1 where i1=3 and d= '2000-01-01'; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> 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 | +-----------------------+-------+ 7 rows in set (0.00 sec)
使用索引扩展时,show status产生的结果如下,其中handler_read_next的值从5减到1,指示使用这个索引更有效率:
mysql> flush table t1; query ok, 0 rows affected (0.01 sec) mysql> flush status -> ; query ok, 0 rows affected (0.02 sec) mysql> set optimizer_switch = 'use_index_extensions=on'; query ok, 0 rows affected (0.00 sec) mysql> select count(*) from t1 where i1=3 and d= '2000-01-01'; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> 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 | +-----------------------+-------+ 7 rows in set (0.01 sec)
系统变量optimizer_switch的use_index_extensions标志允许优化器在决定如何使用innodb表的辅助索引时使不使用主键列。默认情况下,use_index_extensions是启用的。为了检查禁用索引扩展是否可以提高性能可以执行以下语句:
mysql> set optimizer_switch = 'use_index_extensions=off'; query ok, 0 rows affected (0.01 sec)
以上就是详解mysql innodb的索引扩展的详细内容,更多关于mysql 索引扩展的资料请关注其它相关文章!