데이터베이스 인덱싱 (Database Indexing)

인덱스는 책의 목차와 유사한 역할을 한다.
데이터베이스에서 인덱스를 사용하면 전체 테이블을 스캔하지 않고도 원하는 데이터를 빠르게 찾을 수 있다.
인덱스는 테이블의 하나 또는 여러 개의 컬럼을 기반으로 생성될 수 있습니다.

특징:

  1. 자동 정렬

    • 인덱스는 항상 정렬된 상태를 유지한다.
    • 새로운 데이터가 추가될 때마다 정렬된 순서를 유지하기 위해 재정렬이 발생한다.

  2. 독립적 저장

    • 인덱스는 실제 데이터와 별도의 공간에 저장된다.
    • 원본 데이터의 위치를 가리키는 포인터를 포함한다.

  3. 선택적 생성

    • 모든 칼럼에 인덱스를 생성할 필요는 없다.
    • 검색이 자주 발생하는 칼럼에 대해 선택적으로 생성한다.

장점:

  1. 검색 속도 향상

    • 전체 테이블을 스캔하지 않고 인덱스를 통해 빠르게 데이터를 찾을 수 있습니다.
    • WHERE 절의 조건이나 JOIN 연산의 효율성이 크게 향상됩니다.

  2. 정렬 비용 감소

    • ORDER BY 절을 사용할 때 이미 정렬된 인덱스를 활용할 수 있습니다.
    • 추가적인 정렬 작업이 필요하지 않아 성능이 향상됩니다.

  3. 테이블 스캔 감소

    • 필요한 데이터만 선별적으로 접근할 수 있어 시스템 리소스 사용이 감소합니다.

단점:

  1. 추가 저장 공간 필요

    • 인덱스는 별도의 저장 공간을 필요로 합니다.
    • 데이터베이스 크기가 증가할수록 인덱스가 차지하는 공간도 증가합니다.

  2. 데이터 변경 작업의 성능 저하

    • INSERT, UPDATE, DELETE 작업 시 인덱스도 함께 수정해야 합니다.
    • 이로 인해 데이터 변경 작업의 속도가 저하될 수 있습니다.

인덱스 최적화 전략:

  1. 선별적 인덱스 생성

    • 검색이 자주 발생하는 칼럼에 대해서만 인덱스를 생성합니다.
    • 불필요한 인덱스는 제거하여 시스템 부하를 줄입니다.

  2. 복합 인덱스 활용

    • 함께 자주 검색되는 칼럼들에 대해 복합 인덱스를 생성합니다.
    • 칼럼의 순서를 신중히 결정하여 효율성을 극대화합니다.

  3. 인덱스 재구성

    • 주기적으로 인덱스를 재구성하여 단편화를 제거합니다.
    • 성능 저하를 예방하고 최적의 상태를 유지합니다.

  4. 사용 빈도가 높은 쿼리와 해당 컬럼을 파악하여 인덱스를 생성한다.

  5. 인덱스의 크기와 유지 관리 비용을 고려하여 주기적으로 점검하고 불필요한 인덱스는 제거한다.

  6. 쿼리 최적화와 함께 인덱스 최적화를 고려한다.

  7. 정기적인 성능 모니터링과 리팩토링을 통해 인덱스 전략을 지속적으로 개선한다.

주의사항:

  1. 인덱스를 과도하게 사용하면 오히려 성능이 저하될 수 있다.
  2. 데이터의 변경이 빈번한 컬럼보다는 조회가 주로 이루어지는 컬럼에 인덱스를 생성하는 것이 좋다.
  3. Cardinality가 높은 컬럼을 우선적으로 인덱싱하는 것이 검색 성능에 유리하다.

인덱스의 종류

인덱스는 여러 기준에 따라 다양하게 분류될 수 있다.
각 분류 기준에 따른 인덱스 종류를 살펴보자.

구조에 따른 분류

데이터가 인덱스와 물리적으로 어떻게 연관되어 있는지를 기준으로 나뉜다.

  1. 클러스터형 인덱스 (Clustered Index)
    클러스터형 인덱스는 테이블의 데이터가 인덱스의 순서에 따라 물리적으로 정렬되어 저장되는 방식.
    즉, 데이터 자체가 인덱스를 구성하며, 인덱스의 키 값 순서에 따라 데이터가 정렬된다.
    특징:
    1. 데이터 정렬: 테이블의 데이터가 자동으로 정렬되며, 인덱스 키 값이 데이터의 저장 순서를 결정한다.
    2. 테이블당 하나만 생성 가능: 클러스터형 인덱스는 데이터의 물리적 저장 방식을 변경하기 때문에 하나의 테이블에 하나만 생성할 수 있다.
    3. 빠른 검색: 범위 검색이나 정렬된 결과를 반환하는 쿼리에 매우 효율적이다.
    장점:

    1. 빠른 범위 검색: 데이터를 물리적으로 정렬하므로 범위 기반 검색이 빠르다.
    2. 효율적인 정렬 작업: ORDER BY와 같은 정렬 작업에서 추가적인 비용이 거의 들지 않는다.
      단점:
    3. 데이터 수정 비용 증가: 데이터를 삽입, 삭제, 수정할 때마다 물리적 정렬을 유지해야 하므로 오버헤드가 발생한다.
    4. 추가 저장 공간 필요: 클러스터형 인덱스를 유지하기 위한 메타데이터가 필요하다.
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

    -- employees 테이블 생성
CREATE TABLE employees (
    id INT PRIMARY KEY,
    last_name VARCHAR(50),
    first_name VARCHAR(50),
    age INT,
    department VARCHAR(50)
);

-- id 컬럼을 기준으로 클러스터형 인덱스 생성
CREATE CLUSTERED INDEX idx_id ON employees(id);

  2. 비클러스터형 인덱스 (Non-Clustered Index)
    비클러스터형 인덱스는 테이블의 데이터와 별도로 저장되며, 인덱스는 데이터의 위치를 가리키는 포인터를 포함한다.
    데이터 자체는 물리적으로 정렬되지 않고, 별도의 구조로 관리된다.
    특징:
    1. 독립적인 데이터 구조: 비클러스터형 인덱스는 테이블 데이터와 별도로 저장된다.
    2. 여러 개 생성 가능: 하나의 테이블에 여러 개의 비클러스터형 인덱스를 생성할 수 있다.
    3. 포인터 사용: 인덱스를 통해 데이터를 찾을 때 포인터를 사용하여 실제 데이터를 참조한다.
    장점:
    1. 유연성: 여러 열이나 열 조합에 대해 다양한 비클러스터형 인덱스를 생성할 수 있다.
    2. 데이터 변경 시 영향 적음: 클러스터형 인덱스처럼 물리적 정렬을 유지할 필요가 없어 삽입/삭제 시 부담이 적다.
    단점:
    1. 속도 저하 가능성: 데이터를 검색할 때 한 번 더 포인터를 통해 실제 데이터를 참조해야 하므로 클러스터형보다 느릴 수 있다.
    2. 추가 저장 공간 필요: 별도의 구조로 저장되기 때문에 추가적인 저장 공간이 요구됩니다.

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

    -- employees 테이블 생성
CREATE TABLE employees (
    id INT PRIMARY KEY,
    last_name VARCHAR(50),
    first_name VARCHAR(50),
    age INT,
    department VARCHAR(50)
);

-- last_name 컬럼에 대한 비클러스터형 인덱스 생성
CREATE NONCLUSTERED INDEX idx_last_name ON employees(last_name);
특징클러스터형 인덱스비클러스터형 인덱스
데이터 정렬 여부물리적으로 정렬됨별도로 저장되며 물리적 순서와 무관
테이블당 개수 제한하나만 가능여러 개 가능
검색 속도범위 검색 및 정렬 작업에 매우 빠름포인터를 통해 접근하므로 다소 느림
저장 공간 요구량상대적으로 적음추가적인 저장 공간 필요
데이터 변경 비용높음낮음

사용 목적과 테이블 특성에 따라 선택해야 한다.

  • 클러스터형은 범위 검색과 정렬 작업이 많은 경우 적합하며,
  • 비클러스터형은 다양한 열 조합으로 검색해야 하는 경우 유용합니다.

키 속성에 따른 분류

인덱스가 테이블의 키와 어떤 관계를 가지는지를 기준으로 나눈다.

  1. 기본 인덱스 (Primary Index)
    기본 인덱스는 테이블의 **기본 키(Primary Key)**에 대해 자동으로 생성되는 인덱스이다.
    기본 키는 테이블의 각 행을 고유하게 식별하며, 데이터 무결성을 보장한다.
    일반적으로 클러스터형 인덱스로 구현되며, 데이터가 물리적으로 정렬된다.
    특징:

    1. 고유성 보장: 기본 키 값은 중복될 수 없으며, NULL 값을 허용하지 않는다.
    2. 데이터 정렬: 기본 키를 기준으로 데이터가 물리적으로 정렬된다.
    3. 테이블당 하나만 생성 가능: 한 테이블에 하나의 기본 인덱스만 존재할 수 있다.
      장점:
      1. 데이터 검색 속도 향상: 기본 키를 이용한 검색이 매우 빠르다.
      2. 데이터 무결성 보장: 고유성과 NULL 금지를 통해 데이터의 일관성을 유지한다.
        단점:
      3. 삽입/삭제/수정 시 오버헤드: 데이터 정렬을 유지해야 하므로 성능 저하가 발생할 수 있다.
      4. 테이블당 하나만 생성 가능: 추가적인 키를 기준으로 정렬하려면 보조 인덱스를 사용해야 한다.
    1
2
3
4
5
6

    -- employees 테이블 생성 시 기본키 설정
CREATE TABLE employees (
    id INT PRIMARY KEY,
    last_name VARCHAR(50),
    first_name VARCHAR(50)
);

  2. 보조 인덱스 (Secondary Index)
    보조 인덱스는 기본 키 외의 열(Column)에 대해 생성되는 인덱스를 의미한다.
    기본적으로 비클러스터형 인덱스로 구현되며, 데이터와 별도로 저장된다.
    특징
    1. 다양한 열에 생성 가능: 기본 키가 아닌 열에도 생성할 수 있다.
    2. 포인터 사용: 보조 인덱스는 실제 데이터를 가리키는 포인터를 포함한다.
    3. 데이터 정렬 없음: 보조 인덱스를 생성한다고 해서 데이터가 물리적으로 정렬되지는 않는다.
    장점
    1. 다양한 검색 조건 지원: 기본 키 외의 열을 기준으로 효율적인 검색이 가능하다.
    2. 여러 개 생성 가능: 하나의 테이블에 여러 개의 보조 인덱스를 생성할 수 있다.
    단점:

    1. 추가적인 저장 공간 필요: 보조 인덱스를 저장하기 위한 별도의 구조가 필요하다.
    2. 검색 속도 저하 가능성: 데이터를 검색할 때 포인터를 통해 실제 데이터를 참조해야 하므로 기본 인덱스보다 느릴 수 있다.
1
2

-- employees 테이블에서 last_name에 대한 보조 인덱스 생성
CREATE INDEX idx_last_name ON employees(last_name);
특징기본 인덱스 (Primary Index)보조 인덱스 (Secondary Index)
정렬 여부데이터가 물리적으로 정렬됨데이터와 별도로 저장, 정렬되지 않음
유일성고유성을 강제고유성 강제하지 않음
생성 가능 개수테이블당 하나여러 개 생성 가능
검색 속도빠름포인터 참조로 인해 다소 느림
저장 공간 요구량상대적으로 적음추가적인 저장 공간 필요

데이터 커버리지에 따른 분류

데이터 커버리지란 인덱스가 실제 데이터를 얼마나 세밀하게 가리키는지를 의미한다.

  1. 밀집 인덱스 (Dense Index)
    밀집 인덱스는 데이터 파일의 모든 검색 키 값에 대해 인덱스 엔트리를 가지고 있는 인덱스.
    특징:
    1. 모든 레코드에 대해 인덱스 엔트리가 존재한다.
    2. 인덱스 크기가 상대적으로 큽니다.
    3. 데이터 검색 속도가 빠릅니다.
    장점:

    1. 모든 레코드에 대한 직접적인 접근이 가능하다.
    2. COUNT() 같은 집계 함수를 사용할 때 데이터 파일에 접근하지 않고도 처리할 수 있어 효율적이다.
      단점:
    3. 인덱스 크기가 크므로 저장 공간을 많이 차지한다.
    4. 데이터 변경 시 인덱스 업데이트 비용이 높다.
    1
2
3
4
5

    학번: 모든 학생의 학번에 대해 인덱스 생성
1001 → 레코드 위치 1
1002 → 레코드 위치 2
1003 → 레코드 위치 3
1004 → 레코드 위치 4

  2. 희소 인덱스 (Sparse Index)
    희소 인덱스는 데이터 파일의 일부 레코드 또는 데이터 블록에 대해서만 인덱스 엔트리를 가지고 있는 인덱스이다.
    특징:
    1. 각 데이터 블록을 대표하는 키 값만 인덱스에 포함된다.
    2. 인덱스 크기가 상대적으로 작다.
    3. 데이터의 물리적 순서에 의존한다.
    장점:

    1. 인덱스 크기가 작아 저장 공간을 적게 사용한다.
    2. 인덱스 갱신 비용이 낮다.
    3. 일반적으로 밀집 인덱스보다 인덱스 단계 수가 1정도 적어 디스크 접근 횟수가 줄어들 수 있다.
      단점:
    4. 특정 레코드를 찾기 위해 추가적인 탐색이 필요할 수 있다.
    5. 데이터 파일의 물리적 순서에 의존하므로 유연성이 떨어진다.
    1
2
3
4
5

    학년별로 그룹화된 데이터의 시작점만 인덱스 생성
1학년 시작 → 레코드 위치 1
2학년 시작 → 레코드 위치 251
3학년 시작 → 레코드 위치 501
4학년 시작 → 레코드 위치 751

인덱스 선택 기준

  1. 데이터 특성 고려
    • 고유한 값이 많고 정확한 검색이 필요한 경우 → 밀집 인덱스
    • 데이터가 정렬되어 있고 범위 검색이 많은 경우 → 희소 인덱스
  2. 시스템 리소스 고려
    • 저장 공간이 충분하고 검색 성능이 중요한 경우 → 밀집 인덱스
    • 저장 공간이 제한적이고 데이터가 잘 정렬된 경우 → 희소 인덱스
  3. 데이터 변경 빈도 고려
    • 데이터 변경이 적고 빠른 검색이 필요한 경우 → 밀집 인덱스
    • 데이터 변경이 빈번한 경우 → 희소 인덱스

키 구성에 따른 분류

인덱스를 구성하는 컬럼의 수에 따라 나뉜다.

  1. 단일 키 인덱스 (Single-Key Index)
    단일 키 인덱스는 하나의 컬럼만을 사용하여 생성된 인덱스.
    특징:
    1. 구조가 간단하고 구현이 쉽다.
    2. 특정 컬럼에 대한 검색 속도를 향상시킨다.
    3. 데이터베이스 시스템의 부하를 줄일 수 있다.
    장점:
    1. 구현이 간단하고 유지보수가 쉽다.
    2. 특정 컬럼에 대한 검색이 빈번할 때 효과적.
      단점:
    3. 여러 컬럼을 조합한 복잡한 쿼리에는 효율성이 떨어질 수 있다.
    4. 다중 조건 검색에는 적합하지 않을 수 있다.
  2. 복합 키 인덱스 (Composite Index)
    복합 키 인덱스는 두 개 이상의 컬럼을 조합하여 생성된 인덱스.
    특징:
    1. 여러 컬럼을 조합하여 하나의 인덱스로 만든다.
    2. 컬럼의 순서가 중요하다.
    3. 최대 32개까지의 컬럼을 조합할 수 있다.
    장점:
    1. 여러 컬럼을 동시에 검색할 때 검색 속도가 개선된다.
    2. 데이터 정렬의 효율성이 높아진다.
    3. 인덱스의 용량을 절감할 수 있다.
    4. 복잡한 쿼리의 최적화에 도움이 된다.
      단점
    5. 인덱스 생성 시 컬럼 순서가 중요하므로 설계에 주의가 필요하다.
    6. 첫 번째 컬럼이 조건에 포함되지 않으면 인덱스가 효과적으로 작동하지 않을 수 있다.
    7. 너무 많은 컬럼을 포함하면 오히려 성능이 저하될 수 있다.
      사용 시 주의사항:
    8. WHERE 절에 자주 사용되는 컬럼들로 구성해야 한다.
    9. 컬럼의 순서는 검색 조건에서 자주 사용되는 순서대로 지정해야 한다.
    10. 인덱스에 포함된 컬럼 수가 많아질수록 성능이 저하될 수 있으므로 적절한 수의 컬럼을 선택해야 한다.

고유성에 따른 분류

  1. 고유 인덱스 (Unique Index)

    • 인덱스 키 값이 테이블 내에서 유일함을 보장합니다.
    • 중복된 값을 허용하지 않습니다.

  2. 비고유 인덱스 (Non-Unique Index)

    • 인덱스 키 값의 중복을 허용합니다.

특수 목적 인덱스

  1. 비트맵 인덱스 (Bitmap Index)

    • 적은 수의 고유 값을 가진 컬럼에 효과적입니다.
    • 비트 벡터를 사용하여 데이터의 존재 여부를 표현합니다.

  2. 함수 기반 인덱스 (Function-Based Index)

    • 컬럼의 값 자체가 아닌, 컬럼에 특정 함수를 적용한 결과를 인덱싱합니다.
    • 함수나 수식이 포함된 조건 검색에 유용하다.

  3. 공간 인덱스 (Spatial Index)

    • 지리적 데이터나 다차원 데이터를 효율적으로 검색하기 위해 사용됩니다.

  4. 전문 인덱스 (Full-Text Index)

    • 텍스트 데이터의 전체 내용을 검색하는 데 사용됩니다.
    • 문서나 게시글 내용 검색에 사용된다.

데이터 구조에 따른 분류

  1. B-트리 인덱스 (B-Tree Index)
  • 가장 일반적으로 사용되는 인덱스 구조입니다.
  • 균형 잡힌 트리 구조로, 검색, 삽입, 삭제 연산이 효율적입니다.
  1. 해시 인덱스 (Hash Index)
  • 해시 함수를 사용하여 키를 특정 버킷에 매핑합니다.
  • 등호 비교에는 매우 효율적이지만, 범위 검색에는 적합하지 않습니다.
  1. R-트리 인덱스 (R-Tree Index)
  • 공간 데이터를 위한 특수한 구조입니다.
  • 지리 정보 시스템에서 주로 사용됩니다.

참고 및 출처