从MySQL8.0开始支持隐藏索引特性，也就是所谓的invisible index。对于不可见的索引，优化器将直接忽略掉。我们可以通过该特性来影响优化器的行为。另外这也可以视为在drop一个索引之前的缓冲，临时把索引设置为不可见后，再观察应用是否正常或有报错什么的，如果一切OK，再最终删除。
对应的8.0.0的release note:
MySQL now supports invisible indexes. An invisible index is not used by the optimizer at all, but is otherwise maintained normally. Indexes are visible by default. Invisible indexes make it possible to test the effect of removing an index on query performance, without making a destructive change that must be undone should the index turn out to be required. This feature applies to InnoDB tables, for indexes other than primary keys.
To control whether an index is invisible explicitly for a new index, use a VISIBLE or INVISIBLE keyword as part of the index definition for CREATE TABLE, CREATE INDEX, or ALTER TABLE. To alter the invisibility of an existing index, use a VISIBLE or INVISIBLE keyword with the ALTER TABLE ... ALTER INDEX operation. For more information, see Invisible Indexes.在8.0.0中仅支持innodb，在8.0.1版本中修改成支持所有引擎（代码尚未release），对应release note:
Previously, invisible indexes were supported only for the InnoDB storage engine. Invisible indexes are now storage engine neutral (supported for any engine). (Bug #23541244)对应WorkLog: WL#8697: Support for INVISIBLE indexes
测试
# 创建一个普通的表t1，只带主键
mysql> create table t1 (a int primary key auto_increment, b int, c int, d int);
Query OK, 0 rows affected (0.67 sec)
# 增加一个索引
mysql> alter table t1 add key(b);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show indexes from t1\G
*************************** 1. row ***************************
Table: t1
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: a
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
*************************** 2. row ***************************
Table: t1
Non_unique: 1
Key_name: b
Seq_in_index: 1
Column_name: b
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
2 rows in set (0.01 sec)
从show indexes的visible列显示了，这两个索引都是可见的。
# Load some data
insert into t1 select NULL, rand()*100000, rand()*10000,rand()*10000;
insert into t1 select NULL, rand()*100000, rand()*10000,rand()*10000 from t1;
insert into t1 select NULL, rand()*100000, rand()*10000,rand()*10000 from t1;
....
analyze table t1;
mysql> explain select * from t1 where b > 5000 limit 10;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | range | b | b | 5 | NULL | 1932 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec
可以看到索引b被使用到
# 修改索引b为不可见
mysql> alter table t1 alter index b invisible;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show indexes from t1\G
*************************** 1. row ***************************
Table: t1
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: a
Collation: A
Cardinality: 2048
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
*************************** 2. row ***************************
Table: t1
Non_unique: 1
Key_name: b
Seq_in_index: 1
Column_name: b
Collation: A
Cardinality: 2029
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: NO
2 rows in set (0.01 sec)
mysql> explain select * from t1 where b > 5000 limit 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2048
filtered: 33.33
Extra: Using where
1 row in set, 1 warning (0.00 sec)
当索引被修改为invisible后，优化器将不再选择这个索引
# 将索引重新修改为可见
mysql> alter table t1 alter index b visible;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from t1 where b > 5000 limit 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: b
key: b
key_len: 5
ref: NULL
rows: 1932
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
# 你也可以在创建索引的时候显式指定是否可见
mysql> alter table t1 add key(c) invisible;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show indexes from t1 where key_name = 'c'\G
*************************** 1. row ***************************
Table: t1
Non_unique: 1
Key_name: c
Seq_in_index: 1
Column_name: c
Collation: A
Cardinality: 1848
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: NO
1 row in set (0.01 sec)
# 或者在建表时指定关键字
mysql> create table t2 (a int primary key, b int, key(b) invisible);
Query OK, 0 rows affected (0.67 sec)
# 但primary key不可以设置为不可见
mysql> drop table t2;
Query OK, 0 rows affected (0.03 sec)
mysql> create table t2 (a int, b int, primary key(a) invisible);
ERROR 3522 (HY000): A primary key index cannot be invisible
需要注意，把索引设置为不可见后，在引擎层的底层存储，索引依然是需要继续被维护的(从commit的diff可以看到，innodb部分的代码是完全没有改动的)，只是在server层对优化器不可见而已。
index是否隐藏的属性存放在系统表mysql.indexes的列is_visible中。
