Subquery Tuning

서브쿼리 동작 방식

filter

서브쿼리의 결과 건수가 몇건이건 상관 없이 전체 쿼리의 결과는 최대 메인쿼리 결과만큼나옴
메인 쿼리 수행 결과 만큼 서브쿼리를 실행함, 쿼리 순서는 메인쿼리 -> 서브쿼리 고정
메인 쿼리 실행 결과 건 수 + 서브쿼리로 input 되는 값 에 따라 경우의 수 나뉠 수 있음
- 메인 쿼리 실행 결과 건 수 많음 + input 되는 값이 unique 한 경우
- 메인 쿼리 실행 결과 건 수 적음 + input 되는 값이 unique 한 경우
- 메인 쿼리 실행 결과 건 수 많음 + input 되는 값이 unique 하지 않고 중복되는 값이 많음
- 2>3>1 순으로 성능 나올 것
- Filter 동작 방식은 메인쿼리의 실행 결과 건수가 많고 (조인 시도 횟수 증가),서브 쿼리에 제공하는 input 값의 종류가 많을 수록 성능이 좋지않음 (캐싱 효율 떨어짐)
  메인 쿼리의 결과 건수가 적거나,많더라도 서브 쿼리에 제공하는 input 값의 종류가 적을 수록 성능이 양호함

join

서브쿼리를 join 방식으로 바꾸면서 전체 결과 건 수가 달라질 수 있음, PK로 1:1 JOIN 되는 경우 상관없지만 1:M 관계라면 distinct를 추가할것
join 방식으로 변경하면서 쿼리 JOIN 순서도 제어 가능

서브쿼리 튜닝 유형

확인자 역할을 하는 테이블이 JOIN 으로 수행될 때

확인자 역할이란 FROM 절에 나오는 테이블 중 SELECT 절에서 사용되는 컬럼은 없고 단순히 WHERE절의 JOIN 조건이나, FILTER 조건으로만 사용되는 테이블을 뜻함
이러한 확인자 역할의 테이블을 JOIN으로 처리할 때 아래 케이스와 같은 경우 비효율이 발생할 수 있음

다른 테이블과 확인자 역할의 테이블이 1:M 관계라 JOIN을 수행할 때 데이터 건수를 증가시켜 결국 전체 조인 연결 시도횟수를 증가시키게 되는 경우
확인자 역할 테이블 자체가 느리게 풀리는 경우

select a.*,b.*
from tb_test1 a 
inner join tb_test2 b on a.id1=b.id1
inner join 
    (SELECT id1 id2, min(mod_ymdt)
        FROM   tb_test3
        GROUP  BY id1,id2) c on a.id2=c.id2
where a.id3='test'

튜닝 후

### in
select a.*,b.*
from tb_test1 a inner join tb_test2 b on a.id1=b.id1
where a.id3='test' 
and a.id2 in ( select id2 from tb_test3 )

### exists
select a.*,b.*
from tb_test1 a inner join tb_test2 b on a.id1=b.id1
where a.id3='test' 
and exists (
    SELECT id1, id2
    FROM   tb_test3
    where  id2=a.id2    
)

메인쿼리에 효율적인 조건이 없고 서브쿼리엔 좋은 조건이 있을 때

바로 위에서 JOIN 으로 풀리는 확인자 테이블을 in, exists 를 사용하는 서브쿼리로 수정했으나 아래와 같은 경우엔 오히려 서브쿼리를 JOIN으로 풀어줘야 성능이 향상됨

메인 쿼리에 서브쿼리 외에는 WHERE 조건이 없지만 서브쿼리에는 id1=1 같은 결과 건 수를 확 줄여줄 수 있는 조건이 있을 때

select a.*,b.*
from tb_test1 a inner join tb_test2 b on a.id1=b.id1
where exists (
    SELECT id1, id2
    FROM   tb_test3
    where  id2=a.id2    
    and id1=1
)
and exists (
    SELECT id1, id2
    FROM   tb_test4
    where  id2=a.id2    
)
and exists (
    SELECT id1, id2
    FROM   tb_test3
    where  id2=a.id2    
)

튜닝 후

select distinct a.*,b.*
from tb_test1 a inner join tb_test2 b on a.id1=b.id1
inner join (select distinct id1,id2 
            from tb_test3 
            where id1=1 ) c on a.id2=c.id2
where exists exists (
    SELECT id1, id2
    FROM   tb_test4
    where  id2=a.id2    
)
and exists (
    SELECT id1, id2
    FROM   tb_test3
    where  id2=a.id2    
)

=> subquery 를 JOIN 으로 변경했기 때문에 distinct 를 추가해야한다

minus

minus -> not exists 로 변경 시엔 데이터 정합성을 위해 select 절에 나열된 컬럼들이 unique한지 체크할 것
- distinct 를 추가하면 되긴 하지만 추가적으로 정렬 작업이 발생하기 때문에 피할 수 있으면 좋음

스칼라 서브쿼리 특징

스칼라 서브쿼리는 SELECT 절에 사용되는 서브쿼리로, 최대 반복 수행되는 횟수는 메인 쿼리의 결과 건수만큼임
스칼라 서브쿼리는 동일한 input에 대해서는 결과값을 캐싱하여 리턴하기 때문에 최초 1는 스칼라 서브쿼리를 직접 수행하게 되지만 그 이후의 동일한 값에 대해서는 실행을 스킵함
스칼라 서브쿼리의 결과값은 무조건 1건이거나 NULL 이어야함 => 메인 쿼리의 결과 건 수 에 영향을 미치지 않음
스칼라 서브쿼리를 JOIN 으로 변경할 때는 JOIN column은 distinct한 값을 가지기 위해 UNIQUE한 column 이어야 하며 LEFT OUTER JOIN으로 수행되어야함

스칼라 서브쿼리 튜닝 유형

스칼라 서브쿼리의 수행 위치

스칼라 서브쿼리는 메인 쿼리의 결과 건 수 만큼 실행되기 때문에 스칼라 서브쿼리의 수행위치에 크게 영향을 받음

SELECT c1,c2,c3, (SELECT t2.c3 FROM tb_test2 t2 WHERE t2.c1=t1.c1 ) as t2_c3
FROM tb_test t1
order by c1,c2
limit 10;

튜닝 후

SELECT a.*, (SELECT t2.c3 FROM tb_test2 t2 WHERE t2.c1=a.c1 ) as t2_c3
FROM (
    SELECT c1,c2,c3
    FROM tb_test t1
    order by c1,c2
    limit 10
) a

=>tb_test 의 건 수가 100건이라 할때 첫번째 쿼리는 스칼라 서브쿼리를 100번 수행하여 나온 결과를 정렬 후 10건 추출,
두번째 쿼리는 스칼라 서브쿼리를 10번만 수행하여 I/O 처리량에서 훨씬 효율적이게 됨

스칼라 서브쿼리를 JOIN 으로 변경

배치 프로그램처럼 데이터 건 수가 많은 경우엔 비례해서 스칼라 서브쿼리의 수행횟수도 많아지기 때문에 성능이 나오기 어려움
스칼라 서브쿼리를 JOIN으로 변경하여 HASH JOIN으로 유도하는 게 가장 효율적이나 MySQL에는 8.0.18 버전 부터 HASH JOIN을 지원하기 때문에 NL 조인으로 풀리도록 유도함
https://sarc.io/index.php/mariadb/1768-mysql-8-0-18-hash-join

변경 전

SELECT c1,c2,c3, (SELECT t2.c3 FROM tb_test2 t2 WHERE t2.c1=t1.c1 ) as t2_c3
FROM tb_test t1
order by c1,c2

변경 후

SELECT c1,c2,c3,t2.c3
FROM tb_test t1 LEFT OUTER JOIN tb_test2 t2 on t1.c1=t2.c1
order by c1,c2

변경 전

SELECT t1.EMPNO,
       t1.DEPT_NO
FROM   (SELECT E.EMPNO,
               (SELECT D.DEPTNO
                FROM   DEPT D
                WHERE  D.DEPTNO = E.DEPTNO) DEPT_NO
        FROM   EMP E) t1
WHERE  t1.DEPT_NO = 10 ;

=> view 안에 스칼라 서브쿼리 컬럼이 있고 이 컬럼을 밖에서 where 조건으로 사용하는 경우, 해당 컬럼에 인덱스가 있어도 인덱스를 탈 수 없음
t1.DEPT_NO 는 서브쿼리의 결과를 의미하기 때문에 가공된 컬럼처럼 동작함

변경 후

SELECT t1.EMPNO,
       t1.DEPT_NO
FROM   (SELECT E.EMPNO,
               D.DEPTNO DEPT_NO
        FROM   EMP  E LEFT OUTER JOIN DEPT D ON D.DEPTNO = E.DEPTNO
        ) t1
WHERE  DEPT_NO = 10 ;

=> view 안의 서브쿼리를 OUTER JOIN으로 풀었고 필요 시 view 밖의 DEPT_NO=10 조건을 view 안으로 밀어넣어 인덱스를 타도록 유도 가능

단 MySQL은 ORACLE과 달리 complex view(아래 조건이 포함된 view) 에 대해서는 위와 같은 view merging을 지원하지 않음

UNION
GROUP BY
DISTINCT
Aggregation
LIMIT
사용자 생성 변수
ORACLE에서는 GROUP BY, DISTINCT 을 포함해도 비용 비교 후 view merging을 할 수 있음
=> OUTER JOIN 으로 푼 것 보다 스칼라 서브쿼리가 더 빠를 수 있음

변경 전

mysql> explain
    -> SELECT (SELECT SUM(E_1.EMPNO) SUM_EMPNO FROM   EMP E_1 WHERE E_1.EMPNO = E.EMPNO) SUM_EMPNO_1,
    ->        E.HIREDATE,
    ->        E.DEPTNO
    -> FROM   EMP E
    -> WHERE      E.DEPTNO = 10;
+----+--------------------+-------+------------+--------+---------------+-----------+---------+--------------+------+----------+-------------+
| id | select_type        | table | partitions | type   | possible_keys | key       | key_len | ref          | rows | filtered | Extra       |
+----+--------------------+-------+------------+--------+---------------+-----------+---------+--------------+------+----------+-------------+
|  1 | PRIMARY            | E     | NULL       | ref    | FK_DEPTNO     | FK_DEPTNO | 2       | const        |    3 |   100.00 | NULL        |
|  2 | DEPENDENT SUBQUERY | E_1   | NULL       | eq_ref | PRIMARY       | PRIMARY   | 2       | test.E.EMPNO |    1 |   100.00 | Using index |
+----+--------------------+-------+------------+--------+---------------+-----------+---------+--------------+------+----------+-------------+

변경 후

mysql> explain
    -> SELECT T_1.SUM_EMPNO SUM_EMPNO_1,
    ->        E.HIREDATE,
    ->        E.DEPTNO
    -> FROM   EMP E LEFT OUTER JOIN
    ->         (SELECT E_1.EMPNO, SUM(E_1.EMPNO) SUM_EMPNO
    ->         FROM   EMP E_1
    ->         GROUP BY E_1.EMPNO) T_1 on E.EMPNO = T_1.EMPNO
    -> WHERE      E.DEPTNO = 10;
+----+-------------+------------+------------+-------+-------------------+-------------+---------+--------------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys     | key         | key_len | ref          | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+-------------------+-------------+---------+--------------+------+----------+-------------+
|  1 | PRIMARY     | E          | NULL       | ref   | FK_DEPTNO         | FK_DEPTNO   | 2       | const        |    3 |   100.00 | NULL        |
|  1 | PRIMARY     | <derived2> | NULL       | ref   | <auto_key0>       | <auto_key0> | 2       | test.E.EMPNO |    2 |   100.00 | NULL        |
|  2 | DERIVED     | E_1        | NULL       | index | PRIMARY,FK_DEPTNO | PRIMARY     | 2       | NULL         |   14 |   100.00 | Using index |
+----+-------------+------------+------------+-------+-------------------+-------------+---------+--------------+------+----------+-------------+

=> 집계함수를 포함한 스칼라 서브쿼리를 LEFT JOIN 으로 변경하면서 inline view에 group by가 포함됨
group by 를 포함한 inline view는 view merging이 불가능하여 id=2 DERIVED 로 풀려 index 풀스캔을 하여 임시테이블을 생성한 뒤 조인하게 됨
근데 ORACLE처럼 view merging 실패하면 E.DEPTNO=10 조건이 자동으로 inline view 안으로 pushdown 될 줄 알았는데 이것도 안되네..?
OUTER JOIN하는 inline view 안에 group by 가 포함되어있으면 스칼라 조인으로 변환시키는 게 성능 상 이득일 수 있음

Subquery Tuning#

서브쿼리 동작 방식#

filter#

join#

서브쿼리 튜닝 유형#

확인자 역할을 하는 테이블이 JOIN 으로 수행될 때#

튜닝 후#

메인쿼리에 효율적인 조건이 없고 서브쿼리엔 좋은 조건이 있을 때#

튜닝 후#

minus#

스칼라 서브쿼리 특징#

스칼라 서브쿼리 튜닝 유형#

스칼라 서브쿼리의 수행 위치#

튜닝 후#

스칼라 서브쿼리를 JOIN 으로 변경#

변경 전#

변경 후#

변경 전#

변경 후#

단 MySQL은 ORACLE과 달리 complex view(아래 조건이 포함된 view) 에 대해서는 위와 같은 view merging을 지원하지 않음#

변경 전#

변경 후#

Subquery Tuning

서브쿼리 동작 방식

filter

join

서브쿼리 튜닝 유형

확인자 역할을 하는 테이블이 JOIN 으로 수행될 때

튜닝 후

메인쿼리에 효율적인 조건이 없고 서브쿼리엔 좋은 조건이 있을 때

튜닝 후

minus

스칼라 서브쿼리 특징

스칼라 서브쿼리 튜닝 유형

스칼라 서브쿼리의 수행 위치

튜닝 후

스칼라 서브쿼리를 JOIN 으로 변경

변경 전

변경 후

변경 전

변경 후

단 MySQL은 ORACLE과 달리 complex view(아래 조건이 포함된 view) 에 대해서는 위와 같은 view merging을 지원하지 않음

변경 전

변경 후