'조인 튜닝'에 해당되는 글 2건

  1. 2013.11.29 [SQL Server 운영과 튜닝] - Merge Join
  2. 2013.11.29 [SQL Server 운영과 튜닝] - Nested Loop Join
IT-Study2013.11.29 09:46

 

 

3.2 머지 조인

  • 머지 조인은 양쪽 입력을 조인조건을 기준으로 정렬하고 순차적으로 읽으며 조인을 한다.
  • 정렬하기 위한 메모리가 필요하기 때문에 메모리가 부족하면 디스스 I/O 발생해서 성능 저하 발생
  • 슈도 코드

 

 

 

번째 입력이 유니크를 보장할 경우

T1 테이블을 번째 테이블, T2 테이블을 번째 테이블로 지정

각각 조인 조건 기준으로 정렬함

T1에서 입력 행을 가져와서 T2 입력을 처음부터 순차비교하고 일치하는 행을 찾으면 결과로 선택함

T2 입력의 행이 조인 조건과 일치하지 않으면 작은 쪽을 버린다.

마지막 비했던 위치 부터 이어서 다시 비교한다.

~⑤를 모든 입력에 대해서 수행함

 

번째 입력이 유니크를 보장하지 못할 경우(중복 관리 작업 필요)

T1 테이블을 번째 테이블, T2 테이블을 번째 테이블로 지정

각각 조인 조건 기준으로 정렬함

T1에서 입력 행을 가져와서 T2 입력을 처음부터 순차비교하고 일치하는 행을 찾으면 결과로 선택함

T2 입력에서 조인 조건이 같은 중복 행을 찾으면, 중복된 행들을 임시 버퍼에 저장을 한다. 조인이 끝나면 T1 입력에서 다음 행을 가져와서 임시 버퍼에 저장된 행들의 조인 조건과 같은지 비교해서 같다면 임시 버퍼와 조인을 한다. 임시 버퍼와 조인이 끝나면 T1입력에서 다음 행을 가져온다.

T1 입력에서 가져온 행이 임시 버퍼에 저장된 행의 조인 조건보다 크다면 임시버퍼로 해제 T2 마지막 부터 순차 검색함

~⑤를 모든 입력에 대해서 수행함

 

일대다(1:M) 조인과 다대다(M:M) 조인

- 머지 조인은 번째 입력이 유니크를 보장할 경우 번째 입력의 상태에 상관없이 임시 버퍼를 생성하지 않는다.

- 번째 입력이 유니크한지 없으면 번째 입력의 상태에 상관없이 임시 버퍼를 생성한다.

- 만약 번째 입력에 같은 조인 조건을 가지는 중복된 행이 없다면 임시 버퍼를 사용하지 않는다.

 

1:M

- 유니크를 보장할 있는 방법(인덱스, 제약 조건 ) 없기 때문에 SQL Server M:M으로 인식함

- 임시 버퍼 생성 사용

 

M:1

- 임시 버퍼를 생성했지만 사용은 하지 않았다. 임시 버퍼를 사용하지 않은 것은 번째 입력이 논리적으로 유니크하기 때문에 중복이 발생하지 않아서 임시 버퍼에 저장할 필요가 없기 때문이다.

 

유니크 인덱스를 생성한 1:M

- 임시 버퍼를 생성하지 않음

 

정리

- 1:M 경우 1인지 있는 인덱스나 제약 조건이 없다면 1 두번째 입력으로 하여 임시 버퍼가 사용되는 것을 막을 있다.

 

번째 테이블 입력

번째 테이블 입력

임시 테이블 생성

임시 테이블 사용

M

1

O

X

M

1(유니크)

O

X

1

M

O

O

1(유니크)

M

X

X

M

M

O

O

 

Equal Join

- 머지 조인은 최소한 개의 '=' 있어야 한다. 외의 조인 들은 필터 조건으로만 사용된다.

 

정렬을 위한 추가 메모리

- 머지 조인 전에 정렬을 해야 하기 때문에 추가 메모리가 필요

- 조인 조건과 SELECT 절에 기술된 컬럼 들이 함께 저장된다. => 불필요한 SELECT 열은 제외시켜라

 

인덱스를 사용한 정렬(STOP&GO) 작업 제거하기

- 머지 조인의 정렬 기준인 조인 컬럼에 인덱스를 만들어 놓으면 항상 정렬이 되서 인덱스에 저장되어 있기 때문에 다시 정렬할 필요가 없다.

- 효과: 1. STOP&GO 사라짐 2. 메모리 확보를 위한 부하가 사라짐

 

정렬 제거로 인한 반응성 향상

- 인덱스를 사용해서 정렬을 제거할 있다면 정렬을 기다리는 시간이 없기 때문에 바로 조인을 해서 원하는 결과를 빠르게 찾을 있어서 높은 반응성을 기대할 있게 된다.

 

정렬을 위한 메모리 할당 제거

- 인덱스로 정렬을 제거하면 정렬을 위한 메모리를 할당하던 작업이 필요 없게 된다.

 

조인 조건의 인덱스는 사용되지 않을 있다.

- Look up부하가 너무 크게 발생한다고 판단 되면, Table Scan 수도 있다.

 

커버드 인덱스를 사용한 머지 조인 최적화

- 조회에 추가 되는 컬럼을 INCLUDE 통하여 포함 시킨다.

- INCLUDE 장점

 1. 인덱스 크기가 커지는 것을 최소화 한다.(Leaf 절에만 저장됨)

 2. 유티크와 같은 속성을 그대로 유지

 

메모리가 부족할 경우의 성능 저하와 최적화

- 메모리가 충분하지 않다면, 부족한 부분을 디스크로 대체 하기 때문에 성능상 이슈 발생

 

디스크 사용 모니터링

- SQL Server 프로파일러 (Sort Warning)

 

정렬 조건과 데이터 분리를 통해서 메모리 사용 최적화

- 가장 쉬운 것은 메모리를 할당하는 것이나 근본적 해결은 아님

- 정렬에 필요하지 않은 컬럼들을 메모리에 복사하지 않도록 한다.

 

인덱스 선정 기준

조인 조건을 기준으로 인덱스 생성

- 정렬 기준으로 인덱스를 만들기 때문에 추가 정렬 작업이 필요 없다.

- JOIN 실패, WHERE 절에 의해 많은 필터가 되면 불필요한 많은 데이터를 읽음

액세스 컬럼에 인덱스 생성

- 정렬하는 부하가 작을 경우 적합

 

그룹핑을 통해 번째 테이블 유니크 보장을 통한 임시 테이블 최적화

- SQL Server 그룹핑을 통하여 유니크 여부를 있고, 임시 테이블을 생성하지 않게 된다.

- 첫번째 테이블 입력의 유니크 제약만 해결할 있다면 OLTP OLAP 모두를 커버할 있는 조인이 된다.

 

 

 

신고
Posted by TM ~ing
IT-Study2013.11.29 09:39

 

3.1 중첩 루프 조인

  • 작은 입력을 인덱스를 허용해 조인할 가장 효과적이다.
  • 슈도 코드

 

 

T1 T2 입력이 작은 테이블을 외측 참조 테이블, 쪽을 내측 테이블로 선택한다.

T1 입력에서 하나의 행을 가져와서 T2 입력을 검색하면서 조인 조건에 맞는 행을 찾는다.

T2 입력에서 일치하는 행을 찾았다면 결과 집합으로 선택되고, 추가적으로 일치하는 행이 있는지 나머지 행들과 비교를 계속한다. T2 입력에서 더 이상 비교할 행이 없으면, T1 입력에서 다음 행을 가져와 , ③을 계속한다.

T2 입력에서 조인 조건과 일치하는 행을 찾으면 T1 입력에서 가져온 행은 버려지고 다음 행을 가져온다.

T1 입력에 이상 조인할 행이 없을 때까지 반복한다.

 

중첩 루프 조인의 특징

  조인 횟수는 외측 참조 테이블 입력에 의해 결정된다.

  • 중첩 루프 조인에선 외측 참조 테이블의 크기 만큼 내측 테이블과 조인하기 때문에 외측 참조 테이블의 입력이 조인 횟수를 결정하게 된다.
  • WHERE 조건에서 T_OUTER 크기를 얼마나 줄일 있느냐가 조인 횟수를 결정하고, 결론은 중첩 루프 조인의 성능에 영향을 주게 된다.

  중첩 루프 조인은 메모리를 사용하지 않는다.

  • 외부 참조 테이블은 정렬이나 해시 테이블과 같은 추가작업 없이 바로 내측 테이블과 조인이 가능하다.

 

인덱스와 테이블 선택과의 관계

  • 내측 테이블에 적절한 인덱스가 생성되어 있으면, 효율적으로 조인이 가능하다.
  • 중첩 루프 조인은 내측 테이블을 선택할 테이블 전체를 읽는 작업을 피하기 위해서, 조인 조건으로 사용할 인덱스가 있는 테이블을 선택하려고 한다.
  • 중첩 루프 조인을 사용한다면 내측 테이블로 사용할 테이블에는 적당한 인덱스를 만들어야 원하는 실행계획을 만들 있다.

 

테이블 순서에 따른 중첩 루프 조인의 성능차이

  • 외측 테이블에서 내측 테이블로 조인을 시도하는 횟수도 비용이 발생한다. 그러므로 크기가 작은 외측 테이블 사용하면서 비용을 줄인다.

 

중첩 루프 조인에서 인덱스 선정 기준

  • 외측 테이블: WHERE 절에 기술되는 검색 조건에 맞춰서 인덱스를 생성해야 한다.
  • 내측 테이블: 조인 조건을 기준으로 인덱스를 생성해야 한다.

 

STOP&GO

  • 머지 조인(시작전 정렬 작업), 해시조인(해시 테이블 생성)처럼 조인을 시작하기 전에 나오는 추가 작업
  • 중첩 루프 조인: 조인에 성공한 행은 버퍼에 저장되고, 버퍼가 채워지면 조인이 끝나지 않아도 클라이언트에 전송함( 번째 응답 속도가 빠르다.)
  • 머지 조인은 적당한 인덱스가 없으면 무조건 우선 정렬을 해야만 조인할 있기 때문에 조인하려는 수와 상관없이 항상 T_OUTER 입력을 모두 읽어서 정렬하는 작업이 필요하다.

  

[참고]

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

 

 

 

 

 

신고
Posted by TM ~ing