MySQL 8.0 支持隐藏索引（invisible index），也称为不可见索引。隐藏索引不会被优化器使用。 它允许快速启用/禁用MySQL Optimizer使用的索引；主键不能设置为隐藏（包括显式设置或隐式设置）。
索引默认是可见的（visible）。使用CREATE TABLE、CREATE INDEX 或ALTER TABLE语句的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.03 sec)
mysql >CREATE INDEX j_idx ON t1 (j) INVISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql >ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
使用ALTER TABLE … ALTER INDEX语句的VISIBLE或者INVISIBLE选项修改已有索引的可见性：
ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;
通过数据字典表 INFORMATION_SCHEMA.STATISTICS 或者SHOW INDEX命令可以查看索引的可见性。例如：
mysql >SELECT INDEX_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'wjqtest' AND TABLE_NAME = 't1'; +------------+------------+ | INDEX_NAME | IS_VISIBLE | +------------+------------+ | i_idx | YES | | j_idx | NO | | k_idx | NO | +------------+------------+ 3 rows in set (0.00 sec)
不可见索引特性可以用于测试删除某个索引对于查询性能的影响，同时又不需要真正删除索引，也就避免了错误删除之后的索引重建。对于一个大表上的索引进行删除重建将会非常耗时，而将其设置为不可见或可见将会非常简单快捷。
如果某个设置为隐藏的索引实际上仍然需要或者被优化器所使用，可以通过以下多种方法发现缺少该索引带来的影响：
索引提示中使用了该索引的查询将会产生错误。
性能模式（Performance Schema）中的数据显示受影响查询的负载升高。
EXPLAIN 语句显示了不同的查询执行计划。
慢查询日志中出现了新的查询语句。
系统变量 optimizer_switch 的 use_invisible_indexes 值控制了优化器构建执行计划时是否使用隐藏索引。如果设置为 off （默认值），优化器将会忽略隐藏索引（与引入该属性之前的行为相同）。如果设置为 on，隐藏索引仍然不可见，但是优化器在构建执行计划时将会考虑这些索引。
使用SET_VAR优化器提示来optimizer_switch临时更新临时值 ，可以仅在单个查询期间启用不可见索引，如下所示：
mysql >show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`i` int DEFAULT NULL,
`j` int DEFAULT NULL,
`k` int DEFAULT NULL,
KEY `i_idx` (`i`),
KEY `j_idx` (`j`) /*!80000 INVISIBLE */,
KEY `k_idx` (`k`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql >EXPLAIN SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */ i, j FROM t1 WHERE j >= 2\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: 4
filtered: 33.33
Extra: Using where
1 row in set, 2 warnings (0.00 sec)
mysql >EXPLAIN SELECT i, j FROM t1 WHERE k >= 2\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: 4
filtered: 33.33
Extra: Using where
1 row in set, 1 warning (0.00 sec)
但是我在MySQL 8.0.23版本的环境测试，好像和官方文档描述的存在差异，使用使用SET_VAR优化器提示来optimizer_switch临时更新临时值的时候出现如下警告，从警告信息看，optimizer_switch不能设置use_invisible_indexes=on，但是尚未定位到具体的原因，如果有人遇到过，可以留言一起交流；
mysql dba_admin@127.0.0.1:wjqtest21:02:43>show warnings; +---------+------+-------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1231 | Variable 'optimizer_switch' can't be set to the value of 'use_invisible_indexes=on' | | Note | 1003 | /* select#1 */ select `wjqtest`.`t1`.`i` AS `i`,`wjqtest`.`t1`.`j` AS `j` from `wjqtest`.`t1` where (`wjqtest`.`t1`.`j` >= 2) | +---------+------+-------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
索引的可见性不会影响索引的维护。例如，无论索引是否可见，每次修改表中的数据时都需要对相应索引进行更新，而且唯一索引都会阻止插入重复的列值。
一个没有明确定义主键的表仍然可能存在有效的隐式主键（ 表中某些 NOT NULL 字段上创建了 UNIQUE 索引）。在这种情况下，表中第一个这样的索引和显式主键存在相同的约束效果，该索引也不能被设置为不可见。假如存在以下表定义：
CREATE TABLE t2 ( i INT NOT NULL, j INT NOT NULL, UNIQUE j_idx (j)) ENGINE = InnoDB;
定义中没有明确指定主键，但是 NOT NULL 字段 j 上存在一个唯一索引，它实现了和主键相同的数据约束，不能设置为不可见：
mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE; ERROR 3522 (HY000): A primary key index cannot be invisible.
如果为该表指定一个显式的主键：
ALTER TABLE t2 ADD PRIMARY KEY (i);
该主键不能设置为不可见。除此之外，字段 j 上的唯一索引不再是一个隐式的主键，因此可以设置为隐藏索引：
mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE; Query OK, 0 rows affected (0.03 sec)
如果我们现在要删除索引，我们可以将其更改为隐藏。 但是使用“FORCE / USE INDEX”的查询怎么样？ 他们是否会抛出一个错误？ 如果强制不存在的索引，你会收到错误。 你不会看到隐藏索引的错误。 优化器不会使用它，但知道它存在。
mysql> show create table t1 G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `i` int(11) DEFAULT NULL, `j` int(11) DEFAULT NULL, `k` int(11) DEFAULT NULL, KEY `i_idx` (`i`), KEY `idx_1` (`i`,`j`,`k`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> explain select * from t1 force index(idx_1) where i=1 and j=4; +----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ref | idx_1 | idx_1 | 10 | const,const | 2 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> alter table t1 alter index idx_1 invisible; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from t1 force index(idx_1) where i=1 and j=4; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 16 | 6.25 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) mysql> explain select * from t1 where i=1 and j=4; +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ref | i_idx | i_idx | 5 | const | 2 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
正如你所看到的，如果我们使用带有隐藏索引的“FORCE INDEX”，MySQL会执行全表扫描。 MySQL不会抛出任何错误，因为索引存在，但它不可见。 即使有另一个可用的索引，它也将执行全表扫描。 在大型表上，这可能会导致严重的性能问题。 即使MySQL在查询执行期间不抛出任何错误，它也应该会在错误日志中记录一个警告。
总结一些对这个新功能的首次使用的想法和总结：
如果你想删除一个索引，但又想事先知道效果。你就可以使它对优化程序不可见。这是一个快速的元数据更改，使索引不可见。一旦确定没有性能下降，就可以真正去删除索引。
关键的一点是，隐藏索引不能供优化器使用，但它仍然存在，并通过写入操作保持最新。即便我们尝试“FORCE INDEX”，优化器也不会使用它，虽然我认为我们应该能够在某种程度上强制它。可能会有这样的情况：
我们可以创建一个新的隐形索引，但如果想要测试它，必须使它可见。这意味着所有对应用程序有即时影响的查询都将能够使用它。如果目的只是想测试它，我不认为这是最好的方法，不是所有人的服务器上都有相同的数据大小和真实数据。强制隐藏索引这时候可能会很有用。
你有许多索引，但不确定哪一个未使用。你可以将一个索引更改为不可见，以查看是否存在任何性能下降。如果是，你可以立即更改。
你可能有一个特殊情况，只有一个查询可以使用该索引。在这种情况下，隐藏索引可能是一个很好的解决方案。
隐藏索引应用场景：软删除、灰度发布。
参考链接
https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html
转载请注明原文链接：MySQL 8.0新特性：隐藏索引
