[대용량데이터베이스] 조인의 최적화

http://blog.naver.com/st95041?Redirect=Log&logNo=40019616034

2. 조인의 최적화
   조인 : 관계형 데이터베이스에서 가장 기본적이고 가장 중요한 기능
         물리적으로는 전혀 연결관계가 없을 지라도 필요시 논리적인 관계만으로도
         조인을 이용하여 원하는 정보를 서로 연결하여 참조
  → 옵티마이져가 최적의 처리경로를 찾을 수 있도록 인덱스, 클러스터 등의
     옵티마이징 펙터를 적절히 지장하고 경우에 따라서는 힌트나 사용제한 기능을
     사용하여 최적의 액세스 경로로 처리될 수 있도록 조치

2.1 조인효율 향상의 원리
   가장 효율적인 조인 경로는논리적으로 이미 존재하는 것이며
   옵티마이저는 단지 찾아줄 뿐이지 새롭게 없는 경로를 생성해 주는 것이 아니다.
   그러므로 우리는 최적의 경로를 알고 있어야 하며 과연 옵티마이저가 정확하게
   그 경로를 찾아 주는 지를 판단할 수 있어야 한다.
   → 가장 최소의 일량으로 원하는 결과를 얻을 수 있도록 하는 원리와
      그러한 조치 최적의 액세스 경로가 실제적으로 생성되도록 하기 위한 조치사항

    가. 조인 순서
    조인의 효율은 조인되어야 할 각 집합의 처리범위와 순서에 따라 영향
    즉, 처리범위가 가장 좁은 범위를 먼저 처리할수록 조인의 효율은 증가한다.
    → 대부분의 조인은 Nested Loop 조인으로 수행되므로 조인의 순서가
    Nested Loop 조인의 수행속도에 미치는 영향은 크다.
  
    처리범위 : 주어진 조건을 만족하는 로우수만을 의미하는 것이 아니라 그 조건의
    범위를 찾는데 사용되는 주처리(Main Driving)영역의 액세스 범위를 의미
    → Nested Loop 조인에서는 가장 먼저 수행되는 집합의 처리범위가 전체 일량을
    좌우하게 된다. 즉, 앞서 수행한 결과가 많으면 많이 수행되어야 하고
    적으면 적게 수행된다.

    나. 조인 성공률
    세개 이상의 집합에 대한 조인 효율은 조인 성공률이 낮은 집합의 조인이 먼저
    일어나는 것이 유리

    조인 성공률 : 조인의 작업이 수행된 후 조인 조건을 만족하는 총 로우수를 의미
    → 먼저 수행된 수인작업에서 성공된 로우들만 다음 조인작업을 수행하므로
    앞단계에서 하나라도 처리해야 할 로우수를 줄여 주면 그만큼 다음에 처리해야 할
    일량은 줄어들 수 밖에 없다.

    다. 연결고리 상태
    연결작업을 위한 보조장치(인덱스, 클러스터 등)에 따라 실제 처리할 일량은
    많은 차이가 난다.
    → Sort Merge 조인은 연결고리의 인덱스를 사용하지 않으므로 연결고리의 상태에
    전혀 영향을 받지 않는다.
 
    ① 연결고리 정상 : 조인의 연결컬럼에 인덱스가 모두 존재하고 있는 상태
    연결고기 양쪽 모두에 인덱스가 존재하는 경우는 어느 방향으로 연결작업이
    수행되든지 인덱스를 통해 정상적인 연결작업을 수행할 수가 있다. 어느 방향으로
    연결작업이 수행되든 간에 그것이 비록 M:1의 관계에 있다 하더라도 발생되는
    연결작업은 동일
    → 연결고리가 정상인 상태에서는 먼저 처리범위를 줄여 주는 테이블이 먼저 처리되면
    수행속도가 향상

    ② 한쪽 연결고리 이상 : 어느 한쪽의 연결고리에 인덱스가 없는 상태
    KEY1의 값이 사수가 되었으나 연결해야 할 TAB2의 KEY2에는 인덱스가 없으므로
    어쩔 수없이 TAB2를 전부 스캔하며서 'KEY2=KEY1'을 만족하는 로우를 찾을 수 밖에 없다.
    그러나 테이블을 스캔하다가 대응되는 값을 찾았더라도 멈출 수가 없다.
    조인이란 대응되는 하나의 값만 요구하는 것이 아니라 대응되는 모든 로우들을 추출해야
    하므로 테이블을 끝까지 스캔하여야 한다.
    → 연결고리의 어느 한쪽에 이상이 있는 경우는 이상이 발생한 테이블을 반드시 먼저
    처리하지 않으면 안된다.    
  
    조인되는 컬럼이 1:1로 대응되지 않는 경우
    데이터 타입의 차이에 의해 발생되는 경우

    ③ 양쪽 연결고리 이상
    양쪽 연결고리 모두에 인덱스가 없다면 어느 방향으로 조인을 하더라도 문제가 될 것이다.
    이러한 경우 옵티마이저는 연결고리에 영향을 받지 않는 방식인 Sort Merge 방식으로
    실행계획을 수립하게 된다.

2.2 조인의 튜닝(Tuning)
    ① Nested Loop 조인이 유리한 지 Sort Merge 조인이 유리할 것인지를 판단
    ② 연결고리 상태를 확인. 연결고리 이상시 액세스 방향이 정해져 있으므로 현 상태의
       문제점을 확인하여 인덱스의 추가 여부를 결정
    ③ 연결고리를 제외한 컬럼들의 조건에 사용된 연산자와 인덱스의 상태를 비교하여
       처리범위를 가장 많이 줄여 주는 조건을 찾는다.
    ④ 현재의 인덱스가 우리가 찾은 최소의 범위를 액세스할 수 있도록 구성되어 있는지 확인
       부분범위처리가 가능한 경우라면 처리범위가 넓더라도 수행속도가 충분히 보장되는
       경우가 있으므로 이 단계에서는 부분범위처리로 유도할 수 있는지를 잘 살펴보아야 한다.

2.3 조인과 반복연결(Loop Query)
    반복연결 방식(Loop Query) : 기준 테이블의 처리 범위에 있는 로우들을 차례로 읽어
       그 상수값으로 연결해야 할 다른 테이블을 찾는 방법

    가. 전체범위처리방식의 조인
    반복연결방식이 유리해지려면 연결되는 작업의 수행 횟수는
    반드시 처리되어야 할 일량의 일부분이어야 한다.
    나. 부분범위처리방식의 조인
    부분범위처리가 가능하다면 비록 조인을 했을 지라도 운반단위가 채워질 때까지만
    연결작업이 일어난다. 부분범위처리는 많은 양의 처리가 발생하지 않기 때문에
    큰 차이가 나지는 않겠지만 어쨌던 조인이 더 유리해진다.

    ① 전체범위처리가 된 이유가 연결되는 모든 테이블에 원인이 있다면 조인이 빠르다.
    ② 연결되는 테이블 중 하나를 부분범위처리로 바꿀 수 있다면 반복연결방식이 빠르다.
    ③ SQL이 부분범위처리가 된다면 조인이 빠르다.      

2.4 Nested Loop 조인과 Sort Merge 조인

    First_Rows : 같은 내용이 추출된다고 하더라도 먼저 일부를 추출하는 것에 목적
    All_Rows : 전체를 가장 빠르게 처리하는 데 목적을 둘 것인가

    가. Nested Loop 조인
    먼저 어떤 테이블의 처리범위를 하나씩 액세스하면서 그 추출된 값으로 연결된 테이블을 조인

    특징
    ① 순차적으로 처리된다 (순차적)
    ② 먼저 액세스되는 테이블의 처리범위에 의해 처리량이 결정 (선행적)
    ③ 나중에 처리되는 테이블은 앞서 처리된 값을 받아 액세스 (종속적)
    ④ 주로 랜덤 액세스 방식으로 처리
    ⑤ 모든 컬럼들이 인덱스를 가지고 있더라도 연결되는 방향에 따라 사용되는 인덱스들이
       전혀 달라질 수 있다 (선택적)
    ⑥ 연결고리가 되는 인덱스에 의해 연결작업이 수행되므로 연결고리 상태가 매우 중요
    ⑦ 연결작업 수행 후 CHECK 되는 조건은 부분범위처리를 하는 경우에는 범위가 넓을수록 유리

    사용기준
    ① 부분범위처리를 하는 경우에 주로 유리
    ② 상대방 테이블에서 추출된 결과를 받아야 처리 범위를 줄일수 있는 상태라면 항상 유리
    ③ 처리량이 적은 경우
    ④ 연결고리 이상 상태를 만들지 않도록 주의
    ⑤ 최적의 액세스 순서가 되도록 적절한 조치가 요구
    ⑥ 운반단위의 크기가 수행속도에 많은 영향
    ⑦ 선행 테이블의 처리 범위가 많거나 연결테이블의 랜덤 액세스의 양이 많다면
       Sort Merge 조인보다 불리해지는 경우가 많다.

    나. Sort Merge 조인
    양쪽 테이블의 처리범위를 각자 액세스하여 정렬한 결과를 차례로 스캔하면서
    연결고리의 조건으로 머지해 가는 방식
    → 랜덤 액세스가 줄어들어 시스템의 부하를 감소시키자만 일반적으로
    Nested Loop 조인 보다는 사용되는 빈도가 적은 편
  
    특징
    ① 동시적으로 처리된다.
    ② 자신에게 주어진 상수값에 의해서만 범위를 줄인다
    ③ 항상 전체범위처리를 한다
    ④ 자신의 범위처리를 줄일 경우만 인덱스를 이용한 랜덤 액세스이고 머지작업은 스캔방식
    ⑤ 연결고리가 되는 컬럼은 인덱스를 전혀 사용하지 않는다.
    ⑥ 조인의 방향과는 전혀 무관
    ⑦ 인덱스는 대개 가장 유리한 한가지만 사용

    → 데이터의 처리량이 매우 많은 경우라면 어떤 방식의 조인으로 실행되었느냐에 따라
    수행속도에 미치는 영향은 아주 크다

    다. 조인방식의 비교
    한쪽에 조건이 없는 경우 Nested Loop 조인은 영향을 받지 않았으나
    Sort Merge 조인은 엄청난 일의 증가
    정렬 작업이 수행속도에 가장 많은 영향을 미치는 것은 역시 메모리 내에서 얼마나 많은
    정렬 작업을 수행하는가에 관련이 있다. 메모리내 정렬영역의 크기가 적으면 정렬작업에
    대한 부하로 인해 오히려 Nesed Loop 조인보다 늦어질 수 있다.