前言

MySQL 8.0 引入了 Invisible Indexes 这一个特性，对于 DBA 同学来说是一大福音，索引生命周期管理除了有和无外，又多了一种形态–可见和不可见，进而对业务SQL的调优又多了一种手段。

关于 Invisible Indexes，不管是官方还是第三方，都有非常多的介绍文档，这里推荐大家可以先看下：

  1. 官方文档: Invisible Indexes
  2. 官方 server 层团队博客: MySQL 8.0: Invisible Indexes
  3. 官方 worklog: WL#8697: Support for INVISIBLE indexes
  4. Percona blog: Thoughts on MySQL 8.0 Invisible Indexes
  5. 我们的 weixiang 同学的文章：MySQL · 8.0新特性· Invisible Index

简单来说，Invisible Indexes 的特点是：对优化器来说是不可见的，但是引擎内部还是会维护这个索引，并且不可见属性的修改操只改了元数据，所以可以非常快。 当我们发现某个索引不需要，想要去掉的话，可以先把索引设置为不可见，观察下业务的反应，如果一切正常，就可以 drop 掉；如果业务有受影响，那么说明这个索引删掉会有问题，就可以快速改回来。所以相对于 DROP/ADD 索引这种比较重的操作，Invisible Indexes 就会显得非常灵活方便。

Invisible Indexes 是 server 层的特性，和引擎无关，因此所有引擎（InnoDB, TokuDB, MyISAM, etc.）都可以使用。

MySQL 官方只在 8.0 版本中支持了这一特性，考虑到 8.0 的普及还比较遥远，为了让大家能早日用上这么好的功能，我们将 Invisible Indexes 这一特性 backport 到 AliSQL 分支，目前开源分支已经支持，大家可以下载使用。

用法介绍

虽然官方文档里有详细的使用介绍，本文为了完整性，也简单介绍下使用方法。

  1. CREATE TABLE： 我们可以在建表时指定索引的不可见属性，默认是可见的。

    1.  CREATE TABLE `t1` (
    2.    `id` int(11) DEFAULT NULL,
    3.    `tid` int(11) DEFAULT NULL,
    4.    KEY `idx_tid` (`tid`) INVISIBLE
    5.  ) ENGINE=InnoDB;

  2. ADD INDEX: 我们可以在后续加索引时，指定加的索引是否可见

    1.  CREATE TABLE `t1` (
    2.    `id` int(11) DEFAULT NULL,
    3.    `tid` int(11) DEFAULT NULL
    4.    ) ENGINE=InnoDB;
    5.  CREATE INDEX idx_tid ON t1(tid) INVISIBLE;
    6.  ALTER TABLE t1 ADD INDEX idx_tid(tid) INVISIBLE;

  3. ALTER INDEX: 我们可以在后续使用时，更改已有索引的可见性

    1.  CREATE TABLE `t1` (
    2.    `id` int(11) DEFAULT NULL,
    3.    `tid` int(11) DEFAULT NULL,
    4.    KEY `idx_tid` (`tid`) INVISIBLE
    5.  ) ENGINE=InnoDB;
    6.  ALTER TABLE t1 ALTER INDEX idx_tid VISIBLE;

  4. 展示信息增加：INFORMATION_SCHEMA.STATISTICS 内存表和 SHOW INDEX 结果里，分别多了一个 Visible/IS_VISIBLE 字段，表示索引是否可见：

    1.  mysql> SHOW INDEX FROM t1\G
    2.  *************************** 1. row ***************************
    3.  Table: t1
    4.  Non_unique: 1
    5.  Key_name: idx_tid
    6.  Seq_in_index: 1
    7.  Column_name: tid
    8.  Collation: A
    9.  Cardinality: 0
    10.  Sub_part: NULL
    11.  Packed: NULL
    12.  Null: YES
    13.  Index_type: BTREE
    14.  Comment:
    15.  Index_comment:
    16.  Visible: NO
    18.  mysql> SELECT * FROM INFORMATION_SCHEMA.STATISTICS where table_name='t1' AND index_name='idx_tid'\G
    19.  *************************** 1. row ***************************
    20.  TABLE_CATALOG: def
    21.  TABLE_SCHEMA: test
    22.  TABLE_NAME: t1
    23.  NON_UNIQUE: 1
    24.  INDEX_SCHEMA: test
    25.  INDEX_NAME: idx_tid
    26.  SEQ_IN_INDEX: 1
    27.  COLUMN_NAME: tid
    28.  COLLATION: A
    29.  CARDINALITY: 0
    30.  SUB_PART: NULL
    31.  PACKED: NULL
    32.  NULLABLE: YES
    33.  INDEX_TYPE: BTREE
    34.  COMMENT:
    35.  INDEX_COMMENT:
    36.  IS_VISIBLE: NO
    37.  1 row in set (0.00 sec)

下面我们用一例子来看下:

  1. CREATE TABLE `t1` (
  2.   `id` int(11) DEFAULT NULL,
  3.   `tid` int(11) DEFAULT NULL,
  4.   KEY `idx_tid` (`tid`) /*!50616 INVISIBLE */
  5. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  6. INSERT INTO t1 VALUES(1, 2), (3, 4), (5, 6), (7, 8), (9, 10);

可以看到下面的 EXPLAIN 结果，用的是全表扫描：

  1. mysql> EXPLAIN SELECT * FROM t1 WHERE tid=4;
  2. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  3. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
  4. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  5. |  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
  6. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  7. 1 row in set (0.00 sec)

如果用 FORCE INDEX 强制指定的话，会报索引不存在的错（这个官方早期版本是不会报错的，最新新版本已经fix）：

  1. mysql> EXPLAIN SELECT * FROM t1 FORCE INDEX(idx_tid) WHERE tid=4;
  2. ERROR 1176 (42000): Key 'idx_tid' doesn't exist in table 't1'

索引改为可见之后，优化器就可以用了：

  1. mysql> ALTER TABLE t1 ALTER INDEX idx_tid VISIBLE;
  2. mysql> EXPLAIN SELECT * FROM t1 WHERE tid=4;
  3. +----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
  4. | id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra |
  5. +----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
  6. |  1 | SIMPLE      | t1    | ref  | idx_tid       | idx_tid | 5       | const |    1 | NULL  |
  7. +----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
  8. 1 row in set (0.00 sec)

虽然索引对优化器不可见，但是 MySQL 内部还是会维护索引的，包括约束条件，可以看下面这个例子：

  1. CREATE TABLE `t2` (
  2. `id` int(11) NOT NULL DEFAULT '0',
  3. `tid` int(11) DEFAULT NULL,
  4. PRIMARY KEY (`id`),
  5. UNIQUE KEY `idx_tid` (`tid`) INVISIBLE
  6. ) ENGINE=InnoDB;
  8. mysql> INSERT INTO t2 VALUES (1, 2), (3, 4);
  9. Query OK, 2 rows affected (0.00 sec)
  10. Records: 2  Duplicates: 0  Warnings: 0
  12. mysql> INSERT INTO t2 VALUES (5, 2);
  13. ERROR 1062 (23000): Duplicate entry '2' for key 'idx_tid'

可以看到虽然 idx_tid 索引不可见，但是 UNIQUE 约束还是被遵守的。

使用注意和实现区别

使用注意：

  1. PK 不能设置为不可见，这里的 PK 包括显示的PK，或者因为PK不存在，被提升为 PK 的 UK；
  2. 虽然设置索引的不可见属性不需要重建表，但是改变了表定义（frm），需要重新打开表，因此会请求 MDL 排它锁，如果有大事务或者长SQL，会被 block，这点使用时需要注意；
  3. INFORMATION_SCHEMA.STATISTICS 内存表和 SHOW INDEX 结果里多一个字段，如果有用到的话，需要做好兼容。

另外 AliSQL 支持索引使用统计(INFORMATION_SCHEMA.INDEX_STATISTICS)，和 Invisible Indexes 配合使用效果更佳，比如我们可以根据索引使用找出使用频率低的索引，然后快速设置为不可见，如果业务没有影响的话，就可以进一步 DROP 掉索引。

实现上区别： 官方的 INVISIBLE INDEX 是实现在 8.0 里的，而在 8.0 其中一个重大改变，就是引入了 Data Dictionary，把原来在 Server 层放的元文件（.frm, .par, etc.）里的信息，全放在 InnoDB 里了。AliSQL 是 5.6 版本的，因此在元信息还是存储在 frm 文件里。这里有一个问题是，其中索引标志位占2个字节，目前16个 bit 已经全部被定义，如果扩展标志位的话，会造成不兼容，因为这里用了一个原先不会存在 frm 里flag HA_SORT_ALLOWS_SAME 来存储在 frm 表示索引不可见，这是为了保证兼容性，实现上比较 trick 的地方。