TIL

9.3(1) MRR과 블록 네스티드 루프 조인

• 옵티마이저는 통계 정보와 옵티마이저 옵션을 결합해 최적의 실행 계획을 수립한다.
  • 조인 관련 옵티마이저 옵션
  • 옵티마이저 스위치 옵션

9.3.1 옵티마이저 스위치 옵션

• 옵티마이저 스위치 옵션은 optimizer_switch 시스템 변수를 통해 여러 개의 옵션을 세트로 묶어 설정하는 방식으로 제어할 수 있다.
• 각각 옵션은 default, on, off 중 하나를 선택할 수 있다.
• 글로벌과 세션별 그리고 특정 쿼리에만 설정할 수도 있다.

-- // MySQL 서버 전체적으로 옵티마이저 스위치 설정
SET GLOBAL optimizer_switch='indexmerge=on, index_merge_union=on,...';

-- // 현재 커넥션의 옵티마이저 스위치만 설정
SET SESSION optimizer_switch='index_merge=on, index_merge_union=on,...';

-- // 현재 쿼리에만 설정
SELECT /*+ SET_VAR(optimizer_switch='condition_fanout_filter=off')*/
...
FROM ...

MRR과 배치 키 엑세스(mrr & batched_key_access)

• MySQL에서 원래 지원하던 조인 방식은 네스티드 루프 조인(Nested Loop Join)이다.
  • 드라이빙 테이블의 레코드를 한 건씩 읽어 드리븐 테이블의 일치하는 레코드를 찾아서 조인을 수행하는 것
  • 조인 처리는 MySQL 서버가 하지만 실제 드리븐 테이블에서 레코드를 검색하는 것은 스토리지 엔진이 담당한다.
  • 한 건씩 드리븐 테이블에서 검색하는 작업은 최적화를 수행할 수 없었다.
• 기존 조인 방식을 보완하기 위해 MRR(Multi-Range Read)을 지원하게 되었다.
  • 메뉴얼에서는 DS_MRR(Disk Sweep Multi-Range Read)라고 한다.
  • 드라이빙 테이블의 레코드를 읽은 뒤 즉시 조인을 실행하지 않고 조인 버퍼에 버퍼링한 후 한 번에 스토리지 엔진에 요청한다.
  • 스토리지 엔진은 읽을 레코드를 페이지 정렬 순서로 접근해서 페이지 읽기를 최소화할 수 있다.
• MRR을 응용한 조인 방식을 BKA(Batched Key Access) 조인이라고 한다.
  • optimizer_switch=’batched_key_access=on’;으로 활성화 (기본값은 비활성화)
  • BKA 조인이 큰 도움이 되는 경우도 있지만 부가적인 정렬이 필요해지면서 성능에 악영향을 미치는 경우도 있다.

블록 네스티드 루프 조인(block_nested_loop)

• 블록 네스티드 루프 조인은 조인의 연결 조건이 되는 칼럼에 모두 인덱스가 있는 경우 사용되는 조인 방식이다.
  • 이 외에 MySQL에서는 대부분 네스티드 루프 조인이 사용된다.
  • 블록 네스티드 루프 조인은 기본적으로 활성화되어 있다.
• 블록 네스티드 루프와 네스티드 루프의 가장 큰차이
  • 조인 버퍼 사용 유무
    • 블록 네스티드 루프 조인에서 사용된다.
    • 조인이 완료되면 버퍼는 해제된다.
  • 드라이빙 테이블과 드리븐 테이블의 조인 순서
• 블록 네스티드 루프 조인 예제
  • dept_emp.from_date에는 인덱스가 생성되어 있고 emp_no는 프라이머리 키다.

    SELECT *
    FROM dept_emp de, employees e
    WHERE de.from_date>'1995-01-01' AND e.emp_no<109004;
      
    |--|-----------|-----|-----|-----------|-------------------------------------|
    |id|select_type|table|type |key        |Extra                                |
    |--|-----------|-----|-----|-----------|-------------------------------------|
    |1 |SIMPLE     |de   |range|ix_fromdate|Using index condition                |
    |1 |SIMPLE     |e    |range|PRIMARY    |Using join buffer (block nested loop)|
    |--|-----------|-----|-----|-----------|-------------------------------------|
  

  • dept_emp가 드라이빙 테이블, employees가 드리븐 테이블로 쓰였으며 employees를 조인할 때 조인 버퍼를 통해 블록 네스티드 루프 조인을 한다는 것을 확인할 수 있다.
    1. dept_emp 테이블에서 인덱스를 이용해 from_date>’1995-01-01’인 레코드를 검색
    2. 조인에 필요한 dept_emp의 나머지 칼럼을 모두 조인 버퍼에 저장
    3. employees 테이블 프라이머리 키로 emp_no<109004인 레코드 검색
    4. 3번 검색 결과에 2번에서 캐시된 조인버퍼 레코드를 결합해서 반환
  • employees 테이블 결과에서 조인 버퍼에 캐시된 dept_emp 결과를 조인하기 때문에 조인 순서가 거꾸로인 것처럼 실행된다.
  • 일반적으로 조인 수행 후의 결과는 드라이빙 테이블 순서에 의해 결정되지만 조인 버퍼가 사용되는 경우에는 순서가 흐트러질 수 있는 것이다.

MySQL 8.0.18부터 해시 조인 알고리즘에 도입되어 8.0.20 버전부터는 블록 네스티드 루프 조인이 더 이상 사용되지 않고 해시 조인 알고리즘이 대체되어 사용된다.