인덱스란
데이터베이스에서 인덱스란 원하는 데이터를 빨리 찾기 위해 투플의 키 값에 대한 물리적 위치를 기록해 둔 자료구조이다. 도서관에서 책을 찾을 때 서지목록을 보고 책위 위치를 찾는 것 처럼 인덱스도 같은 역할을 한다.
인덱스의 생성
인덱스는 데이터 검색을 빨리하기 위해 사용한다. 하지만 인덱스를 생성했다고 해서 데이터 검색이 무조건 빨라지는 것은 아니다. 다음의 고려사항을 충분히 살펴보자
-
인덱스는 where 절에자주 사용되는 속성이어야 한다.
-
인덱스는 조인에 자주 사용되는 속성이어야 한다.
-
단일 테이블에 인덱스가 많으면 속도가 느려질 수 있다.
-
속성이 가공된는 경우 사용하지 않는다.
-
속성의 선택도가 낮을 때 유리하다. (속성의 모든 값이 다른 경우)
B-tree
일반적인 DBMS의 인덱스는 대부분 B-tree 구조로 되어 있다. 먼저 B-tree의 기본 구조를 알아보고 실제 인덱스 구조에 대해서 살펴보자.
B-tree는 데이터의 검색 시간을 단축한기 위한 자료구조로, 루트노드, 내부노드(브랜치노드), 리프노드로 구성되며 리프노드가 모두 같은 레벨에 존재하는 균형(balanced) 트리다.
B-tree 특징
-
각 노드는 키 값과 포인터를 가진다.
-
키 값은 오름차순으로 저장되어있으며, 키 값 좌우에 있는 포인터는 각각 키 값보다 작은 값과 큰 값을 가진 다음 노드를 가리킨다. 따라서 키 값을 비교하여 다음 단계의 노드를 쉽게 찾을 수 있다.
-
B-tree는 키 값이 새로 추가되거나 삭제될 경우 동적으로 노드를 분할하거나 통합하여 항상 균형 상태를 유지한다. (인덱스 키값을 삭제할 경우에는 해당 키 값이 저장된 리프노드를 찾아서 삭제 마크만 한다)
B-tree 인덱스를 통한 데이터 읽기
인덱스 레인지 스캔
인덱스 레인지 스캔은 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식이다.
루트노드부터 비교를 시작해 리프노드에 도달하여 시작지점을 찾는다. 그 후 리프노드의 레코드만 순서대로 쭉 읽는다. 만약 스캔하다가 리프노드의 끝까지읽으면 리프노드간의 링크를 이용해 다음 리프노드를 찾아서 다시 스캔한다.
이 때 중요한 것은 리프 노드에 저장된 레코드 주소로 데이터파일의 레코드를 읽어오는데 레코드 한건 한건 단위로 랜덤 I/O가 발생한다는 것이다. 인덱스를 통해 해당 데이터의 주소를 찾는 것은 비용이 많이 들지는 않지만, 랜덤 I/O에는 비용이 많이 발생한다.
그렇기 때문에 인덱스를 통해 찾으려는 레코드가 20~25%를 넘으면 랜덤 I/O 때문에 전체 데이터를 읽고 필터링 하는 것이 빠르다.
인덱스 풀 스캔
인덱스 풀 스캔은 인덱스의 처음부터 끝까지 모두 읽는 방식을 말한다. 대표적으로 쿼리의 조건절에 사용된 컬럼이 인덱스의 첫번째 컬럼이 아닌 경우 인덱스 풀 스캔 방식을 사용한다.
인덱스 선두 컬럼이 조건절에 없으면 옵티마이저는 우선적으로 테이블 풀 스캔을 고려한다. 그런데 테이블 용량이 커서 부담이 된다면 옵티마이저는 인덱스 풀 스캔을 고려할 수 있다.
인덱스가 차지하는 면적은 테이블보다 훨씬 적기 때문에, 만약 테이블 전체를 스캔하기보다 인덱스 스캔 단계에서 대부분 레코드를 필터링하고 일부에 대해서만 테이블 액세스가 발생하도록 할 수 있다면 전체적인 I/O 효율 측면에서 이 방식이 유리하다.
루스 인덱스 스캔
루스 인덱스 스캔이란 느슨하게 또는 듬성듬성하게 인덱스를 읽는 것을 의미한다. 루스 인덱스 스캔은 인덱스 레인지 스캔과 비슷하게 동작하지만 중간마다 필요하지 않은 키값은 무시하고 다음으로 넘어가는 형태로 처리한다. 일반적으로 group by 또는 max 등의 함수에 대해 최적화를 하는 경우에 사용된다.
select dept_no, MIN(emp_no)
from dept_emp
where dept_no between 'doo2' and 'd004'
group by dept_no;
위 쿼리에서 dept_emp 테이블은 dept_no와 emp_no 두개의 컬럼으로 인덱스가 생성되어 있다. 또한 인덱스는 dept_no + emp_no 값으로 정렬되어 있어서 dept_no 그룹별로 제일 첫번째 레코드의 emp_no 값만 읽으면 된다.
즉, 인덱스에서 where 조건을 만족하는 범위 전체를 다 스캔할 필요가 없다는 것을 옵티마이저는 알고 있기 때문에 조건에 만족하지 않는 레코드는 무시하고 다음 레코드로 이동한다.
다중 컬럼 인덱스
두 개 이상의 컬럼으로 구성된 인덱스를 다중 컬럼 인덱스라고 한다.
다중 컬럼 인덱스에서 중요한것은 인덱스의 두번째 컬럼을 첫번째 컬럼에 의존해서 정렬되어 있다는 것이다. 즉, 두번째 컬럼의 정렬은 첫번째 컬럼이 똑같은 레코드에서만 의미가 있다.
이렇기 때문에 다중 컬럼 인덱스 내에서 각 컬럼의 위치(순서)가 상당히 중요하며 신중히 결정해야 한다.
가용성과 효율성 판단
효율적인 인덱스 활용을 위해서 인덱스의 작업범위 결정조건으로 사용할 수 없는 조건들에 대해서 알아보자.
아래 조건들은 작업범위 결정조건으로 사용되지 못할 뿐 경우에 따라서 체크조건 으로는 사용될 수 있다.
-
NOT-EQUAL로 비교되는 경우
-
LIKE '%??' 형태로 문자열의 뒷부분을 비교하는 경우
-
다른 연산자로 인덱스 컬럼이 변형된 후 비교된 경우
ex) where substring(column, 1, 1) = 'X' -
데이터 타입이 서로 다른 비교 (타입을 변환해야 비교가 가능한 경우)
다중 컬럼 인덱스에서 작업 범위 결정조건으로 사용하지 못하는 경우는 다음과 같다.
-
인덱스의 첫번째 컬럼에 대한 조건이 없는 경우
-
인덱스의 첫번째 컬럼의 비교조건이 위의 인덱스 사용 불가조건 중 하나일 경우
레퍼런스
https://idea-sketch.tistory.com/43
