2012.01.11 SQL 7일차(Index) ; 정의, 생성원리, 작동원리, 단점(index split 등등), 인덱스의 종류, 결합인덱스의 효율에 관한 이슈, REBUILD 등 INDEX의 전반적인 내용

인덱스(INDEX)

1. 정의 :  한마디로 데이터의 주소록, 데이터를 쉽고 빠르게 관리하려고 만듬

풀어서 설명하면

아파트에 누굴 찾고 싶은데 한집, 한집  , 한집 , 한집 , 한집 일일이 찾지 않고
관리실에 가서 해당되는 사람의 주소를 보고 바로 찾는 방법
(위 상황에서 관리실의 주소록 : INDEX) 

2. 생성원리
FULL SCAN(LOCK) ---------> SORT
(인덱스를 만드려면 정렬을 해야합니다.; 목록을 생성한 후 정렬을 해서 저장합니다.)
 

풀어서 설명하면, 도서관에 정리 안된 책장을 정리해서 목록을 만들고 싶은데, 

일단 목록을 만드려면 무슨 책들이 있는지 알아야 합니다.

그래서 책들을 일일이 다 살펴봐야합니다(FULL SCAN)

그런데 중간에 누가 빌려가버리고, 다시 꽂아넣고 하면 헷갈리므로 일단 책장에 테이프를 발라서 

누가 못빼가거나, 못넣거나 하게 해야 합니다(LOCK)

이 후에 목록들을 모아서 정렬을 하면 됩니다(SORT)

3. 작동원리
(아래의 표를 참조하여 설명합니다.)

MEMBER					블록(하드에 저장되는 위치)
ROWID	이름	주소	연락처	급여		1	2	3
A1	홍길동	강남	1111	20	A	홍길동행
B3	나한일	대구	1234	500	B			나한일행
C2	강감찬	부산	5554	80	C		강감찬행
D1	전진	남산	5674	30	D	전진행

만약에 이름을 기준으로 인덱스를 만든다고 하면, 아래처럼 생성

idx_이름
ROWID	Key(정렬)
C2	강감찬
B3	나한일
D1	전진
A1	홍길동

주소를 기준으로 만든다면 

idx_주소
ROWID	Key(정렬)
A1	강남
D1	남산
B3	대구
C2	부산

식으로 생성이 됩니다.(index는 key값으로 정렬이 됩니다)
------------------------------
 
만약에 사용자가
select * from member
where 이름 = '홍길동';
을 실행시키게 되면

서버프로세스는 딕셔너리한테 이름 인덱스가 있냐? 라고 물어보고
있으면 인덱스에서 홍씨로 검색해서 '홍길동'을 찾은다음 해당블럭 A1에 바로 찾아가서 해당 행을 메모리에 불러들입니다.

select * from member
where 연락처=1234;
을 실행하게 되면

딕셔너리에 연락처 인덱스가 없으므로, 모든 블록을 메모리로 끌어올려서 일일이 다 찾게 됩니다.

위에서 볼 수 있듯이, 인덱스는 where절에 오는 컬럼으로 생성해야 합니다.


그러면 문득, 이런 의문이 듭니다.

이렇게 좋은건데 모든 컬럼 전부 인덱스를 만들어 놓으면 되지 않냐?
      결론적으로 말하면 안됩니다.(해당 테이블이 select만 사용한다면 쌍수를 들고 써야되지만 그런 경우는 없으므로) 
     이유는 아래쪽에 설명합니다.

인덱스의 단점:
1. DML에 취약하다

1) insert의 경우(Index Split 발생)
        :  인덱스가 만들어져 있는 컬럼마다 각각 모두 아래처럼 추가시켜줘야되고,
         이 과정에서 Index Split이 발생해서 인덱스의 성능이 저하

idx_이름
key         rid(rowid)                                                 key      rid
-----     ----                                                                -----     ---- 
강감찬    C2                           도루묵(rid:B1) insert           강감찬    C2 
나한일    B3                       ----------------------->       나한일    B3 
전진       D1                                                                  도루묵    B1
홍길동    A1                                                                  -----    ----
                                                                             oracle에서 자동으로 쪼개버립니다.
                                                                         이렇게 쪼개진 것을 Index Split가 발생했다고 합니다.
                                                                                   -----    ---- 
                                                                                   전진       D1
                                                                                   홍길동    A1 


2) delete의 경우
      : 테이블에서 행을 지워도, 인덱스쪽에서는 지우지 않고, 비활성이라고 표시만 합니다.(인덱스의 데이터는 자동으로 지워지지 않아요)

테이블 : 10만행  -----> 9만행 삭제(총 1만행)  ----->  15만행추가(총 16만행)  -----> 14만5천행 삭제(총 5천행)
INDEX : 10만행  ----->  그대로 10만행           ----->  총(10+15= 25만행)       ----->  그대로 25만행

위 처럼 테이블은 몇건 안되도, 인덱스의 내용은 어마어마해질 수 있습니다.(이 경우 REBUILD를 해줘야합니다.)


3) update경우
     : 인덱스는 업데이트라는 개념이 없고, 위의 2가지를 조합해서 인덱스를 안쓰겠다고 표시만하고, insert(Index Split발생) 하게 됩니다.
(update의 경우는 index에게 최악입니다.)
* 그래서 대량으로 update가 필요할 때는, 인덱스를 지워버리고 table UPDATE한 후 다시 인덱스를 생성하기도 합니다.

2. 타 sql문에 악영향

이런 경우가 발생합니다.

평소에는 0.1초만에 실행되던 쿼리가, 인덱스를 만들고 나서 5초가 걸릴 수 있습니다.
(물론 내가 생각하기에는 전혀 상관없어보이는 컬럼의 인덱스를 만들었습니다.
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------ 

4. 인덱스의 사용 : b-tree인덱스, bitmap인덱스가 있는데 여기선 b-tree인덱스만 다루겠습니다.

1) WHERE 절이나 조인 조건 절에서 자주 사용되는 칼럼

2) 전체 데이터 중에서 4~5% 이내의 데이터를 검색하는 경우(애매합니다잉, 보통 Optimizer가 정해줍니다.)

3) 두개 이상의 칼럼이 WHERE 절이나 조인 조건에서 자주 사용되는 경우

4) 테이블에 저장된 데이터의 변경이 드문 경우

5. 인덱스의 생성
생성문법

       SQL> CREATE [UNIQUE] INDEX index

   ON table (column1 [ASC| DESC] [, column2 [ASC|DESC],…]);

    * UNIQUE : 고유 인덱스 지정

    * ASC | DESC : 인덱스 키를 오름차순 또는 내림차순으로 정렬


6. 인덱스의 종류

* UNIQUE INDEX
       : index 중 성능이 가장 좋고, 한 테이블에 여러개 쓸 수 있습니다.
        성능이 가장 좋은 이유는, 해당 자료를 찾았을때 무조건 그 값밖에 없다는 걸 알기에 가장 성능이 좋습니다.
            (일반 index를 생각해보면, 해당 자료를 찾았어도 또 같은 값이 있나 한번 더 검색해봐야합니다.) 

        그래서 Optimizer는 일반index, unique index중 무조건 unique index를 쓰는 쪽으로 선택을 합니다.
   (* 가능한한, 가능하면 무조건 UNIQUE INDEX로 만들어줘야합니다.)

* DESENDING INDEX
   : 인덱스의 기본정렬은 오름차순인데 이 경우는 내림차순의 인덱스입니다.
   큰 값을 먼저 조회하는 쿼리는 DESENDING INDEX가 유리합니다. 보통 날짜컬럼은 desending index를 많이 사용합니다.
     (예 : 인터넷뱅킹의 입출금 조회, 메일목록, 글검색 등등)

* 결합인덱스
  : 두 컬럼 이상을 결합하여 생성되는 인덱스

사용예)

    학생 테이블의 deptno, grade 칼럼을 결합 인덱스로 생성하여라. 결합 인덱스

    이름은 idx_stud_dno_grade 로 정의한다.

  SQL> CRAETE INDEX idx_stud_dno_grade

ON student (deptno, grade);

   ** 결합인덱스의 경우는 생각 많이 해보셔야 합니다.(지겨우시더라도 아래 꼭 읽어보세요) 
만약 where deptno = 101 AND sal>5000; 을 실행시켰을때, 
인덱스를 만드는 법은
1. 각각     ------> 이렇게 만들었으면 짐싸들고 집에 가세요
2. (deptno, sal)
3. (sal, deptno)
가 있습니다.
이중 어떻게 만드는게 효과적일까요?


2. (deptno, sal)입니다.  
결론적으로 말하면 처음 한번 검사했을때 3번보다 범위를 더 많이 줄여주기 떄문에 2번이 효과적입니다.

다른 예를 보도록 하죠.
한반에 30명이 있다고 하고, 남자는 25명, 빨간펜가진 사람은 2명이라고 합니다.

where 성별='남' AND 빨간펜='Y' 을 실행시켰을 때

        1.         남자 찾고 -------------->  빨간펜
        2.         빨간펜 찾고 -------------->  남자

1.의 경우 일단 남자를 찾으려면 무조건 full scan한번은 해야 합니다.(30번비교)
                                                 + 빨간펜 찾으려면 남자25명중에서 찾아야합니다(25번비교)
     총 30+25 = 55번 비교를 하게 됩니다.

2.의 경우 일단 빨간펜을 찾으려면 무조건 full scan한번은 해야 합니다(30번비교)
                                                + 남자찾으려면 2명중에서 찾으면 됩니다(2번비교)
     총 30+2 = 32번 비교를 하게 됩니다.

이런식으로 비교횟수가 대폭 차이가 나버리기 때문에 상당히 고심하고 결합인덱스를 만드셔야 합니다.
만약에 3개의 컬럼을 이용해서 만든다면 3!개의 종류가 나오겠죠. 이 중 하나가 제일 효율적입니다.
(8개면 8!개 ... 헉..등등)

* 함수기반인덱스(FBI)
    index를   sal+100 같은 형식으로 만드는 것.
    왜 쓰느냐?
        sal 인덱스가 있다고 하여도, where sal+100 > 500;을 써버리면 sal인덱스는 사용하지 않습니다.
       (오라클은 where와 '연산자' 사이의 하나의 컬럼으로 취급하기 때문에
               sal과 sal+100은 다른 컬럼으로 생각합니다.
        그래서 인덱스가 있는 컬럼은 where절에서 최대한 연산을 사용하지 말아야 합니다.)
 
    언제 쓰느냐? 위와 같은 함수(sal + 100) 만 고정적으로 사용한다면 사용하세요. oracle의 입장은 사용을 권장하지 않습니다.

7. 인덱스의 관리
조회

    인덱스에 대한 정보는 user_indexes , user_ind_columns 로 조회 가능함

    사용예)

      학생 테이블에 생성된 인덱스를 조회하여라.

     SQL> SELECT index_name, uniqueness

 FROM user_indexes

 WHERE table_name= ‘STUDENT’;

     

     SQL> SELECT index_name, column_name

 FROM user_ind_columns

 WHERE table_name= ‘STUDENT’

삭제

문법) DROP INDEX index;

 

사용예)

  학생 테이블에 생성한 fidx_stud_no_name 인덱스를 삭제하여라.

  SQL> DROP INDEX fidx_stud_no_name;

재구성

테이블에 정의된 칼럼 값에 대해 변경 사항이 자주 발생할 경우 인덱스 키의

 정렬 순서를 유지하기 위하여 노드값을 조정해 주는 작업을 의미.

 문법)

   ALTER INDEX [schema.] index REBUILD

   [TABLESPACE tablespace]

 사용예)

   학생 테이블에 생성된 stud_no_pk 인덱스를 재구성하세요.
 

   SQL> ALTER INDEX stud_no_pk REBUILD;

* 재구성은 Index Split와 사용하지 않는 인덱스값(delete로 인한)을 전부 수정해 줍니다.
* 망가졌으면 그냥 지우고, 인덱스 다시 만들면 되지 않느냐? 는 생각을 할 수 있는데,
일반적으로 많이... 망가졌으면 지우고 다시 만드는게 좋고, 적당히 망가졌으면 REBUILD하는게 좋습니다.
(물론 REBUILD하는 시간이 다시 만드는 시간보다는 빠릅니다. --> 정렬이 이미 되어있으니까)