인덱스(Index)란?
인덱스는 테이블의 동작 속도를 높여주는 자료 구조로, 인덱스로 데이터의 위치를 빠르게 찾아주는 역할을 한다. 쉽게 생각하면 List, Array에서도 우리가 Index를 사용해서 특정 값을 찾아내듯이 DB에서도 마찬가지 인 것이다. 데이터베이스 안의 레코드를 처음부터 풀스캔하지 않고, B+ Tree로 구성된 구조에서 Index 파일 검색으로 속도를 향상시키는 기술이다.
무조건 인덱스를 많이 설정하면 좋은걸까?
인덱스는 하나 혹은 여러 개의 컬럼에 대해 설정할 수 있다. 단일 인덱스를 여러 개 생성 할 수도, 여러 칼럼을 묶어 복합 인덱스를 설정할 수 도 있다. 하지만 무조건 많이 설정한다고 검색 속도를 높여주냐? 에 대답은 항상 그렇지는 않다 라고 할 수 있다.
DBMS의 인덱스는 항상 정렬된 상태를 유지하기 때문에 원하는 값을 탐색하는데는 빠르지만 새로운 값을 추가 삭제하거나 삭제, 수정하는 경우에는 쿼리문 실행 속도가 느려진다.
인덱스는 데이터베이스 메모리를 사용해서 테이블 형태로 저장되므로 개수와 저장 공간은 비례한다. 그렇기 때문에
- 조회시 자주 사용하는 값
- 교유한 값 중심
으로 인덱스를 설정하는 것이 좋다.
상황 분석
사용하면 좋은 경우
(1) Where 절에서 자주 사용되는 Column
(2) 외래키가 사용되는 Column
(3) Join에 자주 사용되는 Column
Index 사용을 피해야 하는 경우
(1) Data 중복도가 높은 Column
(2) DML이 자주 일어나는 Column
DML(Data Manipulation Language) 각각에는 어떤 영향을 미칠까?
SELECT 쿼리에서 성능이 좋지만, INSERT, UPDATE, DELETE에서는 상황에 따라 다르다.
INSERT
index split 현상이 발생할 수 있음. index split - 인덱스의 Block들이 하나에서 두 개로 나누어지는 현상 -> 인덱스는 데이터가 순서대로 정렬 되어야 함. 기존 블록에 여유 공간이 없는 상황에서 그 블록에 새로운 데이터가 입력되어야 할 경우 오라클이 기존 블록의 내용 중 일부를 새 블록에다가 기록한 후 기존 블록에 빈 공간을 만들어서 새로운 데이터를 추가하게 됨. 성능면에서 매우 불리 ① index split은 새로운 블록을 할당 받고 key를 옮기는 복잡한 작업을 수행. 모든 수행 과정이 Redo에 기록.. 많은 양의 Redo를 유발함 ② index split이 이루어지는 동안 해당 블록에 대해 키 값이 변경되면 안되므로 DML이 블로킹됨. enq:TX-index contention 대기 이벤트 발생(RAC- gc current split)
DELETE
테이블에서 데이터가 delete 될 경우 - 지워지고 다른 데이터가 그 공간을 사용 가능 index에서 데이터가 delete 될 경우 - 데이터가 지워지지 않고, 사용 안 됨 표시만 해 둔다. ->즉, 테이블에 데이터가 1만건 있는 경우, 인덱스에는 2만건이 있을 수 있다는 뜻 => 인덱스를 사용해도 수행속도를 기대하기 힘들다.
UPDATE
: 인덱스에는 update 개념이 없음!!! 테이블에 update가 발생할 경우 인덱스에서는 delete가 먼저 발생한 후 새로운 작업의 insert 작업이 발생한다. delete와 insert 두 개의 작업이 인덱스에 동시에 일어나 다른 DML보다 더 큰 부하를 주게 됨.
-> 결국 데이터를 조회할때 인덱스가 빛을 발한다. -> 결론적으로 DBMS에서 인덱스는 데이터의 저장 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다. ->SELECT 쿼리 문장의 WHERE 조건절에 사용되는 칼럼이라고 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져서 오히려 역효과만 불러올 수 있다.
그럼 어떤 칼럼에 인덱스를 설정하는게 좋을까?
인덱스는 한 테이블당 보통 3~5개 정도가 적당하다. 인덱스는 컬럼을 정해서 설정하는 것이므로 후보 컬럼의 특징을 잘 파악해서 아래 4가지 기준을 사용하면 효율적으로 인덱스를 설정할 수 있다.
- 카디널리티(Cardinality)
- 선택도(Selectivity)
- 활용도
- 중복도
카디널리티(Cardinality)
1
2
3
카디널리티란 전체 행에 대한 특정 컬럼의 중복 수치를 나타내는 지표이다.
예를들어, 주민등록번호 같은 경우는 중복되는 값이 없으므로 카디널리티가 높다고 할 수 있다. 이에 비해 이름같은 경우 *주민등록번호에 비해* 중복되는 값이 많으므로 카디널리티가 낮다고 할 수 있다.
결국, 카디널리티는 상대적인 개념이다.
카디널리티가 높을 수록 인덱스 설정에 좋은 칼럼이다. = 한 컬럼이 갖고 있는 값의 중복 정도가 낮을 수록 좋다.
선택도 (Selectivity)
1
2
3
4
5
데이터에서 특정 값을 얼마나 잘 선택할 수 있는지에 대한 지표이다.
선택도 계산 방법은
= 컬럼의 특정 값의 row 수 / 테이블의 총 row 수 * 100
= 컬럼의 값들의 평균 row 수 / 테이블의 총 row 수 * 100
즉, 선택도는 특정 필드값을 지정했을 때 선택되는 레코드 수를 테이블 전체 레코드 수로 나눈 것입니다.
선택도가 낮은 수록 인덱스 설정에 좋은 칼럼이다. (5 ~ 10 %가 적당하다.)
활용도
1
2
해당 컬럼이 실제 작업에서 얼마나 활용되는지에 대한 값이다.
수동 쿼리 조회, 로직과 서비스에서 쿼리를 날릴 때 WHERE 절에 자주 활용되는지를 판단하면 된다.
활용도가 높을 수록 인덱스 설정에 좋은 칼럼이다.
중복도
중복도가 없을 수록 인덱스 설정에 좋은 컬럼이다.