옵티마이저의 쿼리 최적화

Created
March 27, 2024
Created by
D
DaEun Kim
Tags
Mysql
Property

세미 조인 최적화

옵티마이저는 기존 쿼리를 같은 결과를 제공하면서도 성능 측면에서 더 나은 쿼리로 재작성하는 작업을 통해 성능을 최적화한다.

서브쿼리의 경우 아래 5가지 방법으로 쿼리 최적화를 수행한다.

테이블 풀 아웃 (table pull-out)

서브쿼리에 있는 테이블을 바깥으로 꺼내서 조인의 형태로 재작성한다.

아래 실행 계획표를 보면 id 컬럼의 값이 동일하다.

(실행계획표에서 id 컬럼값이 동일하다는 것은 서브쿼리 수행 후 서브쿼리 수행 결과를 가지고 바깥 테이블을 스캔한 것이 아니라 두 테이블을 조인했음을 의미한다.)

mysql> explain 
select e.* from employees as e 
where emp_no in (select emp_no from dept_emp where dept_no = 'd009');
+----+-------------+----------+------------+--------+---------------------------+---------+---------+---------------------------+-------+----------+-------------+
| id | select_type | table    | partitions | type   | possible_keys             | key     | key_len | ref                       | rows  | filtered | Extra       |
+----+-------------+----------+------------+--------+---------------------------+---------+---------+---------------------------+-------+----------+-------------+
|  1 | SIMPLE      | dept_emp | NULL       | ref    | PRIMARY,ix_empno_fromdate | PRIMARY | 16      | const                     | 46012 |   100.00 | Using index |
|  1 | SIMPLE      | e        | NULL       | eq_ref | PRIMARY                   | PRIMARY | 4       | employees.dept_emp.emp_no |     1 |   100.00 | NULL        |
+----+-------------+----------+------------+--------+---------------------------+---------+---------+---------------------------+-------+----------+-------------+
2 rows in set, 1 warning (0.08 sec)

테이블 풀 아웃을 수행했는지 여부를 더 정확하게 확인하려면 실행 계획을 조회한 직후에 SHOW WARNINGS 명령을 사용하면 된다.

mysql> show warnings\G;
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ 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` join `employees`.`employees` `e` where ((`employees`.`e`.`emp_no` = `employees`.`dept_emp`.`emp_no`) and (`employees`.`dept_emp`.`dept_no` = 'd009'))
1 row in set (0.00 sec)

[참고]

  • 테이블 풀 아웃은 세미 조인 서브쿼리에서만 적용 가능하다.

퍼스트 매치 (first match)

IN 절을 사용한 세미 조인 쿼리를 EXISTS 절을 사용한 것 처럼 튜닝한다. → 조건을 만족하는 데이터 레코드를 1건만 찾으면 더 이상 스캔하지 않도록 튜닝한다.

아래 실행계획표를 보면 Extra 항목에 FirstMatch(e) 가 표시되었고 id 값이 동일해서 employees 테이블과 titles 테이블이 조인되었음을 알 수 있다.

mysql> show index from employees where key_name = 'ix_firstname';
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employees |          1 | ix_firstname |            1 | first_name  | A         |        1250 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.02 sec)


mysql> show index from titles;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| titles |          0 | PRIMARY   |            1 | emp_no      | A         |      249990 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| titles |          0 | PRIMARY   |            2 | from_date   | A         |      388550 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| titles |          0 | PRIMARY   |            3 | title       | A         |      414461 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| titles |          1 | ix_todate |            1 | to_date     | A         |        6617 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.06 sec)


mysql> explain select *
    -> from employees as e where e.first_name = 'Matt'
    -> and emp_no in (select emp_no from titles where from_date between '1995-01-01' and '1995-01-30');
+----+-------------+--------+------------+------+----------------------+--------------+---------+--------------------+------+----------+-----------------------------------------+
| id | select_type | table  | partitions | type | possible_keys        | key          | key_len | ref                | rows | filtered | Extra                                   |
+----+-------------+--------+------------+------+----------------------+--------------+---------+--------------------+------+----------+-----------------------------------------+
|  1 | SIMPLE      | e      | NULL       | ref  | PRIMARY,ix_firstname | ix_firstname | 58      | const              |  233 |   100.00 | NULL                                    |
|  1 | SIMPLE      | titles | NULL       | ref  | PRIMARY              | PRIMARY      | 4       | employees.e.emp_no |    1 |    11.11 | Using where; Using index; FirstMatch(e) |
+----+-------------+--------+------------+------+----------------------+--------------+---------+--------------------+------+----------+-----------------------------------------+
2 rows in set, 1 warning (0.04 sec)

[퍼스트 매치 처리 과정]

  1. ix_firstname 인덱스로 e.first_name 컬럼 레인지 스캔
  2. first_name = ‘Matt’ 조건을 만족하면 emp_no 를 가지고 titles 테이블과 조인
  3. 조인된 titles 테이블의 복합인덱스를 스캔하면서 from_date between '1995-01-01' and '1995-01-30' 조건을 만족하는 레코드를 발견하면 더 이상 복합인덱스를 스캔하지 않고 emp_no 를 조회 대상에 추가

[참고]

  • 서브쿼리 내에서 조건을 만족하는 레코드를 1건만 찾으면 더 이상 스캔하지 않으므로 서브쿼리 바깥에 있는 테이블이 먼저 조회된 이후에 서브쿼리 내 테이블을 스캔한다.
  • 퍼스트매치 최적화는 상관 서브쿼리(correlated subquery) 에서도 사용될 수 있다.
  • GROUP BY 또는 집계함수가 사용된 서브쿼리에서는 사용할 수 없다.

루스 스캔 (loose scan)

루스 인덱스 스캔과 비슷한 읽기 방식으로 세미 조인 서브쿼리를 최적화한다.

mysql> show index from dept_emp;
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| dept_emp |          0 | PRIMARY           |            1 | dept_no     | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| dept_emp |          0 | PRIMARY           |            2 | emp_no      | A         |      331143 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| dept_emp |          1 | ix_fromdate       |            1 | from_date   | A         |        7035 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| dept_emp |          1 | ix_empno_fromdate |            1 | emp_no      | A         |      312401 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| dept_emp |          1 | ix_empno_fromdate |            2 | from_date   | A         |      329130 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set (0.00 sec)


mysql> explain select * from departments where dept_no in (select dept_no from dept_emp);
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------------------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                                      |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------------------------+
|  1 | SIMPLE      | dept_emp    | NULL       | index | PRIMARY       | PRIMARY | 20      | NULL | 331143 |     0.00 | Using index; LooseScan                     |
|  1 | SIMPLE      | departments | NULL       | ALL   | PRIMARY       | NULL    | NULL    | NULL |      9 |    11.11 | Using where; Using join buffer (hash join) |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)


mysql> select count(dept_no) from departments;
+----------------+
| count(dept_no) |
+----------------+
|              9 |
+----------------+
1 row in set (0.03 sec)


mysql> select count(dept_no) from dept_emp;
+----------------+
| count(dept_no) |
+----------------+
|         331603 |
+----------------+
1 row in set (0.66 sec)

실제 departments 테이블 내 총 데이터 레코드 수는 9건밖에 안되지만 서브쿼리에서 dept_emp 테이블을 조회한 결과는 33만건 → 루스 인덱스 스캔으로 유니크한 dep_no 만 읽어서 성능을 최적화 한다.

[루스 스캔 처리 과정]

  1. 서브쿼리 테이블 (dept_emps) 인덱스를 읽는다.
  2. 유니크한 인덱스 레코드를 발견하면 서브쿼리 밖에 있는 테이블(departments) 과 조인을 수행한다.
  3. 다음 인덱스 레코드로 건너뛴다. (루스 인덱스 스캔을 수행한다.)
image

구체화 (materialization)

어떤 테이블이 materialized 테이블이다. → 해당 테이블을 가지고 임시 테이블을 만들었다.

원래 아래 쿼리는 인덱스를 활용할 수 없으므로 employee 테이블을 풀 스캔 해야 한다. 하지만 구체화(materialization)을 통해 풀 스캔 하지않고 아래 과정을 거친다.

  1. 서브쿼리 테이블에서 조건을 만족하는 데이터 레코드 스캔
  2. 스캔 결과를 가지고 임시테이블 생성
  3. 임시테이블과 서브쿼리 바깥의 테이블을 조인
mysql> show index from salary where column_name = 'amount';
Empty set (0.01 sec)


mysql> explain select e.* from employee as e where emp_no in (select emp_no from salary where amount > 60000);
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+--------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys       | key                 | key_len | ref                 | rows   | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+--------+----------+-------------+
|  1 | SIMPLE       | e           | NULL       | ALL    | PRIMARY             | NULL                | NULL    | NULL                | 296327 |   100.00 | NULL        |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 4       | dispatchyo.e.emp_no |      1 |   100.00 | NULL        |
|  2 | MATERIALIZED | salary      | NULL       | ALL    | idx_emp_no          | NULL                | NULL    | NULL                | 313740 |    33.33 | Using where |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+--------+----------+-------------+
3 rows in set, 1 warning (0.01 sec)

위 실행계획표를 보면 salary 테이블 대상으로 임시 테이블이 생성되었으므로 select_type 에 MATERIALIZED 라고 표시되었다.

생성된 임시테이블의 이름은 <subquery2> 이고, employee 테이블과 id 값이 같은 것으로 보아 employee 테이블과 조인되었음을 알 수 있다.

중복 제거 (duplicated weed-out)

서브 쿼리를 inner join + group by 형태로 바꾼 후, 중복되는 데이터 레코드들을 제거하는 방식이다.

[duplicated weed-out 최적화를 유도하기 위한 스위치 설정]

mysql> set optimizer_switch='materialization=OFF';
Query OK, 0 rows affected (0.00 sec)

mysql> set optimizer_switch='firstmatch=OFF';
Query OK, 0 rows affected (0.00 sec)

mysql> set optimizer_switch='loosescan=OFF';
Query OK, 0 rows affected (0.00 sec)

mysql> set optimizer_switch='duplicateweedout=ON';
Query OK, 0 rows affected (0.00 sec)

[세미 조인 쿼리의 실행계획 조회]

mysql> explain select * from employee where emp_no in (select emp_no from salary where amount > 60000);
+----+-------------+----------+------------+--------+---------------+---------+---------+--------------------------+--------+----------+------------------------------+
| id | select_type | table    | partitions | type   | possible_keys | key     | key_len | ref                      | rows   | filtered | Extra                        |
+----+-------------+----------+------------+--------+---------------+---------+---------+--------------------------+--------+----------+------------------------------+
|  1 | SIMPLE      | salary   | NULL       | ALL    | idx_emp_no    | NULL    | NULL    | NULL                     | 313740 |    33.33 | Using where; Start temporary |
|  1 | SIMPLE      | employee | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | dispatchyo.salary.emp_no |      1 |   100.00 | End temporary                |
+----+-------------+----------+------------+--------+---------------+---------+---------+--------------------------+--------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)

[duplicated weed-out 처리 과정]

  1. 서브 쿼리 내 where 절에 있는 조건으로 서브쿼리 테이블 (salary) 스캔
  2. 조건을 만족하는 데이터 레코드를 찾으면 바깥 테이블(employee)과 조인 수행 후 임시테이블에 조인한 결과 저장
  3. 서브쿼리 테이블 스캔을 마칠 때 까지 1 ~ 2 과정 반복
  4. 서브쿼리 테이블 스캔이 끝나면 임시 테이블에 저장된 joined record 대상으로 중복 제거 (위 예시의 경우 emp_no 를 기준으로 중복 제거)
  5. 임시 테이블에서 중복 제거한 결과 제공

실행 계획표의 Extra 항목에 Start temporary, End temporary 라고 표기 → 임시테이블에 조인된 레코드를 넣는 반복이 시작/종료되는 테이블에 표기됨?

[참고]

  • 서브쿼리가 상관 서브쿼리(correlated subquery) 인 경우에도 사용할 수 있다.
  • 서브쿼리에서 GROUP BY 또는 집계 함수가 있으면 사용 할 수 없다.
  • duplicated weed-out 를 대체하는 다른 최적화 방법이 더 많으므로, 왠만하면 duplicated weed-out 방식이 잘 사용되진 않는다?