MySQL8.0新特性——不可见索引（Invisible Indexes）
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_idxmysql> ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE; --创建不可见索引：k_idxmysql> 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 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';
本文转自一个笨小孩博客51CTO博客，如需转载，请自行联系原作者。
- 点赞
- 收藏
- 关注作者
评论（0）