如何优化包含NOT IN、、OR等操作的查询？-mysql教程-PHP中文网

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

如何优化包含not in、<>、or等操作的查询？

优化包含

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

登录后复制

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

OR

登录后复制

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

PPT.CN,PPTCN,PPT.CN是什么,PPT.CN官网,PPT.CN如何使用

一键操作，智能生成专业级PPT

查看详情

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

拆分为
```
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
```
  登录后复制
  那么显著。