MySQL 不可见索引 (Invisible Indexes)

2023年 8月 15日 51.0k 0

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)

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论