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

데이터베이스 인덱스란?

인덱스는 데이터베이스 테이블에서 검색 속도를 향상시키기 위한 자료구조로, 책의 색인(index)과 비슷한 역할을 합니다. 데이터가 저장될 때 특정 컬럼(또는 컬럼 조합)에 대한 추가적인 정렬 구조를 유지하며, 이를 통해 SELECT 쿼리의 검색 성능을 크게 향상시킬 수 있습니다. 하지만 INSERT, UPDATE, DELETE와 같은 쓰기 작업의 성능은 인덱스를 유지하기 위한 오버헤드 때문에 다소 희생됩니다.

---

1. 인덱스의 자료 구조

(1) B-Tree

• B-Tree는 데이터베이스 인덱스의 표준 자료구조로 널리 사용되며, 균형 트리(Balanced Tree)의 형태를 유지합니다.
• 특징:
  • 모든 리프 노드의 깊이가 동일합니다.
  • 각 노드가 여러 자식 노드를 가지며, 트리의 높이를 낮게 유지하여 검색 시간을 줄입니다.
  • 정렬된 데이터를 효과적으로 관리하고, 검색, 삽입, 삭제 시 O(log N)의 시간 복잡도를 가집니다.

(2) B+Tree

• MySQL InnoDB는 B-Tree의 변형인 B+Tree를 사용합니다.
• 특징:
  • 리프 노드가 연결 리스트 형태로 구성되어 순차 검색이 빠릅니다.
  • 리프 노드에는 인덱스 키와 함께 실제 데이터의 주소값(또는 PK)이 저장됩니다.
  • B-Tree와 달리, 데이터 검색은 항상 리프 노드에서 이루어집니다.

(3) 해시(Hash) 인덱스

• 해시 테이블을 기반으로 구현되며, 특정 키 값에 대해 빠른 검색 속도를 제공합니다.
• 특징:
  • 등호(=) 조건의 검색에서 성능이 매우 뛰어납니다.
  • 범위 검색에는 사용할 수 없습니다.
  • MySQL의 MEMORY 엔진에서 주로 사용되며, 다른 엔진에서는 사용되지 않습니다.

---

2. 인덱스의 종류

(1) 프라이머리 키 인덱스(Primary Key Index)

• 테이블의 기본 키 컬럼에 자동으로 생성되는 클러스터드 인덱스입니다.
• 리프 노드가 실제 데이터 페이지를 직접 가리키며, 데이터가 프라이머리 키 기준으로 정렬되어 저장됩니다.

(2) 세컨더리 인덱스(Secondary Index)

• 프라이머리 키 외의 컬럼에 추가적으로 생성된 인덱스입니다.
• 리프 노드에 프라이머리 키를 저장하여, 검색 시 프라이머리 키를 이용해 다시 데이터 페이지를 읽어야 합니다. 이를 백트래킹(backtracking)이라고 합니다.

(3) 유니크 인덱스(Unique Index)

• 인덱스가 적용된 컬럼의 값이 중복되지 않도록 보장합니다.

(4) 복합 인덱스(Composite Index)

• 여러 컬럼을 결합하여 생성한 인덱스입니다.
• 주의: 복합 인덱스는 선두 컬럼(leading column)부터 순서대로 조건이 충족될 때만 사용됩니다.

(5) 풀텍스트 인덱스(Full-Text Index)

• 문자열 데이터에서 효율적으로 텍스트 검색을 수행하기 위해 사용됩니다.
• MySQL에서 FULLTEXT 키워드를 통해 생성할 수 있으며, MATCH() AGAINST() 구문과 함께 사용됩니다.

---

3. MySQL 스캔 방식

(1) 인덱스 레인지 스캔(Index Range Scan)

• 인덱스의 특정 범위에 대해 검색하는 방식.
• WHERE 절에 범위 조건이 포함된 경우 사용되며, 효율이 가장 높음.
• 예:

  SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

(2) 인덱스 풀 스캔(Index Full Scan)

• 인덱스를 처음부터 끝까지 순차적으로 읽는 방식.
• 데이터 레코드를 모두 읽지 않아도 되는 경우, 풀 테이블 스캔보다 효율적.
• 예:

  SELECT * FROM orders WHERE order_status = 'completed';

(3) 루스 인덱스 스캔(Loose Index Scan)

• 필요한 부분의 인덱스만 읽고 건너뛰는 방식.
• GROUP BY, MAX(), MIN() 등의 집계 함수가 포함된 쿼리에 사용.
• 예:

  SELECT department_id, MAX(salary) FROM employees GROUP BY department_id;

(4) 커버링 인덱스(Covering Index)

• 인덱스만으로 쿼리가 요구하는 데이터를 모두 처리할 수 있는 경우, 테이블 데이터 페이지를 읽지 않음.
• 예:

  SELECT order_id, order_date FROM orders WHERE order_status = 'completed';

  (인덱스가 order_status, order_id, order_date 순서로 생성된 경우)

---

4. 인덱스의 단점

1. 쓰기 작업 성능 저하

   • 인덱스를 유지하기 위해 추가적인 연산이 필요하므로, INSERT, UPDATE, DELETE 성능이 저하됩니다.

2. 공간 사용 증가

   • 인덱스를 저장하기 위한 추가적인 디스크 공간이 필요합니다.

3. 인덱스의 남용

   • 너무 많은 인덱스를 생성하면 쓰기 성능과 쿼리 최적화에 오히려 악영향을 줄 수 있습니다.

---

5. 인덱스 설계 시 고려 사항

1. 쿼리 패턴 분석

   • 자주 사용하는 SELECT 쿼리에 적합한 인덱스를 설계.

2. 복합 인덱스 순서

   • WHERE 절 조건에 나오는 컬럼 순서를 고려하여 설계.
   • 선두 컬럼부터 조건이 충족되지 않으면 인덱스를 사용할 수 없음.

3. 쓰기 작업과의 균형

   • 쓰기 작업이 많은 테이블에서는 불필요한 인덱스를 최소화.

4. 카디널리티(Cardinality)

   • 인덱스 컬럼의 중복도를 분석하여, 중복도가 낮은(유니크한 값이 많은) 컬럼을 우선 선택.

---

6. 프로덕션 환경에서의 인덱스 관리

1. EXPLAIN 사용

   • 쿼리 실행 계획을 확인하여 인덱스가 제대로 사용되고 있는지 분석.

2. 인덱스 재구성

   • 데이터 변경이 많아 조각화가 심한 경우, OPTIMIZE TABLE 명령을 통해 재구성.

3. 모니터링 및 삭제

   • 사용되지 않는 인덱스를 찾아 제거하여 성능을 최적화.

---

7. 추가적인 주제

• 클러스터드 인덱스 vs 넌클러스터드 인덱스

  • 클러스터드 인덱스: 테이블 자체가 정렬되어 저장됨 (MySQL InnoDB의 PK).
  • 넌클러스터드 인덱스: 테이블 외부에 별도로 관리되며, 테이블 데이터의 주소를 가짐.

• 파티셔닝과 인덱스

  • 대용량 테이블에서 데이터 파티셔닝(partitioning)과 인덱스를 함께 사용하면 성능 최적화에 도움이 됨.

---

요약

• 인덱스는 데이터 검색을 빠르게 하지만 쓰기 성능에는 영향을 줌.
• B+Tree를 기반으로 MySQL 인덱스가 동작하며, 다양한 스캔 방식(EXPLAIN으로 확인 가능)이 존재.
• 적절한 설계와 관리가 인덱스 성능 최적화의 핵심.

추가 학습 자료

• [Database] 인덱스(index)란?
• [MySQL] 프라이머리 키(PK, Primary Key)에 대해 쉽고 완벽하게 이해하기
• [MySQL] B-Tree로 인덱스(Index)에 대해 쉽고 완벽하게 이해하기
• [10분 테코톡] 안돌의 INDEX
• [10분 테코톡] 초코칩&로키의 인덱스와 스캔 튜닝