Real MariaDB (2017년)
테이블 조인 0 0 58,231

by 구루비스터디 MariaDB 최적화 테이블 조인 [2019.08.11]


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


블럭 기반 조인 알고리즘의 종류 8가지join_cache_Incremental=offjoin_cache_hashed=offjoin_cache_bka=offjoin_cache_level(0~8)
1.Block Nested Loop - Flat)onononon:1~8, off=0~0
2.Block Nested Loop - Incremental)offononon:2~8, off=0~1
3.Block Nested Loop Hash - Flat)onoffonon:3~8, off=0~2
4.Block Nested Loop Hash - Incremental)offoffonon:4~8, off=0~3
5.Batched Key Access - Flat)ononoffon:5~8, off=0~4
6.Batched Key Access - Incremental)offonoffon:6~8, off=0~5
7.Batched Key Access Hash - Flat)onoffoffon:7~8, off=0~6
8.Batched Key Access Hash - Incremental)offoffoffon:8~8, off=0~7


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


"구루비 데이터베이스 스터디모임" 에서 2017년에 "Real MariaDB" 도서를 스터디하면서 정리한 내용 입니다.

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

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

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

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