MySQL · 8.0新特性· Invisible Index-阿里云开发者社区

开发者社区> 阿里云数据库> 正文

MySQL · 8.0新特性· Invisible Index

简介:

从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 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)

从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 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)

当索引被修改为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 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)

# 你也可以在创建索引的时候显式指定是否可见

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中。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
链接