MySQL8.0新特性——不可见索引(Invisible Indexes)

网友投稿 1180 2022-05-30

Mysql8.0新特性——不可见索引(Invisible Indexes)

MySQL8.0开始支持看不见的索引。一个看不见的索引根本不被优化器使用,但是通常是保持正常的。默认情况下索引是可见的。不可见的索引使测试在查询性能上删除索引的效果成为可能,而不需要在需要索引的情况下进行破坏性的更改。

注意:

该特性适用于除主键以外的索引(显式或隐式)默认情况下索引是可见的!。

将index设置为invisible,会导致优化器在选择执行计划时,自动忽略该索引,即便使用了FORCE INDEX

官方文档:

https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html

1、创建测试表t1,并创建3个不可见索引

1

2

3

4

5

6

7

8

9

10

11

12

mysql> CREATE TABLE t1 (i INT,j INT,k INT, INDEX i_idx (i) INVISIBLE) ENGINE = InnoDB;

mysql> CREATE INDEX j_idx ON t1 (j) INVISIBLE;   --创建不可见索引:j_idx

mysql> ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;  --创建不可见索引:k_idx

mysql> SELECT INDEX_NAME, IS_VISIBLE   FROM INFORMATION_SCHEMA.STATISTICS   WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';   --可以看到3个索引都是不可见状态

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

| INDEX_NAME | IS_VISIBLE |

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

| i_idx      | NO         |

| j_idx      | NO         |

| k_idx      | NO         |

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

3 rows in set (0.00 sec)

2、修改索引为可见状态,也就是可使用的状态

1

2

3

4

5

6

7

8

9

10

mysql> ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;   --修改索引为可见状态

mysql> SELECT INDEX_NAME, IS_VISIBLE   FROM INFORMATION_SCHEMA.STATISTICS   WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';  --可以看到状态为yes了。

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

| INDEX_NAME | IS_VISIBLE |

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

| i_idx      | YES        |

| j_idx      | NO         |

| k_idx      | NO         |

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

3 rows in set (0.00 sec)

注意:

主键索引是不能被设为不可见索引的!!!!!

一个没有显式主键的表可能仍然有一个有效的隐式主键,如果它在非空列上有任何惟一的索引。在这种情况下,第一个这样的索引将同样的约束放在表中行上,作为一个显式的主键,而该索引不能被忽略。如下:

3、创建测试表:t2,并设置唯一索引:j_idx(这个表没有明确主键,但是在NOT NULL列j上的索引在行上放置了相同的约束,可以作为主键)

1

2

3

4

5

6

7

8

9

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

Query OK, 0 rows affected (0.12 sec)

mysql> SELECT INDEX_NAME, IS_VISIBLE   FROM INFORMATION_SCHEMA.STATISTICS   WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't2';

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

| INDEX_NAME | IS_VISIBLE |

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

| j_idx      | YES        |

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

1 row in set (0.00 sec)

3.1、将索引设置为不可见索引:会发现报错

mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;

ERROR 3522 (HY000): A primary key index cannot be invisible

(报错是因为:虽然这个表没有明确的主键,但是在NOT NULL列j上的索引在行上放置了相同的约束,作为主键,不能被忽略:)

3.2、增加一个主键,如下:

1

2

3

4

5

6

7

8

9

mysql> ALTER TABLE t2 ADD PRIMARY KEY (i);   --增加一个主键

mysql> SELECT INDEX_NAME, IS_VISIBLE   FROM INFORMATION_SCHEMA.STATISTICS   WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't2';   --可以查看到有一个主键索引

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

| INDEX_NAME | IS_VISIBLE |

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

| j_idx      | YES        |

| PRIMARY    | YES        |

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

2 rows in set (0.00 sec)

3.3、这时候,将j_idx索引设置为不可见状态是可以的,如:

1

2

3

4

5

MySQL8.0新特性——不可见索引(Invisible Indexes)

6

7

8

9

mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;

mysql> SELECT INDEX_NAME, IS_VISIBLE   FROM INFORMATION_SCHEMA.STATISTICS   WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't2';

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

| INDEX_NAME | IS_VISIBLE |

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

| j_idx      | NO         |

| PRIMARY    | YES        |

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

2 rows in set (0.00 sec)

从上面可以看出,已经存了显示的主机,而j_idx唯一索引不再充当隐式主键,因此可以将其设置为不可见状态。

4、日常操作:

--创建表指定索引为不可见状态:

create table t1(id int primary key,name varchar(10),index idx_name (name) invisible);

--修改索引为可见状态:

alter table t1 alter index idx_name visible;

--修改索引为不可见状态:

alter table t1 alter index idx_name invisible;

--查看数据库中有哪些不可见索引:

mysql> select table_schema,table_name,index_name,column_name,is_visible from information_schema.statistics where is_visible='no';

MySQL

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:MRS二次开发(13/27): ES的Rest接口样例
下一篇:项目管理系统的三大核心能力及作用介绍
相关文章