mysql 见索引_MySQL 不可见索引 (Invisible Indexes)

一、不可见索引的使用场景

在一张大表上创建和删除索引是有高额成本的，有时候需要测试一个索引是否有效，可以临时删除索引，对比索引存在与否，对查询的性能影响。使用索引不可见的特性，就能避免索引被真正删除，在需要的时候，把索引设置为可见即可，避免了索引真正删除和创建带来的影响。

二、不可见索引语法

默认情况下，索引都是可见的，可以使用下面的关键字来指定索引的可见性。

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;

如果想修改一个索引的可见性，使用如下语法：

ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;

ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

三、查询索引的可见性

通常使用 NFORMATION_SCHEMA.STATISTICS 表或者 show index from xxx 语法来查询表中索引的可见性，如下：

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 |

+------------+------------+

mysql> show index from sbtest1\G

*************************** 1. row ***************************

Table: sbtest1

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: id

Collation: A

Cardinality: 985302

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

Visible: YES

Expression: NULL

*************************** 2. row ***************************

Table: sbtest1

Non_unique: 1

Key_name: k_1

Seq_in_index: 1

Column_name: k

Collation: A

Cardinality: 158371

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

Visible: YES

Expression: NULL

2 rows in set (0.01 sec)

四、不可见索引注意事项

在优化器参数 optimizer_switch 有一个选项 use_invisible_indexes 来控制优化器是否可以使用不可见索引，该选项默认为 off，即默认情况下，不能使用不可见索引。如果该选项设置为 on，不可见索引仍然保持不可见，但是优化器在构建执行计划时，将该索引考虑在内。

不可见索引不影响该索引的维护，比如数据更新，不可见索引同样也会更新，不可见的唯一索引，同样可以阻止相同值的插入。

隐式的非空唯一索引作为表的主键，该唯一索引不能设置为不可见，除非添加一个显式的主键，然后该唯一索引就可以设置为不可见，示例如下：

CREATE TABLE t2 (

i INT NOT NULL,

j INT NOT NULL,

UNIQUE j_idx (j)

) ENGINE = InnoDB;

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

mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;

Query OK, 0 rows affected (0.03 sec)