8.3.12 Invisible Indexes

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

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:

MySQL支持不可见索引，这样的索引不被优化器使用。这个功能可以用于主键以外的索引（无论显性还是隐性）。

索引默认是可见的。为了精准控制一个索引的可见特性可以用VISIBLE或INVISIBLE关键字作为创建索引语句的一部分，比如CREATE TABLE,CREATE INDEX,或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;

To alter the visibility of an existing index, use a VISIBLE or INVISIBLE keyword with the ALTER TABLE ... ALTER INDEX operation:

为了改变已存在索引的可见性，用VISIBLE或INVISIBLE关键字在ALTER TABLE...ALTER INDEX的操作中：

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. For example:

查看索引是否可见或不可见可以利用INFORMATION_SCHEMA.STATISTICS表或利用SHOW INDEX进行输出。比如：

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:

• Errors occur for queries that include index hints that refer to the invisible index.

• Performance Schema data shows an increase in workload for affected queries.

• Queries have different EXPLAIN execution plans.

• Queries appear in the slow query log that did not appear there previously.

如果优化器需要或使用不可见索引，可以通过下面几个方法注意到不可见索引的缺失对表查询的影响

• 包含引用不可见索引的索引提示的查询会报错
• 对性能架购数据的查询会增加查询负载
• 查询有不同的执行计划
• 查询会出现慢SQL而以前没有出现过

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.

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:

控制是否使用不可见索引的标志是use_invisible_indexes。如果这个标志是off（默认值），优化器会忽略不可见索引。如果这个标识是on，不可见索引仍然不可见，但是优化器会考虑使用不可见索引构建执行计划

可以使用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. 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:

不可见索引不影响索引的维护。比如每行数据的更新都要更新索引，唯一索引能阻止重复的插入无论这列的索引是否可见或不可见

一个表如果没有显示的主键但是仍然可能有隐式主键，如果这个表有一个在NOT NULL列上的唯一索引。在这种情况下，第一个这样的索引起到了和显示主键同样的约束作用，这样的索引不可以被设置成不可见索引。考虑下面的表定义：

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)