Detailed explanation of invisible indexes in MySQL 8.0

Word

MySQL 8.0 has been released for four years since its first version. Version 8.0 has made considerable improvements and reconstructions in terms of functions and code. After communicating with friends in the DBA circle, most of them are still using versions 5.6 and 5.7, and a small number of them have adopted MySQL 8.0. In order to keep up with the pace of database development and enjoy the benefits of technology as soon as possible, we plan to introduce MySQL 8.0 into Youzan's database system.

Before implementation, we will conduct a series of studies and tests on the new features and functions, configuration parameters, upgrade methods, compatibility, etc. of MySQL 8.0. More articles will be published in the future. This article is the first one to learn about the new features of MySQL 8.0. Let’s talk about invisible indexes.

Invisible Index

The invisible index refers to the optimizer. When analyzing the execution plan (by default), the optimizer will ignore the index with the invisible attribute set.

Why is it by default? If optimizer_switch sets use_invisible_indexes=ON, you can continue to use invisible indexes.

Without further ado, let's test a few examples

How to set invisible index

We can set the visibility of the index by using create table, create index, and alter table with the keywords VISIBLE|INVISIBLE.

mysql> create table t1 (i int,
   > j int,
   > k int,
   > index i_idx (i) invisible) engine=innodb;
Query OK, 0 rows affected (0.41 sec)

mysql> create index j_idx on t1 (j) invisible;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table t1 add index k_idx (k) invisible;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select index_name,is_visible from information_schema.statistics where table_schema='test' and table_name='t1';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| i_idx | NO |
| j_idx | NO |
| k_idx | NO |
+------------+------------+
3 rows in set (0.01 sec)

mysql> alter table t1 alter index i_idx visible;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select index_name,is_visible from information_schema.statistics where table_schema='test' and table_name='t1';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| i_idx | YES |
| j_idx | NO |
| k_idx | NO |
+------------+------------+
3 rows in set (0.00 sec)

The role of invisible indexes

Faced with a large number of historical indexes, after several rounds of development and replacement of old and new developers, DBAs probably dare not delete the indexes directly, especially when encountering large tables larger than 100G. Directly deleting the index will increase the risk of database stability.

With the invisible index feature, the DBA can set the index to be invisible while observing the slow query records and thread running status of the database. If the database has no related slow queries for a long time and thread_running is relatively stable, the index can be taken offline. Otherwise, you can quickly set the index to be visible and restore business access.

Invisible Indexes is a server-level feature and has nothing to do with the engine, so all engines (InnoDB, TokuDB, MyISAM, etc.) can use it.

After setting the invisible index, the execution plan cannot use the index

mysql> show create table t2 \G
*************************** 1. row ***************************
    Table: t2
Create Table: CREATE TABLE `t2` (
 `i` int NOT NULL AUTO_INCREMENT,
 `j` int NOT NULL,
 PRIMARY KEY (`i`),
 UNIQUE KEY `j_idx` (`j`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
mysql> insert into t2(j) values(1),(2),(3),(4),(5),(6),(7);
Query OK, 7 rows affected (0.04 sec)
Records: 7 Duplicates: 0 Warnings: 0


mysql> explain select * from t2 where j=3\G
*************************** 1. row ***************************
      id: 1
 select_type: SIMPLE
    table: t2
  partitions: NULL
     type: ALL
possible_keys: NULL
     key: NULL
   key_len: NULL
     ref: NULL
     rows: 7
   filtered: 14.29
    Extra: Using where
1 row in set, 1 warning (0.01 sec)

mysql> alter table t2 alter index j_idx visible;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select * from t2 where j=3\G
*************************** 1. row ***************************
      id: 1
 select_type: SIMPLE
    table: t2
  partitions: NULL
     type: const
possible_keys: j_idx
     key: j_idx
   key_len: 4
     ref: const
     rows: 1
   filtered: 100.00
    Extra: Using index
1 row in set, 1 warning (0.01 sec)

Notes on using invisible indexes

The feature applies to indexes other than primary keys (either explicit or implicit).

Invisible indexes are for non-primary key indexes. The primary key cannot be set to invisible. The primary key here includes explicit primary keys or implicit primary keys (when there is no primary key, it is promoted to the unique index of the primary key). We can use the following example to illustrate this rule.

mysql> create table t2 (
   >i int not null,
   >j int not null ,
   >unique j_idx (j)
   >) ENGINE = InnoDB;
Query OK, 0 rows affected (0.16 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)

### In the absence of a primary key, the unique key is treated as an implicit primary key and cannot be set to invisible.
mysql> alter table t2 alter index j_idx invisible;
ERROR 3522 (HY000): A primary key index cannot be invisible
mysql>
mysql> alter table t2 add primary key (i);
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0

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 |
| PRIMARY | YES |
+------------+------------+
2 rows in set (0.01 sec)

mysql> alter table t2 alter index j_idx invisible;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

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.01 sec)

force /ignore index(index_name) Invisible indexes cannot be accessed, otherwise an error will be reported.

mysql> select * from t2 force index(j_idx) where j=3;
ERROR 1176 (42000): Key 'j_idx' doesn't exist in table 't2'

Setting an index to be invisible requires acquiring an MDL lock, which can cause database jitter when encountering long transactions

The unique index is set to be invisible, which does not mean that the uniqueness constraint of the index itself is invalid.

mysql> select * from t2;
+---+----+
| i | j |
+---+----+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 11 |
+---+----+
8 rows in set (0.00 sec)
mysql> insert into t2(j) values(11);
ERROR 1062 (23000): Duplicate entry '11' for key 't2.j_idx'

summary

Actually there is nothing much to say, I wish you all a happy use.

-The End-

The above is a detailed explanation of the invisible index in MySQL 8.0. For more information about the invisible index in MySQL 8.0, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

Which is faster among MySQL full-text index, joint index, like query, and json query?
MySQL full-text index to achieve a simple version of the search engine example code
MySQL creates full-text index sharing
A brief tutorial on MySQL full-text index application
In-depth understanding based on MySQL full-text index
Detailed analysis of several situations in which MySQL indexes fail
Detailed Analysis of the Selection of MySQL Common Index and Unique Index
Brief Analysis of MySQL B-Tree Index
Descending Index in MySQL 8.0
Index Skip Scan in MySQL 8.0
Summary of Common Problems with Mysql Indexes
MySql index improves query speed common methods code examples
The principles and defects of MySQL full-text indexing

<<: How to deploy Vue project using Docker image + nginx

>>: React Hooks Detailed Explanation