MySQL의 Join 기법

Join

두 개 이상의 테이블을 하나의 집합으로 만드는 연산

Join 기법들

• 대표적인 기법들만 소개할 예정
• NL Join이 디폴트

기법 설명

Nested Loop Join (NL Join)	선행 테이블에서 액세스한 각 값을 후행 테이블과 조인하는 방식
Sort Merge Join (SM Join)	조인 컬럼을 기준으로 데이터를 정렬하여 조인을 수행하는 방식
Hash Join	해시 함수를 이용해서 데이터를 조인하는 방식

NL Join

• 프로그래밍에서 사용하는 중첩 반복문과 유사한 방식으로 조인 수행
• 반복문 외부의 테이블은 선행 테이블 혹은 외부 테이블(Outer Table) 이라 부른다.
• 반복문 내부에 있는 테이블은 후행 테이블 또는 내부 테이블(Inner Table) 이라 부른다.
• 선행 테이블 의 조건을 만족하는 행을 추출하여 후행 테이블을 읽으면서 조인을 수행한다.
  • 선행 테이블의 조건을 만족하는 모든 행의 수만큼 반복 수행
  • 결과행의 수가 적은 테이블을 선행 테이블로 선택하는 것이 좋다.

작업 방법

1. 선행 테이블에서 주어진 조건을 만족하는 행을 찾는다.
2. 선행 테이블의 조인 키 값을 가지고 후행 테이블에 [조인 키가 존재 확인 후 조인] 을 시도한다.
   1. [ ] 내부의 작업을 시도하러 간다.
3. 후행 테이블 의 인덱스에 선행 테이블의 조인 키가 존재하는지 확인한다.
   1. 선행 테이블의 조인 값이 후행 테이블에 존재하지 않으면 선행 테이블 데이터는 필터링된다.
      1. 더 이상 조인 작업을 진행할 필요가 없다.
4. 인덱스에서 추출된 레코드 식별자를 이용해 후행 테이블을 엑세스한다.
   1. 후행 테이블에 주어진 조건이 모두 만족하면 해당 행을 추출버퍼에 넣는다.
5. 앞의 작업을 반복 수행한다.

특징

• 랜덤 엑세스 방식으로 데이터를 읽는다.
  • 많은 I/O 가 발생할 수 있어 무조건 좋다고 볼 수는 없다.
• 조인이 성공하면 바로 조인 결과를 사용자에게 보여준다.
  • 최대한 빠르게 화면에 보여줘야 하는 온라인 프로그램에 적합한 조인 기법이다.
• 후행 테이블에 인덱스가 존재해야 사용 가능하다.
• 선행 테이블이 작고 처리 범위가 좁은 경우 효과적이다.

SM Join

• 주로 스캔 방식으로 데이터를 읽는다.
• NL Join에서 부담이 되던 넓은 범위의 데이터를 처리할 때 이용되는 조인 기법이다.

작업 방법

1. 선행 테이블에서 주어진 조건을 만족하는 행을 찾는다.
2. 선행 테이블의 조인 키를 기준으로 정렬작업을 수행한다.

• 1 ~ 2번 작업을 선행 테이블의 조건을 만족하는 모든 행에 대해 반복 수행

1. 후행 테이블에서 주어진 조건을 만족하는 행을 찾는다.
2. 후행 테이블의 조인 키를 기준으로 정렬 작업을 수행한다.

• 3 ~ 4번 작업을 후행 테이블의 조건을 만족하는 모든 행에 대해 반복 수행

1. 정렬된 결과를 이용하여 조인을 수행한다.
2. 조인에 성공하면 추출버퍼에 넣는다.

특징

• 인덱스가 없어도 사용 가능하다.
• 앞 단계의 작업에서 정렬작업이 미리 수행되어있으면, 정렬 작업이 다시 수행되지 않을 수 있다.
• 정렬할 데이터가 많아 메모리에서 모든 정렬 작업을 수행하기 어려운 경우에는 임시 영역 (디스크)을 사용하기 때문에 성능이 떨어질 수 있다.
  • 대량의 조인 작업의 경우 CPU 위주의 작업을 수행하는 Hash Join이 성능상 유리하다.
• 동등 조인과 비동등 조인에 대한 작업도 가능하다.
  • 동등 조인
    • 동등한 값인지 비교하는 조인 (where절 에서 = 를 쓰는 방법)
  • 비동등 조인
    • 동등하지 않은 값인지 비교하는 조인 (where절 에서 <,>,≤,≥ 등을 사용하는 방법)

Hash Join

• 해싱 기법을 이용하여 조인을 수행한다.
• 조인될 두 테이블 중 하나를 해시 테이블로 선정하여 조인될 테이블의 조인 키 값을 Hash 알고리즘으로 비교하여 매치되는 결과값을 얻는 방식이다.
• HASH JOIN은 비용 기반 옵티마이저를 사용할 때만 사용될 수 있는 조인 방식이며 동등 조인에서만 사용될 수 있다.
• 주로 많은 양의 데이터를 조인해야 하는 경우에 주로 사용한다.

작업 방법

1. 선행 테이블에서 주어진 조건을 만족하는 행을 찾는다.
2. 선행 테이블의 조인 키를 기준으로 해시함수를 적용하여 해시 테이블을 생성한다.

• 1 ~ 2번 작업을 선행 테이블의 조건을 만족하는 모든 행에 대해 반복 수행한다.

1. 후행 테이블에서 주어진 조건을 만족하는 행을 찾는다.
2. 후행 테이블의 조인 키를 기준으로 해시 함수를 적용하여 해당 버킷을 찾는다.
3. 조인에 성공하면 추출버퍼에 넣는다.

• 3 ~ 5번 작업을 후행 테이블의 조건을 만족하는 모든 행에 대해 반복 수행한다.

특징

1. 조인 칼럼의 인덱스를 사용하지 않기 때문에 조인 칼럼의 인덱스가 존재하지 않을 경우에도 사용할 수 있는 기법이다.
2. 메모리 사용이 큰 대용량 테이블 조인시 메모리 외에 임시영역(PGA 메모리)까지 사용하여 저장할 수 있어 유리하다.

NL, SM, Hash Join의 공통점

• 모두 성공한 결과를 추출버퍼에 넣는다.
• 추출 버퍼는 SQL문의 실행 결과를 보관하는 버퍼로서, 일정 크기를 설정해 추출 버퍼에 결과가 모두 차거나 더 이상 결과가 없어서 추출 버퍼를 채울 것이 없으면 결과를 사용자에게 반환한다.

Optimizer

• 데이터 딕셔너리(Data Dictionary)에 있는 오브젝트 통계, 시스템 통계 등의 정보를 사용해서 예상되는 비용을 산정한다.
• 여러 개의 실행 계획 중에서 최저 비용을 가지고 있는 계획을 선택해서 SQL을 실행한다.

최적화 과정

1. 사용자가 던진 쿼리 수행을 위해, 후보군이 될만한 실행계획을 찾는다.
2. 데이터 딕셔너리(Data Dictionary)에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상 비용을 산정한다.
3. 각 실행계획을 비교하여 최저비용을 갖는 하나를 선택한다.

종류

규칙 기반 Optimizer (Rule Based Optimizer : RBO)

• 실행 속도가 빠른 순으로 규칙을 세워두고 우선순위가 높은 방법을 채택한다.
• 규칙은 액세스 경로별 우선순위로서, 인덱스 구조, 연산, 조건절 형태 등이 순위를 결정 짓는 주요 원인이다.

규칙과 우선순위

비용 기반 Optimizer (Cost Based Optimizer : CBO)

• 쿼리를 수행하기 위해 소요되는 일의 양 또는 시간에 따른 비용이 최소한으로 나오는 실행 계획을 수행한다.
• 기준이 되는 비용은 “예상치”다.

비교

항목 규칙 기반 비용 기반

개념	사전에 정의된 규칙 기반	최소비용 계산 실행계획 수립
기준	실행우선 순위(Ranking)	액세스 비용(Cost)
인덱스	인덱스 존재 시 가장 우선시 사용	Cost에 의한 결정
성능	사용자 SQL작성 숙련도	옵티마이저 예측 성능
장점	판단이 매우 규칙적 실행 예상 가능	통계 정보를 통한 현실 요소 적용
단점	예측 통계정보 요소 무시	최소 성능 보장 계획의 예측 제어 어려움

관련 레퍼런스

• 버퍼 / 여러 조인 기법 / 조인 관련 쿼리 비용 / 조인 시간 복잡도 관련 설명

[데이터베이스] 10. DBMS의 버퍼 관리와 쿼리 비용 추정

• 실행계획 딥한 설명 (MySQL와 연관지어 설명된 레퍼런스)

MySQL 실행 계획(좋은 쿼리는 못 만들어도 뭐같은 쿼리는 만들지 말아야지)