mysql8.0新特性--隐藏索引

MySQL数据库技术栈

发布于 2020-09-24 11:06:49

5260

发布于 2020-09-24 11:06:49

我们有时候想删除掉冗余索引，但是又怕删除之后影响到查询性能，这时候再回退就需要一定的时间。MySQL8.0开始支持隐藏索引（invisible indexes），隐藏索引不会被优化器使用，如果你想验证某个索引删除之后的查询性能影响，就可以暂时先隐藏该索引。但是有一点主键不能被设置为隐藏索引，当表中没有显式主键时，表中第一个唯一非空索引会成为隐式主键，也不能设置为隐藏索引。

索引默认是可见的，在使用CREATE TABLE,，CREATE INDEX或者ALTER TABLE等语句时可以通过VISIBLE或者INVISIBLE关键词设置索引的可见性。

创建新表时指定隐藏索引

CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `id_card` int NOT NULL,
  `name` varchar(10) NOT NULL,
  `age` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_id_card` (`id_card`) /*!80000 INVISIBLE */
) ENGINE=InnoDB

我导入了4万条数据，通过以下执行计划可以看到，优化器并没有使用索引，而是使用的全表扫描。

mysql> explain select * from student where id_card=100;
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 39231 |     0.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

alter修改隐藏索引为可见。

alter table student alter index idx_id_card visible;

再次查看执行计划，这次优化器选择了idx_id_card索引。

mysql> explain select * from student where id_card=100;
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_id_card   | idx_id_card | 4       | const |    4 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

alter修改普通索引为隐藏索引。

alter table student alter index idx_id_card invisible;

查询某个表中的索引是否为隐藏索引

mysql> SELECT INDEX_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'student';
+-------------+------------+
| INDEX_NAME  | IS_VISIBLE |
+-------------+------------+
| idx_id_card | NO         |
| PRIMARY     | YES        |
+-------------+------------+
2 rows in set (0.00 sec)

系统变量optimizer_switch中的use_invisible_indexes控制了优化器在构建执行计划时是否使用隐藏索引，如果设置为off(默认)优化器会忽略隐藏索引。如果设置为on，即使隐藏索引不可见，优化器在生成执行计划时仍会考虑使用隐藏索引。

参考了MySQL官方文档《refman-8.0-en.a4》。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2020-09-23，如有侵权请联系 cloudcommunity@tencent.com 删除

云推荐引擎