NL Join에서 인덱스 설계가 중요한 이유

NL Join과 인덱스

책 친절한 SQL 튜닝 272페이지를 보면, 다음 쿼리와 인덱스에서 잘못된 부분을 찾아 개선하라는 문제가 나온다.

# index: (SALE_ORG_ID, STRD_GRP_ID, STRD_ID, STC_DT)

select *
from PRA_HST_STC a, OOM_TRMS b
WHERE a.SALE_ORG_ID = :sale_org_id
and a.STRD_GRP_ID = b.STRD_GRP_ID
and a.STRD_ID = b.STRD_ID
order by a.STC_DT desc

index 공부하기 전에는 못맞췄을 것 같은데, 지금은 2가지 문제가 보인다. 내가 생각한 답을 말하자면, 나는 인덱스를 2개 만들 것 같다.

 

OOM_TRMS(b) 테이블에서(STRD_GRP_ID) or (STRD_ID) or (STRD_GRP_ID, STRD_ID) or (STRD_ID, STRD_GRP_ID) 이것들 중에서 하나를 만들 것이다.

 

근데 언뜻 보기에는 STRD_ID가 선택도가 높아 보여서 (strd_id, strd_group_id)로 만들 것 같긴 하다. (이 쿼리 자체로만 보면 칼럼 순서는 상관 없겠지만)

 

PRA_HST_STC(a) 테이블에서는 (SALE_ORG_ID, STC_DC)로 만들 것 같다.

 

그렇다면 2개의 인덱스 중, 어떤 것이 더 중요한 인덱스일까? 정답은 b다. b가 드리븐 테이블이기 때문이다.

위 사진은 같은 책 내용이다. 사원_X1, 고객_X2는 각각 인덱스를 의미한다. 사원이 드라이빙 테이블이고 고객이 드리븐 테이블이다.

 

사진에서 1 -> 2 -> 3 -> ... -> 11 -> 12의 순서를 자세히 잘 지켜보자. 잘 보면 사원에 대해서 모든 레코드를 읽고 고객에서 취합하는 것이 아니라, 사원에서 한 레코드 읽을 때마다 이에 매칭되는 고객의 레코드를 찾아 잇는 꼴이다.

 

따라서 사원은 인덱스를 1번만 타지만, 고객은 인덱스를 여러 번 타는 모습을 볼 수 있다. 그래서 고객_X1은 수직 탐색도 여러 번 한다. (5, 11, 15)

 

수직 탐색을 한다는 건 곧 B-Tree에서 depth만큼 block을 읽는다는 것을 의미한다. 즉, IN절에 대한 인덱스 처럼 동작하게 된다.

 

만약 드리븐 테이블의 인덱스 설계가 제대로 되어있지 않다면, 불필요한 scan이 많아지면서 효율이 나빠지게 될 것이다.

 

그렇다면 a 테이블의 인덱스 칼럼에서 STRD_GRP_ID, STRD_ID에 대한 칼럼을 제외한 이유는 무엇일까? 위 사진 구조를 보면 이해할 수 있다.

 

위 사진에서 c.관리사원번호 = e.사원번호에 해당하는 조건은 사원_X1이 아닌, 고객_X1에서 필터링해서 찾고 있음을 볼 수 있다.

 

즉, 저 조건들은 드라이빙테이블에서 사용하는 조건이 아니다.

 

위 JOIN sql을 쪼개면 다음과 같이 만들 수 있다.

select *
from PRA_HST_STC a
WHERE a.SALE_ORG_ID = :sale_org_id
order by a.STC_DT desc

select *
from OOM_TRMS b
where b.STRD_GRP_ID = :strd_grp_id
and b.strd_id = :strd_id
order by a.STC_DT desc

sql 1에서 받은 레코드가 변수가 되어 sql 2를 여러 번 호출하는 구조인 것이다. 따라서 a에서는 관계 없는 인덱스 칼럼이 된다.

여기서 한 발자국 더 나가서 nl join 튜닝에서 중요한 포인트가 몇개 있다.

NL Join 튜닝 중요 포인트

1. 사원_x1 인덱스를 읽고 나서 사원 테이블을 액세스하는 부분

사원_x1에서 필터링한 블록 3, 13, 21, 23, 25을 사원에서 읽지만, 21, 23은 부서코드 = 'Z123'에 해당하지 않아 버려진다. 만약 버려지는 칼럼이 많다면 ('입사일자', '부서코드')로 인덱스를 다시 설계해야 할 것이다.

2. 고객_x1 인덱스를 탐색하는 부분

조인 액세스 횟수는 드라이빙 테이블인 사원 테이블에서 필터링한 결과 건수에 의해 결정된다. 위 그림에서는 입사일자 >= '19960101' & 부서코드=Z123(이하 조건 a)에 해당하는 레코드에 해당하는 4, 14, 26에 해당한다.

 

만약 드라이빙 테이블에서 얻은 레코드 수가 많으면 그만큼 드리븐 테이블에 더 많이 액세스하게 될 것이고, 그만큼 수직탐색을 반복하게 될 것이다.

 

만약 조건 a에 해당하는 레코드가 10만 건이고 인덱스 depth가 3이라면 인덱스 수직 탐색 과정에서만 30만 block을 읽게될 것이다.

3. 고객_x1 인덱스를 읽고 나서 고객 테이블을 액세스하는 부분

2에서의 가정에 수평탐색은 더 많이 이루어질 것이다. 수평탐색은 곧 single block i/o와 바로 연결되기 때문에, 만약 필터링되는 게 많아지면 드리븐 테이블의 인덱스 설계를 다시 할 필요가 있다. 매우 중요한 포인트다.

 

single block i/o 관련해서는 다음 글에서 한 번 다루었는데, 지금 보니 자세히 다루진 않은 것 같다. 요것 관련해서도 한 번 정리해 보면 좋을 것 같다.

 

추가로 기억하면 좋을 점은 맨 처음 액세스하는 사원_X1 인덱스에서 얻은 결과 건수에 의해 전체 일량이 좌우된다. 사원_X1 인덱스를 스캔하면서 추출한 레코드가 많으면 사원 테이블로 랜덤 액세스하는 수, 고객_X1 인덱스를 탐색하는 수, 고객 테이블로 랜덤 액세스하는 횟수가 전반적으로 많아진다.

 

마지막으로 nl 조인은 랜덤 액세스 위주의 조인이기 때문에, 레코드를 순차적으로 하나씩 읽는다. 따라서 인덱스가 매우 중요하다.

 

그렇지만 인덱스 설계가 완벽해서 버퍼풀에서 데이터를 읽는다 하더라도, 버퍼풀을 읽는 과정에서 래치 및 버퍼 헤더 락에 걸리기 때문에 대량 데이터 조인시에는 성능이 떨어질 수밖에 없다.

 

래치 관련된 내용은 다음에 한 번 다뤄볼 예정이다.

 

같이 읽으면 좋은 글

- https://jerryk026.tistory.com/239