Detailed explanation of invisible indexes in MySQL 8.0

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)

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)

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)

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

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'