SQL WHERE 절의 IN vs OR

SQL WHERE 절의 IN vs OR

대용량 데이터베이스를 처리할 때 성능이 향상됩니다.IN또는ORSQL에서WHERE조항?

그들이 실행되는 방식에 어떤 차이가 있습니까?

다음의 성능 차이를 알고 싶어 하는 것으로 가정합니다.

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% 느립니다.항을 더 추가하면 차이가 더 커집니다.결과는 다른 데이터베이스와 다른 데이터에 따라 다를 수 있습니다.

가장 좋은 방법은 실행 계획을 살펴보는 것입니다.

---

오라클과 함께 사용해 봤는데, 정확히 똑같았습니다.

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

OR 연산자는 IN과 같은 조건뿐만 아니라 여러 조건을 허용하기 때문에 IN 구조보다 훨씬 복잡한 평가 프로세스가 필요합니다.

다음은 OR과 함께 사용할 수 있지만 IN과 호환되지 않는 항목의 목록입니다. 더 큰 항목, 더 큰 항목 또는 더 큰 항목, 더 적은 항목, 더 적은 항목, 더 적은 항목, 더 적은 항목, 더 많은 항목, 즉 REGEXP_LIKE와 유사한 항목입니다.또한 조건이 항상 동일한 값을 비교하지 않을 수도 있습니다.

쿼리 최적화 도구의 경우 여러 조건에서 OR 연산자를 정의하고 = 연산자를 동일한 값으로 정의하는 구성이므로 IN 연산자를 관리하는 것이 더 쉽습니다.OR 연산자를 사용하는 경우 옵티마이저는 항상 동일한 값에 = 연산자를 사용한다고 생각하지 않을 수 있으며, 더 깊고 복잡한 작업을 수행하지 않는 경우 관련된 모든 조건에서 동일한 값에 대해 = 연산자만 있을 수 있다는 것을 배제할 수 있습니다.이미 언급한 이진 검색과 같이 최적화된 검색 방법을 배제합니다.

[EDIT] Optimizer가 최적화된 IN 평가 프로세스를 구현하지 못할 수도 있지만, 데이터베이스 버전 업그레이드를 통해 한 번만 수행할 수 있다는 점도 배제할 수 없습니다.따라서 최적화된 OR 연산자를 사용하는 경우에는 정교한 작업이 사용되지 않습니다.

오라클은 효율성이 낮은 것(어느 것이든)을 다른 것으로 전환할 수 있을 정도로 충분히 똑똑하다고 생각합니다.그래서 저는 답이 오히려 각각의 가독성에 달려 있어야 한다고 생각합니다.IN명백한 승리)

Postgre에 대한 정보를 추가하겠습니다.SQL 버전 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

OR비교할 값이 적을 때 (가독성 관점에서) 의미가 있습니다.IN소스가 때 합니다.값을 비교할 동적 소스가 있는 경우.

다른 대안은 다음을 사용하는 것입니다.JOIN임시 테이블이 있는
필요한 인덱스가 있다면 성능에 문제가 없어야 한다고 생각합니다.

IN 연산자를 사용하더라도 MS SQL 서버는 자동으로 OR 연산자로 변환합니다.분석을 해보면 실행 계획을 확인할 수 있습니다.따라서 OR 연산자 목록이 길면 사용하는 것이 좋습니다.적어도 몇 나노초의 작업 시간을 절약할 수 있습니다.

저는 많은 OR(350)에서 SQL 쿼리를 했습니다.포스트그레스는 437.80ms를 수행합니다.

이제 IN 사용:

23.18ms

언급URL : https://stackoverflow.com/questions/3074713/in-vs-or-in-the-sql-where-clause