(RDBMS) 인덱스에 대해서

인덱스는 이름 그대로 무언가를 빠르게 찾기 위한 색인이다. 

즉 테이블 내에서 특정 조건에 대한 검색을 속도를 향상시킨다. Full Scan을 방지하기 위해 사전에 색인하는 것이라보면 되겠다.

다만, 인덱스도 하나의 테이블이기 때문에 인덱스 테이블 스페이스가 필요하다(용량이 소모됨) 

우선 Index 방식이 유용한 때와 그렇지 않은 때를 구분하자.

 

Index 방식이 유용한 경우

• Row수가 매우 긴 경우
• PK, FK 등 완전히 Unique하거나, 값의 분포도가 고른 컬럼이 존재하는 테이블
• 쿼리의 WHERE 조건에서 자주 참조되는 테이블
• 큰 테이블에서 적은 데이터 참조가 자주 발생할때 (예, 재직구분에서 휴가 중인 사람을 검색)
• 다른 테이블과 순차적 JOIN(Nested Join)이 발생하는 경우
• 랜덤방식의 테이블 조회가 많은 경우

 

※ 분포도와 손익분기점

분포도 = 데이터별평균로우수 / 테이블의 총 로우수 X 100 = 1 / 컬럼값의 종류 X 100
=> 인덱스를 생성하려는 컬럼의 분포도는 10~15% 이하여야 한다.

 

 

Index 방식이 유용하지 않은 경우(사용하면 안되는 경우)

• Update, Insert, Delete가 빈번한 테이블(Index Split 현상 발생 가능)
• Row수가 적은 테이블
• Index 후보가 되는 Column 값의 분포도가 고르지 않은 테이블

 

Index 종류는 크게 다음과 같다. 

1. 비클러스터형 인덱스

1) 단일 컬럼 인덱스(Single Column Indexes)

오직 하나의 컬럼만 인덱스로 가짐

CREATE INDEX 인덱스이름
ON 테이블이름 (컬럼이름);

2) Unique 인덱스(Unique Indexes)

지정된 컬럼(필드)의 값 고유함

CREATE UNIQUE INDEX 인덱스이름
ON 테이블이름 (컬럼이름);

3) 조합(연결) 인덱스 (Composite Indexes)

조합 인덱스는 두 개 또는 그 이상의 컬럼을 테이블에 가짐

CREATE INDEX 인덱스이름
on 테이블이름 (컬럼이름1, 컬럼이름2, ...);

결합인덱스의 첫번째 컬럼이 조건에서 사용되지 않으면 그 인덱스는 사용되지 않는다. 첫번재 컬럼은 처리범위의 결정에도 영향을 미친다. (첫번째 컬럼이 매우 중요하다. 그래서 분포도가 좋은 컬럼을 우선한다.)

 

 

2. 클러스터형 인덱스

• 인덱스할 페이지 전체를 다시 정렬한다. 
• 재 정렬작업이므로 부하가 발생할 수 있어 업무시간은 피해서 수행한다. 
• 클러스터형 인덱스는 인덱스 자체의 리프페이지가 곧 데이터 페이지다. 즉, 인덱스 자체에 데이터가 포함되어 있다고 말할 수 있다. 
• 비 클러스터형 보다 검색이 빠르다. 
• 테이블에 하나만 생성할 수 있다. 그래서 어느 열에 생성하느냐에 따라 성능이 달라진다. 

 

Index 주 시나리오

인덱스 Merge CASE

SELECT *
  FROM TAB1
 WHERE COL1 = 'ABC'
   AND COL2 = 123;

1. B*Tree 방법으로 COL1='ABC' 와 COL=123인 첫 번째 로우를 찾는다.
2. 두 개의 로우에 있는 ROWID를 비교하여 적은 값을 가진 쪽을 먼저 스캔 한다. ROWID가 더 커질 때까지 스캔 한 후 멈춘다. (col2 rowid = 7~32)
3. COL1 인덱스의 ROWID를 ②의 방법으로 차례로 스캔. (col1 rowid = 32~67)
4. COL2 인덱스를 COL1의 ROWID보다 커질 때까지 스캔. (col2 rowid = 32~67)
5. COL1 인덱스의 다음 로우를 액세스 하려고 할 때 값이 'ABC' 가 아니므로 중단.

위와 같이 인덱스 머지가 발생을 하면,
COL2 인덱스를 사용하지 않고 COL1 인덱스만 사용하는 것이 유리.

 

 

COL2인덱스를 사용하지 않고 COL1인덱스만 사용.
1. B*Tree 방법으로 COL1 인덱스에서 값이 'ABC'인 첫 번째 로우를 찾는다.
2. ROWID 정보를 이용하여 테이블의 로우를 엑세스 한다.
3. 테이블 로우에 있는 COL2 값이 123인 것만 취한다.
4. COL1 인덱스를 차례대로 스캔 하여 위의 방법을 반복하여 'ABC'가 아니면 작업을 중단.

 

특별한 경우를 제외하고는 인덱스 머지를 하는 것보다는 분포도가 좋은 하나의 인덱스만 사용하는 것이 유리하다.

• 분포도가 아주 양호하지 않더라도 인덱스를 생성해야만 하는 경우 결합인덱스를 사용하면 액세스 속도가 향상된다.
• 결합인덱스는 인덱스 머지를 하여 성공한 결과들을 저장하고 있기 때문에 그 만큼 유리하다.

 

Merge시 주의사항은 다음과 같다.

 

• 조건절에서 인덱스의 첫번째 컬럼을 사용하지 않으면 인덱스는 사용되지 않는다.
• 결합인덱스를 구성하는 컬럼의 순서가 중요하다.
  (모두 =로 사용되는 경우에는 실제 일량에는 차이가 없다)

• 결합인덱스의 앞선 컬럼이 '='로 사용되지 않을때 그 뒤의 컬럼은 '='로 사용했더라도 처리할 범위는 줄어들지 않는다. 따라서 앞선 컬럼들의 '=' 비교가 중요하다.

Merge Index시 컴럼순서 결정방법

• 항상 사용되는가?
  결합인덱스의 첫번째 컬럼이 조건에서 사용되지 않으면 그 인덱스는 사용되지 않는다. 첫번째 컬럼은 처리범위의 결정에도 영향을 미친다.
• 항상 '='로 사용되는가?
  만약 '='로 사용되지 않는다면 설사 두번째 컬럼이 '='로 사용된다 하더라도 처리범위는 줄어들지 않는다.
• 분포도가 좋은 컬럼 우선
  처리범위를 줄일 수 있는 컬럼을 먼저 기술
• 자주사용되는 정렬의 순서 우선
  인덱스는 결합된 컬럼의 순서대로 저장되므로 정렬순서대로 구성되어 있으면 유리
• 어떤 컬럼을 추가
  처리범위를 줄이는데 많은 기여는 못하지만, 랜덤액세스를 줄여주거나, 테이블을 엑세스하지 않고 인덱스만 액세스 하도록 유도하는 등