JOIN 수행 원리 ⭐️⭐️
- NL(Nested Loop) JOIN
- 랜덤 액세스
- Driving Table의 조인 데이터 양이 큰 영향을 줌
- 조인 컬럼에 적당한 인덱스가 있어 Natural Join이 효율적일 때 유용
- OLTP의 목록 처리 업무에 많이 사용
- 유니크 인덱스를 활용해 수행시간이 적은 소량 테이블을 온라인 조회하는 경우 유용
- HASH JOIN
- 동등 조건에서만 사용 가능. 동등 조인(Equal JOIN)에서만 가능
- 조인 컬럼의 인덱스 사용 X
- Hash 처리로 인해 별도 저장공간 필요
- 선행 테이블이 주 메모리의 가용 메모리에 담길 정도로 충분히 작고 해시 키 속성에 중복 값이 적을 때 효과적
- 해시 함수가 적용될 때 동일한 값은 항상 같은 값으로 해싱됨이 보장
- 대용량 처리에 유리
- 일반적으로 SMJ보다 우수한 성능을 보이지만, 대상 테이블이 Join Key 컬럼으로 정렬되어있으면 SMJ가 성능이 더 좋을 수 있음
- Sort Merge JOIN (SMJ)
- JOIN 키를 기준으로 정렬. 등가 조인, 비등가 조인 가능
- 주로 스캔 방식으로 데이터를 읽음
- Driving Table의 개념이 중요하지 않으며 조인 조건의 인덱스 유무에 영향을 받지 않음
- 데이터가 많아 메모리에서 모든 정렬 작업이 어려운 경우엔 임시영역(디스크)를 사용하므로 성능이 저하될 수 있음
- SEMI JOIN / ANTI JOIN
- EXISTS절과 IN은 실행 계획상에 주로 SEMI JOIN으로 나타남
- NOT EXISTS절과 NOT IN은 주로 ANTI JOIN으로 나타남
Optimizer
- RBO(Rule Based Optimizer)
- 가장 높은 우선순위: ROWID(행에 대한 고유주소)를 활용한 테이블 인덱스
- 가장 낮은 우선순위: 전체 테이블 스캔
- 규칙에 따라 적절한 인덱스가 존재하면 항상 인덱스를 사용하려 함
- CBO(Cost Based Optimizer)
Index
- B-Tree(Balance Tree) 인덱스
- 브랜치 블록(분기 목적)과 리프 블록(인덱스 구성 컬럼 값으로 정렬됨)으로 구성
- RDB에서 가장 많이 사용됨. OLTP(온라인 트랜잭션 처리) 시스템 환경에서 가장 많이 사용됨
- 일치 및 범위 검색에 적절
- 테이블 내 데이터 중 10% 이하의 데이터 검색 시 유리
- CLUSTERED 인덱스
- 리프 페이지가 곧 데이터 페이지
- 리프 페이지의 모든 데이터는 인덱스 키 컬럼 순으로 물리적으로 정렬되어 저장
- SQL Server의 클러스터형 인덱스는 Oracle의 IOT(Index Organized Table)와 유사
- IOT: Table Random Access가 발생하지 않도록 처음부터 인덱스 구조로 생성됨. Index leaf block = data block. 정렬 순서를 유지하며 데이터를 생성(PK 컬럼 순)
- BITMAP 인덱스
- 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하고 있는 구조
- 시스템에서 사용될 질의를 시스템 구현 시에 모두 알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위함
- 기본 인덱스(PK) 제약 조건: UNIQUE, NOT NULL
- 보조 인덱스: 꼭 UNIQUE가 아니어도 됨
- 부정형, LIKE, 묵시적 형변환에는 인덱스가 쓰이지 않음
- 인덱스 사용 시 insert, update, delete의 성능 저하
- 인덱스 구성 컬럼 이외의 데이터의 update에는 부하 발생 X
- 테이블 전체를 읽는 경우 FTS(Full Text Search. 전문검색) 사용
- 인덱스 생성 시 정렬 순서 명시 가능 (오름차순/내림차순)
- 다량 데이터 탐색의 경우 인덱스가 아닌 파티션 및 클러스터링 등 물리 저장 기법 활용으로 성능 개선 가능
실행 계획
- 실행 계획이 달라져도 결과는 변하지 않음
- 실행 순서: 위에서 아래로, 안에서 바깥으로
1
2
3
4
5
→ 3-2-5-4-1
- SQL 처리 흐름도: 실행 계획을 시각화하여 표현한 것
