MySQL 8 新特性之Invisible Indexes
背景
索引是把双刃剑，在提升查询速度的同时会减慢DML的操作。毕竟，索引的维护需要一定的成本。所以，对于索引，要加上该加的，删除无用的。前者是加法，后者是减法。但在实际工作中，大家似乎更热衷于前者，而很少进行后者。究其原因，在于后者，难。难的不是操作本身，而是如何确认一个索引是无用的。
如何确认无用索引
在不可见索引出现之前，大家可以通过sys.schema_unused_indexes来确定无用索引。在MySQL5.6中，即使没有sys库，也可通过该视图的基表来进行查询。
mysql>showcreatetablesys.schema_unused_indexes\G ***************************1.row*************************** View:schema_unused_indexes CreateView:CREATEALGORITHM=MERGEDEFINER=`mysql.sys`@`localhost`SQLSECURITYINVOKERVIEW`sys`.`schema_unused_indexes`( `object_schema`,`object_name`,`index_name`)ASselect`t`.`OBJECT_SCHEMA`AS`object_schema`,`t`.`OBJECT_NAME`AS`object_name`,`t`.`INDEX_NAME`AS`index_name`from(`performance_schema`.`table_io_waits_summary_by_index_usage``t`join`information_schema`.`STATISTICS``s`on(((`t`.`OBJECT_SCHEMA`=convert(`s`.`TABLE_SCHEMA`usingutf8mb4))and(`t`.`OBJECT_NAME`=convert(`s`.`TABLE_NAME`usingutf8mb4))and(convert(`t`.`INDEX_NAME`usingutf8)=`s`.`INDEX_NAME`))))where((`t`.`INDEX_NAME`isnotnull)and(`t`.`COUNT_STAR`=0)and(`t`.`OBJECT_SCHEMA`<>'mysql')and(`t`.`INDEX_NAME`<>'PRIMARY')and(`s`.`NON_UNIQUE`=1)and(`s`.`SEQ_IN_INDEX`=1))orderby`t`.`OBJECT_SCHEMA`,`t`.`OBJECT_NAME`character_set_client:utf8mb4 collation_connection:utf8mb4_0900_ai_ci 1rowinset,1warning(0.00sec)
但这种方式也有不足，
1.如果实例发生重启，performance_schema中的数据就会清零。
2.如果基于上面的查询删除了索引，查询性能突然变差，怎么办？
不可见索引的出现，可有效弥补上述不足。将index设置为invisible，会导致优化器在选择执行计划时，自动忽略该索引，即便使用了FORCEINDEX。
当然，这个是由optimizer_switch变量中use_invisible_indexes选项决定的，默认为off。如果想看一个查询在索引调整前后执行计划的差别，可在会话级别调整use_invisible_indexes的值，如，
mysql>showcreatetableslowtech.t1\G ***************************1.row*************************** Table:t1 CreateTable:CREATETABLE`t1`( `id`int(11)NOTNULL, `name`varchar(10)DEFAULTNULL, PRIMARYKEY(`id`), KEY`idx_name`(`name`)/*!80000INVISIBLE*/ )ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci 1rowinset(0.00sec) mysql>explainselect*fromslowtech.t1wherename='a'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ |id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ |1|SIMPLE|t1|NULL|ALL|NULL|NULL|NULL|NULL|6|16.67|Usingwhere| +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1rowinset,1warning(0.00sec) mysql>setsessionoptimizer_switch="use_invisible_indexes=on"; QueryOK,0rowsaffected(0.00sec) mysql>explainselect*fromslowtech.t1wherename='a'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ |id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ |1|SIMPLE|t1|NULL|ref|idx_name|idx_name|43|const|1|100.00|Usingindex| +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ 1rowinset,1warning(0.00sec)
不可见索引的常见操作
createtablet1(idintprimarykey,namevarchar(10),indexidx_name(name)invisible); altertablet1alterindexidx_namevisible; altertablet1alterindexidx_nameinvisible;
如何查看哪些索引不可见
mysql>selecttable_schema,table_name,index_name,column_name,is_visiblefrominformation_schema.statisticswhereis_visible='no'; +--------------+------------+------------+-------------+------------+ |TABLE_SCHEMA|TABLE_NAME|INDEX_NAME|COLUMN_NAME|IS_VISIBLE| +--------------+------------+------------+-------------+------------+ |slowtech|t1|idx_name|name|NO| +--------------+------------+------------+-------------+------------+ 1rowinset(0.00sec)
注意
1.主键索引不可被设置为invisible。
总结
以上所述是小编给大家介绍的MySQL8新特性之InvisibleIndexes，希望对大家有所帮助，如果大家有任何疑问欢迎给我留言，小编会及时回复大家的！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。