MySQL高级讲座篇之：MySQL 8.0的隐藏索引：如何利用它们进行性能测试和索引优化？ - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位观众老爷，大家好！我是你们的老朋友，今天咱们聊点MySQL 8.0里有点意思的东西：隐藏索引 (Invisible Indexes)。

这玩意儿，听起来是不是有点像武侠小说里的隐身术？别急，咱们慢慢揭开它的面纱，看看它到底能干些啥，尤其是怎么在性能测试和索引优化上帮我们一把。

一、啥是隐藏索引？

首先，咱们得搞清楚，啥叫隐藏索引。简单来说，隐藏索引就是一个对优化器“隐形”的索引。默认情况下，MySQL优化器在执行查询时会考虑所有可见的索引，选择它认为最优的那个来加速查询。但是，如果你把一个索引设置为隐藏，优化器就直接忽略它，就像它不存在一样。

注意，这个索引仍然存在于数据库中，数据也会持续更新，只是优化器假装没看见而已。

二、为啥要有隐藏索引？

你可能要问了，搞这么个东西出来干啥？直接删除不就行了？

这就涉及到数据库管理的艺术了。删除索引是个危险的操作，尤其是在生产环境。万一你删错了，或者删掉的索引其实很重要，那可就惨了，轻则查询变慢，重则系统崩溃。

隐藏索引就提供了一种更安全的方式来评估索引的影响。你可以先隐藏一个索引，观察一段时间，看看对系统性能有没有影响。如果没有影响，或者影响很小，那你就可以放心地把它删掉了。如果影响很大，那就赶紧把它恢复过来，就当啥也没发生。

隐藏索引还有其他一些用途，比如：

性能测试： 在不修改生产环境的情况下，测试删除某个索引对查询性能的影响。
索引优化： 评估新索引的效果，或者验证索引的必要性。
灰度发布： 在新版本的发布过程中，逐步启用或禁用某些索引，降低风险。

三、怎么玩转隐藏索引？

好了，理论知识讲完了，咱们来点实际的，看看怎么操作隐藏索引。

1. 创建隐藏索引

在MySQL 8.0中，创建索引时可以指定 INVISIBLE 属性，使其成为隐藏索引。

CREATE TABLE t1 (
    id INT PRIMARY KEY,
    c1 INT,
    c2 INT,
    INDEX idx_c1 (c1),  -- 默认可见索引
    INDEX idx_c2 (c2) INVISIBLE -- 隐藏索引
);

上面的例子中，idx_c1 是一个普通的可见索引，而 idx_c2 则是一个隐藏索引。

2. 修改索引可见性

如果你已经创建了一个索引，想把它变成隐藏的，或者把一个隐藏索引变成可见的，可以使用 ALTER TABLE 语句。

隐藏索引：

ALTER TABLE t1 ALTER INDEX idx_c1 INVISIBLE;

显示索引：

ALTER TABLE t1 ALTER INDEX idx_c1 VISIBLE;

3. 查看索引信息

要查看一个索引是否是隐藏的，可以使用 SHOW INDEX 语句。

SHOW INDEX FROM t1;

在结果集中，你会看到一个 Visible 列，它的值是 YES 或 NO，分别表示索引是可见的还是隐藏的。

或者你也可以查询 information_schema.statistics 表：

SELECT INDEX_NAME, VISIBLE FROM information_schema.STATISTICS WHERE TABLE_NAME = 't1';

4. 使用优化器开关强制使用隐藏索引

默认情况下，优化器会忽略隐藏索引。但是，如果你想强制优化器使用隐藏索引，可以使用优化器开关 optimizer_switch。

SET optimizer_switch = 'use_invisible_indexes=on';

设置了这个开关后，优化器就会把隐藏索引也纳入考虑范围。注意，这个开关是会话级别的，只对当前会话有效。

四、实战演练：性能测试和索引优化

现在，咱们来模拟一个实际场景，看看如何使用隐藏索引进行性能测试和索引优化。

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

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    INDEX idx_user_id (user_id),
    INDEX idx_order_date (order_date)
);

-- 插入一些测试数据
INSERT INTO orders (order_id, user_id, order_date, total_amount)
VALUES
(1, 101, '2023-01-01', 100.00),
(2, 102, '2023-01-02', 200.00),
(3, 101, '2023-01-03', 150.00),
(4, 103, '2023-01-04', 300.00),
(5, 102, '2023-01-05', 250.00),
(6, 101, '2023-01-06', 120.00),
(7, 104, '2023-01-07', 400.00),
(8, 103, '2023-01-08', 350.00),
(9, 102, '2023-01-09', 280.00),
(10, 105, '2023-01-10', 500.00);

现在我们有一个查询，需要根据用户ID和订单日期来查询订单信息。

SELECT * FROM orders WHERE user_id = 101 AND order_date BETWEEN '2023-01-01' AND '2023-01-05';

目前，我们有两个独立的索引：idx_user_id 和 idx_order_date。MySQL优化器可能会选择使用其中一个索引，或者进行全表扫描。

场景一：测试删除索引的影响

假设我们怀疑 idx_order_date 索引对这个查询没有帮助，想测试一下删除它会有什么影响。

隐藏索引：

ALTER TABLE orders ALTER INDEX idx_order_date INVISIBLE;

执行查询，并记录执行时间：
```
EXPLAIN SELECT * FROM orders WHERE user_id = 101 AND order_date BETWEEN '2023-01-01' AND '2023-01-05';
```
记录EXPLAIN输出中的 rows 列，这个值代表了MySQL估计需要扫描的行数。同时，使用 BENCHMARK() 函数或者其他性能测试工具来测量查询的执行时间。

恢复索引：

ALTER TABLE orders ALTER INDEX idx_order_date VISIBLE;

再次执行查询，并记录执行时间：

EXPLAIN SELECT * FROM orders WHERE user_id = 101 AND order_date BETWEEN '2023-01-01' AND '2023-01-05';

对比结果：

比较两次查询的执行时间和扫描行数。如果删除 idx_order_date 索引后，查询速度没有明显变化，或者扫描行数反而减少了，那说明这个索引确实没有太大的作用，可以考虑删除。反之，如果查询速度明显变慢，或者扫描行数增加了，那说明这个索引还是有用的，不能轻易删除。

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

假设我们想创建一个组合索引 idx_user_id_order_date，看看它能不能提高查询性能。

创建隐藏的组合索引：

ALTER TABLE orders ADD INDEX idx_user_id_order_date (user_id, order_date) INVISIBLE;

强制使用隐藏索引，并执行查询：

SET optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN SELECT * FROM orders WHERE user_id = 101 AND order_date BETWEEN '2023-01-01' AND '2023-01-05';

记录 EXPLAIN 输出中的 rows 列和查询的执行时间。

关闭强制使用隐藏索引，并执行查询：

SET optimizer_switch = 'use_invisible_indexes=off';
EXPLAIN SELECT * FROM orders WHERE user_id = 101 AND order_date BETWEEN '2023-01-01' AND '2023-01-05';

记录 EXPLAIN 输出中的 rows 列和查询的执行时间。

对比结果：

比较两次查询的执行时间和扫描行数。如果强制使用组合索引后，查询速度明显加快，或者扫描行数减少了，那说明这个组合索引是有用的，可以把它设置为可见的。
```
ALTER TABLE orders ALTER INDEX idx_user_id_order_date VISIBLE;
```

五、注意事项

版本限制： 隐藏索引是 MySQL 8.0 的新特性，之前的版本不支持。
权限： 修改索引的可见性需要 ALTER 权限。
长期运行的查询： 如果你在隐藏一个索引后，发现有一些长期运行的查询变得很慢，那说明这个索引可能对这些查询很重要，需要谨慎处理。
数据量： 隐藏索引对性能的影响可能取决于数据量的大小。在数据量较小的测试环境中，可能很难看出明显的差异。
统计信息： 优化器依赖于统计信息来选择最优的执行计划。在修改索引的可见性后，最好更新一下统计信息，以确保优化器能够做出正确的决策。可以使用 ANALYZE TABLE 语句来更新统计信息。

六、总结

隐藏索引是 MySQL 8.0 中一个非常有用的工具，它可以帮助我们更安全地进行性能测试和索引优化。通过隐藏和显示索引，我们可以评估索引的影响，验证索引的必要性，并在不修改生产环境的情况下，优化数据库的性能。

当然，隐藏索引并不是万能的。在使用它的时候，需要结合实际情况，谨慎评估，并注意相关的注意事项。

希望今天的讲座能对你有所帮助。如果你还有什么问题，欢迎随时提问。

谢谢大家！

发表回复 取消回复

发表回复取消回复