使用OR子句查询的查询时间过长，但是它们的组成部分很快 [英] Query takes too long to query with OR clause but their parts are very quick

问题描述

我有两个表，其中的〜1M行由其ID索引。

休闲查询...

  SELECT t。*从事务t 
内部联接将其集成到它上.id_trans = t.id_trans 
 W.T.id_trans ='5440073'
或it.id_integration ='439580587'
  

此查询大约需要30秒。但是...

  SELECT ... WHERE t.id_trans ='5440073'
  

花费不到100毫秒，并且

  SELECT。 .. it.id_integration ='439580587'
  

也花费不到100毫秒。甚至

  SELECT ... WHERE t.id_trans ='5440073'
 UNION 
 SELECT ... it.id_integration ='439580587'
  

花费不到100毫秒

为什么 OR 子句为什么要花费那么多时间，即使零件很快呢？

解决方案

为什么 OR 这么慢，但是 UNION 这么快？ / p>

您了解为什么 UNION 快速吗？因为它可以使用两个单独的索引来发挥良好的优势，并且从 UNION 的每个部分收集一些结果行，然后将结果组合在一起。

但是为什么不能 OR 这样做呢？简而言之，Optimizer不够聪明，无法尝试该角度。

在您的情况下，测试位于不同的表上。这会导致对 UNION 的两个部分截然不同的查询计划（请参见 EXPLAIN SELECT ... ）。

假定每个零件仅传递几行，则随后的 UNION 是次要的-即收集两行小的行，对它们进行重复处理（如果您使用 UNION DISTINCT 而不是 UNION ALL ）并提供结果。

同时， OR 查询有效地收集了这两个表 then 根据 OR 的两个部分过滤掉了。中间阶段可能涉及一个巨大的临时表，只是要扔掉大多数行。

（inflate-deflate的另一个示例是 JOINs + GROUP BY 。解决方法不同。）

I have two tables with ~1M rows indexed by their Id's.

the fallowing query...

SELECT t.* FROM transactions t
INNER JOIN integration it ON it.id_trans = t.id_trans
WHERE t.id_trans = '5440073'
OR it.id_integration = '439580587'

This query takes about 30s. But ...

SELECT ... WHERE t.id_trans = '5440073'

takes less than 100ms and

SELECT ... WHERE it.id_integration = '439580587'

also takes less than 100ms. Even

SELECT ... WHERE t.id_trans = '5440073'
UNION
SELECT ... WHERE it.id_integration = '439580587'

takes less then 100ms

Why does the OR clause takes so much time even if the parts being so fast?

解决方案

Why is OR so slow, but UNION is so fast?

Do you understand why UNION is fast? Because it can use two separate indexes to good advantage, and gather some result rows from each part of the UNION, then combine the results together.

But why can't OR do that? Simply put, the Optimizer is not smart enough to try that angle.

In your case, the tests are on different tables; this leads to radically different query plans (see EXPLAIN SELECT ...) for the two parts of the UNION. Each can be well optimized, so each is fast.

Assuming each part delivers only a few rows, the subsequent overhead of UNION is minor -- namely to gather the two small sets of row, dedup them (if you use UNION DISTINCT instead of UNION ALL), and deliver the results.

Meanwhile, the OR query effectively gather all combinations of the two tables, then filtered out based on the two parts of the OR. The intermediate stage may involve a huge temp table, only to have most of the rows tossed.

(Another example of inflate-deflate is JOINs + GROUP BY. The workarounds are different.)

这篇关于使用OR子句查询的查询时间过长，但是它们的组成部分很快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！