MySQL 不可见索引 (Invisible Indexes)

MySQL, 数据库 / 作者：

MySQL 8.0 版本支持不可见索引（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)

发表评论