What's New in MySQL 8.0-Invisible Index

Official document: https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html

MySQL 8.0 invisible index Learning

MySQL supports an invisible index, which is not used by the optimizer. The invisible index feature cannot be used for primary keys.

The default index is visible. You can use the keywords visible and invisible in create table, create index, and alter table operations to specify whether the index is visible.

Create table T1 (I int, j int, k int, index i_idx (I) invisible) engine = innodb;create index j_idx on T1 (j) invisible;alter table T1 add index k_idx (k) invisible

Modify the visibility of an existing index:

Alter table t1 alter index i_idx invisible;alter table t1 alter index i_idx visible

You can view the visibility of the index through information_schema.statistics and show index:

> select index_name, is_visible- > from information_schema.statistics- > where table_schema = 'abce' and table_name =' T1' +-+-+ | INDEX_NAME | IS_VISIBLE | +-+-+ | i_idx | NO | +-+-+ 1 row in set (0.00 sec) > show index from T1 +- -+ | Table | Non_unique | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-- -- + -+ | T1 | 1 | i_idx | 1 | I | A | 0 | NULL | NULL | YES | BTREE | NO | NULL | +- -+- -+ 1 row in set (0.00 sec)

Invisible indexes can be used to test the impact on query performance after removing the index. After all, for large tables, deleting and rebuilding indexes are very expensive operations. The use_invisible_indexes flag in the system variable optimizer_switch controls whether the optimizer uses invisible indexes to build the execution plan. If use_invisible_indexes=off (the default setting), the optimizer ignores invisible indexes; if set to on, the indexes are still invisible, but the optimizer takes invisible indexes into account when generating execution plans.

The converter takes into account invisible indexes when generating the execution plan.

Mysql > show variables like'% optimizer_switch%'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on | number of rows returned: [1], time: 14 ms.

For example:

Alter table t1 alter index i_idx invisible

If the index is set to invisible, the optimizer does not consider the index, that is, if you do not have this feature, you have to delete the index and rebuild the index to test