MySQL 优化实战：为何 DELETE + IN 子查询性能不佳，而 JOIN 却能高效利用索引？
<h2>问题背景：一次看似简单的数据清理</h2><p>在日常功能开发过程在中，我们经常需要根据某些条件清理特定数据。某天，我需要在 <code>tbl_doa_activityspecial</code> 表中删除与另一组条件匹配的记录。直觉上，我写下了这样的 SQL：</p>
<div class="cnblogs_Highlighter">
<pre class="brush:sql;gutter:true;">DELETE FROM tbl_doa_activityspecial
WHERE ActSetId IN (SELECT DISTINCT ActSetId FROM ...);
</pre>
</div>
<p> </p>
<p>这个查询逻辑清晰，但执行时却发现性能极差。使用 <code>EXPLAIN</code> 分析执行计划后，发现了一个令人困惑的现象：MySQL 优化器没有使用 <code>tbl_doa_activityspecial_ActSetId_IDX</code> 这个明显应该使用的索引，而是显示 <code>possible_keys: null</code>。</p>
<h2>探寻根源：MySQL 优化器的"保守策略"</h2>
<p>经过深入分析，我发现这个问题背后有几个关键原因：</p>
<h3>1. 子查询物化导致的性能陷阱</h3>
<p>当 MySQL 遇到 <code>IN (子查询)</code> 结构时，它可能会选择将子查询的结果物化（Materialize）到一个临时表中，然后再执行主查询。这个过程包括：</p>
<ul>
<li>执行子查询并将结果写入临时表</li>
<li>可能对临时表进行去重（如使用 DISTINCT 时）</li>
<li>最后执行基于临时表的查询</li>
</ul>
<p>物化过程破坏了索引使用的连续性，优化器难以将外部查询的条件与子查询的结果高效关联。</p>
<h3>2. DELETE 操作的特殊性</h3>
<p>与 SELECT 查询不同，DELETE 操作有以下特点：</p>
<ul>
<li>风险更高：数据删除是不可逆操作</li>
<li>锁定要求：需要获取行锁，可能影响并发性能</li>
<li>日志记录：需要生成回滚日志用于事务处理</li>
</ul>
<p>因此，MySQL 优化器在处理 DELETE 语句时会更加"保守"，倾向于选择更可靠而非最高效的执行计划。</p>
<h3>3. 统计信息的影响</h3>
<p>如果表的统计信息不是最新的，优化器可能错误地估计使用索引与全表扫描的成本，从而做出非最优决策。</p>
<h2>解决方案：JOIN 重写的力量</h2>
<p>将查询重写为 JOIN 形式后，问题迎刃而解：</p>
<div class="cnblogs_Highlighter">
<pre class="brush:sql;gutter:true;">DELETE t
FROM tbl_doa_activityspecial t
JOIN (SELECT DISTINCT ActSetId FROM ...) s ON t.ActSetId = s.ActSetId;
</pre>
</div>
<p>使用 <code>EXPLAIN</code> 分析新查询，确认已经正确使用了 <code>tbl_doa_activityspecial_ActSetId_IDX</code> 索引。</p>
<h3>为什么 JOIN 更有效？</h3>
<ol>
<li>明确的连接关系：优化器能够清晰识别两个数据集之间的关联条件</li>
<li>避免不必要的物化：减少了创建临时表的开销</li>
<li>更好的成本估算：优化器可以更准确地评估不同执行计划的成本</li>
<li>直接的索引利用：连接条件直接指向索引字段，使索引使用更加直接</li>
</ol>
<h2>深度解析：MySQL 优化器的工作机制</h2>
<h3>查询重写优化</h3>
<p>MySQL 优化器会对查询进行重写，但不同的原始写法会导致不同的重写结果：</p>
<ul>
<li><code>IN</code> 子查询可能被重写为 <code>EXISTS</code> 或物化形式</li>
<li><code>JOIN</code> 语法则提供了更直接的连接语义</li>
</ul>
<h3>成本估算差异</h3>
<p>优化器基于成本估算选择执行计划，主要考虑：</p>
<ul>
<li>IO 成本：读取数据的开销</li>
<li>CPU 成本：处理数据的开销</li>
<li>内存使用：临时表、排序等的内存需求</li>
</ul>
<p>对于 <code>IN</code> 子查询，优化器可能高估使用索引的成本或低估全表扫描的成本。</p>
<h2>其他解决方案对比</h2>
<h3>方案一：使用 EXISTS 子查询</h3>
<div class="cnblogs_Highlighter">
<pre class="brush:sql;gutter:true;">DELETE FROM tbl_doa_activityspecial t
WHERE EXISTS (
SELECT 1 FROM ... s
WHERE s.ActSetId = t.ActSetId
);
</pre>
</div>
<h3>方案二：强制使用索引</h3>
<div>
<div class="cnblogs_Highlighter">
<pre class="brush:sql;gutter:true;">DELETE FROM tbl_doa_activityspecial FORCE INDEX (tbl_doa_activityspecial_ActSetId_IDX)
WHERE ActSetId IN (SELECT ActSetId FROM ...);
</pre>
</div>
<h3>方案三：使用派生表连接</h3>
<div class="cnblogs_Highlighter">
<pre class="brush:sql;gutter:true;">DELETE t
FROM tbl_doa_activityspecial t
INNER JOIN (
SELECT DISTINCT ActSetId FROM ...
) s USING (ActSetId);
</pre>
</div>
<h2>实践建议与最佳实践</h2>
<p><strong>1、始终先使用 SELECT 测试</strong></p>
<div class="cnblogs_Highlighter">
<pre class="brush:sql;gutter:true;">-- 先检查会影响到多少行
SELECT COUNT(*) FROM tbl_doa_activityspecial
WHERE ActSetId IN (SELECT ActSetId FROM ...);
</pre>
</div>
<p><strong>2、大批量删除分批次进行，因为大批量的删除可能会导致锁升级</strong> </p>
<div class="cnblogs_Highlighter">
<pre class="brush:sql;gutter:true;">-- 每次删除1000条记录，避免长事务
DELETE FROM tbl_doa_activityspecial
WHERE ActSetId IN (...)
LIMIT 1000;</pre>
</div>
<p><strong>3、在低峰期执行大规模删除操作，因为你很难确定删除期间会发生什么</strong></p>
<p><strong>通过这次优化经历，我得到了几个重要启示：</strong></p>
<ol>
<li><strong>不要盲目相信直觉：看似逻辑等价的查询，实际性能可能差异巨大</strong></li>
<li><strong>EXPLAIN 是关键工具：任何时候都要使用 EXPLAIN 验证执行计划</strong></li>
<li><strong>了解优化器的工作机制：理解优化器的决策过程有助于写出更高效的 SQL</strong></li>
<li><strong>JOIN 通常优于子查询：在大多数情况下，JOIN 语法能提供更好的性能</strong></li>
</ol>
<p>这个案例再次证明了深入了解数据库内部工作机制的重要性。作为开发者，我们不仅要写出功能正确的 SQL，更要关注其性能特征，特别是在涉及到大规模数据时。</p>
<p><strong>记住：最好的查询不是看起来最优雅的，而是执行最高效的。</strong></p>
<p> </p>
</div>
<p> </p><br><br>
来源：https://www.cnblogs.com/microsoft-xin/p/19594837
頁:
[1]