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 a FORCE INDEX hint).
Invisible indexes should not to be confused with disabled indexes, which the MyISAM storage engine implements (disabled indexes halt maintenance of an index).
When is an index safe to drop?
Indexes are great when the query optimizer can find an optimal execution plan to leverage them. In these cases they will reduce the execution time by orders of magnitude. But it’s important to remember that they come at a cost, and this cost is in no way negligible. 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.
You can see which indexes are currently unused with the built-in SYS schema:
|
1
2
3
4
5
6
7
8
|
mysql> SELECT * FROM sys.schema_unused_indexes; +---------------+-------------+------------+ | object_schema | object_name | index_name | +---------------+-------------+------------+ | world | Country | p | | world | Country | p_c | +---------------+-------------+------------+ 2 rows in set (0.01 sec) |
But maybe your server has only just started up? This does not guarantee that the end of month, or end of quarter report does not depend on these indexes. It can also be difficult to judge cases where there are redundant indexes, and you expect that dropping an index should result in one of the other candidates being selected. Maybe all you have is a hunch that a particular index isn’t justified?
Enter invisible indexes. When an index goes unseen, the optimizer can’t use it. The index is still actively maintained, and still checks UNIQUE and referential constraints for you. And if you go “oops, that was a bad idea”, you can revert the index back to visible in an instant.
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:
|
1 |
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:
|
1
2
3
|
ALTER TABLE Country ADD INDEX c (Continent) INVISIBLE; # after some time ALTER TABLE Country ALTER INDEX c VISIBLE; |
Visibility can now be observed in the table metadata:
|
1
2
3
4
5
6
|
mysql> SHOW INDEXES FROM my_table; +----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | +----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | my_table | 1 | just_to_be_safe_idx | 1 | a | A | NULL | NULL | NULL | YES | BTREE | | | NO | +----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ |
You can search for invisible indexes across all schemata with:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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 |
If you like, you can also declare the index as invisible when you define the table:
|
1
2
3
4
5
6
|
CREATE TABLE my_table ( ... first_name varchar(25); index first_name_idx(first_name) INVISIBLE; ); |
This index will simply stay put, be maintained but never used in execution. But it’s instantly ready should the need arise.
If you load a table using mysqldump/mysqlpump into an older server, the invisibility will simply disappear. The same will happen if you replicate from a newer server to an older one.
Caveats
Note that a primary key index can’t be made invisible. The optimizer still wants to know if a table has a primary key. This also applies if there is no primary key but a UNIQUE key. In this case the first UNIQUE key is treated as primary key and hence can’t be invisible.
Thank you for using MySQL!