[DB] INDEX

DB를 조회할 때, 조회를 할때마다 테이블 전체를 조회하는 건 너무 비효율적이다. 수십, 수백 단위의 데이터에서는 별 문제가 안될 수 있지만, 수백, 수천만 이상의 데이터가 쌓여있을 때는 큰 문제가 된다. 이럴 때 INDEX를 사용한다.

1. INDEX 란?

데이터베이스에서 인덱스(INDEX)는 쓰기 속도가 느리고 저장 공간이 늘어나는 대신 검색 작업의 속도를 향상시키는 데이터 구조이다. 인덱스의 가장 큰 특징은 데이터들이 정렬이 되어있다는 점이다. 이 특징으로 인해 인덱스를 사용하면 DBMS(데이터베이스 관리 시스템)가 조건 검색을 할 때, 테이블에서 특정 데이터 행을 매우 빠르게 찾고 검색할 수 있다.

 

1. WHERE 절
   테이블의 레코드는 순서 없이 저장되기 때문에 특정 조건에 맞는 데이터를 찾아낼 때도 레코드를 처음부터 끝까지 비교해야 한다. 이를 풀 테이블 스캔 (Full Table Scan)이라고 한다. 하지만 인덱스 테이블을 이용해 좀 더 빠르게 조회할 수 있다.
2. ORDER BY 절
   인덱스 테이블은 이미 정렬이 되어 있기 때문에, ORDER BY 과정을 간소화할 수 있다.
3. MIN, MAX 절
   MIN 값과 MAX 값을 레코드의 시작 값과 끝 값 한 건씩만 가져오면 되기 때문에 풀 테이블 스캔으로 스캔하는 것보다 훨씬 효율적으로 값을 찾을 수 있다.

만약 인덱스를 타게 되면 위 그림과 같이 먼저 인덱스에 저장되어 있는 데이터의 물리적 주소로 가서 데이터를 가져오는 식으로 동작을 하여 검색 속도의 향상시킨다.

1.1 INDEX SCAN

이러한 INDEX를 이용해 DB를 조회하는 방법은 여러가지가 있는데, 대표적으로 전체 스캔(INDEX FULL SCAN)과 범위 스캔(INDEX RANGE SCAN)이 있다.

• 전체 스캔: 전체 스캔은 전체 인덱스를 처음부터 끝까지 읽는다. 이 유형의 스캔은 일반적으로 쿼리를 충족하기 위해 전체 인덱스가 필요하거나 인덱스가 작고 테이블 자체보다 더 빨리 읽을 수 있는 경우에 사용된다.

 

• 범위 스캔: 범위 스캔은 지정된 범위 기준과 일치하는 인덱스 부분만 읽습니다. 이 유형의 검색은 일반적으로 WHERE 열 > 5 AND 열 < 10과 같이 값 범위를 지정하는 조건이 쿼리에 포함된 경우에 사용됩니다.

 

이러한 두 가지 유형의 스캔 외에도 오라클에서는 스킵 스캔 및 인덱스 빠른 전체 스캔과 같은 다른 인덱스 액세스 방법도 지원한다. 이러한 방법은 쿼리에 복잡한 조건이 포함되어 있거나 인덱스가 크고 전체 또는 범위 스캔을 사용하여 효율적으로 읽을 수 없는 경우와 같은 특정 상황에서 사용된다고 한다.

2. INDEX 명령어

테이블이 생성되면 DBMS는 기본 키(Primary Key)에 대한 인덱스를 자동으로 생성한다. 또한 별도의 명령어를 통해 테이블의 다른 열에 대한 추가 인덱스를 생성할 수 있다.

Oracle 기준으로 코드를 작성했다.

CREATE INDEX idx_employee_name
ON employees (last_name, first_name);

이 쿼리는 employees 테이블의 last_name과 first_name 컬럼을 인덱스로 사용하는 idx_employee_name 인덱스를 생성한다. 인덱스의 이름은 원하는 이름을 지정하면 되며, 인덱스를 생성할 테이블과 인덱스를 생성할 컬럼을 지정한다.

일반적으로 인덱스는 인덱싱되는 다른 데이터와 함께 데이터베이스 자체에 저장된다. 이렇게 생성한 인덱스들은 아래의 쿼리로 조회할 수 있다.

SELECT index_name, table_name FROM user_indexes

각 인덱스가 연결된 테이블의 이름과 함께 현재 사용자의 모든 인덱스 목록을 볼 수 있다.

마지막으로, 인덱스를 삭제하는 방법이다.

DROP INDEX idx_employee_name

idx_employee_name 이름을 갖는 인덱스를 삭제하는 명령어다. 삭제하고 싶은 인덱스의 이름을 넣으면 된다.

 

3. INDEX의 데이터 구조

이 내용은 스킵하고 싶었지만, 면접 질문에서 자주 나오는 문제라 간단히 정리하고 지나가려고한다. INDEX의 데이터 구조는 B Tree를 주로 이용한다.

3-1. B-Tree

별도의 설정을 하지 않으면 Oracle의 Default 인덱스 생성 방법이다. Binary Search Tree를 확장한 Tree로 각 Node는 여러 개의 Key를 가질 수 있고, 여러 개의 Child를 가질 수 있다. 또한 모든 Leaf Node는 동일한 Depth를 가진다.

3-2. B+Tree

Oracle은 제공하지 않지만, 데이터 저장 방법으로 많이 쓰인다. B-Tree를 개량한 자료구조 이다. B-tree처럼 모든 Leaf Node는 동일한 Depth를 갖는다. B-Tree와의 가장 큰 차이점은 Inner Node에는 Key만 저장이 되고 Leaf Node에 Key와 Data를 함께 저장한다.

3-3. B+-Tree

B+-Tree는 B-Tree의 한 종류로서, 일반적인 B-Tree와 달리 데이터 포인터를 리프(Leaf) 노드에만 저장한다. 리프 노드의 상위 레벨인 비리프(Non-Leaf) 노드는 전형적인 B-Tree 로 구성되며 리프 노드를 빠르게 찾는 인덱스 역할을 한다. 리프 노드에는 키와 키에 대응하는 데이터의 포인터가 저장되어 있다.

이 외에도 오라클에서는 Bitmap indexes,Partitioned indexes, Function-based indexes, Domain indexes 등을 제공한다. 오라클 공식 사이트에서 확인할 수 있다.

4. INDEX 단점?

인덱스는 데이터베이스 쿼리 속도를 높이는 데 유용하지만 몇 가지 제한 사항과 단점들이 있다.

 

데이터베이스 인덱스의 한 가지 잠재적인 약점은 특히 인덱싱된 열이 크거나 테이블에 많은 수의 행이 있는 경우 상당한 양의 디스크 공간을 사용하게 된다. 많은 수의 인덱스를 관리해야 하는 경우, 데이터를 관리하기 더 어려워질 수 있으며 데이터 베이스의 전반적인 성능도 저하된다.

 

그리고, 인덱싱된 열의 데이터가 자주 업데이트 되는 경우 문제가 될 수 있다. 이로 인해 조회 시 잘못된 결과가 발생할 수 있으며, 인덱스가 쿼리 속도를 높이는 데 효과적이지 않을 수 있다. 이 문제를 해결하기 위해 많은 DBMS는 인덱싱된 열의 데이터가 수정될 때마다 인덱스를 자동으로 업데이트하지만 이로 인해 추가 오버헤드가 추가되고 시스템의 전반적인 성능이 저하될 수 있다.

 

마지막으로, 인덱스를 추가하는 것이 항상 쿼리 성능 향상을 위한 최상의 솔루션은 아니라는 점에 유의해야한다. 경우에 따라 비정규화 또는 파티셔닝과 같은 다른 기술이 쿼리 성능을 개선하는 데 더 효과적일 수 있으며 데이터베이스 관리자가 장단점을 신중하게 평가하고 특정 상황에 적합한 접근 방식을 선택하는 것이 중요하다.

5. 마치며

JPA를 들어가기전에 DB관련 CS를 훑고 넘어가는 중인데, 정규화와 INDEX를 보면서 많이 보완한 것 같다. VIEW 정도만 짚고, 강의를 쭉 들으며 정리할 예정이다.

---

• 참고
  https://blogs.oracle.com/sql/post/how-to-create-and-use-indexes-in-oracle-database
  https://d2.naver.com/helloworld/1155
  https://ssup2.github.io/theory_analysis/B_Tree_B+_Tree/