How Invisible Indexes in MySQL 8.0 Simplify Index Management

What Is the Purpose of Invisible Indexes

MySQL 8.0 supports Invisible Indexes , a feature that lets an index be invisible to the optimizer while still being maintained during data changes. This acts like an index recycle bin, allowing you to hide rarely used indexes during optimization without dropping them.

When a database has accumulated many indexes over time, removing and recreating them can be costly, especially for large tables. By marking an index as invisible, you can test the impact of its removal and later make it visible again if needed.

In MySQL 8.0.0 only InnoDB supports invisible indexes; from 8.0.1 all storage engines can use them.

How to Use Invisible Indexes

Specify When Creating a Table

CREATE TABLE t1 (
  i INT,
  j INT,
  k INT,
  INDEX i_idx (i) INVISIBLE
) ENGINE=InnoDB;

Specify When Creating an Index

CREATE INDEX j_idx ON t1 (j) INVISIBLE;

Alter Table to Add an Invisible Index

ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;

Make an Invisible Index Visible

ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

Check Index Visibility

SELECT INDEX_NAME, IS_VISIBLE
FROM information_schema.statistics
WHERE TABLE_SCHEMA='db1' AND TABLE_NAME='t1';

List All Invisible Indexes

SELECT * FROM information_schema.statistics WHERE is_visible='NO';

Note: Primary key indexes cannot be made invisible. Also, a UNIQUE NOT NULL column that serves as an implicit primary key cannot be set invisible.

References

https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html
http://mysqlserverteam.com/mysql-8-0-invisible-indexes/
https://dzone.com/articles/thoughts-on-mysql-80-invisible-indexes