RDB Semi Join을 이용한 성능 최적화

Table 구조

CREATE TABLE `food` (
   `food_id` bigint NOT NULL AUTO_INCREMENT,
   `create_date` datetime(6) DEFAULT NULL,
   `last_modified_date` datetime(6) DEFAULT NULL,
   `food_status` varchar(255) NOT NULL,
   `food_title` varchar(255) NOT NULL,
   `number_of_likes` bigint NOT NULL,
   `price` int NOT NULL,
   `review_msg` varchar(255) DEFAULT NULL,
   `writer_nickname` varchar(50) DEFAULT NULL,
   `category_id` bigint DEFAULT NULL,
   `writer_id` bigint DEFAULT NULL,
   PRIMARY KEY (`food_id`),
   KEY `FK4d8yngcquhwsr9n8qv62ch09e` (`writer_id`),
   KEY `idx_food_search_id` (`category_id`,`food_status`,`food_id`)
 ) ENGINE=InnoDB;


CREATE TABLE `food_flavor` (
   `food_flavor_id` bigint NOT NULL AUTO_INCREMENT,
   `create_date` datetime(6) DEFAULT NULL,
   `last_modified_date` datetime(6) DEFAULT NULL,
   `flavor_id` bigint NOT NULL,
   `food_id` bigint NOT NULL,
   PRIMARY KEY (`food_flavor_id`),
   KEY `FKdhh0jr1nbbdmbkwpt75sk55hn` (`flavor_id`),
   KEY `FK28j3quols8wwo2qtt69t0nfoc` (`food_id`)
 ) ENGINE=InnoDB;

 CREATE TABLE `flavor` (
   `flavor_id` bigint NOT NULL AUTO_INCREMENT,
   `create_date` datetime(6) DEFAULT NULL,
   `last_modified_date` datetime(6) DEFAULT NULL,
   `flavor_type` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`flavor_id`),
   UNIQUE KEY `UK91m3arw1w99hcdn5u9638bb3p` (`flavor_type`)
 ) ENGINE=InnoDB;

구조

• food_flavor 라는 중간테이블을 두는 N : M 관계 입니다.
• 실제 Spring Entity 는 Food, FoodFlavor, Flavor로 3개로 구현 되어있습니다.

요구사항 과 이슈

요구사항

• category_id 가 1인 food 조회
• food를 조회할때 내가 선택한 flavor를 가지고 있는 food 정보만 조회 하고 싶을 경우
  • flavor 의 id는 1,2 입니다.
• 조회 결과 food 만 반환 하기
• pagination 필요

이슈

• food <> food_flavor 테이블이 join이 이뤄지므로 food 에 중복 row 수가 증가 하였습니다.
• query

select food.* 
from food left outer join food_flavor 
    on food.food_id = food_flavor.food_id 
where food.category_id = 1 and food_flavor.flavor_id in (1,2);

• 결과
  

  

  쿼리 결과

중복 제거 방식

• distinct, group by

Group By

• query

select food.*
from food left outer join food_flavor 
    on food.food_id = food_flavor.food_id
    where food.category_id = 1
    group by food.food_id;

explain 결과

• 현재 index가 일부 선언이 안되어 있으므로 food flavor에서 filtered에서 7.70이라는 결과가 나왔지만, 이 부분은 지금 중요한 것이 아니기 때문에 무시 합니다.
• 중요한 부분은 food 에 Extra에 적혀져 있는 Using temporary 입니다.
  • query 처리를 위해서 임시 테이블을 생성하는 의미를 담고있습니다.
• 짧게 설명 드리면 모든 조건이 만족하는 food 정보를 뽑아 낸 후, group by 를 위한 임시 테이블을 생성해 추가 작업을 진행한다는 뜻입니다.
• 많은 메모리 소스를 사용하기 때문에 성능에 이슈가 발생할 수 있습니다.
• 데이터가 많을 수록 slow 쿼리가 된다는 뜻입니다.

Distinct

• query

select distinct food.*
from food left outer join food_flavor 
    on food.food_id = food_flavor.food_id
where food.category_id = 1 and food_flavor.flavor_id in (1,2);

• 결과

• 여기서도 동일하게 temporary라고 적혀져 있는 것을 확인할 수 있습니다.

해결법

Semi Join

Outer Join 후 distinct와 order by 를 사용한 경우

outer join 하는 경우

• 위 사진에서보면 driving table인 food가 food_flavor 테이블에 조인을 완료합니다. 그리고 join이 완성된 테이블에 flavor_id가 1과 2인 값을 추려 내는 과정을 가집니다.
• 하지만 실행 순서가 조인이 where 보다 빠르기 때문에 어쩔 수 없게 의미 없는 flavor_id가 3,4인 데이터까지 join에 사용될 수 있습니다.

Semi Join 사용

• 정의 : subquery와 main query의 연결을 위한 유사 join query문 입니다.
• 특징
  • optimizer 전략에 따라서 여러 최적화 방식이 사용 됩니다 (firstMatch/pullout/Materializaion ...etc) -> 아래에서 자세하게 설명하겠습니다
  • subquery에서 join에 필요한 데이터를 먼저 추려 내는 과정을 거칩니다.
  • 중복 제거를 할 수 있습니다(Duplicate Weedout)
  • 현재 저의 프로젝트에서는 firstMatch를 사용 하고 있습니다
  • mysql 5.5버전 이하인 경우 subquery 최적화가 되지 못하였기 때문에 subquery를 사용하면 inner join으로 변경 됩니다
  • cf) mysql 5.5 버전 이하인경우 실제 subquery로 실행 되면 엄청난 성능 이슈가 발생합니다(outer query 실행후 driving query를 모두 실행하는 것과 같이 N * M의 성능이 발생할 수 있습니다
  • mysql 5.6 이상인 경우에는 optimizer 기법들일 나와서 subquery에 대한 성능 개선이 이뤄 졌기 때문에 사용해도 됩니다
• 위 내용은 너무 어렵게 개념을 설명했다고 판단되어 아래에 예시를 들어서 풀어 내도록하겠습니다.
• 위 예시와 동일한 요구사항을 semi join 으로 풀어낸 query 입니다.

select food.* 
from food 
    where food.food_id in ( 
        select foodflavor1_.food_id 
        from food_flavor foodflavor1_ 
            where foodflavor1_.food_id=food.food_id 
                and food.category_id=1 
                and foodflavor1_.flavor_id in (1,2) 
    );

• food_id를 in절을 사용해 food 정보를 반환 합니다 그렇기 때문에 food에 대해서 중복된 값이 반환 하지 않는 다는 것을 알 수 있습니다
  • select food.* from food where food_id in (1,2,3); 과 동일하다고 보입니다. 하지만 실제 최적화 과정에서 inner join으로 변경/(1,2,3) 중 food_id가 1/2/3 중 1개라도 동일한 값이 존재할 때 바로 멈추는 FirstMatch/임시 테이블을 사용한 Materializaion방식 등으로 변경 됩니다
• where 절에서 subquery가 존재하기 때문에 from 절의 table 을 사용할 수 있습니다.(실행 순서가 from이 where 보다 높기 때문)

select
    foodflavor1_.food_id 
from
    food_flavor foodflavor1_ 
where
    foodflavor1_.food_id=food.food_id 
    and food.category_id=1
    and foodflavor1_.flavor_id in (1,2)

• 처음에 subquery에서 내가 조회 하고 싶은 flavor_id가 1과 2를 가지는 food_flavor 정보를 filter 할 수 있습니다.
• food_flavor중에서 flavor_id가 1,2인 데이터를 먼저 추출 하게 됩니다. -> join전에 join할 row 수를 줄이기 위해서
• 앞에 예시에서는 food_id가 1인 데이터가 join이 이뤄지는 row수는 4개 였지만 지금은 2개 입니다.
• 그리고 subquery에 food를 사용해 foodflavor1_.food_id=food.food_id을 where 조건을 추가 해줍니다 -> 유사 join 정보를 추가
• 그리고 outer table에서 where 절로 in 연산을 통해서 중복을 제거 하기 때문에 group by와 distinct와 같은 추가 메모리를 소비하는 경우를 방지할 수 있습니다

Semi Join 성능 최적화 방식

SELECT *
FROM employees e
WHERE e.emp_no IN (SELECT de.emp_no FROM dept_emp de WHERE de.dept_no = 'd009')

-- employees.emp_no: fk
-- dept_emp.emp_no: pk

pullout 방식

정의

• semi join의 subquery를 inner join으로 변형 해서 최적화 하는 방식 입니다

explain 결과

select employees.*
from `employees`.`dept_emp` `de
    join `employees`.`employees` `e`
        where ( (`employees`.`e`.`emp_no` = `employees`.`de`.`emp_no`) and   (`employees`.`de`.`dept_no` = 'd009') ); 

# 5.5 버전
> EXPLAIN SELECT * FROM employees e WHERE e.emp_no IN (SELECT de.emp_no FROM dept_emp de WHERE de.dept_no = 'd009');
+------+--------------------+-------+--------+---------+------------+--------+--------------------------+
| id   | select_type        | table | type   | key     | ref        | rows   | Extra                    |
+------+--------------------+-------+--------+---------+------------+--------+--------------------------+
|    1 | PRIMARY            | e     | ALL    | NULL    | NULL       | 300252 | Using where              |
|    2 | DEPENDENT SUBQUERY | de    | eq_ref | PRIMARY | const,func |      1 | Using where; Using index |
+------+--------------------+-------+--------+---------+------------+--------+--------------------------+

# 5.6 버전
+------+-------------+-------+--------+---------------------------+---------+---------+---------------------+-------+--------------------------+
| id   | select_type | table | type   | possible_keys             | key     | key_len | ref                 | rows  | Extra                    |
+------+-------------+-------+--------+---------------------------+---------+---------+---------------------+-------+--------------------------+
|    1 | PRIMARY     | de    | ref    | PRIMARY,ix_empno_fromdate | PRIMARY | 12      | const               | 46914 | Using where; Using index |
|    1 | PRIMARY     | e     | eq_ref | PRIMARY                   | PRIMARY | 4       | employees.de.emp_no |     1 |                          |
+------+-------------+-------+--------+---------------------------+---------+---------+---------------------+-------+--------------------------+

실행 계획 설명

1. 5.5버전

• subquery에 대해서 최적화가 되지 않은 상태입니다
• inner join이 아닌 outer table에서 fullscan한 후 subquery를 실행 하는 방식입니다
• N * M 같이 성능이 괭장히 안좋아 지는 케이스 입니다

2. 5.6 버전

• subquery에 대해서 성능 최적화가 되어 subquery 성능이 일부 개선 되었습니다
• firstmatch 옵션의 off일 경우 그리고 subquery table에 대해서 index가 선언될경우 사용됩니다

FirstMatch 최적화

정의

• Main query의 filtering 효율이 좋을 경우 사용 되는 케이스 입니다
• 현재 저의 프로젝트에서 사용하는 옵티마이저 기법입니다
• subquery에 대허서 1건의 데이터가 일치하는 경우 바로 subquery실행을 중지하고 결과를 반환하는 join 기법 입니다
• 1건의 검색하고 멈추는 단축 실행 경로를 사용합니다

explain 결과

explain select food0_.*, user1_.*
    from
        food food0_ 
    left outer join
        user user1_ 
            on food0_.writer_id=user1_.user_id 
    where
        food0_.food_id in (
            select -- first match 위치
                foodtag2_.food_id 
            from
                food_tag foodtag2_ 
            where
                foodtag2_.food_id=food0_.food_id 
                and (
                    foodtag2_.ingredient_type in ("MAIN", "ADD")
                ) 
                and food0_.create_date<= "2022-11-11 00:00:00"
	        and food0_.category_id=6
	        and foodtag2_.tag_id=1
	        and food0_.report_status="NORMAL"
	        and food0_.food_status="SHARED"
        )     
    order by
        food0_.price desc limit 10000,  10;

IN-to-EXISTS 보다 FirstMatch가 좋은점

• 동등 조건 전파가 subquery에서 가능하던게 main query에서도 가능하게됩니다
• first match 방식을 사용할지 in-to-exist방식을 사용할지 optimizer에서 맞길 수 있습니다

cf) 용어설명

• 동등 조건 전파: where col1 < col2 and col2 = 15일 경우 where col1 < 15로 치환할 수 있는 것 -> 동일한 조건에 대해서 생략이 가능한 것입니다
• in-to-exist: in query를 exist 쿼리로 변경해서 질의 하는 것을 의미 합니다

FistMatch의 제약 사항

• outer query를 조회 후 subquery를 실행합니다
• sub query에 group by 와 같은 기능이 추가될 경우 first match를 사용할 수 없습니다 -> grouping을 하게 되면 subquery에 대해서 전체를 탐색해 grouping을 맺어야 하기 때문입니다
• main query의 독립적으로 index를 가지고 있어야합니다 -> filter조건이 절적하지 않을 경우 fullscan이 이뤄지게 됩니다 이경우 Materializaion방식을 사용하는게 더 이득일 경우가 있습니다

Materializaion 최적화

정의

• Materializaion라는 임시 테이블을 생성해서 subquery결과를 담습니다 그리고 inner join을 사용해서 결과를 도출하는 방식입니다expalin 전략

 set optimizer_switch='FirstMatch=off';
 explain select food0_.*, user1_.*
    from
        food food0_ 
    left outer join
        user user1_ 
            on food0_.writer_id=user1_.user_id 
    where
        food0_.food_id in (
            select
                foodtag2_.food_id 
            from
                food_tag foodtag2_ 
            where
                foodtag2_.food_id in (1,2,3,4)
        ) 
    order by
        food0_.price desc limit 10000,  10;

실행 처리 결과

제약 조건 및 특징

• subquery는 상관서브쿼리가 아니어야 합니다(비상관 쿼리)
• subquery에 group by 사용해도 됩니다 -> subquery결과를 임시테이블에 저장하기 때문에 gorup by결과를 임시 table에 저장 합니다
• 임시 테이블 사용하기 때문에 DBMS에 메모리 부하가 걸리게 됩니다

상관 서브쿼리(Materializaion 미사용)

 explain select food0_.*, user1_.*
    from
        food food0_ 
    left outer join
        user user1_ 
            on food0_.writer_id=user1_.user_id 
    where
        food0_.food_id in (
            select
                foodtag2_.food_id 
            from
                food_tag foodtag2_ 
            where
                foodtag2_.food_id = food0_.food_id -- 상관 쿼리
        ) 
    order by
        food0_.price desc limit 10000,  10;

Materializaion 최적화 전략

• Scan: 구체화된 임시 테이블 full scan 하는 방식(index가 없을 경우)
• Lookup: 구체화된 임시 테이블에 distinct_key index를 사용하는 경우

cf) 용어 정리

• 상관 쿼리: main query의 column을 subquery에서 사용하는 것
• 비상관 쿼리: subquery에 main query column이 존재하지 않는 것을 의미 합니다

LooseScan 최적화

정의

• semi join에서 모든 record를 읽지 않고 조건에 사용된 index를 유니크 한 값만 읽어오는 방식 입니다
• subquery쪽 index에서 unique 한 값을 추출 후 main query table을 조인 해서 처리 합니다

특징

• 중복이 아닌 unique key 를 추출 하기 때문에 subquery

EXPLAIN
SELECT *
  FROM departments d    -- 9건
 WHERE d.dept_no IN (SELECT de.dept_no
                       FROM dept_emp de    -- 33만건
                     )
;
+------+-------------+-------+--------+---------------+---------+---------+----------------------+--------+------------------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref                  | rows   | Extra                  |
+------+-------------+-------+--------+---------------+---------+---------+----------------------+--------+------------------------+
|    1 | PRIMARY     | de    | index  | PRIMARY       | PRIMARY | 16      | NULL                 | 331143 | Using index; LooseScan |
|    1 | PRIMARY     | d     | eq_ref | PRIMARY       | PRIMARY | 12      | employees.de.dept_no |      1 |                        |
+------+-------------+-------+--------+---------------+---------+---------+----------------------+--------+------------------------+

결론

• mysql 5.5 이하 부터는 subquery 최적화 이슈로 인해서 inner join을 사용해서 문제를 해결하는 것을 추천합니다
• mysql 5.6 이상이며, 만약 main query에 filter조건이 적절하게 적용이 되어있고, subquery에 group by가 없을경우 semi join을 사용해도 무방합니다
• 1:N 관계 테이블에서 1 table의 데이터만 조회하고, 중복을 제거 하고 싶을경우 semi join이 성능 상의 이득을 가져다 줄 수 있습니다.
• cf) 위에서 사진에서 food table에 food_flavor_id 라고적어 놓은게 있는데 이건 writer_id 입니다. (오타)...