DB Index 란?

1) INDEX의 의미

RDBMS에서 검색속도를 높이기 사용하는 하나의 기술입니다.
INDEX는 색인입니다. 해당 TABLE의 컬럼을 색인화(따로 파일로 저장)하여 검색시 해당 TABLE의 레코드를 full scan 하는게 아니라 색인화 되어있는 INDEX 파일을 검색하여 검색속도를 빠르게 합니다.
이런 INDEX는 TREE구조로 색인화합니다. RDBMS 에서 사용하는 INDEX는 Balance Search Tree 를 사용합니다.
실제로는 RDBMS 에서 사용되는 B-Tree 는 B-Tree 에서 파생된 B+ Tree 를 사용한다고 합니다.
 
참고로 ORACLE이나 MSSQL에서는 여러종류의 TREE를 선택하여 사용가능하다.

2) INDEX의 원리

 
INDEX를 해당 컬럼에 주게 되면 초기 TABLE생성시 만들어진 MYD,MYI,FRM 3개의 파일중에서
MYI에 해당 컬럼을 색인화 하여 저장합니다. 물론 INDEX를 사용안할시에는 MYI파일은 비어 있습니다. 그래서 INDEX를 해당컬럼에 만들게 되면 해당컬럼을 따로 인덱싱하여 MYI 파일에 입력합니다. 그래서 사용자가 SELECT쿼리로 INDEX가 사용하는 쿼리를 사용시 해당 TABLE을 검색하는것이 아니라 빠른 TREE로 정리해둔 MYI파일의 내용을 검색합니다.
만약 INDEX를 사용하지 않은 SEELCT쿼리라면 해당 TABLE full scan하여 모두 검색합니다.
이는 책의 뒷부분에 찾아보기와 같은 의미로 정리해둔 단어중에서 원하는 단어를 찾아서 페이지수를 보고 쉽게 찾을수 있는 개념과 같습니다. 만약 이 찾아보기 없다면 처음부터 끝까지 모든 페이지를 보고 찾아야 할것입니다.

3) INDEX의 장점

- 키 값을 기초로 하여 테이블에서 검색과 정렬 속도를 향상시킵니다.
- 질의나 보고서에서 그룹화 작업의 속도를 향상시킵니다.
- 인덱스를 사용하면 테이블 행의 고유성을 강화시킬 수 있습니다.
- 테이블의 기본 키는 자동으로 인덱스 됩니다.
- 필드 중에는 데이터 형식 때문에 인덱스 될 수 없는 필드도 있습니다.
- 여러 필드로 이루어진(다중 필드) 인덱스를 사용하면 첫 필드 값이 같은 레코드도 구분할   수 있습니다.
  참고로 액세스에서 다중 필드 인덱스는 최대 10개의 필드를 포함할 수 있습니다.
 

4) INDEX의 단점

- 인덱스를 만들면 .mdb 파일 크기가 늘어난다.
- 여러 사용자 응용 프로그램에서의 여러 사용자가 한 페이지를 동시에 수정할 수 있는 병행성이 줄어든다.
- 인덱스 된 필드에서 데이터를 업데이트하거나, 레코드를 추가 또는 삭제할 때 성능이 떨어집니다.
- 인덱스가 데이터베이스 공간을 차지해 추가적인 공간이 필요해진다. (DB의 10퍼센트 내외의 공간이 추가로 필요)
- 인덱스를 생성하는데 시간이 많이 소요될 수 있다.
- 데이터 변경 작업이 자주 일어날 경우에 인덱스를 재작성해야 할 필요가 있기에 성능에 영향을 끼칠 수 있다.

따라서 어느 필드를 인덱스 해야 하는지 미리 시험해 보고 결정하는 것이 좋습니다. 인덱스를 추가하면 쿼리 속도가 1초 정도 빨라지지만, 데이터 행을 추가하는 속도는 2초 정도 느려지게 되어 여러 사용자가 사용하는 경우 레코드 잠금 문제가 발생할 수 있습니다.

또, 다른 필드에 대한 인덱스를 만들게 되면 성능이 별로 향상되지 않을 수도 있습니다. 예를 들어, 테이블에 회사 이름 필드와 성 필드가 이미 인덱스 된 경우에 우편 번호 필드를 추가로 인덱스에 포함해도 성능이 거의 향상되지 않습니다. 만드는 쿼리의 종류와 관계 없이 가장 고유한 값을 갖는 필드만 인덱스 해야 합니다.

5) INDEX의 목적

 
RDBMS에는 INDEX가 있습니다. 인덱스의 목적은 해당 RDBMS의 검색 속도를 높이는데 있습니다.
SELECT 쿼리의 WHERE절이나 JOIN 예약어를 사용했을때만 인덱스를 사용되며 SELECT 쿼리의 검색 속도를 빠르게 하는데 목적을 두고 있습니다.
※ DELETE,INSERT,UPDATE쿼리에는 해당 사항없으며 INDEX사용시 좀 느려집니다.
 

6) 인덱스를 생성해야 하는 경우와 그렇지 않은 경우

- 인덱스는 열 단위로 생성된다.
- WHERE절에서 사용되는 컬럼을 인덱스로 만든다.
- 데이터의 중복도가 높은 열은 인덱스로 만들어도 효용이 없다. (예 : 성별, 타입이 별로 없는 경우, 적은경우)
- 외래키가 사용되는 열에는 인덱스를 되도록 생성해주는 것이 좋다.
- JOIN에 자주 사용되는 열에는 인덱스를 생성해주는 것이 좋다.
- INSERT / UPDATE / DELETE가 얼마나 자주 일어나는지를 고려한다.
- 사용하지 않는 인덱스는 제거하자
 

7) 인덱스 주의사항

 * SELECT하는 경우에도 데이터 블록 수와 DB_FILE_MULTIBLOCK_READ_COUNT 값과 분포도 등에 따라 인덱스가 빠를 경우도 있고 full table scan보다 늦어지는 경우도 있음.

 7-1. DML에 취약함 

ⓐ INSERT
: index split 현상이 발생할 수 있음.
index split - 인덱스의 Block들이 하나에서 두 개로 나누어지는 현상 
-> 인덱스는 데이터가 순서대로 정렬 되어야 함. 기존 블록에 여유 공간이 없는 상황에서  그 블록에 새로운 데이터가 입력되어야 할 경우
오라클이 기존 블록의 내용 중 일부를 새 블록에다가 기록한 후 기존 블록에 빈 공간을 만들어서 새로운 데이터를 추가하게 됨.
*성능면에서 매우 불리 
① index split은 새로운 블록을 할당 받고 key를 옮기는 복잡한 작업을 수행. 모든 수행 과정이 Redo에 기록.. 많은 양의 Redo를 유발함
② index split이 이루어지는 동안 해당 블록에 대해 키 값이 변경되면 안되므로 DML이 블로킹됨.
enq:TX-index contention 대기 이벤트 발생(RAC- gc current split)

ⓑ DELETE
테이블에서 데이터가 delete 될 경우 - 지워지고 다른 데이터가 그 공간을 사용 가능
index에서 데이터가 delete 될 경우 - 데이터가 지워지지 않고, 사용 안 됨 표시만 해 둔다.
->즉, 테이블에 데이터가 1만건 있는 경우, 인덱스에는 2만건이 있을 수 있다는 뜻
=> 인덱스를 사용해도 수행속도를 기대하기 힘들다.

ⓒ UPDATE : 인덱스에는 update 개념이 없음!!!
테이블에 update가 발생할 경우 인덱스에서는 delete가 먼저 발생한 후 새로운 작업의 insert 작업이 발생한다. delete와 insert 두 개의 작업이 인덱스에 동시에 일어나 다른 DML보다 더 큰 부하를 주게 됨.

 7-2. 타 SQL 실행에 악영향을 줄 수 있음

갑자기 인덱스를 추가하면 잘 돌아가고 있던 쿼리에 옵티마이저가 실행계획을 바꾸는 경우가 생겨 갑자기 아주 느려지는 경우가 생김
-> 기존의 테이블에 인덱스를 추가할 경우 기존에 있던 SQL 문장들까지 전부 고려한 후 인덱스를 생성해야 함.

 

8) 생성방법

 - 자동생성 : PK나 Unique제약 조건을 정의할 경우 Unique Index가 자동으로 생성됨

9) 인덱스 종류

9-1. B-TREE

 B: binary, balance

   Root
   block : branch block에 대한 정보
      l
  Branch
   block : leaf blcok에 대한 정보
      l
    Leaf
   block : 실제 데이터들의 주소


ⓐ  UNIQUE INDEX : 인덱스 안에 있는 key 값에 중복되는 데이터가 없음 (성능 good)
 - 생성 문법
 SQL > CREATE UNIQUE INDEX 인덱스명
     2  ON 테이블이름(컬럼명 1 ASC|DESC, 컬럼명,....) ;

ASC : 오름차순 정렬 (기본값)
DESC  : 내림차순 정렬

SQL> create table dept2(no number, dname varchar2(10));

Table created.

SQL> create unique index idx_dept2_dname
  2  on dept2(dname);

Index created.


SQL> insert into dept2
  2  values(9100,'임시매장');

1 row created.

SQL> insert into dept2
  2  values(1006,'서울지사');

1 row created.

SQL> insert into dept2
  2  values(9101,'임시매장');
insert into dept2
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.IDX_DEPT2_DNAME) violated

// 이미 들어간 dname이라서 에러 발생

ⓑ Non UNIQUE INDEX : 중복되는 데이터가 들어가야 하는 경우
 - 생성 문법
 SQL > CREATE INDEX 인덱스명
     2  ON 테이블이름(컬럼명 1 ASC|DESC, 컬럼명,....) ;

예 - professor table의 position 컬럼에 Non UNIQUE INDEX를 생성
SQL > create index idx_prof_position
    2   on professor(position);

ⓒ Function Based INDEX ( FBI - 함수기반 인덱스 )

* 인덱스는 where절에 오는 조건 컬럼이나 조인에 쓰이는 컬럼으로 만들어야 함
** 인덱스를 사용하려면 where 절의 조건을 절대로 다른 형태로 가공해서 사용하면 안된다.

꼭 써야 할 때
SAL + 100 = 200 이라는 형태로 써야할 때
-> 인덱스도 SAL+100 형태의 인덱스를 생성= 함수기반 인덱스 

SQL> create index idx_prof_pay_fbi
  2  on professor(pay+100);

Index created.

professor table에 pay+100이라는 컬럼이 없지만 인덱스를 만들 때 저 연산을 수행해서 인덱스를 만들어줌.

-> 임시적인 해결책은 될 수 있어도 근본적 처방은 아님
-> pay + 100을 생성했는데 쿼리의 조건이 변경되면 인덱스를 다시 생성해야 함
-> FBI는 기존 인덱스를 활용할 수 없다.(단점)

ⓓ DESCENDING INDEX : 내림차순으로 인덱스를 생성함
큰 값을 많이 조회하는 SQL에 생성하는 것이 좋음!
ex ) 계좌내역 최근 날짜부터 조회, 회사 매출

SQL> create index idx_prof_pay
  2  on professor(pay desc);

Index created.


하나의 메뉴에 오름차순과 내림차순을 한번에 조회 할 경우
: 오름차순, 내림차순 두 개의 인덱스를 만들면 DML의 성능에 악영향을 미침
-> 힌트를 사용 ( 아래나 위에서부터 읽도록 할 수 있음)

ⓔ 결합 인덱스 (Composite INDEX) : 인덱스 생성시 두 개 이상의 컬럼을 합쳐서 인덱스 생성
주로 where 절의 조건 컬럼이 2개 이상이 and로 연결되어 사용되는 경우 많이 사용
-> 잘못 생성하게 되면 성능에 나쁜 영향을 미침!!

예) 사원 테이블에 총 50명이 있고, 남자 25명, 여자 25명
여자 중 이름이 '유관순'인 사람이 2명

사원 테이블에서 성별이 '여자' 이고 이름이 '유관순'인 사람을 찾을 때 :
SQL > SELECT 이름, 성별
FROM 사원
WHERE 성별 = '여자'
AND 이름 = '유관순';

* 결합 인덱스 생성 구문 예 :

SQL > CREATE INDEX idx_사원_성별_이름
   2  ON 사원(성별,이름);

**** 결합 인덱스 생성시 컬럼의 배치 순서 

case 1 : ON 사원(성별, 이름) 

50 명 -> 여자 -> 25명 -> 유관순 -> 2명
        50                      => 25회 검사

case 2 : ON 사원(이름, 성별)

50명 -> 유관순 -> 2명 -> 여자 -> 2명
        50                      => 2회 검사


=> 같은 테이블에 같은 SQL이지만 결합 인덱스를 어떻게 생성하는가에 따라 속도나 검사 횟수가 완전히 달라지게 된다. (신중히 생성...)

9-2. BITMAP INDEX 

: 데이터 값의 종류가 적고 동일한 데이터가 많을 경우에 많이 사용
성별 컬럼 : 남. 여 
 
Bitmap index를 생성하려면 데이터의 변경량이 적어야 하고, 값의 종류도 적은 곳이 좋다.
일반적으로 OLAP환경에서 많이 생성하게 됨. 
무조건 적으로 생성해야 하는 것이 아니고, 테이블 성격이나 데이터를 종합적으로 분석해서 적절한 인덱스를 생성한다. 

BITMAP INDEX는 어떤 데이터가 어디있다는 지도정보(map)를 Bit로 표시한다. 
데이터가 존재하는 곳은 1로, 데이터가 없는 곳은 0으로 표시  정보를 찾을 때 1인 값만 찾음 !!! 

SQL > create BITMAP index dex_사원_성별_bit   2  on 사원(성별);
bitmap index를 생성하면 성별 컬럼 값의 종류대로 map이 생성됨.
남자 : 1   0   1   0   0
여자 : 0   1   0   1   1

bitmap index를 사용하고 있는데 만약 컬럼 값이 새로 하나 더 생길 경우? 
기존의 BITMAP INDEX를 전부 수정해야 함. 
-> B-TREE INDEX는 관련 블록만 변경되면 되지만 BITMAP INDEX는 모든 맵을 다 수정해야 한다는 큰 문제점
-> BITMAP INDEX는 블록 단위로 lock을 설정해서 같은 블록에 들어있는 다른 데이터도 수정작업이 안 되는 경우가 종종 생김 

9-3. 데이터 처리 방법  

- OLTP ( Online Transaction Processing - 실시간 트랜잭션 처리) : 실시간으로 데이터 입력과 수정이 일어나는 환경 - B -TREE 인덱스 많이 사용
- OLAP ( Online Analytical Processing - 온라인 분석 처리) : 대량의 데이터를 한꺼번에 입력한 후 주로 분석이나 통계 정보를 출력할 때 사용하는 환경 - BITMAP 인덱스 많이 사용

10) 인덱스의 종류

  10-1. 고유 인덱스(Unique Index)

- 고유 인덱스는 유일한 값을 갖는 컬럼에 대해서 생성하는 인덱스로 고유 인덱스를 지정하려면 UNIQUE 옵션을 지정해야 합니다.
   SQL> CREATE UNIQUE INDEX  idx_ukempno_emp ON emp(empno);

 10-2. 비고유 인덱스(NonUnique Index)

 10-3. 단일 인덱스(Single Index)

- 단일 인덱스는 한 개의 컬럼으로 구성한 인덱스를 말합니다.
  SQL> CREATE INDEX  idx_ukempno_emp ON emp(empno);

 10-4. 결합 인덱스(Composite Index)

- 결합 인덱스는 두 개 이상의 컬럼으로 인덱스를 구성하는 것을 말합니다. 부서 번호와 부서명을 결합하여 인덱스를 설정 해 보도록 하겠습  니다.
  SQL> CREATE INDEX  idx_dept_com ON index_dept(deptno, dname);

 10-5. 함수 기반 인덱스(Function Based Index)

- 함수 기반 인덱스는 SAL*12와 같이 컬럼에 어떠한 산술식을 수행했을때를 말합니다.
  SAL컬럼에 INDEX가 걸려있다해도 SAL*12은 INDEX를 타지 못합니다. 이럴때 함수 기반 인덱스를 생성합니다.
  SQL> CREATE INDEX  idx_annsal_emp ON emp(sal*12);

11) 인덱스 구조와 작동 원리 (B-TREE 기준)

테이블과 인덱스 비교
- 테이블은 컬럼이 여러 개, 데이터가 정렬되지 않고 입력된 순서대로 들어감
-> 인덱스는 컬럼이 key 컬럼과 ROWID 컬럼 두 개로 이루어져 있음 ( 오름차순, 내림차순 정렬가능)

Key : 인덱스를 생성하라고 지정한 컬럼의 값


select *
from emp
where empno=7902;

데이터 파일의 블록이 10만개 일 때, SQL을 수행시
1. 서버 프로세스가 파싱 과정을 마친 후 DB buffer cache에 empno 가 7902인 정보가 있는지 확인
2. 정보가 없으면 하드 디스크 파일에서 7902정보를 가진 블록을 복사해서 DB buffer cache로 가져온 후 7900 정보만 골라내서 사용자에게 보여줌
이 때
index 없는 경우 -  7902정보가 어떤 블록에 들어 있는지 모르므로 10만개 전 부 db buffer cache로 복사한 후 하나하나 찾음
index 있는 경우 - where 절의 컬럼이 index가 만들어져 있는지 확인 후, 인덱스에 먼저 가서 7902정보가 어떤 ROWID를 가지고 있는지 확인한 후 해당 ROWID에 있는 블록만 찾아가서 db buffer cache에 복사함.

12) 단일 컬럼 인덱스

 12-1. 인덱스를 고려 해야 하는 경우

- WHERE 조건 절에 자주 사용되는 컬럼
- 자주 바뀌는 않는 컬럼

 12-2. 변경 작업에 따른 인덱스에 부하 감소

- 동일 값이 작은 컬럼(1~15% 이하, 5%이하)

 12-3. 인덱스를 피해야 하는 경우

- 함수 및 연산자에 의해 변경되는 컬럼
- 낮은 선택도
- 균일 분포가 아닌 컬럼

 12-4. 조합 인덱스가 단일 인덱스 보다 많은 장점이
있지만 2개의 단일 인덱스 사용이 성능 상 좋다.

13) 조합 컬럼 인덱스

 13-1. 조합 인덱스의 큰 장점

- 선택도가 좋지 않은 두 개 이상의 컬럼을 조합하여,선택도가 좋은 조합 인덱스가 된다.
- 질의 컬럼이 모두 조합 인덱스에 있는 경우, 물리적인 데이터 블록을 읽을 필요가 없다.

 13-2. 조합 인덱스를 고려해야 하는 경우

- AND 연산자에 의해 자주 같이 질의 되는 컬럼들
- 다수의 여러 개의 질의가 특정 컬럼들을 질의

 13-3. 인덱스 가이드라인

- 자주 사용되는 컬럼을 왼쪽
- 동일한 사용 빈도이면 선택도가 좋은 것을 왼쪽

14) 새로운 인덱스 추가 시 문제점

 14-1. 기존 인덱스에 의해 잘 운용되던 환경 가정

 14-2. 새로운 SQL 문장을 위해 새 인덱스 필요성 발생

- 새로운 인덱스에 따른 테이블 관리 비용 상승
- 기존 SQL 문장이 혼동(Upset)될 가능성 커짐

 14-3. 잘 돌던 SQL이 새로운 인덱스를 타는 경우

 14-4. 새로운 인덱스의 추가는 신중해야 한다.

- 기존의 SQL에 미치는 영향 최소화

15) 효율적인 인덱스 및 SQL 활용 방안

 15-1. 각 테이블에 적당한 수의 인덱스 사용

 15-2. 가능한 Unique 인덱스 생성

 15-3. 전체 테이블의 5%이상 되는 SQL 질의 회피

- 인덱스 사용 비용이 더 높음

 15-4. 인덱스가 타지 않는 SQL 문장 실수 주의

 15-5. 정렬 작업의 최소화

- DISTINCT, ORDER BY, GROUP BY
- 정렬이 최소화되는 인덱스 활용

 15-6. IN 보다는 EXISTS(정렬최소화)

 15-7. 선택도가 좋은 선행 테이블을 FROM 절에 맨마지막에

16) INDEX가 동작하지 않는 경우

- 인덱스 컬럼 절의 변형
- 내부적인 데이터 변환
- NULL 조건의 사용
- 부정형 조건의 사용
- LIKE 연산자 사용
- 최적기가 판단

* reference

• http://2factor.tistory.com/30
• http://javapia.blogspot.kr/2010/07/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4db%EC%9D%98-%EC%9D%B8%EB%8D%B1%EC%8A%A4index%EB%9E%80.html
• http://yysvip.tistory.com/183
• http://ynebula38.tistory.com/75
• http://choko11.tistory.com/entry/%EC%9D%B8%EB%8D%B1%EC%8A%A4-1-%EA%B0%9C%EB%85%90%EC%A2%85%EB%A5%98%EC%A3%BC%EC%9D%98%EC%82%AC%ED%95%AD
• http://gyh214.tistory.com/63