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

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、CREATE INDEX或ALTER TABLE的索引定义中使用VISIBLE或INVISIBLE关键字：

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;

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

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

Information about whether an index is visible or invisible is available from the INFORMATION_SCHEMA.STATISTICS table or SHOW INDEX output. 有关索引是可见还是不可见的信息可从INFORMATION_SCHEMA.STATISTICS表或SHOW INDEX输出获得。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:如果优化器实际需要或使用一个不可见的索引，有几种方法可以注意它的缺失对表查询的影响：

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

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:使用SET_VAR优化器提示临时更新optimizer_switch的值，您可以仅在单个查询期间启用不可见索引，如下所示：

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.例如，索引会随着表行的更改而不断更新，而唯一索引会防止在列中插入重复项，无论该索引是可见的还是不可见的。

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. 没有显式主键的表如果在NOT NULL列上有任何UNIQUE索引，则可能仍然具有有效的隐式主键。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;

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:该定义不包含显式主键，但NOT NULL列j上的索引对行的约束与主键相同，并且不能使其不可见：

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);

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:此外，j上的唯一索引不再作为隐式主键，因此可以使其不可见：

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