꿈꾸는 개발자, DBA 커뮤니티 구루비

5.10 서브 쿼리 (MariaDB)(MySQL)

5.10.1 세미 조인 서브쿼리 최적화

세미 조인 서브쿼리 최적화 5가지

1 Table pullout 최적화
2 FirstMatch 최적화
3 Semi-join Materializaion 최적화
4 LooseScan 최적화
5 Duplicate Weedout 최적화

서브쿼리 최적화 요건

IN(subquery) 또는 = ANY(subquery) 형태
UNION 없는 단일 SELECT
집계함수 와 HAVING 절을 가지지 말아야
서브쿼리의 WHERE 조건이 외부쿼리의 다른 조건과 AND 로 연결
조인을 사용한 UPDATE 나 DELETE 가 이니어야
미리 수립된 실행계획을 사용하지 않는 경우(PreparedStatement 사용시 실행계획 재사용됨)
외부쿼리와 서브쿼리가 실제 테이블 사용(가상 테이블 사용시 세미조인 최적화 안됨)
외부쿼리와 서브쿼리가 straight_join 힌트 미사용

5.10.1.1 Table pullout 최적화

SQL 1 : MySQL 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 |
+------+--------------------+-------+--------+---------+------------+--------+--------------------------+
-- 책 내용 옮겨 적은 거

SQL 2 : MySQL 5.6 이상 -


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   | 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 |                          |
+------+-------------+-------+--------+---------------------------+---------+---------+---------------------+-------+--------------------------+

Table pullout 징후

id 값이 동일함 : 조인으로 실행되었음을 의미
Extra 필드가 비어 있음

SQL 3 : EXPLAIN EXTENDED & SHOW warnings


EXPLAIN EXTENDED
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   | possible_keys             | key     | key_len | ref                 | rows  | filtered | Extra                    |
+------+-------------+-------+--------+---------------------------+---------+---------+---------------------+-------+----------+--------------------------+
|    1 | PRIMARY     | de    | ref    | PRIMARY,ix_empno_fromdate | PRIMARY | 12      | const               | 46914 |   100.00 | Using where; Using index |
|    1 | PRIMARY     | e     | eq_ref | PRIMARY                   | PRIMARY | 4       | employees.de.emp_no |     1 |   100.00 |                          |
+------+-------------+-------+--------+---------------------------+---------+---------+---------------------+-------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

SHOW warnings;
+-------+------+-----------------------------------------------------------------+
| Level | Code | Message                                                         |
+-------+------+-----------------------------------------------------------------+
| Note  | 1003 | select `employees`.`e`.`emp_no`     AS `emp_no`                 |
|       |      |      , `employees`.`e`.`birth_date` AS `birth_date`             |
|       |      |      , `employees`.`e`.`first_name` AS `first_name`             |
|       |      |      , `employees`.`e`.`last_name`  AS `last_name`              |
|       |      |      , `employees`.`e`.`gender`     AS `gender`                 |
|       |      |      , `employees`.`e`.`hire_date`  AS `hire_date`              |
|       |      |   from `employees`.`dept_emp` `de`                              |
|       |      |   join `employees`.`employees` `e`                              |
|       |      |  where ( (`employees`.`e`.`emp_no` = `employees`.`de`.`emp_no`) |
|       |      |    and   (`employees`.`de`.`dept_no` = 'd009') )                |
+-------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [employees]>

Table pullout 최적화 제약사항

세미조인 서브쿼리에서만 가능
서브쿼리가 조인키가 Unique 해야 함

Table pullout 최적화 특징

Table pullout 이 적용되어도 기존 다른 최적화 기법이 사용 불가능한 것이 아니므로 최대한 활용하자
서브쿼리의 테이블을 아우터로 끌어내어 조인 수행. 모든 테이블을 끄집어 낸다면 서브쿼리가 사라짐
Table pullout 을 제어하는 optimizer_switch 없음.
SET optimizer_switch='semijoin=off'; 가 가능하지만 이는 pullout 을 그는게 아니라 모든 세미조인 최적화를 끄는 것

5.10.1.2 FirstMatch 최적화

SQL : FirstMatch


EXPLAIN
SELECT *
  FROM employees e
 WHERE e.first_name = 'Matt'
   AND e.emp_no IN (SELECT t.emp_no
                      FROM titles t
                     WHERE t.from_date BETWEEN '1995-01-01' AND '1995-01-30'
                    )
;
+------+-------------+-------+------+----------------------+--------------+---------+--------------------+------+-----------------------------------------+
| id   | select_type | table | type | possible_keys        | key          | key_len | ref                | rows | Extra                                   |
+------+-------------+-------+------+----------------------+--------------+---------+--------------------+------+-----------------------------------------+
|    1 | PRIMARY     | e     | ref  | PRIMARY,ix_firstname | ix_firstname | 44      | const              |  233 | Using index condition                   |
|    1 | PRIMARY     | t     | ref  | PRIMARY              | PRIMARY      | 4       | employees.e.emp_no |    1 | Using where; Using index; FirstMatch(e) |
+------+-------------+-------+------+----------------------+--------------+---------+--------------------+------+-----------------------------------------+

FirstMatch 징후

id 값이 동일함 : 조인으로 실행되었음을 의미
Extra : FirstMatch

FirstMatch 특징

Exists 처럼 동작 :1건만 찾으면 검색을 멈춤

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

동등 조건 전파(Equality porpagation)가 서브쿼리에서만 가능하던게 아우터 테이블까지 가능
무조건 최적화 수행하던게 최적화 수행할 지 여부를 취사 선택

FirstMatch 제약사항 및 특성

1건 검색하고 멈추는 단축 실행 경로(Short-cut path) 이므로 아우터 먼저 조회되고 서브가 실행된다.
Extra : FirstMatch(table-N) 표시
상관서브쿼리에서도 사용된다.
Group BY 나 집합함수 사용시엔 최적화 적용 안됨
SET optimizer_switch='firstmatch=on'
아우터 쿼리가 독립적으로 인덱스를 적절히 사용할 수 있는 조건을 가지면서 서브쿼리가 수행될 때
아우터 쿼리가 독립적으로 인덱스를 적절히 사용할 수 있는 조건이 없다면 Semi-join Materializaion 최적화

5.10.1.3 Semi-join Materializaion 최적화

SQL 1 : Semi-join Materializaion


EXPLAIN
SELECT *
  FROM employees e
 WHERE e.emp_no IN (SELECT t.emp_no
                      FROM titles t
                     WHERE t.from_date BETWEEN '1995-01-01' AND '1995-01-30'
                    )
;
+------+--------------+-------------+--------+---------------+--------------+---------+------+--------+--------------------------+
| id   | select_type  | table       | type   | possible_keys | key          | key_len | ref  | rows   | Extra                    |
+------+--------------+-------------+--------+---------------+--------------+---------+------+--------+--------------------------+
|    1 | PRIMARY      | e           | ALL    | PRIMARY       | NULL         | NULL    | NULL | 299423 |                          |
|    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key  | distinct_key | 4       | func |      1 |                          |
|    2 | MATERIALIZED | t           | index  | PRIMARY       | PRIMARY      | 159     | NULL | 442189 | Using where; Using index |
+------+--------------+-------------+--------+---------------+--------------+---------+------+--------+--------------------------+

아우터 테이블에 대한 인덱스 조건이 없으므로 풀스캔해야 하며 FirstMatch 는 성능에 안좋다.
서브테이블을 구체화 하여 조인 형태로 실행
key : distinct_key
구체화된 <subquery2> 가 드라이빙되지 않은 것은 옵티마이져의 실수가 아닐까?
title 의 from_date 에 인덱스를 만들어 보자

SQL 2 : 인덱스 추가


ALTER TABLE titles ADD INDEX ix_fromdate(from_date);
Query OK, 0 rows affected (48.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

EXPLAIN
SELECT *
  FROM employees e
 WHERE e.emp_no IN (SELECT t.emp_no
                      FROM titles t
                     WHERE t.from_date BETWEEN '1995-01-01' AND '1995-01-30'
                    )
;
+------+--------------+-------------+--------+---------------------+-------------+---------+--------------------+------+--------------------------+
| id   | select_type  | table       | type   | possible_keys       | key         | key_len | ref                | rows | Extra                    |
+------+--------------+-------------+--------+---------------------+-------------+---------+--------------------+------+--------------------------+
|    1 | PRIMARY      | <subquery2> | ALL    | distinct_key        | NULL        | NULL    | NULL               | 2689 |                          |
|    1 | PRIMARY      | e           | eq_ref | PRIMARY             | PRIMARY     | 4       | employees.t.emp_no |    1 |                          |
|    2 | MATERIALIZED | t           | range  | PRIMARY,ix_fromdate | ix_fromdate | 3       | NULL               | 2689 | Using where; Using index |
+------+--------------+-------------+--------+---------------------+-------------+---------+--------------------+------+--------------------------+

MATERIALIZED 최적화의 2가지 전략

Materialization - Scan : 구체화된 임시테이블 드라이빙. full scan
Materialization - Lookup : 구체화된 임시테이블 드리븐. distinct_key 인덱스 사용

SQL 3 : Group By 가 있어도 사용 가능


EXPLAIN
SELECT *
  FROM employees e
 WHERE e.emp_no IN (SELECT t.emp_no
                      FROM titles t
                     WHERE t.from_date BETWEEN '1995-01-01' AND '1995-01-30'
                     GROUP BY t.title
                    )
;
+------+--------------+-------------+--------+---------------------+-------------+---------+--------------------+------+--------------------------+
| id   | select_type  | table       | type   | possible_keys       | key         | key_len | ref                | rows | Extra                    |
+------+--------------+-------------+--------+---------------------+-------------+---------+--------------------+------+--------------------------+
|    1 | PRIMARY      | <subquery2> | ALL    | distinct_key        | NULL        | NULL    | NULL               | 2689 |                          |
|    1 | PRIMARY      | e           | eq_ref | PRIMARY             | PRIMARY     | 4       | employees.t.emp_no |    1 |                          |
|    2 | MATERIALIZED | t           | range  | PRIMARY,ix_fromdate | ix_fromdate | 3       | NULL               | 2689 | Using where; Using index |
+------+--------------+-------------+--------+---------------------+-------------+---------+--------------------+------+--------------------------+

Semi-join Materialization 최적화 제약사항 및 특성

IN(subquery)에서 서브쿼리는 상관서브쿼리가 아니어야 한다
Group by 가 있어도 된다.
임시테이블이 사용된다.

5.10.1.4 LooseScan 최적화

SQL 1 : LooseScan 을 원했지만 MATERIALIZED


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      | d           | index  | PRIMARY       | ux_deptname  | 122     | NULL |      9 | Using index |
|    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key  | distinct_key | 12      | func |      1 |             |
|    2 | MATERIALIZED | de          | index  | PRIMARY       | ix_fromdate  | 3       | NULL | 331143 | Using index |
+------+--------------+-------------+--------+---------------+--------------+---------+------+--------+-------------+

SQL 2 : Materializaion=off


SET optimizer_switch='materialization=off';
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     | d     | index | PRIMARY       | ux_deptname | 122     | NULL                |     9 | Using index                |
|    1 | PRIMARY     | de    | ref   | PRIMARY       | PRIMARY     | 12      | employees.d.dept_no | 20696 | Using index; FirstMatch(d) |
+------+-------------+-------+-------+---------------+-------------+---------+---------------------+-------+----------------------------+

SQL 3 : FirstMatch=off


SET optimizer_switch='FirstMatch=off';
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 |                        |
+------+-------------+-------+--------+---------------+---------+---------+----------------------+--------+------------------------+

루스 인덱스 스캔으로 서브쿼리 테이블을 읽고 아우터 테이블을 드리븐
SET optimizer_switch='loosescan=off';

SQL 4 : 루스스캔이 가능한 서브쿼리의 형태


SELECT * FROM t1 WHERE c IN (SELECT key1 FROM t2 WHERE ...);
SELECT * FROM t1 WHERE c IN (SELECT key2 FROM t2 WHERE key1 = '상수' AND ...);

5.10.1.5 Duplicate Weedout 최적화

SQL 1 : MATERIALIZED


EXPLAIN
SELECT *
  FROM employees e
 WHERE e.emp_no IN (SELECT s.emp_no
                      FROM salaries s
                     WHERE s.salary > 150000
						  )
;
+------+--------------+-------------+--------+-------------------+-----------+---------+--------------------+------+--------------------------+
| id   | select_type  | table       | type   | possible_keys     | key       | key_len | ref                | rows | Extra                    |
+------+--------------+-------------+--------+-------------------+-----------+---------+--------------------+------+--------------------------+
|    1 | PRIMARY      | <subquery2> | ALL    | distinct_key      | NULL      | NULL    | NULL               |   36 |                          |
|    1 | PRIMARY      | e           | eq_ref | PRIMARY           | PRIMARY   | 4       | employees.s.emp_no |    1 |                          |
|    2 | MATERIALIZED | s           | range  | PRIMARY,ix_salary | ix_salary | 4       | NULL               |   36 | Using where; Using index |
+------+--------------+-------------+--------+-------------------+-----------+---------+--------------------+------+--------------------------+

SQL 2 : materialization=off


SET optimizer_switch='materialization=off';
EXPLAIN
SELECT *
  FROM employees e
 WHERE e.emp_no IN (SELECT s.emp_no
                      FROM salaries s
                     WHERE s.salary > 150000
						  )
;
+------+-------------+-------+--------+-------------------+-----------+---------+--------------------+------+-------------------------------------------+
| id   | select_type | table | type   | possible_keys     | key       | key_len | ref                | rows | Extra                                     |
+------+-------------+-------+--------+-------------------+-----------+---------+--------------------+------+-------------------------------------------+
|    1 | PRIMARY     | s     | range  | PRIMARY,ix_salary | ix_salary | 4       | NULL               |   36 | Using where; Using index; Start temporary |
|    1 | PRIMARY     | e     | eq_ref | PRIMARY           | PRIMARY   | 4       | employees.s.emp_no |    1 | End temporary                             |
+------+-------------+-------+--------+-------------------+-----------+---------+--------------------+------+-------------------------------------------+

세미조인 서브쿼리를 이너조인으로 바꾸어 수행한 뒤 중복 제거 하는 방식
Extra : Start temporary End temporary
Duplicate Weedout 최적화 제한사항 및 특성
- 상관서브쿼리에서도 사용 가능
- Group By 나 집계함수 사용시 최적화 안됨
- 서브쿼리를 조인으로 바꾸므로 최적화 할 수 있는 방법이 많다
- optimizer_switch 없음. 다른 스위치를 off 하여 수행 가능

5.10.2 세미 조인이 아닌 서브쿼리 최적화

세미 인 듯 세미 아닌 세미 같은 서브쿼리

SQL 1 : 다른 조건과 OR 로 연결된 서브쿼리


SELECT *
  FROM t
 WHERE expr1 IN (SELECT ...)
    OR expr2
;

SQL 2 : NOT IN 서브쿼리


SELECT *
  FROM t
 WHERE expr1 NOT IN (SELECT ...)
;

SQL 3 : Select 절의 서브쿼리


SELECT (SELECT ...)
  FROM t
;

SQL 4 : Having 절의 서브쿼리


SELECT *
  FROM t
HAVING expr1 IN (SELECT ...)
;

SQL 5 : Union 이 포함된 서브쿼리


SELECT *
  FROM t
 WHERE expr1 IN (SELECT ... UNION SELECT ...)

세미조인이아닌 서브쿼리 최적화는 다음 2가지
- Materializaion : 비상관 서브쿼리일 때
- IN-to-EXISTS : 상관 서브쿼리일 때

5.10.2.1 Materializaion

세미 조인의 서브쿼리의 Materializaion 과 거의 비슷하게 동작
비상관 서브쿼리일 때
차이점은 NULL 의 효율적 처리를 위한 2가지 알고리즘
- RowId-merge partial matching
- Table scan partial matching

5.10.2.2 IN-to-EXISTS

IN 서브쿼리를 EXISTS 로 바꾸어 실행

SQL : OR 로 연결


EXPLAIN EXTENDED
SELECT *
  FROM employees e
 WHERE e.emp_no IN (SELECT de.emp_no
                      FROM dept_emp de
                     WHERE de.dept_no = 'd009'
						        )
    OR e.first_name = 'Matt'
;
+------+--------------------+-------+--------+---------------------------------------+---------+---------+------------+--------+----------+------------------------+
| id   | select_type        | table | type   | possible_keys                         | key     | key_len | ref        | rows   | filtered | Extra                  |
+------+--------------------+-------+--------+---------------------------------------+---------+---------+------------+--------+----------+------------------------+
|    1 | PRIMARY            | e     | ALL    | ix_firstname                          | NULL    | NULL    | NULL       | 299423 |   100.00 | Using where            |
|    2 | DEPENDENT SUBQUERY | de    | eq_ref | PRIMARY,ix_fromdate,ix_empno_fromdate | PRIMARY | 16      | const,func |      1 |   100.00 | Using where; Usingndex |
+------+--------------------+-------+--------+---------------------------------------+---------+---------+------------+--------+----------+------------------------+
2 rows in set, 1 warning (0.00 sec)

MariaDB [employees]> SHOW WARNINGS;
+-------+------+---------------------------------------------------------------------------------------+
| Level | Code | Message                                                                               |
+-------+------+---------------------------------------------------------------------------------------+
| Note  | 1003 | select `employees`.`e`.`emp_no` AS `emp_no`                                           |
|       |      |      , `employees`.`e`.`birth_date` AS `birth_date`                                   |
|       |      |      , `employees`.`e`.`first_name` AS `first_name`                                   |
|       |      |      , `employs`.`e`.`last_name` AS `last_name`                                       |
|       |      |      , `employees`.`e`.`gender` AS `gender`                                           |
|       |      |      , `employees`.`e`.`hire_date` AS `hire_date`                                     |
|       |      |   from `employees`.`employees` `e`                                                    |
|       |      |  where (<expcache><`employees`.`e`.`emp_no`>(<in_optimizer>(`employees`.`e`.`emp_no`, |
|       |      | <exists>(select `employees`.`de`.`emp_no`                                             |
|       |      |            from `employees`.`dept_emp` `de`                                           |
|       |      |           where ((`eloyees`.`de`.`dept_no` = 'd009')                                  |
|       |      |             and (<cache>(`employees`.`e`.`emp_no`) = `employees`.`de`.`emp_no`)))))   |
|       |      |              or (`employees`.`e`.`first_name` = 'Matt'))                              |
+-------+------+---------------------------------------------------------------------------------------+

위 show warning 내용 중 <exists> 부분

5.10.3 서브 쿼리 캐시

상관 서브쿼리가 아우터 쿼리의 실행결과 건수만큼 반복처리 되어야 할 때
위 show warning 내용 중 <expcache> 부분
- 파라미터 : <`employees`.`e`.`emp_no`>
- 캐시결과 : (<in_optimizer> ... )
캐시결과는 내부 임시테이블에 파라미터 값과 서브쿼리 결과가 함께 저장된다.
- 파라미터 컬럼들을 묶어서 유니크 인덱스생성
- 처음에는 메모리 테이블(힙 테이블)로 생성되지만
- tmp_table_size 나 max_heap_table_size 보다 커지면 캐시히트율을 계산하여 캐시를 어떻게 유지할지 결정