2.2 장
논리적인 SQL 개념 용어
1) 서브쿼리 위치에 따른 SQL 용어
2) 메인쿼리와의 관계성에 따른 SQL 용어
3) 반환결과에 따른 SQL 용어
4) 조인 연산방식 용어
5) 조인 알고리즘 용어
논리적인 SQL 개념 용어
1) 서브쿼리 위치에 따른 SQL 용어
스칼라 서브쿼리
- 스칼라 서브쿼리 결과는 무조건 데이터 1건
인라인 뷰
- 인라인 뷰의 결과는 내부적으로 메모리 또는 디스크에 임시 테이블을 생성해 활용
중첩 서브쿼리
- 보통 비교 연산자를 비롯해 IN,EXISTS,NOT IN,NOT EXISTS 많이 사용
2) 메인쿼리와의 관계성에 따른 SQL 용어
비상관 서브쿼리
- 메인쿼리&서브쿼리 간에 관계성이 없음
- 서브쿼리가 실행된 뒤 메인쿼리에게 그 결과를 던져주는 형태
- 서브쿼리 실행 -> 메인쿼리 실행
select *
from 학생
where 학번 in (select 학번 from 학생 where 성별='남')상관 서브쿼리
- 메인쿼리&서브쿼리 간에 관계성이 있음
- 스칼라 서브쿼리/중첩 서브쿼리 일때 상관 서브쿼리 발생
select ... from 학생 where ... IN
(select ... from 지도교수 where 학생.학번=지도교수.학번)- 메인쿼리 실행(학생.학번 데이터 가져오기) -> 서브쿼리 실행(지도교수.학번=학생.학번) -> 메인쿼리 전달(select * from 학생~)
3) 반환결과에 따른 SQL 용어
단일행 서브쿼리
- 서브쿼리 결과가 1건의 행으로 반환되는 쿼리
- 스칼라 서브쿼리와 동일하다고 볼 수 있음
select ... from ...
where 학번 = (select MAX(학번) from 학생)다중행(다중로우) 서브쿼리
- 서브쿼리 결과가 여러 행의로 반환되는 쿼리
- 메인쿼리의 조건절에서는 IN 구문으로 서브쿼리에서 반환되는 값들을 받음
select ... from ...
where 학번 IN (select MAX(학번) from 학생 group by 전공코드)다중열(다중컬럼) 서브쿼리
- 서브쿼리 결과가 여러개의 행과 열로 반환
select ... from ...
where (이름,전공코드) IN (select 이름,전공코드 from 학생 where 이름 like '김%')4) 조인 연산방식 용어
inner join
select 학생.학번,학생.이름,지도교수.교수명
from 학생
join 지도교수
on 학생.학번=지도교수.학번left outer join
select 학생.학번,학생.이름,지도교수.교수명
from 학생
left outer join 지도교수
on 학생.학번=지도교수.학번right outer join
주로 left outer join 사용
cross join
발생할 수 있는 모든 조합 찾아내 반환
select 학생.학번,학생.이름,지도교수.학번,지도교수.교수명
from 학생
cross join 지도교수natural join
- 공통으로 존재하는 컬럼명으로 조인 조건절을 알아서 찾아줌=>직접적인 조인 조건문 작성X=>작성 시 오류발생
- 공통으로 존재하는 컬럼명 없을 시 cross join 수행
select 학생.학번,학생.이름,지도교수.학번,지도교수.교수명
from 학생
natural join 지도교수5) 조인 알고리즘 용어
1. 드라이빙 테이블 VS 드리븐 테이블
조인을 수행할때, 테이블에 동시 접근할 수 없기 때문에 테이블의 데이터에 접근하는 우선순위 존재
- 먼저 접근하는 테이블 = 드라이빙 테이블(outer table)
- 나중에 접근하는 테이블 = 드리븐 테이블(inner table)
가능하면 적은결과가 반환될것으로 예상되는 테이블을 드라이빙 테이블로 선정 + 조인조건절의 열이 인덱스로 설정되도록 구성
예제) 학생.학번 IN (1,100) 조건이 있는 학생 테이블의 데이터를 드라이빙
select 학생.학번, 학생.이름, 비상연락망.관계, 비상연락망.연락처 from 학생 join 비상연락방 on 학생.학번=비상연락망.학번 where 학생.학번 IN (1,100)
2. 중첩 루프 조인 (nested loop join)
- 드라이빙 테이블의 데이터 1건당 드리븐 테이블 반복해 검색
- 드라이빙 테이블 N건 X 드리븐 테이블 M건
예제 1) 기본키,인덱스 없는 테이블 중첩 루프 조인
select ... from 학생 join 비상연락망 on 학생.학번=비상연락망.학번 where 학생.학번 in (1,100)학생 100건, 비상연락망 1000건
그림은 데이터가 정렬되어 있지만, 대부분 데이터가 뒤엉켜 있을 가능성이 있음(학번 1을 찾기 위해 학생 테이블 데이터 100건 접근 + 학번 1을 찾기 위해 비상연락망 테이블 데이터 1000건 접근)+(학번 100을 찾기 위해 학생 테이블 데이터 100건 접근 + 학번 100을 찾기 위해 비상연락망 테이블 데이터 1000건 접근)=2200건
예제 2) 학생,비상연락망 테이블의 학번 컬럼에 인덱스 생성되어 있는 중첩 루프 조인
(학번 1인 데이터(1+2) 와 학번 100인 데이터(1+1) 접근 = 6)
인덱스는 인덱스로 정의된 열 기준으로 순차 정렬됨. 하지만, 인덱스를 이용해 테이블의 데이터를 찾아가는 과정에서 비고유 인덱스를 사용했을 경우 임의 접근 방식인 랜덤 액세스 발생
반대로, 기본키(클러스터형 인덱스) 사용했을 경우 기본 키 순서대로 데이터가 적재되어있으므로 조회 효율이 매우 높음
3. 블록 중첩 루프 조인 (block nested loop join)
- 인덱스 있는 드라이빙테이블과 인덱스 없는 드리븐테이블
- 인덱스 없는 드리븐 테이블에 대해 매번 전체 테이블을 비효율적으로 검색해야함
- 드라이빙 테이블 1건 X 드리븐 테이블 M건
- 이 비효율성을 줄이기 위해 블록 중첩 루프 조인 사용
- 드라이빙 테이블에 대해 조인버퍼라는 개념 도입해서 조인 성능의 향상
예제) 인덱스 있는 학생테이블과 인덱스 없는 비상연락망 테이블
1) 드라이빙 테이블(학생 테이블) 에서 학번 1,학번 100 데이터 검색
2) 검색된 데이터를 조인버퍼에 다 찰때까지 적재
3) 조인버퍼와 비상연락망 테이블 데이터 비교
4) 조인버퍼의 데이터들과 비상연락망 테이블의 한 번의 테이블 풀 스캔으로 원하는 데이터 모두 찾기
==> 비상연락망 테이블의 테이블 풀 스캔 횟수를 줄이는 것이 목적 (성능 저하를 개선)
4. 배치 키 액세스 조인
- 블록 중첩 루프 조인은 드리븐 테이블에서 랜덤 액세스 발생=>액세스할 데이터 범위가 넓다면 비효율적인 조인 방식
- 접근할 데이터를 미리 예상하고 가져오는 배치 키 액세스 조인
- 블록 중첩 루프 조인처럼 조인버퍼 사용
- 추가로, 드리븐 테이블의 데이터를 예측하고 정렬된 상태로 담는 랜덤 버퍼 사용 (MRR-다중범위읽기)
- 따라서 드리븐 테이블에 대해 랜덤 액세스가 아닌 시퀀셜 액세스 수행
예제) 인덱스가 존재하는 학생, 비상연락망 테이블
1) 드라이빙 테이블에서 필요 데이터 추출해서 조인 버퍼에 적재
2) 드리븐 테이블의 인덱스 기반으로 필요 데이터 예측해서 랜덤 버퍼에 적재
3) 3번에서 조인 조건절로 비교
4) 결과 반환
5. 해시 조인
- 조인에 참여하는 각 테이블의 데이터를 내부적으로 해시값으로 만들어 내부 조인 수행
- 해시값으로 내부 조인을 수행한 결과는 조인 버퍼에 저장되므로 조인열의 인덱스를 요구하지 않음
예제)
1) 학생 테이블의 학번 1 의해시값과 비상연락망 테이블의 학번 1의 해시값 비교 => 서로 동일한 경우에만 조인 버퍼에 저장
==>핵심적인 조인 알고리즘으로 처리되지는 않음
