Real MariaDB (2017년)
서브 쿼리 0 0 1,906

by 구루비스터디 MariaDB 최적화 MariaDB 서브쿼리 [2019.08.11]


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 보다 커지면 캐시히트율을 계산하여 캐시를 어떻게 유지할지 결정
"구루비 데이터베이스 스터디모임" 에서 2017년에 "Real MariaDB" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4199

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입