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

테이블 액세스 초기화

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

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

메인 메모리 DB와 비교

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

I/O 메커니즘

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

 

버퍼 캐시(Buffer Cache)

최근 사용된 디스크 블록을 메모리에 캐시하는 것

 

 

인덱스 클러스터링 팩터

버퍼 Pinning

오라클은 래치 획득과 해시 체인 스캔 과정을 거쳐 어렵게 찾아간 테이블 블록에 대한 포인터(메모리 주소값)를 바로 해제하지 않고 일단 유지하는데 이를 '버퍼 Pinning'이라고 부름

 

인덱스 손익분기점

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

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

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

 

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

 

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

 

온라인 프로그램 튜닝 VS 배치 프로그램 튜닝

온라인 프로그램 튜닝

• 보통 소량 데이터를 읽고 갱신한다.
• 인덱스 사용과 NL 조인이 유리하다.
• 인덱스를 이용해 소트 연산을 생략함으로써 부분범위 처리 방식으로 구현할 수 있다면, 온라인 환경에서 대량 데이터를 조회할 때도 아주 빠른 응답 속도를 낼 수 있다.

배치 프로그램 튜닝

• 항상 전체범위 처리 기준으로 튜닝해야 한다.
• Full Scan과 해시 조인이 유리하다.
• 초대용량 테이블을 Full Scan하면 상당히 오래 걸린다.
  • 배치 프로그램에선 파티션 활용 전략이 매우 중요한 튜닝 요소이고, 병렬 처리까지 더할 수 있으면 금상첨화다.
  • 성능 측면에서만 보면 테이블을 파티셔닝하는 이유는 결국 Full Scan을 빠르게 처리하기 위해서이다.

 

인덱스 컬럼 추가

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

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

 

인덱스만 읽고 처리

Covered 쿼리

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

Include 인덱스

• 특정 경우에 다중컬럼 인덱스를 대체할 만한 설정으로 사용
• 인덱스 스캔이나 정렬 등에는 활용할 수 없지만, 컬럼 검색 시에 해당 인덱스 블록에서 바로 값을 꺼내와서 랜덤액세스를 약간 줄일 수 있다는 장점이 있음. (유일한 장점)

 

인덱스 구조 테이블

 

IOT (Index-Organized Table)

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

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

 

인덱스 클러스터 테이블

• 클러스터 키 값이 같은 레코드를 한 블록에 모아서 저장하는 구조이다.
• 한 블록에 모두 담을 수 없을 때는 새로운 블록을 할당해서 클러스터 체인으로 연결한다.
• 여러 테이블 레코드를 같은 블록에 저장할 수 도 있다.(다중 테이블 클러스터)
• 클러스터 인덱스도 일반 B*Tree 인덱스 구조를 사용하지만, 테이블 레코드를 일일이 가리키지 않고 해당 키 값을 저장하는        첫 번째 데이터 블록을 가리킨다는 점이 다르다.
• 클러스터 인덱스는 테이블 레코드와 1:M 관계를 갖는다.(항상 Unique)
• 이런 구조적 특성 떄문에 클러스터 인덱스를 스캔하면서 값을 찾을 때는 랜덤 액세스가 값 하나당 한 번씩밖에 발생하지 않는다.
• 클러스터에 도달해서는 시퀀셜 방식으로 스캔하기 때문에 넓은 범위를 읽더라도 비효율이 없다는 게 핵심 원리이다.

 

부분 범위 처리

 

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

 

 

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

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

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

배치 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-List Iterator 방식으로 푸는 것이 항상 효과적인 것은 아니다.

 

BETWEEN과 LIKE 스캔 범위 비교

LIKE보다 BETWEEN을 사용하는 게 낫다.

 

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

• OR 조건 활용
  • 인덱스 액세스 조건으로 사용 불가
  • 인덱스 필터 조건으로도 사용 불가
  • 테이블 필터 조건으로만 사용 가능
  • 단, 인덱스 구성 컬럼 중 하나 이상이 Not Null 컬럼이면, 18c부터 인덱스 필터 조건으로 사용 가능
  • 가급적 사용하지 않아야 한다.
  • 유일한 장점은 옵션 조건 컬럼이 Null 허용 컬럼이더라도 결과집합을 보장한다는 것뿐이다.
• LIKE/BETWEEN 조건 활용(아래 네 가지 경우에 속하는지 반드시 점검)
  
  • 인덱스 선두 컬럼
  • NULL 허용 컬럼
  • 숫자형 컬럼
  • 가변 길이 컬럼
• UNION ALL 활용
  • 옵션 조건 컬럼도 인덱스 액세스 조건으로 사용한다.
  • 유일한 단점은 SQL 코딩량이 길어진다는 점이다.
• NVL/DECODE 함수 활용
  • 큰 장점은 옵션 조건 컬럼을 인덱스 액세스 조건으로 사용할 수 있다는 데 있다. 즉 UNION ALL보다 단순하면서도 UNION ALL과 같은 성능을 낸다.
  • 단점은 NULL 허용 컬럼에 사용할 수 없다.

 

인덱스 설계

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

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

 

• I/O를 최소화하면서도 소트 연산을 생략하기 위한 인덱스를 구성하는 공식
  • '=' 연산자로 사용한 조건절 컬럼 선정
  • ORDER BY 절에 기술한 컬럼 추가
  • '=' 연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정

결합 인덱스 선택도

• 선택도
  • 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율
  • 선택도에 총 레코드 수를 곱해서 '카디널리티'를 구한다.
• 인덱스 선택도
  • 인덱스 컬럼을 모두 '=' 로 조회할 떄 평균적으로 선택되는 비율
  • 선택도가 높은 인덱스는 생성해봐야 테이블 액세스가 많이 발생하기 때문에 효용가치가 별로 없다.

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

 

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

 

중복 인덱스 제거

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