MySQL 8.0 has been released for a long time, but everyone may stay at 5.7.0 x. Even older. In fact, MySQL 8.0 has added many heavy new features, such as "hidden index" or "invisible index" introduced by the stack leader today.
What the hell is a hidden index?
Hide index literally means to hide the index, that is, invisible. It is not used for query optimization, so it will not be used by the optimizer. Hidden indexes are applicable to indexes other than primary key indexes (displayed or implicitly set), which means that primary key indexes cannot be hidden in any way.
The CREATE INDEX or alter index can be used to control the visibility of the index explicitly defined in MySQL.
As shown in the following example:
CREATE TABLE javastack ( age INT, weight INT, tall INT, INDEX age_idx (age) INVISIBLE ) ENGINE = InnoDB; CREATE INDEX weight_idx ON javastack (weight) INVISIBLE; ALTER TABLE javastack ADD INDEX tall_idx (tall) INVISIBLE;
To change the visibility of an existing index, use the VISIBLE or INVISIBLE keyword in the ALTER TABLE... ALTER INDEX command.
Change age index to invisible (hidden):
ALTER TABLE javastack ALTER INDEX age_idx INVISIBLE;
Age index changed to visible:
ALTER TABLE javastack ALTER INDEX age_idx VISIBLE;
How to know whether the index in a table is visible or invisible can be obtained from INFORMATION_SCHEMA.STATISTICS table or SHOW INDEX command output. For example:
mysql> SELECT
INDEX_NAME,
IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 'javastack';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| age_idx | YES |
| weight_idx | NO |
| tall_idx | NO |
+------------+------------+
What's the use of hiding indexes?
From the introduction of hidden indexes above, we know that hidden indexes can not be used by the optimizer, so we can set an index of a table to be hidden, and then test the query performance of SQL statements.
That is, you can use the hidden index to quickly test the impact of deleting the index on the SQL query performance without index deletion and reconstruction. If you need the index, you can set it visible. This is undoubtedly very useful in the large table test, because the deletion and RE addition of the large table index consume a lot of performance, and even affect the normal operation 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 missing conditions that affect the query:
1) The SQL query statement contains an index prompt, and an error will occur when pointing to the invisible index;
2) The performance mode data shows the increased load of the affected SQL query statements;
3) Different execution plans appear when expanding SQL query statements;
4) The SQL query statement appears in the slow query log (not before);
System variable optimizer_ Use of switch_ invisible_ The value of the indexes flag controls whether the optimizer uses hidden indexes when executing the plan build.
If use_ invisible_ When the indexes value is set to off (the default value), the optimizer will ignore the hidden index by default, which is the same as before adding this parameter.
If use_ invisible_ When the indexes value is set to on, the hidden index remains invisible, but the optimizer will add the hidden index to the construction of the execution plan.
If you want to enable hidden indexes on a single SQL query statement, you can use set_ The VaR optimizer prompts you to update the optimizer temporarily_ The value of switch is as follows:
mysql> EXPLAIN SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */
> age, weight FROM javastack WHERE weight >= 150\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: javastack
partitions: NULL
type: range
possible_keys: weight_idx
key: weight_idx
key_len: 5
ref: NULL
rows: 2
filtered: 100.00
Extra: Using index condition
mysql> EXPLAIN SELECT age, weight FROM javastack WHERE weight >= 150\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: javastack
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 33.33
Extra: Using where
The visibility of the index does not affect its own maintenance. For example, no matter whether the index is visible or invisible, the index will be updated every time the table data row is changed, and a unique index can also prevent 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 the NOT NULL column. In this case, the first such index will impose the same constraints on the table data row as the explicit primary key, and the index cannot be set invisible.
As defined in the following table:
CREATE TABLE javastack ( age INT NOT NULL, weight INT NOT NULL, UNIQUE weight_idx (weight) ) ENGINE = InnoDB;
The table definition does not contain any explicit primary key, but the weight column is NOT NULL. The unique index created on this column has the same constraints as the primary key on the data row and cannot be made invisible:
mysql> ALTER TABLE javastack ALTER INDEX weight_idx INVISIBLE; ERROR 3522 (HY000): A primary key index cannot be invisible.
Suppose we now add an explicit primary key to the table:
ALTER TABLE javastack ADD PRIMARY KEY (age);
The explicit primary key cannot be set to invisible. At this time, the unique index on the weight column no longer acts as the implicit primary key, so its setting can be made invisible.
mysql> ALTER TABLE javastack ALTER INDEX weight_idx INVISIBLE; Query OK, 0 rows affected (0.03 sec)
summary
This paper introduces a new feature in MySQL 8.0: hidden (invisible) index. This index is not a new index type, but can control whether the index is added to the construction of execution plan.
In actual production, we can also use the hidden index to test the performance of SQL statements, or to logically delete the index, as well as the gray publishing test of the index, which is very useful.
Author: Java technology stack
last
Gold, silver and four will arrive soon. I hope you can learn these technical points well. Please welcome friends who need to receive these learning materials and interview notes** Click here for free!**
Learning video:
Real interview questions of large factories:
Please read these learning materials and interview notes** Click here for free!**
Learning video:
[external chain picture transferring... (img-d5f1USXz-1623623166474)]
Real interview questions of large factories:
