MySQL 8.0 – Invisible Indexes

Overview

In this blog, we aim to discuss the new MySQL 8.0 feature: invisible indexes and how to manage index visibility.

MySQL 8.0 supports invisible indexes, that is, indexes not used by the optimizer. This feature applies to indexes other than primary keys (either explicit or implicit).

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.

Index visibility does not affect index maintenance. An index still continues to be updated according to the changes made to table rows, and a unique index still prevents the insertion of duplicates into a column, regardless of whether the index is visible or invisible. 

How to Create Invisible Indexes

To create a new invisible index, you can issue the following statement:

CREATE INDEX index_name ON table_name (idx1) INVISIBLE;

In this syntax, the INVISIBLE keyword indicates that the index you are creating is hidden; if not specified, the index will be made VISIBLE by default.

If you wish to change the visibility of an existing index, you use the following statement:

ALTER TABLE table_name ALTER INDEX index_name INVISIBLE;

Once done, you can find the indexes and their visibility by querying the statistics table in the information_schema database:

SELECT index_name, is_visible FROM information_schema.statistics WHERE table_schema = DB_name AND table_name = table_name;

If you wish to change the invisible indexes back to being visible, this command can be used

ALTER TABLE table_name ALTER INDEX index_name VISIBLE;

To control visible indexes used by the query optimizer, MySQL uses the use_invisible_indexes flag of the optimizer_switch system variable. By default, the use_invisible_indexes is off:

SELECT @@optimizer_switch;

Benefits of Using this Feature

As mentioned earlier, the query optimizer does not use invisible indexes, so why do we use invisible indexes? 

To answer that, 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 be required. 

Dropping and re-adding an index can be expensive for a large table, adding extra overhead to your system, whereas making it invisible and visible are fast and in-place operations, which happen in online and non-locking ways.

If an index is made invisible, and it is actually needed or used by the optimizer, there are a few ways to notice the effect of its absence on queries for the table like below:

• Errors occur for queries that include index hints that refer to the invisible index
• Performance Schema data shows an increase in workload for affected queries
• Queries have different EXPLAIN execution plans
• Queries appear in the slow query log that did not appear there previously

If any such index is necessary, it can be easily changed back to VISIBLE.

MySQL invisible index limitations

Please note that the index on a primary key column cannot be made invisible. If you try to do so, MySQL will issue an error.

In addition, an implicit primary key index also cannot be invisible. When you define a UNIQUE index on a NOT NULL column of a table that does not have a primary key, MySQL implicitly understands that this column is the primary key column and will not allow you to make that index invisible.

For, e.g.:

Consider the following table definition:

CREATE TABLE test (a INT NOT NULL,b INT NOT NULL,UNIQUE b_idx (b)) ENGINE = InnoDB;

The definition shows there is no explicit primary key defined here, but the UNIQUE index on NOT NULL column b places the same constraint on rows as a primary key, and hence it cannot be made invisible:

ALTER TABLE test ALTER INDEX b_idx INVISIBLE;

ERROR 3522 (HY000): A primary key index cannot be invisible.

Now, let’s say we add an explicit primary key to the table:

ALTER TABLE test ADD PRIMARY KEY (a);

This direct primary key cannot be made invisible now. But the unique index on b no longer acts as an implicit primary key, so it can be made invisible now.

ALTER TABLE test ALTER INDEX a_idx INVISIBLE;

ERROR 3522 (HY000): A primary key index cannot be invisible.

ALTER TABLE test ALTER INDEX b INVISIBLE;

Query OK, 0 rows affected (0.03 sec)

Conclusion

This blog describes MySQL invisible indexes, how to create an invisible index, and how to change the visibility of an existing index. I hope you found this blog helpful!