데이터베이스 INDEX 개념과 INDEX 사용방법 알아보자. – by.SC

 

DATABASE INDEX

 

 

* 인덱스(INDEX) : 어떤 데이터가 어디 위치하였는지 위치 정보를 가진 주소록 개념

 

 

1>   인덱스가 필요한 이유

 

일반적으로 SELECT 구문을 통해 데이터 조회 요청

 

DB 서버 프로세스는 Memory (DB 버퍼 캐시) 먼저 확인

 

메모리(DB 버퍼 캐시)에는 자주 사용되는 테이블이 캐싱 저장

 

원하는 데이터가 메모리(DB 버퍼 캐시)에 없는 경우 실제 하드 디스크의 데이터 파일에서 복사해 온 다음 리턴

 

-> 메모리에 데이터가 있어 조회할 경우 빠른 쿼리 수행속도 조회

 

-> 반대로, 원하는 데이터가 메모리에 없으면 하드의 모든 블록 테이블을 조회 (Table Full Scan)

 

 

테이블 풀 스캔을 막기위해 ‘데이터의 주소록’ 인덱스를 만들어서 데이터 관리하는 것 입니다.

 

결론적으로 원하는 데이터 블록 주소(ROWID)를 찾는 개념입니다.

 

 

-       ROWID 구성

 

예시 : AABSmhAAkAAAIb7AAB

 

AABSmh : 데이터 오브젝트 번호

 

AAk : 파일번호

 

AAAIb7 : BLOCK 번호

 

AAB : ROW 번호

 

 

2>   인덱스 구조 &원리

 

 

-       기본원리 : 테이블 전체 스캔 ->정렬 -> 블록 기록

 

-       인덱스로 지정한 컬럼(Key) 값과 해당 컬럼(Key)에 해당하는 ROWID로 구성

 

-       인덱스가 생성되면 where 절에 해당 컬럼의 인덱스가 생성되어 있으면,

먼저 해당 데이터가 있는 ROWID 부터 검색해서 해당 블록만 가져옵니다.

 

 

 

3>   인덱스 종류

 

 

# B-TREE 인덱스 : 주로 실시간 처리 시스템 (OLTP)

 

-       일반적으로 아는 TREE 구조로 Root Block이 있고, 그 아래 가지방식으로 Branch Block, Leaf Block 형태로 구성

 

-       데이터 값 종류가 많고, 동일한 데이터가 적은 경우 사용

 

 

B-TREE INDEX 구조

 

 

1.     UNIQUE INDEX

 

지정한 컬럼으로 생성한 인덱스의 해당 컬럼 데이터가 중복으로 들어갈 수 없습니다.

 

 

<INDEX 생성 문법>

 

CREATE UNIQUE INDEX 인덱스명칭

ON 테이블명 (컬럼명1 ASC | DESC, 컬럼명, ……..);

 

->  기본적으로 오름차순 ASC 정렬로 생성

 

<예시>

 

CREATE UNIQUE INDEX idx_school_sname

ON school(sname);

 

 

 

2.     NON UNIQUE INDEX

 

중복된 컬럼이 들어가야하는 테이블의 경우 생성

 

 

<INDEX 생성 문법>

 

CREATE INDEX 인덱스명칭

ON 테이블명 (컬럼명1 ASC | DESC, 컬럼명, ……..);

 

 

 

 

3.     결합 INDEX

 

 

-       두 개 이상의 컬럼을 합쳐 INDEX 생성

 

-       주로 WHERE 조건절이 2개 이상의 쿼리로 작성된 경우 사용

 

 

SELECT 팀이름, 번호

FROM 리그

WHERE 번호 = ‘2’

           AND 팀이름 = ‘첼시’;

 

 

번호, 팀이름 컬럼이 주로 조건절에서 사용된다면 해당 컬럼으로 INDEX를 생성해주는 것이 좋습니다.

 

아래와 같이 두 컬럼으로 결합 INDEX 생성이 가능합니다.

 

그러나 아래 컬럼 순서에 따라 성능차이가 있을 수 있습니다.

 

 

1>   번호 + 팀이름

 

CREATE INDEX idx_리그_번호_팀이름

ON 리그(번호, 팀이름);

 

2>   팀이름 + 번호

 

CREATE INDEX idx_리그_번호_팀이름

ON 리그(팀이름, 번호);

 

 

번호는 2개, 팀이름은 20개 정도 있다고 가정하면 1번은 번호로 먼저 검색하고, 거기서 팀을 검색하므로 2번 조회합니다.

 

아래 2번으로 생성하면 팀 20개에서 번호가 ‘2’를 찾아야 하므로 20번 조회를 하게 되므로 성능상 떨어집니다.

 

컬럼 특성을 살펴셔 속도나 조회 회수가 달라지므로 성능을 위해 고려를 하여 신중하게 INDEX 생성해야 합니다.

 

 

 

4.     함수기반 INDEX

 

 

-       함수(FUNCTION) 형태로 INDEX 생성

 

조건절 (WHERE절)에서 사용되는 컬럼을 인덱스 생성에 사용하지만, number로 인덱스를 생성하였는데

 

조건절(WHERE절)이 WHERE number * 10 = 20 같은 경우 해당 number 인덱스 사용이 불가

 

->  WHERE 조건절은 인덱스에서 생성한 컬럼과 다른 형태로 가공해서 사용 X

 

부득이하게 number * 10 조건으로 쿼리를 작성하여 인덱스를 생성하고 싶으면 아래와 같이 (number * 10) 함수 형태로 인덱스를 생성

 

<예제>

 

CREATE INDEX idx_department_number

ON department(number * 10);

 

 

 

# BITMAP 인덱스 : 주로 분석, 통계 정보 등 일괄 처리 시스템 (OLAP)

 

 

- 데이터 값의 종류가 적고, 동일한 데이터가 많은 경우 사용

 

- BITMAP 인덱스 생성 쿼리

 

CREATE BITMAP INDEX idx_학급_성별_bit

ON 학급(성별);

 

 

위와 같이 BITMAP 인덱스를 생성하면 아래와 같이 성별은 2가지 이므로, 맵도 2개로 생성된다고 보시면 됩니다.

 

남자 : 1 1 0 0

여자 : 0 0 1 1

 

성별이 남,녀로 2가지 이므로. 2개의 맵이 생성되지만 다른 국가, 회사 등 데이터가 여러가지 나오는 경우 해당 값들 만큼 맵이 생성됩니다.

 

 

성별로 생성하여 2개의 맵으로 생성되었는데 다른 컬럼의 데이터가 추가되면, BITMAP 인덱스 경우 기존의 것도 전부 수정해야하는 단점이 있습니다.

 

 

 

4> 인덱스 주의사항

 

 

INSERT 작업에서 테이블에는 차례대로 입력이 되지만, 인덱스에는 데이터가 순서대로 정렬이 되고 입력이 필요하여

 

성능이 저하될 수 있습니다.

 

DELETE 경우도 테이블에서만 삭제가 되고, 인덱스에서는 남아있어 늘어난 데이터로 인한 쿼리 수행 속도 저하가 되기도 합니다.

 

UPDATE 또한 인덱스에는 UPDATE가 없어 DELETE, INSERT 두 작업이 수행되면서 더 큰 부하를 준다고 합니다.

 

 

 

5> 인덱스 관리

 

- 적용된 INDEX 조회

 

 

SELECT * FROM USER_INDEXES;

 

 

- 인덱스 HINT 기능

 

 

인덱스 아래쪽부터 조회하는 오라클 HINT 기능

 

실행계획을 세워주는 옵티마이저에게 의도를 요청하는 기능

 

<문법>

 

SELECT /*+ INDEX(테이블명 INDEX명) */ 컬럼

FROM 테이블명

;

 

<예시>

 

SELECT /*+ INDEX(TABLE_A IDX_학교_NAME) */

      NAME, NUMBER

  FROM TABLE_A

WHERE TIME BETWEEN

       TO_DATE >= SYSDATE - 7

   AND NUMBER = 23

;

 

 

아래는 트리거(TRIGGER)에 대해 정리한 내용입니다. 참고 하시기 바랍니다!

 

 

https://lovefor-you.tistory.com/181

데이터베이스 TRIGGER 개념, TRIGGER 사용방법 알아보자. – by.SC

 

 

저도 필요해서 찾아봐서 정리하였는데 도움이 되셨으면 해서 작성하였습니다~!

 

 

수정할 부분이나 다른 의견 있으시면 댓글 부탁드립니다!!

 

 

조금이나마 도움이 되셨으면 좋아요 하트♥나 구독 부탁드립니다~!

파이팅 하시고 좋은 하루 되세요!!