디비 이야기 19. 인덱스

1. 인덱스란?

= 빠른 검색을 위해 사용함 / 쿼리 처리속도를 향상시킴 

= 가장 많이 사용하는 검색 트리는 B-TREE다. 

주의) 인덱스를 사용하려면 추가 공간이 필요하며 데이터 변경이 자주 일어나면 성능 저하가 될 수 있음 

 

2. 인덱스 특징 

1) 데이터 베이스 시스템이 인덱스를 유지보수하여 사용자가 직접 조작하지 않아도 된다

2) 빠르게 찾아 디스크 엑세스 횟수를 줄인다

3) 언제든지 생성/삭제가 가능하여 다른 인덱스에 영향을 주지 않는다 

4) DB튜닝의 큰 효과를 볼 수 있음 

-> 과다사용은 금지

5) DB에 차지하는 공간이 많아서 TABLE SACAN보다 속도가 느려질 수 있다 

 

3. 인덱스 생성 

문법 : CREATE INDEX 인덱스명 on 테이블명(컬럼명) 

4. 인덱스 사용 조건

= WHERE 조건에 다중행 연산자 -> 단일행 연산자로 바꿔야한다 

-> 쿼리 튜닝이라 한다! 

5. 인덱스 종류

1) 고유, 비고유 인덱스 

문법 : CREATE UNIQUE INDEX 인덱스명 on 테이블명(컬럼명);

- 비고유 : 중복값 허용

- 고유 : 유일한 값을 가지는 컬럼에 대해 생성하는 인덱스다 

 

2) 결합 인덱스 

= 두개 이상의 컬럼을 생성시 사용

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

 

6. 인덱스 삭제 

문법 : DROP INDEX [스키마명.]인덱스명 

 

7. 장점 

1) SQL문 변경하지 않아도 성능 개선을 한다

2) 테이블 데이터에 영향을 주지 않는다

3) 일정한 효과를 기대가능

 

8. 인덱스 사용

1) 인덱스는 자동으로 생성되지 않는다.

2) PK컬럼은 PK를 생성시 자동으로 인덱스가 생성된다

-> PK 컬럼순서 참고하기. (http://blog.daum.net/whop369/177)

주의) 생년월일, 이름을 기준으로 하는 인덱스는 자동으로 생성되지 않는다!

 

9. 인덱스 종류

1) ORDERED

= 데이터가 정렬된 순서로 인덱스 생성관리

EX) B- TREE 알고리즘 / 오름,내림차순

 

2) HASH

= 직접 데이터에 키 값으로 접근하는 인덱스

특징1. 데이터 접근비용이 균일

특징2. 레코드(ROW)양에 무관

 

3) BITMAP

= 각 컬럼에 적은 개수의 값이 저장된 경우 선택 (OLAP성 업무에 적합)

= 수정변경이 적은 경우 유용

EX) 성별, 직급, 색상 등

 

4) FUNCTIONAL (함수기반 인덱스)

= 인덱스 설정하면 인덱스 가능 및 속도 향상

EX) CREATE INDEX IDX_EMP01_ANNSAL ON EMP01(SAL*12);

 

5) SIGNLED (단일 인덱스)

= 하나의 컬럼만으로만 인덱스를 지정하는 방식 

-> 업무 특성에 의해 주로 사용되는 컬럼이 하나인 경우

 

6) CONCATENATED (결합 인덱스)

= 복수개의 컬럼을 이용하여 인덱스 지정하는 방식

-> WHERE 조건으로 사용되는 빈도가 많은 경우 사용!

 

7) CLUSTERED 

= 데이터의 물리적 순서에 따라 인덱스가 생성

-> 특정 범위의 검색시 유리

 

10. 인덱스 조작

1) 인덱스 생성 

EX) CREATE [UNIQUE] INDEX <index_name> ON <table_name> <<column(s)>>

 

[인덱스 명령문 요소]

1) UNIQUE = 중복 허용하지 않음

2) index_name = 인덱스 테이블 이름

3) table_name = 인덱스 할 대상 테이블 이름

4) column = 인덱스 대상 테이블의 특정 컬럼 이름들

-> 복수 컬럼 지정가능

 

11. 인덱스 삭제 

= DROP INDEX <index name>;

 

12. 인덱스 변경

= ALTER [UNIQUE] INDEX <index name> ON <table name> (<<column(s)>>)

 

13. 인덱스 스캔 방식

1) index Range Scan 

= 인덱스 루트 블록 ~ 리프 블록까지 수직적으로  탐색 후 리프 블록을 필요한 범위만 스캔

 

2) index Full Scan

= 리프블록 처음부터 끝까지 수평적으로 탐색하는 방식

-> 데이터 검색을 위한 최적의 인덱스가 없을시, 차선으로 선택

 

3) index Unique Scan

= 유니크 인덱스를 '=' 조건으로 탐색하는 경우 작동함

-> 수직 탐색만으로 데이터를 찾는 방식

 

4) index Skip Scan 

= 인덱스 선두 컬럼이 조건절에 빠졌어도 인덱스를 활용하는 스캔 방식

 

5) index Fast Full Scan

= index Full Scan보다 빠른 스캔방식

= 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock Read 방식으로 스캔하는 방식

 

6) index Range Scan Descending

=  인덱스를 뒤~앞으로 스캔하기에 내림차순으로 정렬된 결과집합을 얻는다는 점만 다르다.