MySQL 8.0 New Feature: Invisible Indexes - Alibaba Cloud Developer Forums: Cloud Discussion Forums

Ysera
Assistant Engineer
Assistant Engineer
  • UID634
  • Fans0
  • Follows0
  • Posts44
Reads:1740Replies:0

[MySQL]MySQL 8.0 New Feature: Invisible Indexes

Created#
More Posted time:Oct 24, 2016 13:45 PM
Invisible indexes are a new feature in MySQL 8.0. The optimizer simply ignores invisible indexes. Therefore, we can use this feature to affect optimizer behavior. In addition, we can regard it as a buffer before dropping an index. After temporarily setting an index as invisible, we can observe whether or not the application functions normally or if errors are reported. If everything works fine, we can then drop the index.
MySQL 8.0.0 release notes:

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.
This features applies to InnoDB tables only in MySQL 8.0.0, but to all engines (the code not yet released) in MySQL 8.0.1. MySQL 8.0.1 release notes:

Previously, invisible indexes were supported only for the InnoDB storage engine. Invisible indexes are now storage engine neutral (supported for any engine). (Bug #23541244)
Corresponding WorkLog: WL#8697: Support for INVISIBLE indexes
Official Documentation

Testing
# Create a common table t1 with only primary keys

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

# Add an index

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 t1\G
*************************** 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)

Issue the show indexes statement, and we can see YES in the Visible fields, which means that both indexes are 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

We can see that index b is in use


# Change index b to invisible

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 t1\G
*************************** 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 10\G
*************************** 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)

The index has been changed to invisible, so the optimizer will no longer select this index

# Change the index back to visible

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 10\G
*************************** 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)

# When creating an index, you can also explicitly indicate if it is visible or not

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)

# Or, indicate keywords during table creation

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

# However, primary keys cannot be set to invisible

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


Note that index invisibility does not affect index maintenance in the underlying storage engine (issuing a commit statement, you can see that the code in diff of the InnoDB table is not changed). The index is invisible only to the optimizer in the server.
The visibility attribute of indexes are stored in the is_visible column of the mysql.indexes system table.
Guest