mysql 隐式索引

定义

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;

若要更改现有索引的可见性，请在alter表中使用VISIBLE或INVISIBLE关键字…ALTER INDEX操作:

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

查看索引可见性

关于索引是可见还是不可见的信息可以从INFORMATION_SCHEMA获得。统计表或显示索引输出。例如:

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         |
+------------+------------+

用处

不可见的索引使测试删除索引对查询性能的影响成为可能，而不必进行破坏性的更改(如果索引是必需的，则必须撤消这些更改)。对于大型表，删除和重新添加索引的代价可能很高，而使其不可见和可见则是快速的就地操作。
如果一个不可见的索引实际上是需要的或由优化器使用，有几种方法可以注意到它的缺失对表查询的影响:

• 对于包含引用不可见索引的索引提示的查询，会出现错误。
• 性能模式数据显示受影响查询的工作负载有所增加。
• 查询有不同的解释执行计划。
• 查询出现在以前没有出现的慢速查询日志中。

注意点

optimizer_switch 系统变量的 use_invisible_indexes 标志控制优化器是否使用不可见的索引来构建查询执行计划。如果该标志为off (默认值)，优化器将忽略不可见的索引(与引入该标志之前的行为相同)。如果启用了该标志，不可见的索引将保持不可见，但是优化器会将它们考虑到执行计划构造中。

索引可见性不影响索引维护。例如，每次对表行进行更改时，都会继续更新索引，而惟一索引可以防止将重复项插入到列中，无论索引是可见的还是不可见的。

没有显式主键的表如果在非空列上有惟一索引，则仍然可能有有效的隐式主键。在本例中，第一个这样的索引将对表行施加与显式主键相同的约束，并且不能使该索引不可见。考虑下表定义:

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

这个定义不包含显式的主键，但是非空列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)