MySQL 8.0 隐藏索引（Invisible Indexes）的应用与管理

好的，各位亲爱的程序员朋友们，大家好！我是你们的老朋友，人称“代码诗人”的码农老王。今天，咱们来聊聊MySQL 8.0里一个低调但实力不俗的功能——隐藏索引（Invisible Indexes）。

想象一下，你们精心设计了一个数据库表，上面布满了各种索引，就像夜空中的繁星，闪耀着优化的光芒。然而，时间长了，有些“星星”的光芒黯淡了，它们可能不再被查询优化器宠幸，甚至成了拖慢查询速度的罪魁祸首。这时候，你是不是想把它们摘掉，却又担心摘掉后影响现有业务？

这时候，隐藏索引就派上用场啦！它就像一个隐身斗篷，让索引不再被查询优化器看见，但又实实在在地存在着。你可以先让索引“隐身”，观察一段时间，确认没有问题后再彻底删除，是不是很棒？😎

好了，废话不多说，咱们这就开始今天的“隐藏索引探秘之旅”。

一、什么是隐藏索引？（Invisible Indexes，你瞅啥？）

简单来说，隐藏索引就是MySQL 8.0引入的一种索引状态，它可以让索引对查询优化器“隐形”。默认情况下，索引是“可见的”（Visible），查询优化器会考虑使用它来加速查询。但当你把索引设置为“不可见”（Invisible）后，查询优化器就会忽略它，就像它不存在一样。

想象一下： 你有一个装满玩具的房间，查询优化器就像一个挑剔的小朋友，负责挑选玩具来玩（执行查询）。可见索引就像摆在明面上的玩具，小朋友会优先考虑它们。而隐藏索引就像被你藏在柜子里的玩具，小朋友压根看不到，自然也不会去玩。

二、为什么要使用隐藏索引？（这玩意儿有啥用？）

隐藏索引的出现，主要是为了解决以下几个问题：

安全删除索引： 删除索引是高危操作，万一删错了，可能导致线上事故。隐藏索引提供了一个“试错”的机会。你可以先隐藏索引，观察一段时间，确认没有问题后再删除，避免误删。
评估索引的影响： 在生产环境中，评估某个索引的影响是很困难的。隐藏索引可以让你在不影响现有业务的情况下，评估删除该索引的影响。你可以先隐藏索引，观察一段时间的查询性能变化，再决定是否删除。
在线测试索引： 有时候，你可能想测试一个新的索引，但又不想立即让它影响线上查询。隐藏索引可以让你先隐藏这个索引，然后通过optimizer_switch参数强制查询优化器使用它，观察其性能表现，再决定是否让它“重见天日”。
灰度发布： 对于大型系统，索引的变更可能需要灰度发布。你可以先隐藏新索引，只让部分用户或应用使用它，观察一段时间后再逐步推广。

三、如何管理隐藏索引？（手把手教你玩转隐藏索引）

管理隐藏索引非常简单，主要通过ALTER TABLE语句来实现。

1. 创建索引时指定可见性：

CREATE TABLE `employees` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int DEFAULT NULL,
  `department` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_age` (`age`) INVISIBLE, -- 创建时直接设置为隐藏
  KEY `idx_department` (`department`) VISIBLE  -- 显式指定为可见，其实默认就是可见的
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

注意： 如果你不指定VISIBLE或INVISIBLE，默认创建的索引是可见的。

2. 修改现有索引的可见性：

-- 将索引设置为隐藏
ALTER TABLE `employees` ALTER INDEX `idx_name` INVISIBLE;

-- 将索引设置为可见
ALTER TABLE `employees` ALTER INDEX `idx_name` VISIBLE;

3. 查看索引的可见性：

你可以通过以下几种方式查看索引的可见性：

SHOW INDEX语句：
```
SHOW INDEX FROM `employees`;
```
在结果集中，你会看到一个Visible列，1表示可见，0表示隐藏。

INFORMATION_SCHEMA.STATISTICS表：

SELECT INDEX_NAME, VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 'employees' AND TABLE_SCHEMA = 'your_database_name';

同样，VISIBLE列的值表示索引的可见性。

performance_schema.table_io_waits_summary_by_index_usage 表:
```
SELECT INDEX_NAME, COUNT_STAR, SUM_TIMER_WAIT FROM performance_schema.table_io_waits_summary_by_index_usage WHERE TABLE_NAME = 'employees' AND TABLE_SCHEMA = 'your_database_name' ORDER BY SUM_TIMER_WAIT DESC;
```
这个表可以帮助你了解索引的使用情况，如果一个索引是隐藏的，并且长时间没有被使用，那么你就可以考虑删除它了。

四、隐藏索引的注意事项（前方高能预警！）

主键索引不能被隐藏： 主键是表的基础，不能被隐藏。如果你尝试隐藏主键索引，MySQL会报错。
唯一索引不能被隐藏： 唯一索引保证了数据的唯一性，也不能被隐藏。
全文索引不能被隐藏： 全文索引用于全文搜索，同样不能被隐藏。
空间索引不能被隐藏: 空间索引用于空间数据，同样不能被隐藏。
FORCE INDEX无效： 即使你使用了FORCE INDEX强制查询优化器使用某个索引，如果该索引是隐藏的，查询优化器仍然会忽略它。
optimizer_switch参数： 你可以通过optimizer_switch参数来控制查询优化器是否可以使用隐藏索引。例如：
```
-- 允许查询优化器使用隐藏索引
SET optimizer_switch = 'use_invisible_indexes=on';

-- 禁止查询优化器使用隐藏索引（默认值）
SET optimizer_switch = 'use_invisible_indexes=off';
```
这个参数可以让你在不改变索引可见性的情况下，测试隐藏索引的性能。
复制： 在主从复制环境中，索引的可见性会被复制到从库。也就是说，你在主库上隐藏了一个索引，从库上的该索引也会被隐藏。
备份与恢复： 备份和恢复操作会保留索引的可见性。也就是说，如果你备份了一个包含隐藏索引的数据库，恢复后，这些索引仍然是隐藏的。
性能影响： 隐藏索引本身不会对性能产生影响。因为查询优化器不会考虑使用它们，所以它们不会影响查询速度。但是，过多的无用索引会占用存储空间，并且在插入、更新、删除数据时，会增加维护索引的开销。

五、实战案例：优化慢查询（让你的查询飞起来！）

假设你有一个orders表，记录了用户的订单信息。

CREATE TABLE `orders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `order_date` date NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `status` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_order_date` (`order_date`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

现在，你发现一个查询非常慢：

SELECT * FROM `orders` WHERE `status` = 'pending' AND `order_date` > '2023-01-01';

你怀疑idx_status索引可能不是最优的，因为它只覆盖了status列，而查询还需要过滤order_date。

解决方案：

隐藏idx_status索引：

ALTER TABLE `orders` ALTER INDEX `idx_status` INVISIBLE;

创建一个新的复合索引：

CREATE INDEX `idx_status_order_date` ON `orders` (`status`, `order_date`);

观察查询性能：

观察一段时间，看看新的索引是否能提高查询速度。你可以使用EXPLAIN语句来分析查询计划，看看查询优化器是否使用了新的索引。
如果新的索引效果良好，删除旧的索引：
```
DROP INDEX `idx_status` ON `orders`;
```
注意： 在删除索引之前，一定要确保新的索引已经稳定运行，并且不会影响现有业务。
如果新的索引效果不佳，恢复旧的索引：
```
ALTER TABLE `orders` ALTER INDEX `idx_status` VISIBLE;
```
然后，你可以尝试其他的优化方案。

六、隐藏索引的局限性（没有银弹！）

虽然隐藏索引很强大，但它并不是万能的。它也有一些局限性：

不能解决所有性能问题： 隐藏索引只能帮助你管理索引，但不能解决所有性能问题。如果你的查询慢是由于其他原因造成的，比如锁竞争、IO瓶颈等，隐藏索引就无能为力了。
需要监控： 你需要定期监控隐藏索引的使用情况，避免它们长期占用存储空间。
需要谨慎使用： 隐藏索引是一个强大的工具，但也需要谨慎使用。如果你不了解其原理和注意事项，可能会导致意想不到的问题。

七、总结（码农老王的肺腑之言）

隐藏索引是MySQL 8.0引入的一个非常实用的功能，它可以帮助你更安全、更灵活地管理索引。你可以使用它来评估索引的影响、在线测试索引、安全删除索引等。但是，隐藏索引并不是万能的，你需要谨慎使用，并且定期监控其使用情况。

希望今天的分享对大家有所帮助。记住，代码的世界是充满乐趣的，让我们一起努力，写出更优雅、更高效的代码！💪

最后，送给大家一句码农老王的座右铭：“代码如诗，注释如画，Bug如人，与你相伴。” 😉

希望各位朋友们喜欢今天的分享，我们下期再见！拜拜！ 👋

发表回复 取消回复

发表回复取消回复