MySQL如何取代OR_MySQL查询中OR条件优化与替代方案教程

使用UNION ALL、IN、EXISTS或JOIN替代OR可提升MySQL查询性能，核心是让每个子查询独立利用索引，避免全表扫描。

在MySQL查询中遇到

OR

OR

OR

UNION ALL

IN

EXISTS

JOIN

解决方案

当我们在MySQL中面对一个包含

OR

首先，最直接也最常用的替代方式是

UNION ALL

OR

UNION ALL

SELECT

OR

SELECT * FROM users WHERE status = 'active' OR registration_date > '2023-01-01'

status

registration_date

SELECT

UNION ALL

OR

UNION ALL

UNION

UNION

其次，对于针对同一列的多个离散值的查询，

IN

OR

WHERE category_id = 1 OR category_id = 5 OR category_id = 10

WHERE category_id IN (1, 5, 10)

IN

category_id

IN

JOIN

再者，对于一些更复杂的，涉及到子查询或关联表的

OR

EXISTS

JOIN

OR

SELECT * FROM C WHERE C.condition_A = 'X' OR C.id IN (SELECT B.c_id FROM B WHERE B.condition_B = 'Y')

UNION ALL

LEFT JOIN

JOIN

NULL

EXISTS

EXISTS

最后，也是最根本的，有时候

OR

OR

gender = 'male' OR age < 18

OR

AND

为什么MySQL中的OR条件有时会成为性能瓶颈？

在我看来，

OR

OR

AND

想象一下，你有一个

WHERE column1 = value1 OR column2 = value2

column1

column2

OR

OR

此外，当

OR

使用UNION ALL替代OR条件有哪些场景和优势？

在我多年的数据库调优经历中，

UNION ALL

OR

场景：

• OR条件涉及不同列，且每列都有独立的有效索引。 这是最经典的场景。例如，

  SELECT * FROM products WHERE category_id = 10 OR supplier_id = 5 OR price > 1000;

  登录后复制
  如果

  category_id

  登录后复制
  、

  supplier_id

  登录后复制
  、

  price

  登录后复制
  都有各自的索引，那么将其拆分为三个独立的

  SELECT

  登录后复制
  语句，每个语句都只查询一个条件，并利用对应的索引，然后通过

  UNION ALL

  登录后复制
  合并，效果会非常好。
• OR条件中包含的子句，其中一些子句会导致索引失效。 比如，

  WHERE status = 'active' OR JSON_EXTRACT(data, '$.flag') = true;

  登录后复制
  这里的

  JSON_EXTRACT

  登录后复制
  函数会使

  data

  登录后复制
  列上的索引失效。如果将

  status = 'active'

  登录后复制
  单独查询，并利用

  status

  登录后复制
  列的索引，再将第二个条件单独查询（即使是全表扫描），然后

  UNION ALL

  登录后复制
  ，通常会比一个包含函数导致全表扫描的

  OR

  登录后复制
  查询更快。
• 需要对每个OR子句应用不同的

  ORDER BY

  登录后复制
  或

  LIMIT

  登录后复制
  。 虽然这不直接是

  OR

  登录后复制
  的替代，但在某些复杂报告或分页场景中，如果

  OR

  登录后复制
  的逻辑需要精细控制每个部分的排序或数量，

  UNION ALL

  登录后复制
  提供了这种灵活性。

优势：

• 充分利用索引： 每个

  SELECT

  登录后复制
  子句都可以独立地进行优化，并利用其最适合的索引。这避免了

  OR

  登录后复制
  条件可能导致的索引失效问题。
• 优化器决策简化： 优化器不再需要处理复杂的

  OR

  登录后复制
  逻辑和索引合并策略，它只需要优化每个独立的

  SELECT

  登录后复制
  语句，这通常是它最擅长的。
• 避免不必要的去重开销： 使用

  UNION ALL

  登录后复制
  而不是

  UNION

  登录后复制
  ，可以避免结果集去重的额外处理，从而节省CPU和I/O资源，尤其是在你确定各个子查询的结果集不会有重复，或者重复可以接受时。
• 可读性提升： 对于一些非常复杂的

  OR

  登录后复制
  逻辑，拆分成多个

  SELECT

  登录后复制
  子句可能会让查询意图更加清晰，便于理解和维护。

代码示例：

代码小浣熊

代码小浣熊是基于商汤大语言模型的软件智能研发助手，覆盖软件需求分析、架构设计、代码编写、软件测试等环节

51

查看详情

-- 原始的OR查询 (可能性能不佳)
SELECT *
FROM orders
WHERE customer_id = 123
   OR order_date >= '2023-01-01'
   OR total_amount > 500.00;

-- 使用UNION ALL 替代
(SELECT * FROM orders WHERE customer_id = 123)
UNION ALL
(SELECT * FROM orders WHERE order_date >= '2023-01-01' AND customer_id != 123) -- 注意避免重复，如果不需要去重且重复可接受，则无需AND customer_id != 123
UNION ALL
(SELECT * FROM orders WHERE total_amount > 500.00 AND customer_id != 123 AND order_date < '2023-01-01'); -- 同上，避免重复

-- 更通用的UNION ALL，不考虑去重，让应用层处理或后续用DISTINCT
(SELECT * FROM orders WHERE customer_id = 123)
UNION ALL
(SELECT * FROM orders WHERE order_date >= '2023-01-01')
UNION ALL
(SELECT * FROM orders WHERE total_amount > 500.00);

在实际应用中，如果每个子查询的结果可能存在重复，而你又需要去重，那么在最外层再加一个

DISTINCT

UNION

UNION ALL

IN操作符在优化多值查询方面如何优于OR？

当我需要查询一个字段匹配多个离散值时，比如

WHERE status = 'pending' OR status = 'processing' OR status = 'failed'

OR

IN

WHERE status IN ('pending', 'processing', 'failed')

IN

优势：

• 优化器友好： 对于

  IN

  登录后复制
  操作符，MySQL优化器能够更清晰地理解你的意图是查询一个列的多个特定值。在内部，它可能会将

  IN

  登录后复制
  列表转换为一个有效的范围扫描（range scan），或者在某些情况下，如果

  IN

  登录后复制
  列表非常大，甚至会将其转换为一个临时表进行

  JOIN

  登录后复制
  操作。这些内部优化通常比处理一长串由

  OR

  登录后复制
  连接的等值条件要高效得多。
• 索引利用率高： 如果被查询的列（如

  status

  登录后复制
  ）有索引，

  IN

  登录后复制
  操作符能够非常有效地利用这个索引。它可以在索引树上进行多次查找，快速定位到所有匹配的行，而无需扫描整个表。相比之下，虽然一长串

  OR

  登录后复制
  理论上也能利用索引，但优化器在处理这种复杂的布尔逻辑时，可能会面临更多挑战，有时甚至会选择效率较低的路径。
• 代码简洁性与可读性： 这是最直观的优势。想象一下如果你有几十个值要匹配，用

  OR

  登录后复制
  连接会使得查询语句冗长且难以阅读和维护。

  IN

  登录后复制
  操作符则能将这些值清晰地组织在一个列表中，大大提高了代码的可读性。

局限性：

需要注意的是，

IN

OR

WHERE column1 = value1 OR column2 = value2

IN

UNION ALL

代码示例：

-- 原始的OR查询 (针对同一列，冗长且可能效率稍逊)
SELECT order_id, status, customer_id
FROM orders
WHERE status = 'pending' OR status = 'processing' OR status = 'failed';

-- 使用IN操作符替代 (推荐)
SELECT order_id, status, customer_id
FROM orders
WHERE status IN ('pending', 'processing', 'failed');

通过

EXPLAIN

IN

什么时候应该考虑使用EXISTS或JOIN来优化复杂的OR逻辑？

当

OR

EXISTS

JOIN

OR

使用EXISTS的场景和优势：

EXISTS

TRUE

• 场景： 你需要查询主表中的数据，这些数据满足某个条件，或者在另一个关联表中存在满足特定条件的记录。例如，查询所有订单，这些订单或者状态是“已取消”，或者订单中包含某个特定商品。
• 优势：
  • 短路评估： 这是

    EXISTS

    登录后复制
    最大的特点。子查询一旦找到一个匹配项，就不再执行，这比

    IN

    登录后复制
    操作符可能需要扫描所有匹配值要快。
  • 避免数据重复： 与

    JOIN

    登录后复制
    不同，

    EXISTS

    登录后复制
    不会增加主查询的行数，它只是一个布尔判断，因此不会产生重复行，避免了后续去重的开销。
  • 处理非关联子查询： 尽管通常用于关联子查询，但

    EXISTS

    登录后复制
    也可以处理非关联子查询，判断某个条件是否存在。

代码示例 (EXISTS):

-- 原始的OR查询 (可能涉及子查询，效率堪忧)
SELECT o.order_id, o.customer_id
FROM orders o
WHERE o.status = 'cancelled'
   OR o.order_id IN (SELECT oi.order_id FROM order_items oi WHERE oi.product_id = 789);

-- 使用EXISTS替代
SELECT o.order_id, o.customer_id
FROM orders o
WHERE o.status = 'cancelled'
   OR EXISTS (SELECT 1 FROM order_items oi WHERE oi.order_id = o.order_id AND oi.product_id = 789);

这里，

EXISTS

orders

order_items

使用JOIN的场景和优势：

JOIN

OR

JOIN

• 场景： 你需要查询主表中的数据，并且这些数据必须与关联表中的某些记录同时满足条件。例如，查询所有客户，这些客户或者年龄小于30，或者他们最近有购买记录。
• 优势：
  • 获取关联数据：

    JOIN

    登录后复制
    不仅能过滤数据，还能将关联表中的列一同查询出来，这在很多业务场景中是必要的。
  • 优化器擅长： MySQL的优化器在处理各种

    JOIN

    登录后复制
    操作方面非常成熟，能够有效地利用索引进行快速连接。
  • 灵活性： 可以使用

    INNER JOIN

    登录后复制
    、

    LEFT JOIN

    登录后复制
    等不同类型的

    JOIN

    登录后复制
    来满足不同的业务需求。

代码示例 (JOIN):

-- 原始的OR查询 (可能导致性能问题)
SELECT c.customer_id, c.name
FROM customers c
WHERE c.age < 30
   OR c.customer_id IN (SELECT o.customer_id FROM orders o WHERE o.order_date >= CURDATE() - INTERVAL 30 DAY);

-- 使用LEFT JOIN + IS NOT NULL 替代
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.order_date >= CURDATE() - INTERVAL 30 DAY
WHERE c.age < 30 OR o.order_id IS NOT NULL;

在这个

LEFT JOIN

o.order_id

NULL

o.order_id IS NOT NULL

IN

EXISTS

选择

EXISTS

JOIN

EXISTS

JOIN

JOIN

索引策略如何影响OR条件的查询性能？

索引对于任何数据库查询的性能都至关重要，但对于

OR

OR

首先，一个基本的认知是，索引的主要作用是快速定位数据，而不是扫描数据。对于

AND

WHERE col1 = 'A' AND col2 = 'B'

(col1, col2)

然而，当涉及到

OR

WHERE col1 = 'A' OR col2 = 'B'

1. 单列索引的局限性：

   • 如果

     col1

     登录后复制
     有索引，

     col2

     登录后复制
     没有索引，那么即使

     col1 = 'A'

     登录后复制
     的部分能利用索引，为了满足

     col2 = 'B'

     登录后复制
     的条件，MySQL可能仍然需要进行全表扫描。因为数据库必须确保所有满足

     col2 = 'B'

     登录后复制
     的行都被找到，而这部分没有索引可以依赖。最终，整个

     OR

     登录后复制
     查询可能会退化为全表扫描。
   • 如果

     col1

     登录后复制
     和

     col2

     登录后复制
     都有单独的索引，MySQL可能会尝试使用“索引合并”（Index Merge Optimization）策略。这意味着它会分别使用

     col1

     登录后复制
     的索引和

     col2

     登录后复制
     的索引找到各自的匹配行，然后将这两个结果集进行合并（对于

     OR

     登录后复制
     条件是求并集）。虽然这比全表扫描好，但索引合并本身也是有开销的，尤其是在结果集很大的情况下，合并操作可能会消耗大量CPU和内存资源。而且，索引合并并非总是会被优化器选择，它需要满足一定的条件。

2. 复合索引的挑战：

   • 对于

     OR

     登录后复制
     条件，建立一个覆盖所有条件的复合索引通常是无效的。例如，对于

     WHERE col1 = 'A' OR col2 = 'B'

     登录后复制
     ，建立一个复合索引

     (col1, col2)

     登录后复制
     通常不会像

     AND

     登录后复制
     条件那样直接带来性能提升。因为索引的顺序性决定了它只能从最左边的列开始高效查找。如果你在查找

     col2 = 'B'

     登录后复制
     时，

     col1

     登录后复制
     的条件没有被满足，那么这个复合索引就无法被有效利用。
   • 只有在非常特殊的情况下，比如

     WHERE (col1 = 'A' AND col2 = 'B') OR (col1 = 'C' AND col2 = 'D')

     登录后复制
     ，这种形式的

     OR

     登录后复制
     可能可以通过多个复合索引的组合来优化，但这也回到了索引合并的复杂性。

3. 索引选择性：

   • 索引的选择性（Cardinality）也会影响

     OR

     登录后复制
     查询的性能。如果

     OR

     登录后复制
     条件中的某个子句涉及的列选择性很低（即该列的值重复很多），即使有索引，优化器也可能认为直接全表扫描反而更快，因为通过索引查找并回表（如果不是

以上就是MySQL如何取代OR_MySQL查询中OR条件优化与替代方案教程的详细内容，更多请关注php中文网其它相关文章！