likes
comments
collection
share

MySQL也开始堆砌功能了，😳 索引还能隐藏起来？

作者站长头像
站长
· 阅读数 5

MySQL的索引可以隐藏起来？第一次看到时，让我感到非常懵逼😳。我进行了一番调研，发现这是MySQL 8.0的新特性，该版本于2018年发布，距今已经过去了 6 年。我现在才了解这一特性，实在惭愧吖！

今天必须给他盘明白！

MySQL也开始堆砌功能了，😳 索引还能隐藏起来？

MySQL 8.0 引入了一个新特性：隐藏索引。简单来说，就是可以将索引隐藏起来，当进行查询时，MySQL会忽略被隐藏的索引，但实际上并未删除该索引。

在MySQL数据库删除索引时，会耗费较高的资源，极大地影响服务器性能。然而，隐藏索引的开销非常小，在某些需要软删除索引的情况下，可以首先考虑隐藏索引，使索引暂时失效，达到类似删除的效果。例如，当索引存在重复时，查询语句命中的索引总是效率较低的一个索引，此时可以考虑将重复的低效索引隐藏起来，这样再次进行查询时，MySQL就不会使用该索引。虽然使用 force index可以强制走某个索引，但是改SQL需要上线修复啊，使用隐藏索引无需上线就能解决问题，何乐而不为呢。

接下来五哥将通过实验，探究下隐藏索引的特性。

设计一个表

设计userInfo 用户信息表，其中主键自增，name具有唯一索引！

CREATE TABLE `userInfo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=utf8mb4

如何隐藏一个索引

  1. 隐藏一个索引：

mysql> alter table userInfo alter INDEX nameINVISIBLE;

  1. 取消隐藏索引：

mysql> alter table userInfo alter INDEX name VISIBLE;

当隐藏索引后，我们查看下 表结构的变化

MySQL也开始堆砌功能了，😳 索引还能隐藏起来？ 当索引被隐藏后，表结构中会添加注释，显示索引被隐藏。

隐藏索引后，查询时还可以使用该索引吗？

经过测试后，发现

  1. 隐藏索引后，查询时，将无法命中该索引，使用explain可以查看执行计划并未选中该索引。

  2. 取消隐藏索引后，再次查询，可以重新命中索引！

MySQL也开始堆砌功能了，😳 索引还能隐藏起来？

思考

既然取消隐藏后，可以重新使用该索引，那么索引被隐藏期间，写入数据时是否会更新索引呢？

答案是 会的，隐藏期间，写入记录会同步更新索引。 （MySQL 官方文档作了说明）

隐藏索引后，对写入数据的影响

接下来测试索引被隐藏后，写入数据时，是否会有影响。例如当一个索引是唯一索引时，被隐藏后，写入冲突的数据，测试该索引是否生效！

userInfo表的name列是唯一索引列，表中已经存在 name = '3' 的记录，当索引被隐藏后，重新插入 name = '3'的记录，发现写入失败，被告知 索引冲突！

由此可见， 在索引被隐藏后，唯一索引的去重特性依然生效！ MySQL也开始堆砌功能了，😳 索引还能隐藏起来？

隐藏索引后，更新SQL是否会锁住整个表？

我们都知道，MySQL在更新记录时，会添加行锁、间隙锁，这个锁是锁定在索引上的。换句话说，当更新记录的SQL没有命中索引时，此时MySQL会锁住整个表。

接下来，将测试 索引被隐藏后，更新SQL是否会 锁住整张表！

测试的结果反馈：索引被隐藏后，更新SQL无法命中该索引，如果此时没有命中其他索引，那么更新SQL会锁住整张表！

执行顺序事务 1事务 2
0：隐藏 name索引
1update userInfo set name = password where name = '3';
2开启事务
3update userInfo set name = password where name = '4'; 被阻塞
4COMMIT 提交事务
5updte语句停止阻塞，开始执行

通过测试情况可以看出，当隐藏索引后，事务 1 更新 name = 3的记录，暂不提交事务。然后事务 2 尝试更新 name = 4的记录，但是却被阻塞了。正常情况下，事务 1和事务 2分别修改 3，4两条记录，应该互不影响。

这表明，当索引被隐藏后，更新SQL无法命中索引，因此整张表被锁住了，这导致事务 2再进行更新操作，会被阻塞住！

通过执行 explain 查询更新SQL的执行计划，可以看到 possible_keys 一列为空，说明没有命中任何索引。这与测试结果完全一致。 MySQL也开始堆砌功能了，😳 索引还能隐藏起来？

隐藏索引会随着数据量增加 耗时增加吗？

删除索引是比较耗时的动作，随着表中数据量增加耗时也在增加。由此推测，随着数据量增加，隐藏索引耗时会增加吗？

为了验证这个推测，我需要在表中灌入大量数据，然后验证隐藏索引的性能

使用MySQL 存储过程灌入大量数据

定义 存储过程

定义存储过程，往数据库中插入 10万条数据

DROP PROCEDURE IF EXISTS proc_initData;
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i<=100000 DO
         insert into userInfo(`name`, `password`) values(i,i);
        SET i = i+1;
    END WHILE;
END $
CALL proc_initData();

执行存储过程

我将存储过程放到本地文件中，使用source filePath 命令执行该存储过程。经过一段时间，存储过程执行完成。

MySQL也开始堆砌功能了，😳 索引还能隐藏起来？

查询总记录数，接近10W条。 MySQL也开始堆砌功能了，😳 索引还能隐藏起来？

验证隐藏索引

通过下图可以看到，MySQL在十万数据量时，隐藏索引的耗时 仅40ms，速度是很快的。 MySQL 数据量小于10条时，我尝试隐藏索引，耗时大概是 20ms，数据量增加，隐藏索引的耗时并未显著增加。

从MySQL官方文档的描述来看，隐藏索引是轻量操作，在无法接受删除索引的性能损耗时，可以使用隐藏索引替代删除索引。

MySQL也开始堆砌功能了，😳 索引还能隐藏起来？

当有其他事务持有行锁时，隐藏索引被阻塞

在测试隐藏索引的过程中，碰巧有个事务还没有提交，正在持有行锁。当我尝试隐藏索引时，发现操作被阻塞了。

只有当行锁被释放后，才能执行隐藏索引的操作。

由此可见，隐藏索引需要等待表的所有写锁被释放之后才能执行。如果在业务高峰期时，虽然隐藏索引的执行速度很快，但如果其他事务持有锁的时间很长，隐藏索引的操作就会被阻塞。

总结

  1. 隐藏索引是轻量操作，随数据量增加，隐藏索引耗时增加不明显。

  2. 隐藏索引后，写入记录依然会更新索引数据。并且唯一索引还会生效，如果数据重复，写入依然会失败。

  3. 隐藏索引后，查询SQL将无法使用该索引，更新语句中Where条件也无法命中该索引。

  4. 隐藏索引后，更新语句无法命中该索引，如果也没有命中其他索引，那么更新语句会锁住整个表。

  5. 隐藏所以后，可以取消隐藏。取消后，索引可以正常使用。

最后相信大家公司里都还在使用 MySQL 5.6，5.7 ，还没有大规模使用8.0。

虽然隐藏索引没啥大用，但是可以多多了解，扩充下技术视野。

转载自:https://juejin.cn/post/7300782146603466793
评论
请登录