MySQL 8.0 新特性 :隐式索引 Invisible Indexes

MySQL 8.0 支持了 Invisible Indexes 隐式索引 这个特性,可以把某个索引设置为对优化器不可见,生成查询计划时便不使用这个索引了,但这个索引还是被正常维护的,例如表数据变更后还是会更新索引。

隐式索引 最明显的一个作用类似 索引回收站

例如数据库长时间运行后,会积累很多索引,做数据库优化时,想清理掉没什么用的多余的索引,但可能删除某个索引后,数据库性能下降了,发现这个索引时有用的,就要重新建立。

对于较大的表来说,删除、重建索引的成本是很高的,如果在清理索引时能先放入回收站,确认没影响后再彻底删除,有影响的话就恢复回来,这样就方便多了,把索引设置为 隐藏/显示 就可以实现这个需求。

在 MySQL 8.0.0 中,只有 InnoDB 支持隐式索引,在 8.0.1 中 所有存储引擎都可以使用。

如何使用隐式索引

建表时指定

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 VISIBLE;

查看索引的显示状态

SELECT INDEX_NAME, IS_VISIBLE
FROM information_schema.statistics
WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 't1';

查看所有的隐式索引

SELECT * FROM information_schema.statistics WHERE is_visible='NO';

注意:不能对主键设置隐式索引

如果一个表中没有指定明确的主键,但对一个 NOT NULL 的列设为了 UNIQUE 索引,那么也不能对这个列设置隐式索引,因为此时这个列是此表的隐性主键。

小结

参考资料:

  1. https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html
  2. http://mysqlserverteam.com/mysql-8-0-invisible-indexes/
  3. https://dzone.com/articles/thoughts-on-mysql-80-invisible-indexes
加载余下内容▼

相关文章:

;