[SQL 튜닝 실습] SQL 튜닝용어 정리 2

[조인 훈련]

이 학번 컬럼값을 기준으로 두 테이블을 가지고 조인 분석을 하세요.

 

순서

1. 원 2개 겹쳐그리기

2. 공통된 교집합 찾아서 값 적기

3. 교집합을 제외한 각각 양쪽에 해당하는 값 적기

 

정답

 

(*) 조인작성 시 사람이 판단하는 기준인 왼쪽을 기준으로 인지하기 편하므로 레프트 아우터 조인을 권장한다.

 

조인 알고리듬 용어

테이블 조인 시, 조인순서 배치에 따라 테이블의 데이터에 접근하는 우선순위

첫번째 : 드라이빙 테이블

두번째 : 드리븐 테이블 용어로 구분한다.

(*) 리턴결과가 적을 것으로 예상되는 테이블을 드라이빙 테이블로 선정하며, 조인 조건적의 열이 인덱스로 설정되도록 구성해야한다.

 

중첩 루프 조인 (NL 조인)

드라이빙 테이블의 데이터 1건당 드리븐 테이블의 데이터 N건 씩 반복하며 검색하여 최종적으로 양쪽 테이블에 공통된 데이터 출력

 

인덱스가 없는 상황에서 테이블 풀 스캔을 실시

1) 학번 1을 학생 테이블에서 검색하려고 학생 테이블의 데이터 100건에 모두 접근

2) 학번 1과 동일한 데이터가 있는지 비교하려고 비상연락망 테이블의 데이터 1000건에 접근

3) 학번 100의 학생 정보를 찾고자 학생 테이블의 데이터 100건에 모두 접근

4) 학생 테이블의 학번 100 데이터와 동일한 데이터 1000건에 모두 접근 즉, 2,200 건의 데이터에 접근

 

인덱스가 있는 상황에서 스캔

1) 인덱스 초기값을 확인 후 테이블의 데이터에 접근

2) 인덱스 다음값을 확인 후 접근대상이 아니면 인덱스 다음값으로 스킵

(*) 인덱스로 테이블의 데이터까지 찾아가는 과정에서 랜덤 액세스가 발생

(*) 랜덤 액세스를 줄이려면 고유(유니크한) 인덱스를 설계 후 그 인덱스 컬럼으로 조건절을 작성한다.

 

 

블록 중첩 루프조인 (BNL 조인)

인덱스가 있는 드라이빙 테이블과 인덱스가 없는 드리븐 테이블의 조인이 필요할 때 블록 중첩 루프 조인이다.

드라이빙 테이블에 대해 '조인 버퍼' 라는 개념을 도입하여 조인성능의 향상을 기대한다

 

조인 수행절차

1) 드라이빙 테이블인 학생 테이블에서 학번 1과 100에 해당하는 데이터를 검색

2) 검색된 데이터를 조인 버퍼에 가득 채워질때까지 적재

3) 조인버퍼와 드리븐 테이블의 데이터를 비교하여 조인 다시 1) 부터 3) 까지 반복 조인버퍼의 데이터들과 드리븐 테이블의 한 번의 테이블 풀 스캔으로 원하는 데이터 모두 조회가능

 

배치 키 액세스 조인 (BKA 조인) 

중첩 루프 조인 방식은 데이터 접근 시 인덱스에 의한 랜덤 액세스가 발생하므로 액세스할 데이터 범위가 넓으면 비효율적이다

이러한 랜덤 액세스 단점을 해결하고자 접근할 데이터를 미리 예상하고 조회하기위해 배치 키 액세스 조인이 개발됨

블록 중첩 루프 조인에서 사용된 조인 버퍼 개념을 그대로 사용한다.

미리 예측된 데이터를 가져와서 정렬된 상태에서 랜덤 버퍼에 담기때문에 드리븐 테이블에 대해 랜덤 액세스가 아닌 시퀀셜 액세스를 수행하는 방식

 

해시 조인

마리아DB 버전 5.3 이후부터 블록 중첩 루프 해시 라는 이름으로 기능제공

조인에 참여하는 각 테이블의 데이터를 내부적으로 해시값으로 만들어 내부조인을 수행한다

해시값으로 내부조인을 수행한 결과는 조인버퍼에 저장되므로 조인열의 인덱스를 필수로 요구하지 않아도 된다.

 

개념적인 튜닝 용어

오브젝트 스캔 유형

테이블 스캔 : 인덱스를 안거치고 바로 디스크에 위치한 테이블 데이터에 접근하는 유형

인덱스 스캔 : 인덱스로 테이블 데이터를 찾아가는 유형

 

테이블 풀 스캔

인덱스를 안거치고 테이블로 바로 진행해서 처음부터 끝까지 데이터를 조회하는 방식

인덱스없이 사용하기때문에 성능 측면에서는 부정적이다

 

인덱스 범위 스캔

인덱스를 범위 기준으로 스캔 후 스캔 결과를 토대로 테이블의 데이터를 조회하는 방식

BETWEEN ~ AND 또는 <, >, LIKE 등의 비교연산 등의 조건에 포함될 경우 인덱스 범위 스캔으로 수행한다

좁은 범위를 스캔할 때는 성능적으로 아주 효율적이지만,

넓은 범위를 스캔할 때는 비효율적이다.

 

인덱스 풀 스캔

인덱스를 처음부터 끝까지 수행하는 방식

단, 테이블에 접근하지 않고 인덱스로 구성된 열 정보만 요구하는 SQL 문에서 수행

인덱스라는 오브젝트의 전 영역을 검색하기때문에 검색 범위를 최대한 줄이는 방향으로 SQL 튜닝을 해야한다.

 

인덱스 고유 스캔

기본키나 고유 인덱스로 테이블에 접근하는 방식

인덱스를 사용하는 스캔 방식 중 가장 효율적인 스캔 방식

조건절에 = 조건으로 작성하며, 해당 조인 열이 고유 인덱스의 선두열로 설정되었을 때 활용

 

인덱스 루스 스캔

인덱스의 필요한 부분들만 골라서 스캔하는 방식

인덱스 범위 스캔처럼 넓은 범위에 전부 접근하지않고 조건문 기준으로 필요한 데이터와 필요없는 데이터를 구분한 후

불필요한 인덱스 키는 무시한다

GROUP BY 구문이나 MAX(), MIN() 함수가 포함되면 작동한다.

이미 오름차순으로 정렬된 인덱스에서 최댓값이나 최솟값이 필요한 경우가 해당

 

인덱스 병합 스캔

테이블 내에 생성된 인덱스들을 통합해서 스캔하는 방식

 

디스크 접근 방식

페이지란 데이터를 검색하는 최소 단위

 

시퀀셜 액세스

서로 연결된 페이지를 차례대로 읽을 수 있으며 보통 테이블 풀 스캔에서 활용한다

 

랜덤 액세스

여기저기 물리적으로 떨어진 페이지를 임의로 열어보면서 데이터를 읽을 수 있다.

최소한의 페이지에 접근할 수 있도록 접근 범위를 줄이고 효율적인 인덱스를 활용할 수 있도록 튜닝해야한다.

 

조건 유형

액세스 조건

디스크에 있는 데이터에 어떻게 접근할 것인지 다루는 액세스 조건은 SQL 튜닝에서 가장 중요한 핵심 사항이다

SQL 조건문이 복잡하면 실제 데이터를 가져오기 위해 활용하는 SQL문 조건절에는 한계가 있기때문에

옵티마이저는 조건절의 특정 조건문을 이용해 소량의 데이터를 가져오고,

인덱스를 통해 시간 낭비를 줄이는 조건절을 선택해서

스토리지 엔진의 데이터에 접근하고 마리아DB 엔진으로 데이터를 가져온다

 

필터 조건

액세스 조건을 이용해 추가로 불필요한 데이터를 제거하거나 가공하는 조건이다