인덱스(INDEX) 정의와 인덱스 튜닝 기초

1. 인덱스

추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조입니다.

 

데이터베이스에서 테이블의 모든 데이터를 검색하면 시간이 오래 걸리기 때문에 데이터와 데이터의 위치를 포함한 자료구조를 생성하여 빠르게 조회할 수 있도록 돕고 있습니다.

 

* 만약 index를 사용하지 않은 컬럼을 조회해야 하는 상황이라면 전체를 탐색하는 Full Scan을 수행해야 합니다.

Full Scan은 전체를 비교하여 탐색하기 때문에 처리속도가 떨어집니다.

 

1-1. 인덱스(index)의 장점

1. 테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있습니다.

2. 전반적인 시스템의 부하를 줄일 수 있습니다.

 

1-2. 인덱스(index)의 단점

1. 인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요합니다.

2. 인덱스를 관리하기 위해 추가 작업이 필요합니다.

3. 인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있습니다.

 

1-3. 인덱스(index)를 사용하는 경우

1. 대량의 데이터 검색

2. 정렬된 결과 출력

3. 조인 연산 수행

4. 유니크 값 검색

5. 검색 빈도가 높은 필드

* 사용되지 않는 인덱스는 제거해주어 인덱스 관리를 해줍니다.

 

2. 인덱스(index) 자료구조

인덱스를 구현하기 위해서는 다양한 자료구조를 사용할 수 있는데, 가장 대표적인 해시 테이블과 B+Tree가 있습니다.

 

2-1. 해시 테이블(Hash Table)

해시테이블은 key와 value 로 데이터를 저장하는 자료구조 중 하나로 빠른 데이터 검색이 필요할 때 유용합니다. 해시 테이블은 key값을 이용해 고유한 index를 생성하여 그 index에 저장된 값을 찾아오는 구조입니다.

해시 테이블 기반의 DB 인덱스는 컬럼의 값, 데이터의 위치를 key와 value로 사용하여 컬럼의 값으로 생성된 해시를 통해 인덱스를 구현합니다.

해시 테이블의 시간복잡도는 O(1)이며 매우 빠른 검색을 지원합니다.

하지만 DB 인덱스에서 해시 테이블이 사용되는 경우는 제한적인데, 그 이유는 해시가 등호(=) 연산에만 특화되었기 때문입니다.

해시 함수는 값이 1이라도 달라지면 완전히 다른 해시 값을 생서하는데, 이러한 특성에 의해 부등호(>,<) 연산이 자주 사용되는 데이터베이스 검색을 위해서는 해시 테이블이 적합하지 않습니다.

이러한 이유로 데이터베이스의 인덱스에서는 B+Tree가 일반적으로 사용됩니다.

 

2-2.B+Tree

자식 노드가 2개 이상인 B-Tree를 개선시킨 자료구조

기존의 B-Tree는 어느 한 데이터의 검색은 효율적이나 모든 데이터를 한 번 순회하는데에는 트리의 모든 노드를 방문해야 하므로 비효율적이며 이를 개선한 것이 B+Tree입니다.

B+Tree는 오직 leaf node에만 데이터를 저장하고 나머지 노드에는 자식 포인터만 저장합니다.

leaf node끼리는 Linkedlist로 연결되어있습니다.

장점

1. leaf node에만 데이터 저장하므로 메모리 확보

2. Full Scan의 경우 leaf node에만 데이터 저장되어있고, 이들이 Linkedlist로 연결되어 있어 선형시간이 소모됩니다.

 

단점

1. 반드시 특정 key에 접근하기 위해서 leaf node까지 가야한다.

 

* 인덱스에서는 왜 B-Tree가 아닌, B+Tree를 주로 이용하는 이유

인덱스 컬럼은 부등호(>, <)를 이용한 순차 검색 연산이 자주 발생합니다. 

B+Tree의 Linkedlist를 이용하면 효율적인 순차 검색이 가능해집니다.

 

3. 인덱스 스캔 방식

3-1. Index Range Scan

그림처럼 인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후에 리프블록을 필요한 범위만 스캔하는 방식입니다.

Index Range Scan이 나타난다고 해서 항상 빠른 속도를 보장하는 것은 아니다.

범위와 테이블 액세스 횟수를 얼마만큼 줄일 수 있느냐가 중요합니다.

Index Range Scan이 가능하게 하려면 인덱스를 구성하는 선두 컬럼이 조건절에 사용되야 합니다.

Index Range Scan 과정을 거쳐 생성된 결과 집합은 인덱스 컬럼 순으로 정렬된 상태가 되기 때문에 이런 특징을 잘 이용하면 sort order by 연산을 생략하거나 min/max 값을 빠르게 추출할 수 있습니다.

 

3-2. Index Full Scan

수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식으로서, 대개는 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택됩니다.

개념적으로 설명하기 위한 것이고 실제로는 수직적 탐색이 먼저 일어난다. 루트 블록과 브랜치 블록을 거치지 않고는 가장 왼쪽에 위치한 첫 번째 리프 블록으로 찾아갈 방법이 없기 때문입니다.

 

3-3. Index Unique Scan

수직적 탐색만으로 데이터를 찾는 스캔 방식으로서 Unique 인덱스를 ‘=‘ 조건으로 탐색하는 경우에 작동한다.

 

3-4. Index Skip Scan

인덱스 선두 컬럼이 조건절로 사용되지 않으면 옵티마이저는 기본적으로 Table Full Scan을 선택한다. 또는 Table Full Scan 보다 I/O를 줄일 수 있거나 정렬된 결과를 쉽게 얻을 수 있다면 Index Full Scan 방식을 사용한다고 했다.

오라클은 인덱스 선두 컬럼이 조건절에 빠졌어도 인덱스를 활용하는 새로운 스캔방식을 Index Skip Scan라고 합니다.

내부 수행원리는 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건에 부합하는 레코드를 포함할 ‘가능성이 있는’ 하위 블록(브랜치 또는 리프 블록)만 골라서 액세스하는 방식이라고 할 수 있습니다.

이 스캔 방식은 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용합니다.

 

3-5. Index Fast Full Scan

Index Full Scan 보다 빠른 스캔 방식입니다. 빠른 이유는 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock Read 방식으로 스캔하는 방식 때문입니다.

 

3-6. Index Range Scan Descending

인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과 집합을 얻는다는 점에서 Index Range Scan와 다르다.

 

4. 인덱스 튜닝 기초

인덱스를 정상적으로 사용하려면 범위 스캔 시작지점을 찾기 위해 루트 블록부터 리프 블록까지 수직적 탐색과정을 거쳐야 합니다.

만약 인덱스 선두 컬럼이 조건절에 사용되지 않으면 범위 스캔을 위한 시작점을 찾을 수 없어 옵티마이저는 인덱스 전체를 스캔하거나 테이블 전체를 스캔하는 방식을 선택합니다.

인덱스 선두 컬럼이 조건절에 사용되더라도 범위 스캔이 불가능하거나 인덱스를 사용 못하는 경우에 대해 알아봅시다.

 

4-1. 범위 스캔이 불가능하거나 인덱스 사용이 불가능한 경우

가. 인덱스 선두컬럼을 조건절에 가공한 경우

Ex) where substr(업체명, 1, 2) = ‘대한’

 

나. 부정형 비교를 사용한 경우

Ex) where 직업 <> ‘학생’

 

다. is not null 조건의 부정형 비교일 경우

Ex) where 부서코드 is not null

 

—> 위 세 경우 모두 정상적인 인덱스 범위 스캔이 불가능해지고 Index Full Scan이 가능하다.

 

4-2. 인덱스 컬럼 가공 튜닝 방안(튜닝 전 -> 튜닝 후)

• 문자열 자르기에 경우 where 문에 쿼리를 가공하지 말고 like 사용

where substr(업체명, 1, 2) = ‘대한’ -> where 업체명 like ‘대한%’

• 계산식의 경우 where 문에 하지 말고 우측에서 계산대입

where 월급여 * 12 = 36000000 -> where 월급여 =  36000000/12

• date 형식의 범위값 지정은 and 와 >=,< 범위로 사용하고 data 형식을 사용

where  to_char(일시, ‘yyyymmdd’) = :dt -> where 일시 >= to_date(:dt, ‘yyyymmdd’) and 일시 < to_date(:dt, ‘yyyymmdd’)+1

• 두 컬럼을 합쳐서 where 조건에 넣지 말고 값에서 함수 적용

where 연령 || 직업 = ’30공무원’ -> where 연령 = 30 and 직업 = ‘공무원’

 

4-3. 묵시적 형변환

인덱스 컬럼을 사용자가 명시적으로 가공하지 않더라도 조건절에서 비교되는 두 값의 데이터 타입이 다르면 내부적으로 형변환이 일어납니다.

number형 컬럼에 대하여 검색조건으로 숫자형이 맞지만, 실수로 문자형으로 코딩하는 경우가 있습니다.

Ex)  select * from emp where deptno = ’20’

—> 문자형과 숫자형이 만나면 옵티마이저가 문자형을 숫자형으로 변환해주는 DBMS가 제공하는 기능입니다.

 

 

5. 테이블 액세스 최소화

 

5-1. 인덱스 ROWID에 의한 테이블 랜덤 액세스

쿼리에서 참조되는 컬럼이 인덱스에 모두 포함되는 경우가 아니라면, 테이블 랜덤 액세스가 발생한다.

(Table Access By Index ROWID)

Ex) Execution Plan 중 TABLE ACCESS *BY INDEX ROWID) OF ‘컬럼명’

 

인덱스 ROWID에 의한 테이블 액세스 구조

인덱스에 저장돼 있는 rowid는 흔히 ‘물리적 주소정보’라고 일컬어진다. 오브젝트 번호, 데이터 파일 번호, 블록 번호 같은 물리적 요소들로 구성돼 있기 때문입니다.

물리적 위치정보로 구성되어 있지만, 인덱스에서 테이블 레코드로 직접 연결되는 구조가 아니기 때문에 ‘논리적 주소정보’라고 표현하기도 합니다.

 

* rowid는 메모리 상의 위치정보가 아니라 디스크 상의 위치정보로 데이터 블록을 읽을 때는 항상 버퍼 캐시를 경유하므로 메모리 상에서 버퍼 블록을 찾기 위해 해시 구조와 알고리즘을 사용합니다.

자세하게 설명하자면 아래와 같은 메커니즘을 말합니다.

 

인덱스 ROWID를 이용해 테이블 랜덤 액세스로 블록을 읽는 메커니즘

1. 인덱스 ROWID -> DBA(디스크 블록 위치 정보)를 해시함수로 해시 값 확인

2. 해시 값 -> 해시 버킷 검색

3. 버킷에 연결된 해시 체인 스캔하면 블록헤더를 검색

4-1. 블록 헤더에 저장된 포인터로 버퍼 블록 읽음

4-2. 블록 헤더를 못 찾을 경우 버퍼 공간에 적재하기 위해 공간 확보 및 직접 디스크 블록을 읽고 버퍼에 적재

 

이를 통해 항상 버퍼 캐시를 참색하는 것이 고비용 구조이고 접근 과정 중 내부 매커니즘도 존재하기에 그런 과정과 경합이 발생하여 블록 하나 읽는데 생각보다 많은 비용이 치러지고 성능 저하가 발생하여 해결하기 위해 테이블 랜덤 액세스를 줄여야 합니다.

5-2.  클러스터링 팩터

Clustering Factor라는 개념을 사용해 인덱스 ROWID에 의한 테이블 액세스 비용을 평가합니다.

클러스터링 팩터는 ‘군집성 계수(데이터가 모여 있는 정도)’로 번역되며, 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여 있는 정도를 의미합니다.

데이터가 물리적으로 근접해 있다면 흩어져 있을 때보다 데이터를 찾는 속도가 빨라집니다.

물리적으로 근접해 있으면 ROWID로 테이블 액세스 할 때 테이블 블록에 대한 포인터를 가지고 있어 다음 데이터를 액세스 할 때 이전 스캔한 ROWID를 가지고 있으므로 포인터를 다시 찾지 않고 재사용하기 때문에 블록 I/O가 적게 발생하여 속도가 빠릅니다.

인덱스 손익분기점

인덱스 ROWID에 의한 테이블 액세스는 생각보다 고비용 구조입니다. 따라서 일정량을 넘는 순간 테이블 전체를 스캔할 때보다 오히려 더 느려집니다.

Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점을 흔히 ‘손익분기점’ 이라고 부릅니다.

예를 들어 손익 분기점이 10% 라는 의미는 1000개 중 100개 레코드 이상을 읽을 때는 인덱스를 이용하는 것보다 테이블 전체를 스캔하는 것이 더 빠르다는 뜻입니다.

손익 분기점은 클러스터링 팩터에 따라 달라지며, 나쁘면 5%미만, 좋으면 90% 수준까지 올라갑니다.

정리하자면 클러스터링 팩터가 좋을 수록 데이터가 잘 모여있어 인덱스 스캔보다 테이블 전체를 스캔하는 것이 더 빠르다는 뜻입니다.

 

5-3.  인덱스 스캔이 테이블 전체 스캔보다 느리게 만드는 요인

1. 인덱스는 랜덤 액세스, 풀 스캔은 시퀀셜 액세스 방식

2. 디스크 I/O 시 인덱스는 Single Block Read 방식, 풀스캔은 Multi Block Read 방식

간단히 설명하자면

시퀀셜 액세스는 레코드 간 논리적 또는 물리적인 순서를 따라 차례대로 읽어 나가는 방식

랜덤 액세스는 레코드 간 논리적, 물리적 순서를 따르지 않고 한 건을 읽기 위해 한 블록 씩 접근하는 방식

Single Block Read는 한번의 I/O 하나의 데이터 블록만 읽어 메모리에 적재하는 것입니다.

Multi Block Read는 I/O 때 필요한 시점에 인접한 블록들을 같이 읽어 메모리에 적재하는 것입니다.

 

 

6.  테이블 액세스 최소화 튜닝

6-1.  기존 인덱스 컬럼 추가

해당하는 인덱스가 없어서 새로 인덱스를 만들어 여러 개의 인덱스를 관리하는 것보다 기존 인덱스에 컬럼을 추가하면 테이블 랜덤 액세스를 줄일 수 있습니다.

Ex) select /*+ index(emp emp_x01) */ ename, job, sal from emp where deptno = 30 and sal >= 2000;

인덱스 컬럼 추가 전 컬럼 : emp_x01(deptno, job)

인덱스 컬럼 추가 후 컬럼 : emp_x01(deptno, job, sal)

 

6-2.  Covered Index

필요한 모든 컬럼에 인덱스를 포함시키는 방법으로 인덱스만 읽어 처리하는 방식입니다.

 

6-3.  인덱스 클러스터 테이블

클러스터링 팩터 좋은 경우 참고

Ex) 

— 인덱스 클러스터 생성

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, … ) cluster c_dept( deptno );

 

 

6-4.  해시 클러스터 테이블

해시 함수에서 반환된 값이 같은 데이터를 물리적으로 함께 저장하는 구조입니다.

클러스터 키로 데이터를 검색하거나 저장할 위치를 찾을 때 해시 함수를 사용하는데, 해시 함수가 인덱스 역할을 대신하는 것이며, 해싱 알고리즘을 이용해 클러스터 키 값을 데이터 블록 주소로 변환 해줍니다.

대신 항상 ‘=‘ 조건으로만 검색되는 컬럼을 해시 키로 선정해야 합니다.

Ex)

— 해시 클러스터 생성

create cluster c_dept ( deptno number(2) ) hashkeys 4;

— 클러스터 테이블 생성

create table dept ( deptno number(2) not null, … ) cluster c_dept( deptno );

 

6-5.  배치 I/O(=부분 범위 처리)

대량의 데이터를 조회하면, 디스크 I/O 발생량도 함께 증가하므로 성능히 좋지 않다.

테이블 블록에 대한 디스크 I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리하는 방법입니다.

대신 배치 I/O 기능은 인덱스를 이용해서 출력하는 데이터 정렬 순서가 매번 다를 수 있다.

Ex) 힌트 명시 와 실행 계획

select /*+ batch_table_access_by_rowid(e) */ * from emp e where deptno = 20 order by job, empno;

실행계획에는 table access by index rowid batched 라고 출력됩니다.

 

7.  인덱스 스캔 효율화

앞서 랜덤 액세스 발생량을 최소화하는 방법에 대해 설명하였고, 시퀀셜 액세스 방식의 비효율을 해소하는 방법에 대해 설명해보자

 

7-1.  인덱스 선행 컬럼이 범위 조건일 때의 비효율(=)

인덱스 구성 컬럼이 조건절에서 모두 등치 ‘=‘ 조건으로 비교되거나 일부가 등치 ‘=‘ 조건이거나 조건절에 생략되는 것이 후행 컬럼이면 비효율이 없습니다.

Ex)

인덱스 컬럼 [컬럼1+컬럼2+컬럼3+범위1]

where 컬럼1 = a ;

where 컬럼1 = a and 컬럼2 = b ;

where 컬럼1 = a and 컬럼2 = b and 컬럼3 = c and 범위1 between d and f ;

 

반면 인덱스 선행 컬럼이 조건절에 누락되거나 between, 부등호(><), like 같은 범위 검색 조건이 사용되면 인덱스를 스캔하는 방식에 비효율이 발생하고 조건을 만족하지 않는 레코드까지 스캔하고 버려야 하는 경우가 생깁니다.

 

Ex)

인덱스 컬럼 [범위1+컬럼1+컬럼2+컬럼3]

where 컬럼1 = a ;

where 컬럼1 = a and 컬럼2 = b ;

where 컬럼1 = a and 컬럼2 = b and 컬럼3 = c and 범위1 between d and f ;

 

7-2.  범위 조건을 In-List로 전환(BETWEEN -> IN)

7-1의 경우처럼 인덱스 순을 변경하면 좋지만, 운영중인 시스템에서 인덱스 구성을 바꾸기가 어려운 경우가 있습니다.

이럴 때, 범위 조건을 다음과 같이 IN-List 로 바꾸어주면 효과를 얻을 수 있습니다.

인덱스에서 수직적 탐색이 발생하여 필요 없는 레코드를 스캔하지 않게 된다.

Ex)

where 범위1 in (‘d’, ’f’) and 컬럼1 = a and 컬럼2 = b….

실행계획에는 inlist iterator 라고 명시됩니다.

 

7-3.  범위 조건을 2개 이상 사용할 때의 비효율

like와 같은 범위 검색 조건이 2개 이상 사용하면 필터 역할을 하는 like는 대량의 데이터를 스캔할 때에는 비효율을 보여줍니다.

여러 like를 사용할 경우 나누어 만들어 주거나 UNION ALL을 이용하여 사용하는 방법이 있습니다.

 

7-4.  선두 컬럼에 OR 조건의 경우 인덱스를 타지 않는다.

7-5.  LIKE 보단 비교적 BETWEEN 사용

 

8. 인덱스 설계

인덱스 많으면 DML 성능 저하, DB 사이즈 증가, DB 관리 및 운영 비용 증가가 발생합니다.

 

8-1. 인덱스 설계 기준

1. 조건절에 항상 사용 및 자주 사용하는 컬럼 선정

2. ‘=‘ 조건을 선행 컬럼에 위치

3. 수행 빈도 고려

4. 업무상 중요도 고려

5. 좋은 클러스터링 팩터

6. 데이터 량 고려

7. 저장공간 고려

8. 필요없는 인덱스 제거