掌握 MySQL 中隐形索引的艺术

欢迎深入了解 MySQL 的一项出色功能——隐形索引。该功能随 MySQL 8.0 一起发布，增加了每个数据库管理员都梦想的灵活性：能够动态​​切换索引的可见性而不丢失它！让我们深入研究一下这个功能的工作原理以及为什么它会改变游戏规则。

什么是隐形索引？

不可见索引是 MySQL 8.0 套件中的一个瑰宝，它允许您控制索引对优化器的可见性。本质上，您可以决定在查询优化期间是否应考虑索引，而无需永久添加或删除索引。

以下是如何使索引不可见或可见：

-- 使索引不可见
ALTER TABLE  ALTER INDEX  不可见；

-- 使索引可见
ALTER TABLE  ALTER INDEX  可见；

为什么要使用隐形索引？

假设您正在调整数据库的性能或测试在没有某些索引的情况下查询的执行情况。传统上，您可能会删除一个索引进行测试，然后重新添加它，这个过程不仅耗时，而且在实时环境中也存在一定风险。不可见索引通过允许保留索引但简单地告诉优化器忽略它来解决这个问题。

这个功能不仅仅是为了方便；这是关于性能的。使索引不可见几乎是瞬时的，比删除和重新添加索引快得多。

真实世界验证：案例研究

为了了解此功能的影响，我使用了 Gitpod Enterprise 工作区，利用强大且短暂的环境来避免设置和安装的麻烦。这是设置的概览：

• 机器：Gitpod Enterprise XXLarge 工作区
• 操作系统：Ubuntu 22.04.4 LTS（Jammy Jellyfish）
• 容器化：Docker 版本 26.0.1
• MySQL 版本：官方 MySQL 8.0 Docker 镜像

搭建舞台

我首先创建一个书籍表并用大量数据集填充它：

创建表格书籍（
  id INT 主键自动递增，
  名称 VARCHAR(255) NOT NULL
）；

-- 新增1亿条记录
设置会话cte_max_recursion_深度= 100000000；
插入书籍（名称）
与递归 cte (n) AS (
  选择1
  联合所有
  从 cte 中选择 n + 1，其中 n 



<blockquote>
输出： 查询正常，100000000 行受影响（7 分 40.58 秒）
            记录：100000000 重复：0 警告：0
</blockquote>

<h3>
  
  
  尝试索引
</h3>

<p>接下来，我向名称列添加了索引，并执行操作来切换其可见性：<br></p>

<pre class="brush:php;toolbar:false">-- 添加索引
ALTER TABLE books 添加索引 idx_name(name);

输出： 查询正常，0 行受影响（3 分 54.75 秒）
记录：0 重复：0 警告：0

--使索引不可见
ALTER TABLE books ALTER INDEX idx_name INVISIBLE;

输出： 查询正常，0 行受影响（0.01 秒）
记录：0 重复：0 警告：0

--使索引再次可见
ALTER TABLE books ALTER INDEX idx_name VISIBLE;

输出： 查询正常，0 行受影响（0.00 秒）
记录：0 重复：0 警告：0

--删除索引
删除书籍上的 idx_name 索引；

输出： 查询正常，0 行受影响（0.02 秒）
记录：0 重复：0 警告：0

观察结果

结果很有启发。使索引不可见和再次可见几乎是瞬时的，这与添加或删除索引所需的时间形成鲜明对比。这强调了使用隐形索引进行性能调优的效率。

结论

隐形索引是 MySQL 工具包中的一项重要功能，提供灵活性和性能优势。它们在索引可能会干扰性能的场景中特别有用，或者在测试索引对查询执行的影响而无需删除和重新添加它们的开销时特别有用。

对于详细的 MySQL 操作，MySQL 在线 DDL 操作官方文档提供了全面的指导，包括添加全文索引或空间索引等不允许并发 DML 操作的场景。

隐形索引不仅仅是一个功能；它们是数据库优化的战略优势——对于任何参与数据库管理或优化的人来说绝对是一个值得掌握的工具！