|
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. |
|
