详解MySQL InnoDB的索引扩展
索引扩展,InnoDB通过将主键列附加到每个辅助索引中来自动扩展该索引。创建如下表结构:
mysql>CREATETABLEt1( ->i1INTNOTNULLDEFAULT0, ->i2INTNOTNULLDEFAULT0, ->dDATEDEFAULTNULL, ->PRIMARYKEY(i1,i2), ->INDEXk_d(d) ->)ENGINE=InnoDB; QueryOK,0rowsaffected(0.14sec)
表t1在列(i1,i2)上定义了主键。同时也在列(d)上定义了一个辅助索引，但InnoDB扩展了这个索引并且将它视为(d,i1,i2)来处理。
在决定如何使用以及是否使用该索引时，优化器会考虑扩展辅助索引的主键列。这可以产生更高效的查询执行计划和更好的性能。
优化器可以使用扩展的二级索引来进行ref、range和index_merge索引访问，进行松散索引扫描，进行连接和排序优化，以及进行MIN()/MAX()优化。
下面的示例将显示优化器是否使用扩展辅助索引来影响执行计划向表t1插入以下数据:
mysql>INSERTINTOt1VALUES(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'); QueryOK,25rowsaffected(0.05sec) Records:25Duplicates:0Warnings:0
假设执行下面的查询:
SEToptimizer_switch='use_index_extensions=off'; explainselectcount(*)fromt1wherei1=3andd='2000-01-01';
在这种情况下，优化器不能使用主键，因为主键包含列(i1、i2)，并且查询没有引用i2。相反，优化器可以使用列(d)上的辅助索引k_d，执行计划取决于是否使用扩展索引。
当优化器不考虑索引扩展时，它将索引k_d仅视为(d)
mysql>SEToptimizer_switch='use_index_extensions=off'; QueryOK,0rowsaffected(0.00sec) mysql>explainselectcount(*)fromt1wherei1=3andd='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:Usingwhere 1rowinset,1warning(0.00sec)
当优化器考虑到索引扩展时，它将k_d视为(d,i1,i2)。在这种情况下，它可以使用最左边的索引前缀(d,i1)来生成更好的执行计划
mysql>SEToptimizer_switch='use_index_extensions=on'; QueryOK,0rowsaffected(0.00sec) mysql>explainselectcount(*)fromt1wherei1=3andd='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:Usingindex 1rowinset,1warning(0.00sec)
在这两种情况下，key表示优化器将使用辅助索引k_d，但是EXPLAIN输出显示了使用扩展索引所带来的这些改进:
.key_len从4字节变成了8字节，指示键查找使用了列d和i1，不仅仅是d。
.ref的值从const变成了const,const,因为键查找使用两个键的列而不是一个。
.rows:从5减到1，指示InnoDB将会检查更少的行来生成查询结果。
.Extra值从Usingwhere;Usingindex变成了Usingindex。这意味着查询记录只需要使用索引而不用查询数据行记录。
可以使用showstatus来查看优化器在使用与不使用扩展索引时的差异:
mysql>flushtablet1; QueryOK,0rowsaffected(0.01sec) mysql>flushstatus; QueryOK,0rowsaffected(0.03sec)
上面的flushtable和flushstatus语句用来清除表的缓存和清除状数据统计数据。
不使用索引扩展时showstatus产生的结果如下:
mysql>SEToptimizer_switch='use_index_extensions=off'; QueryOK,0rowsaffected(0.01sec) mysql>selectcount(*)fromt1wherei1=3andd='2000-01-01'; +----------+ |count(*)| +----------+ |1| +----------+ 1rowinset(0.00sec) mysql>showstatuslike'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| +-----------------------+-------+ 7rowsinset(0.00sec)
使用索引扩展时，showstatus产生的结果如下，其中handler_read_next的值从5减到1，指示使用这个索引更有效率:
mysql>flushtablet1; QueryOK,0rowsaffected(0.01sec) mysql>flushstatus ->; QueryOK,0rowsaffected(0.02sec) mysql>SEToptimizer_switch='use_index_extensions=on'; QueryOK,0rowsaffected(0.00sec) mysql>selectcount(*)fromt1wherei1=3andd='2000-01-01'; +----------+ |count(*)| +----------+ |1| +----------+ 1rowinset(0.00sec) mysql>showstatuslike'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| +-----------------------+-------+ 7rowsinset(0.01sec)
系统变量optimizer_switch的use_index_extensions标志允许优化器在决定如何使用InnoDB表的辅助索引时使不使用主键列。默认情况下，use_index_extensions是启用的。为了检查禁用索引扩展是否可以提高性能可以执行以下语句:
mysql>SEToptimizer_switch='use_index_extensions=off'; QueryOK,0rowsaffected(0.01sec)
以上就是详解MySQLInnoDB的索引扩展的详细内容，更多关于MySQL索引扩展的资料请关注毛票票其它相关文章！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。