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 教程,都是干货。
参考文档:
https://dev.mysql.com/doc/ref…
话说你们用的 MySQL 哪个版本呢?来投票一下!
另外,关注公众号 Java 技术栈,在后盾回复:面试,能够获取我整顿的 MySQL 系列面试题和答案,十分齐全。
版权申明:本文系公众号 “Java 技术栈 ” 原创,原创实属不易,转载、援用本文内容请注明出处,禁止剽窃、洗稿,请自重,尊重别人劳动成果和知识产权。
近期热文举荐:
1.Java 15 正式公布,14 个新个性,刷新你的认知!!
2. 终于靠开源我的项目弄到 IntelliJ IDEA 激活码了,真香!
3. 我用 Java 8 写了一段逻辑,共事直呼看不懂,你试试看。。
4. 吊打 Tomcat,Undertow 性能很炸!!
5.《Java 开发手册(嵩山版)》最新公布,速速下载!
感觉不错,别忘了顺手点赞 + 转发哦!