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

好嘞！各位观众老爷们，今天咱们聊点儿MySQL 8.0 里的“小透明”——隐藏索引（Invisible Indexes）。别看它名字不起眼，用好了，那可是能给你的数据库性能来个华丽转身，简直是优化界的“隐形战机”🚀。

开场白：索引，数据库里的“高速公路”

想象一下，你要在一座城市里找一个特定的地址。如果没地图，没路牌，你只能一条街一条街地瞎逛，累得半死不说，效率还奇低。索引，在数据库里就扮演着“地图”和“路牌”的角色。它能帮你快速定位到你想找的数据，避免全表扫描的“地毯式搜索”，大大提升查询效率。

但是，索引这玩意儿，用得好是“高速公路”，用不好就是“停车场”。建多了，浪费存储空间，还会拖慢数据更新的速度；建少了，又不够用，查询效率上不去。这就很让人头疼😫。

正文：隐藏索引，数据库里的“障眼法”大师

MySQL 8.0 引入的隐藏索引，就像一位“障眼法”大师，能让你在不真正删除索引的情况下，让优化器暂时忽略它。这有什么用呢？别急，听我慢慢道来。

1. 隐藏索引的“真面目”：INVISIBLE 属性

隐藏索引的秘密武器，就是一个叫 INVISIBLE 的属性。当你创建一个索引时，可以指定它是否可见：

VISIBLE: 默认值，索引可见，优化器会考虑使用它。
INVISIBLE: 索引隐藏，优化器默认不会使用它。

创建隐藏索引的语法很简单：

CREATE INDEX idx_name ON table_name (column_name) INVISIBLE;

修改索引可见性的语法也很简单：

ALTER TABLE table_name ALTER INDEX idx_name INVISIBLE;  -- 隐藏索引
ALTER TABLE table_name ALTER INDEX idx_name VISIBLE;    -- 显示索引

2. 隐藏索引的“独门绝技”：测试与验证

隐藏索引最大的用处，就是让你在不影响现有生产环境的情况下，测试和验证索引的有效性。

场景一：评估新索引的效果

假设你觉得某个查询很慢，想创建一个新的索引来优化它。但是，你又担心这个新索引会带来负面影响，比如拖慢其他查询，或者增加数据更新的开销。

这时候，你就可以先创建一个隐藏索引：
```
CREATE INDEX idx_new ON orders (customer_id, order_date) INVISIBLE;
```
然后，你可以通过 optimizer_switch 参数，强制优化器使用这个隐藏索引，看看效果如何：
```
SET optimizer_switch = 'use_invisible_indexes=on';
```
接下来，执行你的查询，看看性能有没有提升。如果效果不错，就可以把索引改成可见的：
```
ALTER TABLE orders ALTER INDEX idx_new VISIBLE;
```
如果效果不佳，或者发现有其他问题，就可以直接删除这个索引，而不用担心对生产环境造成影响。是不是很方便？😎
场景二：评估删除索引的影响

有时候，你可能会觉得某个索引没啥用，想把它删掉。但是，你又不敢贸然行动，因为你不知道这个索引是不是被某些你不知道的查询偷偷地用着。

这时候，你就可以先把这个索引隐藏起来：
```
ALTER TABLE orders ALTER INDEX idx_old INVISIBLE;
```
然后，观察一段时间（比如一周），看看系统有没有出现性能问题。如果没有，说明这个索引确实没啥用，可以放心删掉了。如果有问题，那就赶紧把它恢复成可见的。

这种方式比直接删除索引要安全得多，可以避免误删索引带来的风险。

3. 隐藏索引的“注意事项”：细节决定成败

虽然隐藏索引很好用，但是也有一些需要注意的地方：

optimizer_switch 参数: 默认情况下，优化器是不会使用隐藏索引的。如果你想强制优化器使用隐藏索引，需要设置 optimizer_switch = 'use_invisible_indexes=on'。但是，这个参数是会影响全局的，所以用完之后记得改回来： SET optimizer_switch = 'use_invisible_indexes=off';
或者，在session级别设置也可以：SET SESSION optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN 命令: 默认情况下，EXPLAIN 命令也不会显示隐藏索引。如果你想让 EXPLAIN 命令显示隐藏索引，需要设置 optimizer_switch = 'use_invisible_indexes=on'。
主键索引: 主键索引不能被设置为隐藏索引。这个是MySQL的限制，别想着把主键索引藏起来哦。
存储引擎: 隐藏索引功能适用于InnoDB和MyISAM存储引擎。其他的存储引擎可能不支持。
性能影响: 即使索引是隐藏的，它仍然会占用存储空间，并且在数据更新时会带来一定的开销。所以，不要滥用隐藏索引。

4. 隐藏索引的“管理之道”：让数据库井井有条

为了更好地管理隐藏索引，你可以使用一些SQL语句来查询和修改索引的可见性。

查询索引的可见性：

你可以通过查询 information_schema.statistics 表来获取索引的可见性信息。
```
SELECT
    TABLE_NAME,
    INDEX_NAME,
    IS_VISIBLE
FROM
    information_schema.statistics
WHERE
    TABLE_SCHEMA = 'your_database_name' AND INDEX_NAME = 'your_index_name';
```
IS_VISIBLE 列的值为 YES 表示索引可见，为 NO 表示索引隐藏。

批量隐藏或显示索引：

如果你想批量隐藏或显示多个索引，可以使用循环语句或者存储过程来实现。

例如，下面的存储过程可以批量隐藏指定表的所有索引：

DELIMITER //
CREATE PROCEDURE hide_all_indexes(IN db_name VARCHAR(255), IN tbl_name VARCHAR(255))
BEGIN
    DECLARE index_name VARCHAR(255);
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR
        SELECT INDEX_NAME
        FROM information_schema.statistics
        WHERE TABLE_SCHEMA = db_name AND TABLE_NAME = tbl_name AND INDEX_NAME != 'PRIMARY';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO index_name;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET @sql = CONCAT('ALTER TABLE `', db_name, '`.`', tbl_name, '` ALTER INDEX `', index_name, '` INVISIBLE;');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE cur;
END //
DELIMITER ;

-- 调用存储过程
CALL hide_all_indexes('your_database_name', 'your_table_name');

这个存储过程会遍历指定表的所有索引（除了主键索引），并将它们设置为隐藏。显示索引的存储过程也类似，只需要把 INVISIBLE 改成 VISIBLE 即可。

5. 隐藏索引的“最佳实践”：让数据库飞起来

定期清理无用索引： 使用隐藏索引功能，定期评估和清理无用索引，可以减少存储空间的占用，降低数据更新的开销，提升数据库的整体性能。
谨慎评估新索引： 在创建新索引之前，先使用隐藏索引功能进行测试和验证，可以避免盲目创建索引带来的风险。
监控系统性能： 在使用隐藏索引功能时，要密切关注系统性能，及时发现和解决潜在的问题。
文档记录： 对于隐藏的索引，要做好文档记录，说明隐藏的原因和目的，方便后续维护和管理。

案例分析：隐藏索引的“实战演练”

假设你有一个 orders 表，包含了订单信息，其中有一个 customer_id 字段，用于表示客户ID。

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
);

现在，你发现一个查询很慢，这个查询是根据 customer_id 统计订单总额：

SELECT customer_id, SUM(amount)
FROM orders
WHERE customer_id = 123
GROUP BY customer_id;

你觉得可以创建一个 customer_id 索引来优化这个查询。但是，你又担心这个索引会影响其他查询的性能。

这时候，你就可以先创建一个隐藏索引：

CREATE INDEX idx_customer_id ON orders (customer_id) INVISIBLE;

然后，设置 optimizer_switch 参数，强制优化器使用这个隐藏索引：

SET optimizer_switch = 'use_invisible_indexes=on';

接下来，执行你的查询，看看性能有没有提升：

SELECT customer_id, SUM(amount)
FROM orders
WHERE customer_id = 123
GROUP BY customer_id;

你可以使用 EXPLAIN 命令来查看查询计划，确认优化器是否使用了这个隐藏索引。

如果性能提升明显，就可以把索引改成可见的：

ALTER TABLE orders ALTER INDEX idx_customer_id VISIBLE;

如果性能没有提升，或者发现有其他问题，就可以直接删除这个索引：

DROP INDEX idx_customer_id ON orders;

通过这种方式，你就可以在不影响生产环境的情况下，安全地评估和测试索引的效果。

总结：隐藏索引，优化数据库的“秘密武器”

隐藏索引是 MySQL 8.0 引入的一个非常有用的功能，它可以让你在不影响生产环境的情况下，测试和验证索引的有效性，从而更好地优化数据库性能。

用好隐藏索引，就像拥有了一把“隐形手术刀”，可以精准地解决数据库性能问题，让你的数据库飞起来🚀！

希望今天的分享对大家有所帮助。如果有什么疑问，欢迎在评论区留言，我们一起探讨！谢谢大家！ 🙏

发表回复 取消回复

发表回复取消回复