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 索引,那么也不能对这个列设置隐藏索引,因为此时这个列是此表的隐性主键。

小结

参考资料:

https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html
http://mysqlserverteam.com/mysql-8-0-invisible-indexes/
https://dzone.com/articles/thoughts-on-mysql-80-invisible-indexes

原文发布于微信公众号 - 性能与架构(yogoup)

原文发表时间:2016-11-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏landv

金蝶K/3 固定置产相关SQL语句

11010
来自专栏我的博客

MYSQL死锁问题

如果insert或update用到了同样的索引也会造成死锁。 比如 update table_a set name = ‘test1’ where id =...

37560
来自专栏nummy

MySQL取得日期(前一天、某一天)

mysql> select date_sub(curdate(),interval 1 day); +----------------------------...

9520
来自专栏散尽浮华

利用mk-table-checksum监测Mysql主从数据一致性操作记录

前面已经提到了mysql主从环境下数据一致性检查:mysql主从同步(3)-percona-toolkit工具(数据一致性监测、延迟监控)使用梳理 今天这里再介...

41180
来自专栏Python

SQL逻辑查询语句执行顺序

一 SELECT语句关键字的定义顺序 SELECT DISTINCT <select_list> FROM <left_table> <join_type> J...

23750
来自专栏乐沙弥的世界

收集统计信息导致索引被监控

      对于索引的调整,我们可以通过Oracle提供的索引监控特性来跟踪索引是否被使用。尽管该特性并未提供索引使用的频度,但仍不失为我们参考的方式之一。然而...

7920
来自专栏张善友的专栏

使用信息架构视图访问数据库元数据

元数据简介 元数据 (metadata) 最常见的定义为"有关数据的结构数据",或者再简单一点就是"关于数据的信息",日常生活中的图例、图书馆目录卡和名片等都...

24860
来自专栏史上最简单的Spring Cloud教程

Openresty最佳案例 | 第8篇:RBAC介绍、sql和redis模块工具类

RBAC介绍 RBAC(Role-Based Access Control,基于角色的访问控制),用户基于角色的访问权限控制。简单地说,一个用户拥有若干角色,每...

34380
来自专栏乐沙弥的世界

PL/SQL --> DML 触发器

何谓触发器?简言之,是一段命名的PL/SQL代码块,只不过该代码块在特定的条件下被触发并且执行。对于这样的代码我们称之为触发器

11030
来自专栏跟着阿笨一起玩NET

sql server根据表中数据生成insert语句

修正了表中的字段如果是SQL中的关键字(如Order)时,生成的脚本执行会出错的bug

14910

扫码关注云+社区

领取腾讯云代金券