MySQL·8.0新特性·InvisibleIndex

從MySQL8.0開始支援隱藏索引特性，也就是所謂的invisible index。對於不可見的索引，優化器將直接忽略掉。我們可以通過該特性來影響優化器的行為。另外這也可以視為在drop一個索引之前的緩衝，臨時把索引設定為不可見後，再觀察應用是否正常或有報錯什麼的，如果一切OK，再最終刪除。

對應的8.0.0的release note:

MySQL now supports invisible indexes. An invisible index is not used by the optimizer at all, but is otherwise maintained normally. Indexes are visible by default. Invisible indexes make it possible to test the effect of removing an index on query performance, without making a destructive change that must be undone should the index turn out to be required. This feature applies to InnoDB tables, for indexes other than primary keys.

To control whether an index is invisible explicitly for a new index, use a VISIBLE or INVISIBLE keyword as part of the index definition for CREATE TABLE, CREATE INDEX, or ALTER TABLE. To alter the invisibility of an existing index, use a VISIBLE or INVISIBLE keyword with the ALTER TABLE ... ALTER INDEX operation. For more information, see Invisible Indexes.

在8.0.0中僅支援innodb，在8.0.1版本中修改成支援所有引擎（程式碼尚未release），對應release note:

Previously, invisible indexes were supported only for the InnoDB storage engine. Invisible indexes are now storage engine neutral (supported for any engine). (Bug #23541244)

對應WorkLog: WL#8697: Support for INVISIBLE indexes

官方文件

測試

# 建立一個普通的表t1，只帶主鍵

mysql> create table t1 (a int primary key auto_increment, b int, c int, d int);
Query OK, 0 rows affected (0.67 sec)

# 增加一個索引

mysql> alter table t1 add key(b);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show indexes from t1G
*************************** 1. row ***************************
        Table: t1
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: a
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
*************************** 2. row ***************************
        Table: t1
   Non_unique: 1
     Key_name: b
 Seq_in_index: 1
  Column_name: b
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
2 rows in set (0.01 sec)

從show indexes的visible列顯示了，這兩個索引都是可見的。

# Load some data
insert into t1 select NULL, rand()*100000, rand()*10000,rand()*10000;
insert into t1 select NULL, rand()*100000, rand()*10000,rand()*10000 from t1;
insert into t1 select NULL, rand()*100000, rand()*10000,rand()*10000 from t1;
....

analyze table t1;

mysql> explain select * from t1 where b > 5000 limit 10;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | b             | b    | 5       | NULL | 1932 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec

可以看到索引b被使用到


# 修改索引b為不可見

mysql> alter table t1 alter index b invisible;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show indexes from t1G
*************************** 1. row ***************************
        Table: t1
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: a
    Collation: A
  Cardinality: 2048
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
*************************** 2. row ***************************
        Table: t1
   Non_unique: 1
     Key_name: b
 Seq_in_index: 1
  Column_name: b
    Collation: A
  Cardinality: 2029
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: NO
2 rows in set (0.01 sec)



mysql> explain select * from t1 where b > 5000 limit 10G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2048
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

當索引被修改為invisible後，優化器將不再選擇這個索引

# 將索引重新修改為可見

mysql> alter table t1 alter index b visible;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t1 where b > 5000 limit 10G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: b
          key: b
      key_len: 5
          ref: NULL
         rows: 1932
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

# 你也可以在建立索引的時候顯式指定是否可見

mysql> alter table t1 add key(c) invisible;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show indexes from t1 where key_name = `c`G
*************************** 1. row ***************************
        Table: t1
   Non_unique: 1
     Key_name: c
 Seq_in_index: 1
  Column_name: c
    Collation: A
  Cardinality: 1848
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: NO
1 row in set (0.01 sec)

# 或者在建表時指定關鍵字

mysql> create table t2 (a int primary key, b int, key(b) invisible);
Query OK, 0 rows affected (0.67 sec)

# 但primary key不可以設定為不可見

mysql> drop table t2;
Query OK, 0 rows affected (0.03 sec)

mysql> create table t2 (a int, b int, primary key(a) invisible);
ERROR 3522 (HY000): A primary key index cannot be invisible

需要注意，把索引設定為不可見後，在引擎層的底層儲存，索引依然是需要繼續被維護的(從commit的diff可以看到，innodb部分的程式碼是完全沒有改動的)，只是在server層對優化器不可見而已。

index是否隱藏的屬性存放在系統表mysql.indexes的列is_visible中。