공부는 해도 해도 끝이 없구먼

Nested Loop Join

1. 기본 메커니즘

아래는 중첩 루프문(Nested Loop)의 수행 구조이다.

for(i=0; i<100; i++){
    for(j=0; j<100; j++){
    }
}

뒤에서 설명할 Sort Merge Join과 Hash Join도 각각 소트 영역(Sort Area)과 해시 영역(Hash Area)에 가공해 둔 데이터를 이용한다는 점만 다를 뿐 기본적인 조인 프로세싱은 다르지 않다.

단, 기억할 것은, 각 단계를 완료하고 나서 다음 단계로 넘어가는 게 아니라 한 레코드씩 순차적으로 진행한다는 사실이다.

2. NL Join 수행 과정 분석

프로그래밍을 할 줄 아는 분들이면 매커니즘은 자연스럽게 넘어갈 것이라 사료되는데,

아래의 조인문에서 조건절 비교 순서를 분석해 보자.

/*Data ON-AIR 참조*/

select /*+ ordered use_nl(e) */ e.empno, e.ename, d.dname, e.job, e.sal 
from dept d, emp e 
where e.deptno = d.deptno …………… 
① and d.loc = 'SEOUL' …………… 
② and d.gb = '2' …………… 
③ and e.sal >= 1500 …………… 
④ order by sal desc;

인덱스 상황은 다음과 같다.

* pk_dept : dept.deptno 
* dept_loc_idx : dept.loc 
* pk_emp : emp.empno 
* emp_deptno_idx : emp.deptno 
* emp_sal_idx : emp.sal

조건절 비교 순서, 그리고 위 5개 인덱스 중 어떤 것이 사용될지도 함께 고민해 보면 재밌을 것이다.

Execution Plan --------------------------------------------------- 0 
SELECT STATEMENT 1 0 SORT ORDER BY 2 1 NESTED LOOPS 3 2 
TABLE ACCESS BY INDEX ROWID DEPT 4 3 INDEX RANGE SCAN DEPT_LOC_IDX 5 2 
TABLE ACCESS BY INDEX ROWID EMP 6 5 INDEX RANGE SCAN EMP_DEPTNO_IDX

사용되는 인덱스 : dept_loc_idx, emp_deptno_idx

조건절 비교순서 : ② → ③ → ① → ④ 

실행계획을 해석할 때, 형제(Sibling) 노드 간에는 위에서 아래로 읽는다.

부모-자식(Parent-Child) 노드 간에는 안쪽에서 바깥쪽으로, 즉 자식 노드부터 읽는다.

위 실행계획의 실행 순서를 나열하면 다음과 같다.

1. dept_loc_idx 인덱스 범위 스캔(ID = 4)

2. 인덱스 rowid로 dept 테이블 액세스(ID = 3)

3. emp_deptno_idx 인덱스 범위 스캔(ID = 6)

4. 인덱스 rowid로 emp 테이블 액세스(ID = 5)

5. sal 기준 내림차순(desc) 정렬(ID = 1)

아래는 도식화한 NL Join 실행계획이다.

형제 노드 간에는 좌에서 우로 읽고,

부모-자식 노드 간에는 아래에서 위쪽으로, 즉 자식 노드부터 읽는다.

3. NL Join의 특징

- Random 액세스 위주의 조인 방식

- 조인을 한 레코드씩 순차적으로 진행

즉 NL Join은 소량의 데이터나 부분 범위 처리가 가능한 온라인 트랜잭션 환경에 적합한 조인 방식이라고 할 수 있다.

Sort Merge Join

NL Join은 인덱스에 조인 컬럼을 선두로 가지고 있는지가 매우 중요하다.

선두에 조건이 없으면 옵티마이저는 Sort Merge Join이나, Hash Join을 고려하게 된다.

이 중 Sort Merge Join의 Sort는 양쪽 집합을 조인 컬럼 기준으로 정렬함이고, Merge는 정렬된 양쪽 집합을 Merge 함을 의미한다.

1. 기본 메커니즘

아래는 dept 테이블을 기준으로 emp 테이블을 조인할 경우 Sort Merge Join을 이행하라는 힌트의 예제이다.

/*Data ON-AIR 참조*/

select /*+ ordered use_merge(e) */ d.deptno, d.dname, e.empno, e.ename 
from dept d, emp e 
where d.deptno = e.deptno 
Execution Plan ------------------------------------------------------------- 0 
SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=654 Bytes=35K) 1 0 
MERGE JOIN (Cost=11 Card=654 Bytes=35K) 2 1 
SORT (JOIN) (Cost=6 Card=654 Bytes=14K) 3 2 
TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=654 Bytes=14K) 4 1 
SORT (JOIN) (Cost=5 Card=327 Bytes=11K) 5 4 
TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=327 Bytes=11K)

아래는 도식화된 Sort Merge 수행 과정이다.

실제 조인 수행 과정이 NL Join과 크게 다르지 않다. outer 집합과 inner 집합을 미리 정렬해 둔다는 점만 다르다. 다시 말하지만, 양쪽 집합을 먼저 정렬해 두었기 때문에 위와 같은 처리 로직이 가능하다.

2. Sort Merge Join의 특징

- 조인 하기 전에 양쪽 집합을 정렬한다.

- 부분적으로 부분 범위처리가 가능하다.

- 테이블별 검색 조건에 의해 전체 일량이 좌우된다.

- 스캔 위주의 조인 방식이다.

  - >Inner 테이블 반복 엑세스를 하지 않아서 Random 엑세스가 발생되지 않으나, 전혀없지는 않아서 랜덤 엑세스가 많으면 안 쓰는게 낫다.

Hash Join

1. 기본 메커니즘

Hash Join은 NL Join이나 Sort Merge Join이 효과적이지 못한 상황을 해결하고자 나온 조인 방식이다.

Hash Join 힌트를 사용해 보자.

/*Data ON-AIR 참조*/

select /*+ ordered use_hash(e) */ d.deptno, d.dname, e.empno, e.ename 
from dept d, emp e 
where d.deptno = e.deptno 
Execution Plan ------------------------------------------------------------- 0 
SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=654 Bytes=35K) 1 0 
HASH JOIN (Cost=5 Card=654 Bytes=35K) 2 1 
TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=654 Bytes=14K) 3 1 
TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=327 Bytes=11K)

Hash Join은 둘 중 작은 집합(Build Input)을 읽어 해시 영역(Hash Area)에 해시 테이블(= 해시 맵)을 생성하고,

반대쪽 큰 집합(Probe Input)을 읽어 해시 테이블을 탐색하면서 조인하는 방식이다.

각 단계별로 살펴보자.

• 1단계 : 두 집합 중 작다고 판단되는 집합을 읽어 해시 함수를 이용하여 해시 버킷으로 구성된 배열인 해시 테이블을 만든다. 해시 함수에서 리턴받은 해시 값이 같은 데이터를 같은 해시 버킷에 체인(연결 리스트)으로 연결한다.
• 2단계 : 해시 테이블 생성을 위해 선택되지 않은 나머지 데이터 집합(Probe Input)을 스캔한다.
• 3단계 : Probe Input에서 읽은 데이터로 해시 테이블을 탐색할 때도 해시 함수를 사용한다. 즉, 해시 함수에서 리턴받은 버킷 주소로 찾아가 해시 체인을 스캔하면서 데이터를 찾는다.

NL Join처럼 조인 과정에서 발생하는 Random 액세스 부하가 없고 Sort Merge Join처럼 조인 전에 미리 양쪽 집합을 정렬하는 부담도 없다. 다만, 해시 테이블을 생성하는 비용이 수반된다. 따라서 Build Input이 작을 때 효과적이다. Build Input이 대용량 테이블이면 디스크에 W/R 하는 과정을 거치기 때문에 성능이 저하된다. Build Input으로 선택된 테이블이 작은 것도 중요하지만  해시 키값으로 사용되는 컬럼에 중복값이 없을 때 효과적이다.

2. Build Input이 가용 메모리 공간을 초과할 때 처리 방식 

 In-Memory Hash Join이 불가능할 때 DBMS는 ‘Grace Hash Join’이라고 알려진 조인 알고리즘을 사용하는데,

두 단계로 나누어 진행된다.

1) 파티션 단계

조인되는 양쪽 집합 모두 조인 칼럼에 해시 함수를 적용하고, 반환된 해시 값에 따라 동적으로 파티셔닝을 실시한다. 독립적으로 처리할 수 있는 여러 개의 작은 서브 집합으로 분할함으로써 파티션 짝(pair)을 생성하는 단계다. 파티션 단계에서 양쪽 집합을 모두 읽어 디스크 상의 Temp 공간에 일단 저장해야 하므로 In-Memory Hash Join보다 성능이 크게 떨어지게 된다.

2) 조인 단계

파티션 단계가 완료되면 각 파티션 짝(pair)에 대해 하나씩 조인을 수행한다. 이때, 각각에 대한 Build Input과 Probe Input은 독립적으로 결정된다. 즉, 파티션하기 전 어느 쪽이 작은 테이블이었는지에 상관없이 각 파티션 짝(pair)별로 작은 쪽 파티션을 Build Input으로 선택해 해시 테이블을 생성한다. 해시 테이블이 생성되고 나면 반대 쪽 파티션 로우를 하나씩 읽으면서 해시 테이블을 탐색하며, 모든 파티션 짝에 대한 처리가 완료될 때까지 이런 과정을 반복한다. Grace Hash Join은 한마디로, 분할 정복(Divide & Conquer) 방식이라고 말할 수 있다. 실제로는 DBMS 벤더마다 조금씩 변형된 형태의 하이브리드(Hybrid) 방식을 사용하지만 두 개의 큰 테이블을 Hash Join하는 기본 알고리즘은 Grace Hash Join에 바탕을 두고 있다.

3. Build Input이 해시 키 값에 중복이 많을 때 발생하는 비효율

앞서 설명하였듯이 해시 알고리즘의 성능은 해시 충돌을 얼마나 최소화할 수 있느냐에 달렸으며, 이를 방지하려면 그만큼 많은 해시 버킷을 할당해야만 한다. DBMS는 가능하면 충분히 많은 개수의 버킷을 할당함으로써 버킷 하나당 하나의 키 값만 갖게 하려고 노력한다. 그런데 해시 버킷을 아무리 많이 할당하더라도 해시 테이블에 저장할 키 컬럼에 중복 값이 많다면 하나의 버킷에 많은 엔트리가 달릴 수 밖에 없다. 그러면 해시 버킷을 아무리 빨리 찾더라도 해시 버킷을 스캔하는 단계에서 많은 시간을 허비하기 때문에 탐색 속도가 현저히 저하된다. Build Input의 해시 키 컬럼에는 중복 값이 (거의) 없어야 Hash Join이 빠르게 수행될 수 있음을 이해할 것이다.

4. Hash Join 사용기준

아래는 Hash Join 성능을 좌우하는 두 가지 포인트이다.

• 한 쪽 테이블이 가용 메모리에 담길 정도로 충분히 작아야 함
• Build Input 해시 키 칼럼에 중복 값이 거의 없어야 함

아래는 Hash Join 이 효과적으로 사용될 경우이다.

• 조인 칼럼에 적당한 인덱스가 없어 NL Join이 비효율적일 때
• 조인 칼럼에 인덱스가 있더라도 NL Join 드라이빙 집합에서 Inner 쪽 집합으로의 조인 액세스량이 많아 Random 액세스 부하가 심할 때
• Sort Merge Join 하기에는 두 테이블이 너무 커 소트 부하가 심할 때
• 수행빈도가 낮고 조인할 때

Hash Join은 앞선 다른 조인과 다르게 영구적이지 못하고 소멸되는 치명적인 단점이 있어 수행빈도가 높은 쿼리에 Hash Join을 많이 사용한다면 CPU/Memory 작별을 고해야 할지 모른다.

즉 'Hash Join'은 수행 빈도가 낮고, 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인할 경우에 주로 사용해야 한다. 

1. Index Tuning 기초

1). 인덱스 사용이 완전히 불가능하거나 범위 스캔이 불가능한 경우

하기와 같이 인덱스 건두 컬럼을 WHERE절에서 가공을 하면 (함수기반의 인덱스를 정의하지 않는 한)

정상적인 인덱스 사용이 불가하다.

SELECT * FROM 고깃집 WHERE SUBSTR(삼겹살원산지,1,2) = '오스'

또한 아래 처럼 부정형 비교를 사용해도 마찬가지다.

SELECT * FROM 손님 WHERE 직업<> '먹방유튜버'

is not null 조건도 물론 부정형 비교에 해당되므로 정상적인 인덱스 사용은 어렵다.

SELECT * FROM 한우 WHERE 한우등급 IS NOT NULL

하지만 다음의 경우는 사용 가능하다.

SELECT * FROM 손님 WHERE 허기 IS NULL

다른 인덱스 컬럼에 IS NULL이 아닌 조건식이 하나라도 있거나, NOT NULL 제약이 있으면, Oracle에서도 IS NULL조건에 대한 Index Range Scan이 가능하다. (인덱스 선두 컬럼이 조건절에 누락이 되지 않는 한에서)

2). Index column 가공

조건절에 인덱스 컬럼을 가공하면 정상적인 Index Range Scan은 불가한데, 하기와 같은 다양한 튜닝 방안이 있다.

SELECT *
FROM 고기집
WHERE SUBSTR(삼겹살원산지,1,2) = '오스'
=> SELECT * 
   FROM 고기집 
   WHERE 삼겹살원산지 LIKE '오스%'


SELECT *
FROM 주문
WHERE TO_CHAR(일시, 'yyyymmdd') = :orderDt
=> SELECT *
   FROM 주문
   WHERE 일시 >= TO_DATE(:orderDt, 'yyyymmdd')
   AND 일시 < TO_DATE(:orderDt, 'yyyymmdd') +1


SELECT *
FROM 손님
WHERE 연령 || 직업 = '15먹방유튜버'
=> SELECT *
   FROM 손님
   WHERE 연령 = '15'
   AND 직업 = '먹방유튜버'


...

2. Table Random Access 최소화

1). Index Rowid에 의한 테이블 랜덤 액세스

인덱스에 참조되는 컬럼이 모두 포함되는 경우가 아니라면, ‘테이블 랜덤 액세스’가 일어난다. 아래 실행계획에서 ‘TABLE ACCESS (BY INDEX ROWID)’라고 표시된 부분을 말한다.

SQL> select * from 손님 where 지역 = '제주도'; 
Execution Plan ------------------------------------------------ 0 
SELECT STATEMENT Optimizer=ALL_ROWS 1 0 TABLE ACCESS (BY INDEX ROWID) 
OF '손님' (TABLE) 2 1 INDEX (RANGE SCAN) OF '고객_지역_IDX' (INDEX)

테이블 랜덤 액세스의 내부 메커니즘

• 인덱스 ROWID에 의한 테이블 액세스 구조
• 인덱스에서 하나의 rowid를 읽고 DBA(디스크 상의 블록 위치 정보)를 해시 함수에 적용해 해시 값을 확인한다.
• 해시 값을 이용해 해시 버킷(슬롯)을 찾아간다.
• 해시 버킷에 연결된 해시 체인을 스캔하면서 블록 헤더를 찾는다.
• 해시 체인에서 블록 헤더를 찾으면 거기 저장된 포인터를 이용해 버퍼 블록을 읽는다.
• 해시 체인을 스캔하고도 블록 헤더를 찾지 못하면, LRU(페이지교체) 리스트를 스캔하면서 Free 버퍼를 찾는다. 디스크에서 읽은 블록을 적재하기 위해 빈 캐시 공간을 찾는 것이다.
• LRU 리스트에서 Free 버퍼를 얻지 못하면 Dirty 버퍼를 디스크에 기록해 Free 버퍼를 확보한다.
• Free 버퍼를 확보하고 나면 디스크에서 블록을 읽어 캐시에 적재한다.

• 클러스터링 팩터(Clustering Factor)

Oracle은 ‘클러스터링 팩터’라는 개념을 사용해 인덱스 ROWID에 의한 테이블 액세스 비용을 평가한다. SQL Server는 공식적으로 이 용어가 없지만 내부적인 비용 계산식에 이런 개념이 포함돼 있을 것이다. 클러스터링 팩터는 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미한다.

아래의 그림은 좋은 상태를 도식화한 것으로서, 인덱스 레코드 정렬 순서와 거기서 가리키는 테이블 레코드 정렬 순서가 100% 일치하는 것을 볼 수 있다.

반면 다음은 인덱스 클러스터링 팩터가 가장 안 좋은 상태를 도식화한 것으로서, 인덱스 레코드 정렬 순서와 테이블 레코드 정렬 순서가 전혀 일치하지 않는다.

2). Index 손익분기점

인덱스 rowid에 의한 테이블 액세스는 고비용 구조여서, 일정량을 넘는 순간 Full Scan 할 때 보다 더 느려진다.

흔히 Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점을 ‘손익 분기점’이라고 부른다.

- 인덱스 손익분기점은 일반적으로 5~20%의 낮은 수준에서 결정

- 클러스터링 팩터가 나쁘면 손익분기점은 5% 미만에서 결정 (더할 경우 1% 미만으로..)

- 클러스터링 팩터가 아주 좋을 때는 손익분기점이 90% 수준까지도

인덱스에 의한 액세스가 Full Table Scan보다 더 느리게 만드는 가장 핵심적인 두 가지 요인

- 인덱스 Rowid에 의한 테이블 액세스는 랜덤 액세스인 반면, Full Table Scan은 시퀀셜 액세스 방식으로 이루어진다

- 디스크 I/O 시, 인덱스 Rowid에 의한 테이블 액세스는 Single Block Read 방식을 사용,

  Full Scan은 Multiblock Read 방식을 사용한다.

손익분기점 극복하는 방법

- 테이블을 인덱스 구조로 생성 (테이블 자체가 인덱스 구조이므로 항상 정렬된 상태 유지)

- SQL Server의 Include Index 사용 (인덱스 키 외에 미리 지정한 컬럼을 리프 레벨에 함께 저장. 랜덤 액세스 횟수 감소)

- Oracle의 Clustered Table 사용 ( 키 값이 같은 레코드를 같은 블록에 저장하기 때문에 클러스터 테이블에 대한 클러스터    인덱스를 이용할 때는 테이블 Random 액세스가 키 값별로 한 번씩만 발생, Sequential 방식으로 스캔하기 때문에 넓은 

  범위를 읽더라도 비효율이 없다.)

- 파티셔닝 (대량 범위검색 조건으로 자주 사용되는 컬럼 기준으로 테이블을 파티셔닝 하면 일부만 읽고도 멈출 수 있다.)

  * 클러스터 : 블록단위 저장 / 파티셔닝 : 세그먼트단위 저장

3). Table Random Access 최소화 튜닝

A. 인덱스 컬럼추가

 - 인 덱스 스캔량은 줄지 않지만 테이블 Random 액세스 횟수를 감소시킬 수 있다.

B. Covered Index (모든 컬럼 사용)

 -  테이블 Random 액세스가 아무리 많더라도 필터 조건에 의해 버려지는 레코드가 거의 없다면.. 

C. Include Index

 - 인덱스 키 외에 미리 지정한 컬럼을 리프 레벨에 함께 저장하는 기능이고, 컬럼을 최대 1,023개까지 지정 가능하다.

 - 수평적인 탐색을 위한 필터 조건으로만 사용된다.

 - 인덱스 생성 시에 아래와 같이 include 옵션을 지정하면 된다.

    CREATE IINDEX [INDEX_NAME] ON TABLE (column,....) INCLUDE (column,...)

D. IOT, 클러스터형 인덱스, 클러스터 테이블 활용

 - 해시 클러스터 테이블은 해시 함수에서 반환된 값이 같은 데이터를 물리적으로 함께 저장하는 구조이다.

 - 클러스터 키로 데이터를 검색하거나 저장할 위치를 찾을 때 해시 함수를 사용한다. (해시 함수가 인덱스 역할 대체함.)

 - 역시나 해시답게 '='만 사용가능하다.

E. 수동으로 클러스터링 팩터 높이기

 - 테이블을 Reorg 할 때는 가장 자주 사용되는 인덱스를 기준으로 삼아야 함

 - 다른 인덱스를 사용하는 중요한 쿼리 성능에 나쁜 영향을 주지 않는지 반드시 체크

 - 테이블과 인덱스를 Rebuild 하는 부담이 적고 그 효과가 확실할 때만 사용

3. Index Scan Range 최소화

I/O 튜닝의 핵심 원리는 아래의 두 가지다.

1. Random 액세스 발생량을 줄인다.

2. Sequential 액세스에 의한 선택 비중을 높인다.

앞서서 랜덤 액세스를 최소화하는 방안은 설명하였으니, 이제는 2번 Sequential 액세스에서도 인덱스를 Sequential 방식으로 스캔하는 단계에서 발생하는 비효율 해소 원리를 다루는 내용이다. 

1). 인덱스 선행 컬럼이 범위 조건일 때의 비효율

인덱스는 등치조건으로 비교하면 리프 블록을 스캔하면서 읽은 레코드는 모두 테이블 액세스로 이어지는데, 버려지는 레코드가 하나도 없으므로 인덱스 스캔 단계에서의 효율은 가히 최고라 칭할 수 있다.

그러나 세상은 호락호락하지 않은 법

인덱스 선행 컬럼이 조건절에 누락 또는 Between, 부등호, Like 같은 범위검색 조건이 사용되면 인덱스를 스캔하는 단계에서 비효율이 생긴다.

2). 범위조건을 In-List로 전환

범위검색 컬럼이 맨 뒤로 가도록 인덱스를 변경하면 좋겠지만, 운영 중인 시스템에서 인덱스 구성을 바꾸기는 쉽지 않다.

하기는 In-List 사용 시 주의해야 할 점이다.

- In-List 사용 시에는 개수가 많지 않아야 한다. (수직 탐색이 여러 번 발생하기 때문)

- 인덱스 높이가 높을 경우 (또는 Between 보다 비효율이 클 경우) Index Skip Scan이 유용할 수 있다.

3). 범위조건을 2개 이상 사용할 때의 비효율

범위검색 조건을 2개 이상 사용하면 첫 번째가 인덱스 스캔 범위를 거의 결정되고, 두 번째는 필터 조건 역할만 하기 때문에 성능상 불리해질 수 있다.

스캔량이 소량일 때는 그 차이가 미미하지만 대량일 때는 상당한 성능차이를 보일 수 있으므로 인덱스 컬럼에 대한 비교 연산자를 신중하게 선택해야 한다.

 - UNION ALL을 이용하는 방법이 있다. (조건에 입력되는 값의 선택도에 따라 결정을 권고)

4. Index 설계

1). 결합 인덱스 구성을 위한 기본 공식

- 가장 정상적이고 일반적인 것은 Index Range Scan이다.

- 결합 인덱스를 구성할 때 첫 번째 기준은, 조건절에 항상 사용되거나, 선정된 칼럼 중 ‘=’ 조건으로 자주 조회되는 컬럼은 

  앞쪽에 두어야 한다.

- 소트 오퍼레이션을 생략하도록 하기 위해 컬럼을 추가하는 것이다. (인덱스는 항상 정렬 상태를 유지하므로 조건절에

  미사용 된 컬럼이더라도 소트 연산을 대체할 목적으로 인덱스 구성에 포함시킴으로써 성능 개선을 도모할 수가 있다.)

  => 사용법 : 인덱스 칼럼 구성과 같은 순서로 누락 없이(뒤쪽 칼럼이 누락되는 것은 상관없음) order(group) by절에 기술해야 함.

- 선택도가 충분히 낮은지가 중요하다.

But 

선택도도 중요하지만, 선택도보다도 조건절에서 어떻게 사용되며, 사용빈도는 어떤지, 효용성을 따져서 어떤 것이 빠르게 데이터 검색이 가능한지 등이 더 중요할 수 있기에 충분한 고민을 해야 할 것이다.

2). 추가적인 고려사항

위의 공식은 일반적인 기본 공식이지만, 추가적으로 고려해야 할 요소도 존재하다.

- 쿼리 수행 빈도

- 업무상 중요도

- 클러스터링 팩터

- 데이터양

- DML 부하 (기존 인덱스 개수, 초당 DML 발생량, 자주 갱신되는 컬럼 포함 여부 등)

- 저장 공간

- 인덱스 관리 비용 등

End-Point

결과적으로 개별 쿼리 성능을 높일 뿐만 아니라

전략적인 인덱스 설계로 생성되는 인덱스 개수를 최소화함으로써

DML 부하를 줄이는 것이 중요한 목표여야 할 것이다.

1. Index Range Scan

- 인덱스를 수직적으로 탐색 후에 리프 블록에서 필요한 범위만 스캔하는 것이다.

- B*Tree 형태의 인덱스에서 가장 일반적인 형태의 엑세스 방식이다. 

- SQL Server에서는 Index Seek이라고 표현한다.

- Index Range Scan이 가능하게 하려면 인덱스를 구성하는 선두 컬럼이 조건절에 사용되어야 한다.

- 결과집합은 인덱스 컬럼 순으로 정렬된 상태가 되기 때문에 이런 특징을 잘 이용하면 sort order by 연산을 생략하거나

   min/max 값을 빠르게 추출할 수 있다.

Point

인덱스를 스캔하는 범위를 얼만큼 줄일 것인지, 테이블로 액세스하는 횟수를 얼마만큼 줄일 것인지가 관건이며, 이는 인덱스 설계와 SQL 튜닝의 핵심 원리 중 하나이다.

SQL> create index soju_sojuno_idx on soju(sojuno); 
SQL> set autotrace traceonly explain SQL> select * from soju where sojuno = 7; 
Execution Plan ------------------------------------------------------ 0 
SELECT STATEMENT Optimizer=ALL_ROWS 1 0 TABLE ACCESS (BY INDEX ROWID) 
OF 'SOJU' (TABLE) 2 1 INDEX (RANGE SCAN) OF 'SOJU_SOJUNO_IDX' (INDEX)

2. Index Full Scan

- 수직적 탐색 후 Leaf 블록을 처음부터 마지막까지 수평적으로 탐색

- 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다.

Point

데이터 저장공간은 '컬럼길이 × 레코드수’에 의해 결정되므로 인덱스가 차지하는 면적은 테이블보다 적은 것은 사실이다. 인덱스 스캔 단계에서 필터를 걸치고, 일부만 테이블 엑세스가 발생할 경우 테이블 전체를 스캔하는 것 보다 낫다.

SQL> create index soju_idx on soju (sojuname, price); 
SQL> set autotrace traceonly exp SQL> select * from soju 2 where price > 5000 5 
order by sojuname; Execution Plan ---------------------------------------------------------- 0 
SELECT STATEMENT Optimizer=ALL_ROWS 1 0 TABLE ACCESS (BY INDEX ROWID) 
OF 'SOJU' (TABLE) 2 1 INDEX (FULL SCAN) OF 'SOJU_IDX' (INDEX)

3. Index Unique Scan

- 수직적 탐색만으로 데이터를 찾는 스캔으로, Unique 인덱스를  = 조건으로 탐색할 경우에 작동

SQL> create unique index pk_soju on soju(sojuno); 
SQL> alter table soju add 2 constraint pk_soju primary key(sojuno) using index pk_soju; 
SQL> set autotrace traceonly explain SQL> select sojuno, sojuname from soju where sojuno = 1924; 
Execution Plan ----------------------------------------------- 0 
SELECT STATEMENT Optimizer=ALL_ROWS 1 0 TABLE ACCESS (BY INDEX ROWID) 
OF 'SOJU' 2 1 INDEX (UNIQUE SCAN) OF 'PK_SOJU' (UNIQUE)

*TMI : 1924년도는 진로가 처음 출시된 연도이다.

4. Index Skip Scan

- 인덱스 선두 컬럼이 조건절에 빠졌어도 인덱스를 활용

- Root or Branch 블록에서 읽은 컬럼값 정보를 이용해 조건에 부합하는 레코드를 '포함 가능성'있는 하위블록만 선택해    액세스하는 방식이다.

Point

조건절에 빠진 인덱스 선두 컬럼의 중복값의 개수가 적고 후행 칼럼의 중복값의 개수가 많을 때 유용하다.

In-List를 사용하면 Index Skip Scan을 타지 않고도 빠른 결과값을 얻는데, In-List로 제공하는 값의 종류가 적어야 한다.

SQL> select * from 소주판매원 where 연봉 between 1500 and 2000; 
Execution Plan -------------------------------------------------- 0 
SELECT STATEMENT Optimizer=ALL_ROWS 1 0 TABLE ACCESS (BY INDEX ROWID) 
OF '소주판매원' (TABLE) 2 1 INDEX (SKIP SCAN) OF '소주판매원 _IDX' (INDEX)

5. Index Fast Full Scan

- 인덱스 트리구조를 무시, 세그먼트 전체를 Multiblock Read 방식으로 스캔해서 Index Full Scan보다 빠른 인덱스다.

하기는 Index Full Scan과 Index Fast Full Scan의 비교이다.

6. Index Range Scan Descending

- Index Range Scan과 방식은 동일한 스캔 방식이지만, 뒤에서 앞으로 스캔하기 때문에 내림차순으로 정렬된다.

Point

max/min 등 집계함수를 사용하여 값을 도출할 때도 해당 컬럼에 인덱스가 있으면 뒤에서 부터 읽는다.

SQL> select * from soju 2 where sojuno is not null 3 order by sojuno desc; 
Execution Plan ------------------------------------------------------------- 0 
SELECT STATEMENT Optimizer=ALL_ROWS 1 0 TABLE ACCESS (BY INDEX ROWID) 
OF 'SOJU' (TABLE) 2 1 INDEX (RANGE SCAN DESCENDING) OF 'PK_SOJU' (INDEX (UNIQUE))

1. 인덱스 기본 

특징

- 인덱스는 DBMS 마다 저장방식, 구조, 탐색 알고리즘이 조금씩 다르지만 일반적으로 B*Tree의 자료구조형을 가진다.

- 보통의 경우에 Root부터 Leaf까지 3단계를 지니지만 2단계 구조도 거칠 경우가 있다.

- 루트에서 리프 블록까지의 거리를 인덱스 깊이라고 부르며, 반복 탐색할 때 성능에 영향을 끼친다.

- 루 트와 브랜치 블록은 각 하위 노드들의 데이터 값 범위를 나타내는 키 값과, 그 키 값에 해당하는 블록을 찾는 데 필요한 주소 정보를 가진다.

- 리프 블록은 인덱스 키 값과, 그 키 값에 매칭되는 테이블 레코드를 찾아가는 데 필요한 주소 정보(ROIWD)를 가진다. 키 값이 같을 때는 ROWID 순으로 정렬이 된다.

- 리프 블록은 선형으로 이루어져 있어 ASC, DESC 상관없이 양방향 연결 리스트 구조로 되어있다.

NULL 주의사항

- Oracle에서 인덱스 구성 컬럼이 모두 NULL인 레코드는 인덱스에 저장하지 않는다.

- SQL Server는 인덱스 구성 컬럼이 모두 NULL인 레코드도 인덱스에 저장한다.

- NULL 값을 Oracle은 맨 뒤에 저장, SQL Server는 맨 앞에 저장한다.

- DBMS마다 NULL값 처리가 다르기 때문에 인덱스를 설계 또는 SQL을 작성할 경우에 숙지해야 한다.

2. 인덱스 탐색 

- 탐색 과정을 살펴보기 앞서 B*Tree의 구조를 다시금 보면, Root에서 부터 아래로 화살표가 향해있는 수직적 탐색과

  하단의 Leaf 블록에서 옆으로 화살표가 되어 있는 수평적 탐색을 볼 수 있다.

- 수직적 탐색은 수평적 탐색을 위한 시작 지점을 찾는 과정이며, Root에서 Leaf 블록까지 아래로 진행하기에 수직적 탐색

  이다.

- 수평적 탐색은 인덱스 리프 블록에 저장된 레코드 마다의 연결 순서에 따라 좌 -> 우, 우->좌로 스캔하기에 수평적 탐색

  이다.