[친절한 SQL 튜닝] 인덱스 튜닝

테이블 액세스 최소화

테이블 랜덤 액세스

인덱스 ROWID는 논리적 주소이다.

• 디스크 상에서 테이블 레코드를 찾아가기 위한 위치 정보를 담는다.
• 포인터가 아니다.

메인 메모리 DB와 비교

• 오라클은 테이블 블록이 수시로 버퍼캐시에서 밀려났다가 다시 캐싱되며, 그때마다 다른 공간에 캐싱되기 때문에 인덱스에서 포인터로 직접 연곃할 수 없는 구조다.
• 포인터가 아닌 디스크 주소 정보(DBA)를 이용해 해시 알고리즘으로 버퍼 블록을 찾아간다.

I/O 메커니즘 복습

• 블록을 읽을 때는 디스크로 가기 전에 버퍼캐시부터 찾아본다. 읽고자 하는 DBA를 해시 함수에 입력해서 해시 체인을 찾고 거기서 버퍼 헤더를 찾는다.
• 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록을 찾아간다.
• 테이블을 Full Scan 할 때는 익스텐트 맵을 통해 읽을 블록들의 DBA 정보를 얻는다.
• 인덱스로 테이블 블록을 액세스할 때는 리프 블록에서 읽은 ROWID를 분해해서 DBA 정보를 얻는다.
  • 테이블 레코드를 찾기 위해 매번 DBA 해싱과 래치 획득 과정을 반복해야 한다. 동시 액세스가 심할 때는 캐시버퍼 체인 래치와 버퍼 Lock에 대한 경합까지 발생한다.
  • 이처럼 인덱스 ROWID를 이용한 테이블 액세스는 생각보다 고비용 구조다.

인덱스 클러스터링 팩터

클러스터링 팩터 (CF)

• 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미
• 데이터량이 많으면 데이터가 뿔뿔이 흩어져 있어서 CF가 좋지 않다.

버퍼 Pinning

• 오라클은 래치 획득과 해시 체인 스캔 과정을 거쳐 어렵게 찾아간 테이블 블록에 대한 포인터를 유지하는데 이를 '버퍼 Pinning'이라고 부른다.

CF가 좋은 컬럼에 생성한 인덱스는 테이블 액세스량에 비해 블록 I/O가 적게 발생하기 때문에 검색 효율이 매우 좋다.

인덱스 손익분기점

인덱스 ROWID를 이용한 테이블 액세스는 생각보다 고비용 구조다. 따라서 읽어야 할 데이터가 일정량을 넘는 순간, 테이블 전체를 스캔하는 것보다 오히려 느려진다.

인덱스 손익분기점

• Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점

인덱스를 이용한 테이블 액세스가 Table Full Scan보다 더 느려지게 만드는 가장 핵심적인 요인

• Table Full Scan은 시퀀셜 액세스인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 랜덤 액세스 방식이다.
• Table Full Scan은 Multiblock I/O인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 Single Block I/O 방식이다.

CF가 나쁘면 손익분기점은 5% 미만에서 결정되며, 심할 때는(BCHR이 매우 안 좋을 때) 1% 미만으로 낮아진다.

반대로 CF가 아주 좋을 때(인위적으로 전체 데이터를 인덱스 컬럼 순으로 정렬해서 재입력했을 때)는 손익분기점이 90% 수준까지 올라가기도 한다.

인덱스 손익분기점과 버퍼캐시 히트율

• 버퍼캐시는 요즘 기준으로 보통 수백만개 블록을 캐싱하는 수준이다. 따라서 특정 인덱스로 100만 건 이상 액세스한다면 캐시 히트율은 극히 낮을 수 밖에 없다.
• 1000만 건 정도 테이블이면 인덱스 컬럼 기준으로 값이 같은 테이블 레코드가 근처에 모여 있을 가능성이 매우 작다.
  • 인덱스를 스캔하면서 읽은 테이블 블록을 뒤에서 다시 읽을 가능성이 작기 때문에 거의 모든 데이터를 디스크에서 읽게 된다.
  • 이런 상황이면 손익분기점 자체가 의미 없어진다.
• 만 건만 넘어도 Table Full Scan 방식으로 읽는 게 빠를 수 있다.

온라인 프로그램 튜닝 vs 배치 프로그램 튜닝 (중요)

• 온라인 프로그램
  • 보통 소량 데이터를 읽고 갱신한다.
  • 인덱스 사용과 NL 조인이 유리하다.
  • 인덱스를 이용해 소트 연산을 생략함으로써 부분범위 처리 방식으로 구현할 수 있다면, 대량 데이터르 조회도 빠르게 할 수 있다.
• 배치 프로그램
  • 항상 전체범위 처리 기준으로 튜닝해야 한다.
  • Full Scan과 해시 조인이 유리하다.
  • 초대용량 테이블을 Full Scan하면 상당히 오래 걸린다.
    • 배치 프로그램에서는 파티션 활용 전략이 매우 중요한 튜닝 요소이고, 병렬 처리까지 더할 수 있으면 금상첨화다.
    • 성능 측면에서만 보면 테이블을 파티셔닝하는 이유는 결국 Full Scan을 빠르게 처리하기 위해서다.

모든 성능 문제를 인덱스로 해결하려 해선 안 된다. 인덱스는 다양한 튜닝 도구 중 하나일 뿐이며, 큰 테이블에서 아주 적은 일부 데이터를 빨리 찾고자 할 떄 주로 사용한다.

인덱스 컬럼 추가

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

인덱스 스캔량은 줄지 않지만, 테이블 랜덤 액세스 횟수를 줄여준다.

인덱스만 읽고 처리

Covered 쿼리

• 쿼리에 사용된 컬럼을 모두 인덱스에 추가해서 테이블 액세스가 아예 발생하지 않게 했을 때, 인덱스만 읽어서 처리하는 쿼리
• 효과는 매우 좋지만, 추가해야 할 컬럼이 많아 실제 적용하기 곤란한 경우도 많다.

Include 인덱스

인덱스 구조 테이블

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

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

IOT (Index-Organized Table)

• 랜덤 액세스가 아예 발생하지 않도록 테이블을 인덱스 구조로 생성한 것
• 테이블을 찾아가기 위한 ROWID를 갖는 일반 인덱스와 달리 IOT는 그 자리에 테이블 데이터를 갖는다.
• 즉, 테이블 블록에 있어야 할 데이터를 인덱스 리프 블록에 모두 저장하고 있다.
• 일반 테이블은 '힙 구조 테이블'로 순서 없이 데이터를 입력하지만, IOT는 정렬 상태를 유지하며 입력한다.

IOT는 인위적으로 CF를 좋게 만드는 방법 중 하나다. 같은 값을 가진 레코드들이 100% 정렬된 상태로 모여 있으므로 시퀀셜 방식으로 데이터를 액세스한다. 이 때문에 BETWEEN이나 부등호 조건으로 넓은 범위를 읽을 때 유리하다.

클러스터 테이블

클러스터 테이블에는 인덱스 클러스터와 해시 클러스터 두 가지가 있다.

인덱스 클러스터 테이블

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

인덱스 클러스터 테이블 구성

-- 먼저 아래와 같이 클러스터를 생성한다.
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(4)  not null
, loc    varchar2(13) )
cluster c_dept#( deptno );

클러스터 인덱스도 일반 B*Tree 인덱스 구조를 사용하지만, 테이블 레코드를 일일이 가리키지 않고 해당 키 값을 저장하는 첫 번째 데이터 블록을 가리킨다는 점이 다르다.

클러스터 인덱스는 테이블 레코드와 1:M 관계를 갖는다.

이런 구조적 특성 떄문에 클러스터 인덱스를 스캔하면서 값을 찾을 때는 랜덤 액세스가 값 하나당 한 번씩 밖에 발생하지 않는다.

클러스터에 도달해서는 시퀀셜 방식으로 스캔하기 때문에 넓은 범위를 읽더라도 비효율이 없다는 게 핵심 원리다.

해시 클러스터 테이블

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

해시 클러스터 테이블 구성

-- 먼저 클러스터를 생성하고 위에서 했던 것처럼 클러스터 테이블을 생성한다.
create cluster c_dept# ( deptno number(2) ) hashkeys 4 ;

부분범위 처리 활용

부분범위 처리

전체 쿼리 결과집합을 쉼 없이 연속적으로 전송하지 안혹 사용자로부터 Fetch Call이 있을 때마다 일정량씩 나누어 전송하는 것이다.

정렬 조건이 있을 때 부분범위 처리

• DB 서버는 '모든' 데이터를 다 읽어 created 순으로 정렬을 마치고서야 클라이언트에게 데이터 전송을 시작할 수 있따.
• 다행히 created 컬럼이 선두인 인덱스가 있으면, 부분범위 처리가 간으하다.

Array Size 조정을 통한 Fetch Call 최소화

• 전송해야 할 데이터량에 따라 Array Size를 조절할 필요가 있다.

부분범위 처리 구현

출력 레코드 수가 Array Size에 도달하면 멈추었다가 사용자 요청이 있을 때 다시 데이터를 Fetch하도록 하는 코드를 직접 구현할 수도 있지만, 보통 개발 프레임워크에 미리 구현돼있는 기능을 활용한다.

OLTP 환경에서 부분범위 처리에 의한 성능개선 원리

OLTP성 업무에서 쿼리 결과 집합이 아주 많을 때 사용자는 특정한 정렬 순서로 상위 일부 데이터만 확인한다.

이럴 때, 항상 정렬 상태를 유지하는 인덱스를 이용하면, 정렬 작업을 생략하고 앞쪽 일부 데이터를 아주 빠르게 보여줄 수 있다.

멈출 수 있어야 의미있는 부분범위 처리

• 문제는 앞쪽 일부만 출력하고 멈출 수 있는가이다. 이것이 부분범위 처리의 핵심이다.
• 2-Tier 환경에서는 이렇게 구현할 수 있지만 n-Tier 아키텍처에서는 클라이언트가 특정 DB 커넥션을 독점할 수 없다.
• 단위 작업을 마치면 DB 커넥션을 곧바로 커넥션 풀에 반환해야 하므로 그 전에 SQL 조회 결과를 클라이언트에게 '모두' 전송하고 커서를 닫아야 한다.
• 자세한 내용은 5.3 참고

배치 I/O

• 읽는 블록마다 건건이 I/O Call을 발생시키는 비효율을 줄이기 위해 고안한 기능
• 인덱스를 이용해 테이블을 액세스하다가 버퍼 캐시에서 블록을 찾지 못하면 테이블 블록에 대한 디스크 I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리한다.

인덱스 스캔 효율화

인덱스 탐색

수직적 탐색은 스캔 시작점을 찾는 과정이라는 걸 잊지 말자.

각 조건절이 제 역할을 하는(쓸모있는) 조건절인지 확인하자.

인덱스 스캔 효율성

인덱스 선행 컬럼이 조건절에 없거나 '=' 조건이 아니면 인덱스 스캔 과정에 비효율이 발생한다.

액세스 조건과 필터 조건

인덱스 액세스 조건

• 인덱스 스캔 범위를 결정하는 조건절
• 인덱스 수직적 탐색을 통해 스캔 시작점을 결정하는 데 영향을 미치고, 인덱스 리프 블록을 스캔하다가 어디서 멈출지를 결정하는 데 영향을 미치는 조건절

인덱스 필터 조건

• 테이블로 액세스할지를 결정하는 조건절
• 인덱스를 이용하든, 테이블을 Full Scan 하든, 테이블 액세스 단계에서 처리되는 조건절은 모두 필터 조건이다.

테이블 필터 조건

• 쿼리 수행 다음 단계로 전달하거나 최종 결과집합에 포함할지를 결정한다.

비교 연산자 종류와 컬럼 순서에 따른 군집성

테이블과 달리 인덱스에는 '같은 값'을 갖는 레코드들이 서로 군집해 있다.

인덱스 컬럼 중 어느 하나를 누락하거나 '=' 조건이 아닌 연산자로 조회하면 조건절을 만족하는 레코드가 서로 흩어진 상태가 된다.

첫 번째 나타나는 범위검색 조건까지가 인덱스 액세스 조건이고, 나머지는 필터 조건이다.

• 아래의 케이스를 제외하면, 인덱스 컬럼에 대한 조건절은 모두 액세스 조건에 표시된다.
  • 좌변 컬럼을 가공한 조건절
  • 왼쪽 '%' 또는 양쪽 '%' 기호를 사용한 like 조건절
  • 같은 컬럼에 대한 조건절이 두 개 이상일 때, 인덱스 액세스 조건으로 선택되지 못한 조건절
  • OR Expansion 또는 INLIST ITERATOR로 선택되지 못한 OR 또는 IN 조건절
• 헷갈리지 말자 !

인덱스 선행 컬럼이 등치(=) 조건이 아닐 때 생기는 비효율

인덱스 선행 컬럼이 조건절에 없거나 부등호, BETWEEN, LIKE 같은 범위검색 조건이면, 인덱스를 스캔하는 단계에서 비효율이 생긴다.

BETWEEN을 IN-List로 전환

BETWEEN 조건을 IN-List 조건으로 전환할 때 주의할 점

• IN-List 개수가 많지 않아야 한다.
  • 개수가 많으면 수직적 탐색이 많이 발생한다.
• 인덱스 스캔 과정에 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유용하다.

Index Skip Scan 활용

선두 컬럼이 BETWEEN이어서 나머지 검색 조건을 만족하는 데이터들이 서로 멀리 떨어져 있을 때, Index Skip Scan의 위력이 나타난다.

IN 조건은 '='이 아니다

따라서 인덱스를 어떻게 구성하느냐에 따라 성능도 달라질 수 있다.

IN 조건이 '='이 되려면 IN-List Iterator 방식으로 풀려야만 한다. 그렇지 않으면, IN 조건은 필터 조건이다.

IN 조건을 '=' 조건으로 만들기 위해, 즉 액세스 조건으로 만들기 위해 IN-List Iterator 방식으로 푸는 것이 항상 효과적인 것은 아니다.

NUM_INDEX_KEYS 힌트 활용

• /*+ num_index_keys(a 고객별가입상품_X1 1) */는 세 번째 인자 값의 인덱스 컬럼까지만 액세스 조건으로 사용하라는 의미다.

BETWEEN과 LIKE 스캔 범위 비교

LIKE보다 BETWEEN을 사용하는 게 낫다. BETWEEN을 사용하면 적어도 손해는 안 본다.

범위검색 조건을 남용할 때 생기는 비효율

코딩을 쉽게 하려고 인덱스 컬럼에 범위검색 조건을 남용하면 인덱스 스캔 비효율이 생긴다.

SQL 작성할 때 주의해야 하며, 데이터 분포에 따라 인덱스 컬럼에 대한 비교 연산자를 신중하게 선택해야 한다.

다양한 옵션 조건 처리 방식의 장단점 비교

OR 조건 활용

• OR 조건을 활용한 옵션 조건 처리 정리
  • 인덱스 액세스 조건으로 사용 불가
  • 인덱스 필터 조건으로도 사용 불가
  • 테이블 필터 조건으로만 사용 가능
  • 단, 인덱스 구성 컬럼 중 하나 이상이 Not Null 컬럼이면, 18c부터 인덱스 필터 조건으로 사용 가능
• 가급적 사용하지 않아야 한다.
• 유일한 장점은 옵션 조건 컬럼이 NULL 허용 컬럼이더라도 결과집합을 보장한다는 것뿐이다.

LIKE/BETWEEN 조건 활용

• 이 패턴을 사용하고자 할 때는 아래 네 가지 경우에 속하는지 반드시 점검해야 한다. (BETWEEN 조건은 1, 2번 조건만)
  1. 인덱스 선두 컬럼
  2. NULL 허용 컬럼
  3. 숫자형 컬럼
  4. 가변 길이 컬럼

UNION ALL 활용

• 옵션 조건 컬럼도 인덱스 액세스 조건으로 사용한다.
• 유일한 단점은 SQL 코딩량이 길어진다는 점이다.

NVL/DECODE 함수 활용

• 큰 장점은 옵션 조건 컬럼을 인덱스 액세스 조건으로 사용할 수 있다는 데 있다. 즉, UNION ALL보다 단순하면서도 UNION ALL과 같은 성능을 낸다.
• 단점은, LIKE 패턴처럼 NULL 허용 컬럼에 사용할 수 없다.
• 이 함수를 여러 개 사용하면 그중 변별력이 가장 좋은 컬럼 기준으로 한 번만 OR Expansion이 일어난다.
  • 따라서 OR Expansion 기준으로 선택되지 않으면 인덱스 구성 컬럼이어도 모두 필터 조건으로 처리된다.

다른 성능 관련 SQL 패턴은 애플리케이션 특성에 맞게 어느 하나의 솔루션을 선택해서 개발팀에 권고안을 제시할 수 있지만, 옵션 조건 처리는 그럴 수 없기 때문에 어렵다.

지금까지 설명한 여러 방식의 장단점을 이해함으로써 상황에 따라 선택할 수밖에 없다.

함수호출부하 해소를 위한 인덱스 구성

PL/SQL 함수의 성능적 특성

• PL/SQL 사용자 정의 함수는 매우 느리다.
• 느린 이유
  • 가상머신 상에서 실행되는 인터프리터 언어
  • 호출 시마다 컨텍스트 스위칭 발생
  • 내장 SQL에 대한 Recursive Call 발생

효과적인 인덱스 구성을 통해 함수호출 최소화하자.

인덱스 설계

인덱스 설계에 필요한 여러 가지 판단 기준, 공식을 초월한 전략적 설계의 필요성, 방법론 등을 알아보자.

인덱스 설계가 어려운 이유

인덱스가 많으면 생기는 문제

• DML 성능 저하 (TPS 저하)
• 데이터베이스 사이즈 증가 (디스크 공간 낭비)
• 데이터베이스 관리 및 운영 비용 상승

개별 쿼리 성능뿐만 아니라 그 개수를 최소화함으로써 DML 부하를 줄여야 하므로 인덱스 설계가 어렵다. OLTP 환경에서 특히 그렇다.

가장 중요한 두 가지 선택 기준

1. 조건절에 항상 사용하거나, 자주 사용하는 컬럼을 선정한다.
2. 그렇게 선정한 컬럼 중 '=' 조건으로 자주 조회하는 컬럼을 앞쪽에 둔다.

스캔 효율성 이외의 판단 기준

인덱스 스캔 효율성 외의 고려해야 할 판단 기준들

• 수행 빈도
• 업무상 중요도
• 클러스터링 팩터
• 데이터량
• DML 부하 (= 기존 인덱스 개수, 초당 DML 발생량, 자주 갱신하는 컬럼 포함 여부 등)
• 저장 공간
• 인덱스 관리 비용 등

이 중 가장 중요한 하나를 꼽자면 그것은 수행 빈도다.

수행빈도가 매우 높은 SQL이라면, 테스트 과정에 당장 성능이 좋게 나오더라도 인덱스를 최적으로 구성해 줘야 한다. NL 조인 Inner 쪽 인덱스는 '=' 조건 컬럼을 선두에 두는 것이 중요하고, 될 수 있으면 테이블 액세스 없이 인덱스에서 필터링을 마치도록 구성해야 한다.

데이터량도 인덱스를 설계할 때 중요한 판단 기준이다.

공식을 초월한 전략적 설계

SQL 튜닝 전문가라면, 열 개 중 최적을 달성해야 할 가장 핵심적인 액세스 경로 한두 개를 전략적으로 선택해서 최적 인덱스를 설계하고, 나머지 액세스 경로는 약간의 비효율이 있더라도 목표 성능을 만족하는 수준으로 인덱스를 구성할 수 있어야 한다. 그리고 왜 그런 선택을 했는지, 전략적 판단 근거가 무엇인지 답할 수 있어야 한다.

인덱스 개수를 최소화하면, 사용빈도가 높거나 중요한 액세스 경로가 새로 도출됐을 때 최적의 인덱스를 추가할 여유도 생긴다.

소트 연산을 생략하기 위한 컬럼 추가

조건절에 사용하지 않는 컬럼이더라도 소트 연산을 생략할 목적으로 인덱스 구성에 포함시킴으로써 성능 개선을 도모할 수 있다.

I/O를 최소화하면서도 소트 연산을 생략하기 위한 인덱스를 구성하는 공식

1. '=' 연산자로 사용한 조건절 컬럼 선정
2. ORDER BY 절에 기술한 컬럼 추가
3. '=' 연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정
   • 이들 조건을 만족하는 데이터가 적으면, 인덱스에 추가하여 테이블 랜덤 액세스를 줄이면 좋다.

IN 조건은 '='이 아니다

• 소트 연산을 생략하려면 IN 조건절이 IN-List Iterator 방식으로 풀려선 안 된다.
• 즉, IN 조건절을 인덱스 액세스 조건으로 사용하면 안 되고 필터 조건으로 사용해야 한다.

결합 인덱스 선택도

인덱스 생성 여부를 결정할 때는 선택도가 충분히 낮은지가 중요한 판단 기준이다.

선택도

• 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율
• 선택도에 총 레코드 수를 곱해서 '카디널리티'를 구한다.

인덱스 선택도

• 인덱스 컬럼을 모두 '='로 조회할 때 평균적으로 선택되는 비율
• 선택도가 높은 인덱스는 생성해봐야 테이블 액세스가 많이 발생하기 때문에 효용가치가 별로 없다.

인덱스를 생성할 때는 반드시 선택도/카디널리티를 확인해야 한다.

컬럼 순서 결정 시, 선택도 이슈

• 컬럼 간 순서를 결정할 때는 각 컬럼의 선택도보다 필수 조건 여부, 연산자 형태가 더 중요한 판단 기준이다.
• 어느 컬럼을 앞에 두는 것이 유리한지는 상황에 따라 판단할 일이다.

중복 인덱스 제거

조건절을 분석하거나 조건절이 없더라도 NDV를 확인하여 중복 인덱스를 찾아 재설계 하자.

인덱스 설계도 작성

인덱스 설계 시 시스템 전체 효율을 고려해야 한다. 인덱스 설계에도 전체를 조망할 수 있는 설계도면이 필요하다.

인덱스 설계 전에 파티션 설계를 먼저 진행하거나 최소한 병행해야 제대로 된 인덱스 전략을 수립할 수 있다.