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(发邮件时,请将#更换为@)进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。