MySQL8功能详解——隐藏索引

MySQLSE

发布于 2020-09-28 11:36:52

1K0

隐藏索引是指索引对MySQL优化器隐藏（不可见），开启该功能后，索引并没有真的消失，相关的维护还存在，只是优化器并不去使用该索引。这个功能在测试评估索引有效性时非常有用。DBA对希望删除的索引开启该功能，经过完整验证，确认之后，可以放心删除索引。

所有的索引默认是可见的，可以在 CREATE TABLE, CREATE INDEX, ALTER TABLE的时候，对新索引设置为不可见。使用方法如下：

CREATE TABLE t1(
  a INT,
  b INT,
  c INT,
 INDEX a_idx(a) INVISIBLE
) ENGINE = InnoDB;
CREATE INDEX b_idx ON t1 (b) INVISIBLE;
ALTER TABLE t1 ADD INDEX c_idx(c) INVISIBLE;
此外，使用INVISIBLE和VISIBLE通过ALTER TABLE ... ALTER INDEX对已经存在的索引进行更改。使用方法如下：
ALTER TABLE t1 ALTER INDEX a_idx INVISIBLE;
ALTER TABLE t1 ALTER INDEX a_idx VISIBLE;

想知道当前索引是否被隐藏，可以通过查询INFORMATION_SCHEMA.STATISTICS 表或者SHOW INDEX的输出结果来确认

SELECT  INDEX_NAME, IS_VISIBLE
      FROM  INFORMATION_SCHEMA.STATISTICS
      WHERE  TABLE_SCHEMA = 'test'  AND  TABLE_NAME = 't1';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| a_idx     | YES        |
| b_idx     | NO         |
| c_idx     | NO         |
+------------+------------+

当将索引设置为不可见时，可以通过下面几个方法确认优化器是否需要使用到该索引：

使用到该索引的索引提示语句会发生错误。
查询的执行计划和之前的不同
查询出现在慢日志中
Performance Schema里面相关的查询工作量会增加

此外，隐藏索引不影响索引的维护。数据发生更改的同时，索引也会持续更新。另外需要注意的一点，如果没有显式主键的表具有NOT NULL列的唯一索引，该索引和显式主键具有相同的约束，不能隐藏该索引。例如：

CREATE  TABLE t2 (
 a INT  NOT  NULL,
 b INT  NOT  NULL,
  UNIQUE  b_idx (b)
) ENGINE = InnoDB;
ALTER  TABLE  t2  ALTER  INDEX  b_idx  INVISIBLE;
ERROR: 3522 (HY000):A primary key index cannot be invisible

以上内容是关于隐藏索引的介绍，更为详尽的内容请参照官网手册。https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html

感谢关注MySQL！

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-12-30，如有侵权请联系 cloudcommunity@tencent.com 删除

云推荐引擎