DB 인덱스란?

참고

[DB] 데이터베이스(DB) 인덱스(Index) 란 무엇인가?

인덱스(index)

데이터베이스 테이블에 대한 검색 성능의 속도를 높여주는 자료 구조로 컬럼의 값과 물리적 주소를 (key, value)쌍으로 저장한다.

인덱스를 생성하면, 데이터들을 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소가 함께 저장된다. 이러한 인덱스가 생성되면 쿼리문으로 정보를 찾을 때 데이터를 전부 스캔 할 필요 없이 특정 컬럼들을 인덱싱 하면서 탐색의 범위를 줄일 수 있다. 마치 우리가 도서의 목차들을 이용하여 원하는 정보를 쉽게 탐색하는 것과 같은 방식이다.

인덱스의 장단점

가장 크고 핵심적인 특징 >> **정렬되어 있다!!**

장점

1. 조건 검색 where 절의 효율성

테이블이 만들어지고 정보들이 쌓이게 되면 테이블의 레코드(row 행)는 내부적으로 순서가 없이 아무렇게나 저장이 된다. 그러면 where절을 이용한 검색 시 데이터를 처음부터 끝까지 모두 읽어서 검색을 하게 된다. 이를 풀 테이블 스캔(Full Table Scan) >> 풀 스캔(Full Scan)이라고 한다. 하지만 우리가 인덱스를 이용한 인덱스 테이블 스캔(Index Table Scan)을 하면 인덱스는 기본적으로 정렬이 되어 있기 때문에 조건에 맞는 데이터(where절)들을 빠르게 찾을 수 있다.

2. 정렬 order by 절의 효율성

인덱스는 이미 정렬 되어 있는 정보들이기 때문에 order by에 의한 정렬(Sort) 과정이 불필요하다. order by는 부하가 상당한 작업인데 인덱스를 사용하면 이러한 전반적인 자원을 소모하지 않아도 된다.

3. min, MAX의 효율적인 처리가 가능하다.

이미 정렬이 되어있기 때문에 min 과 MAX 가 시작과 끝에 존재한다.

 

단점

정렬되어 있다는 점이 가장 큰 장점이지만 이 **정렬을 유지**해야 한다는 점은 단점이 된다.

1. 인덱스는 DML(Data Manipulation Language)에 취약하다

DML(Data Manipulation Language) 중에서도 insert, update, delete를 통해 데이터가 추가되거나 값이 바뀐다면 계속해서 인덱스 테이블 내에 있는 값들을 다시 정렬해 줘야 한다. 그리고 위의 사진과 같이 원본 테이블 말고도 인덱스 테이블이 따로 둬야 하기 때문에 작업이 더 많아지는 단점도 있다.

때문에 DML이 빈번한 테이블보다 검색을 위주로 하는 테이블에서 인덱스를 이용해 주도록 하자

2. 무조건 인덱스 스캔이 좋은 것은 아니다.

검색 위주의 테이블에서 인덱스를 이용하는 것이 좋긴 하지만 아무리 검색 위주의 테이블 이더라도 무조건 인덱스를 사용하는 것이 좋은 것은 아니다. 전체 데이터 중 10~15% 정도의 데이터를 처리하는 경우에는 인덱스가 효율적이지만 그보다 많은 정보를 다룬다면 오히려 비효율적일 수 있다.

ex) 연령대, 성별 같은 값의 범위 자체가 적은 컬럼의 경우 인덱스로의 접근 후에도 많은 데이터를 검색해야 해서 비효율적이다.

3. 속도 향상을 위해 인덱스를 많이 만드는 것은 좋지 않다.

인덱스를 만드는 것은 또 다른 테이블을 만드는 것으로 기존 데이터의 10% 정도의 저장공간이 추가로 필요하다. 따라서 무턱대고 인덱스를 만드는 것은 지양해야 하고 장단을 잘 비교해서 결정하자.

- index를 남발하지 않아야 하는 이유

DB의 성능 문제가 발생하면 생각할 수 있는 대응 중 하나가 인덱스를 추가로 생성하는 것이다. 하지만 이런식으로 인덱스를 늘려가다 보면 하나의 쿼리를 해결하는 건 가능할지 몰라도 전체적 DB의 성능 부하를 초래하게 된다. 따라서 우선은 SQL문을 개선하던지 다른 해결법을 찾아보고 인덱스는 마지막에 고려해야 한다.

 

인덱스의 관리

인덱스는 항상 최신의 데이터를 정렬된 상태로 유지해야 가치가 있는 것이다. 하지만 CRUD 과정에서 매번 추가삭제정렬을 하다가는 오히려 부하에 의해 성능이 저하되고 만다. 이를 위해 아래와 같은 대처를 해준다.

• insert : 새로운 데이터에 대한 인덱스를 추가한다.
• delete : 데이터를 삭제하는 것이 아니라 **"사용하지 않는다"** 라고 접근한다. 데이터를 실제로 지우는 것이 아니니 정렬할 필요가 없다.
• update : 기존 인덱스를 **"사용하지 않음 처리"** 하고 갱신된 데이터를 인덱스를 추가한다.

 

인덱스 생성 전략

인덱스를 최대한 효율적으로 사용하려면 데이터의 분포도는 최대한으로 잡아주고 조건절에 자주 호출되는 컬럼을 인덱스로 생성하는 것이 좋다. 인덱스는 특정 컬럼을 기준으로 생성하고 기준이 된 컬럼으로 정렬된 인덱스 테이블이 생성되도록 하는 것이다. 기준 컬럼은 최대한 중복이 되지 않는 값이 좋고, 가장 최선은 pk값으로 인덱스를 거는 것이다.

인덱스로 만들기 좋은 컬럼

1. 조건절에 자주 등장하는 컬럼
2. 항상 = 으로 비교되는 컬럼
3. 중복되는 데이터가 최소한인 컬럼(분포도가 좋은 컬럼)
4. order by 절에서 자주 사용되는 컬럼
5. join 조건으로 자주 사용되는 컬럼

 

인덱스 구조

여러 인덱스 구조가 존재하는데 그중에서도 가장 많이 사용하는 구조는 밸런스드 트리 인덱스 구조이다. 예를 들면 B*트리와 B+트리 등이 있다.

B* 트리 인덱스는 대부분 DBMS 그리고 오라클에서 주로 사용하는 구조이다. 대부분 Root(기준) / Branch(중간) / Leaf(말단) 노드로 구성되며 계층적 구조를 가지고 있다. 인덱스를 생성하는 순간 컬럼의 값들을 정렬하는데, 오라클 서버를 기준으로 생성과정을 간단히 서술하면

• 서버에서 풀 스캔보다 인덱스 스캔이 유리하다 판단하면 인덱스 생성
• 생성된 인덱스의 정렬 순서가 중간쯤 되는 데이터를 Root 블록으로 지정
• Root를 기준으로 가지가 되는 Branch를 정의
• 마지막으로 Leaf에 인덱스의 키가 되는 데이터와 데이터의 물리적 주소 정보인 ROWID를 저장
  • Root에는 Branch의 시작점에 대한 정보를 갖고 있어 찾고자 하는 데이터의 위치가 어는 Branch에 위치하는지 알 수 있다. Branch에서도 Leaf의 시작점 정보를 가지고 있어 어느 Leaf에 포함되어 있는지 알 수 있다.