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

vlambda
2020-09-24

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

我们有时候想删除掉冗余索引，但是又怕删除之后影响到查询性能，这时候再回退就需要一定的时间。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)