MySQL8.0 new Features--Invisible Index (Invisible Indexes)

MySQL8.0 new Features--Invisible Index (Invisible Indexes)

MySQL8.0 started to support invisible indexes. An invisible index is not used by the optimizer at all, but is usually kept normal. By default, the index is visible. An invisible index makes it possible to test the effect of dropping an index on query performance without making disruptive changes that require an index.

Attention:

This attribute applies to indexes other than the primary key (explicit or implicit) by default the index is visible!.

Setting Index to invisible causes the optimizer to automatically ignore the index when it chooses the execution plan, even if the Force index

Official documents:

Https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html

1. Create a test table T1 and create 3 invisible indexes

mysql> create table t1  (i int,j int,k int, index i_idx  (i )  invisible)  engine = innodb;mysql> create index j_idx on t1   (j)  invisible;   --Creating an invisible Index: J_idxmysql> alter table t1 add  INDEX k_idx  (k)  invisible;  --Creating an invisible Index: k_idxmysql> select index_ Name, is_visible   from information_schema. statistics   where table_schema =  ' Test '  AND TABLE_NAME =  ' T1 ';    --can see that 3 indexes are not visible +------------+------------+| index_name | is_visible  |+------------+------------+| i_idx      | no          | |  j_idx      | NO          ||  k_idx      | no         |+------------+------------ +3 rows in set  (0.00 SEC)

2, modify the index to a visible state, that is, the state can be used

mysql> alter TABLE t1 ALTER INDEX I_IDX VISIBLE; --Modify the index to a visible state mysql> SELECT index_name, is_visible from INFORMATION_SCHEMA.  STATISTICS WHERE table_schema = ' Test ' and table_name = ' T1 '; --you can see that the status is yes. +------------+------------+| index_name | Is_visible |+------------+------------+| I_idx | YES | | J_idx | NO | | K_idx | NO |+------------+------------+3 rows in Set (0.00 sec)

Attention:

Primary KEY index is a!!!!! that cannot be set as an invisible index

A table without an explicit primary key may still have a valid implicit primary key if it has any unique index on a non-empty column. In this case, the first such index places the same constraint on the table row, as an explicit primary key, and the index cannot be ignored. As follows:

3. Create a Test table: T2, and set a unique index: J_IDX (This table has no explicit primary key, but the index on NOT NULL column J places the same constraint on the row, which can be used as the primary key)

mysql> CREATE TABLE T2 (i INT not null,j INT not null,unique J_idx (j)) ENGINE = InnoDB; Query OK, 0 rows affected (0.12 sec) mysql> SELECT index_name, is_visible from INFORMATION_SCHEMA. STATISTICS WHERE table_schema = ' Test ' and table_name = ' T2 '; +------------+------------+| index_name | Is_visible |+------------+------------+| J_idx | YES |+------------+------------+1 row in Set (0.00 sec)

3.1. Set index to Invisible Index: error will be found

Mysql> alter TABLE T2 ALTER INDEX J_IDX INVISIBLE;

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

(the error is because: Although the table has no explicit primary key, the index on NOT NULL column J places the same constraint on the row, as the primary key, cannot be ignored:)

3.2. Add a primary key, as follows:

mysql> ALTER TABLE T2 ADD PRIMARY KEY (i); --Add a primary key mysql> SELECT index_name, is_visible from INFORMATION_SCHEMA.   STATISTICS WHERE table_schema = ' Test ' and table_name = ' T2 '; --you can see that there is a primary key index +------------+------------+| index_name | Is_visible |+------------+------------+| J_idx | YES | | PRIMARY | YES |+------------+------------+2 rows in Set (0.00 sec)

3.3. At this time, it is possible to set the J_IDX index to an invisible state, such as:

Mysql> alter TABLE T2 ALTER INDEX j_idx invisible;mysql> SELECT index_name, is_visible from INFORMATION_SCHEMA. STATISTICS WHERE table_schema = ' Test ' and table_name = ' T2 '; +------------+------------+| index_name | Is_visible |+------------+------------+| J_idx | NO | | PRIMARY | YES |+------------+------------+2 rows in Set (0.00 sec)

As can be seen from the above, the displayed host has been saved, and the J_idx unique index no longer acts as an implicit primary key, so it can be set to an invisible state.

4. Daily Operation:

--Create TABLE specifies that the index is not visible:

CREATE TABLE t1 (ID int primary key,name varchar), index idx_name (name) invisible);

--Modify the index to a visible state:

ALTER TABLE T1 ALTER index idx_name visible;

--Modify the index to an invisible state:

ALTER TABLE T1 ALTER index idx_name invisible;

--See which indexes are not visible in the database:

Mysql> Select table_schema,table_name,index_name,column_name,is_visible from Information_schema.statistics where Is_visible= ' no ';

MySQL8.0 new Features--Invisible Index (Invisible Indexes)