인덱스

데이터베이스 인덱스란?

인덱스는 책의 목차처럼 테이블에 저장된 데이터에 빠르게 접근하기 위해 사용되는 구조이다.

인덱스에 사용되는 키를 기준으로, 데이터를 정렬된 방식으로 저장하여 데이터의 검색 속도를 크게 향상한다.

그 대신 데이터를 추가, 수정, 삭제할 때 정렬 과정과, 추가적인 저장 공간을 차지하기 때문에 오버헤드를 유발할 수 있다.

---

클러스터드 인덱스와 논클러스터드 인덱스

인덱스는 클러스터드 인덱스와 논클러스터드 인덱스로 나뉜다.

클러스터드 인덱스는 테이블의 데이터 자체가 인덱스에 의해 정렬이 된다. 물리적인 데이터 정렬을 의미하기 때문에 하나만 존재한다.

논클러스터드 인덱스는 인덱스를 위한 별도의 테이블이 생성된다.

1. 클러스터드 인덱스 (Clustered Index):
   • 물리적 데이터 정렬: 테이블의 데이터 자체가 인덱스에 의해 정렬된다. 테이블이 클러스터드 인덱스에 따라 재구성된다.
   • 단일 존재: 하나의 테이블에 하나의 클러스터드 인덱스만 생성할 수 있다.
   • PK와의 관계: 기본 키(Primary Key)는 기본적으로 클러스터드 인덱스로 설정되는 경우가 많다. Mysql의 경우 PK를 설정하면 기본적으로 클러스터드 인덱스가 된다.
   • 추가 테이블 생성 여부: 클러스터드 인덱스는 테이블 자체에 영향을 주며, 별도의 테이블을 생성하지 않는다. (데이터 정렬과 관련된 내부 데이터 구조가 관리된다.)
   • 삽입/수정 오버헤드: 데이터가 물리적으로 정렬되어 있어, 새로운 데이터를 삽입하거나 수정할 때 기존 데이터의 위치를 재조정해야 한다. 데이터가 정렬 순서를 유지하려면 최악의 경우 O(N) 시간이 소요될 수 있다. 따라서 클러스터드 인덱스의 키는 변경 가능성이 낮은 값으로 설정하는 것이 좋다. PK를 변경되지 않는 고유한 값으로 설정하는 이유와도 같다.
     • PK를 전화번호로 설정할 경우 전화번호가 바뀌면 테이블이 다시 정렬되어야 함.
   • 장점: 데이터 검색이 매우 빠르다.
   • 단점: 데이터 삽입 및 수정 시 오버헤드가 발생한다.
2. 논클러스터드 인덱스 (Non-Clustered Index):
   • 물리적 데이터 미정렬: 테이블 데이터는 정렬되지 않지만, 인덱스 자체는 별도로 정렬된다.
   • 다수 생성 가능: 하나의 테이블에 여러 개의 논클러스터드 인덱스를 생성할 수 있다.
   • 추가 테이블 생성: 논클러스터드 인덱스는 별도의 인덱스 테이블을 생성하여 원본 데이터의 포인터를 저장한다.
   • 삽입/수정 오버헤드: 테이블 데이터는 변경되지 않지만, 별도의 인덱스 테이블에 대한 수정이 필요하다. 상대적으로 클러스터드 인덱스보다 삽입/수정 오버헤드가 적다. O(log N)
   • 장점: 다양한 쿼리에 대해 유연하게 사용할 수 있다.
   • 단점: 데이터 접근 시 추가적인 조회가 필요해 클러스터드 인덱스보다 느릴 수 있다.

클러스터드 인덱스와 논클러스터드를 나누지 않고 인덱스의 장점과 단점에 대해 이야기하면, 읽기 작업에서는 빠른 성능을 보이지만 삽입&수정 작업에는 오버헤드가 발생한다. (데이터 크기가 작거나, 카디널리티가 작은 경우 읽기 성능에 큰 차이가 없을 수 있다.)

---

인덱스의 데이터 구조

B-트리(왼쪽)와 B+트리(오른쪽)

 

1. B-트리와 B+트리:
   • B-트리 : 가장 대표적인 인덱스 구조이다. 각 노드의 키값은 1개이고 자식 노드 개수가 최대 2개인 이진 탐색 트리와 달리, N개의 자식 노드를 가질 수 있는 자료구조이다.  노드가 키 값을 정렬된 상태로 유지하며, 데이터는 리프 노드뿐만 아니라 내부 노드에도 저장된다.
   • 특징
     • 균형 유지: 모든 리프 노드는 동일한 높이를 가진다.
     • 분기 구조: 각 노드가 여러 키와 자식 포인터를 가질 수 있어 검색, 삽입, 삭제가 효율적이다.
     • 높이 제어: 트리의 높이가 낮아, 디스크 접근 횟수가 줄어든다.
   • B+트리: B-트리의 변형으로, 데이터는 리프 노드에만 저장되며, 리프 노드 간에 연결 리스트로 연결되어 있어 순차 접근이 더 빠르다. (범위 탐색에 더욱 적합)
   • 사용 이유: 데이터 검색, 삽입, 삭제가 O(log n)의 시간 복잡도를 가지며, 안정적이고 빠르다.
2. 해시(Hash):
   • 키를 해싱하여 고유한 값으로 변환해 데이터를 저장한다.
   • 장점: 정확한 키 검색 속도가 매우 빠릅니다. O(1)
   • 단점: 범위 검색을 지원하지 않아 잘 사용되지 않는다.

---

효율적인 인덱스를 선택하는 방법과 쿼리 실행 계획

인덱스가 적용되는 SQL 절과 사례

1. WHERE 절:
   • 특정 조건에 맞는 데이터를 빠르게 검색.
   • SELECT * FROM employees WHERE employee_id = 100;
     • where절에 사용된 employee_id 컬럼에 인덱스가 있으면 해당 인덱스를 사용해 조회한다.
2. JOIN 절:
   • JOIN 키에 인덱스를 생성하면 성능이 향상한다.
   • SELECT * FROM employees e JOIN departments d ON e.department_id = d.department_id;
3. ORDER BY 절:
   • 인덱스가 정렬된 데이터를 이용해 빠르게 정렬 수행.
   • SELECT * FROM employees ORDER BY last_name;
4. GROUP BY 절:
   • 그룹화 성능이 인덱스를 통해 최적화될 수 있다.
   • SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

이런 조건절을 사용할 경우 인덱스를 고려해 볼 수 있다.

 

또, 효율적인 인덱스를 선택하려면 쿼리 실행 계획(Query Execution Plan)을 분석해야 한다.

MySQL에서는 EXPLAIN 키워드를 사용하여 실행 계획을 확인할 수 있다.

EXPLAIN SELECT * FROM employees WHERE employee_id = 100;

실행 계획 주요 정보:

• type: 접근 방식(예: ALL, index, range 등).
• key: 사용된 인덱스.
• rows: 검색할 행의 예상 수.
• Extra: 추가 정보(예: Using index, Using where 등).

예를 들면 인덱스(employee_id)와 인덱스(employee_id, score)와 같이 두 개의 인덱스가 있다면 둘 중 어떤 것을 사용할지 옵티마이저가 선택을 할 수 있다.

 

커버링 인덱스(Covering Index)

• 커버링 인덱스란 쿼리 실행에 필요한 모든 컬럼이 인덱스에 포함되어 있어 테이블 데이터에 접근하지 않고 인덱스만으로 쿼리를 처리할 수 있는 인덱스이다. 이를 통해 디스크 I/O를 줄이고 성능을 향상할 수 있다.

CREATE INDEX idx_covering ON employees (department_id, last_name, first_name);

SELECT first_name, last_name 
FROM employees 
WHERE department_id = 10 AND last_name = 'Smith';

 

위 예시에서는 select에 사용된 first_name과 last_name이 사용하는 인덱스에 모두 포함되어 있다.

그래서 해당 sql문이 실행된다면 인덱스 테이블을 조회하고 추가적인 테이블 조회가 필요없이 원하는 결과를 얻을 수 있다.

결과적으로 추가적인 디스크 I/O 작업이 일어나지 않아 더욱 빠른 성능으로 조회가 가능해진다.

---

 

그 외

 

인덱스 통계와 복합 인덱스

1. 인덱스 통계:
   • 데이터베이스는 인덱스에 대한 통계 정보를 유지하며, 옵티마이저는 이를 사용해 최적의 실행 계획을 결정한다.
   • 통계가 오래되면 실행 계획이 비효율적으로 결정될 수 있으므로 주기적으로 갱신이 필요하다. MySQL에서는 ANALYZE TABLE 명령어를 통해 통계를 갱신할 수 있다.
2. 복합 인덱스:
   • 두 개 이상의 컬럼을 조합한 인덱스이다.
   • 데이터는 첫 번째 컬럼부터 순서대로 정렬되며, 쿼리가 복합 인덱스를 효율적으로 사용하려면 컬럼 순서가 중요하다.
     • 정렬 방식: 첫 번째 컬럼으로 정렬된 후, 동일한 값들에 대해 두 번째 컬럼으로 정렬.
   • CREATE INDEX idx_name ON employees (department_id, last_name);
   • SELECT * FROM employees WHERE department_id = 10 AND last_name = 'Smith';

---

인덱스가 있어도 풀 스캔하는 경우

1. LIKE 연산자 사용:
   • %로 시작하는 패턴 매칭은 인덱스를 활용하지 못할 수 있다.
   • SELECT * FROM employees WHERE name LIKE '%son';
2. 함수 또는 연산 사용:
   • 컬럼에 함수가 적용되면 인덱스가 무시될 수 있다.
   • SELECT * FROM employees WHERE YEAR(hire_date) = 2020;
3. 데이터의 비균등 분포:
   • 인덱스를 통해 검색해야 할 데이터가 많을 경우, 풀 스캔이 더 효율적일 수 있다.
4. 테이블 데이터 크기가 작은 경우:
   • 테이블에 데이터가 적게 저장된 경우, 인덱스를 사용하는 것보다 풀 스캔이 더 빠를 수 있다. (디스크 I/O를 최소화)

---

 

마무리.

지나치게 많은 인덱스를 생성하면 저장 공간을 많이 차지하고 오히려 성능이 저하될 수 있다.

쿼리 패턴을 분석해 필요한 인덱스를 설계하고, 중복 인덱스를 피하면서 꼭 필요한 컬럼에만 생성하는 것이 좋다.