Invisible indexes are a new feature in MySQL 8.0 that provide the ability to mark an index as unavailable for use by the Optimizer. This means that the index will still be maintained and kept up-to-date as data is modified, but no queries will be permitted to make use of the index (even if the query uses
FORCE INDEX index_name).
Invisible indexes should not to be confused with disabled indexes, which the MyISAM storage engine implements (disabled indexes halt maintenance of an index). There are two notable use cases for invisible indexes:
Soft delete. Whenever performing a destructive operation in production, it is desirable to be able to observe before making the change permanent. Think of this as like an index “Recycle bin.” In the event that you were mistaken and the index was being used, it is only a metadata change to make it visible again - much faster than recreating or restoring from backup. For example:
ALTER TABLE Country ALTER INDEX c INVISIBLE;
Staged rollout. Whenever adding indexes, it is important to consider that they may change your existing query plans - sometimes in undesirable ways. Invisible indexes present an opportunity to stage the rollout of an index to a desirable time, potentially away from peak load and when you are actively in a position to observe the system. For example:
ALTER TABLE Country DROP INDEX c; ALTER TABLE Country ADD INDEX c (Continent) INVISIBLE; # after some time ALTER TABLE Country ALTER INDEX c VISIBLE;
All indexes default to being visible unless specified otherwise. You can search for invisible indexes across all schemas with:
SELECT * FROM information_schema.statistics WHERE is_visible='NO'; *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: world TABLE_NAME: Country NON_UNIQUE: 1 INDEX_SCHEMA: world INDEX_NAME: c SEQ_IN_INDEX: 1 COLUMN_NAME: Continent COLLATION: A CARDINALITY: 7 SUB_PART: NULL PACKED: NULL NULLABLE: INDEX_TYPE: BTREE COMMENT: disabled INDEX_COMMENT: IS_VISIBLE: NO
It’s a good idea to remove un-needed indexes. While most practitioners are aware that indexes hurt the performance of modifications (insert, update) this itself is a simplification. They can also hurt the performance of reads as the optimizer needs to evaluate them for plan selection.