개요.
인덱스란 추가적인 쓰기 작업과 저장 공간을 활용하여 DB내 테이블의 검색 속도 향상을 위한 자료구조이다.
[ 인덱스 장단점 ]
- 장점
- 테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있다.
- 전반적인 시스템의 부하를 줄일 수 있다.
- 단점
- 인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요하다.
- 인덱스를 관리하기 위해 추가 작업이 필요하다.
- 인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있다.
만약 CREATE, DELETE, UPDATE가 빈번한 속성에 인덱스를 걸게 되면 인덱스의 크기가 비대해져서 성능이 오히려 저하되는 역효과가 발생할 수 있다. 그러한 이유 중 하나는 DELETE와 UPDATE 연산 때문이다. 앞에서 설명한대로, UPDATE와 DELETE는 기존의 인덱스를 삭제하지 않고 '사용하지 않음' 처리를 해준다고 하였다. 만약 어떤 테이블에 UPDATE와 DELETE가 빈번하게 발생된다면 실제 데이터는 10만건이지만 인덱스는 100만 건이 넘어가게 되어, SQL문 처리 시 비대해진 인덱스에 의해 오히려 성능이 떨어지게 될 것이다.
[ 인덱스(index)를 사용하면 좋은 경우 ]
- 규모가 작지 않은 테이블
- INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼
- JOIN이나 WHERE 또는 ORDER BY에 자주 사용되는 컬럼
- 데이터의 중복도가 낮은 컬럼
- 기타 등등
인덱스를 사용하는 것 만큼이나 생성된 인덱스를 관리해주는 것도 중요하다. 그러므로 사용되지 않는 인덱스는 바로 제거를 해주어야 한다.
인덱스 생성
CREATE INDEX 인덱스명 ON 테이블명(컬럼명);
예) CREATE INDEX IDX_MDCODE_01 ON MDCODE(CODE_NAME);인덱스 삭제
DROP INDEX [인덱스명];인덱스 확인
--테이블명 대소문자 구분
SELECT * FROM PG_INDEXES WHERE TABLENAME = 'mdcode';
[인덱스 관리]
DBMS는 index를 항상 최신의 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다. 그렇기 때문에 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 각각 다음과 같은 연산을 추가적으로 해주어야 하며 그에 따른 오버헤드가 발생한다.
- INSERT: 새로운 데이터에 대한 인덱스를 추가함
- DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행함
- UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가함
테이블 안에 모든 Index Rebuild
REINDEX TABLE 테이블명;Index 명 지정 Rebuild
REINDEX INDEX INDEX명Database 에 모든 INDEX Rebuild
REINDEX DATABASE Database명Rebuild 를 하고 나서 디스크 사이즈가 변경 없을 경우
postgresql 의 경우 index rebuild 가 진행되어도 디스크의 사용량은 변화가 없음. postgresql.conf 에 vacuum 설정이 안되어 있으면 아래와 같이 명령어를 사용하면 디스크 사용량이 변화되는 것을 볼 수 있음.
기본 vacuum 은 off 로 설정됨.
VACUUM 테이블명
댓글