3회차 인덱스 튜닝

3회차 인덱스 튜닝 - 1

seoyyyy 2025. 2. 8. 15:12

1. 테이블 액세스 최소화

1.1 테이블 랜덤 액세스

- SQL 이 참조하는 컬럼을 인덱스가 모두 포함하지 않는다면 인덱스를 스캔한 후 반드시 테이블을 액세스하게 되는데 실행계획에서는 'TABLE ACCESS BY INDEX ROWID' 라고 표시된다.
- 인덱스를 스캔하는 이유는 검색조건을 만족하는 소량의 데이터를 인덱스에서 빨리 찾고 거기서 테이블 레코드를 찾아가기 위한 주소값, 즉 ROWID를 얻으려는데 있다.

😀인덱스 ROWID는 물리적 주소일까? 논리적 주소일까?
- 인덱스 ROWID는 논리적 주소이다.

- ROWID는 프로그래밍의 포인터와는 다른개념이며, 테이블 레코드와 물리적으로 직접 연결된 구조가 아니다.
- 디스크 상에서 테이블 레코드를 찾아가기 위한 논리적 주소 정보를 담고 있다.

😊 메인 메모리 DB와 비교

- 메인 메모리 DB(MMDB)
: 데이터를 모두 메모리에 로드해 놓고 메모리를 통해서만 I/O를 수행하는 DB
: 인스턴스 가동 시 디스크에 저장된 데이터를 버퍼캐시로 로딩하고 이어 인덱스를 생성함, 이때 인덱스는 오라클처럼 디스크상의 주소정보를 갖는게 아닌 메모리상의 주소정보, 즉 포인터를 갖는다. (테이블 액세스 비용이 낮음)

- 오라클 DB
: 테이블 블록이 수시로 버퍼 캐시에서 밀려났다 캐싱되며, 그때마다 다른 공간에 캐싱되므로 인덱스에서 포인터로 직접 연결할 수 없는 구조이다. 메모리 주소 정보(포인터)가 아닌 디스크 주소 정보(DBA)를 이용해 알고리즘으로 버퍼 블록을 찾아간다.
: 블록을 읽을 때 읽고자 하는 DBA를 해싱 알고리즘으로 해시 체인을 찾고 거기서 버퍼 헤더를 찾은 뒤 버퍼 블록을 찾아간다.
: ROWID가 가리키는 테이블 블록을 버퍼캐시에서 먼저 찾아보고 찾지 못하면 디스크에서 블록에서 찾아 버퍼캐시에 적재한 후 읽는다.
: 이 과정에서 DBA 해싱과 래치 획득 과정을 반복하고, 동시 액세스가 심할 땐 캐시버퍼 체인 래치와 버퍼 Lock 에 대한 경합이 발생할 수 있다. ➡️ 인덱스 ROWID를 이용한 테이블 액세스는 이처럼 고비용 구조이다.

1.2 인덱스 클러스터링 팩터

😎 클러스터링 팩터(Clustering Factor, 'CF') 란?
: '군집성 계수' 로 번역할 수 있음
: 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미
: CF 가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋음
: 검색 효율이 좋다 == 테이블 액세스량에 비해 블록 I/O가 적게 발생함

- 아래는 인덱스 클러스터링 팩터가 가장 좋은상태를 도식화한 것이며, 인덱스 레코드 정렬 순서와 테이블 레코드 정렬 순서가 100% 일치한 상태

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

1.3 인덱스 손익 분기점

- 인덱스 ROWID를 이용한 테이블 액세스는 고비용 구조로 읽어야 할 데이터가 일정량을 넘기는 순간 테이블 전체를 스캔하는 것보다 오히려 느려진다.
- Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점을 '인덱스 손익분기점'이라고 부른다.

- Table Full Scan은 1,000만건 중 한 건을 조회하든, 10만건을 조회하든, 1,000만건을 다 조회하든 성능이 거의 일정하다.
- 인덱스를 이용해 테이블을 액세스할 때는 테이블 랜덤 액세스를 하므로 추출 건수가 많을수록 느려진다.

😣 인덱스를 이용한 테이블 액세스가 Table Full Scan보다 더 느려질 수 있는 핵심적인 두가지 요인
- Table Full Scan은 시퀀셜 액세스, 인덱스 ROWID를 이용한 테이블 액세스는 랜덤 액세스 방식
- Table Full Scan은 Multiblock I/O, 인덱스 ROWID를 이용한 테이블 액세스는 Single Block I/O 방식

- 여기서 더해 인덱스 클러스터링 팩터(CF)가 안좋으면 같은 테이블 블록을 여러번 반복 액세스하면서 논리적 I/O 횟수, 물리적 I/O 횟수가 증가해 손익분기점이 5% 에서 1% 미만으로 낮아진다. 반대로 CF가 좋을 때 손익분기점이 90%까지도 올라간다.

😀온라인 프로그램 튜닝 VS 배치 프로그램 튜닝
- 온라인 프로그램
: 보통 소량 데이터를 읽고 갱신하므로 인덱스를 효과적으로 활용하는 것이 무엇보다 중요하다.
: 조인은 대부분 NL(Nested Loop, 인덱스를 이용하는 조인방식) 방식을 사용한다.
: 인덱스를 이용해 소트연산을 생략함으로써 부분범위 처리방식으로 구현할 수 있다면 대량 데이터를 조회할 때도 아주 빠른 응답 속도를 낼 수 있다.
- 배치(Batch) 프로그램
: 대량 데이터를 읽고 갱신하므로 항상 전체범위 처리 기준으로 튜닝해야한다.
: 인덱스와 NL 조인보다 Full Scan과 해시 조인이 유리하다.
: 대량 배치 프로그램에서 Full Scan이 유리하지만 초대용량 테이블을 Full Scan 시엔 상당히 오래기다려야 하고 시스템의 부담도 적지않다.
: 따라서 배치 프로그램에서는 파티션 활용 전략이 매우 중요한 튜닝 요소이다. (+ 병렬 처리까지 더한다면 더 좋음)

😊 초대용량 테이블의 Full Scan을 빠르게 처리하기 위해서는 테이블을 파티셔닝 하는 것도 방법이다. ( 관리적 측면X 성능 측면 O)
ex) 고객변경 이력 테이블을 변경 일시 기준으로 파티셔닝하면 변경일시 조건(최근 1년) 에 해당하는 파티션만 골라 Full Scan

1.4 인덱스 컬럼 추가

테이블 액세스 최소화를 위해 가장 일반적으로 사용하는 튜닝 기법은 인덱스에 컬럼을 추가하는 방법이다.

아래는 DEPTNO, JOB 순인 EMP_X01 인덱스 하나만 있는 상태에서 쿼리를 수행한 예이다.

SELECT /*+ index(emp emp_x01) */ *
from emp
where deptno = 30
and sal >= 2000;

위 조건에 만족하는 사원은 한명이지만 이를 찾기 위해 테이블을 여섯번 액세스하게 된다.
이를 위해 인덱스를 DEPTNO, SAL 순으로 변경하는 방식은 실 운영 환경에서 쉽지 않고, 인덱스를 매 경우마다 새로 생성하면 인덱스 관리비용이 증가한다.

이 때 기존 인덱스에 SAL 컬럼을 추가하는 것만으로 인덱스 스캔량은 그대로지만 테이블 랜덤 액세스 횟수가 줄어들게 되어 큰 효과를 얻을 수 있다.

1.5 인덱스만 읽고 처리

😎 Include 인덱스
- SQL Server 2005 버전에 추가된 기능으로 Oracle엔 아직 없는 기능
- 인덱스 키 외에 미리 지정한 컬럼을 리프 레벨에 함께 저장하는 기능으로 인덱스 생성 시 아래와 같이 include 옵션을 지정하면 된다. (최대 1,023개까지 지정 가능)

-- include 인덱스 생성
create index emp_x01 on emp(deptno) include (sal)

-- oracle 인덱스 생성
create index emp_x02 on emp(deptno, sal)

- 기존 인덱스(emp_x02)는 deptno, sal 컬럼 모두 루트와 브랜치 블록에 저장하여 수직적 탐색에 사용할 수 있지만 include 방식(emp_x01)은 sal 컬럼을 리프 블록에만 저장한다. 수직적 탐색 시 deptno만 사용하고, 수평적 탐색에서 sal 컬럼도 필터 조건으로 사용할 수 있다.(랜덤 액세스 횟수를 줄이는 용도)
- 기존 인덱스는 아래 쿼리 실행 시 소트 연산 생략이 가능하지만 include 인덱스는 생략이 되지 않는다.

select * from emp where deptno = 20 order by sal

1.6 인덱스 구조 테이블

IOT(Index-Organized Table)
: 테이블을 찾아가기 위한 ROWID를 갖는 일반 인덱스와 달리 IOT는 테이블 블록에 있어야 할 데이터를 인덱스 리프 블록에 모두 저장한다. (인덱스 리프 블록이 곧 데이터 블록)

테이블을 인덱스 구조로 만드는 구문

create table index_org_t (a number, b varchar(10), constraint index_org_t_pk primary key (a))

organization index;

참고로 일반 테이블은 '힙 구조 테이블'로 테이블 생성 시 대개 생략하지만 아래와 같이 organization 옵션을 명시할 수도 있다.

create table index_org_t (a number, b varchar(10), constraint index_org_t_pk primary key (a))

organization heap;

- IOT는 인위적으로 클러스터링 팩터를 좋게 만드는 방법 중 하나이다.
- 같은 값을 가진 레코드들이 100% 정렬된 상태로 모여 있어 랜덤 액세스가 아닌 시퀀셜 방식으로 데이터를 액세스 한다.(BETWEEN, 부등호 조건으로 넓은 범위를 읽을 때 유리)

1.7 클러스터 테이블

😀 인덱스 클러스터 테이블
: 클러스터 키 값이 같은 레코드를 한 블록에 모아서 저장하는 구조
: 한 블록에 모두 담을 수 없을 때는 새로운 블록을 할당해서 클러스터 체인으로 연결한다.
: 여러 테이블 레코드를 같은 블록에 저장할 수 있으며 이를 '다중 테이블 클러스터'라고 부른다.

인덱스 클러스터 테이블을 구성하려면 아래와 같이 클러스터를 생성해야 한다.

-- 클러스터 생성
create cluster c_dept# (deptno number(2)) index ;

-- 클러스터 인덱스 정의
create index c_dept#_idx on cluster c_dept#;

-- 클러스터 테이블 생성
create table dept(
   deptno number(2) not null
   , dname varchar2(14) not null
   , loc varchar2(13) )
   cluster c_dept#(deptno);

- 클러스터 인덱스는 데이터 검색 용도로 사용할 뿐만 아니라 데이터가 저장될 위치를 찾을 때도 사용하기 때문에 클러스터에 테이블을 담기 전에 클러스터 인덱스를 반드시 정의해야 한다.

- 일반 테이블에 생성한 인덱스 레코드는 테이블 레코드와 1:1 대응 관계를 갖지만, 클러스터 인덱스는 테이블 레코드와 1:M 관계를 갖는다. ➡️클러스터 인덱스의 키값은 항상 unique 함(=중복 X)

- 클러스터 인덱스를 스캔하면서 값을 찾을 때는 랜덤 액세스가 값 하나당 한 번씩 밖에 발생하지 않으며 (클러스터 체인을 스캔하면서 발생하는 랜덤 액세스 제외) 클러스터에 도달해서는 시퀀셜 방식으로 스캔하므로 넓은 범위를 읽어도 비효율이 없다.

😀 해시 클러스터 테이블
- 해시 클러스터는 인덱스를 사용하지 않고 해시 알고리즘을 사용해 클러스터를 찾아간다는 점만 다르다.

해시 클러스터 구성 방법

-- 클러스터 생성
create cluster c_dept# (deptno number(2)) hashkeys 4;

-- 클러스터 테이블 생성
create table dept(
  deptno number(2) not null
 , dname varchar2(14) not null
 , loc varchar2(13))
 cluster c_dept#(deptno);

2. 부분범위 처리 활용

2.1 부분 범위 처리

- 전체 쿼리 결과집합을 쉼 없이 연속적으로 전송하지 않고 클라이언트로부터 Fetch Call이 있을 때마다 일정량씩 나누어 전송하는 것(전송하지 않은 데이터가 있어도 추가 Fetch Call 요청을 받기 전까지 그대로 멈춰서 기다린다.)

/**
1. 최초 rs.next() 호출 시 Fetch Call을 통해 DB 서버로부터 전송받은 데이터 10건을 클라이언트 캐시에 저장
2. 이후 rs.next() 호출 시 캐시에서 데이터를 다 읽을 때 까지 Fetch Call을 하지 않음
3. 캐시의 데이터를 모두 소진하면 rs.next() 호출 시 추가 Fetcth Call을 통해 10건을 전송받음
4. 100건을 다 읽을 때까지 2~3번 과정을 반복
5. 100개 레코드를 전송받은 후 ResultSet과 Statement 객체를 닫았으므로
   테이블에 데이터가 아무리 많더라도 오래걸리지 않는다.
**/
private void execute(Connection con) throws Exception {
	Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery("select name from big_table");
    
    for(int i=0; i<100; i++){
        if(rs.next()) System.out.println(rs.getString(1));
    }
    
    rs.close();
    stmt.close();
}

- 위의 Java 메서드 호출 시 big_table 이 1억건의 대용량 테이블이어도 오래걸리지 않고 실행 결과가 곧바로 화면에 출력된다.
- 이유는 DBMS가 데이터를 모두 읽어 한번에 전송하지 않고 먼저 읽는 데이터부터 일정량(Array Size)을 전송하고 멈추기 때문
- 데이터를 전송하고 나면 서버 프로세스는 CPU를 OS에 반환, 대기 큐에서 잠을 잔 후 다음 Fetch Call을 받으면 대기 큐에서 나와 그 다음 데이터를 일정량 읽어 전송 후 다시 잠을 잔다.

😊 쿼리에 정렬 조건이 있을 때 부분범위 처리가 가능할까?

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select name from big_table order by created");

- 쿼리문에 order by가 존재할 때 해당 컬럼이 인덱스에 포함되어있지 않다면 "전체범위처리"로 데이터 정렬을 마친 후 그때부터 일정량을 나눠 클라이언트에게 전송한다. 하지만 인덱스의 선두 컬럼이라면 소트연산이 생략되어 "부분범위처리"가 가능하다.

😊 Array Size 조정을 통한 Fetch Call 최소화
- Array Size를 조정함으로 써 전송해야 할 총량이 변하지는 않지만, Fetch Call 횟수를 줄일 수 있다.
- 반대로 앞쪽 일부 데이터만 Fetch 하다가 멈추는 프로그램이면 Array Size를 작게 설정하는 것이 유리하다.
JAVA 에서 Array Size 조정 예시

int arraysize = 10;
Statement stmt = con.createStatement();
stmt.setFetchSize(arraysize);

😣 멈출 수 있어야 의미있는 부분범위 처리
- 부분 범위 처리의 핵심은 '데이터를 앞쪽 일부만 출력하고 멈출 수 있는가' 이다.
- 토드나 오렌지 같은 쿼리 툴은 이미 그렇게 구현이 되어으며 이들처럼 클라이언트 프로그램이 DB 서버에 직접 접속하는 2-Tier 환경에서는 그렇게 구현할 수 있으며 실제로도 그렇게 많이 구현되어있다.
- 클라이언트와 DB 서버 사이에 WAS, AP 서버 등이 존재하는 n-Tier 아키텍처에서는 클라이언트가 특정 DB 커넥션을 독점할 수 없다. 단위 작업을 마치면 DB 커넥션을 곧바로 커넥션 풀에 반환해야 하므로 그 전에 SQL 조회 결과를 클라이언트에게 모두 전송하고 커서(Cursor)를 닫아야 하므로 부분 범위 처리가 어렵다. ➡️ Top N 쿼리를 활용하면 가능

😎 배치 I/O
- 읽는 블록마다 건건이 I/O를 발생시키는 비효율을 줄이기 위해 고안한 기능으로 인덱스를 이용해 테이블을 엑세스하다가 버퍼 캐시에서 블록을 찾지 못하면 일반적으로 디스크 블록을 바로 읽지만 배치 I/O 기능이 작동하면 테이블 블록에 대한 디스크 I/O를 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리한다.
- 인덱스 ROWID 를 이용한 랜덤 액세스의 고비용 문제를 해결하기 위한 방법
- 11g에서는 NL 조인 Inner 쪽 테이블 액세스 시에만 이 기능이 작동했지만, 12c 부터는 인덱스 ROWID로 테이블을 액세스하는 어떤 부분에서든 이 기능이 작동할 수 있다.
- 관련 힌트: batch_table_access_by_rowid

create index emp_x01 on emp(deptno, job, empno);

select /*+ batch_table_access_by_rowid(e) */ *
from emp e
where deptno = 20
order by job empno;

/** 실행계획 */
--------------------------------------------------------------------------------------
| ID  | Operation                               | Name     | Rows   | Bytes | Cost   |  
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |          |      5 |   190 |      2 |
|   1 |   SORT ORDER BY                         |          |      5 |   190 |      2 |
|   2 |     TABLE ACCESS BY INDEX ROWID BATCHED | EMP      |      5 |   190 |      2 |
|   3 |       INDEX RANGE SCAN                  | EMP_X01  |      5 |   190 |      2 |
--------------------------------------------------------------------------------------

- 배치 I/O 작동 시 데이터 정렬 순서가 매번 다를 수 있으며 order by 정렬 연산 시 인덱스로 소트 연산을 생략할 수 없다.
- 인덱스의 order by를 생략한 SQL 패턴을 사용하기 위해 배치 I/O를 비활성화 하기도 한다. (가능한 ORDER BY 는 명시해주는 것이 좋음)
- 배치 I/O 비활성화 방법(2가지)
1. _optimizer_batch_table_access_by_rowid 파라미터를 false 로 설정
2. no_batch_table_access_by_rowid 힌트 사용