优化包含
NOT IN、
OR等操作的查询，核心在于理解这些操作符的底层工作机制及其对索引使用的影响，并积极寻找能够利用索引或减少数据扫描的替代方案，比如将
NOT IN替换为
LEFT JOIN ... IS NULL或
NOT EXISTS，将复杂的
OR条件分解为
UNION ALL或利用
EXISTS，同时确保相关列有合适的索引。 解决方案
在我看来，处理这类查询，首先要做的就是放下对现有SQL语句的“情感”，用一种批判性的眼光去审视它。很多时候，我们写SQL是基于业务逻辑的直观表达，而不是基于数据库性能的考量。
NOT IN和
OR就是这种直观表达的典型，它们在某些场景下确实简洁，但在性能上却可能成为瓶颈。
我通常会从以下几个方面入手：
-
理解执行计划（Execution Plan）：这是诊断问题的金钥匙。无论是
NOT IN
还是OR
，它们在执行计划中往往会暴露出全表扫描（Full Table Scan）、嵌套循环（Nested Loops）或临时表（Temporary Table）等高开销操作。通过分析执行计划，我们能清晰地看到数据库在哪个环节“卡壳”了，从而有针对性地进行优化。 -
替换
NOT IN
：这是最常见的优化点之一。NOT IN
在处理子查询返回大量数据或子查询结果包含NULL
时，表现会非常糟糕。我的经验是，几乎所有NOT IN
都可以被LEFT JOIN ... IS NULL
或NOT EXISTS
替代，而且通常效果更好。 -
重构
OR
条件：当一个查询中包含多个OR
条件时，尤其是在不同列上，数据库往往难以有效利用索引，最终可能退化为全表扫描。这时，考虑将其拆分为多个独立的SELECT
语句，然后用UNION ALL
连接起来，或者利用EXISTS
来改写。 -
索引策略：确保所有参与
WHERE
子句、JOIN
条件和ORDER BY
子句的列都有合适的索引。对于OR
条件，如果涉及的列都在同一个表上，并且都有索引，某些数据库（如MySQL的InnoDB）可能会使用索引合并（Index Merge）优化，但这不是万能的。 -
数据量与分布：有时问题不在于操作符本身，而在于数据量太大或数据分布不均。例如，如果
NOT IN
的子查询返回的数据量非常庞大，或者OR
条件命中率极低，那么任何优化都可能效果有限，这时可能需要考虑更深层次的设计调整，比如物化视图、数据分区，甚至是应用层面的缓存。
我的观点是，优化查询是一个迭代的过程。先尝试最直接的替换和重构，然后再次检查执行计划，看看是否有所改善。如果效果不明显，再深入分析，考虑更复杂的索引或设计方案。
为什么NOT IN查询效率低下，有哪些更优的替代方案？NOT IN查询效率低下的原因，我总结下来主要有两点，也是我经常在性能调优中遇到的坑：
首先，
NOT IN在内部处理时，对于子查询的结果集，它需要逐一比对主查询的每一行。如果子查询返回的数据量很大，这种逐一比对的开销会非常高。更要命的是，许多数据库在处理
NOT IN时，如果子查询结果中包含任何
NULL值，整个
NOT IN条件就会返回
UNKNOWN，导致最终结果为空，这不仅是性能问题，更是逻辑错误。我见过不少开发者因此陷入泥潭，调试半天发现是
NULL捣的鬼。
其次，
NOT IN通常难以有效利用索引。即使子查询的列上有索引，数据库也可能无法在主查询的
NOT IN条件上利用索引进行快速查找，因为它需要确保主查询的每一行都不存在于子查询的任何结果中，这比
IN操作（只需要存在于任何一个结果中）要复杂得多，往往导致全表扫描。
基于这些痛点，我强烈建议采用以下两种更优的替代方案：
-
LEFT JOIN ... WHERE IS NULL
：这是我最常用的替代方式，它直观、高效，并且能很好地处理NULL
问题。其基本思想是，尝试将主表与子查询（或关联表）进行左连接。如果主表中的某一行在关联表中找不到匹配项（即关联列为NULL
），那么它就是我们想要的“不在”的数据。-- 原始的 NOT IN 查询 SELECT a.* FROM table_a a WHERE a.id NOT IN (SELECT b.a_id FROM table_b b WHERE b.status = 'inactive'); -- 优化后的 LEFT JOIN ... IS NULL SELECT a.* FROM table_a a LEFT JOIN (SELECT DISTINCT b.a_id FROM table_b b WHERE b.status = 'inactive') AS excluded_ids ON a.id = excluded_ids.a_id WHERE excluded_ids.a_id IS NULL;
这里我特意在子查询中加了
DISTINCT
，因为LEFT JOIN
时如果右表有重复，可能会导致左表记录重复，这不是我们想要的结果。这种方式通常能更好地利用a.id
和b.a_id
上的索引。 -
NOT EXISTS
：NOT EXISTS
是另一种非常强大的替代方案。它的工作原理是，对于主查询的每一行，检查子查询是否能找到任何匹配的行。如果找不到，则条件为真。NOT EXISTS
的一个优点是，子查询只要找到一个匹配项就会停止扫描，而NOT IN
可能需要扫描整个子查询结果集。更重要的是，NOT EXISTS
对NULL
的处理更健壮，它不会像NOT IN
那样因为子查询中的NULL
而导致整个条件失效。-- 原始的 NOT IN 查询 (同上) SELECT a.* FROM table_a a WHERE a.id NOT IN (SELECT b.a_id FROM table_b b WHERE b.status = 'inactive'); -- 优化后的 NOT EXISTS SELECT a.* FROM table_a a WHERE NOT EXISTS (SELECT 1 FROM table_b b WHERE b.a_id = a.id AND b.status = 'inactive');
我个人更偏爱
LEFT JOIN ... IS NULL
，因为它在某些场景下（特别是当子查询结果集不大时）的执行计划可能更易于理解和优化。但NOT EXISTS
在处理大型子查询或复杂条件时，往往能展现出更优秀的性能，尤其是在Oracle这类数据库中。选择哪种，最终还是得看具体的执行计划和数据特点。
包含多个
OR条件的复杂查询，尤其当这些
OR条件涉及不同列时，是我在性能调优中经常遇到的另一个“老大难”问题。数据库优化器在处理
OR时，往往会面临一个困境：它很难同时为所有
OR分支都有效利用索引。结果就是，它可能选择放弃索引，进行全表扫描，或者使用效率不高的索引合并策略。
PIA
全面的AI聚合平台，一站式访问所有顶级AI模型
226
查看详情
我的经验是，重构这类查询的关键在于“分而治之”和“化繁为简”。
-
拆分为
UNION ALL
：这是最直接也最常用的方法。如果你的OR
条件可以被清晰地分解成几个独立的、互不干扰的查询逻辑，那么将它们分别写成独立的SELECT
语句，然后用UNION ALL
连接起来，通常能获得更好的性能。每个独立的SELECT
语句都可以单独利用其涉及列上的索引，避免了OR
条件带来的索引使用障碍。-- 原始的包含多个 OR 条件的查询 SELECT * FROM orders WHERE (customer_id = 101 AND status = 'pending') OR (order_date < '2023-01-01' AND total_amount > 1000) OR (region = 'North' AND delivery_method = 'express'); -- 优化后的 UNION ALL SELECT * FROM orders WHERE customer_id = 101 AND status = 'pending' UNION ALL SELECT * FROM orders WHERE order_date < '2023-01-01' AND total_amount > 1000 UNION ALL SELECT * FROM orders WHERE region = 'North' AND delivery_method = 'express';
这里需要注意，
UNION ALL
不会去重，如果你的业务逻辑允许重复结果，这没问题。如果需要去重，可以使用UNION
，但UNION
会带来额外的去重开销，可能会抵消部分性能提升。在我的实践中，大多数情况下UNION ALL
就足够了，因为通常我们关心的是获取所有符合条件的数据，而不是严格去重。 -
利用
EXISTS
或IN
（如果适用）：有时，OR
条件是为了检查一个主表记录是否满足多个关联条件中的任何一个。这种情况下，EXISTS
或IN
可能会是更好的选择。-- 假设我们想找到在某个特定时间段内，有任意一个子订单满足某种条件的父订单 -- 原始的复杂 OR (可能需要 JOIN) SELECT p.* FROM parent_orders p JOIN child_orders c ON p.id = c.parent_id WHERE (c.status = 'returned' AND c.return_date > '2023-06-01') OR (c.quantity > 100 AND c.product_category = 'electronics'); -- 优化后的 EXISTS SELECT p.* FROM parent_orders p WHERE EXISTS (SELECT 1 FROM child_orders c WHERE c.parent_id = p.id AND (c.status = 'returned' AND c.return_date > '2023-06-01' OR c.quantity > 100 AND c.product_category = 'electronics'));这里虽然子查询内部仍然有
OR
，但EXISTS
的特性使得它在找到第一个匹配项后就可以停止，并且它通常能更好地利用child_orders
表上的索引。如果OR
条件只是检查某个列是否在多个值中，那么直接使用IN
操作符会更简洁高效，例如WHERE status IN ('pending', 'processing', 'shipped')。 创建复合索引或函数索引：在某些特定情况下，如果
OR
条件涉及的列经常一起出现，并且数据分布允许，可以考虑创建复合索引。例如，CREATE INDEX idx_status_region ON orders (status, region);
。但请注意，复合索引的顺序很重要，并且它对OR
条件的帮助是有限的，通常只对第一个条件有效。对于涉及函数调用的OR
条件，如果数据库支持，可以考虑创建函数索引。但这些都是比较高级且需要谨慎评估的方案。
最终，选择哪种重构方式，都需要结合实际的业务场景、数据分布、数据库类型和最重要的——执行计划来决定。没有一劳永逸的方案，只有最适合当前问题的解决方案。
如何利用索引策略和执行计划分析来提升包含这些操作的查询性能？在我看来，索引策略和执行计划分析就像是医生手中的X光片和处方药。你不能只开药（建索引）而不看病灶（分析执行计划），也不能只看病灶而不对症下药。它们是紧密结合、缺一不可的。
-
深入理解执行计划： 这是我每次遇到性能问题时，首先会做的事情。执行计划能告诉你数据库“思考”了什么，它打算如何执行你的查询。
-
关注扫描类型：看到
Full Table Scan
（全表扫描）或Full Index Scan
（全索引扫描）时，要警惕。特别是全表扫描，它意味着数据库可能没有找到更好的路径。Range Scan
（范围扫描）或Index Seek
（索引查找）通常是比较理想的。 -
关注连接类型：
Nested Loops
（嵌套循环）在小数据集上可能很快，但在大数据集上会非常慢。Hash Join
或Merge Join
则有不同的适用场景。 -
关注临时表/排序：如果执行计划中出现大量
Using temporary
或Using filesort
，这通常意味着数据库需要将数据加载到内存或磁盘进行排序/聚合，这是性能杀手。 - 关注行数估算：执行计划会显示数据库预估的行数。如果实际行数与预估值相差甚远，可能意味着统计信息过时，或者查询条件过于复杂导致优化器判断失误。
对于
NOT IN
和OR
，执行计划往往会揭示它们导致全表扫描或低效的嵌套循环。例如，一个NOT IN
子查询如果返回了大量数据，你可能会看到主查询对子查询结果进行一次又一次的扫描比对。而OR
条件，如果涉及的列没有合适的组合索引，或者优化器认为索引合并不划算，就会直接走全表扫描。 -
关注扫描类型：看到
-
制定精准的索引策略： 索引不是越多越好，也不是越大越好。错误的索引甚至会降低写入性能。我的索引策略通常遵循以下原则：
-
覆盖索引（Covering Index）：如果一个查询只需要从索引中获取所有需要的数据，而不需要回表（即访问原始数据行），那么这个索引就是覆盖索引。这对于
SELECT count(*)
或只选择索引列的查询非常有效。例如，SELECT id, status FROM orders WHERE status = 'pending'
，如果orders
表在(status, id)
上有一个索引，那么这个查询就可以直接从索引中获取所有数据。 -
复合索引（Composite Index）：当
WHERE
子句中经常出现多个列的组合条件时，可以考虑创建复合索引。例如，WHERE customer_id = ? AND order_date > ?
，可以在(customer_id, order_date)
上创建复合索引。需要注意的是，复合索引的列顺序很重要，通常将选择性高的列放在前面。对于OR
条件，复合索引的帮助有限，因为它通常只能帮助到索引的第一个列。 -
函数索引（Functional Index）：如果你的
WHERE
子句中使用了函数（如YEAR(order_date)
），而你又想利用索引，那么可以考虑创建函数索引。但这并非所有数据库都支持，并且会增加索引维护的开销。 -
避免冗余索引：例如，如果已经有了
(a, b, c)
的复合索引，那么单独的(a)
和(a, b)
索引可能就是冗余的，因为前者已经包含了后者的信息。但如果查询经常只用到a
或a, b
，那么单独的索引也可能被优化器选择。这需要通过执行计划来验证。 - 主键和唯一索引：它们是数据库性能的基石，确保数据的完整性和查询的唯一性。它们本身就是一种高效的索引。
针对
NOT IN
和OR
的优化，我的索引建议是：-
NOT IN
/NOT EXISTS
/LEFT JOIN ... IS NULL
：确保主查询和子查询（或关联表）中用于连接的列（如a.id
和b.a_id
）都有索引。这能大大加速连接或子查询的查找过程。 -
OR
条件：如果能重构为UNION ALL
，那么每个UNION ALL
分支中的WHERE
条件都应该有相应的索引。如果无法重构，且OR
条件涉及多个列，可以尝试为每个列单独创建索引，让数据库优化器有机会使用索引合并。但如果OR
条件涉及的列在同一个表上，且经常一起出现，可以考虑复合索引，尽管其效果可能不如UNION ALL
那么显著。
-
覆盖索引（Covering Index）：如果一个查询只需要从索引中获取所有需要的数据，而不需要回表（即访问原始数据行），那么这个索引就是覆盖索引。这对于
总的来说，优化是一个不断试错和学习的过程。我常常会建立一个假设（比如“我觉得这里加个索引会快”），然后通过修改SQL或添加索引，再运行执行计划，对比前后差异，最终找到最优解。这个过程需要耐心，也需要对数据库原理有扎实的理解。
以上就是如何优化包含NOT IN、<>、OR等操作的查询？的详细内容，更多请关注知识资源分享宝库其它相关文章！
相关标签： mysql oracle go 大数据 sql语句 为什么 sql mysql NULL count select union 循环 using table oracle 数据库 重构 大家都在看： MySQL内存使用过高（OOM）的诊断与优化配置 MySQL与NoSQL的融合：探索MySQL Document Store的应用 如何通过canal等工具实现MySQL到其他数据源的实时同步？ 使用Debezium进行MySQL变更数据捕获（CDC）实战 如何设计和优化MySQL中的大表分页查询方案
发表评论:
◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。