Oracle 인덱스(Index)와 B트리 인덱스(트리 기반 인덱스)

인덱스(Index) 정의 및 특징

1) 원하는 데이터를 쉽게 찾을 수 있도록 돕는 책의 ‘찾아보기’와 유사한 개념으로, 기본적인 목적은 검색 성능의 최적화이다. 즉, 조건을 만족하는 데이터를 인덱스를 통해 효과적으로 찾을 수 있도록 돕는다. 추가적으로 이 부분은 DBA분에게 물어보니 인덱스를 활용한 데이터 조회는 전체 데이터의 15% 이내의 데이터를 조회할 때가 인덱스 효율이 가장 좋으며, 그 이상의 건수에 대한 조회가 필요할 때부터는 효율이 떨어진다고 한다.

2) 테이블에 인덱스를 생성하지 않아도 되고 여러 개를 생성해도 된다. → 선택적으로 생성할 수 있는 구조.

3) DML 작업(Insert, Update, Delete 등)은 테이블과 인덱스를 함께 변경해야 하기 때문에 오히려 느려질 수 있다는 단점이 존재하다.

4) 인덱스를 경유해서 반환된 결과 데이터는 인덱스 데이터와 동일한 순서로 갖는다.

5) 인덱스를 생성할 때 동일 칼럼으로 구성된 인덱스를 중복해서 생성할 수 없다. → 인덱스 구성 칼럼은 동일하지만 칼럼의 순서가 다르면 서로 다른 인덱스이기 때문에 생성 가능하다.

INDEX 생성과 사용법

CREATE INDEX 인덱스명 ON 테이블명(칼럼명1, 칼럼명2, … )

ON 테이블명 뒤 괄호 내에 파라미터로 칼럼명을 1개만 두어 단일 칼럼에 대한 인덱스를 생성할 수도 있고, 위 예시대로 여러 칼럼을 지정하는 인덱스(다중 인덱스 or 복합 인덱스)를 사용할 수도 있다. 중요한 점은 다중 인덱스를 이용하려면 다중 인덱스 생성 시 설정한 칼럼을 Select 조회 조건으로 모두 사용해야 옵티마이저가 해당 다중 인덱스를 활용할 확률이 높다. 또한 인덱스는 NOT NULL 값에 대해서만 생성되기 때문에, NULL 여부를 체크하는 조회 쿼리는 인덱스를 사용하지 못한다.

인덱스의 활용 방법 & 고려사항

특징에서도 언급했지만 인덱스를 생성한다고 무조건 좋은 것이 아니다. 어느 상황에서 인덱스의 사용 효율이 좋을까? 개발 당시에 성능이 좋던 쿼리들도 운영단계에서 데이터가 쌓이다 보면 조회 속도가 느려질 수 있는데, 보통 이 경우에 인덱스를 생성한다. 또한 테이블 내 전체 데이터 수의 5% 미만일 경우 적은 실행 비용(COST)으로 조회 성능이 가장 좋다고 한다.

INDEX 효율이 좋은 경우 또는 칼럼을 정리해보면

1) 큰 테이블에서 적은 양의 데이터를 조회할 때(Table Full Scan하면 시간이 오래 걸리기 때문)

2) Where절이나 Join조건을 자주 사용되는 칼럼

3) 여러 Row(데이터)들을 기준으로 봤을 때, Null값을 많이 포함한 칼럼

반대로 INDEX 효율이 떨어지는 경우는 언제일까?

1) 전체 데이터 수의 15% 이상 조회가 필요한 경우

2) 테이블의 데이터가 적은 경우, 인덱스를 설정하지 않는 게 오히려 성능이 좋다.

3) 조회(Select)보다 삽입(Insert), 수정(Update), 삭제(Delete) 처리 비중이 많은 테이블.

4) Where절이나 Join조건을 자주 사용되지 않는 칼럼

추가적으로, 조회 쿼리 실행 시 조건식에서 INDEX를 이용하려면 해당 인덱스 칼럼을 가공 or 연산하지 않은 상태에서 비교하여야만 쿼리가 INDEX를 탄다는 점을 고려해야 한다. 예) 전화번호 칼럼의 경우(02-111-1111) 맨 앞 지역번호(02)에 해당하는 문자열만을 분리(가공/연산)하여 조건 검색하면 인덱스를 타지 않음.

왜냐하면 인덱스 칼럼에 외부적 변형이 일어나면, 말 그대로 상대값과 비교되기 전에 칼럼이 가공이 된 것이기 때문에 당연히 인덱스를 사용할 수 없게 되는 것이다. 여기서 칼럼의 외부적 변형이란 쿼리 조건식에 인덱스를 가진 칼럼을 연산, 결합, 사용자 지정 함수 등의 방법으로 가공함을 의미하며, 인덱스 사용 시 이러한 변형이 일어나지 않도록 주의해야 한다. 다시 말해, 조건식에서는 테이블 칼럼을 변경하지 말고 비교 값을 변경해서 비교해야 인덱스 스캔이 된다.

① 인덱스를 타지 않는 쿼리 조건식 예제

SELECT * FROM PHONEBOOK WHERE SUBSTR(number, 1, INSTR(number, '-'));

SELECT * FROM PHONEBOOK WHERE regist_date + 1 > 2;

SELECT * FROM PHONEBOOK WHERE SUBSTR(user_name, -8) = 'Peterson';

SELECT * FROM PHONEBOOK WHERE TO_CHAR(join_date, 'yyyymmdd') = '20201231'

② 인덱스를 타는 쿼리 조건식

SELECT * FROM PHONEBOOK WHERE number LIKE '02-%';

SELECT * FROM PHONEBOOK WHERE regist_date > 2;

SELECT * FROM PHONEBOOK WHERE user_name = 'Jordan Bernt Peterson';

SELECT * FROM PHONEBOOK WHERE join_date = TO_DATE('20201231', 'yyyymmdd')

B-트리 인덱스(트리 기반 인덱스) 특징

B-Tree index는 DBMS에서 가장 일반적인 인덱스로, 트리 구조의 최상위에 하나의 "Root Block(Node)"가 존재하고 그 하위에 자식 Block(Node)이 붙어 있는 형태이다. 트리 구조의 가장 하위에 있는 노드를 "Leaf Block"이라 하며, Root Block도 아니고 Leaf Block도 아닌 중간 Block을 "Branch Block"이라고 한다. 데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데, 인덱스의 Leaf Block은 항상 실제 데이터 레코드를 찾아가기 위한 주소값을 가지고 있다. 그렇다면 이러한 블록들을 이용해 원하는 데이터를 어떻게 찾아낼까?? 아래 그림은 B-트리 인덱스의 구조이다.

B트리 인덱스 구조

어떤 데이터의 주소 값을 알기 위해 인덱스 49번 블록의 값을 찾고 싶다면??

① 루트블록에서 60보다 작으므로 왼쪽 포인터로 이동 → ② 49는 브랜치블록의 40보다 크므로 오른쪽 포인터로 이동 → ③ 이동한 결과 해당 블록이 리프블록이므로 블록 내에 49를 검색 → ④ 찾아낸 주소 값(레코드 식별자)을 이용하여 테이블에서 원하는 데이터를 조회

다음은 B트리 인덱스의 각 블록 별 특징을 정리한 표이다.

B-트리 인덱스의 구성	특징
모든 블록 공통	인덱스 데이터는 순서대로 정렬돼있다.
브랜치 블록(Branch Block)	① Branch Block 중에서 가장 상위에서 있는 블록을 Root Block이라고 한다. ② Branch Block은 분기를 목적으로 하는 블록이며, 다음 단계의 Block을 가리키는 포인터를 가진다.
리프 블록(Leaf Block)	① 리프 블록은 아래 2개의 데이터로 구성된다.  1. (인덱스를 구성하는) 칼럼의 데이터.  2. 해당 데이터를 가지고 있는 행의 위치를 가리키는 레코드 식별자(RID, Record Identifier/Rowid)로 구성된다. ※만약, SQL문에서 다른 칼럼이 더 필요하면 리프 블록에 존재하는 레코드 식별자를 이용해서 테이블을 액세스한다. ② 양방향 링크(Double Link)를 가짐. 이것을 통해서 오름 차순(Ascending Order)과 내림 차순(Descending Order) 검색을 쉽게 가능. 따라서 B Tree Index는 '='로 검색하는 일치(Exact Match) 검색과 'BETWEEN', '>' 등과 같은 연산자로 검색하는 범위(Range) 검색 모두에 적합하다.