MySQL 8.0 的隐藏索引：索引管理的利器，还是性能陷阱？
<p>本文已收录在Github，<strong>关注我，紧跟本系列专栏文章，咱们下篇再续！</strong></p><ul>
<li>🚀 魔都架构师 | 全网30W技术追随者</li>
<li>🔧 大厂分布式系统/数据中台实战专家</li>
<li>🏆 主导交易系统百万级流量调优 & 车联网平台架构</li>
<li>🧠 AIGC应用开发先行者 | 区块链落地实践者</li>
<li>🌍 以技术驱动创新，我们的征途是改变世界！</li>
<li>👉 实战干货：编程严选网</li>
</ul>
<h2 id="0-前言">0 前言</h2>
<p>MySQL8.0开始支持隐藏索引，不可见索引。它允许快速启用/禁用MySQL Optimizer使用的索引。</p>
<p>隐藏索引不会被优化器使用，但仍需维护</p>
<h2 id="1-应用场景">1 应用场景</h2>
<ul>
<li>软删除</li>
<li>灰度发布，测试新索引 ：在不影响现有查询性能的情况下，测试新创建的索引是否有效</li>
<li><strong>维护索引</strong> ：在更新或重建索引时，可以先将其设置为不可见，避免影响在线查询</li>
<li><strong>临时禁用无用索引</strong> ：如果某个索引不再被频繁使用，可以将其设置为不可见，减少不必要的开销</li>
</ul>
<h2 id="2-啥用">2 啥用？</h2>
<p>若想删除一个索引，又想事先知道效果。可使它对优化程序不可见。这是一个快速的元数据更改，使索引不可见。一旦确定没有性能下降，就可真正去删除索引。</p>
<p>关键：隐藏索引不能供优化器使用，但它仍存在，并通过写入操作保持最新。即便尝试“FORCE INDEX”，优化器也不会用它，虽然我认为我们应该能在某种程度强制它。可能会有这样情况：</p>
<p>可创建一个新的隐形索引，但若想测试它，须使它可见。即所有对应用程序有即时影响的查询都将能用它。若目的只是想测试它，我不认为这是最好方法，不是所有人的服务器都有相同的数据大小和真实数据。强制隐藏索引这时可能会很有用。</p>
<p>你有许多索引，但不确定哪个未使用。可将一个索引更改为不可见，以查看是否存在任何性能下降。若是，你可立即更改。</p>
<p>可能有个特殊情况：只有一个查询可用该索引。此时，隐藏索引可能是很好解决方案。</p>
<h2 id="3-创建">3 创建</h2>
<p>有两个选项：</p>
<h3 id="31-创建一个具有隐藏索引的表">3.1 创建一个具有隐藏索引的表</h3>
<pre><code class="language-sql">CREATE TABLE t1 (
i INT,
j INT,
k INT,
INDEX i_idx (i) INVISIBLE
) ENGINE = InnoDB;
CREATE INDEX j_idx ON t1 (j) INVISIBLE;
</code></pre>
<h3 id="32-alter-table并将索引更改为隐藏">3.2 alter table并将索引更改为隐藏</h3>
<pre><code class="language-sql">ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;
</code></pre>
<h2 id="4-使用">4 使用</h2>
<p>如果我们现在要删除索引，我们可以将其更改为隐藏。 但是使用“FORCE / USE INDEX”的查询怎么样？ 他们是否会抛出一个错误？ 如果强制不存在的索引，你会收到错误。 你不会看到隐藏索引的错误。 优化器不会使用它，但知道它存在。</p>
<pre><code class="language-bash">show create table t1 G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`i` int(11) DEFAULT NULL,
`j` int(11) DEFAULT NULL,
`k` int(11) DEFAULT NULL,
KEY `i_idx` (`i`),
KEY `idx_1` (`i`,`j`,`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)
mysql> explain select * from t1 force index(idx_1) where i=1 and j=4;
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
|1 | SIMPLE | t1 | NULL | ref| idx_1 | idx_1 | 10 | const,const | 2 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
mysql> alter table t1 alter index idx_1 invisible;
Query OK, 0 rows affected (0.01 sec)
Records: 0Duplicates: 0Warnings: 0mysql> explain select * from t1 force index(idx_1) where i=1 and j=4;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|1 | SIMPLE | t1 | NULL | ALL| NULL | NULL | NULL | NULL | 16 | 6.25 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.01 sec)
mysql> explain select * from t1 where i=1 and j=4;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
|1 | SIMPLE | t1 | NULL | ref| i_idx | i_idx | 5 | const | 2 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
</code></pre>
<p>若用带有隐藏索引的“FORCE INDEX”，MySQL会执行全表扫描。 MySQL不会抛任何错误，因为索引存在，但不可见。 即使有另一个可用的索引，它也将执行全表扫描。</p>
<p>大型表上，这可能导致严重性能问题。 即使MySQL在查询执行期间不抛任何错误，它也应在错误日志中记录一个警告。</p>
<blockquote>
<p>本文由博客一文多发平台 OpenWrite 发布！</p>
</blockquote><br><br>
来源：https://www.cnblogs.com/JavaEdge/p/18972902
頁:
[1]