vlambda博客
学习文章列表

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)

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


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