조인 실행 과정(TIL)

데이터베이스 & SQL/데이터베이스

조인 실행 과정(TIL)

Recfli 2024. 4. 30. 16:02

1. NL 조인

Nested Loops는 가장 기본적인 조인 실행 방법으로, 외부 테이블과 내부 테이블 전체를 하나하나 스캔하며 결합 방식에 맞으면 리턴하는 방식이다. 친절한 SQL 튜닝에서는 다음과 같은 SQL과 PL/SQL이 대응된다.

// SQL
select e.사원명, c.고객명, c.전화번호 from 사원 e, 고객 c
where e.입사일자 >= '19960101' and c.관리사원번호 = e.사원번호

// PL/SQL 코드
begin
  for outer in (select 사원번호, 사원명 from 사원 where 입사일자 >= '199600101')
  loop -- outer 루프
    for inner in (select 고객명, 전화번호 from 고객
                  where 관리사원번호 = outer.사원번호)
    loop -- inner 루프
      dbms_output.put_line(
        outer.사원명 || ' : ' || inner.고객명 || ' : ' || inner.전화번호);
    end loop;
  end loop;
end;

NL 조인 방식이 일어나면 개별 테이블에 대하여 모두 인덱스를 통한 접근이 가능하다. 그래서 외부 테이블과 내부 테이블을 무엇을 선택하느냐에 따라 다르다. 특히 내부 테이블의 결합 키 필드에 인덱스가 존재하느냐 안하느냐의 차이가 크다. 왜냐하면 없는 경우엔 외부 테이블을 읽는 횟수만큼 풀 테이블 스캔하기 때문이다. 이는 심각한 성능저하를 가져온다.

뭔가 가장 무식한 방법 같지만, NL 조인의 장점은 모든 DBMS가 지원하고 한 번의 단계에서 처리하는 레코드 수가 적으면 다른 방식에 비해 메모리를 적게 쓴다는 장점이 있다. 특히 OLTP 환경에서 소량의 데이터를 조인해서 가져올 때 유리하며, 인덱스 설정이 잘 되어 있으면 더 좋은 성능을 보인다.

하지만 반대로 가져와야할 데이터가 많은 경우에는 좋지 않은 방식이다. 왜냐하면 가져와야 하는 데이터의 외부 테이블 내 레코드 개수가 많으면, 인덱스로 찾는 경우 수직적 탐색 과정이 엄청나게 일어나기 때문이다. 이땐 다음에 설명하는 다른 방식을 사용해야 한다.

이 외에도 하나의 장점이 더 있다면, 부정 조건(!=, <>) 결합이 가능한 유일한 방식이라는 점이다. 그래서 소량의 데이터 가져올 때, 부정 조건일 때 NL 조인을 사용하면 된다.

2. 소트 머지 조인

데이터베이스 메모리 글에서 공동 메모리 영역에 관해 설명한 이유가 여기서 나온다. 대량의 데이터를 가져와야 해서 인덱스를 타는 것이 효용이 없는 경우, 인덱스가 없는 경우에는 모든 데이터를 개별 세션을 가진 프로세스의 메모리 영역에서 데이터를 정렬해 사용하는 방법이 있다.

소트 머지 조인이 일어나면 외부, 내부 테이블 데이터를 가져와 SQL에 적힌 조건대로 정렬한다. 메모리에 정렬된 기준으로 NL 조인과 동일한 방식으로 테이블 순회 과정을 거친다. 그러면, 인덱스를 타는 것의 단점인 수직적 탐색이 늘어나는 문제를 겪지 않아도 된다. 또한, Lock이 걸려있는 버퍼 캐시에 접근해 데이터를 매번 확인하지 않아도 된다. 이젠 프로세스 개별 공간에 있는 데이터를 확인하면 된다. 대신 문제가 있다. 한번에 가져와서 개별 프로세스에서 정렬을 할텐데 그 메모리가 수용 가능한 메모리를 벗어나면 안된다. 이런 문제를 해결하는 다른 조인 방법도 있는데 이는 다음에 설명할 해시 조인이다.

그러면, 해시 조인으로 하면 되지 왜 소트 머지 조인을 사용하냐는 의문이 들 수 있다. 머지 소트 조인은 조건이 덜 명확할 때 유리하다. 예를 들어, 조건식이 등호가 아닌 부등호인 경우에서도 사용을 할 수 있다. NL 조인이 안되는 건 아니지만 이 때 더 많은 데이터를 빨리 가져올 수 있다. 또한 완전히 조인 조건식이 없는 크로스 조인에도 사용이 가능하다. 추가적으로 테이블이 결합 키로 정렬되어있다면 정렬을 생략할 수도 있다.

3. 해시 조인

해시 조인은 소트 머지 조인처럼 양쪽 테이블을 정렬하는 부담을 없앨 수 있는 방법이다. 해시 조인은 작은 쪽 테이블을 읽어 해시 테이블을 만든다. 이 때 해시 테이블 상에 있는 값들은 읽을 필요가 있는 모든 레코드와 함께 저장된다. 그렇기에 개별 프로세스의 메모리에 데이터를 올리지만 소트 머지 조인에 비해 같은 메모리에 더 많은 레코드를 담을 수 있다. 그리고 큰 쪽 테이블을 읽어 해시 테이블을 탐색하며 NL 방식과 동일하게 조인한다.

단점으로는 양쪽 테이블 레코드를 한번 풀테이블 스캔을 해야 한다는 점과 출력되는 해시 값은 입력 순서를 알지 못해, 등치 결합('=')에만 사용할 수 있다는 점이 있다.

또한 소트 머지 조인과 해시 조인 방식에서 오해할 수 있는 부분이 있다. 각각의 방법은 모두 처음 조인 전 데이터를 한 번 정렬하고 해시 테이블을 생성하는 과정이 있다. 이 때는 똑같이 버퍼 캐시에서 Lock 경쟁을 해서 데이터를 읽어온 뒤 일어나는 것이다. 그 이후에 정렬된, 해시 테이블화된 데이터를 가져올 때에는 개별 메모리이니 경쟁이 없는 것이다. 그 점을 꼭 기억하고 넘어가자.

기초적인 SQL 관련 내용과 인덱스 튜닝 방법을 알고 싶어 두 개의 책과 여러 블로그 글들을 보았었다. 느낀 건 직접 튜닝 해보고 실행 계획을 보아야지 실력이 늘 수 있는 부분이라는 것이다. 이 이상의 내용은 사실 개별 케이스에 관한 내용이고 인덱스 컬럼을 어떻게 설정해서, 힌트를 설정해서, SQL을 작성해서 적절한 실행 계획을 동작시키는지에 관한 내용이었다. 그래서 여기까지만 하려고 한다. 다음에는 실제로 겪은 문제를 해결할 때 마다 공유하도록 하겠다.

[ 참고 자료 ]

친절한 SQL 튜닝 - 조시형

SQL 레벨업 - 미크