데이터베이스 인덱스

인데스의 물리적 저장 방식

클러스터드 인덱스 (Clustered Index)

클러스터드 인덱스는 테이블의 데이터 자체가 인덱스 순서대로 정렬되어 저장되는 방식이다.

• 데이터 정렬: 테이블에 있는 모든 행이 인덱스 키 값에 따라 정렬되어 디스크에 저장된다.
• 유일성: 일반적으로 테이블 당 하나의 클러스터드 인덱스만 존재할 수 있다. (예를 들어, InnoDB에서는 기본 키를 클러스터드 인덱스로 사용한다.)
• 데이터 접근: 클러스터드 인덱스를 사용하면, 인덱스 검색 후 별도의 데이터 페이지 접근 없이 바로 원하는 데이터를 읽을 수 있어 조회 성능이 매우 우수하다.
• 범위 검색 효율: 인덱스 순서대로 데이터가 저장되어 있으므로, 범위 검색이나 순차적인 데이터 처리에 효과적이다.
• 장점:
  • 디스크 I/O가 감소하여 읽기 작업에 유리하다.
  • 정렬된 상태로 저장되므로, 순차적 스캔이나 범위 쿼리에서 빠른 성능을 보인다.
• 단점:
  • 데이터 삽입, 업데이트, 삭제 시 데이터의 물리적 위치가 변경될 가능성이 있어, 이러한 작업에서 오버헤드가 발생할 수 있다.
  • 클러스터드 인덱스가 변경되면 관련 데이터 페이지 전체를 재정렬해야 하므로, 쓰기 작업에 민감한 경우 성능 저하가 발생할 수 있다.

넌클러스터드 인덱스 (Non-Clustered Index)

넌클러스터드 인덱스는 데이터와 인덱스가 별도의 영역에 저장된다.

• 인덱스 구조: 인덱스는 키 값과 함께 실제 데이터의 위치(포인터 혹은 로우 ID)를 저장한다.
• 데이터 접근: 인덱스 검색 후, 인덱스에 저장된 포인터를 이용하여 실제 데이터가 저장된 페이지에 접근하는 두 단계의 과정을 거친다.
• 다중 인덱스 가능: 하나의 테이블에 여러 개의 넌클러스터드 인덱스를 생성할 수 있다.
• 유연성: 클러스터드 인덱스와 달리 테이블 데이터의 물리적 순서를 변경하지 않으므로, 다양한 컬럼 조합으로 인덱스를 생성해 쿼리 성능을 향상시킬 수 있다.
• 장점:
  • 여러 개의 인덱스를 생성하여 다양한 쿼리 패턴에 대응할 수 있다.
  • 데이터의 물리적 순서를 변경하지 않기 때문에, 데이터 삽입 및 수정 시 클러스터드 인덱스에 비해 상대적으로 오버헤드가 적을 수 있다.
• 단점:
  • 인덱스를 통해 데이터를 찾은 후 실제 데이터를 조회하는 추가 단계(포인터를 통한 데이터 접근)가 필요하여, 클러스터드 인덱스보다 읽기 성능이 다소 떨어질 수 있다.
  • 인덱스가 많아지면, 데이터 변경 시 각각의 인덱스 업데이트가 필요해져서 쓰기 작업에서 오버헤드가 발생할 수 있다.

인덱스의 내부 자료구조

B-Tree 인덱스

• 구조: B-Tree(또는 B+Tree)는 균형 잡힌 트리 구조로, 각 노드에 여러 키 값을 저장하며 리프 노드까지의 깊이가 거의 동일.
• 특징:
  • 범위 검색 지원: 키의 순서가 유지되므로 범위 검색(BETWEEN, <, >)에 효과적.
  • 부분 일치 검색: 접두사 매칭이나 LIKE 연산(특히 접두사가 고정된 경우)에 유리.
  • 정렬: 정렬된 상태를 유지하므로 ORDER BY와 결합하여 사용할 수 있다.
• 사용 사례: 대부분의 RDBMS에서 기본 인덱스 구조로 사용되며, 범위 검색과 정렬, 접두사 검색 등 다양한 쿼리 패턴에 적합.

Hash 인덱스

• 구조: 해시 테이블 구조로, 키에 대해 해시 함수를 적용하여 해시 버킷에 매핑.
• 특징:
  • 정확한 값 매칭에 최적화: = (동등 비교) 연산에서 빠른 조회가 가능.
  • 범위 검색 불가: 해시 값은 순서를 보장하지 않으므로 범위 검색이나 정렬에는 부적합.
  • 충돌 가능성: 해시 충돌이 발생할 수 있으며, 이에 대한 처리가 필요.
• 사용 사례: 메모리 기반 데이터베이스나 캐시 시스템(InnoDB의 Adaptive Hash Index나 Memcached 등)에서, 값의 정확한 매칭이 주된 요구사항일 때 사용.

부분 인덱스 (Partial Index)

부분 인덱스는 전체 테이블이 아니라, 특정 조건을 만족하는 행에 대해서만 인덱스를 생성하는 방식이다.

• 인덱스 생성 조건:
  • WHERE 절과 유사한 조건을 지정하여, 인덱스에 포함될 행을 한정한다.
  • 예를 들어, 상태(status)가 ‘active’인 행만 인덱싱하는 경우.
• 특징:
  • 인덱스 크기 축소: 전체 데이터를 인덱싱하지 않으므로, 인덱스의 크기와 관련된 디스크/메모리 비용이 줄어든다.
  • 특정 쿼리 최적화: 자주 사용하는 조건에 최적화되어 있어, 해당 조건의 검색 성능이 크게 향상된다.
• 장점:
  • 불필요한 데이터까지 인덱싱하지 않아, 인덱스의 관리 비용과 갱신 부담을 줄일 수 있다.
  • 조건을 만족하는 데이터에 대해 빠른 검색이 가능하다.
• 단점:
  • 조건에 맞지 않는 행은 인덱스에 포함되지 않으므로, 해당 조건이 아닌 검색에서는 인덱스 활용이 어려울 수 있다.
  • 적절한 조건 설정이 중요하며, 잘못된 조건 선택 시 오히려 성능에 부정적 영향을 줄 수 있다.

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

함수 기반 인덱스는 단순히 컬럼 값 그 자체가 아니라, 컬럼 값에 특정 함수를 적용한 결과를 인덱싱 대상으로 삼는다.

• 계산된 값 인덱싱:
  • 예를 들어, 문자열을 모두 소문자로 변환한 값이나 날짜에서 연도만 추출한 값 등을 인덱스로 생성할 수 있다.
  • 이렇게 하면, 함수 결과를 조건으로 하는 검색 쿼리에서 빠른 조회가 가능해진다.
• 특징:
  • 유연한 인덱싱: 기존 컬럼 값에 직접 인덱스를 생성하는 것이 어려운 경우에도, 변환된 결과를 인덱싱하여 다양한 쿼리 패턴에 대응할 수 있다.
  • 검색 최적화: 함수 결과를 미리 계산해 인덱스에 저장하므로, 쿼리 실행 시마다 함수 계산 없이 빠르게 결과를 찾을 수 있다.
• 장점:
  • 함수 적용 결과를 기준으로 검색할 때, 쿼리 성능을 크게 개선할 수 있다.
  • 데이터 변형이 필요한 조건에서도 효과적인 인덱스 활용이 가능하다.
• 단점:
  • 인덱스 생성 시 함수 계산 비용이 발생하며, 데이터 변경 시 해당 인덱스의 갱신 비용이 추가된다.
  • 복잡한 함수나 자주 변경되는 함수의 경우, 인덱스의 유지 관리가 어려워질 수 있다.

인덱스를 남용할 경우 성능이 저하되는 이유

• 쓰기 성능 저하:

  인덱스는 데이터 변경(INSERT, UPDATE, DELETE) 시 함께 갱신되어야 한다. 인덱스가 많으면 해당 작업마다 추가 오버헤드가 발생하여 쓰기 성능이 떨어진다.

• 디스크 공간 증가:

  인덱스는 별도의 저장 공간을 차지하므로, 과도한 인덱스 생성은 디스크 공간 낭비와 캐시 효율 저하로 이어진다.

• 쿼리 최적화의 부작용:

  옵티마이저가 여러 인덱스 중에서 잘못된 인덱스를 선택하거나, 인덱스 관리 비용이 증가하면 전체 쿼리 성능이 저하된다.

• 복잡한 인덱스 유지 관리:

  인덱스 구조가 복잡해지면, 인덱스 리빌드나 재구성 작업 등이 필요할 수 있어 관리 비용이 상승한다.

복합 인덱스와 커버링 인덱스

복합 인덱스 (Composite Index)

하나의 인덱스에 두 개 이상의 컬럼(열)을 포함하는 인덱스.

• 인덱스 순서에 따라 왼쪽 접두사 원칙(Leftmost Prefix Principle)이 적용. 즉, 인덱스의 첫 번째 컬럼부터 순차적으로 사용되는 경우에 효과적임.
• 여러 컬럼에 대한 조건을 결합하여 검색할 때 유리함.
• 예시: (first_name, last_name) 컬럼에 대해 복합 인덱스를 생성하면, first_name 또는 first_name과 last_name을 함께 사용하는 쿼리에 효과적임.

커버링 인덱스 (Covering Index)

쿼리에서 요청한 모든 컬럼(SELECT, WHERE, JOIN 조건 등)이 인덱스에 포함되어 있어, 실제 테이블 데이터를 읽지 않고도 인덱스만으로 쿼리를 처리할 수 있는 인덱스.

• 인덱스만으로 쿼리 결과를 가져올 수 있으므로, 디스크 I/O가 줄어들고 성능이 개선된다.
• 반드시 별도의 인덱스 형태가 아니라, 상황에 따라 복합 인덱스가 커버링 인덱스로 활용될 수 있다.
• 예시: 테이블에 (id, name, age, address) 컬럼이 있을 때, 자주 사용하는 쿼리가 SELECT name, age FROM table WHERE id = ?라면, (id, name, age) 복합 인덱스가 커버링 인덱스로 활용될 수 있다.

MySQL에서 EXPLAIN을 사용하여 쿼리 성능 분석하는 방법

쿼리 앞에 EXPLAIN 키워드를 추가하여 실행 계획을 확인.

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

• 주요 출력 항목
  • id: 쿼리의 실행 순서나 서브쿼리의 구분을 나타낸다.
  • select_type: 단순 SELECT, 서브쿼리, UNION 등 쿼리의 유형을 표시한다.
  • table: 해당 행이 어떤 테이블에 대한 정보인지를 나타낸다.
  • type: 조인 방식(예: ALL, index, range, ref, eq_ref, const 등)을 보여주며, ALL은 전체 테이블 스캔, const는 상수 검색 등으로 성능 차이를 나타낸다.
  • possible_keys: 쿼리에 사용 가능한 인덱스 목록.
  • key: 실제로 선택된 인덱스를 보여준다.
  • rows: 옵티마이저가 예측한 스캔할 행의 수로, 값이 낮을수록 좋다.
  • Extra: 추가적인 실행 정보(예: Using where, Using index, Using temporary, Using filesort 등)를 제공한다.
• 분석 방법
  • 전체 테이블 스캔 확인: type 컬럼이 ALL인 경우 전체 테이블을 스캔하므로 인덱스 사용 여부를 검토한다.
  • 인덱스 사용 여부: key 컬럼에 인덱스가 사용되고 있는지, 그리고 possible_keys와 비교하여 옵티마이저가 올바른 인덱스를 선택했는지 확인한다.
  • Extra 정보 분석: Using index가 표시되면 커버링 인덱스처럼 인덱스만으로 쿼리를 해결할 수 있다는 의미이고, Using temporary나 Using filesort가 나타나면 성능 최적화 대상임을 의미한다.
  • 행 수 확인: 예상보다 많은 행을 읽고 있다면, 조건이나 인덱스 설계를 다시 검토해야 한다.

인덱스 통계 정보

인덱스 통계 정보는 데이터베이스 옵티마이저가 효율적인 실행 계획을 세우는 데 핵심적인 역할을 한다고 보인다.

인덱스 통계 정보의 역할

옵티마이저는 테이블 내 데이터 분포, 각 인덱스의 선택도, 카디널리티(행의 수) 등의 통계를 참조하여, 어떤 인덱스를 사용하고 어떤 조인 순서를 선택할지 결정한다.
예를 들어, 특정 조건에 맞는 데이터가 전체 데이터의 1%만 존재한다면, 해당 조건에 적합한 인덱스를 사용해 빠르게 데이터를 찾을 수 있다. 반대로, 많은 행을 포함하는 조건의 경우 인덱스를 사용하는 것이 오히려 비용이 높아질 수 있으므로 옵티마이저는 다른 접근 방식을 선택할 수 있다.

최신 통계 정보의 중요성

데이터가 변경되면 인덱스 통계도 변화하게 된다. 만약 오래된 통계 정보를 기반으로 실행 계획이 수립된다면, 옵티마이저는 부정확한 추정을 하여 비효율적인 쿼리 실행 계획을 선택할 위험이 있다.
예를 들어, 실제 데이터 분포와 통계가 맞지 않으면, 옵티마이저가 전체 테이블 스캔을 선택하는 등 부적절한 결정을 내릴 수 있다. 따라서 정기적으로 통계 정보를 업데이트하여 최신 상태를 유지해야, 최적의 실행 계획이 도출되고 전반적인 쿼리 성능이 보장된다.

정리

• 옵티마이저는 인덱스 통계 정보를 활용해 최적의 실행 계획을 수립한다.
• 최신 통계가 없으면 실제 데이터 분포와 다르게 실행 계획이 선택되어 성능 저하가 발생할 수 있다.
• 정기적인 통계 업데이트로 정확한 실행 계획을 유지하는 것이 중요하다.