하위 쿼리를 사용하는 쿼리는 하위 쿼리 대신 고정 데이터가 있는 동일한 쿼리보다 더 긴 시간이 필요합니다.

하위 쿼리를 사용하는 쿼리는 하위 쿼리 대신 고정 데이터가 있는 동일한 쿼리보다 더 긴 시간이 필요합니다.

결합 쿼리

select a, b from A where a > 5 and b in (select b from B where c = "some")

고정 쿼리보다 약 30배 더 오래 걸립니다.

select a, b from A where a > 5 and b in (1, 2, 3)

그럼에도 불구하고.

• select b from B where c = "some"고정 쿼리에 사용된 행 집합과 정확히 동일한 결과를 가져옵니다.(1, 2, 3)
• select b from B where c = "some"실행하는 데만 0.01초가 걸립니다.
• select a, b from A where a > 5실행하는 데 0.3초가 걸립니다.

A에 지수가 (a,b)를 넘습니다.

결합된 쿼리 분석:

analyze select a, b from A where a > 5 and b in (select b from B)
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: A
         type: range
possible_keys: idx_a_b
          key: idx_a_b
      key_len: 8
          ref: NULL
         rows: 126459
       r_rows: 66181.00
     filtered: 100.00
   r_filtered: 100.00
        Extra: Using index condition; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: B
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: A.b
         rows: 1
       r_rows: 1.00
     filtered: 100.00
   r_filtered: 0.09
        Extra: Using where

r_rows = 66181이 일치함에 유의하십시오.select a, b from A where a > 5.

MariaDB는 인덱스의 a 부분만 사용하고 b 부분은 무시하는 것 같습니다. 첫 번째 단계에서 하위 쿼리에서 얻을 수 있어야 합니다.확장된 설명은 MariaDB가 내 쿼리를 대체하는 것을 보여줍니다.

select b, a from B join A where ((B.b = A.b) and (A.a > 5) and (B.c = "some"))

이상한 것은, 서브쿼리가 반환하는 고정 집합 (1, 2, 3)이 주어졌을 때, 서브쿼리 자체 대신 마리아DB가 인덱스의 a와 b를 동시에 사용하는 것처럼 보인다는 것입니다.

analyze select a, b from A where a > 5 and y in (1, 2, 3)
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: A
         type: range
possible_keys: idx_a_b
          key: idx_a_b
      key_len: 10
          ref: NULL
         rows: 126459
       r_rows: 59.00
     filtered: 100.00
   r_filtered: 100.00
        Extra: Using index condition; Using temporary; Using filesort

r_rows = 59는 두 쿼리(결합된 쿼리와 고정된 쿼리)의 결과 집합 크기와 일치합니다.

A의 인덱스에서 a와 subquery의 b를 모두 사용하여 MariaDB가 고정 쿼리와 동일한 쿼리 계획을 사용하도록 하려면 어떻게 해야 합니까?

쿼리가 서로 다르게 최적화되는 경우도 있습니다.쿼리 플래너는 고정된 값 목록을 가지고 있을 때 사용자가 무엇을 하고 있는지에 대해 훨씬 더 많이 알고 있습니다.

중복 사항이 없는 경우b,그리고나서join종종 좋은 실행 계획을 만듭니다.

select a.a, a.b
from a join
     b
     on a.b = b.b
where a.a > 5;

저는 또한 시도해보는 것을 추천합니다.exists대신:

select a, b
from A
where a > 5 and
      exists (select 1 from B where b.b = a.b) ;

그리고 당신은 당신의 컴퓨터에 대한 인덱스를 가지고 있어야 합니다.b(b):

create index idx_b_b on b(b);

B vs b, A vs a를 다루려다 머리가 멍해져서 테이블 이름을 바꿨습니다.

select  Y.b, Y.a
    from  X
    join  Y
    where  ((X.b = Y.b)
       and  (Y.a > 5)
       and  (X.c = "some")
           )

그 쿼리는 필요합니다.

X:  INDEX(c, b)
Y:  INDEX(a, b), INDEX(b, a)

이렇게 하면 Optimizer는 어떤 테이블로 시작하든 상관없이 효율적으로 사용할 수 있습니다.Optimizer(옵티마이저)는 다음에 대한 통계를 바탕으로 시작하기에 더 나은 표를 선택합니다.a그리고.c. 그러나 "histograms(히스토그램)"(MariaDB 10.0 - 10.3; 10.4는 기본적으로 해당 히스토그램이 켜져 있음)을 켜지 않는 한 Optimizer에 전체 내용이 표시되지 않을 수 있습니다.

언급URL : https://stackoverflow.com/questions/56621434/query-using-subquery-needs-longer-than-same-query-with-fixed-data-instead-of-sub