Oracle (1) - 인덱스 원리 및 개념과 종류 (B-TREE, BITMAP INDEX)

1. 인덱스란?

어떤 데이터가 어디에 있는지 위치 정보를 가진 주소록과 같은 개념입니다.

일반적인 select 쿼리를 실행할 때 먼저 메모리의 데이터베이스 버퍼 캐시를 살펴봅니다.

버퍼 캐시에는 자주 사용되는 테이블들이 캐싱되어 있는데, 여기서 데이터가 있을 경우에는 바로 찾아 출력하며

데이터가 없을 경우에는 하드 디스크에 있는 데이터 파일에서 데이터를 찾기 시작합니다.

 

인덱스를 사용한다면 이러한 과정을 거치지 않고 바로 주소를 통해 찾아갑니다.

예를 들어, 특정한 사람의 집에 택배를 배송하기 위해 대한민국을 모두 조사해야 합니다. (Full Scan)

하지만, 인덱스를 사용한다면 바로 특정 거주지의 주소를 이용하여 배송지로 택배를 배송할 수 있습니다.

 

2. 인덱스의 생성 원리

해당 테이블을 모두 읽고 인덱스를 만드는 동안 데이터가 변경되면 문제가 되므로, 해당 데이터들이 변경되지 못하도록 조치한 후 메모리(PGA의 Sort Area)에 정렬하게 됩니다.
따라서, 전체 테이블을 스캔한 뒤, 메모리에 정렬(PGA내에 Sort Area에 공간이 부족하면, Temporary tablespace를 이용하여 정렬)하고 Block들을 기록하는 과정을 거치게 됩니다.

 

3. 인덱스의 구조와 작동 원리 (B-TREE 인덱스 기준)

인덱스(Index)는 컬럼이 Key(사용자가 인덱스를 지정하라고 지정한 컬럼)와 ROWID 두개로 이루어져 있습니다.

 

SELECT * FROM EMP WHERE EMPNO = 7902;

데이터 파일의 블록이 10만개가 있다고 할 때 위의 SQL을 수행한다면,

1. 서버 프로세스가 구문 파싱 과정을 마친 후 DB Buffer 캐시에 EMPNO가 7902인 정보가 있는지 먼저 확인합니다.
2. 해당 정보가 캐시에 없다면 디스크 파일에서 7902 정보를 가진 블럭을 찾아, DB Buffer 캐시로 가져온 뒤 해당 정보를 사용자에게 보여줍니다.

• Index가 없는 경우는 7902 정보가 디스크 어떤 블럭에 있는지 알 수 없으므로, 10만개의 데이터 전부 DB Buffer 캐시로 복사한 뒤, Full Scan을 통하여 데이터를 찾게 됩니다.
• Index가 있는 경우는 WHERE 절의 조건으로 준 컬럼이 Index의 Key로 생성이 되어있는지 확인한 뒤에, 인덱스로 먼저 가서 7902 정보가 어떤 ROWID를 가지고 있는지 확인합니다. 해당 ROWID에 있는 블럭을 찾아가서 DB Buffer 캐시로 가져와 해당 정보를 사용자에게 보여줍니다.

4. 인덱스의 종류

1. B-TREE 인덱스

• OLTP(Online Trasaction Processing : 실시간 트랜잭션 처리) 
• 실시간으로 데이터 입력과 수정이 일어나는 환경에 많이 사용함.

< B-TREE 인덱스 구조 >

• B : binary, balance의 약자
• Root Block : Branch Block에 대한 정보
• Branch Block : Leaf Block에 대한 정보
• Leaf Block : 실제 데이터들의 주소

2. BITMAP 인덱스

• OLAP(Online Analytical Processing : 온라인 분석 처리)
• 대량의 데이터를 한꺼번에 입력한 뒤 주로 분석이나 통계 정보를 출력할 때 많이 사용함.
• 데이터 값의 종류가 적고, 동일한 데이터가 많을 경우 많이 사용함.

B-TREE 인덱스와, BITMAP 인덱스의 상세한 정보는 각각 B-TREE 포스팅, BITMAP 포스팅 에서 확인할 수 있습니다.

 

4. 인덱스 주의사항

1) INSERT 작업의 경우 Index Split이라는 현상이 발생합니다. 인덱스는 데이터가 순서대로 정렬되어 저장하게 되는데, 기존 블럭에 여유 공간이 없는 상황에서 새로운 데이터가 입력되어야 하는 경우 오라클은 기존 블럭의 내용 중 일부를 새 블럭에다가 기록한 다음 기존 블럭에서 빈 공간을 만들어서 새로운 데이터를 추가하게 됩니다. 따라서, 성능면에서 매우 불리할 수 있습니다.

 

2) DELETE 작업의 경우 일반적인 테이블에서는 데이터가 delete될 경우에 해당 위치 데이터가 지워지고 그 공간을 사용할 수 있으나, 인덱스에서는 데이터가 delete 될 경우 데이터는 지워지지 않고, 사용하지 않는다는 의미의 표시만 하게 됩니다. 즉, 테이블에 2만건의 데이터가 있다고 가정하고 1만건을 삭제해도 인덱스에는 데이터가 2만건 존재한다는 의미입니다. 따라서, 인덱스를 사용해도 수행속도를 기대하기는 힘들 수 있습니다.

 

3) UPDATE 작업의 경우 인덱스는 update라는 작업이 존재하지 않습니다. 기존의 데이터를 delete한 다음에 새로운 값의 데이터를 insert하는 이중 과정을 작업해야 합니다. 따라서, 다른 DML 작업보다 더 큰 부하를 주게 됩니다.

 

5. 인덱스 생성시 고려사항

1. 일반적으로 테이블 전체 로우 수의 15% 이하의 데이터를 조회할 때 인덱스를 생성합니다.
2. 테이블 건수가 적다면 인덱스를 경유하기 보다는 테이블 전체를 스캔하는 것이 더 빠르므로, 인덱스 생성을 지양합니다.
3. 인덱스 생성시 컬럼은 유일성 정도가 좋거나 넓은 값을 가진 컬럼을 지정하는 것이 좋습니다. (NULL 값을 많이 갖는 컬럼은 피하는 것이 좋습니다.)
4. 결합 인덱스 생성시에는 컬럼의 순서가 중요합니다. 보통 자주 사용하는 컬럼을 앞에 지정합니다.
5. 인덱스를 생성해 두면 해당 테이블에 DML 작업을 진행할 시, 인덱스에도 수정작업이 동시에 발생하기 때문에 테이블에 만들 수 있는 인덱스의 수는 제한이 없으나, 너무 많이 만들면 오히려 성능이 저하될 수 있습니다.
6. 데이터의 검색보다 수정, 삭제, 삽입 작업이 빈번한 테이블에는 인덱스를 생성하지 않는 것이 좋습니다.
7. 인덱스 생성시 가장 중요한 것은 SQL 쿼리가 인덱스를 잘 활용할 수 있게끔 짜여져야 합니다.

---

참조

• https://rongscodinghistory.tistory.com/113
• https://lee-mandu.tistory.com/483