oracle中使用in和not in查询效率总结和优化建议
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">1.IN的效率</a></li><li><a href="#_label1">2.NOT IN的潜在问题</a></li><li><a href="#_label2">3. 优化建议</a></li><li><a href="#_label3">4. 示例对比</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_0">场景：查询在表B中不存在的记录</a></li></ul><li><a href="#_label4">5. 关键总结</a></li><ul class="second_class_ul"></ul><li><a href="#_label5">附：Oracle中not in ()语法问题</a></li><ul class="second_class_ul"></ul></ul></div><p>在Oracle数据库中，<code>IN</code>和<code>NOT IN</code>的查询效率受多种因素影响，以下是关键点总结和优化建议：</p><p class="maodian"><a name="_label0"></a></p><h2>1.IN的效率</h2>
<ul><li><strong>优化方式</strong>：
<ul><li><code>IN</code> 通常会被优化为 <strong>OR条件</strong> 或 <strong>半连接（Semi-Join）</strong>，如果子查询关联到外部表，可能转为 <code>EXISTS</code>。</li><li>若字段有索引，且优化器选择索引扫描（Index Scan），效率较高。</li></ul></li><li><strong>适用场景</strong>：<ul><li>静态值列表较短时（例如 <code>IN (1,2,3)</code>）。</li><li>子查询结果集较小且能利用索引时。</li></ul></li></ul>
<p class="maodian"><a name="_label1"></a></p><h2>2.NOT IN的潜在问题</h2>
<ul><li><strong>NULL 值陷阱</strong>：<br />如果子查询结果包含 <code>NULL</code>，<code>NOT IN</code> 会导致结果集为空（逻辑上等价于 <code>!= ALL</code>）。需确保子查询字段非空（如添加 <code>WHERE col IS NOT NULL</code>）。</li><li><strong>效率问题</strong>：<ul><li>若子查询结果集较大，<code>NOT IN</code> 可能需要全表扫描，效率较低。</li><li>可能被优化为 <strong>反连接（Anti-Join）</strong>，但需索引支持。</li></ul></li><li><strong>替代方案</strong>：<br />优先使用 <code>NOT EXISTS</code>，避免 <code>NULL</code> 问题且通常更高效（尤其在子查询能利用索引时）。</li></ul>
<p class="maodian"><a name="_label2"></a></p><h2>3. 优化建议</h2>
<ul><li><p><strong>使用 EXISTS/NOT EXISTS 替代</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 优于 NOT IN
SELECT * FROM table1 t1
WHERE NOT EXISTS (
SELECT 1 FROM table2 t2 WHERE t2.id = t1.id
);
</pre></div>
<ul><li><code>EXISTS</code> 在找到匹配项后立即终止子查询，减少计算量。</li><li>对 <code>NULL</code> 安全，无需额外处理。</li></ul></li><li><p><strong>确保索引有效</strong>：</p>
<ul><li>为 <code>IN</code>/<code>NOT IN</code> 涉及的字段创建索引（尤其是主键或高选择性字段）。</li><li>子查询的连接字段（如 <code>t2.id</code>）应建立索引。</li></ul></li><li><p><strong>处理长静态列表</strong>：</p>
<ul><li>避免超过1000个元素的静态列表（如 <code>IN (1,2,...,1001)</code>），可改用临时表或拆分查询。</li></ul></li><li><p><strong>检查执行计划</strong>：<br />使用 <code>EXPLAIN PLAN</code> 分析查询是否走索引或优化为高效的连接方式（如哈希反连接）。</p></li></ul>
<p class="maodian"><a name="_label3"></a></p><h2>4. 示例对比</h2>
<p class="maodian"><a name="_lab2_3_0"></a></p><h3>场景：查询在表B中不存在的记录</h3>
<ul><li><strong>低效写法</strong>（可能受NULL影响）：
<div class="jb51code"><pre class="brush:sql;">SELECT * FROM tableA
WHERE id NOT IN (SELECT id FROM tableB);
</pre></div></li><li><strong>高效改写</strong>：<div class="jb51code"><pre class="brush:sql;">SELECT * FROM tableA a
WHERE NOT EXISTS (
SELECT 1 FROM tableB b WHERE b.id = a.id
);
</pre></div></li></ul>
<p class="maodian"><a name="_label4"></a></p><h2>5. 关键总结</h2>
<table><thead><tr><th>操作符</th><th>效率影响因素</th><th>适用场景</th><th>注意事项</th></tr></thead><tbody><tr><td><code>IN</code></td><td>索引、子查询结果集大小、静态列表长度</td><td>小结果集或静态短列表</td><td>避免超长静态列表</td></tr><tr><td><code>NOT IN</code></td><td>子查询中的NULL、索引缺失、结果集大小</td><td>需显式处理NULL的子查询</td><td>优先用 <code>NOT EXISTS</code> 替代</td></tr><tr><td><code>EXISTS</code></td><td>子查询索引、关联字段</td><td>检查存在性，尤其是大表关联</td><td>对 <code>NULL</code> 安全</td></tr><tr><td><code>NOT EXISTS</code></td><td>子查询索引、关联字段</td><td>检查不存在性，替代 <code>NOT IN</code></td><td>优于 <code>NOT IN</code> 的通用选择</td></tr></tbody></table>
<p>通过合理使用索引、避免 <code>NULL</code> 陷阱、改写为 <code>EXISTS</code>/<code>NOT EXISTS</code>，并结合执行计划分析，可以显著提升查询效率。</p>
<p class="maodian"><a name="_label5"></a></p><h2>附：Oracle中not in ()语法问题</h2>
<p>在SQL查询中使用isnotin子查询时遇到的问题，即当子查询返回值包含null时，条件始终无法匹配导致查询失败。作者通过排查发现并解释了这一现象，指出isnotin与null值的交互会导致条件始终返回false。解决方案是在子查询中过滤掉null值，通过添加'wherexxxisnotnull'来修复问题。</p>
<p>最近遇到一个SQL，where条件中用了is not in (子查询）的语法来过滤数据，但整个SQL执行时一直查不到东西</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202511/202511692849968.png" /></p>
<p>排查了下，最后定位到问题出在这个is not in ()条件中，于是将括号里面的子查询执行了下，发现他查询的字段中有一条数据为null值</p>
<p><img alt="" src="https://img.jbzj.com/file_images/article/202511/202511692849969.png" /></p>
<p>刚开始没看出有啥问题，百度一波后发现，如果is not in ()子查询返回值中有null值，那这个条件始终会返回false，导致整个SQL啥都查询不到。</p>
<p>所以修复的方法就是在子查询中过滤掉空值，子查询后加上 "where xxx is not null"即可</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202511/202511692849970.png" /></p>
頁:
[1]