Invisible indexes make it possible to test the effect of removing an index on query performance, without making a destructive change that must be undone should the index turn out to be required. Dropping and re-adding an index can be expensive for a large table, whereas making it invisible and visible are fast, in-place operations.
Thoughts on MySQL 8.0 Invisible Indexes
The use_invisible_indexes flag of the optimizer_switch system variable controls whether the optimizer uses invisible indexes for query execution plan construction. If the flag is off (the default), the optimizer ignores invisible indexes (the same behavior as prior to the introduction of this flag). If the flag is on, invisible indexes remain invisible but the optimizer takes them into account for execution plan construction.
A table with no explicit primary key may still have an effective implicit primary key if it has any UNIQUE indexes on NOT NULL columns. In this case, the first such index places the same constraint on table rows as an explicit primary key and that index cannot be made invisible. Consider the following table definition:
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:
I think invisible indexes are a great new feature that could be useful for many customers. We should to be able to use an invisible index if necessary, and be able to log queries that are trying to use invisible indexes.
MySQL 8.0 added a feature called invisible indexes that can improve your query tuning experience. Invisible indexes are indexes that have been created but do not get considered by the query optimizer when retrieving data.
Starting from MySQL 8.0, MySQL has introduced a new feature: invisible indexes. These invisible indexes allow you to mark indexes as unavailable/invisible to the query optimizer. MySQL maintains the invisible indexes and keeps them up to date whenever the data in the columns associated with the indexes changes.
By default, indexes in MySQL are visible. To make them invisible, we have to explicitly declare their visibility at the time of creation or by using the ALTER TABLE command. This is an online DDL operation which is done in a non-locking manner. MySQL provides us with the VISIBLE and INVISIBLE keywords to maintain this index visibility.
I do recommend making an index invisible as part of a process of decommissioning an index, similar to a soft delete of a column to avoid hurried recreation. And the Sys Schema will show you indexes that have not been used, just make sure you have long enough of a time period to let those queries that only run once a week/month/quarter or longer show themselves.
There are many new features in MySQL 8.0, but there are some that may not have caught your eye that are very handy for developers. Some, like invisible indexes, histograms, and true descending indexes are probably a little too deep down the DBA rabbit hole for most PHP Developers; there are three that can immediately benefit the average PHP Developer.
MySQL 8 introduces invisible indexes. Invisible indexes are indexes that actually exist, but are not visible to the MySQL query optimizer. Even if used by FORCE INDEX, the optimizer will not use invisible indexes. 2ff7e9595c
Comments