Join 기법

DB Join 기법에 대한 정리

 

• 논리적 조인
  • Inner Join
  • Outer Join
  • Natural Join
    • 두 테이블의 공통 속성을 매개체로 테이블의 정보를 묶어내는 연산
    • ON 절을 안써줘도 같은 컬럼명 끼리 알아서 매핑되는?
  • Equal Join
    • 두 테이블의 공통 정보 조인 연산
    • 단순 조인 또는 내부 조인
  • Cross Join
  • Semi Join
• 물리적 조인
  • Nested loop
    • 중첩된 반복문과 유사한 방식으로 조인을 수행
    • 선행 테이블의 처리 범위를 하나씩 액세스 하면서 추출된 값으로 연결할 테이블을 조인하는 방식
    • 선행테이블의 결정과 처리범위에 따라 성능이 좌우, 선행테이블이 작을수록 유리
      • 선수행(먼저 액세스 되는쪽, 드라이빙 테이블) 범위가 중요, 후수행은 랜덤액세스
      • 결과 행의 수가 적은 테이블을 순서상 선행 테이블로 선택하는 것이 유리
    • MySQL은 Nested Loop만 지원
      • 8.0.18 버전 부터 hash join도 지원
  • Merge (Sort Merge)
    • 양쪽 테이블의 처리범위를 각자 액세스, 정렬 후 연결 조건으로 merge
    • 랜덤 엑세스가 줄어들어 부하를 감소시키지만, NL Join 보다 사용 빈도는 적음
    • 조인 컬럼의 인덱스가 없어도 사용 가능
    • 특징
      • 동시적으로 처리된다
      • 부분 범위 처리를 할 수가 없으며, 항상 전체 범위를 처리(?)
      • 조인 방향과는 전혀 무관 (먼저, 동시적으로 각자 정렬하니까?)
      • 전체 범위 처리 하는경우 유리 (왜?)
      • 조인 작업을 위해 항상 정렬 작업이 발생하는 것은 아님
  • Hash
    • 해시 테이블 사용하여 두 입력간에 일치하는 행을 찾는 조인
      • 조인 컬럼을 기준으로 해싱하여 서로 동일한 해쉬 값을 갖는 것들 중, 실제 값이 같은지를 비교하며 조인을 수행
    • 작은 테이블과 큰 테이블의 조인시에 유리
    • Equal 조인에서만 가능
      • 해시 함수 적용(해싱)할 때, 큰 값이 항상 큰 값으로 해싱되고, 작은 값이 항상 작은 값으로 해싱 된다는 보장이 없으므로
        • 버티카에서는 Equal 조인 아니어도 hash join으로 동작하는 거 같던데,,,,,, 확인 필요
    • NL join의 random 액세스 비용과 sort merge join의 미리 양쪽 집합을 정렬하는 비용도 없다, 그렇지만 hash 테이블 생성 비용이 수반됨
      • 타 조인기법보다, CPU 자원을 많이 소비하며, 양쪽 테이블의 스캔이 동시에 일어난다.
    • 특징
      • 두 개 조인 테이블 중, 작은 rowset을 가지고 해쉬 테이블을 메모리에 생성
        • 메모리에 적재할 수 있는 크기 보다 커지면, 임시 영역(디스크)에 해쉬 테이블을 저장
      • 기존 인덱스를 전혀 사용하지 않는다.
      • 대용량 조인일 때, 큰 해시영역이 필요하므로 메모리를 많이 사용할 수 있어 오버헤드 발생 할 수 있음

드라이빙 테이블(DRIVING TABLE)

조인시 먼저 액세스되는 쪽을 드라이빙 테이블(DRIVING **TABLE, OUTER TABLE)**이라고 하며,

나중에 **액세스 되는 테이블을 드리븐 테이블(DRIVEN TABLE, INNER TABLE)**이라고 한다.

인덱스(INDEX)의 존재 및 우선순위 혹은 FROM절에서의 TABLE 지정 순서에 영향을 받으며

어느 테이블이 먼저 엑세스되느냐에 따라 속도의 차이가 크게 날 수 있으므로 많은 양의 데이터를 다룰 때, 드라이빙 테이블은 매우 중요하다.

결정 규칙

비용기반 옵티마이저(Cost-Based Optimizer, CBO)는 규칙의 우선순위가 아닌 쿼리를 수행하는데 소요되는 예상 비용을 바탕으로 실행계획을 생성한다. 통계정보, DBMS 설정정보, DBMS 버전 등의 차이로 인해 똑같은 SQL문이라도 서로 다른 실행계획이 생성될 수 있다.

1. 두 컬럼 모두 각각 인덱스가 있는 경우

• 옵티마이저의 판단으로 각 테이블의 통계 정보에 있는 레코드 건수에 따라 드라이빙 테이블과 드리븐 테이블을 결정한다.

2. 한쪽의 컬럼에만 인덱스가 있는 경우

• 드리븐 테이블에 인덱스가 없다면 조인을 하는 과정에서 드라이빙 테이블의 레코드 하나당 드리븐 테이블을 매번 풀 스캔해야만 한다. 따라서 인덱스가 없는 테이블이 드라이빙 테이블로 결정되고 인덱스가 있는 테이블이 드리븐 테이블로 결정된다.
  • 조건절의 조건을 읽은 후 조인 시도
  • 조건절의 건수만큼 조인 시도, 드리븐 테이블은 랜덤 액세스 (왜 랜덤액세스여)

3. 두 컬럼 모두 인덱스가 없는 경우

• 어느 테이블을 드라이빙으로 선택하더라도 드리븐 테이블의 풀 스캔은 발생하기 때문에 스캔되는 레코드 수에 따라 옵티마이저가 적절히 드라이빙 테이블을 선택하게 된다.
  • 그나마 레코드 수가 적은 테이블이 드리븐으로?
• 조인이 수행될때 양쪽 테이블의 컬럼에 모두 인덱스가 없을 때만 드리븐 테이블을 풀스캔한다.
• 나머지 경우에는 드라이빙 테이블을 풀 테이블 스캔을 사용할 수는 있어도, 드리븐 테이블을 풀 테이블 스캔으로 접근하는 실행 계획은 옵티마이저가 거의 만들어내지 않는다.

옵티마이저

• SQL을 빠르고 효율적으로 수행할 최적의 경로를 생성하는 DBMS 내부 핵심엔진
• CBO - 비용기반 옵티마이저
  • 현재 대부분의 DBMS (MySQL도)
  • 통계정보
    • CBO에서 가장 중요하게 사용되는 정보
    • MySQL - 자동으로 계속 갱신됨, ANALYZE 명령어로 수동 갱신가능 (InnoDB 경우 ANALYZE 실행중 읽기 쓰기 모두 불가)
• RBO
  • 조인 컬럼 인덱스의 존재 유무가 중요한 판단 기준

 

 

쿼리 실행절차

1. SQL 파싱 - MySQL 엔진
   • SQL 파스트리 생성 - 요청받은 SQL 문장을 파싱하여, MySQL서버가 이해할 수 있는 수준으로 분리 (MySQL 서버의 SQL 파서가 수행)
2. 실행계획 수립 - MySQL 엔진
   • SQL 파스 트리를 참조하여
     • 불필요한 조건 제거 및 복잡한 연산 단순화
     • 조인이 있는 경우, 어떤 순서로 읽을 지 결정
     • 조건과 통계정보를 참조해 사용할 인덱스 결정
     • 임시테이블을 통해 다시 가공해야 하는지 결정
3. 수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청, 받은 레코드를 MySQL 엔진이 조인하거나 정렬하는 작업 수행
   • MySQL 엔진 + 스토리지 엔진이 처리