인덱스(Index)란 무엇인가?
인덱스는 데이터베이스 테이블에 있는 데이터를 빨리 찾기 위한 용도의 데이터베이스 객체이며 일종의 색인기술이다.
테이블에 index를 생성하게 되면 index Table을 생성해 관리한다. 인덱스는 테이블에 있는 하나 이상의 컬럼으로
만들 수 있다. 가장 일반적인 B-tree인덱스는 인덱스 키(인덱스로 만들 테이블의 컬럼 값)와 이 키에 해당하는 컬럼 값을 가진
테이블의 로우가 지정된 주소 값으로 구성된다.
인덱스(Index)를 사용하는 이유
인덱스의 가장 큰 특징은 데이터들이 정렬이 되어있다는 점이다. 이 특징으로 조건 검색이라는 영역에서 굉장한 장점이 된다. (찾기 편하다)
조건 검색 Where 절의 효율성
테이블을 만들고 안에 데이터가 쌓이게 되면 내부적으로 순서가 없이 뒤죽박죽으로 저장된다.
이렇게 되면 where절에 특정 조건에 맞는 데이터들을 찾아낼때도 처음부터 끝까지 다 읽어서 검색 조건과 맞는지 비교한다. (검색속도가 느리다)
이것을 풀 테이블 스캔이라고 한다. 인덱스 테이블은 테이블들이 정렬되어 저장되어 있기 떄문에 해당 조건(Where)에 맞는
데이터들을 빠르게 찾아낼 수 있다. 이것이 인덱스(Index)를 사용하는 큰 이유이다.
정렬 Order by 절의 효율성
인덱스(Index)를 사용하면 Order by에 의한 Sort 과정을 피할수 있다. Order by는 굉장히 부하가 많이 걸리는 작업인데
정렬과 동시에 1차적으로 메모리에 정렬이 이루어지고 메모리보다 큰 작업이 필요하다면 디스크 I/O도 추가적으로 발생한다.
하지만 인덱스를 사용하면 이러한 전반적인 자원의 소모를 하지 않아도 된다. 이미 정렬이 되어 있기 때문에 가져오기만 하면 되기 때문이다.
MIN , MAX 의 효율적인 처리가능
데이터가 정렬되어 있기에 얻을 수 있는 장점이다. MIN값과 MAX값을 레코드의 시작값과 끝 값 한건씩만
가져오면 되기에 FULL TABLE SCAN으로 테이블을 다 뒤져서 작업하는 것보다 훨씬 효율적으로 찾을 수 있다.
인덱스(index)의 관리
인덱스는 항상 최신의 데이터를 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다.
그렇기 때문에 인덱스가 적용된 칼럼에 INSERT, UPDATE, DELETE 가 수행된다면 계속 정렬을 해주어야 하고
그에 따른 부하가 발생하게된다. 이런 부하를 최소화하기 위해 인덱스는 데이터 삭제라는 개념에서 인덱스를 사용하지 않는다 라는
작업으로 이를 대신한다.
INSERT: 새로운 데이터에 대한 인덱스를 추가한다.DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행한다.UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가한다.
B * Tree 인덱스
인덱스에는 여러가지 유형이 있지만 그 중에서 가장 많이 사용하는 인덱스의 구조로는 밸런스드 트리 인덱스 구조이다.
그리고 B Tree는 인덱스 중에서도 가장 많이 사용하는것은 B*TREE 와 B+TREE를 가장 많이 사용되는 인덱스의 구조이다.
B*Tree 인덱스는 대부분 DBMS 그리고 오라클에서 특히 중점적으로 사용하고 있는 가장 보편적인 인덱스이다. 구조는 위와 같이 Root(기준) / Branch(중간) / leaf(말단) Node로 구성됩니다. 특정 컬럼에 인덱스를 생성하는 순간 칼럼의 값들을 정렬하는데, 정렬한 순서가 중간 쯤 되는 데이터를 뿌리에 해당하는 ROOT 블록으로 지정하고 ROOT 블록을 기준으로 가지가 되는 BRANCH블록을 정의하며 마지막으로 잎에 해당하는 LEAF 블록에 인덱스의 키가 되는 데이터와 데이터의 물리적 주소 정보인 ROWID를 저장한다.
인덱스(Index) 사용예시
인덱스 생성
문법
CREATE INDEX [인덱스명] ON [테이블명](컬럼 1, 컬럼2, 컬럼3 .....)
CREATE INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS);
--예제 컬럼 중복 X
CREATE[UNIQUE] INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS);
쿼리문을 작성하면 INDEX를 생성할 수 있다. UNIQUE 키워드를 붙이면 컬럼값에 중복값을 허용되지 않는다는 뜻이다.
인덱스 조회
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'CUSTOMERS';
SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME = 'CUSTOMERS';
인덱스를 생성하면 User_INDEXES나 ALL_IND_COLUMNS 에서 조회할 수 있다. 방금 CUSTOMERS테이블에 만들었던 EX_INDEX가 첫번째 ROW에 있는걸 알 수 있다.
인덱스 삭제
문법
DROP INDEX [인덱스 명}
DROP INDEX EX_INDEX;
인덱스는 조회할때 성능을 극대로 확대하기 위해 만든 객체인데 너무 많이 만들면 index, delete, update 시에 부하가 발생해 전체적인 데이터베이스 성능을 저하 시키려고 한다. 그럼 안쓰는 인덱스는 삭제하는것이 좋다.
인덱스 수정
문법
DROP INDEX [기존 인덱스 명] TO [바뀔 인덱스 명]
ALTER INDEX EX_INDEX RENAME TO EX_INDEX_NEW
인덱스 명을 바꾸고 싶다면 위와 같이 하면된다. 참고로 오라클에서 인덱스의 구성 칼럼을 추가하는 것은 ALTER로 불가능하다. 인덱스를 변경하기 위해서는 삭제 DROP을 한 후에 새로 생성 CREATE를 해주어야 한다.
인덱스 리빌드
인덱스 파일은 생성 후 insert, update, delete 등을 반복하다보면 성능이 저하된다.
생성된 인덱스는 트리구조를 가진다. 삽입,수정,삭제등이 오랫동안 일어나다보면 트리의 한쪽이 무거워져 전체적으로
트리의 깊이가 깊어집니다. 이러한 현상으로 인해 인덱스의 검색속도가 떨어지므로 주기적으로 리빌딩하는 작업을 거치는것이 좋다.
--문법
ALTER INDEX [인덱스명] REBUILD;
--예제
ALTER INDEX EX_INDEX REBUILD;
위의 쿼리를 실행시키면 인덱스가 리빌드 된다. 또는 인덱스를 조회해서 리빌드 쿼리를 만들어 한번에 실행시키면 간편하다.
인덱스(index)를 남발하면 안되는 이유
개발을 진행할때 대개 개발서버와 운영서버를 나누어 관리한다. 대부분 개발서버에서 개발을 할때에는 적은량의
데이터를 가지고 로직검사를 하며 로직검사에 통과한 코드들이 운영서버에 업데이트가 된다. 하지만 개발서버에는 잘 동작하던
로직들이 운영서버의 많은량의 데이터들을 처리하다보면 성능이슈가 많이 발생합니다. 그 성능이슈의 주요원인은 바로 데이터베이스에 있다.
데이터베이스 관리자는 성능문제가 발생하면 가장 빨리 생각하는 해결책이 인덱스 추가 생성이다.
문제를 쉽게 해결하기 위해 쿼리 속도 문제가 날때마다 인덱스를 추가하는 것은 바람직하지 못하다. 성능이슈가 나서 인덱스를 만들고 또 다른 SQL문에서 성능이슈가 발생하여 또 인덱스를 만들었다고 한다면, 문제가 발생할때마다 인덱스를 생성하면서 인덱스가 계속 추가하게되고 참조하는 하나의 쿼리문을 빠르게 만들 수 있지만 전체적인 데이터베이스의 성능 부하를 초래하게된다. 그렇기 때문에 인덱스를 생성하는것 보다는 SQL문을 좀 더 효율적으로 짜는 방향으로 나가야한다. 인덱스 생성은 꼭 마지막 수단으로 강구해야 할 문제인것이다.
[DB] 데이터베이스 인덱스(Index) 란 무엇인가?