MySQL新版本特性之：`MySQL 8.0`的`Invisible Indexes`：其在`索引管理`中的应用。

MySQL 8.0 Invisible Indexes：索引管理的利器

大家好，今天我们来聊聊MySQL 8.0中一个非常有用的特性：Invisible Indexes (隐形索引)。这个特性在索引管理方面提供了极大的灵活性，允许我们在不实际删除索引的情况下，评估其对查询性能的影响，或者在某些场景下临时禁用索引。

什么是Invisible Indexes？

简单来说，Invisible Indexes就是对优化器“不可见”的索引。当一个索引被设置为invisible时，优化器在生成执行计划时将不会考虑使用这个索引。但这并不意味着索引被物理删除，它仍然存在于数据库中，并且会持续维护（更新、插入、删除等）。

这与DROP INDEX删除索引完全不同，也与OPTIMIZE TABLE无关。Invisible Indexes提供了一种非侵入式的索引管理方式，允许我们在不影响现有查询的情况下进行索引实验。

Invisible Indexes 的语法

设置索引可见性使用 ALTER TABLE 语句，并使用 INVISIBLE 或 VISIBLE 关键字。

• 将索引设置为 invisible：

  ALTER TABLE table_name ALTER INDEX index_name INVISIBLE;

• 将索引设置为 visible：

  ALTER TABLE table_name ALTER INDEX index_name VISIBLE;

Invisible Indexes 的应用场景

Invisible Indexes 在索引管理中有着广泛的应用，以下是一些常见的场景：

1. 测试新索引对查询性能的影响： 在正式启用新索引之前，可以将其设置为 invisible，然后通过optimizer_switch参数强制优化器使用它来评估查询性能。如果性能提升不明显甚至降低，可以继续优化索引设计，而无需担心影响现有生产环境。

2. 评估删除现有索引的影响： 在删除一个使用率低的索引之前，可以将其设置为 invisible，观察一段时间内查询性能的变化。如果性能没有明显下降，说明该索引确实可以安全删除。

3. 临时禁用索引： 在某些特殊情况下，例如批量数据加载或特定的维护操作，禁用某些索引可以提高性能。可以使用 invisible indexes 快速禁用索引，并在操作完成后重新启用。

4. 在线索引修改： 在某些情况下，可能需要在线修改索引，比如修改数据类型或索引类型。可以先创建一个新的 invisible 索引，然后在验证其性能之后再切换到可见状态，最后删除旧索引。

5. A/B测试索引： 可以同时创建两个或多个针对相同列的不同类型的索引，并将其中一个设置为 visible，其他的设置为 invisible。然后通过调整 optimizer_switch 参数，观察不同索引对特定查询的性能影响，选择最佳的索引策略。

Invisible Indexes 的使用示例

示例 1：测试新索引的性能

假设我们有一个 orders 表，包含 customer_id 和 order_date 两列。我们怀疑在 (customer_id, order_date) 上创建一个联合索引可以提高某些查询的性能。

1. 创建 invisible 索引：

   ALTER TABLE orders ADD INDEX idx_customer_order_date (customer_id, order_date) INVISIBLE;

2. 强制优化器使用该索引进行查询： 通过 optimizer_switch 参数，我们可以控制优化器的行为。 我们需要设置 use_invisible_indexes=on，并使用 EXPLAIN 语句分析查询计划。

   SET optimizer_switch = 'use_invisible_indexes=on';
   
   EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date BETWEEN '2023-01-01' AND '2023-01-31';

   如果 EXPLAIN 输出显示查询使用了 idx_customer_order_date 索引，那么我们可以继续评估其性能。

3. 评估性能： 使用 SQL_NO_CACHE 选项执行查询，并记录执行时间。

   SELECT SQL_NO_CACHE * FROM orders WHERE customer_id = 123 AND order_date BETWEEN '2023-01-01' AND '2023-01-31';

   重复执行多次，取平均执行时间。 然后，将 idx_customer_order_date 索引设置为 visible，重复上述步骤，比较执行时间。

4. 最终决定： 如果新索引显著提高了查询性能，则保持其可见状态；否则，可以将其删除。

示例 2：评估删除现有索引的影响

假设我们有一个 users 表，其中有一个 email 列上的索引 idx_email，我们怀疑这个索引的使用率很低。

1. 将索引设置为 invisible：

   ALTER TABLE users ALTER INDEX idx_email INVISIBLE;

2. 观察一段时间内查询性能的变化： 监控数据库的查询性能，特别是涉及到 email 列的查询。可以使用 MySQL Performance Schema 或第三方监控工具来收集查询性能数据。

3. 分析结果： 如果查询性能没有明显下降，说明 idx_email 索引确实可以安全删除。

4. 最终决定： 如果确定索引可以删除，则执行 DROP INDEX idx_email ON users;。

示例 3：批量数据加载

在批量数据加载到表中时，索引的维护会显著降低加载速度。 可以通过禁用索引来提高加载速度，并在加载完成后重新启用索引。

1. 禁用索引：

   ALTER TABLE table_name ALTER INDEX index_name INVISIBLE; -- 对需要禁用的索引重复执行此操作

2. 执行批量数据加载： 使用 LOAD DATA INFILE 或其他批量加载工具。

3. 启用索引：

   ALTER TABLE table_name ALTER INDEX index_name VISIBLE; -- 对禁用的索引重复执行此操作

示例 4：在线索引修改

假设我们需要修改 products 表中 product_name 列的索引类型，从 BTREE 修改为 FULLTEXT。

1. 创建新的 invisible FULLTEXT 索引：

   ALTER TABLE products ADD FULLTEXT INDEX idx_product_name_ft (product_name) INVISIBLE;

2. 测试新的索引： 设置 optimizer_switch 参数，强制优化器使用新的索引，并评估其性能。

   SET optimizer_switch = 'use_invisible_indexes=on';
   EXPLAIN SELECT * FROM products WHERE MATCH (product_name) AGAINST ('keyword');
   SELECT SQL_NO_CACHE * FROM products WHERE MATCH (product_name) AGAINST ('keyword');

3. 切换索引： 如果新的索引性能良好，则将其设置为 visible，并将旧的索引设置为 invisible。

   ALTER TABLE products ALTER INDEX idx_product_name_ft VISIBLE;
   ALTER TABLE products ALTER INDEX idx_product_name INVISIBLE; -- 假设原索引名为 idx_product_name

4. 删除旧索引： 在确认新的索引完全取代了旧索引的功能后，可以删除旧索引。

   DROP INDEX idx_product_name ON products;

Invisible Indexes 的限制和注意事项

• SHOW INDEXES 仍然会显示 invisible 索引： 需要注意，SHOW INDEXES 命令会显示所有索引，包括 invisible 索引。 可以通过查看 Visible 列来区分 visible 和 invisible 索引。

  SHOW INDEXES FROM table_name;

  输出示例：

  Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
  products	0	PRIMARY	1	id	A	1000	NULL	NULL		BTREE			YES	NULL
  products	1	idx_product_name_ft	1	product_name	NULL	NULL	NULL	NULL		FULLTEXT			NO	NULL

• optimizer_switch 参数的影响： use_invisible_indexes 参数控制优化器是否可以使用 invisible 索引。 默认情况下，该参数为 off。 在测试或评估 invisible 索引时，需要将其设置为 on。

• 存储引擎的限制： 并非所有存储引擎都支持 Invisible Indexes。 例如，MySQL 5.7 中使用的 MyISAM 存储引擎不支持此特性。 InnoDB 存储引擎支持 Invisible Indexes。

• 权限： 需要 ALTER 权限才能修改索引的可见性。

• 主键索引: 主键索引不能设置为 invisible。

• 唯一索引: 唯一索引可以设置为 invisible，但需要谨慎操作，因为这可能会导致数据完整性问题。如果唯一索引被设置为 invisible，并且插入了违反唯一约束的数据，MySQL不会报错，直到索引被设置为visible并且优化器开始使用它时，才会发现问题。

optimizer_switch 参数详解

optimizer_switch 是一个全局变量，用于控制优化器的行为。 与 Invisible Indexes 相关的参数是 use_invisible_indexes。

• use_invisible_indexes=off (默认值): 优化器不会考虑使用 invisible 索引。
• use_invisible_indexes=on: 优化器可以使用 invisible 索引。

可以通过以下方式修改 optimizer_switch 参数：

• 全局设置： 影响所有会话。

  SET GLOBAL optimizer_switch = 'use_invisible_indexes=on';

• 会话设置： 仅影响当前会话。

  SET SESSION optimizer_switch = 'use_invisible_indexes=on';

建议使用会话设置，避免影响其他会话。

使用案例：优化查询性能

假设我们有一个 sales 表，其中包含 product_id, sales_date 和 sales_amount 列。我们经常需要查询特定日期范围内，特定产品的销售额。

CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    sales_date DATE NOT NULL,
    sales_amount DECIMAL(10, 2) NOT NULL,
    INDEX idx_product_id (product_id),
    INDEX idx_sales_date (sales_date)
);

现在我们执行以下查询：

SELECT SUM(sales_amount)
FROM sales
WHERE product_id = 123
AND sales_date BETWEEN '2023-01-01' AND '2023-01-31';

MySQL可能会选择使用 idx_product_id 或 idx_sales_date 索引，但单独使用任何一个索引都无法有效地过滤数据。我们可以创建一个联合索引来优化这个查询。

1. 创建 invisible 联合索引：

   ALTER TABLE sales ADD INDEX idx_product_date (product_id, sales_date) INVISIBLE;

2. 强制优化器使用该索引：

   SET optimizer_switch = 'use_invisible_indexes=on';
   EXPLAIN SELECT SUM(sales_amount)
           FROM sales
           WHERE product_id = 123
           AND sales_date BETWEEN '2023-01-01' AND '2023-01-31';

   如果 EXPLAIN 输出显示使用了 idx_product_date 索引，则继续下一步。

3. 评估性能： 比较在启用和禁用 idx_product_date 索引的情况下，查询的执行时间。

   -- 禁用 idx_product_date 索引
   SET optimizer_switch = 'use_invisible_indexes=off';
   SELECT SQL_NO_CACHE SUM(sales_amount)
           FROM sales
           WHERE product_id = 123
           AND sales_date BETWEEN '2023-01-01' AND '2023-01-31';
   
   -- 启用 idx_product_date 索引
   SET optimizer_switch = 'use_invisible_indexes=on';
   SELECT SQL_NO_CACHE SUM(sales_amount)
           FROM sales
           WHERE product_id = 123
           AND sales_date BETWEEN '2023-01-01' AND '2023-01-31';

   如果启用 idx_product_date 索引后，查询性能显著提高，则将其设置为 visible。

4. 设置为 visible：

   ALTER TABLE sales ALTER INDEX idx_product_date VISIBLE;

通过这个案例，我们展示了如何使用 Invisible Indexes 来测试和优化查询性能，而无需在生产环境中冒风险。

使用案例：处理重复索引

假设 employees 表中，first_name和last_name列经常一起被用于查询。为了优化这类查询，最初创建了一个联合索引idx_name，包含first_name和last_name。之后，因为某些原因，又分别在first_name和last_name列上创建了单独的索引idx_first_name和idx_last_name。现在，我们怀疑idx_name索引可能不再必要，因为优化器可能会选择使用idx_first_name和idx_last_name的组合来完成查询。

1. 将联合索引设置为 invisible:

   ALTER TABLE employees ALTER INDEX idx_name INVISIBLE;

2. 监控查询性能: 监控涉及到first_name和last_name的查询的性能。观察是否有任何显著的性能下降。例如，以下查询：

   SELECT * FROM employees WHERE first_name = 'John' AND last_name = 'Doe';

   使用性能监控工具或者慢查询日志来收集查询性能数据。

3. 分析结果: 如果在idx_name设置为invisible后，查询性能没有显著下降，说明优化器能够有效地使用idx_first_name和idx_last_name来完成查询。这表明idx_name索引可能是冗余的。

4. 删除联合索引: 在确认idx_name索引是冗余的之后，可以将其删除：

   DROP INDEX idx_name ON employees;

通过这个案例，我们展示了如何使用Invisible Indexes来识别和删除冗余索引，从而减少索引维护的开销并可能提高写入操作的性能。

Invisible Indexes 为索引管理带来便利

Invisible Indexes 是 MySQL 8.0 中一个非常有用的特性，它为索引管理带来了极大的灵活性。通过 Invisible Indexes，我们可以安全地测试新索引、评估删除现有索引的影响、临时禁用索引，以及进行在线索引修改。掌握 Invisible Indexes 的使用方法，可以帮助我们更好地管理 MySQL 数据库的索引，从而提高查询性能并降低维护成本。

关键特性和应用场景总结

Invisible Indexes 允许在不删除索引的情况下，使其对优化器不可见，从而实现灵活的索引管理。 这在测试新索引，评估删除旧索引，以及进行在线索引修改时非常有用，确保数据库的稳定性和性能。