5.8 인덱스 머지(index_merge) (MariaDB)(MySQL)

• 인덱스를 이용하여 테이블을 읽는 경우 대부분은 하나의 인덱스만 활용할 수 있다.
• 인덱스 머지 실행계획을 사용하면 하나의 테이블에 대해 2개 이상의 인덱스를 이용하게 된다.

SQL 1 : 건수가 적은 쪽 1 개 인덱스만 이용

EXPLAIN
SELECT *
  FROM employees
 WHERE first_name = 'Matt'                              --   233 건
   AND hire_date BETWEEN '1995-01-01' AND '2000-01-01'  -- 34028 건
;
+------+-------------+-----------+------+--------------------------+--------------+---------+-------+------+------------------------------------+
| id   | select_type | table     | type | possible_keys            | key          | key_len | ref   | rows | Extra                              |
+------+-------------+-----------+------+--------------------------+--------------+---------+-------+------+------------------------------------+
|    1 | SIMPLE      | employees | ref  | ix_firstname,ix_hiredate | ix_firstname | 44      | const |  233 | Using index condition; Using where |
+------+-------------+-----------+------+--------------------------+--------------+---------+-------+------+------------------------------------+

SQL 2 : 둘다 건수가 많은데 인덱스 머지 발생 안함

EXPLAIN
SELECT *
  FROM employees
 WHERE first_name BETWEEN 'A' AND 'B'                   -- 22039 건
   AND hire_date BETWEEN '1995-01-01' AND '2000-01-01'  -- 34028 건
;
+------+-------------+-----------+-------+--------------------------+--------------+---------+------+-------+------------------------------------+
| id   | select_type | table     | type  | possible_keys            | key          | key_len | ref  | rows  | Extra                              |
+------+-------------+-----------+-------+--------------------------+--------------+---------+------+-------+------------------------------------+
|    1 | SIMPLE      | employees | range | ix_firstname,ix_hiredate | ix_firstname | 44      | NULL | 43234 | Using index condition; Using where |
+------+-------------+-----------+-------+--------------------------+--------------+---------+------+-------+------------------------------------+

SQL 3 : sort_intersection 알고리즘 활성화 > 2개 인덱스 모두 이용

MariaDB [employees]> SET optimizer_switch = 'index_merge_sort_intersection = on';    -- 공백 때문에 에러 발생
ERROR 1231 (42000): Variable 'optimizer_switch' can't be set to the value of 'index_merge_sort_intersection = on'
MariaDB [employees]> SET optimizer_switch = 'index_merge_sort_intersection=on';      -- 공백 지우고 정상 수행
Query OK, 0 rows affected (0.00 sec)
EXPLAIN
SELECT *
  FROM employees
 WHERE first_name BETWEEN 'A' AND 'B'                   -- 22039 건
   AND hire_date BETWEEN '1995-01-01' AND '2000-01-01'  -- 34028 건
;
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+-------------------------------------------------------------+
| id   | select_type | table     | type        | possible_keys            | key                      | key_len | ref  | rows | Extra                                                       |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+-------------------------------------------------------------+
|    1 | SIMPLE      | employees | index_merge | ix_firstname,ix_hiredate | ix_firstname,ix_hiredate | 44,3    | NULL | 9912 | Using sort_intersect(ix_firstname,ix_hiredate); Using where |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+-------------------------------------------------------------+

5.8.1 Using union

SQL 1 : 2개의 이퀄 조건 OR 로 연결 > index merge

EXPLAIN
SELECT *
  FROM employees
 WHERE first_name = 'Matt'        -- 233 건
    OR hire_date  = '1987-03-31'  -- 111 건
;
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+----------------------------------------------------+
| id   | select_type | table     | type        | possible_keys            | key                      | key_len | ref  | rows | Extra                                              |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+----------------------------------------------------+
|    1 | SIMPLE      | employees | index_merge | ix_firstname,ix_hiredate | ix_firstname,ix_hiredate | 44,3    | NULL |  344 | Using union(ix_firstname,ix_hiredate); Using where |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+----------------------------------------------------+

SQL 2 : OR 조건 중 1개 조건이 인덱스를 이용하지 못하면? > 나머지 인덱스도 이용 못함

EXPLAIN
SELECT *
  FROM employees
 WHERE first_name LIKE '%Matt'    -- 233 건
    OR hire_date  = '1987-03-31'  -- 111 건
;
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | employees | ALL  | ix_hiredate   | NULL | NULL    | NULL | 299423 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

SQL 3 : Using union 알고리즘의 숨은 비밀? > 정렬된 결과

SELECT *
  FROM employees
 WHERE first_name = 'Matt'        -- 233 건
    OR hire_date  = '1987-03-31'  -- 111 건
 LIMIT 10
;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10083 | 1959-07-23 | Vishv      | Zockler   | M      | 1987-03-31 |
|  10690 | 1962-09-06 | Matt       | Jumpertz  | F      | 1989-08-22 |
|  12302 | 1962-02-14 | Matt       | Plessier  | M      | 1987-01-28 |
|  13163 | 1963-12-11 | Matt       | Heping    | F      | 1987-03-31 |
|  13233 | 1954-08-11 | Alselm     | Chvatal   | M      | 1987-03-31 |
|  13507 | 1959-09-05 | Matt       | Wallrath  | M      | 1985-06-28 |
|  14004 | 1959-04-10 | Arto       | Wendorf   | M      | 1987-03-31 |
|  15456 | 1957-09-27 | Matt       | Granlund  | M      | 1987-08-17 |
|  15577 | 1961-02-15 | Matt       | Menhardt  | M      | 1985-10-26 |
|  15696 | 1959-05-06 | Matt       | Zhang     | F      | 1987-08-21 |
+--------+------------+------------+-----------+--------+------------+

정렬 없이 어떻게 정렬된 결과를 낼 수 있을까? 또한 양쪽에 중복된 자료는 어떻게 걸러 낼까?

• 인덱스 검색 결과는 이미 키로 정렬되어 있다.
• 정렬된 결과를 한건씩 비교하면서 중복 제거하는 깔데기 우선순위 큐(priority Queue)

5.8.2 Using sort_union

SQL 1 : 한쪽 조건이 동등조건이 아닌 경우

EXPLAIN
SELECT *
  FROM employees
 WHERE first_name = 'Matt'                              --  233 건
    OR hire_date  BETWEEN '1987-03-01' AND '1987-03-31' -- 2964 건
;
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+---------------------------------------------------------+
| id   | select_type | table     | type        | possible_keys            | key                      | key_len | ref  | rows | Extra                                                   |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+---------------------------------------------------------+
|    1 | SIMPLE      | employees | index_merge | ix_firstname,ix_hiredate | ix_firstname,ix_hiredate | 44,3    | NULL | 3196 | Using sort_union(ix_firstname,ix_hiredate); Using where |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+---------------------------------------------------------+

• 동등조건이 아니므로 hire_date 인덱스 검색 결과는 emp_no 로 정렬되어 있지 않음
• 정렬되어 있지 않으므로 우선순위큐 사용 불가. 정렬을 수행하게 됨

5.8.3 Using intersect

SQL 1 : 2개의 동등조건 AND 로 연결

EXPLAIN
SELECT COUNT(*)
  FROM employees
 WHERE first_name = 'Matt'        -- 233 건
   AND hire_date  = '1991-07-22'  --  47 건
;
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+---------------------------------------------------------------------+
| id   | select_type | table     | type        | possible_keys            | key                      | key_len | ref  | rows | Extra                                                               |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+---------------------------------------------------------------------+
|    1 | SIMPLE      | employees | index_merge | ix_firstname,ix_hiredate | ix_hiredate,ix_firstname | 3,44    | NULL |    1 | Using intersect(ix_hiredate,ix_firstname); Using where; Using index |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+---------------------------------------------------------------------+

5.8.4 Using sort_intersect (MariaDB)

SQL 1 : 1개의 조건이 동등조건이 아닌 경우

EXPLAIN
SELECT COUNT(*)
  FROM employees
 WHERE first_name LIKE 'Matt%'    -- 233 건
   AND hire_date  = '1991-07-22'  --  47 건
;
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+-------------------------------------------------------------+
| id   | select_type | table     | type        | possible_keys            | key                      | key_len | ref  | rows | Extra                                                       |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+-------------------------------------------------------------+
|    1 | SIMPLE      | employees | index_merge | ix_firstname,ix_hiredate | ix_hiredate,ix_firstname | 3,44    | NULL |    1 | Using sort_intersect(ix_hiredate,ix_firstname); Using where |
+------+-------------+-----------+-------------+--------------------------+--------------------------+---------+------+------+-------------------------------------------------------------+

5.9 테이블 조인 (MariaDB)(MySQL)

5.9.1 조인의 종류

• INNER JOIN, OUTER JOIN
• OUTER JOIN : LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN
• NATURAL JOIN, CROSS JOIN(FULL JOIN, CARTESIAN JOIN)
• SEMI JOIN, ANTI JOIN

5.9.1.1 JOIN(INNER JOIN)

• Nested Loop 방식으로 동작

5.9.1.2 OUTER JOIN

• LEFT OUTER JOIN, RIGHT OUTER JOIN
• FULL OUTER JOIN : 지원되지 않음

SQL 1 : 잘 못 작성한 아우터 조인

SELECT *
  FROM employees e
  LEFT OUTER JOIN salaries s
    ON e.emp_no = s.emp_no
 WHERE s.salary > 5000
;

• ON 절의 아우터 조인 조건과 WHERE 절의 검색 조건이 서로 상충됨
• 쿼리가 변형되어 INNER JOIN 으로 실행됨
• 작성 의도가 불분명한 쿼리임. 다음 두가지 중 한가지로 작성해야 작성 의도를 정확하게 알 수 있음

SQL 2 : 순수하게 아우터 조인으로 표현한 쿼리

SELECT *
  FROM employees e
  LEFT OUTER JOIN salaries s
    ON e.emp_no = s.emp_no
   AND s.salary > 5000
;

SQL 3 : 순수하게 이너 조인으로 표현한 쿼리

SELECT *
  FROM employees e
 INNER OUTER JOIN salaries s
    ON e.emp_no = s.emp_no
 WHERE s.salary > 5000
;

5.9.1.3 카테시안 조인

• CROSS JOIN : 아무런 조건 없이 두 집합을 조인
• 대부분의 경우 조인 조건의 누락으로 인해 의도치 않게 발생된다.
• MariaDB 에서의 CROSS JOIN 은 문법상 INNER JOIN 과 동일하다.
• CROSS JOIN 구문에 ON 절 사용하면 INNER JOIN 이 될 수 있고,
• INNER JOIN 구문에 ON 절 제거하면 CROSS JOIN 이 될 수 있다.
• 즉, 어떤 구문을 사용했는지가 중요한게 아니라, 어떤 조인조건을 주었느냐에 따라 조인 종류가 결전된다.

5.9.1.4 NATURAL JOIN

SQL 1 : 전통방식?

SELECT *
  FROM employees e
     , JOIN salaries s
 WHERE e.emp_no = s.emp_no
;

표준 Inner Join

SELECT *
  FROM employees e
 INNER JOIN salaries s
    ON e.emp_no = s.emp_no
;

Using 을 이용한 조인

SELECT *
  FROM employees e
 INNER JOIN salaries s
 USING (emp_no)
;

Natural Join

SELECT *
  FROM employees e
 NATURAL JOIN salaries s
;

5.9.2 조인 알고리즘

• MiriaDB 5.3 이전 : 네스티드 루프 알고리즘만 사용
• MiriaDB 5.3 이후 : 다양한 형태의 알고리즘 도입. 활용율은 높지 않지만 개선중.
• MiriaDB 5.3 부터 지원되는 알고리즘의 종류
  • 단순 네스티드 루프(Simple Nested Loop)
  • 블록 네스티드 루프(Block Nested Loop)
  • 블록 네스티드 루프 해시(Block Nested Loop Hash)
  • 블록 인덱스(Block Index Join, Batched Key Access)
  • 블록 인덱스 해시(Block Index Hash Join, Batched Key Access Hash)

5.9.2.1 조인 캐시 레벨(join_cache_level) (MariaDB)

• 블럭 기반 조인 알고리즘의 활용 방식
  • Flat 방식 : 조인버퍼에 레코드 필드를 복사하는 방식
  • Incremental 방식 : 포인터만 조인버퍼에 저장하는 방식
• 블럭 기반 조인 알고리즘의 종류 8가지
  • 1. 블록 네스티드 루프(Block Nested Loop - Flat)
  • 2. 블록 네스티드 루프(Block Nested Loop - Incremental)
  • 3. 블록 네스티드 루프(Block Nested Loop Hash - Flat)
  • 4. 블록 네스티드 루프(Block Nested Loop Hash - Incremental)
  • 5. 배치 키 엑세스(Batched Key Access - Flat)
  • 6. 배치 키 엑세스(Batched Key Access - Incremental)
  • 7. 배치 키 엑세스(Batched Key Access Hash - Flat)
  • 8. 배치 키 엑세스(Batched Key Access Hash - Incremental)
• 알고리즘 선택 optimizer_switch 변수 3가지 기본 설정값
  • join_cache_Incremental=on
  • join_cache_hashed=on
  • join_cache_bka=on
• join_cache_level 시스템 변수
  • join_cache_level=1 (0~8)
  • 설정된 값 이하 번호만 수행 가능
  • batched_key_access (MySQL)
• 기타 조인시 optimizer_switch
  • outer_join_with_cache
  • semijoin_with_cache

5.9.2.2 조인 버퍼 설정 (MariaDB)

조인 버퍼 설정

SET optimizer_switch='optimizer_join_buffer_size=on';
SET join_cache_space_limit=5*1024*1024;

• 설정된 5M 한도 내에서 적절하게 필요한만큼 메모리 공간 할당

5.9.2.3 단순 네스티드 루프(Simple Nested Loop, NL)

• 기존 방식, 조인 버퍼 사용 안함

• SQL 1

EXPLAIN
SELECT d.dept_name
     , e.first_name
  FROM departments d
     , employees e
     , dept_emp de
 WHERE d.dept_no = de.dept_no
   AND de.emp_no = e.emp_no
;
+------+-------------+-------+--------+---------------------------+-------------+---------+---------------------+-------+-------------+
| id   | select_type | table | type   | possible_keys             | key         | key_len | ref                 | rows  | Extra       |
+------+-------------+-------+--------+---------------------------+-------------+---------+---------------------+-------+-------------+
|    1 | SIMPLE      | d     | index  | PRIMARY                   | ux_deptname | 122     | NULL                |     9 | Using index |
|    1 | SIMPLE      | de    | ref    | PRIMARY,ix_empno_fromdate | PRIMARY     | 12      | employees.d.dept_no | 20696 | Using index |
|    1 | SIMPLE      | e     | eq_ref | PRIMARY                   | PRIMARY     | 4       | employees.de.emp_no |     1 |             |
+------+-------------+-------+--------+---------------------------+-------------+---------+---------------------+-------+-------------+

5.9.2.4 블록 네스티드 루프(Block Nested Loop, BNL)

• SQL 1 : 블럭 조인 버퍼 없이 수행된다면?

SET join_cache_level=0;
EXPLAIN
SELECT *
  FROM dept_emp de
     , employees e
 WHERE de.from_date > '1995-01-01'
   AND e.emp_no > 109004
;
+------+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
|    1 | SIMPLE      | e     | range | PRIMARY       | PRIMARY | 4       | NULL | 149711 | Using where |
|    1 | SIMPLE      | de    | ALL   | ix_fromdate   | NULL    | NULL    | NULL | 331143 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+

• e 를 루프 도는 수만큼 de 의 동일 자료를 반복 엑세스

• SQL 2 : 블럭 조인 버퍼 사용

SET join_cache_level=8;
EXPLAIN
SELECT *
  FROM dept_emp de
     , employees e
 WHERE de.from_date > '1995-01-01'
   AND e.emp_no > 109004
;
+------+-------------+-------+-------+---------------+---------+---------+------+--------+-------------------------------------------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows   | Extra                                           |
+------+-------------+-------+-------+---------------+---------+---------+------+--------+-------------------------------------------------+
|    1 | SIMPLE      | e     | range | PRIMARY       | PRIMARY | 4       | NULL | 149711 | Using where                                     |
|    1 | SIMPLE      | de    | ALL   | ix_fromdate   | NULL    | NULL    | NULL | 331143 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+-------+---------------+---------+---------+------+--------+-------------------------------------------------+

• SQL 3 : 블럭 조인 버퍼 사용, de 를 먼저 드라이빙하도록 조건 변경

EXPLAIN
SELECT *
  FROM dept_emp de
     , employees e
 WHERE de.from_date > '2001-01-01'
   AND e.emp_no > 109004
;
+------+-------------+-------+-------+---------------+-------------+---------+------+--------+-------------------------------------------------+
| id   | select_type | table | type  | possible_keys | key         | key_len | ref  | rows   | Extra                                           |
+------+-------------+-------+-------+---------------+-------------+---------+------+--------+-------------------------------------------------+
|    1 | SIMPLE      | de    | range | ix_fromdate   | ix_fromdate | 3       | NULL |   5324 | Using index condition                           |
|    1 | SIMPLE      | e     | range | PRIMARY       | PRIMARY     | 4       | NULL | 149711 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+-------+---------------+-------------+---------+------+--------+-------------------------------------------------+

• 드라이빙 테이블을 조인 버퍼에 담고
• 드리븐 테이블을 읽어 버퍼와 NL 조인
• 마치 드리븐 테이블이 선행 테이블이 되어 조인 되는 듯.
• 드라이빙 테이블의 결과가 너무 많으면 1~4의 과정을 여러번 반복
• 쿼리에 필요한 컬럼만 버퍼에 저장되므로 효율적
• 조인 버퍼 레코드 포멧 개선
  • 가변길이 컬럼의 최대 길이만큼 0 으로 채우던 작업 안하도록 개선
  • Null 값 공건 차지 안하도록 개선
• 조인 버퍼의 증분모드 지원 : 3개 이상 조인시 중복 저장 개선, 포인터만 저장

• 아우터조인과 세미조인을 위한 알고리즘 사용
  • 조인버퍼에 매치 플래그 사용

5.9.2.5 블록 네스티드 루프 해시(Block Nested Loop Hash, BNLH) (MariaDB)

• 동등조건 일때

• SQL

SET join_cache_level=8;
SET optimizer_switch='mrr=on';
SET optimizer_switch='mrr_sort_keys=on';
EXPLAIN
SELECT *
  FROM dept_emp de
     , departments d
 WHERE de.dept_no = d.dept_no
;
+------+-------------+-------+-------+---------------+-------------+---------+---------------------+-------+-------------------------------------------------------+
| id   | select_type | table | type  | possible_keys | key         | key_len | ref                 | rows  | Extra                                                 |
+------+-------------+-------+-------+---------------+-------------+---------+---------------------+-------+-------------------------------------------------------+
|    1 | SIMPLE      | d     | index | PRIMARY       | ux_deptname | 122     | NULL                |     9 | Using index                                           |
|    1 | SIMPLE      | de    | ref   | PRIMARY       | PRIMARY     | 12      | employees.d.dept_no | 20696 | Using join buffer (flat, BKAH join); Key-ordered scan |
+------+-------------+-------+-------+---------------+-------------+---------+---------------------+-------+-------------------------------------------------------+

• 해시조인은 빌드 단계와 프로브 단계로 실행된다.
• 조인 컬럼의 해시값을 계산하여 해시테이블 생성(조인 버퍼)
• 빌드 테이블이 큰 경우 작은 단위로 잘라서 여러번 수행

5.9.2.6 블록 인덱스 조인(Block Index Join, Batched Key Access, BKA)

• 내부적으로 멀티레인지 리드(Multi Range Read, MRR) 기능 사용
• 배치 키 엑세스
  • 드라이빙 테이블을 읽어서 필요컬럼과 조인컬럼을 조인버퍼에 저장
  • 조인 버퍼 가 가득 차면 버퍼의 내용을 MRR 엔진으로 전송
  • MRR 엔진은 넘겨받은 내용을 적절한 방식으로 정렬한 다음
  • 드리븐 테이블을 읽어서 리턴하면 조인 완료

• 정렬기준 3가지
• 1. Rowid 기준 정렬
  • 조인 컬럼을 이용해 드리븐 테이블의 인덱스를 읽고
  • 인덱스 끝에 있는 Rowid(MyISAM, Aria) 나 PK(InnoDB, XtraDB) 를 정렬한 뒤
  • 정렬된 순서대로 드리븐 테이블을 읽는 방식
  • Extra : Rowid-ordered scan
  • 참조 : 5.7.1절, 그림5-16
• 2. Key 기준 정렬
  • 드라이빙 테이블의 조인 컬럼을 정렬하여
  • 정렬된 순서대로 드리븐 테이블을 읽는 방식
  • 조인 조건이 드리븐 테이블의 키인 경우
  • Extra : Key-ordered scan
  • 참조 : 5.7.2절, 그림5-17
• 3. Key 와 Rowid 모두 정렬
  • 참조 : 5.7.2절, 그림5-18

5.9.2.7 블록 인덱스 해시 조인(Block Index Hash Join, Batched Key Access Hash) (MariaDB)

• 배치 키 엑세스 방식으로 읽어서 해시 조인을 수행하는 방식
  • 드라이빙 테이블을 멀티 레인지 리드 접근 방법으로 조회해서 해시테이블 생성
  • 드리븐 테이블을 읽어서 해시테이블 검색해서 결과 반환

5.9.3 조인의 주의사항

5.9.3.1 조인 실행 결과의 정렬 순서

• SQL

EXPLAIN
SELECT *
  FROM dept_emp de
     , employees e
 WHERE de.emp_no = e.emp_no
   AND de.dept_no = 'd005'
;
+------+-------------+-------+--------+---------------------------+---------+---------+---------------------+--------+-------------+
| id   | select_type | table | type   | possible_keys             | key     | key_len | ref                 | rows   | Extra       |
+------+-------------+-------+--------+---------------------------+---------+---------+---------------------+--------+-------------+
|    1 | SIMPLE      | de    | ref    | PRIMARY,ix_empno_fromdate | PRIMARY | 12      | const               | 165571 | Using where |
|    1 | SIMPLE      | e     | eq_ref | PRIMARY                   | PRIMARY | 4       | employees.de.emp_no |      1 |             |
+------+-------------+-------+--------+---------------------------+---------+---------+---------------------+--------+-------------+

• de 의 프라이머리 키가 (dept_no + emp_no) 이므로 emp_no 로 정렬된 결과를 예측할 수 있다.
• 단, 실행계획이 달라진다면? 정렬결과는 보장되지 않는다.
• 정렬된 결과를 보장해 주는 것은 ORDER BY 절 뿐이다.

5.9.3.2 INNER JOIN 과 OUTER JOIN 의 선택

• 아우터 조인을 사용할 필요가 없는데도 아우터 조인을 사용하는 경우
  • 습관적으로 혹은 데이터가 안나올까봐
• 아우터 조인이 느리다고 생각하여 억지로 이너조인으로 바꾸려는 경우
  • 결과가 같다면 성능도 같다.
• 결론은
  • 선택은 성능이 아닌 목적(업무 요건)
  • 막연한 두려움이 아닌 정확한 의도

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 보다 커지면 캐시히트율을 계산하여 캐시를 어떻게 유지할지 결정