[DB] 인덱스(Index)의 개념과 특징

인덱스(Index)

인덱스(Index)는 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조이다.

인덱스를 사용하면 특정 컬럼(또는 컬럼 조합)의 값을 기반으로 데이터를 빠르게 찾을 수 있다. 인덱스를 활용하면, 데이터를 조회하는 SELECT 외에도 UPDATE 나 DELETE 의 성능이 향상된다. 그러한 이유는 해당 연산을 수행하려면 해당 대상을 조회해야만 작업을 할 수 있기 때문이다.

인덱스가 있으면 O(logn) 시간 안에 정확한 행을 찾아 작업을 수행하지만, 인덱스가 없으면 O(n) 시간이 걸려 성능이 저하된다.

보조 인덱스는 (당연하게) B+트리 형태로 디스크에 저장되며, 테이블 테이터와는 독립적으로 유지된다.

InnoDB 스토리지 엔진에서의 클러스터링 인덱스와 보조 인덱스 동작 방식

클러스터링 인덱스

클러스터링 인덱스는 테이블의 데이터를 PK 기준으로 정렬하여 저장하는 B+트리 구조이다.
클러스터링 인덱스의 **리프 노드에는 해당 행의 “실제 데이터 전체”**가 저장됨
InnoDB 에서는 테이블 자체가 클러스터링 인덱스로 저장되며, 리프 노드까지 탐색하면 바로 데이터를 가져올 수 있다.

특징

리프 노드에 모든 컬럼이 저장됨(실제 테이블 데이터)
테이블의 PK 를 기준으로 자동 정렬됨
테이블 데이터와 인덱스가 동일한(하나의) B+트리 구조에서 관리됨
PK 조회 속도가 빠름(O(logn))

예시) 클러스터링 인덱스 및 클러스터링 인덱스의 B+트리 구조

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10,2)
) ENGINE=InnoDB;

        [50]
       /    \\
   [10, 20]  [60, 70]

리프 노드:
   [10, "Alice", 5000]
   [20, "Bob", 6000]
   [60, "Charlie", 7000]

보조 인덱스

보조 인덱스는 PK 가 아닌 다른 컬럼에 대한 B+트리 인덱스로, 테이블의 실제 데이터는 포함하지 않고, PK 를 리프 노드에 저장함
보조 인덱스를 사용하면 리프 노드에는 인덱싱된 컬럼 값 + 해당 행의 PK 값(클러스터링 인덱스를 가리키는 포인터)이 저장됨
따라서 보조 인덱스를 통해 데이터를 검색할 때는 보조 인덱스를 통해 PK 를 찾고, 클러스터링 인덱스를 추가로 조회(INDEX LOOKUP)해야함

특징

리프 노드에는 인덱싱된 컬럼 값과 PK(포인터)만 저장
보조 인덱스만으로 테이블의 전체 데이터를 가져올 수 없음
보조 인덱스를 통해 찾은 PK로 클러스터링 인덱스를 추가로 조회해야 함
조회 속도가 PK 보다 상대적으로 늘미(추가적인 I/O 발생)

예시) 보조 인덱스 및 보조 인덱스의 B+트리 구조

CREATE INDEX idx_name ON employees(name);

        [Alice, Bob, Charlie]
          |      |       |
        (PK=10)(PK=20)(PK=60)

MySQL 에서 조건(PK 존재 여부, 인덱스 적용 여부)에 따른 조회 방식

PK 가 존재하는 경우

MySQL 의 InnoDB 스토리지 엔진에서는 PK 가 존재할 경우 테이블 자체가 B+트리 구조로 저장된다. 즉, 테이블의 모든 데이터는 PK 기준으로 정렬되어 B+트리 클러스터링 인덱스 형태로 관리된다.

PK 기준으로 조회할 때(B+트리 적용)
- PK(Primary Key)는 클러스터링 인덱스로 동작하며, 테이블의 모든 데이터는 PK 를 기준으로 B+형태로 정렬 및 저장됨
- PK 로 검색하면 B+트리 탐색을 통해 O(logn) 시간에 데이터를 찾을 수 있음
PK 가 아닌 컬럼을 기준으로 조회할 때 (FULL SCAN 또는 보조 인덱스 적용)
- PK 가 아닌 컬럼을 기준으로 검색할 경우, 보조 인덱스가 없으면 FULL TABLE SCAN이 발생
- 보조 인덱스(Secondary Index)가 존재하면 해당 인덱스를 사용하여 탐색 가능

PK 가 존재하지 않는 경우

PK 가 없는 테이블에서는 MySQL 이 별도의 순서 없이 데이터를 Heap(힙) 형태로 저장한다. 테이블의 행들은 특정한 정렬 순서 없이 디스크의 빈 공간에 무작위로 삽입된다.

인덱스 없는 컬럼을 기준으로 조회할 때
- 테이블에 PK 가 없고, 검색 대상 컬럼에 인덱스가 없는 경우 FULL TABLE SCAN 발생
- MySQL 은 테이블의 모든 행을 스캔하여 조건을 만족하는 데이터를 찾음
보조 인덱스가 적용된 컬럼으로 조회할 때(B+트리 적용)
- PK 가 없어도 보조 인덱스가 존재하는 컬럼에 대한 조회는 인덱스를 통해 B+트리 탐색이 적용됨
- 보조 인덱스를 통해 데이터 위치를 찾고, 필요 시 테이블의 나머지 데이터를 조회하는 방식(INDEX LOOKUP)을 사용

*PK 와 보조 인덱스의 관계

PK 가 존재하면 보조 인덱스는 PK 를 참조하여 데이터를 조회함
PK 가 없으면 보조 인덱스는 MySQL 이 자동으로 생성한 숨겨진 Row ID 를 참조함

인덱스의 관리

DBMS 는 Index 를 항상 최신의 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다. 그렇기 때문에 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE 가 수행된다면 각각 다음과 같은 연산을 추가적으로 해주어야 하며, 그에 따른 오버헤드가 발생한다.

INSERT: 새로운 데이터에 대한 인덱스를 추가
- 새 데이터를 삽입할 때, 인덱스 컬럼이 있는 경우 해당 키 값이 B+트리 인덱스에 새로운 위치에 추가됨
- 삽입 후에도 트리의 정렬이 유지되도록 자동으로 조정됨
DELETE: 삭제하는 데이터의 인덱스에 대해 삭제 표시를 진행
- 데이터를 삭제할 때 즉시 인덱스에서 해당 키를 제거하지 않고, 삭제 표시함
- 이후 백그라운드 스레드(Purge Thread)가 실제 삭제를 한번에 수행함
UPDATE: 수정하는 데이터의 인덱스에 대해 삭제 표시, 갱신된 데이터에 대해 인덱스를 추가
- 해당 데이터를 삭제 표시하고, 새로운 값을 인덱스에 새로 삽입함
- 삽입 후에도 트리의 정렬이 유지되도록 자동으로 조정됨

실제로 삭제하지 않고 삭제 표시(Marked as Deleted)를 하는 이유

성능 최적화(디스크 I/O 최소화)
- B+트리는 삽입, 삭제 시 트리 구조의 조정(재배치 및 병합)이 발생함
- 삭제 작업 시 즉시 인덱스를 재조정하면, 트리 노드의 병합(merge) 또는 재배치(redistribution) 작업이 빈번하게 발생할 수 있으며, 이는 성능 저하의 원인이 됨
- 따라서, MySQL 의 경우는 특정 인덱스 항목을 **“삭제 표시”**하고, 나중에 백그라운드 작업을 통해 실제로 한번에 제거함
삭제 후 복구 지원
- InnoDB 는 MVCC(Multi-Version Concurrency Control) 을 사용하여 트랜잭션을 관리함
- 삭제 시 즉시 제거하면, 롤백 및 스냅샷 읽기가 불가능할 수 있음
- 따라서 삭제된 데이터가 여전히 스토리지에 남아있어야 하며, 일정 기간 후에만 완전히 삭제됨
예시)

DELETE FROM employees WHERE id = 200;
ROLLBACK; -- 삭제 취소

삭제 플래그가 지정된 데이터의 처리 방식

MySQL이 삭제 시 사용하지 않음 표시(Marked as Deleted)를 남기면 다음과 같이 관리된다.

새로운 데이터 삽입 시 공간 재사용
삭제된 인덱스 공간은 새로운 데이터를 삽입할 때 자동으로 재사용됨
백그라운드 정리
InnoDB의 Purge Thread 라는 백그라운드 프로세스가 주기적으로 삭제된 행을 제거하고 트리를 최적화함
테이블 분석 및 수동 최적화
ANALYZE TABLE 을 통해 통계를 갱신하고, OPTIMIZE TABLE 을 사용하여 실제 삭제 및 공간 회수

인덱스의 장점과 단점

장점

테이블을 조회하는 속도와 그에 따른 성능을 향상 시킬 수 있다.
전반적인 시스템의 부하를 줄일 수 있다.

단점

인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장 공간이 필요함
인덱스를 관리하기 위해 추가 작업이 필요함
인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있음

CREATE, DELETE, UPDATE 가 빈번한 속성에 인덱스를 걸게 되면, 인덱스의 크기가 비대해져서 성능이 오히려 저하되는 역효과가 발생할 수 있다. 그러한 이유 중 하나는 DELETE, UPDATE 연산때문이다.

UPDATE 와 DELETE 는 기존의 인덱스를 삭제하지 않고 “삭제 표시” 처리를 한다고 했다. 따라서 어떤 테이블에 UPDATE 와 DELETE 가 빈번하게 발생한다면, 실제 데이터보다 인덱스가 훨씬 많이 존재하게 되어, SQL 문 처리 시 비대해진 인덱스에 의해 성능이 떨어지게 될 것이다.

인덱스를 사용하는 것이 적절하지 않은 경우

자주 변경(INSERT/UPDATE/DELETE)이 발생하는 테이블
- 인덱스는 데이터를 삽입, 수정, 삭제할때마다 유지되어야 하므로, 잦은 변경이 발생하면 인덱스 재구성으로 인해 성능이 저하됨
- 특히 UPDATE 가 인덱스 컬럼에서 발생하면 기존 값을 삭제 표시하고 새 값을 삽입해야 하므로 성능이 더 저하될 수 있음
소량의 데이터가 있는 테이블
- 테이블의 행 수가 적은 경우, 인덱스를 사용해도 큰 성능 향상이 없으며, 오히려 인덱스를 조회하는 비용이 추가됨
- MySQL 옵티마이저는 작은 테이블의 경우 FULL TABLE SCAN 이 더 빠르다고 판단할 수 있음
높은 중복도가 있는 컬럼(저품질 인덱스)
- 인덱스는 검색 시 유니크한 값을 빠르게 찾는 데 유리하지만, 중복도가 높은 컬럼(예: 성별, 상태값, 플래그 값 등)은 인덱스의 효과가 거의 없음
- 예를 들어, 전체 데이터 중 gender 컬럼의 값이 “M”, “F” 두 개뿐이라면 인덱스의 효율이 낮음
WHERE 조건이 인덱스를 활용하지 않은 경우
- 인덱스를 사용하더라도 특정 WHERE 조건이 인덱스를 활용할 수 없는 경우가 있음
- 예를 들어, LIKE ‘%value%’ 검색은 인덱스를 사용할 수 없고, FULL TABLE SCAN 이 발생
연속적인 범위 값(시계열 데이터)에 대한 빈번한 삽입
- 시계열 데이터를 인덱스 키로 설정하면 데이터가 삽입될 때마다 인덱스의 재구성 작업이 빈번하게 발생
  - 증가하는 값 - 비교적 문제점이 적음(트리의 가장 오른쪽 리프 노드 삽입, 리밸런싱 거의 X)
  - 감소하는 값 - 트리의 왼 리프 노드에 삽입, 이는 기존 노드들을 자주 이동시키고, 리프 노드의 스플릿이 빈번하게 발생하여 성능 저하 유발 가능
- 인덱스의 리프 노드가 자주 스플릿되면서 성능이 저하될 수 있음
- 해결법: 시계열 데이터의 경우 클러스터링 인덱스를 적절히 활용하고 파티셔닝을 고
이미 정렬된 데이터(자연 정렬)가 존재하는 경우
- 만약 테이블의 데이터가 특정 컬럼 기준으로 이미 정렬된 상태로 삽입된다면, 인덱스의 필요성이 적음
- 예를 들어, AUTO_INCREMENT 키와 같이 이미 정렬된 데이터를 저장할 때 불필요한 인덱스가 추가되면 오버헤드만 증가함

참고

https://mangkyu.tistory.com/96

'DB' 카테고리의 다른 글

[DB] B-Tree 가 DB 인덱스(Index) 로 사용되는 이유 (0)	2025.01.18

인덱스(Index)

InnoDB 스토리지 엔진에서의 클러스터링 인덱스와 보조 인덱스 동작 방식

MySQL 에서 조건(PK 존재 여부, 인덱스 적용 여부)에 따른 조회 방식

인덱스의 관리

실제로 삭제하지 않고 삭제 표시(Marked as Deleted)를 하는 이유

삭제 플래그가 지정된 데이터의 처리 방식

인덱스의 장점과 단점

인덱스를 사용하는 것이 적절하지 않은 경우

'DB' 카테고리의 다른 글

티스토리툴바