MySQL 8.0 has been around for a long time, but it’s probably still 5.7.x or older, but there are a lot of new features in MySQL 8.0, such as “hidden index” or “invisible index”.

What the hell is a hidden index?

Hiding an index literally means hiding an index from view. It is not used for query optimization, so it is not used by the optimizer. Hidden indexes apply to indexes other than primary key indexes (shown or set implicitly), meaning that primary key indexes cannot be hidden in any way.

By default, indexes created in the MySQL database are VISIBLE. To explicitly control the visibility of an INDEX, you can use the VISIBLE or INVISIBLE keyword in the INDEX definition command of CREATE TABLE, CREATE INDEX, or ALTER TABLE.

As shown in the following example:

To change the visibility of an existing index, run the ALTER TABLE… The VISIBLE or INVISIBLE keyword is used in the ALTER INDEX command.

Age index changed to invisible (hidden) :

ALTER TABLE javastack ALTER INDEX age_idx INVISIBLE;
Copy the code

Age index changed to visible:

ALTER TABLE javastack ALTER INDEX age_idx VISIBLE;
Copy the code

How to know whether an INDEX in a table is visible or invisible can be obtained from the information_schema. STATISTICS table, or from the SHOW INDEX command output. Such as:

What is the use of hiding indexes?

We can test the query performance of SQL statements by hiding the index Settings of a table from the optimizer.

Which can take advantage of the hidden index test quickly delete after index affect the performance of an SQL query, without index deletion, reconstruction operation, if the index is required, it is good to set the visible again, this is very useful in the large table test, because for big table index to delete and add back very consumption performance, and even affect the normal work of the table.

Hide index Settings

If an index is set to be hidden but actually needs to be used by the optimizer, there are several table index cases where missing indexes affect queries:

1) SQL query statement contains index hint pointing to invisible index will occur error;

2) Performance mode data shows increased load of affected SQL query statements;

3) DIFFERENT execution plans appear when EXPLIAN SQL query statement is executed;

4) THE SQL query statement appears in the slow query log (did not appear before);

The value of the usE_INvisIBLE_INDEXES flag of the system variable Optimizer_switch, which controls whether the optimizer uses a hidden index when performing a plan build.

If usE_INvisIBLE_INDEXES is set to off (the default), the optimizer ignores hidden indexes by default, just as it did before this parameter was added.

If usE_INvisIBLE_INDEXES is set to on, hidden indexes remain invisible, but the optimizer adds hidden indexes to the build of the execution plan.

If you want to enable hidden indexes on a single SQL query, you can use the SET_VAR optimizer prompt to temporarily update the value of optimizer_switch, as shown below:

The visibility of an index does not affect the maintenance of the index itself; for example, the index is updated every time a table row changes, whether it is visible or invisible, and a unique index prevents the insertion of duplicate data.

A table without an explicit primary key may still be a valid implicit primary key if it has any unique index on a NOT NULL column. In this case, the first such index imposes the same constraints on the table rows as an explicit primary key, and the index cannot be set to invisible.

Definitions in the following table:

The table definition does NOT contain any explicit primary keys, but the weight column is NOT NULL, and a unique index created on that column has the same constraints as the primary key on the data row and cannot be made invisible:

Suppose we now add an explicit primary key to the table:

ALTER TABLE javastack ADD PRIMARY KEY (age);
Copy the code

An explicit primary key cannot be made invisible, and the unique index on the weight column no longer acts as an implicit primary key, so its setting can be made invisible.

conclusion

This article introduces a new feature in MySQL 8.0: hidden (invisible) indexes, which are not new index types, but can control whether or not an index is added to the build of the execution plan.

In the actual production can also use hidden index SQL statement performance test, or logical deletion of the index, as well as index gray release test, useful or quite large.

This time to share here, I hope to be useful to you. Feel good, look at, forward to share oh ~

Source: mp.weixin.qq.com/s/xZwZKXZpe…