mytecdb.com

MySQL 不可见索引 (Invisible Indexes)

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)

文章评论

0条评论