发布于 由admin

MySQL高阶讲座之：`MySQL`的`Invisible Indexes`：其在`Online DDL`和索引验证中的作用。

各位老铁，大家好！我是你们的老朋友，今天咱们来聊聊MySQL里一个有点儿低调，但关键时刻能救命的特性：Invisible Indexes（不可见索引）。这玩意儿在Online DDL和索引验证里，那可是个大宝贝。

一、啥是Invisible Indexes？

简单说，Invisible Indexes就是MySQL里你可以创建一种“隐身”的索引。它真实存在，占据磁盘空间，也会随着数据的变化而更新，但优化器默认情况下不会使用它。就像一个武林高手隐姓埋名，默默练功，不到关键时刻不出手。

想象一下，你有一张表，上面已经有了一堆索引。你想加个新的索引，但又怕这个索引一上线就坑了优化器，导致慢查询。这时候，Invisible Indexes就派上用场了。你可以先创建一个不可见索引，观察一段时间，确定没问题了再让它“现身”。

二、怎么创建和管理Invisible Indexes？

创建Invisible Indexes非常简单，就是在CREATE INDEX语句里加上INVISIBLE关键字：

CREATE INDEX idx_order_date ON orders (order_date) INVISIBLE;

让它现身，就用VISIBLE关键字：

ALTER TABLE orders ALTER INDEX idx_order_date VISIBLE;

让它再次隐身：

ALTER TABLE orders ALTER INDEX idx_order_date INVISIBLE;

查看索引是否可见，可以用SHOW INDEXES或者查询information_schema.STATISTICS表：

SHOW INDEXES FROM orders;

-- 或者
SELECT INDEX_NAME, VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'orders'
GROUP BY INDEX_NAME, VISIBLE;

VISIBLE列会告诉你这个索引是否可见。

三、Invisible Indexes在Online DDL中的作用

Online DDL（在线数据定义语言）是指在数据库运行过程中，对表结构进行修改，而不会长时间阻塞数据库的读写操作。MySQL 5.6开始引入了Online DDL，它允许你在修改表结构的同时，继续提供服务。

Invisible Indexes在Online DDL中扮演着重要的角色，特别是当你要添加一个可能会影响性能的索引时。

  • 风险控制： 在生产环境直接CREATE INDEX可能会导致优化器选择新的索引，从而影响查询性能。使用Invisible Indexes，你可以先创建一个不可见索引，让它在后台慢慢构建，而不会立即影响查询。

  • 性能测试： 索引构建完成后，你可以先让部分用户或者特定的业务场景使用这个索引，观察其性能表现。如果效果不好，直接让索引保持不可见状态，避免影响全局。

  • 平滑切换： 如果索引效果良好，你可以逐步让索引可见，观察性能变化。如果发现问题，可以立即让索引再次不可见，回退到之前的状态。

一个完整的Online DDL流程示例：

  1. 创建不可见索引：
CREATE INDEX idx_customer_id ON orders (customer_id) INVISIBLE;

这个操作会在后台构建索引，不会阻塞读写操作。

  1. 观察索引构建进度：

可以使用SHOW PROCESSLIST或者查询information_schema.PROCESSLIST表来查看索引构建的进度。

  1. 性能测试（可选）：

可以通过设置optimizer_switch参数，强制优化器使用指定的索引：

SET SESSION optimizer_switch='use_index_extensions=on';

-- 强制使用idx_customer_id索引
SELECT /*+ INDEX(orders idx_customer_id) */ * FROM orders WHERE customer_id = 123;

-- 恢复默认设置
SET SESSION optimizer_switch='use_index_extensions=off';

或者，可以设置optimizer_hints，达到同样的效果。

  1. 逐步让索引可见：
-- 先让部分用户使用
SET SESSION optimizer_switch='use_index_extensions=on';
ALTER TABLE orders ALTER INDEX idx_customer_id VISIBLE;
SET SESSION optimizer_switch='use_index_extensions=off';

-- 观察一段时间，如果没问题，再全局生效
ALTER TABLE orders ALTER INDEX idx_customer_id VISIBLE;
  1. 监控性能：

使用各种监控工具，如Percona Monitoring and Management (PMM)，或者MySQL自带的Performance Schema，监控数据库的性能指标，例如查询响应时间、CPU使用率、IO负载等。

四、Invisible Indexes在索引验证中的作用

有时候，你会怀疑某个索引是否真的有用。也许它很久之前创建的，但现在已经不被使用了。删除索引是个危险的操作，万一删错了，可能会导致严重的性能问题。Invisible Indexes提供了一种安全的验证方法。

  • 验证索引有效性： 你可以先让索引不可见，观察一段时间，看看是否对查询性能产生影响。如果没有影响，说明这个索引可能已经没用了，可以安全地删除。

  • 验证新索引效果： 在删除旧索引之前，你可以先创建一个新的不可见索引，让它在后台构建。然后，让旧索引不可见，观察一段时间，看看新索引是否能够替代旧索引。

索引验证示例：

  1. 让可疑索引不可见：
ALTER TABLE orders ALTER INDEX idx_old_index INVISIBLE;
  1. 观察性能：

监控数据库的性能指标，看看是否受到影响。

  1. 分析查询计划：

使用EXPLAIN语句分析查询计划，看看是否仍然有查询使用了这个索引。如果EXPLAIN结果中没有出现idx_old_index，说明这个索引确实没有被使用。

  1. 删除索引（如果确认没用）：
DROP INDEX idx_old_index ON orders;

五、Invisible Indexes的注意事项

  • 存储空间： Invisible Indexes仍然会占用存储空间，并且会随着数据的变化而更新。因此，不要创建过多的Invisible Indexes，以免浪费存储资源。
  • 性能开销： 虽然优化器默认情况下不会使用Invisible Indexes，但它们仍然会带来一定的性能开销，因为数据库需要维护这些索引。
  • 复制： Invisible Indexes会复制到从库。因此，在主库上创建的Invisible Indexes，也会在从库上创建。
  • 参数设置： 可以通过设置optimizer_switch参数，强制优化器使用Invisible Indexes。这对于测试和验证非常有用。
  • 版本限制： Invisible Indexes是MySQL 5.7.20及以上版本才支持的特性。

六、实战案例：优化慢查询

假设你发现一个查询很慢，经过分析，发现是优化器选择了错误的索引。你可以尝试以下步骤：

  1. 分析查询计划：
EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

查看EXPLAIN结果，看看优化器选择了哪个索引。

  1. 让优化器选择的索引不可见：
ALTER TABLE orders ALTER INDEX idx_wrong_index INVISIBLE;
  1. 再次分析查询计划：
EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

看看优化器是否选择了正确的索引。

  1. 如果优化器仍然没有选择正确的索引，可以创建一个新的索引，并让它可见：
CREATE INDEX idx_order_date_customer_id ON orders (order_date, customer_id);
  1. 如果新的索引效果良好，可以删除旧的索引：
DROP INDEX idx_wrong_index ON orders;

七、总结

Invisible Indexes是MySQL里一个非常实用的特性，它可以帮助你安全地管理索引，降低Online DDL的风险，验证索引的有效性，以及优化慢查询。掌握Invisible Indexes的使用方法，可以让你在数据库管理方面更加得心应手。

一些常用的SQL语句汇总：

SQL语句 描述
CREATE INDEX idx_name ON table_name (column_name) INVISIBLE; 创建一个不可见索引。
ALTER TABLE table_name ALTER INDEX idx_name VISIBLE; 让索引可见。
ALTER TABLE table_name ALTER INDEX idx_name INVISIBLE; 让索引不可见。
SHOW INDEXES FROM table_name; 查看表的所有索引，包括是否可见。
SELECT INDEX_NAME, VISIBLE FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'table_name' GROUP BY INDEX_NAME, VISIBLE; 更详细地查看索引的可见性。
SET SESSION optimizer_switch='use_index_extensions=on'; 开启use_index_extensions，允许优化器使用Invisible Indexes（仅当前会话有效）。
SET SESSION optimizer_switch='use_index_extensions=off'; 关闭use_index_extensions，恢复默认行为，优化器不使用Invisible Indexes（仅当前会话有效）。
EXPLAIN SELECT ...; 分析查询计划，查看优化器选择了哪个索引。
DROP INDEX idx_name ON table_name; 删除索引。

好了，今天的讲座就到这里。希望大家有所收获！记住，Invisible Indexes就像一把隐藏的利剑，用好了能让你在数据库的世界里披荆斩棘，所向披靡！下次有机会再跟大家分享其他MySQL的实用技巧。 拜拜！

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注