[SQL 튜닝] 3. 인덱스 튜닝

책 [친절한 SQL 튜닝; 조시형 지음]을 참고하여 작성하였습니다. 

 

1. 

TABLE ACCESS BY INDEX ROWID

 

인덱스 ROWID는 사실 논리적 주소에 가깝다. 타고 타고 넘어가는 거기 때문에 빠르지 않다.

 

메인 메모리 DB는 데이터를 모두 메모리에 로드해 놓고 메모리를 통해서만 I/O를 수행하는 DB다. 디스크를 경유하지 않고 대부분 데이터를 메모리에서 읽는다는 뜻이다. 디스크에 저장된 데이터를 버퍼캐시로 로딩하고 이어서 인덱스를 생성한다. 인덱스는 디스크 상의 주소정보를 갖는 게 아니라, 메모리상의 주소정보를 갖는다. 

 

이에 비해  일반 DBMS는 메모리 주소정보가 아닌, 디스크 주소정보(DBA)를 이용해 해시 알고리즘으로 버퍼 블록을 찾아간다.

인덱스로 테이블 블록을 액세스할 때는 액세스할 때는 리프 블록에서 읽은 ROWID를 분해해서 DBA 정보를 얻고, 테이블을 Full Scan 할 때는 익스텐트 맵을 통해 읽을 블록들의 DBA 정보를 얻는다.

 

2.

 

인덱스 클러스터링 팩터(CF)

데이터가 물리적으로 근접해 있으면 흩어져 있을 때보다 데이터를 찾는 속도가 빠르다. ex. 인덱스 레코드 정렬 순서와 테이블 레코드 정렬 순서가 일치하면 테이블 블록에 대한 포인터를 바로 해제하지 않고 유지할 수 있다. (버퍼피닝) 테이블 액세스량에 비해 블록 I/O를 줄일 수 있다. 

 

 

인덱스 손익분기점

CF에 따라 손익분기점이 달라지는데, 일반적으로는 5~20%일 때이다. 이걸 통해 인덱스가 항상 좋을 수는 없다는 것을 알고 테이블 전체 스캔이 항상 나쁜 것만이 아님을 알아야 한다. 

Table Full Scan	인덱스 rowid를 이용한 테이블 액세스
시퀀셜 액세스 방식	랜덤 액세스 방식
Multiblock I/O	Singleblock I/O

 

OLTP vs OLAP

OLTP	OLAP
소량 데이터 읽고 갱신	대량 데이터를 읽고 갱신하는 배치 프로그램 (항상 전체범위 처리 기준으로 튜닝해야)
NL 조인	Full Scan, 해시 조인 유리

 

배치 프로그램에서는 파티션 활용 전략이 매우 중요한 튜닝 요소이다.

 

테이블 액세스를 최소화하기 위해 인덱스에 컬럼을 추가할 수도 있다. 이는 테이블 액세스 단계 필터 조건에 의해 버려지는 레코드가 많을 때 테이블 액세스 전에 비교 컬럼을 추가하는 것이다. 하지만 tradeoff는 항상 존재하고 확실한 성능 보장이 있는 거 아니면 다시 생각해 봐야 한다. 왜냐하면 인덱스 관리 비용이 증가함은 물론, DML 부하에 따른 트랜잭션 성능 저하가 생길 수 있기 때문이다. 

 

필터 조건에 의해 버려지는 레코드가 없더라도 절대 일량이 많다면 성능은 느려질 수밖에 없는데, 그때는 쿼리에 사용된 컬럼 모두 인덱스에 추가하면 테이블 액세스를 제거하여 성능이 획기적으로 좋아진다. 하지만 실무에 적용하기에는 곤란한 경우가 많다. 이때 적용된 sql 쿼리를 'Covered 쿼리', 인덱스는 'Covered 인덱스'라고 부른다. 

 

Include 인덱스

인덱스 키 외에 미리 지정한 컬럼을 리프 레벨에 함께 저장하는 기능이다. 수평적 탐색에 필터 조건으로 사용할 수 있다. 

create index emp_x01 on emp (deptno) include (sal)

 

인덱스 구조 테이블과 클러스터 테이블 

인덱스 구조 테이블은 랜덤 액세스가 아예 발생하지 않도록 테이블을 인덱스 구조로 생성한 것이다. IOT(Index-Organized Table), '클러스터형 인덱스'라고 부른다. IOT는 테이블 블록에 있어야 할 데이터를 인덱스 리프 블록에 모두 저장하고 있는다. 인덱스 구조 테이블이기 떄문에 정렬 상태를 유지하며 데이터를 입력한다. CF를 좋게 만들기 때문에 시퀀셜 블록 I/O가 가능하다.

 

클러스터 테이블은 클러스터 키 값이 같은 레코드를 한 블록에 모아서 저장하는 구조다. 심지어 같은 블록에 여러 테이블 레코드를 저장할 수도 있다(다중 테이블 클러스터).

• 인덱스 클러스터
• 해시 클러스터 - 해시 알고리즘 사용

오라클 클러스터는 키 값이 같은 데이터를 같은 공간에 저장해 둘 뿐, IOT나 SQL Server의 클러스터형 인덱스처럼 정렬하지 않는다. 클러스터 인덱스는 테이블 레코드를 일일이 가리키지 않고 해당 키 값을 저장하는 첫 번째 데이터 블록을 가리킨다.(1:m) 

 

부분범위 처리

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

 

3. 

스캔 시작점과 끝점, 스캔량을 줄이는지의 여부는 데이터 조건을 찬찬히 따져보면 수월하게 이해가 된다.

 

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

 

cf. 선두 컬럼과 선행 컬럼은 같은 게 아니다! 선두 컬럼은 맨앞에 있는 컬럼을 가리키고 선행 컬럼은 어떤 컬럼보다 상대적으로 앞쪽에 놓인 컬럼을 칭할 때 사용한다.

 

인덱스 액세스 컬럼와 인덱스 필터 조건이 있는데, 인덱스 액세스 컬럼은 아래의 역할을 수행하는 조건절이다.

• 인덱스 수직적 탐색을 통해 스캔 시작점을 결정하는 데 영향을 미친다.
• 인덱스 리프 블록을 스캔하다가 어디서 멈출지를 결정하는 데 영향을 미친다.

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

 

인덱스는 값이 정렬되어 저장되어 있기 때문에 '=' 연산자로 조회하면 조건절을 만족하는 레코드가 모두 모여 있게 된다. 반대로 '='이 아닌 연산자로 조회하면 조건절을 만족하는 레코드가 서로 흩어진 상태가 된다. 선행 컬럼이 모두 '=' 조건인 상태에서 첫 번째 나타나는 범위검색 조건까지만 만족하는 인덱스 레코드는 모두 연속해서 모여 있지만 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다. 그렇기 때문에 첫 번째 나타나는 범위검색 조건까지가 인덱스 액세스 조건이고, 나머지는 필터 조건이라고 이해한다. (오라클 실행계획에서 몇 가지 경우들을 제외하고 필터 조건을 액세스 조건으로 구분하더라도)

 

Between 조건을 IN-List로 전환할 때의 주의 사항은 IN-List 개수가 많지 않아야 한다는 것이다. In-List 개수만큼 인덱스 수직적 탐색을 하기 때문에 Between 조건 때문에 리프 블록을 많이 스캔하는 비효율보다 IN-List 개수만큼 브랜치 블록을 반복 탐색하는 비효율이 더 클 수 있기 때문이다. 참고로 인덱스 스캔 과정에서 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유용하다. 인덱스 스캔 과정을 떠올릴 때 인덱스 트리의 리프가 '블럭'으로 된 점을 상기하고 접근하자. 읽어야 할 블록의 개수, I/O의 단위는 블록 이렇게... 책에도 이렇게 나와있다. "데이터 분포나 수직적 탐색 비용을 따져보지도 않고 BETWEEN을 IN-List로 변환하는 우를 범하지 않기 바란다."

 

 

4. 옵션 처리 방식 장단점 

• OR 조건 활용

where :cust_id is null or 고객ID = :cust_id

 

옵티마이저에 의한 OR Expansion 쿼리 변환이 기본적으로 작동하지 않는다. 왜냐하면 or 조건은 여러 조건 중 하나만 맞아도 되어서 옵티마이저 입장에서는 각 조건마다 인덱스를 타고 결과를 합치는 게 항상 효율적이라고 판단하지 않기 때문이다. 인덱스 선두 컬럼에 대한 옵션 조건에 OR 조건을 사용해선 안 된다. 테이블 필터 조건으로만 사용 가능하다.

• LIKE/BETWEEN 조건 활용

where 상품명 = :prd_nm and 상품분류코드 like :prd_cls_cd || '%'

 

변별력이 좋은 필수 조건이 있는 상황에서 사용하는 것은 나쁘지 않다. 하지만 필수 조건의 변별력이 좋지 않을 때는 성능에 문제가 생긴다. Table Full Scan이 유리할 상황에 Index Range Scan을 선택할 수 있기 때문이다. 

 

아래 상황에서는 피해야 한다.

• 인덱스 선두 컬럼
• NULL 허용 컬럼. 성능을 떠나 결과 집합에 오류가 생기기 때문
• 숫자형 컬럼. 잦은 자동 형 변환이 일어나기 때문
• 가변 길이 컬럼

• UNION ALL 활용 

select * from 거래
where :cust_id is null
and 거래일자 between :dt1 and :dt2
union all
select * from 거래
where :cust_id is not null
and 고객ID = :cust_id
and 거래일자 between :dt1 and :dt2

 

옵션 조건 컬럼도 인덱스 액세스 조건으로 사용한다. 고객ID가 NULL 허용 컬럼이더라도 사용하는 데 전혀 문제가 없다. 하지만 코드가 길어진다. 

 

• NVL/DECODE 함수 활용 

select * from 거래
where 고객ID = nvl(:cust_id, 고객ID)
and 거래일자 between :dt1 and :dt2

select * from 거래
where 고객ID = decode(:cust_id, null, 고객ID, :cust_id)
and 거래일자 between :dt1 and :dt2

 

 

둘 다 실행 계획이 동일하게 어느 상황에서든 인덱스를 사용한다. 고객ID 컬럼을 함수 인자로 사용(인덱스 컬럼 가공)했는데도 인덱스를 사용할 수 있는 것은 OR Expansion 쿼리 변환이 일어났기 때문이다. UNION ALL 방식으로 옵티마이저가 쿼리를 변환했다. 옵션 조건 컬럼을 인덱스 액세스 조건으로 사용할 수 있다. 즉 UNION ALL보다 단순하면서 UNION ALL과 같은 성능을 낸다. 단 함수를 여러 개 사용할 경우 그중 가장 변별력이 좋은 컬럼 기준으로 한 번만 OR Expansion이 일어난다. 

 

5. 

PL/SQL 사용자 정의 함수는 개발자들이 일반적으로 생각하는 것보다 매우 느리다. 

• 가상머신(VM) 상에서 실행되는 인터프리터 언어 (native 코드로 완전 컴파일된 내장 함수보다 많이 느림)
• 호출 시마다 컨텍스트 스위칭 발생
• 내장 SQL에 대한 Recursive Call 발생

따라서 Recursive Call을 최소화하는 것이 중요한데, 함수의 인자로 쓰이는 컬럼의 인덱스 구성에 따라 성능 차이가 난다.