• MySQL8功能详解——隐藏索引

    小编:啊南 33阅读 2020.11.20

    隐藏索引是指索引对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
    关联标签: