Database/SQL 튜닝

인덱스 튜닝

Debin 2023. 8. 23.

1. 테이블 액세스 최소화

테이블 랜덤 액세스

인덱스 ROWID는 물리적 주소? 논리적 주소?

인덱스를 스캔하는 이유는, 검색 조건을 만족하는 소량의 데이터를 인덱스에서 빨리 찾고 거기서 테이블 레코드를 찾아가기 위한 주소 값, 즉 ROWID를 얻으려는 데 있다.
인덱스 ROWID는 물리적 주소보다 논리적 주소에 가깝다.
물리적으로 직접 연결되지 않고 테이블 레코드를 찾아가기 위한 논리적 주소 정보를 담고 있기 때문이다.
ROWID는 프로그래밍에서 말하는 포인터가 아니며, 테이블 레코드와 물리적으로 직접 연결된 구조는 더더욱 아니다.
오라클 같은 경우는 테이블 블록이 수시로 버퍼캐시에서 밀려났다가 다시 캐싱되며, 그때마다 다른 공간에 캐싱되기 때문에 인덱스에서 포인터로 직접 연결할 수 없는 구조다.
메모리 주소 정보가 아닌 디스크 정보를 이용해 해시 알고리즘으로 버퍼 블록을 찾아간다.

I/O 메커니즘

DBA(데이터파일번호 + 블록번호)는 디스크 상에서 블록을 찾기 위한 주소 정보다.
매번 디스크에서 블록을 읽을 수는 없으며, I/O 성능을 높이려면 버퍼 캐시를 활용해야 한다.
블록을 읽을 때는 디스크로 가기 전에 버퍼 캐시부터 찾아본다.
읽고자 하는 DBA를 해싱 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록을 찾아간다.

정리하면 ROWID가 가리키는 테이블 블록을 버퍼캐시에서 먼저 찾아보고, 못 찾을 때만 디스크에서 블록을 읽는다.

모든 데이터가 캐싱돼 잇더라도 테이블 레코드를 찾기 위해 매번 DBA 해싱과 래치 획득 과정을 반복해야 한다.

동시 액세스가 심할때는 캐시버퍼 체인 래치와 버퍼 Lock에 대한 경합까지 발생한다.

인덱스 ROWID를 이용한 테이블 액세스는 생각보다 고비용 구조다.

인덱스 클러스터링 팩터

클러스터링 팩터는 특정 컬럼을 기준으로 같은 값을 갖는 테이터가 서로 모여있는 정도를 의미한다.
CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋다.

인덱스 손익분기점

Index Range Scan에 의한 테이블 액세스가 Full Table Full Scan보다 느려지는 지점을 흔히 '인덱스 손익분기점'이라고 부른다.
인덱스를 이용해 테이블을 액세스할 때는 전체 1000만 건 중 몇 건을 추출하느냐에 따라 성능이 크게 달라진다.
추출 건수가 많을수록 느려진다.

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

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

인덱스 손익분기점은 보통 5~20%의 낮은 수준에서 결정된다.

인덱스 CF가 나쁘면 같은 테이블 블록을 여러번 반복 액세스하면서 논리적 I/O 횟수가 늘고, 물리적 I/O 횟수도 늘기 때문이다.

CF가 나쁘면 손익분기점은 5% 미만에서 결정되며, 심할 때는 1% 미만으로 낮아진다.

반대로 CF가 아주 좋을 때는 손익 분기점이 90% 수준까지 올라가기도 한다.

이 개념이 시사하는 바는 크다.

온라인 트랜잭션을 처리하는 프로그램과 배치 프로그램 튜닝의 특징을 구분 짓는 핵심 개념이기 때문이다.

온라인 트랜잭션 처리 프로그램: NL 조인 방식 사용, 인덱스 활용
배치프로그램: Full Scan과 해시 조인 사용, 파티션 활용 전략 중요, 병렬 처리

인덱스 칼럼 추가

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

인덱스만 읽고 처리

테이블 랜덤 액세스가 아무리 많아도 필터 조건에 의해 버려지는 레코드가 거의 없다면 거기에 비효율은 없다.
그럼 이때는 어떻게 튜닝해야할까?
반드시 성능을 개선해야 한다면, 쿼리에 사용된 컬럼을 모두 인덱스에 추가해서 테이블 액세스가 아예 발생하지 않게 하는 방법을 고려해볼 수 있다.
인덱스만 읽어서 처리하는 쿼리를 'Covered 쿼리'라고 부르며, 그 쿼리에 사용한 인덱스를 'Covered 인덱스'라고 부른다.
include 인덱스라는 것도 있는데, 이는 인덱스 키 외에 미리 지정한 컬럼을 리프 레벨에 함께 저장하는 기능이다.
include 인덱스는 오직 테이블 랜덤 액세스를 줄이는 용도로 개발됐다.

인덱스 구조 테이블

랜덤 액세스가 아예 발생하지 않도록 테이블을 인덱스 구조로 생성할 수 있는데, 오라클에서는 이를 IOT(Index-Organized Talbe)라고 부른다.
MS-SQL Server는 '클러스터형 인덱스'라고 부른다.
테이블을 찾아가기 위한 ROWID를 갖는 일반 인덱스와 달리 IOT는 그 자리에 테이블 데이터를 갖는다.
즉, 테이블 블록에 있어야 할 데이터를 인덱스 리프 블록에 모두 저장하고 있다.
IOT는 인위적으로 클러스터링 팩터를 좋게 만드는 방법 중 하나다.

클러스터 테이블

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

인덱스 클러스터 테이블

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

해시 클러스터 테이블

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

2. 부분범위 처리 활용

부분범위 처리

전체 쿼리 결과집합을 쉼 없이 연속적으로 전송하지 않고 사용자로부터 Fetch Call이 있을 때마다 일정량씩 나누어 전송하는 것을 이른바 '부분범위 처리'라고 한다.
데이터를 전송하는 단위인 Array Size는 클라이언트 프로그램에서 설정한다.
자바에서 Array Size 기본 값은 10이며, Statement 객체 setFetchSize 메서드를 통해 설정을 변경할 수 있다.
order by '컬럼' 에서 '컬럼'이 선두인 인덱스가 있으면, 부분범위 처리가 가능하다.
네트워크, 자원 낭비를 막기 위한 Array Size 조정을 통한 Fetch Call 최소화가 필수적이다.

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

OLTP성 업무에서 쿼리 결과 집합이 아주 많을 때 사용자가 모든 데이터를 일일이 다 확인하지는 않는다. 특정한 정렬 순서로 상위 일부 데이터만 확인한다.
그럴 때, 항상 정렬 상태를 유지하는 인덱스를 이용하면, 정렬 작업을 생략하고 앞쪽 일부 데이터를 아주 빠르게 보여줄 수 있다.
문제는 앞쪽 일부만 출력하고 멈출 수 있는가이다. 이것이 부분범위 처리의 핵심이다.
n-tier 아키텍처에서는 클라이언트가 특정 DB 커넥션을 톡점할 수 없다.
SQL 조회 결과를 클라이언트에게 '모두' 전송하고 커서를 닫아야 한다. 따라서 SQL 결과집합을 조금씩 나눠 전송하도록 구현하기 어렵다.

읽을수록 페이징 처리와 겹치는 부분이 많은 것 같은데.....

3. 인덱스 스캔 효율화

운영 환경에서 가능한 일반적인 튜닝 기법은 인덱스 컬럼 추가다.

인덱스 스캔 효율성

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

액세스 조건과 필터 조건

인덱스를 스캔하는 단계에 처리하는 조건절은 액세스 조건과 필터 조건으로 나뉜다.
인덱스 액세스 조건은 인덱스 스캔 범위를 결정하는 조건절이다.
인덱스 수직적 탐색을 통해 스캔 시작점을 결정하는 데 영향을 미치고, 인덱스 리프 블록을 스캔하다가 어디서 멈출지를 결정하는 데 영향을 미치는 조건절이다.
인덱스 필터 조건은 테이블로 액세스할지를 결정하는 조건절이다.

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

테이블 필터 조건은 쿼리 수행 다음 단계로 전달하거나 최종 결과집합에 포함할지를 결정한다.

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

테이블과 달리 인덱스에는 '같은 값'을 갖는 레코드들이 서로 군집해 있다.
'같은 값'을 찾을 때 '=' 연산자를 사용하므로 인덱스 컬럼을 앞쪽부터 누락 없이 '=' 연산자로 조회하면 조건절을 만족하는 레코드는 모두 모여 있다. 어느 하나를 누락하거나 '=' 조건이 아닌 연산자로 조회하면 조건절을 만족하는 레코드가 서로 흩어진 상태가 된다.
선행 컬럼이 모두 '=' 조건인 상태에서 첫 번째 나타나는 범위 검색 조건까지만 만족하는 인덱스 레코드는 모두 연속해서 모여 있지만, 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다.
가장 선두 컬럼이 범위 검색 조건이면, 그 조건이 스캔 범위를 결정한다. 이들 조건이 액세스 조건이며, 나머지 인덱스 컬럼은 조건은 모두 인덱스 필터 조건이다.

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

인덱스 스캔 효율성은 인덱스 컬럼을 조건절에 모두 등치 조건으로 사용할 때 가장 좋다.
리프 블록을 스캔하면서 읽은 레코드는 하나도 걸러지지 않고 모두 테이블 액세스로 이어지므로 인덱스 스캔 단계에서의 비효율은 전혀 없다.
인덱스 컬럼 중 일부가 조건절에 없거나 등치 조건이 아니더라도, 그것이 뒤쪽 컬럼일 때는 비효율이 없다.
반면 인덱스 선행 컬럼이 조건절에 없거나 부등호, BETWEEN, LIkE와 같은 범위 검색 조건이면, 인덱스를 스캔하는 단계에서 비효율이 생긴다.

BETWEEN을 IN-List로 전환

BETWEEN 조건을 IN-List로 바꿔주면 큰 효율을 얻을 수 있다.
IN-List 개수만큼 UNION ALL 브랜치가 생성되고 각 브랜치마다 모든 컬럼을 '=' 조건으로 검색하므로 앞서 선두 컬럼에 BETWEEN을 사용할 때와 같은 비효율이 사라진다.
IN-List 항목 개수가 늘어나면 사용이 곤란하다. 이때는 NL 방식의 조인문이나 서브쿼리로 구현해야 한다.
IN-List 조건으로 전화할 때 주의점은 IN-List 개수가 많지 않아야 한다는 것이다.
IN-List 개수가 많으면 수직적 탐색이 많이 발생한다.
그러면 리프 블록을 스캔하는 비효율보다 IN-List 개수만큼 브랜치 블록을 반복 탐색하는 비효율이 더 클 수 있다.

Index Skip Scan 활용

BETWEEN 조건을 IN-List 조건으로 변환하면 도움이 되는 상황에서 굳이 조건절을 바꾸지 않고도 같은 효과를 낼 방법이 있다.
바로 Index Skip Scan을 활용하는 것이다.
선두 컬럼이 BETWEEN이어서 나머지 검색 조건을 만족하는 데이터들이 서로 멀리 떨어져 있을 때, Index Skip Scan이 효율적이다.

IN 조건은 '=' 인가

IN 조건은 '='이 아니다.
따라서 인덱스를 어떻게 구성하느냐에 따라 성능도 달라질 수 있다.
조건절이 IN-List Iterator 방식으로 풀려야 IN 조건이 되는 것이다. (union all)
그러나 IN-List Iterator 방식으로 풀리면 In 조건은 '='이 아니다. 바로 필터 조건이다.
또한 항상 IN 조건을 '=' 조건으로 만들기 위해, 즉 액세스 조건으로 만들기 위해 IN-List Iterator 방식으로 푸는 것은 효과적이지 않다.

BETWEEN과 LIKE 스캔 범위 비교

LIKE와 BETWEEN은 둘 다 범위검색 조건으로서, 앞에서 설명한 범위검색 조건을 사용할 때의 비효율 원리가 똑같이 적용된다.
하지만 데이터 분포화 조건절 값에 따라 인덱스 스캔량이 서로 다를 수 있다.
결론은 LIKE보다 BETWEEN을 사용하는 게 낫다.

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

코딩을 쉽게 하려고 인덱스 칼럼에 범위 검색 조건(LIKE, BETWEEN)을 남용하면 인덱스 스캔 비효율이 생긴다.
SQL은 신중하게 작성해야 하며, 데이터 분포에 따라 인덱스 컬럼에 대한 비교 연산자를 신중하게 선택해야 한다.

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

OR 조건 활용

인덱스 액세스 조건으로 사용 불가
인덱스 필터 조건으로도 사용 불가
테이블 필터 조건으로만 사용 가능
단, 인덱스 구성 컬럼 중 하나 이상이 Not Null 컬럼이면, 18c부터 인덱스 필터 조건으로 사용가능

가급적 사용하지 말자.

LIKE/BETWEEN 활용

이 두 패턴을 사용할 때는 아래와 같은 경우를 반드시 점검해야 한다.

인덱스 선두 컬럼
NULL 허용 컬럼
- 성능을 떠나 결과 집합에 오류가 생기기 때문
숫자형 컬럼
- 자동 형변환이 일어날 수 있기 때문
가변 길이 컬럼
- 다양한 값이 조회될 수 있으므로 변수 값 길이에 대한 조건절을 추가해야 한다.

UNION ALL 활용

옵션 조건 컬럼도 인덱스 액세스 조건으로도 사용 가능.
단점은 코딩량이 길어진다.

NVL/DECOPDE 함수 활용

인덱스 칼럼을 가공해도 인덱스를 사용할 수 있는 것은 OR Expansion 쿼리 변환이 일어났기 때문이다.
옵티마이저가 UNION ALL 방식으로 쿼리를 변환한 것이다.
이 방식의 장점은 옵션 조건 컬럼을 인덱스 액세스 조건으로 사용할 수 있다는 것이다.
즉 UNION ALL보다 단순하면서도 UNION ALL과 같은 성능을 낸다.
단점은 LIKE 패턴처럼 NULL 허용 컬럼에 사용할 수 없다.
조건절 변수에 NULL을 입력하면 값이 NULL인 레코드가 결과집합에서 누락되기 때문이다.
옵션 조건 처리용 NVL/DECODE 함수를 여러 개 사용하면 그중 변별력이 가장 좋은 컬럼 기준으로 한 번만 OR EXPANSION이 일어난다.
따라서 OR Expansion 기준으로 선택되지 않으면 인덱스 구성 컬럼이어도 모두 필터 조건으로 처리된다.

4. 인덱스 설계

인덱스 설계가 어려운 이유

인덱스가 많으면 아래와 같은 문제가 생긴다.

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

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

결합 인덱스를 구성할 때 첫 번째 기준은, 조건절에 항상 사용하거나, 자주 사용하는 컬럼을 선정하는 것이다.
두 번째 기준은, 그렇게 선정한 칼럼 중 '=' 조건으로 자주 조회하는 컬럼을 앞쪽에 두어야 한다는 것이다.

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

스캔 효율성 외 고려할 판단 기준을 나열하면 다음과 같다.

수행 빈도 (제일 중요)
업무상 중요도
클러스터링 팩터
데이터량
DML 부하
저장공간
인덱스 관리 비용 등

공식을 초월한 전략적 설계

요약하면 업무 상황을 고려한 전략적 판단이 제일 중요하다.

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

I/O를 최소화하면서도 소트 연산을 생략하려면, 아래 공식에 따라 인덱스를 구성하면 된다.

'=' 연산자로 사용한 조건절 컬럼 선정
ORDER BY 절에 기술한 컬럼 추가
'=' 연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정

소트 연산을 생략하려면 IN 조건절이 IN-List Iterator 방식으로 풀려선 안된다.

즉, IN 조건절을 인덱스 액세스 조건으로 사용하면 안된다. 필터 조건으로 사용해야 한다.

결합 인덱스 선택도

인덱스 생성 여부를 결정할 때는 선택도가 충분히 낮은지가 중요한 판단기준이다.
'선택도'란, 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율을 말하며, 선택도에 총 레코드 수를 곱해서 '카디널리티'를 구한다.
'인덱스 선택도'는 인덱스 컬럼을 모두 '='로 조회할 때 평균적으로 선택되는 비율을 의미한다.
선택도가 높은(카디널리티가 높은) 인덱스는 생성해봐야 효용가치가 별로 없다. 테이블 액세스가 많이 발생하기 때문이다.
따라서 인덱스를 생성할 때는 반드시 선택도/카디널리티를 확인해야 한다.

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

결합 인덱스 컬럼 간 순서를 정할 대도 선택도가 중요할까?
예시로 고객번호를 앞에 두는 것이 유리하다고 생각하겠지만 그렇지 않다. 성별과 고객번호 중 어떤 컬럼이 앞으로 오든 인덱스 스캔 효율에 전혀 차이가 없다.
둘 다 인덱스 액세스 조건이므로 어떤 컬럼이 앞으로 오든 인덱스 스캔 범위는 똑같다.
인덱스 설계할 때 우리가 할 일은 '항상 사용하는' 컬럼을 앞쪽에 두고 그 중 '=' 조건을 앞쪽에 위치시키는 것 뿐이다.
예시
- 고객등급, 고객번호 둘 다 '=' 조건이면 상관없지만, 둘 중 하나가 조건절에서 누락되거나 범위 검색 조건인 경우는 얘기가 복잡해진다.
- 범위 검색 조건인 경우는 고객 등급을 앞에 두는 것이 유리하다. (인덱스 스킵 스캔, IN-List 조건 활용)

결론은 인덱스 생성 여부를 결정할 때는 선택도가 매우 중요하지만,

컬럼 간 순서를 결정할 때는 각 컬럼의 선택도보다 필수 조건 여부, 연산자 형태가 더 중요한 판단 기준이다.

어느 컬럼을 앞에 두는 것이 유리한지는 상황에 따라 판단할 일이다.