MySQL · 8.0新特性· Invisible Index

本文涉及的产品
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS SQL Server,基础系列 2核4GB
简介:

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

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
存储 关系型数据库 MySQL
MySQL · myrocks · clustered index特性
Cluster index介绍 最近在RDS MyRocks中,我们引入了一个重要功能,二级聚集索引(secondary clustering index). 我们知道innodb和rocksdb引擎的主键就是clustered index。
1957 0
|
存储 自然语言处理 AliSQL
MySQL · 引擎特性 · Column Compression浅析
前言 当用户的数据量比较大时,通常需要对数据进行压缩,以减少磁盘占用。InnoDB目前有两种方式来实现这一目的。 第一种是传统的数据压缩,通过指定row_format及key_block_size,能够将用户表压缩到指定的page size并进行存储,默认使用zlib。这种压缩方式使用比较简单,但也是诟病较多的, 代码陈旧,相关代码基本上几个大版本都没发生过变化,一些优化点还是从faceboo
1946 0
|
存储 SQL 关系型数据库
MySQL · 源码阅读 · Secondary Engine
背景MySQL默认的存储引擎是InnoDB,而引入Secondary Engine,用来实现同时支持第二引擎,在同一个MySQL Server上挂多个存储引擎,在支持InnoDB的同时,还可以把数据存放在其他的存储引擎上。 全量的数据都存储在Primary Engine上,某些指定数据在Secondary Engine 上也存放了一份,然后在访问这些数据的时候,会根据系统参数和cost选择存储引擎
1065 0
MySQL · 源码阅读 · Secondary Engine
|
SQL 关系型数据库 MySQL
MySQL · 引擎特性 · InnoDB index lock前世今生
前言 InnoDB并发过程中使用两类锁进行同步。 1. 事务锁 维护在不同的Isolation level下数据库的Atomicity和Consistency两大基本特性。 InnoDB定义了如下的lock mode: /* Basic lock modes */ enum lock_m
3219 0
|
关系型数据库 MySQL
MySQL · 新特性分析 · 5.7中Derived table变形记
Derived table实际上是一种特殊的subquery,它位于SQL语句中FROM子句里面,可以看做是一个单独的表。MySQL5.7之前的处理都是对Derived table进行Materialize,生成一个临时表保存Derived table的结果,然后利用临时表来协助完成其他父查询的操作,比如JOIN等操作。MySQL5.7中对Derived table做了一个新特性。该特性允许将符合
7236 0
|
SQL Oracle 关系型数据库
MySQL · 特性分析 · common table expression
common table expression Common table expression简称CTE,由SQL:1999标准引入, 目前支持CTE的数据库有Teradata, DB2, Firebird, Microsoft SQL Server, Oracle (with recursion since 11g release 2), PostgreSQL (since 8.4), Mar
2288 0
|
缓存 关系型数据库 MySQL
MySQL · 特性分析 · drop table的优化
背景 系统为了加速对象的访问,通常都会增加一层缓存,以缓解下一层IO的瓶颈,OS的page cache和数据库的buffer pool都基于此。 但对象的删除,如果同步清理对象的缓存的话,不仅大大增加了延时,同时可能因为缓存过大导致IO blooding。所以针对缓存的清理,都会采用lazy d
1853 0
|
存储 SQL 关系型数据库
MySQL · 引擎特性 · DROP TABLE之binlog解析
Drop Table的特殊之处 Drop Table乍一看,与其它DDL 也没什么区别,但当你深入去研究它的时候,发现还是有很多不同。最明显的地方就是DropTable后面可以紧跟多个表,并且可以是不同类型的表,这些表还不需要显式指明其类型,比如是普通表还是临时表,是支持事务的存储引擎的表还是不支持事务的存储引擎的表等。这些特殊之处对于代码实现有什么影响呢?对于普通表,无论是创建还是删除,数据库
2258 0
|
SQL 存储 关系型数据库
MySQL · 5.7新特性 · Query Rewrite Plugin
Query Rewrite Plugin 从MySQL5.7.6版本开始支持Rewrite Plugin,可以将符合条件的SQL进行重写。在真实世界中,这个特性还是非常有用的,例如错误的上线了某个SQL,但由于无法走到索引导致全库; 或者你可能使用某个第三方的已编译好的软件,但SQL可能执行错误,
11798 0

相关产品

  • 云数据库 RDS MySQL 版