如何优化包含NOT IN、<>、OR等操作的查询？

优化NOT IN和OR查询的核心是避免全表扫描，优先使用LEFT JOIN … IS NULL或NOT EXISTS替代NOT IN，将OR拆分为union ALL，并为各分支条件建立合适索引，结合执行计划分析确保索引有效利用。

优化包含

NOT IN

、

OR

等操作的查询，核心在于理解这些操作符的底层工作机制及其对索引使用的影响，并积极寻找能够利用索引或减少数据扫描的替代方案，比如将

NOT IN

替换为

LEFT JOIN ... IS NULL

或

NOT EXISTS

，将复杂的

OR

条件分解为

UNION ALL

或利用

EXISTS

，同时确保相关列有合适的索引。

解决方案

在我看来，处理这类查询，首先要做的就是放下对现有sql语句的“情感”，用一种批判性的眼光去审视它。很多时候，我们写SQL是基于业务逻辑的直观表达，而不是基于数据库性能的考量。

NOT IN

和

OR

就是这种直观表达的典型，它们在某些场景下确实简洁，但在性能上却可能成为瓶颈。

我通常会从以下几个方面入手：

1. 理解执行计划（Execution Plan）：这是诊断问题的金钥匙。无论是

   NOT IN

   还是

   OR

   ，它们在执行计划中往往会暴露出全表扫描（Full table Scan）、嵌套循环（Nested Loops）或临时表（Temporary Table）等高开销操作。通过分析执行计划，我们能清晰地看到数据库在哪个环节“卡壳”了，从而有针对性地进行优化。

2. 替换

   NOT IN

   ：这是最常见的优化点之一。

   NOT IN

   在处理子查询返回大量数据或子查询结果包含

   NULL

   时，表现会非常糟糕。我的经验是，几乎所有

   NOT IN

   都可以被

   LEFT JOIN ... IS NULL

   或

   NOT EXISTS

   替代，而且通常效果更好。

3. 重构

   OR

   条件：当一个查询中包含多个

   OR

   条件时，尤其是在不同列上，数据库往往难以有效利用索引，最终可能退化为全表扫描。这时，考虑将其拆分为多个独立的

   select

   语句，然后用

   UNION ALL

   连接起来，或者利用

   EXISTS

   来改写。

4. 索引策略：确保所有参与

   WHERE

   子句、

   JOIN

   条件和

   ORDER BY

   子句的列都有合适的索引。对于

   OR

   条件，如果涉及的列都在同一个表上，并且都有索引，某些数据库（如mysql的InnoDB）可能会使用索引合并（Index Merge）优化，但这不是万能的。

5. 数据量与分布：有时问题不在于操作符本身，而在于数据量太大或数据分布不均。例如，如果

   NOT IN

   的子查询返回的数据量非常庞大，或者

   OR

   条件命中率极低，那么任何优化都可能效果有限，这时可能需要考虑更深层次的设计调整，比如物化视图、数据分区，甚至是应用层面的缓存。

我的观点是，优化查询是一个迭代的过程。先尝试最直接的替换和重构，然后再次检查执行计划，看看是否有所改善。如果效果不明显，再深入分析，考虑更复杂的索引或设计方案。

为什么NOT IN查询效率低下，有哪些更优的替代方案？

NOT IN

查询效率低下的原因，我总结下来主要有两点，也是我经常在性能调优中遇到的坑：

首先，

NOT IN

在内部处理时，对于子查询的结果集，它需要逐一比对主查询的每一行。如果子查询返回的数据量很大，这种逐一比对的开销会非常高。更要命的是，许多数据库在处理

NOT IN

时，如果子查询结果中包含任何

NULL

值，整个

NOT IN

条件就会返回

UNKNOWN

，导致最终结果为空，这不仅是性能问题，更是逻辑错误。我见过不少开发者因此陷入泥潭，调试半天发现是

NULL

捣的鬼。

其次，

NOT IN

通常难以有效利用索引。即使子查询的列上有索引，数据库也可能无法在主查询的

NOT IN

条件上利用索引进行快速查找，因为它需要确保主查询的每一行都不存在于子查询的任何结果中，这比

IN

操作（只需要存在于任何一个结果中）要复杂得多，往往导致全表扫描。

基于这些痛点，我强烈建议采用以下两种更优的替代方案：

1. 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

   上的索引。

2. 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

时，往往会面临一个困境：它很难同时为所有

OR

分支都有效利用索引。结果就是，它可能选择放弃索引，进行全表扫描，或者使用效率不高的索引合并策略。

Raphael AI

免费无限制AI图像生成工具

439

查看详情

我的经验是，重构这类查询的关键在于“分而治之”和“化繁为简”。

1. 拆分为

   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

   就足够了，因为通常我们关心的是获取所有符合条件的数据，而不是严格去重。

2. 利用

   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')

   。

3. 创建复合索引或函数索引：在某些特定情况下，如果

   OR

   条件涉及的列经常一起出现，并且数据分布允许，可以考虑创建复合索引。例如，

   CREATE INDEX idx_status_region ON orders (status, region);

   。但请注意，复合索引的顺序很重要，并且它对

   OR

   条件的帮助是有限的，通常只对第一个条件有效。对于涉及函数调用的

   OR

   条件，如果数据库支持，可以考虑创建函数索引。但这些都是比较高级且需要谨慎评估的方案。

最终，选择哪种重构方式，都需要结合实际的业务场景、数据分布、数据库类型和最重要的——执行计划来决定。没有一劳永逸的方案，只有最适合当前问题的解决方案。

如何利用索引策略和执行计划分析来提升包含这些操作的查询性能？

在我看来，索引策略和执行计划分析就像是医生手中的X光片和处方药。你不能只开药（建索引）而不看病灶（分析执行计划），也不能只看病灶而不对症下药。它们是紧密结合、缺一不可的。

1. 深入理解执行计划： 这是我每次遇到性能问题时，首先会做的事情。执行计划能告诉你数据库“思考”了什么，它打算如何执行你的查询。

   • 关注扫描类型：看到

     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

   条件，如果涉及的列没有合适的组合索引，或者优化器认为索引合并不划算，就会直接走全表扫描。

2. 制定精准的索引策略： 索引不是越多越好，也不是越大越好。错误的索引甚至会降低写入性能。我的索引策略通常遵循以下原则：

   • 覆盖索引（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

     那么显著。

总的来说，优化是一个不断试错和学习的过程。我常常会建立一个假设（比如“我觉得这里加个索引会快”），然后通过修改SQL或添加索引，再运行执行计划，对比前后差异，最终找到最优解。这个过程需要耐心，也需要对数据库原理有扎实的理解。