데이터베이스 인덱스(Index) 개념과 검색 성능 튜닝

 

 

 

 

 

 

 

데이터베이스를 사용할 때 인덱스를 빼놓고 얘기할 수 없을 정도로 인덱스는 기본적이고 중요한 내용입니다. 몇몇 주니어분들과 얘기해 보았을 때 인덱스가 검색 쿼리 성능에 좋다는 정도까지만 알고 사용하고 있었습니다.

 

인덱스에 대해 잘 모르고 사용하면 오히려 검색 쿼리 성능에 악영향을 끼칠 수 있습니다. 인덱스 어떻게 하면 효율적으로 사용할 수 있는지 알아보겠습니다.

 

 

  데이터베이스 인덱스(Index)란?

데이터베이스에서 말하는 인덱스는 쉽게 찾아볼 수 있도록 일정한 순서에 따라 놓은 목록입니다.

 

책을 읽을때 맨 앞 페이지에 목차가 있습니다. 목차에는 해당 페이지의 키워드와 설명이 나와 있는 페이지 번호가 나열되어 있는데 키워드와 페이지 번호로 원하는 정보를 빠르게 찾기 위해서 사용됩니다.

 

즉, 인덱스는 데이터베이스에서 데이터를 빠르게 찾기 위해 사용된다고 보시면 됩니다.

 

 

 

  인덱스 알고리즘

1. Full Table Scan (인덱스 적용 X)

Full Table Scan은 데이터를 순차적으로 접근하는 방법입니다.

테이블에 인덱스가 설정이 되어 있지 않거나 인덱스를 타지 못하는 쿼리를 사용했을 때 발생합니다.

(인덱스를 설정하여도 데이터베이스 옵티마이저가 성능상 효율적이지 못할 때 Full Table Scan이 발생함)

 

유저정보 테이블

1 가유저

2 나유저

3 다유저

4 라유저

5 마유저

6 바유저

찾고자 하는 데이터가 마유저 데이터 일 때 데이터를 가유저 ~ 마유저까지 위에서부터 순차적으로 접근하여 찾게 됩니다.

 

Full Table Scan은 아래와 같은 상황일 때 사용됩니다.

1. 적용 가능한 인덱스가 없는 경우
2. 인덱스 처리 범위가 넓은 경우
3. 크기가 작은 테이블에 액세스 하는 경우

 

 

2. B-Tree (인덱스 적용)

B-Tree의 데이터 구조는 최상단의 루트 노드(root node), 중간 노드들은 브랜치 노드(branch node) 가장 하단의 노드들을 리프 노드(leaf node)라고 부른다. 

<B-Tree 구조>

 

 

실제 모든 데이터는 leaf node에서 관리되고, root node와 branch node에서는 자식 노드의 포인터만 저장하고 있습니다. 

 

B-Tree 구조에서 leaf node에 저장되어 있는 데이터 62를 탐색해 보겠습니다. 

먼저 root node에서 1, 51으로 2번의 탐색이 이루지고, branch node에서 51, 61으로 2번 탐색, 마지막 leaf node에서 61, 62으로 2번의 탐색이 발생하여 총 6번의 탐색으로 데이터를 찾아갈 수 있습니다.

 

반면 Full Table Scan이라면 1~62까지의 전체 데이터를 순차적으로 탐색합니다.

 

 

 

  CRUD에서의 인덱스

1. SELECT

인덱스가 설정된 컬럼에 WHERE이 사용된 쿼리를 수행하면 성능이 향상됩니다.

하지만 전체 데이터의 10 ~ 15% 이상의 데이터를 처리하면 오히려 성능이 낮아질 수 있습니다. 예를 들어 성별과 같은 데이터의 range가 적은 컬럼은 비효율적입니다.

 

2. INSERT

1) leaf node에 공간이 남아 있는 경우

<leaf node 삽입1>

데이터 65를 삽입을 해보겠습니다.

기존에 확보되어 있는 공간에서 데이터가 기록되기 때문에 리소스에 큰 부담이 없을 것입니다.

 

2) leaf node에 공간이 가득 찬 경우

<leaf node 삽입2>

데이터 66을 삽입하려고 하는데 기존 leaf node에 확보되어 있는 공간이 없어 삽입할 수 없습니다.

 

 

<leaf node 삽입3>

leaf node에 공간이 가득 찬 경우 아래와 같이 진행됩니다.

1. 신규 leaf node 공간을 만든다.
2. 기존 데이터와 입력된 데이터를 신규 leaf node에 나누어 저장한다.

위와 같은 작업은 데이터베이스가 느려지고 이는 성능에 큰 영향을 줍니다.

인덱스가 생성될수록 성능에 주는 영향도는 더욱더 커지므로 무분별하게 생성되지 않도록 관리가 필요합니다.

 

3. DELETE

테이블에 DELETE 쿼리가 수행되면 눈에 보이지 않아 데이터가 지워진다고 알고 있지만 인덱스의 데이터를 지우지 않고 사용 안 함 표시를 합니다.

 

4. UPDATE

인덱스 컬럼의 UPDATE 수행 시 아래와 같은 순서로 진행됩니다.

1. DELETE를 통해 기존 데이터를 사용안함 처리
2. INSERT를 통해 변경된 데이터를 처리

위와 같은 이유로 인덱스 컬럼에 수정 작업이 많아질수록 성능이 저하됩니다.

 

 

 

  정리

인덱스를 사용하면 WHERE을 사용하는 쿼리 속도 향상에는 좋아질 수 있습니다.

하지만 INSERT, DELETE, UPDATE 수행 시 leaf node 분할과 데이터 사용 안 함으로 인덱스 조각화가 증가함에 따라 성능 저하를 불러올 수 있습니다. 

 

인덱스를 언제 사용하면 효율적인지 아래와 같이 정리해 보았습니다.

• 데이터 중복도가 낮은 컬럼 (유니크한 값이 많이 있을수록 좋음)
• INSERT, DELETE, UPDATE 작업이 빈번하게 발생하지 않는 컬럼
• WHERE절이 자주 사용되는 컬럼
• 데이터의 규모가 방대한 테이블

 

인덱스는 쿼리 속도를 위해 무작정 사용하는 것보다 관리도 중요합니다. 인덱스 특성을 파악해서 업무의 중요도에 따라 인덱스를 효율적으로 사용하시기 바랍니다.