mysql5.7 隐藏_MySQL 8.0 中的索引可以隐藏了!

本文介绍了MySQL 8.0中的隐藏索引特性,允许将非主键索引设置为不可见,便于测试SQL查询性能和进行索引管理。隐藏索引不影响索引维护,可以通过optimizer_switch系统变量控制其在执行计划中的使用。
摘要由CSDN通过智能技术生成

MySQL 8.0

虽然发布很久了,但可能大家都停留在

5.7.x

,甚至更老,其实

MySQL 8.0

新增了许多重磅新特性,比如栈长今天要介绍的

"

隐藏索引

"

或者

"

不可见索引

"

隐藏索引是什么鬼?

隐藏索引

字面意思就是把索引进行隐藏,即不可见,它不是用来查询优化的,所以它不会被优化器使用到。隐藏索引适用于除主键索引(显示或者隐式设置)之外的索引,意味着主键索引是不能通过任何方式隐藏的。

MySQL

数据库默认创建的索引都是可见的,要显式控制一个索引的可见性,可以在

CREATE TABLE

CREATE INDEX

ALTER TABLE

的索引定义命令中使用

VISIBLE

INVISIBLE

关键字。

如下面示例所示:

CREATE TABLE javastack (

age INT,

weight INT,

tall INT,

INDEX age_idx (age) INVISIBLE

) ENGINE = InnoDB;

CREATE INDEX weight_idx ON javastack (weight) INVISIBLE;

ALTER TABLE javastack ADD INDEX tall_idx (tall) INVISIBLE;

要变更现有索引的可见性,可以在

ALTER TABLE ... ALTER INDEX

命令中使用

VISIBLE

INVISIBLE

关键字。

年龄索引变更为不可见(隐藏):

ALTER TABLE javastack ALTER INDEX age_idx INVISIBLE;

年龄索引变更为可见:

ALTER TABLE javastack ALTER INDEX age_idx VISIBLE;

怎么知道一个表中的索引是可见还是不可见,可以从

INFORMATION_SCHEMA.STATISTICS

表,或者

SHOW INDEX

命令输出中获得。例如:

mysql> SELECT

INDEX_NAME,

IS_VISIBLE

FROM INFORMATION_SCHEMA.STATISTICS

WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 'javastack';

+------------+------------+

| INDEX_NAME | IS_VISIBLE |

+------------+------------+

| age_idx      | YES        |

| weight_idx   | NO         |

| tall_idx     | NO         |

+------------+------------+

隐藏索引有什么用?

从上面隐藏索引介绍我们知道,隐藏索引可以不被优化器所使用,那么我们可以把某个表的某个索引设置隐藏,然后再测试

SQL

语句的查询性能。

即可以利用隐藏索引快速测试删除索引后对

SQL

查询性能的影响,而无需进行索引删除、重建操作,如果需要该索引,再设置可见就好了,这在大表测试中无疑非常有用,因为对于大表索引的删除和重新添加很耗性能,甚至影响表的正常工作。

隐藏索引设置

如果一个索引被设置成隐藏了,但实际上又需要被优化器所使用,有几种表索引情况缺失对查询造成的影响:

1

)

SQL

查询语句中包含了索引提示指向不可见索引会发生错误;

2

)性能模式数据中显示了受影响

SQL

查询语句的负载增高;

3

)

SQL

查询语句进行

EXPLIAN

时出现了不同的执行计划;

4

)

SQL

查询语句出现在了慢查询日志中(之前没有出现);

系统变量

optimizer_switch

use_invisible_indexes

标志的值,控制了优化器执行计划构建时是否使用隐藏索引。

如果

use_invisible_indexes

值设置为

off

关闭状态(默认值),优化器默认会忽略隐藏索引,即和加入该参数之前的效果一样。

如果

use_invisible_indexes

值设置为

on

打开状态,隐藏索引仍然保持不可见,但优化器会把隐藏索引加入到执行计划的构建中。

如果想要在某条单个

SQL

查询语句上启用隐藏索引,可以使用

SET_VAR

优化器提示来临时更新

optimizer_switch

的值,如下所示:

mysql> EXPLAIN SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */

>     age, weight FROM javastack WHERE weight >= 150\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: javastack

partitions: NULL

type: range

possible_keys: weight_idx

key: weight_idx

key_len: 5

ref: NULL

rows: 2

filtered: 100.00

Extra: Using index condition

mysql> EXPLAIN SELECT age, weight FROM javastack WHERE weight >= 150\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: javastack

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 5

filtered: 33.33

Extra: Using where

索引的可见性不会影响

索引的自身维护,例如,不管索引是可见还是不可见,每次表数据行的更改索引都会更新,并且唯一索引也可防止插入重复数据。

没有显式主键的表如果在 NOT NULL 列上有任何一个唯一索引,则仍可能成为有效的隐式主键。在这种情况下,第一个这样的索引会对表数据行施加与显式主键相同的约束,并且该索引不能设置为不可见。

如以下表的定义:

CREATE TABLE javastack (

age INT NOT NULL,

weight INT NOT NULL,

UNIQUE weight_idx (weight)

) ENGINE = InnoDB;

该表定义不包含任何显式主键,但是

weight

列为

NOT NULL

,在该列上创建的唯一索引在数据行上与主键具有相同的约束,并且不能使其不可见:

mysql> ALTER TABLE javastack ALTER INDEX weight_idx INVISIBLE;

ERROR 3522 (HY000): A primary key index cannot be invisible.

假设现在我们将一个显式主键添加到表中:

ALTER TABLE javastack ADD PRIMARY KEY (age);

显式主键不能设置为不可见,此时,

weight

列上的唯一索引不再充当隐式主键,因此可以使其设置不可见。

mysql> ALTER TABLE javastack ALTER INDEX weight_idx INVISIBLE;

Query OK, 0 rows affected (0.03 sec)

总结

本文介绍了

MySQL 8.0

中的新特性:隐藏(不可见)索引,这个索引并不是新加的索引类型,而是可以控制索引是否加入到执行计划的构建之中。

在实际生产中也可以利用隐藏索引进行

SQL

语句的性能测试,或者对索引进行逻辑删除,以及索引的灰度发布测试等,用处还是蛮大的。

本次的分享就到这里了,希望对大家有用。觉得不错,在看、转发分享一下哦

~

最后,

MySQL

系列教程还会继续更新,关注

Java

技术栈公众号第一时间推送,还可以在公众号菜单中获取历史

MySQL

教程,都是干货。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值