牛逼！MySQL 8.0 中的索引可以隐藏了…

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

显式主键不能设置为不可见，此时mssql 在表中插入，weight 列上的唯一索引不再充当隐式主键，因此可以使其设置不可见。

mysql>?ALTER?TABLE?javastack?ALTER?INDEX?weight_idx?INVISIBLE;?
Query?OK,?0?rows?affected?(0.03?sec)

总结

本文介绍了 MySQL 8.0 中的新特性：隐藏（不可见）索引，这个索引并不是新加的索引类型，而是可以控制索引是否加入到执行计划的构建之中。

在实际生产中也可以利用隐藏索引进行 SQL 语句的性能测试，或者对索引进行逻辑删除，以及索引的灰度发布测试等，用处还是蛮大的。

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

（编辑：通辽站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!