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

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 부하를 줄이는 것이 중요한 목표여야 할 것이다.