index
인덱스를 데이터베이스에서 사용하는 가장 큰 이유는 검색 속도를 높이기 위해서 입니다. 인덱스는 테이블 내 데이터를 정렬한 뒤 필요로 하는 데이터만 빨리 가지고 오기 위해서 사용을 하게 됩니다.
인덱스가 없다면 테이블 데이터를 처음부터 끝까지 해당 데이터가 있는지 하나하나 찾아봐야 합니다. 그것이 바로 Full scan입니다. 하지만, 만약 인덱스가 해당 테이블에 있다면 인덱스를 보고 해당 데이터의 위치로 바로 가서 데이터를 가지고 올 수 있습니다. 그게 바로 index scan이라는 것입니다!
index 생성 기준
- 테이블 내 데이터가 많을 때 만드는 걸 권장한다.
테이블 내 데이터가 많지 않을 때는 Full scan이 Index scan보다 더 빠를 수 있습니다. 인덱스는 테이블 내 데이터가 많으면 많을수록 더 극명한 효과를 가지고 올 수 있습니다.
- Primary key가 있는 칼럼에는 생성할 필요 없다!
Primary key가 부여된 칼럼에는 클러스터 인덱스가 생성됩니다. 또한 Unique제약 조건이 걸려있는 칼럼 또한 마찬가지입니다. 위 두 가지 제약조건이 적용된 칼럼에는 인덱스를 따로 추가할 필요가 없습니다.
- Cardinality를 확인 후 생성한다.
Cardinality란 어떤 칼럼 내 값의 분산도를 말합니다.
예를 들어서 주민등록번호 칼럼과 성별이 들어있는 칼럼을 생각해보면, 유일한 값인 주민등록번호가 들어간 칼럼은 Cadinality가 높다라고 이야기할 수 있으며, 성별은 단 두 개의 데이터만 들어가 있으므로 Cardinality가 낮다고 표현할 수 있습니다. 즉, Cardinality가 높은 칼럼에 인덱스를 생성하는 것이 유리합니다.
index의 단점
- 인덱스를 만들면 .mdb 파일 크기가 늘어납니다.
- 여러 사용자 응용 프로그램에서의 여러 사용자가 한 페이지를 동시에 수정할 수 있는 병행성이 줄어듭니다.
- 인덱스 된 필드에서 데이터를 업데이트하거나, 레코드를 추가 또는 삭제할 때 성능이 떨어집니다.
- 인덱스가 데이터베이스 공간을 차지해 추가적인 공간이 필요해집니다. (DB의 10퍼센트 내외의 공간이 추가로 필요)
- 인덱스를 생성하는데 시간이 많이 소요될 수 있습니다.
- 데이터 변경 작업이 자주 일어날 경우에 인덱스를 재작성해야 할 필요가 있기에 성능에 영향을 끼칠 수 있습니다.
따라서 어느 필드를 인덱스 해야 하는지 미리 시험해 보고 결정하는 것이 좋습니다. 인덱스를 추가하면 쿼리 속도가 1초 정도 빨라지지만, 데이터 행을 추가하는 속도는 2초 정도 느려지게 되어 여러 사용자가 사용하는 경우 레코드 잠금 문제가 발생할 수 있습니다.
사용하면 좋은 경우
(1) Where 절에서 자주 사용되는 Column
(2) 외래키가 사용되는 Column
(3) Join에 자주 사용되는 Column
Index 사용을 피해야 하는 경우
(1) Data 중복도가 높은 Column -> 효용이 없음
(2) DML(INSERT, DELETE, UPDATE)이 자주 일어나는 Column -> 취약함
- INSERT
: index split 현상이 발생할 수 있습니다.
(index split - 인덱스의 Block들이 하나에서 두 개로 나누어지는 현상)
인덱스는 데이터가 순서대로 정렬 되어야 합니다. 기존 블록에 여유 공간이 없는 상황에서 그 블록에 새로운 데이터가 입력되어야 할 경우 기존 블록의 내용 중 일부를 새 블록에다가 기록한 후 기존 블록에 빈 공간을 만들어서 새로운 데이터를 추가하게 됩니다.
*성능면에서 매우 불리
① index split은 새로운 블록을 할당 받고 key를 옮기는 복잡한 작업을 수행합니다. 모든 수행 과정이 Redo에 기록되어 많은 양의 Redo를 유발합니다.
② index split이 이루어지는 동안 해당 블록에 대해 키 값이 변경되면 안되므로 DML이 블로킹됩니다.
- DELETE
테이블에서 데이터가 delete 될 경우 지워지고 다른 데이터가 그 공간을 사용 가능합니다.
하지만 index에서 데이터가 delete 될 경우 데이터가 지워지지 않고, 사용 안 됨 표시만 해 둡니다.
즉, 테이블에 데이터가 1만건 있는 경우, 인덱스에는 2만건이 있을 수 있다는 뜻입니다. 이 경우, 인덱스를 사용해도 수행속도를 기대하기 힘듭니다.
- UPDATE
인덱스에는 update 개념이 없습니다. 테이블에 update가 발생할 경우 인덱스에서는 delete가 먼저 발생한 후 새로운 작업의 insert 작업이 발생합니다. delete와 insert 두 개의 작업이 인덱스에 동시에 일어나 다른 DML보다 더 큰 부하를 주게 됩니다.
index 생성, 삭제 방법
CREATE INDEX 인덱스명
ON 테이블명 ( 컬럼명1, 컬럼명2, ... ); 또는
-- 단일 인덱스
ALTER TABLE 테이블명 ADD INDEX 인덱스명 ( 컬럼명 );
-- 복합 인덱스
ALTER TABLE 테이블명 ADD INDEX 인덱스명 ( 컬럼명1, 컬럼명2, ... );
-- 인덱스 삭제
ALTER TABLE 테이블명 DROP INDEX 인덱스명;