Join의 종류

1)     조인의 종류 : 물리적인 방법의 조인을 말한다.

A.     중첩 루프 (Nested Lopp) 조인

B.      병합 (Merge) 조인

C.      해시 (Hash) 조인

D.     Remote 조인

 

-       Row 많아질수록 쿼리의 Cost가 높아진다, Loop가 가장 기본 적인 방법이며, 양이 적을때에는 성능이 좋지만 데이터가 많아질수록 비용도 급격히 증가한다.

-       Merge 방식은 데이터가 적을 경우에는 Loop 보다는 못하지만, 양이 많아 질수록 더 뛰어난 성능을 보인다.

-       Hash 방식은 데이터가 얼마 없을 경우에는 Overhead로 인하여 성능이 좋지 않지만 데이터가 많을수록 Loop 보다는 낮고 Merge 보다는 못하게 비용이 증가한다.

1)     Nested Loop (중첩 루프 조인)

-       한집합의 원소값을 다른 집합의 원소 값과 매칭해 나가는 작업, 가능한 모든 경우를 조회하여 결과 집합을 찾는 방법

-       선행테이블 (Drving Table ) = 바깥 테이블 = 찾는 주체가 되는 테이블

-       후행 테이블 (Driven Table)  = 안쪽 테이블 = 비교 대상이 되는 테이블

-       여기서 바깥/안쪽 의 의미는 조인에서의 위치가 아닌 먼저 조회 되느냐 마느냐의 의미임.

-       힌트가 없을시에는 선행테이블은 옵티마이저가 알아서 배치하나 힌트가 있을 시에는 테이블 배치 순서에 따른다.

-       성능을 높이기 위한 방법

n  후행 테이블의 크기가 작을수록, 후해 테이블에 인덱스가 미리 설정되어야 한다.

n  선행 테이블이 이미 sort되어 있으면 좀 더 빠르다.

-       set statistics io on

-        

-       select e.emp_id, e.fname + e.lname as name, j.job_id, j.job_desc

-       from  dbo.employee as e with(nolock)

-       join jobs as j with(nolock)  on e.job_id = j.job_id

-       테이블 'jobs'. 검색 수 43, 논리적 읽기 수 86,

-       테이블 'employee'. 검색 수 1, 논리적 읽기 수 2,

 

 

 

 

 

 

 

 

 

 

 

 

-       실행계획에 employee 부터 읽혀지며, job_id에 인덱스가 없기 때문에 full scan 하는것과 동일하다. 아래 그림처럼 emplolyee의 job_id를 읽어서 순서대로 jobs테이블에 찾는 작업을 한다.

-       Employee 테이블의 row수가 43개 행이기 때문에 jobs을 43번 scan 한다.

그럼 강제로 jobs를 선행 테이블로 읽게 변경해 보자.

-       select e.emp_id, e.fname + e.lname as name, j.job_id, j.job_desc

-       from  jobs as j with(nolock)

-       join employee as e with(nolock)  on e.job_id = j.job_id

-       option (force order, loop join)

-        

-       --테이블'employee'. 검색수14, 논리적읽기수29

-       --테이블'jobs'. 검색수1, 논리적읽기수2

 

 

 

 

 

 

 

 

 

 

 

-       Cost 비용을 봐서는 jobs늘 선행 테이블로 해서 강제로 loop 하는것이 더 좋아보인다.

-       Jobs 테이블의 row 수만큼 employee 를 풀 스캔 한다. (14개)

-       아래 쿼리는 두 테이블의 데이터 길이이다.

-       select  CAST( sum( datalength(emp_id) + datalength(fname) + datalength(minit)

-                     + datalength(lname) + datalength(job_id) + datalength (job_lvl) +  DATALENGTH(pub_id)

-                     + DATALENGTH(hire_date)) as varchar) + ' byte'

-       from employee with(nolock)

-       --1607 byte

-        

-       select  CAST( sum( datalength(job_id) + datalength(job_desc) + datalength(min_lvl)

-                     + datalength(max_lvl) ) as varchar) + ' byte'

-       from JOBS with(nolock)

-       --314 byte

 

-       첫번째 쿼리는 employee 테이블 만큼 스캔 하였으니 1067 바이트 만틈 읽었고 jobs는 clustred index seek 하였으니 314 바이트 만큼 읽었다.  총  1067+ 314 = 1921 byte

-       두번째 쿼리는 jobs를 먼저 full scan 하였으므로  (1067 * 14) + 314 = 22812 byte

-       첫번째 쿼리가 11배 정도 입/출력이 작다. 쿼리의 성능을 입/출력 양으로 측정은 쿼리 수행 속도를 결정한다., 위 두 쿼리는 어떤 테이블을 사용하던지 한쪽은 full scan 해야 한다.

create index IDX_EMPLOYEE_JOB_ID on employee ( job_id)

-       인덱스를 생성하고 두번째 쿼리를 다시 실행하면 employee 를 index seek 하기 때문에 좀 더 성능이 좋아진다.

 

 

 

 

 

 

 

-       일반적으로 테이블의 10% 정도까지의 데이터를 접근할 때 인덱스를 사용하는 것이 낮다고 한다 실제적으로 쿼리 수행해보면 (8% 정도 였던것 같기도 함) 그러나 이것도 상대적이다 1억건의 테이블에서 1천만건을 읽는데 인덱스 이용하면 오래 걸린다. 인덱스로 한건 한건 접근하기 때문이다.

-       인덱스를 사용하지 않고 조인 한다면

-       select e.emp_id, e.fname + e.lname as name, j.job_id, j.job_desc

-       from  jobs as j with(index(0))

-       join employee  as e with(index(0))  on e.job_id = j.job_id

-       테이블 'employee'. 검색 수 1, 논리적 읽기 수 2

-       테이블 'jobs'. 검색 수 1, 논리적 읽기 수 2,

 

 

-       Hash 조인을 사용한다. Merge join의 경우 sort를 하기 때문에 비용이 더 많이 든다. 이 두 테이블은 row수가 많이 않기 때문에 hash join이 가장 적합하다.

2)     Mearge Join (병합조인)

-       전제 조건은 양 테이블이 모두 조인 키에 의해 정렬되어 있어야 함.

-       선행,후행 테이블의 크기는 성능과 관련이 없다, 그러나 선행 테이블이 중복 행이 존재하지 않을 때, 즉 고유할 때, 메모리 사용량이 적으며, 이것이 권장된다.

-       병합조인은 중복 행이 존재한다면 어지간히 해서 발생하지 않는다, 그래서 다:1 관계에서 발생하지 다:다 에서는 발생하지 않는다. 이럴 경우는 hash join을 한다.

-       큰 장점은 양쪽 테이블에서 스캔 수가 한번 일어난다.

-       데이터가 많을 때 이미 정렬되어 있기 때문에 전부 찾지 않는다.

-       정렬되어 있지 않다면 정렬 하는 작업을 하기 때문에 메모리를 더 사용할 수 있다. 그래서 유니클 할때만 힌트를 사용해서 고정하는 것이 좋다. 그렇지 않으면 정렬 비용이 발생하고 tempdb의 페이지 할당이 증가된다.

 

-       select *

-       from employee as e

-        join jobs as j on e.job_id = j.job_id

-       option(merge join)

테이블'jobs'. 검색수1, 논리적읽기수2

테이블'employee'. 검색수1, 논리적읽기수88

3)     Hash Join

-       선행 테이블을 해시 버킷을 만드는데 사용되며, 이것을 빌드 입력이라고 부른다. 후행 테이블은 검색입력 이라고 부른다.

-       선행 테이블은 후행 테이블이 처리되기 전에 완전히 읽혀져야 함으로 응답 시간이 조인들 중에서 가장 늦다.

-       작은 테이블이 선행 테이블로 사용되어져야 한다. 큰 테이블이 선행 테이블이 되면 해시 버킷을 만들어야 하는데 메모리 사용량이 늘어 난다.

-       해시조인의 결과의 순서가 없다 그래서 순서가 필요하다면, ORDER BY를 꼭 명시해야 한다.

-       인덱스가 없을 때 주로 해시 조인이 사용된다.

 

-       select *

-       from employee as e

-        join jobs as j on e.job_id = j.job_id

-       option(hash join)

-       테이블 'employee'. 검색 수 1, 논리적 읽기 수 2

-       테이블 'jobs'. 검색 수 1, 논리적 읽기 수 2,

 

정리

 

 

1) Input Order 필요한가?

요즘 옵티마이저는 똑똑해서 필요 없다.

-       Loop join : 아니다, 하지만 더 낳은 성능을 위해 선행 테이블이 정렬될 수도 있다.

-       Merge join : 양 테이블이 반드시 조인 키에 의해 정렬되어 있어야 한다.

-       Hash join : 필요 없다.

2)     Sort Order 필요한가?

-       Loop join : 선행 테이블

-       Merge join : 둘 다

-       Hash join : 필요 없다.

3)     선행/후행 테이블 선택은 어떻게 ?

-       Loop join : 후행 테이블은 인덱스가 있거나 작아야 한다.

-       Merge join : 별로 중요하지 않다.

-       Hash join : 작은 테이블이 선행 테이블이 되어야 한다.

4)     메모리 사용량

-       Loop join : 추가 메모리 필요 없다.

-       Merge join : back loop 위해 추가 메모리 필요

-       Hash join : 해시 버킷을 위해 추가 메모리 필요