Shulou(Shulou.com)06/01 Report--

This article mainly introduces the invisible index analysis of MySQL8.0 features, hoping to supplement and update some knowledge, if you have other questions to understand, you can continue to follow my updated article in the industry information.

A new feature of MySQL8.0-invisible index (Invisible Indexes)

MySQL8.0 began to support invisible indexes. An invisible index is not used by the optimizer at all, but usually remains normal. The index is visible by default. Invisible indexes make it possible to test the effect of deleting indexes on query performance without the need for destructive changes where indexes are needed.

Note:

This feature applies to indexes other than primary keys (explicit or implicit) the index is visible by default.

Setting index to invisible causes the optimizer to automatically ignore the index when choosing an execution plan, even if FORCE INDEX is used

1. Create 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;-create invisible index: j_idxmysql > ALTER TABLE T1 ADD INDEX k_idx (k) INVISIBLE;-create invisible index: k_idxmysql > SELECT INDEX_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME =' T1' -- you can see that all three indexes are invisible. +-+-+ | 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 be visible, that is, the state that can be used

Mysql > ALTER TABLE T1 ALTER INDEX i_idx VISIBLE;-- change the index to visible state mysql > SELECT INDEX_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME =' T1 status;-- you can see that the state is yes. +-+-+ | INDEX_NAME | IS_VISIBLE | +-+-+ | i_idx | YES | | j_idx | NO | | k_idx | NO | +-+-+ 3 rows in set (0.00 sec)

Note:

Primary key indexes cannot be set to invisible indexes!

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

3. Create the test table: T2, and set the unique index: j_idx (this table does not have an explicit primary key, but the index on NOT NULL column j places the same constraint on the row and 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 the index to invisible: you will find an error

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 does not have an explicit primary key, the index on NOT NULL column j places the same constraint on the row as the primary key and 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)

At this point, 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 you can see 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 made invisible.

4. Daily operation:

-- create a table to specify that the index is invisible:

Create table T1 (id int primary key,name varchar (10), index idx_name (name) invisible)

-- modify the index to be visible:

Alter table t1 alter index idx_name visible

-- modify the index to be invisible:

Alter table t1 alter index idx_name invisible

-- check what invisible indexes are in the database:

Mysql > select table_schema,table_name,index_name,column_name,is_visible from information_schema.statistics where is_visible='no'

Read the above invisible index analysis of MySQL8.0 features, hoping to bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.