SQL 튜닝이론 - 인덱스 활용 시 테이블 엑세스를 최소화하는 인덱스 튜닝방안

SQL 튜닝이론 - 인덱스 활용 시 테이블 엑세스를 최소화하는 인덱스 튜닝방안

 

테이블 조회 시, 테이블 액세스를 최소화해야 하는 이유

대량 테이터를 조회할때 인덱스를 사용하면 테이블 전체를 읽는 것보다 훨씬 느리게 조회됩니다. 인덱스 스캔 후 반드시 테이블 액세스가 발생하기 때문입니다.

 

Execution Plan
----------------------------------------------------------------
SELECT STATEMENT OPTIMIZER=ALL_ROWS
    TABLE ACCESS BY INDEX ROWID OF 'CUSTOMER'(TABLE)
        INDEX RANGE SCAN OF 'CUSTOMER_LOCATION_IDX'(INDEX)

 

실행계획의 "TABLE ACCESS BY INDEX ROWID OF 'CUSTOMER'(TABLE)" 에 해당하는 작업이 테이블 액세스입니다. 인덱스를 스캔하는 이유는 조건에 만족하는 빠르게 액세스 하기 위함입니다. 인덱스에 레코드에는 테이블 레코드의 주소인 ROWID가 있습니다. ROWID는 테이블 레코드를 찾아가기 위한 논리적 주소정보입니다.

 

ROWID가 데이터레코드의 물리적 주소정보가 아닌 논리적 주소정보라는 점에 주목해야합니다. 만약 ROWID가 물리적 주소라면 프로그래밍언어의 포인터의 역할하여 0에 가까운 비용으로 연결된 데이터레코드를 찾아갈 것입니다. 하지만 논리적 주소정보로 데이터레코드를 찾아가려면 알고리즘을 통한 탐색과정이 필요하기 때문에 비용이 발생합니다. 이것이 대용량 테이블을 인덱스를 통해 접근할 때 많이 시간이 소요되는 이유입니다.

 

버퍼캐시의 탐색과정

인덱스의 ROWID를 통해 테이블에 엑세스 하기 전에 항상 버퍼캐시에 해당 데이터가 있는지 확인합니다. 인덱스에 있는 ROWID를 분해하여 버퍼캐시 내의 DBA정보를 얻습니다. 얻은 DBA정보를 해시함수에 입력해 버퍼 헤더를 찾습니다. 마지막으로 버퍼 헤더에 담긴 버퍼블록의 메모리 주소값을 통해 버퍼캐시에서 해당하는 버퍼 블록을 읽습니다. 만족하는 레코드를 찾지 못하면 테이블에 액세스 합니다.

 

이런 과정에서 비용이 발생하는 부분은 DBA해싱과 메모리에 접근하기 위한 래치 획득입니다. 동시 액세스가 심할 때는 캐시버퍼 체인 래치와 버퍼 LOCK에 대한 경합까지 발생합니다.

 

클러스터링 팩터

클러스터링 팩터는 특정 칼럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미합니다. 데이터의 군집성을 나타내는 계수입니다. 클러스터링 팩터가 높은 칼럼에 생성한 인덱스는 검색효율이 매우 뛰어납니다. 단순한 예로 인덱스 레코드와 테이블 레코드의 정렬순서가 일치하면 클러스터링 팩터가 가장 높습니다. 이유는 테이블 액세스량에 비해 블록 I/O가 적게 발생하기 때문입니다.

 

조건에 만족하는 모든 인덱스 레코드에 대해 1대 1로 블록 I/O가 발생한다면 클러스터링 팩터는 의미가 없습니다. 하지만 인덱스를 스캔하고 블록 I/O가 발생하는 과정에서 버퍼 PINNING이 발생합니다. 버퍼 PINNING은 인덱스의 래치 획득과 해시 차인 스캔과정을 생략하게 합니다.

 

오라클의 경우, 래치 획득과 해시 체인 스캔을 통해 찾아간 테이블 블록에 대한 포인터를 해제하지 않고 다음 인덱스 레코드를 읽습니다. 이때 직전과 같은 테이블 블록을 가리키는 경우 논리적 블록 I/O 과정을 생략하고 곧바로 테이블 블록에 접근할 수 있습니다. 극단적인 예로 인덱스 레코드와 테이블 레코드의 순서가 같은 경우 한 번의 블록 I/O를 통해 전체 데이터를 읽어올 수 있습니다.

 

인덱스의 손익분기점

 

테이블을 직접 읽는 TABLE FULL SCAN의 성능은 항상 동일합니다. 반면, 인덱스를 활용하는 INDEX RANGE SCAN의 경우 전체 데이터 중 추출하는 건수가 많아질수록 따라 성능이 크게 저하됩니다. 성능 저하의 이유에는 인덱스 스캔량이 느는 이유도 있지만 블록 I/O와 테이블 액세스의 수가 늘기 때문입니다.

 

인덱스를 이용한 테이블 액세스가 TABLE FULL SCAN에 비해 느려지는 핵심 요인은 다음과 같습니다.

 

- TABLE FULL SCAN 은 시퀀셜 액세스(순차적인 액세스)인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 랜덤 엑세스 방식입니다.

- TABLE FULL SCAN은 MULTIBLOCK I/O 인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 SINGLE BLOCK I/O 방식입니다.

 

 

이런 요인으로 인해 인덱스의 손익분기점은 보통 전체 레코드의 5~20% 수준입니다, 클러스터링 팩터가 낮은 칼럼에 대한 인데스라면 더 낮은 수준으로 결정될 것입니다.

 

프로그램의 종류에 따라 달라지는 튜닝방법

온라인 프로그램의 경우 소량 데이터를 읽고 갱신하므로 인덱스를 효과적으로 활용하는 것이 무엇보다 중요합니다. 반면, 대량 데이터를 읽고 갱신하는 배치 프로그램은 항상 전체범위를 처리 기준으로 튜닝해야 합니다. 대량 배치 프로그램에선 인덱스보다 FULL SCAN이 효과적이고, 초대용량 테이블의 경우 FULL SCAN 하면 상당히 오래 기다려야 하므로 파티션 활용과 병렬처리가 매우 중요합니다.

 

-- 실형확인번호로 조회한 특정 고객의 최근 1년 이내 변경이럭 중 전월 말일 데이터를 출력

select
from 고객 c, 고객변경이력 h
where c.실명확인번호 = :rmnno
and h.고객번호 = c.고객번호
and h.변경일시 = (
    select max(변경일시)
    from 고객변경이력 m
    where 고객번호 = c.고객번호
    and 변경일시 >= trunc(add_months(sysdatte, -12), 'mm')
    and 변경일시 < trunc(sysdate, 'mm')
)

0  SELECT STATEMENT
1  0   NESTED LOOPS
2  1     NESTED LOOPS
3  2      NESTED LOOPS
4  3         TABLE ACCESS BY INDEX ROWID OF '고객'
5  4             INDEX RANGE SCAN OF '고객_X01'
6  3         VIEW PUSHED PREDICATED OF SYS.VW_SQ_1
7  6             SORT AGGREGATE
8  7                 FIRST ROW
9  8                     INDEX RANGE SCAN MIN/MAX OF '고객변경이력_PK'
10 2      INDEX UNIQUE SCAN OF 고객변경이력_PK
11 1     TABLE ACCESS BY INDEX ROWID OF '고객변경이력'
```'

위의 쿼리는 실명확인번호 조건에 해당하는 데이터가 소량이므로 인덱스와 NL조인을 사용하는 것이 효과적입니다.

```sql

-- 고객구분코드가 'A001'인 고객의 최근 1년 이내 변경이럭 중 전월 말일 데이터를 출력

select
from 고객 c, 고객변경이력 h
where c.고객구분코드 = 'A001'
and h.고객번호 = c.고객번호
and h.변경일시 = (
    select max(변경일시)
    from 고객변경이력 m
    where 고객번호 = c.고객번호
    and 변경일시 >= trunc(add_months(sysdatte, -12), 'mm')
    and 변경일시 < trunc(sysdate, 'mm')
)

```'
위 쿼리는 Full Scan 과 해시조인을 사용해야 효과적입니다. 고객구분코드가 A001에 해당하는 고객이 100만명이기 때문에 인덱스와 NL 조인을 활용하는 것이 비효율 적입니다.

```SQL
select /*+full(c) full(h) index_ffs(m.고객변경이력) ordered no_merge(m) use_hash(m) use_hash(h)*/ 
c.고객번호, c.고객명, h.전화번호, h.주소, h.상태코드, h.변경일시
from 고객 c 
,(
    select 고객번호, max(변경일시) 최종변경일시
    from 고객변경이력 
    where 변경일시 >= trunc(add_months(sysdatte, -12), 'mm')
    and 변경일시 < trunc(sysdate, 'mm')
    group by 고객번호
) m
, 고객변경이력 h
where c.고객구분코드 = 'A001'
and m.고객번호 = c.고객번호
and h.고객번호 = m.고객변호
and h.변경일시 = m.최종변경일시


0  SELECT STATEMENT
1  0   HASH JOIN
2  1       HASH LOOPS
3  2        TABLE ACCESS FULL OF '고객'  
4  2        VIEW 
5  4            SORT GROUP BY
6  5                FILTER
7  6                    INDEX FAST FULL SCAN '고객변경이력_PK'
8  1   TABLE ACCESS FULL OF '고객변경이력'

 

위 쿼리는 고객변경이력 테이블을 두 번 읽는 비효율이 있어 아래와 같이 윈도 함수를 이용할 수 있습니다.

 

select
from (
    select /*+full(c) full(h) leading(c) use_hash(h)*/ 
    c.고객번호, c.고객명, h.전화번호, h.주소, h.상태코드, h.변경일시
    ,rank() over (partition by h.고객번호 order by h.변경일시 desc) no
    from 고객 c, 고객변경이력 h
    where     c.고객구분코드 = 'A001'
    and     h.고객번호 = c.고객번호
    and     h.변경일시 >= trunc(add_months(sysdatte, -12), 'mm')
    and     h.변경일시 < trunc(sysdate, 'mm')
) where no = 1


0  SELECT STATEMENT
1  0   VIEW
2  1    WINDOW(SORT PUSHED RANK)      
3  2        FILTER 
4  3            HASH JOIN
5  4                TABLE ACCESS FULL OF '고객'
6  4                TABLE ACCESS FULL OF '고객변경이력'

 

인덱스 칼럼 추가

 

테이블 액세스 최소화를 위한 가장 일반적인 튜닝 기법은 인덱스에 조건절에 있는 칼럼을 추가하는 것입니다. 인덱스의 순서르 변경하거나 조건절에 최적화된 인덱스르 만드는 것은 인덱스 관리 비용의 증가와 DML 부하를 유발합니다. 반면, 인덱스에 컬럼을 추가하는 것은 인덱스 스캔량은 줄지 않지만 테이블 랜덤 액세스의 횟수를 줄여줍니다.

 

-- INDEX 로밍렌탈_N2(서비스번호)
SELECT 렌탈관리번호, 고객명, 서비스관리번호, 서비스번호, 예약접수일시, 방문국가코드1, 방문국가코드2, 방문국가코드3, 로밍승인번호, 자동로밍여부
FROM 로밍렌탈
WHERE 서비스번호 LIKE '010%'
AND 사용여부 = 'Y'

ROWS    ROWS SOURCE OPERATION
----    ---------------------
1909    TABLE ACCESS BY INDEX ROWID 로밍렌탈 (CR=266,968 PR=27830, PW=0)
266476        INDEX RANGE SCAN 로밍렌탈_N2 (CR=1011 PR=900, PW=0)

 

위 쿼리의 인덱스 스캔 건수는 266746건입니다. 건수만큼 테이블 렌덤 액세스가 발생하여 265,957개 (266968 - 1011)의 블록을 읽었습니다. 이는 전체블록 I/O의 99.6% 를 차지하는 양입니다. 추가적으로 26만 개의 테이블을 엑세스하는데 26만개의 블록을 방문했으므로 서비스번호의 클래스터링 펙터가 매우 좋지 않다는 것을 알 수 있습니다. 더 큰 문제는 최종집합이 1909개뿐이라는 것입니다. 테이블을 방문하고 사용여부가 Y인 조건을 체크하는 과정에서 대부분 걸러진 것입니다.

 

로밍렌털_N2 인덱스에 사용여부 칼럼을 추가하면 인덱스 스캔량을 감소시킬 수 있습니다.

 

ROWS    ROWS SOURCE OPERATION
----    ---------------------
1909    TABLE ACCESS BY INDEX ROWID 로밍렌탈 (CR=2902,968 PR=27830, PW=0)
1909        INDEX RANGE SCAN 로밍렌탈_N2 (CR=1011 PR=0, PW=0)

 

테이블 액세스가 1909번 발생했고 모두 결과집합에 포함됐습니다. 불필요한 테이블 엑세스가 발생하지 않았습니다. 총 블록 I/O 또한 2902개로 줄었습니다.

인덱스만 읽고 처리하기

인덱스에 없는 조건절에 사용된 칼럼인 테이블 액세스 필터 조건에 의해 버려지는 레코드가 많을때 인덱스 컬럼을 추가함으로써 테이블 엑세스 량을 줄일 수 있었습니다. 그런데 테이블 랜덤 엑세스가 아무리 많아도 필터 조건에 의해 버려지는 레코드가 거의 없다면 비효율은 없습니다.

 

SEELCT 부서번호, SUM(수량)
FROM 판매집계
WHERE 부서번호 LIKE '12%'
GROUP BY 부서번호;

 

위 쿼리에서 부서번호 단일 인덱스를 사용한다면 테이블 엑세스 상의 비효율은 없습니다. 부서번호 LIKE '12%'에 해당하는 데이터 레코드가 많다면 어쩔 수 없이 조회에 많은 시간이 소모됩니다. 만약 반드시 성능을 개선해야 한다면 쿼리에 사용된 칼럼을 (SELECT-LIST, GROUP BY 포함) 모두 인덱스에 추가해서 테이블 액세스가 아예 발생하지 않게하는 방법이 있습니다. 이런 쿼리를 COVERED 쿼리라고 하고 인덱스를 COVERED 인덱스라고 합니다. 하지만 이는 실전에 적용하기는 어렵습니다.

 

인덱스 구조 테이블

 

랜덤 엑세스가 아예 발생하지 않도록 인덱스 구조로 생성한 테이블을 오라클에선 IOT, MS-SQL SERVER에서는 클러스터형 인덱스라고 부릅니다. 인덱스 구조의 테이블에 ROWID 대신 테이블 데이터가 있는 형태입니다. 즉, 테이블 블록에 있어야 할 데이터를 리프 블록에 모두 저장하고 있습니다. 테이블을 인덱스 구조로 만드는 구문은 아래와 같습니다.

 

CREATE TABLE INDEX_ORG_T ( A NUMBER, B VARCAHR(10), CONSTRAINT INDEX_ORG_t_PK PRIMARY KEY(A))
ORGANIZATION INDEX;

 

일반 테이블은 힙 구조 테이블로 다음과 같이 생성합니다.

 

CREATE TABLE INDEX_ORG_T ( A NUMBER, B VARCAHR(10), CONSTRAINT INDEX_ORG_t_PK PRIMARY KEY(A))
ORGANIZATION HEAP;

 

일반 힙 구조 테이블에 데이터를 입력하면 FREELIST로부터 할당받은 블록에 정해진 순서 없이 데이터를 입력합니다. 반면 IOT는 정렬 상태를 유지하며 데이터를 입력합니다. 이로 인해 클러스터링 팩터가 매우 높습니다. 데이터를 시퀀셜 액세스하고 BETWEEN이나 부등호 조건으로 조회 시 유리합니다.

 

IOT 테이블은 데이터 입력과 조회 패턴이 다른 테이블에 유용합니다. 100명의 영업사원의 일별실적을 한 블록에 집계하여 1년에 365 개의 블록을 생성합니다. 이 테이블을 사원별 매월 실적을 조회합니다.

 

SELECT 
FROM 영업실적 
WHERE 사번 = 's1234'
AND  일자 between '20180101' and '20181231'
GROUP BY SUBSTR(일자, 1, 6)

 

일반적으로 인덱스를 사용하면 사원마다 365개의 블록을 읽어야 합니다. 각 블록에 특정 사원에 대한 레코드가 하나씩 들어있으니 클러스터링 팩터가 매우 나쁩니다. 이처럼 입력과 조회 패턴이 다를 때 사 번이 첫 번째 정렬기준이 되도록 iot를 구성하면 한 블록에 특정 사원에 대한 데이터가 모이므로 최대 4블록(365개 의 레코드)만 읽고도 조회가 가능합니다.

 

클러스터 테이블

 

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

 

인덱스 클러스터 테이블

 

값이 같은 레토드를 한 블록에 모아 저장하는 구조입니다. 블록이 가득 차면 새로운 블록을 할당하고 클러스터 체인으로 연결합니다. 여러 테이블의 레코드를 하나의 블록에 저장하는 다중 테이블 클러스터도 가능합니다. 일반적으로 하나의 블록은 하나의 테이블만 사용합니다.

 

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

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
    , IOC VARCHAR2(13))

CLUSTER C_DEPT#(DEPTNO);
)

 

클러스터 인덱스의 리프노드는 해당 키 값을 저장하는 첫 번째 데이터 블록만 가리킵니다. 같은 값을 같은 레코드가 모여있기 때문입니다. 이런 구조적 특징으로 인해 클러스터 인덱스에서 값을 찾을 때는 클러스터 체인을 스캔하면서 발생하는 랜덤 액세스를 제외하고는 값 하나당 한 번씩의 테이블 액세스가 발생합니다. 클러스터에 도달해서는 시퀀셜 방식으로 스캔하기 때문에 넓은 범위를 읽더라도 비효율이 없습니다.

 

클러스터 인덱스로 조회할 때 실행계획은 아래와 같습니다.

 

SELECT * FROM DEPT WHERE DEPTNO = :DEPTNO;

0    SELECT STATEMENT OPTIMIZER=ALL_ROWS
0    1    TABLE ACCESS CLUSTER OF DEPT
2    1        INDEX UNIQUE SCAN OF C_DEPT#_IDX(INDEX(CLUSTER))

 

해시 클러스터 테이블

 

인덱스를 사용하지 않고 해시 알고리즘을 사용해 클러스터를 찾아갑니다.

 

create cluster c_dept#(deptno number(2)) HASHKEY 4;

CREATE TABLE DEPT (
    DEPTNO NUMBER(2) NOT NULL
    ,DNAME VARCHAR2(14) NOT NULL
    , IOC VARCHAR2(13))

CLUSTER C_DEPT#(DEPTNO);
)

SELECT * FROM DEPT WHERE DEPTNO = :DEPTNO;

0    SELECT STATEMENT OPTIMIZER=ALL_ROWS
0    1    TABLE ACCESS HASH OF DEPT (CLUSTER(HASH))