[SQL BOOSTER] 6-1. INDEX의 기본 개념

📚 SQL BOOSTER (유일환, DBian)

 

CH6. INDEX

6-1. INDEX의 개본 개념

6-1-1. 인덱스(INDEX)란?

DB의 인덱스(INDEX)는 책의 색인(INDEX)처럼 테이블 내의 데이터를 찾을 수 있게 일부 데이터를 모아서 구성한 데이터 구조이다.

인덱스를 이용하면 테이블 내의 데이터를 빠르게 찾아낼 수 있다.

 

예시1. 테스트를 위한 T_ORD_BIG 테이블 만들기

CREATE TABLE T_ORD_BIG AS
SELECT T1.*, T2.RNO, TO_CHAR(T1.ORD_DT, 'yyyymmdd') ORD_YMD
FROM T_ORD T1
    , (SELECT ROWNUM RNO
       FROM DUAL
       CONNECT BY ROWNUM <= 10000) T2;

 

예시2. T_ORD_BIG 테이블의 통계 생성하기(에러)

EXEC DBMS_STATS.GATHER_TABLE_STATS('test_user', 'T_ORD_BIG');

 

위의 SQL을 실행하여 T_ORD_BIG 테이블의 통계정보를 생성하려는데 다음과 같은 오류가 발생했다.

 

ORA-00900 오류의 원인을 찾기 위해 구글링해보았다.

EXEC 명령어는 PL/SQL 명령어가 아닌 SQL*Plus 명령어이기 때문에 EXEC 대신 BEGIN ... END; 를 사용해보라는 댓글을 보게되어 문제를 해결할 수 있었다.

 

(출처) https://stackoverflow.com/questions/64058383/how-to-resolve-issue-of-stored-proc-not-executing-dbms-stats-gather-table-stats

How to resolve issue of stored proc not executing dbms_stats.gather_table_stats() when executing as it string via EXECUTE IMMEDI

 

예시2. T_ORD_BIG 테이블의 통계 생성하기

BEGIN
DBMS_STATS.GATHER_TABLE_STATS('test_user', 'T_ORD_BIG');
END;

 

예시3. 인덱스가 없는 T_ORD_BIG 테이블 조회 - ORD_SEQ가 343인 데이터 카운트하기

SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM t_ord_big big
WHERE big.ord_seq = 343;

 

실행계획 확인

SELECT T1.sql_id, T1.child_number, T1.sql_text
FROM v$sql T1
WHERE T1.sql_text LIKE '%GATHER_PLAN_STATISTICS%'
ORDER BY T1.last_active_time DESC;

SELECT *
FROM TABLE (dbms_xplan.DISPLAY_CURSOR('0bnmdbygb56pk',0,'ALLSTATS LAST'));

 

 

실제 실행계획을 확인해보니 시간은 9초가 걸렸고, 전체 Buffers(논리적 IO)는 258K가 나왔다.

실행 순서는 테이블 전체를 읽어서 ORD_SEQ가 343인 데이터를 찾아내서 카운트 처리를 한 것으로 나온다.

 

이제 WHERE 조건절에 사용된 ORD_SEQ 컬럼에 인덱스를 만들어 보겠다.

 

예시4. ORD_SEQ 컬럼에 인덱스 구성하기

CREATE INDEX X_T_ORD_BIG_TEST ON T_ORD_BIG(ORD_SEQ);

 

예시5. 인덱스가 존재하는 T_ORD_BIG 테이블 조회 - ORD_SEQ가 343인 데이터 카운트하기

SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM t_ord_big big
WHERE big.ord_seq = 343;

 

실행계획

 

인덱스를 생성한 후 동일한 SQL을 실행하여 실제 실행계획을 확인해보면 수행시간이 9초에서 0.05초로 줄었고, 전체 Buffers도 258K에서 24로 좋아진 것을 확인할 수 있다.

실행 순서를 살펴보면 인덱스(X_T_ORD_BIG_TEST)를 사용해서 ORD_SEQ가 343인 데이터를 찾은 후 카운트 처리를 한 것을 알 수 있다.

 

이처럼 인덱스를 만드는 것만으로 성능이 향상될 수 있다.

 

6-1-2. 인덱스의 종류

인덱스는 기준에 따라 다양하게 구분할 수 있다.

 

(1) 인덱스를 구성하는 컬럼 수에 따라 구분

단일 인덱스(Single column index) : 인덱스에 하나의 컬럼만 사용

복합 인덱스(Composite index) : 인덱스에 두 개 이상의 컬럼을 사용

 

단일 인덱스는 PK 속성이 단일 컬럼일 때 주로 사용한다.

잘 만들어진 하나의 복합 인덱스는 여러개의 인덱스를 대신할 수 있으며, 여러 SQL의 성능을 커버할 수도 있다.

가능하면 하나의 복합 인덱스로 여러 SQL을 커버하는 것이 좋다.

SQL별로 필요한 인덱스를 모두 만들다보면 인덱스가 너무 많아지기 때문이다.

 

(2) 인덱스를 구성하는 컬럼 값의 중복 허용 여부에 따라 구분

유니크 인덱스(Unique index) : 인덱스 구성 컬럼 값에 중복을 허용하지 않는다.

비유니크 인덱스(Non-unique index) : 인덱스 구성 컬럼 값에 중복을 허용한다.

 

PK 제약조건에는 무조건 유니크 인덱스가 구성된다. (PK는 자동으로 인덱스가 만들어진다.)

데이터베이스 설계 시점부터 업무적으로 유니크한 속성들을 파악해서 유니크 인덱스를 만들어주는 것이 좋다.

 

(3) 인덱스의 물리적 구조에 따라 구분

B* 트리 인덱스(B*Tree index) : 트리 형태의 자료 구조 사용, OLTP 시스템은 대부분 B* 트리 구조 인덱스를 사용한다.

비트맵 인덱스(Bitmap index) : 값의 종류가 많지 않은 컬럼에 사용한다.

 

(4) 파티션된 인덱스

글로벌 인덱스

로컬 인덱스

 

위의 분류를 혼합해서 인덱스를 생성할 수도 있다.

 

6-1-3. B*트리 구조와 탐색 방법

인덱스를 생성할 때 별다른 옵션을 정의하지 않으면 B*트리 구조의 인덱스가 만들어진다.

 

B*트리 구조

• B(balanced) : 균형이 잡혀 있다, 리프 노드들이 같은 수준(깊이)에 자리해 있다.
• *(star) : 근접한 리프 노드가 연결(link)된 구조

정리해보면, B* 트리는 균형이 잡혀 있고 근접한 리프 노드가 연결된 구조다.

B* 트리는 루트(root, 뿌리), 브랜치(branch, 가지), 리프(leaf, 잎사귀) 세 가지 유형의 블록으로 구성되어 있다.

나무에서 뿌리는 하나지만, 가지는 매우 많고, 잎사귀는 더 많듯이 B* 트리는 전체적으로 삼각형 모양이 된다.

 

인덱스를 구성하는 블록을 인덱스 블록이라고 하며, 인덱스 블록은 서로 연결되어 있다.

루트는 자신의 하위 브랜치와, 각 브랜치는 각각 자신의 하위 브랜치나 리프 블록과 연결되어 있다.

리프 블록 밑으로는 다른 인덱스 블록은 없다.

 

인덱스 블록의 구성

루트 블록

• 최상위에 단 하나만 존재
• 하위 브랜치 블록의 인덱스 키 값과 주소를 가지고 있다.

브랜치 블록

• 루트와 리프의 중간에 위치, 브랜치는 여러 층이 있을 수 있다.
• 하위 브랜치 또는 하위 리프의 인덱스 키 값과 주소를 가지고 있다.

리프 블록

• 최하위에만 위치
• 인덱스 키 값과 데이터의 로우 위치(ROWID)를 가지고 있다.
• 리프 블록은 인덱스 키 값 순으로 정렬되어 있다.

 

B* 트리 구조를 이용해 데이터를 찾아가는 과정은 다음과 같다.

ORD_YMD로 구성된 인덱스를 이용해 'ORD_YMD = 20170104'인 데이터를 찾아내보자.

 

(1) 루트 블록

위 그림에서 루트 블록(B99)를 보면 세 개의 브랜치 블록(B05, B06, B01)을 찾아갈 수 있다.

'20170104'는 빈 값보다 크고 '20170601'보다 작다.

그러므로 브랜치 블록 중에서 B05 블록으로 이동해야 한다.

 

(2) 브랜치 블록

그림에서 B05 블록은 세 개의 리프 블록(B02, B10, B21)을 가지고 있다. 

'20170104'는 '20170102'보다 크고 '20170104'보다 작거나 같다.

그러므로 B10으로 이동해야 한다.

여기서 주의할 점은 B10 뒷부분에도 '20170104'가 일부 있을 수 있기 때문에 B21이 아닌 B10으로 이동해야 한다는 점이다.

 

(3) 리프 블록

인덱스를 검색해서 리프 블록에 도달하면 이제는 리프 블록을 차례대로 스캔해야 한다.

스캔 작업은 찾으려는 값보다 큰 값을 발견하기 전까지 수행한다.

여기서는 B10 블록의 첫 번째 데이터에서 시작해 B21 블록의 '20170105'를 만날 때까지 스캔이 진행된다.

이때 리프 블록을 스캔하면서 ROWID를 참고해 실제 테이블에 접근하는 작업을 수행한다.

ROWID는 데이터가 실제 저장된 주소 값이다.

ROWID를 이용해 데이터를 찾아내는 과정은 실행계획에 'TABLE ACCESS BY INDEX ROWID'라는 오퍼레이션으로 나타난다.

 

6-1-4. 데이터를 찾는 방법

오라클에서 데이터를 찾는 방법

1. 테이블 전체 읽기(TABLE ACCESS FULL)
2. 인덱스를 이용한 찾기(INDEX RANGE SCAN & TABLE ACCESS BY INDEX ROWID)
3. ROWID를 이용한 직접 찾기(TABLE ACCESS BY INDEX ROWID)

 

6-1-5. 데이터를 찾는 방법 - 테이블 전체 읽기

테이블 전체 읽기는 테이블의 데이터 블록을 차례대로 모두 읽으면서 필요한 데이터를 찾는 방법이다.

실행계획에 'TABLE ACCESS FULL'로 표현된다.

테이블 전체 읽기는 찾고자 하는 조건에 활용할 인덱스가 없거나 인덱스보다 테이블 전체를 읽는 것이 더 효율적이라고 판단할 때 사용하는 방법이다.

 

오라클에서 데이터가 테이블에 저장될 때는 특정 순서를 갖지 않는다.

테이블 내에 저장된 데이터는 어떤 기준으로도 정렬되어 있지 않다.

따라서 오라클은 찾으려는 데이터가 어디에 있고, 정확히 몇 건 있는지 알지 못한다.

모든 테이블 블록을 읽어야만 조건이 맞는 데이터를 빠짐없이 찾아낼 수 있다.

 

WHERE 조건절에 사용된 컬럼에 적절한 인덱스가 없다면, 테이블 전체 읽기(TABLE ACCESS FULL)만이 원하는 데이터를 찾을 수 있는 유일한 방법이다.

 

예시1. TABLE ACCESS FULL을 사용하는 SQL - 고객별 '20170316' 일자의 주문 건수 찾기

SELECT /*+ GATHER_PLAN_STATISTICS */
       T1.cus_id
       , count(*) ORD_CNT
FROM t_ord_big T1
WHERE T1.ord_ymd = '20170316'
GROUP BY T1.cus_id
ORDER BY T1.cus_id;

 

실행계획

 

위 실행계획 2번 오퍼레이션을 보면 TABLE ACCESS FULL이 있는 것을 알 수 있다.

TABLE ACCESS FULL은 테이블 블록 전체를 읽는다.

그러므로 테이블이 클수록 오래 걸린다.

만약에 천만 건의 데이터를 가진 테이블에서 찾고자 하는 데이터가 한 건이라면 TABLE ACCESS FULL은 매우 비효율적이다.

이런 경우에는 인덱스를 사용해 데이터를 찾아내는 것이 효율적이다.

하지만 천만 건의 데이터를 가진 테이블에서 찾아야 하는 데이터가 백만 건 정도 된다면 인덱스를 사용하는 것보다 TABLE ACCESS FULL이 더 효율적일 수 있다.

이처럼 TABLE ACCESS FULL이 무조건 성능이 나쁜 것은 아니다.

 

6-1-6. 데이터를 찾는 방법 - 인덱스를 이용한 찾기

인덱스를 이용한 찾기는 인덱스를 이용해 필요한 데이터만 찾는 방법이다.

'INDEX RANGE SCAN', 'INDEX SKIP SCAN', 'INDEX FULL SCAN' 등 다양한 방식이 있지만, 이 책에서는 가장 기본인 'INDEX RANGE SCAN'에 대해 설명한다.

이 방법은 필요에 따라 'TABLE ACCESS BY INDEX ROWID' 작업을 동반한다.

 

인덱스를 이용해 데이터를 찾는 과정을 전체적으로 그려보면 다음과 같다.

 

1. 루프에서 리프로 : 검색 조건에 해당하는 첫 번째 리프 블록을 찾는 과정

2. 리프 블록 스캔 : 찾아낸 지점부터 리프 블록을 차례대로 읽어 가는 과정

3. 테이블 접근 : 리프 블록을 스캔하면서 필요에 따라 테이블에 접근하는 과정

 

위 설명에서 1번과 2번을 묶어서 'INDEX RANGE SCAN'이라고 한다.

'INDEX RANGE SCAN'은 필요에 따라 'TABLE ACCESS BY INDEX ROWID' 작업을 동반한다. 

 

인덱스를 이용해 데이터를 찾는 과정을 자세하게 살펴보면 다음과 같다.

(1) 루트에서 리프로 (리프 블록 찾기)

루트 블록에서 주어진 조건이 저장된 리프 블록을 찾아가는 과정이다.

이 과정은 부하가 없다고 생각해도 될 정도로 매우 빠르게 이루어진다.

 

(2) 리프 블록 스캔(RANGE SCAN)

리프 블록은 인덱스의 키 컬럼인 ORD_YMD 컬럼 값의 순으로 정렬되어 있다.

예를 들어 ORD_YMD가 '20170103'인 데이터를 검색하고 있다.

'루트에서 리프로' 과정에서 '20170103'이 최초로 저장된 3번 리프 블록을 찾아냈다고 가정하자.

3번 리프 블록을 찾아낸 후에는 리프 블록 내의 데이터를 차례대로 읽어 나간다. 차례대로 읽어나가는 과정은 ORD_YMD가 '20170103'보다 큰 데이터를 만날 때까지다.

이 과정이 리프 블록 스캔이다.

 

(3) 테이블 접근(TABLE ACCESS BY INDEX ROWID)

'리프 블록 스캔' 과정에서는 필요에 따라 테이블에 접근한다.

인덱스 리프 블록의 ROWID 값을 참조해 테이블의 데이터를 찾아가는 과정이다.

이 과정은 테이블에 필요한 값이 있을 때만 일어난다. 만약에 ORD_YMD 값만 사용해 SQL을 처리할 수 있다면 이 과정은 생략된다.

 

이제 실제 인덱스를 만들어 테스트해보자.

 

예시1. INDEX RANGE SCAN을 사용하는 SQL

ORD_YMD 컬럼에 인덱스 구성하기

CREATE INDEX X_T_ORD_BIG_1 ON t_ord_big(ord_ymd);

 

고객별 ord_ymd가 '20170316'인 주문 건수 카운트하기

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 X_T_ORD_BIG_1) */
       T1.cus_id
       , count(*) ord_cnt
FROM t_ord_big T1
WHERE T1.ord_ymd = '20170316'
GROUP BY T1.cus_id
ORDER BY T1.cus_id;

 

INDEX(T1 X_T_ORD_BIG_1) 힌트를 사용해 인덱스를 사용하도록 강제하고 있다.

힌트는 SQL 처리 방법(실행계획)만 변경할 뿐 실행 결과에는 절대 영향을 주지 않는다.

 

실행계획

 

WHERE 조건에 맞는 데이터를 찾기 위해 'INDEX RANGE SCAN'을 사용했고, 인덱스에 없는 'cus_id' 값을 가져오기 위해 'TABLE ACCESS BY INDEX ROWID' 작업까지 수행했다.

 

(참고) X_T_ORD_BIG_1 인덱스를 이용한 SQL은 처음 실행에는 9.4초가 걸리지만 두 번째 실행부터는 1.57초 정도로 매우 빠르게 실행된다. 두 번째 실행부터는 물리적IO없이 논리적IO로만 SQL이 처리되기 때문이다.

 

6-1-7. INDEX RANGE SCAN VS. TABLE ACCESS FULL

랜덤 엑세스(RANDOM ACCESS)

IO 작업 한 번에 하나의 블록을 가져오는 접근 방법을 뜻한다.

인덱스의 리프 블록에서 ROWID를 이용해 테이블에 접근할 때 랜덤 엑세스가 발생한다.

실행계획에는 'TABLE ACCESS BY INDEX ROWID'로 표시된다.

 

데이터를 찾을 때, '인덱스를 이용한 찾기'와 '테이블 전체 읽기'의 성능을 비교해 보자.

 

예시1. ORD_YMD 인덱스를 이용했을 때 성능이 더 좋은 SQL

SELECT /*+ GATHER_PLAN_STATISTICS */
       T1.cus_id
       , count(*) ord_cnt
FROM t_ord_big T1
WHERE T1.ord_ymd = '20170316'
GROUP BY T1.cus_id
ORDER BY T1.cus_id;

 

WHERE 조건절을 보면 ord_ymd가 '20170316'인 주문 데이터를 조회하고 있다.

t_ord_big 테이블에는 총 3천만 건 정도의 데이터가 있다. 그 중에 ord_ymd가 '20170316'인 데이터는 5만 건이다.

3천만 건에서 5만 건 정도를 찾는 경우라면 'INDEX RANGE SCAN'이 효율적이라고 판단할 수 있다.

(정확하게는 데이터 건수가 아닌 블록 수로 판단해야 한다.)

 

예시2. ORD_YMD 인덱스를 사용했을 때 성능이 안좋은 SQL

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 X_T_ORD_BIG_1) */
       T1.ord_st
       , sum(ord_amt) sum_amt
FROM t_ord_big T1
WHERE T1.ord_ymd >= '20170401'
AND   T1.ord_ymd < '20170701'
GROUP BY T1.ord_st;

 

t_ord_big 테이블에서 3개월 간의 주문을 조회한다.

약 7,650,000 건에 달하는 대량 데이터다.

 

실행계획

 

X_T_ORD_BIG_1 인덱스를 사용하도록 힌트를 주었다.

이 SQL은 4분이 걸린다. 전체 Buffers를 살펴보면 1011K다. 많은 논리적 IO가 발생했다.

실행계획을 보면 'TABLE ACCESS BY INDEX ROWID'가 7,650K번 실행되었다.

'TABLE ACCESS BY INDEX ROWID'는 바로 전 단계인 'INDEX RANGE SCAN'의 A-rows만큼 실행된다.

즉, 매우 많은 랜덤 엑세스가 발생했음을 알 수 있다.

 

예시3. 테이블 전체 읽기 방식을 사용했을 때 성능이 더 좋은 SQL

SELECT /*+ GATHER_PLAN_STATISTICS FULL(T1) */
       T1.ord_st
       , sum(ord_amt) sum_amt
FROM t_ord_big T1
WHERE T1.ord_ymd >= '20170401'
AND   T1.ord_ymd < '20170701'
GROUP BY T1.ord_st;

 

실행계획

 

'FULL(T1)' 힌트를 사용하자 인덱스로 처리할 때보다 속도가 빨라졌다.

총 실행 시간이 4분에서 1분 21초로 단축되었다. Buffers 수치도 1011K에서 258K로 좋아졌다.

즉, 찾고자 하는 데이터가 특정 수준 이상으로 많으면 인덱스를 이용한 '랜덤 엑세스'보다 'FULL SCAN' 방식이 더 효율적이다.

 

찾아야 하는 데이터가 절대적으로 많다면 'FULL SCAN'이 더 좋은 성능을 만들어낸다.

하지만 테이블에 데이터가 계속 쌓이는 구조라면 'FULL SCAN' 방식은 시간이 지날수록 성능이 더 나빠진다.

이런 경우에는 오래된 데이터를 잘라내거나 파티션 전략을 수립할 필요가 있다.

또는 중간 집계 테이블 등을 활용하는 방안을 고려해야 한다.

 

정리하면 다음과 같다.

1. 적은 양의 데이터를 읽는다면 'INDEX RANGE SCAN'이 유리하다.

2. 많은 양의 데이터를 읽어야 한다면 'FULL SCAN'이 유리할 수 있다.

3. 하지만 'FULL SCAN'은 데이터가 쌓일수록 성능이 점차 나빠진다. 테이블 관리 전략이 필요하다.

 

따라서 SQL 성능을 높이려는 목적으로 무조건 인덱스를 만들어서는 안 된다.

인덱스를 만들기 전에 새로운 인덱스가 과연 성능에 도움이 될지를 판단해야 한다.