MySQL 支持不可见索引；也就是说，这些索引不会被优化器使用。该功能适用于除主键（无论是显式的还是隐式的）之外的索引。MySQL supports invisible indexes; that is, indexes that are not used by the optimizer. The feature applies to indexes other than primary keys (either explicit or implicit).

索引默认是可见的。要显式控制新索引的可见性，可以在 CREATE TABLE、CREATE INDEX 或 ALTER TABLE 的索引定义中使用 VISIBLE 或 INVISIBLE 关键字：Indexes are visible by default. To control visibility 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:

CREATE TABLE t1 (
  i INT,
  j INT,
  k INT,
  INDEX i_idx (i) INVISIBLE
) ENGINE = InnoDB;
CREATE INDEX j_idx ON t1 (j) INVISIBLE;
ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;

要更改现有索引的可见性，请在 ALTER TABLE ... ALTER INDEX 操作中使用 VISIBLE 或 INVISIBLE 关键字：To alter the visibility of an existing index, use a VISIBLE or INVISIBLE keyword with the ALTER TABLE ... ALTER INDEX operation:

ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

关于索引是可见还是不可见的信息可以从信息模式的 STATISTICS 表或 SHOW INDEX 输出中获得。例如：Information about whether an index is visible or invisible is available from the Information Schema STATISTICS table or SHOW INDEX output. For example:

mysql> SELECT INDEX_NAME, IS_VISIBLE
       FROM INFORMATION_SCHEMA.STATISTICS
       WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 't1';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| i_idx      | YES        |
| j_idx      | NO         |
| k_idx      | NO         |
+------------+------------+

不可见索引可以在不进行破坏性更改的情况下测试移除索引对查询性能的影响，如果索引实际上是必需的，这些更改必须撤销。对于大表来说，删除并重新添加索引可能成本高昂，而将索引设置为不可见或可见是快速的、就地的操作。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. Dropping and re-adding an index can be expensive for a large table, whereas making it invisible and visible are fast, in-place operations.

如果一个被设为不可见的索引实际上是必需的或者被优化器使用，有几种方法可以注意到它在表查询中缺失的影响：If an index made invisible actually is needed or used by the optimizer, there are several ways to notice the effect of its absence on queries for the table:

use_invisible_indexes 标志是 optimizer_switch 系统变量的一部分，用于控制优化器是否在查询执行计划构建中使用不可见索引。如果该标志为 off（默认值），优化器会忽略不可见索引（与引入此标志之前的行为相同）。如果该标志为 on，不可见索引仍然不可见，但优化器在构建执行计划时会考虑它们。The use_invisible_indexes flag of the optimizer_switch system variable controls whether the optimizer uses invisible indexes for query execution plan construction. If the flag is off (the default), the optimizer ignores invisible indexes (the same behavior as prior to the introduction of this flag). If the flag is on, invisible indexes remain invisible but the optimizer takes them into account for execution plan construction.

使用SET_VAR优化器提示来临时更新optimizer_switch的值，你可以仅在单个查询期间启用隐形索引，如下所示：Using the SET_VAR optimizer hint to update the value of optimizer_switch temporarily, you can enable invisible indexes for the duration of a single query only, like this:

mysql> EXPLAIN SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */
     >     i, j FROM t1 WHERE j >= 50\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: j_idx
          key: j_idx
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition

mysql> EXPLAIN SELECT i, j FROM t1 WHERE j >= 50\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: 5
     filtered: 33.33
        Extra: Using where

索引的可见性不会影响索引维护。例如，无论索引是可见还是不可见，索引都会根据表行的变更持续更新，并且唯一索引会防止向某列插入重复值。Index visibility does not affect index maintenance. For example, an index continues to be updated per changes to table rows, and a unique index prevents insertion of duplicates into a column, regardless of whether the index is visible or invisible.

一个没有显式主键的表，如果在NOT NULL列上有任何UNIQUE索引，仍然可能有一个有效的隐式主键。在这种情况下，第一个这样的索引对表行施加了与显式主键相同的约束，并且该索引不能被隐藏。考虑以下表定义：A table with no explicit primary key may still have an effective implicit primary key if it has any UNIQUE indexes on NOT NULL columns. In this case, the first such index places the same constraint on table rows as an explicit primary key and that index cannot be made invisible. Consider the following table definition:

CREATE TABLE t2 (
  i INT NOT NULL,
  j INT NOT NULL,
  UNIQUE j_idx (j)
) ENGINE = InnoDB;

该定义没有显式主键，但在NOT NULL列j上的索引对行施加了与主键相同的约束，并且不能被隐藏：The definition includes no explicit primary key, but the index on NOT NULL column j places the same constraint on rows as a primary key and cannot be made invisible:

mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;
ERROR 3522 (HY000): A primary key index cannot be invisible.

现在假设在表中添加了一个显式主键：Now suppose that an explicit primary key is added to the table:

ALTER TABLE t2 ADD PRIMARY KEY (i);

显式主键不能被设置为不可见。此外，j上的唯一索引不再作为隐式主键，因此可以被设置为不可见：The explicit primary key cannot be made invisible. In addition, the unique index on j no longer acts as an implicit primary key and as a result can be made invisible:

mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;
Query OK, 0 rows affected (0.03 sec)