인덱스(INDEX)란?

인덱스는 데이터베이스 테이블에 있는 데이터를 빨리 찾기 위한 용도의 데이터베이스 객체이며 일종의 색인 기술이다.
테이블에 index를 생성하게 되면, Index Table을 생성해서 관리한다.
인덱스는 테이블에 있는 하나 이상의 컬럼으로 만들 수 있다.
가장 일반적인 B-tree 인덱스는 인덱스 키(인덱스로 만들 테이블의 컬럼 값)와 이 키에 해당하는 컬럼 값을 가진 테이블의 로우가 저장된 값으로 구성된다.

인덱스 사용 예시

인덱스 생성

문법

CREATE INDEX [인덱스명] ON [테이블명] (컬럼1, 컬럼2, 컬럼3, ....)

예제

CREATE INDEX EX_INDEX ON CUSTOMERS(NAME, ADDRESS);

중복 컬럼 없는 예제
→ UNIQUE를 붙이면 컬럼 값에 중복값을 허용하지 않는다는 의미이다.

CREATE[UNIQUE] INDEX EX_INDEX ON CUSTOMERS(NAME, ADDRESS);

인덱스 조회

인덱스를 생성하면 USER_INDEXES 나 ALL_IND_COLUMNS 에서 조회할 수 있다.

문법

SELECT * FROM USER_INDEXES WHERE TABLE_NAME = '테이블명';

SELECt * FROM ALL_IND_COLUMNS WHERE TABLE_NAME = '테이블명';

인덱스 삭제

인덱스 조회성능을 극대화하기 위해 만든 객체인데 너무 많이 만들면 insert, delete, update시에 부하가 발생해 전체적인 데이터베이스 성능을 저하시킨다.
따라서, 안쓰는 인덱스는 삭제하는 것이 좋다.

문법

DROP INDEX [인덱스명];

예제

DROP INDEX EX_INDEX;

인덱스명 수정

문법

ALTER INDEX [기존 인덱스 명] TO [바뀔 인덱스 명];

예제

ALTER INDEX EX_INDEX RENAME TO EX_INDEX_NEW;

인덱스 리빌드 사용 예시

인덱스를 리빌드 하는 이유

인덱스 파일은 생성 후 insert, update, delete, ...을 반복하다보면 성능이 저하된다.
생성된 인덱스는 트리 구조를 가진다.
삽입, 수정, 삭제, ...이 오랫동안 일어나다보면 트리의 한쪽이 무거워져 전체적으로 트리의 깊이가 깊어진다.
이러한 현상으로 인해 인덱스의 검색 속도가 떨어지므로 주기적으로 리빌딩하는 작업을 거치는 것이 좋다.

인덱스 리빌드 할 대상 조회 쿼리

해당 쿼리는 index 트리의 깊이가 4 이상인 index를 조회하는 쿼리이다.
해당 쿼리문을 실행하여 검색되는 index가 있다면 리빌딩하는 것이 좋다.

SELECT I.TABLESPACE_NAME,I.TABLE_NAME,I.INDEX_NAME, I.BLEVEL,
       DECODE(SIGN(NVL(I.BLEVEL,99)-3),1,DECODE(NVL(I.BLEVEL,99),99,'?','Rebuild'),'Check') CNF
FROM   USER_INDEXES I
WHERE   I.BLEVEL > 4
ORDER BY I.BLEVEL DESC

인덱스 리빌드

해당 쿼리를 실행하면 인덱스가 리빌드 된다.

문법

ALTER INDEX [인덱스명] REBUILD;

이렇게 일일히 리빌드 시키기 귀찮다면, USER_INDEXES에 있는 인덱스를 조회하여 인덱스 리빌드 쿼리를 만들어 한 번에 실행시킬 수 있다.

문법

SELECT 'ALTER INDEX' || INDEX_NAME || ' REBUILD; '
FROM USER_INDEXES;

인덱스를 남발하지 말아야하는 이유

개발을 진행할 때 대개 개발 서버와 운영 서버를 나누어서 관리한다.
대부분 개발 서버에서 개발을 할 때에는 적은 양의 데이터를 가지고 로직 검사를 하며 로직 검사에 통과한 코드를이 운영 서버에 업데이트가 된다.
하지만, 개발서버에서는 잘 동작하던 로직들이 운영서버의 많은 양의 데이터들을 처리하다보면 성능 이슈가 많이 발생한다. 그 성능 이슈의 주요 원인은 바로 데이터베이스에 있다.
데이터베이스 관리자가 성능문제가 발생할 때 가장 빨리 생각하는 해결책은 인덱스 추가 생성이다.

하지만 문제를 그저 쉽게 해결하기 위해 쿼리 속도 문제가 날 때마다 인덱스를 추가하는 것은 바람직하지 못하다.
성능이슈가 나서 인덱스를 만들고 또 다른 SQL문에서 성능 이슈가 발생하여 또 인덱스를 만들었다고 쳐보자.
이렇게 문제가 발생할 때마다 인덱스를 생성하면서 인덱스가 계속 추가되면 생성된 인덱스를 참조하는 하나의 쿼리문을 빠르게는 만들 수 있지만, 전체적인 데이터베이스의 성능 부하를 초래하게 된다.
그렇기에 인덱스를 생성하는 것보다는 SQL문을 조금 더 효율적으로 짜는 방향으로 나아가야한다.
인덱스 생성은 꼭 마지막으로 강구해야할 문제이다.

⇒ 인덱스 생성은 데이터베이스 전체에 성능 부하를 초래할 수 있으므로 인덱스를 남발해서 만들어서는 안된다.