추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스의 검색 속도를 향상하기 위한 자료구조
Index를 통해 검색 속도 향상과 조회 성능을 높일 수 있다.
Index를 사용하지 않는 칼럼을 조회하는 상황에서는 테이블 전체를 탐색하는 Table Full Scan이 수행되어 처리 속도가 떨어진다.
인덱스를 활용하면, SELECT 외에도 UPDATE와 DELETE 성능을 향상할 수 있다.
UPDATE와 DELETE의 WHERE문에서 데이터를 조회하는 성능을 향상된다.
Index 적용 예시
// single column index
CREATE INDEX si_id ON MEMBER (id);
CREATE INDEX si_name ON MEMBER (name);
// multi column index
CREATE INDEX mi_id_name ON MEMBER (id, name);
CREATE INDEX mi_id_name_age ON MEMBER (id, name, age);
Index 관리
Index를 관리하기 위해서는 데이터베이스의 약 10%의 저장공간이 필요하다.
Index는 항상 최신의 정렬된 상태로 유지해야 원하는 데이터를 빠르게 탐색할 수 있다.
따라서 Index에 적용된 칼럼에 INSERT, UPDATE, DELETE가 수행된다면, Index를 관리를 위한 추가적인 연산이 발생하여 그에 따른 오버헤드가 발생한다.
INSERT : 인덱스 페이지(데이터가 저장되는 단위)가 꽉차면, 비어있는 페이지를 확보하고 문제 있는 페이지를 분할하는 과정에서 성능에 영향을 준다.
DELETE : 데이터를 실제 지우지 않고 "사용하지 않음" 처리를 한다.
UPDATE : DELETE - INSERT 순으로 동작한다.
DELETE와 UPDATE가 빈번하게 일어나는 칼럼에 대해서 index를 설정하면, 인덱스의 크키가 비대해져 성능을 오히려 저하시킨다.
DELETE와 UPDATE시 기존의 인덱스를 삭제하지 않고 “사용하지 않음” 처리(삭제마킹)를 하기 때문에 인덱스의 크기가 커져, 처리 성능이 떨어진다.
어떤 칼럼에 Index를 설정하는 것이 좋을까?
수정 빈도 : UPDATE, DLELTE가 자주 발생하지 않는 칼럼
활용도 : Join과 where, order by에 자주 사용하는 칼럼
중복도 : 데이터의 중복도가 낮은 칼럼 (카디널리티가 높음)
Index 장단점
장점
특정 칼럼에 대해서 조회 시, 검색 속도 성능을 향상할 수 있다.
전반적인 시스템 부하를 줄일 수 있다.
단점
인덱스를 관리하기 위해 DB의 10%의 저장공간이 필요하다.
인덱스 관리를 위한 추가 작업이 필요하다.
인덱스를 잘못 사용할 경우 성능이 저하되는 역효과가 발생한다.
Index 자료구조
해시 테이블
Key, Value로 데이터를 저장하는 자료구조로, 빠른 데이터 검색이 필요할 때 유용하다.
데이터베이스에서는 부등호(>, <)이 자주 일어나기 때문에, 등호(=) 연산에 특화된 해시테이블은 인덱스 자료구조로 적합하지 않다.
B+ Tree
DB의 인덱스를 위해 자식 노드가 2개 이상인 B-Tree를 개선시킨 자료구조
리프 노드만 인덱스와 함께 데이터를 저장하고 있고, 나머지 노드는 데이터를 위한 인덱스만 갖는다.
리프 노드는 Linked List로 연결되어 있다.
데이터 노드 크기는 인덱스 노드의 크기와 같지 않아도 된다.
인덱스는 삭제되지 않고, 비활성화 처리되기 때문에 데이터 노드의 크기와 인덱스 노드의 크기가 다를 수 있다.
데이터 베이스의 인덱스 칼럼은 부등호를 이용한 순차 검색 연산이 자주 발생한다. 따라서 B+ Tree의 리프 노드들을 Linked List로 연결하여 순차검색을 용이하게 하는 등 BTree를 인덱스에 맞게 최적화한 것이다.
Index 주의사항
인덱스로 사용될 칼럼값 그대로 사용해야만 인덱스가 사용된다.
WHERE salary * 10 > 15000 (인덱스 적용 X)
WHERE salary > 15000 / 10 (인덱스 적용 O)
복합 인덱스 순서가 중요하다. (앞 순서의 조건은 반드시 포함시켜야 한다.)
다음 칼럼이 이전 칼럼에 의존하여 정렬된다. 따라서 두 번째 칼럼만으로 질의를 하는 경우 인덱스를 잘 타지 못한다.
하나의 쿼리는 여러 인덱스 테이블을 동시에 탐색하지 않고 하나의 인덱스만 탐색한다.
의도대로 인덱스가 동작하지 않을 경우 explain으로 확인
옵티마이저의 실행 계획을 확인
개발환경, 배포환경의 통계 내역이 다르기 때문에 따문에 옵티마이저 실행 계획이 다를 수 있다.
인덱스는 PK에 의존하기 때문에, PK 값이 클수록 한 페이지에 담을 수 있는 인덱스 정보가 줄어들고, 메모리를 비효율적으로 사용되기 때문에 성능에 좋지 않다.
클러스터링 인덱스 & 넌클러스터링 인덱스
클러스터링 인덱스
인덱스로 지정한 컬럼을 기준으로 물리적으로 정렬하는 인덱스
우선순위 : 1) primary key 2) unique + not null
실제 데이터 자체가 정렬되어 있다.
테이블당 1개만 존재 가능하다.
검색속도는 빠르지만, 입력, 수정, 삭제는 느리다.
넌클러스터링 인덱스
데이터 자체는 정렬되지 않고, 인덱스값을 기준으로 정렬하여 새로 인덱스 페이지를 만드는 인덱스
새로 인덱스 페이지를 만들고 리프 페이지에 실제 데이터가 위치한 주소를 가리킴
unique column, unique index, index 생성
테이블당 여러개 존재 가능
검색 속도는 Cluster에 비해 느리지만, 입력, 수정, 삭제가 빠르다.
테이블 스캔
Table Full Scan
테이블에 속한 블록 전체를 읽어서 사용자가 원하는 데이터를 찾는다.
Index Range Scan
인덱스 칼럼이 가공되지 않는 상태로 조건절에 있을 때 수행
인덱스의 일정량을 스캔하면서 얻은 ROWID를 사용해 테이블 레코드를 찾는다.
Index Full Scan
인덱스 칼럼이 조건에 없으면 Index Range Scan이 불가능하므로, 옵티마이저는 Table Full Scan을 고려한다.
그런데 대용량 테이블이라면 Table Full Scan에 대한 부담이 너무 크면 Index를 활용할 필요가 있다.
인덱스의 전체 크기는 테이블의 전체 크기보다 훨씬 적으므로, Index Range Scan을 할 수 없을 때 Full Table Scan보다는 Index Full Scan을 고려한다.
