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 의 선택

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