SQLWHERE 子句中的 IN 与 OR

在处理大型数据库时，哪个性能更好: SQLWHERE子句中的 IN还是 OR？

有没有关于他们执行方式的 不同？

155383 次浏览
小开

当需要比较的值较少时，OR是有意义的(从可读性的角度来看)。 IN是非常有用的，特别是当你有一个动态源，你想要的值进行比较。

另一种选择是使用带有临时表的 JOIN。
我不认为性能应该是一个问题，只要您有必要的索引。

小开

找到答案的最好方法是查看执行计划。

我用 神使试过，结果一模一样。

CREATE TABLE performance_test AS ( SELECT * FROM dba_objects );


SELECT * FROM performance_test
WHERE object_name IN ('DBMS_STANDARD', 'DBMS_REGISTRY', 'DBMS_LOB' );

即使查询使用 IN，执行计划说它使用 OR:

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |     8 |  1416 |   163   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| PERFORMANCE_TEST |     8 |  1416 |   163   (2)| 00:00:02 |
--------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


1 - filter("OBJECT_NAME"='DBMS_LOB' OR "OBJECT_NAME"='DBMS_REGISTRY' OR
"OBJECT_NAME"='DBMS_STANDARD')
小开
最佳答案

我假设您想知道以下两者之间的性能差异:

WHERE foo IN ('a', 'b', 'c')
WHERE foo = 'a' OR foo = 'b' OR foo = 'c'

如果值为常数，则根据 MySQL 手册对列表进行 IN排序，然后使用二进制搜索。我可以想象，OR对它们逐个进行评估，没有特定的顺序。所以 IN在某些情况下更快。

了解这一点的最佳方法是在数据库中同时分析特定数据，以查看哪一种方法更快。

我在一个有1000000行的 MySQL 上都试过了。当对列进行索引时，性能没有明显的差异——两者几乎都是即时的。当列没有被索引时，我得到了以下结果:

SELECT COUNT(*) FROM t_inner WHERE val IN (1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000);
1 row fetched in 0.0032 (1.2679 seconds)


SELECT COUNT(*) FROM t_inner WHERE val = 1000 OR val = 2000 OR val = 3000 OR val = 4000 OR val = 5000 OR val = 6000 OR val = 7000 OR val = 8000 OR val = 9000;
1 row fetched in 0.0026 (1.7385 seconds)

所以在这种情况下，使用 OR 的方法要慢30% 。添加更多的术语会使差异变得更大。其他数据库和其他数据的结果可能有所不同。

小开

我认为甲骨文足够聪明，可以将效率较低的那个(无论是哪个)转换成另一个。因此，我认为答案应该取决于每种方法的可读性(我认为 IN显然胜出)

小开

OR 运算符需要比 IN 结构更复杂的求值过程，因为它允许许多条件，而不仅仅是类似于 IN 的等式。

这里有一个类似的例子，你可以使用 OR，但是与 IN 不兼容: 更大的或等于的，更小的，更小的或等于的。 此外，考虑到条件可能并不总是比较相同的值。

对于查询优化器来说，更容易管理 IN 运算符，因为它只是一个在多个条件上定义 OR 运算符的构造，而 = 运算符则在同一个值上。如果你使用 OR 操作符，优化器可能不会认为你总是在同一个值上使用 = 操作符，如果它不执行更深入和更复杂的细化，它可能会排除在所有涉及的条件下可能只有 = 操作符相同的值，随之而来的优化搜索方法，如已经提到的二进制搜索。

[编辑] 优化器可能不会实现优化的 IN 评估过程，但这并不排除这种情况可能发生一次(通过数据库版本升级)。因此，如果您使用 OR 运算符，优化的细化将不会在您的情况下使用。

小开

我在大量 OR (350)中做了一个 SQL 查询，Postgres 做的是 437.80毫秒。

Use OR

现在使用 IN:

Use IN

23.18毫秒

小开

我将为 PostgreSQL版本11.8(发布2020-05-14)添加信息。

IN可能明显更快，例如带有 ~ 23M 行的表。

使用 OR查询:

explain analyse select sum(mnozstvi_rozdil)
from product_erecept
where okres_nazev = 'Brno-město' or okres_nazev = 'Pardubice';


-- execution plan
Finalize Aggregate  (cost=725977.36..725977.37 rows=1 width=32) (actual time=4536.796..4540.748 rows=1 loops=1)
->  Gather  (cost=725977.14..725977.35 rows=2 width=32) (actual time=4535.010..4540.732 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Partial Aggregate  (cost=724977.14..724977.15 rows=1 width=32) (actual time=4519.338..4519.339 rows=1 loops=3)
->  Parallel Bitmap Heap Scan on product_erecept  (cost=15589.71..724264.41 rows=285089 width=4) (actual time=135.832..4410.525 rows=230706 loops=3)
Recheck Cond: (((okres_nazev)::text = 'Brno-město'::text) OR ((okres_nazev)::text = 'Pardubice'::text))
Rows Removed by Index Recheck: 3857398
Heap Blocks: exact=11840 lossy=142202
->  BitmapOr  (cost=15589.71..15589.71 rows=689131 width=0) (actual time=140.985..140.986 rows=0 loops=1)
->  Bitmap Index Scan on product_erecept_x_okres_nazev  (cost=0.00..8797.61 rows=397606 width=0) (actual time=99.371..99.371 rows=397949 loops=1)
Index Cond: ((okres_nazev)::text = 'Brno-město'::text)
->  Bitmap Index Scan on product_erecept_x_okres_nazev  (cost=0.00..6450.00 rows=291525 width=0) (actual time=41.612..41.612 rows=294170 loops=1)
Index Cond: ((okres_nazev)::text = 'Pardubice'::text)
Planning Time: 0.162 ms
Execution Time: 4540.829 ms

使用 IN查询:

explain analyse select sum(mnozstvi_rozdil)
from product_erecept
where okres_nazev in ('Brno-město', 'Pardubice');


-- execution plan
Aggregate  (cost=593199.90..593199.91 rows=1 width=32) (actual time=855.706..855.707 rows=1 loops=1)
->  Index Scan using product_erecept_x_okres_nazev on product_erecept  (cost=0.56..591477.07 rows=689131 width=4) (actual time=1.326..645.597 rows=692119 loops=1)
Index Cond: ((okres_nazev)::text = ANY ('{Brno-město,Pardubice}'::text[]))
Planning Time: 0.136 ms
Execution Time: 855.743 ms

提交答案