커버링 인덱스(Covering Index)란 무엇인가?

커버링 인덱스는 쿼리가 필요로 하는 모든 컬럼을 포함하는 인덱스를 말합니다. 이 인덱스를 사용하면 테이블의 실제 데이터 페이지에 접근하지 않고도 인덱스만으로 원하는 데이터를 조회할 수 있어 디스크 I/O를 절약할 수 있습니다.

MySQL 공식 문서 정의:

*"쿼리에서 검색되는 모든 컬럼을 포함하는 인덱스입니다. 인덱스 값을 전체 테이블 행을 찾는 포인터로 사용하는 대신, 쿼리는 인덱스 구조에서 값을 반환하여 디스크 I/O를 절약합니다. InnoDB는 MyISAM보다 더 많은 인덱스에 이 최적화 기술을 적용할 수 있습니다. 왜냐하면 InnoDB의 보조 인덱스에는 기본 키 컬럼도 포함되어 있기 때문입니다. InnoDB는 해당 트랜잭션이 끝날 때까지 트랜잭션에 의해 수정된 테이블에 대한 쿼리에 이 기술을 적용할 수 없습니다."*

단일 컬럼 인덱스와 다중 컬럼 인덱스 모두 커버링 인덱스로 활용될 수 있습니다. 적절한 인덱스 설계와 쿼리 작성으로 이 최적화 기법을 최대한 활용할 수 있습니다.

커버링 인덱스를 사용하는 이유

• 디스크 I/O 감소: 인덱스만으로 데이터를 조회하므로 디스크 접근 횟수가 줄어듭니다.
• 성능 향상: 디스크 I/O 감소로 쿼리 응답 속도가 빨라집니다.
• 잠금 경합 감소: 테이블 데이터 페이지에 대한 접근이 줄어들어 잠금 경합이 감소합니다.

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

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

• 정의: 테이블의 실제 데이터가 인덱스와 동일한 구조로 저장되는 인덱스입니다.
• 특징:
  • 테이블 당 하나만 존재합니다.
  • 기본 키(primary key)가 클러스터드 인덱스로 사용됩니다.
• 장점:
  • 인덱스를 통해 바로 데이터에 접근하므로 조회 속도가 빠릅니다.

비클러스터드 인덱스 (Non-clustered Index)

• 정의: 인덱스는 별도의 구조로 저장되고, 인덱스 엔트리는 데이터의 물리적 위치를 가리킵니다.
• 특징:
  • 테이블 당 여러 개의 비클러스터드 인덱스를 가질 수 있습니다.
• 단점:
  • 인덱스를 통해 데이터를 찾은 후 실제 데이터 페이지를 다시 조회해야 하므로 추가적인 I/O가 발생합니다.

MySQL 공식 문서 인용:

*"클러스터드 인덱스를 통해 행에 접근하는 것은 빠릅니다. 왜냐하면 인덱스 검색이 행 데이터가 있는 페이지로 직접 연결되기 때문입니다. 테이블이 큰 경우, 클러스터드 인덱스 아키텍처는 인덱스 레코드와 다른 페이지에 행 데이터를 저장하는 스토리지 구조와 비교했을 때 디스크 I/O 작업을 절약할 수 있습니다."*

커버링 인덱스 사용과 미사용 시 성능 비교

실험: 100만 건의 데이터로 임시 테이블 생성 후 성능 비교

1. 데이터 준비

   CREATE TABLE test_table (
       id INT PRIMARY KEY,
       col1 VARCHAR(100),
       col2 VARCHAR(100),
       col3 VARCHAR(100)
   );
   
   INSERT INTO test_table (id, col1, col2, col3)
   SELECT
       t1.number AS id,
       MD5(RAND()) AS col1,
       MD5(RAND()) AS col2,
       MD5(RAND()) AS col3
   FROM
       numbers AS t1  -- numbers 테이블은 1부터 1,000,000까지의 숫자를 가진 테이블이라고 가정
   LIMIT 1000000;

2. 인덱스 설정

   • 커버링 인덱스 생성

     CREATE INDEX idx_col1_col2 ON test_table (col1, col2);

   • 인덱스 미사용

     • 인덱스를 생성하지 않음.

3. 쿼리 실행 및 성능 측정

   • 커버링 인덱스 사용 시

     SELECT col1, col2 FROM test_table WHERE col1 = 'some_value';

     • 인덱스만으로 쿼리를 처리하여 빠른 응답 속도를 보입니다.

   • 인덱스 미사용 시

     SELECT col1, col2 FROM test_table WHERE col1 = 'some_value';

     • 전체 테이블 스캔이 발생하여 응답 시간이 느려집니다.

4. 결과

   • 커버링 인덱스를 사용하면 쿼리 성능이 현저히 향상됩니다.
   • 인덱스를 사용하지 않으면 대량의 데이터를 처리할 때 성능 저하가 발생합니다.

GROUP BY에서의 커버링 인덱스 활용

GROUP BY 절에서도 커버링 인덱스를 활용하여 성능을 개선할 수 있습니다.

• 인덱스 생성

  CREATE INDEX idx_col1_col2 ON test_table (col1, col2);

• 쿼리 실행

  SELECT col1, COUNT(*) FROM test_table GROUP BY col1;

• 설명

  • col1과 col2를 포함하는 인덱스를 생성하여 GROUP BY 시 인덱스만으로 결과를 도출합니다.
  • 디스크 I/O를 최소화하여 쿼리 성능을 높입니다.

참고 자료

1. MySQL 공식 문서 - InnoDB Index Types
2. MySQL 공식 문서 - Covering Indexes

인덱스(Index)란 무엇인가?

MySQL에서 인덱스는 데이터를 효율적으로 검색하기 위해 사용되는 자료구조입니다. 기본적으로 MySQL은 B-Tree 인덱스를 사용하며, 이는 데이터를 정렬된 상태로 저장하여 빠른 검색을 가능하게 합니다.

B-Tree 인덱스 구조

• 브랜치 노드: 각 노드는 여러 키와 자식 노드를 가지며, 데이터는 정렬된 형태로 저장됩니다.
• 정렬된 데이터: 인덱스는 앞의 키를 기준으로 뒤의 키가 정렬되어 있습니다.
• 빠른 검색: 로그 시간 복잡도로 데이터를 탐색할 수 있습니다.

카디널리티(Cardinality)란?

카디널리티는 데이터의 중복도를 나타내는 수치입니다.

• 높은 카디널리티 (High Cardinality): 중복도가 낮은 데이터 (예: 주민등록번호, 핸드폰 번호).
• 낮은 카디널리티 (Low Cardinality): 중복도가 높은 데이터 (예: 성별, 국가 코드).

카디널리티가 높을수록 인덱스의 효율성이 높아집니다.

인덱스 동작 방식

테이블에 인덱스를 설정할 때 고려해야 할 세 가지 경우가 있습니다.

1. 한 컬럼에 인덱스를 설정하는 경우

• 데이터 중복도가 낮은 컬럼을 선택하여 인덱스를 설정합니다.
• 높은 카디널리티를 가진 컬럼에 인덱스를 걸면 검색 성능이 향상됩니다.

2. 다중 컬럼에 각각 인덱스를 설정하는 경우

• 여러 컬럼에 단일 컬럼 인덱스를 각각 설정합니다.
• 쿼리 실행 시 MySQL은 Index Merge 최적화를 통해 인덱스 결과를 병합합니다.
• 참고: MySQL Index Merge Optimization

3. 다중 컬럼 인덱스(Multiple-column Index)를 설정하는 경우

• 여러 컬럼을 하나의 인덱스로 설정합니다.
• 인덱스 설정 시 카디널리티가 높은 순서로 컬럼을 나열하는 것이 좋습니다.

카디널리티에 따른 인덱스 성능 비교

3.1 다중 컬럼 인덱스 사용 조건

• WHERE 절에서 =, >, <=, BETWEEN, IN 등의 연산자를 사용하는 경우 B-Tree 인덱스가 효율적으로 작동합니다.

  *"B-Tree 자료 구조는 WHERE 절에서 =, >, <=, BETWEEN, IN 등의 연산자에 대응하여 특정 값, 값의 집합 또는 값의 범위를 빠르게 찾을 수 있게 해줍니다."*

• 조인 시 인덱스가 걸린 컬럼을 사용하면 성능이 향상됩니다.

  *"조인을 수행할 때 다른 테이블에서 행을 가져오기 위해 인덱스를 사용합니다. MySQL은 동일한 타입과 크기로 선언된 컬럼의 인덱스를 더 효율적으로 사용할 수 있습니다."*

• 인덱스된 컬럼의 데이터 타입과 크기가 동일하면 더욱 효율적입니다.

  • 예를 들어, VARCHAR(10)과 CHAR(10)은 동일한 크기로 간주되지만, VARCHAR(10)과 CHAR(15)는 그렇지 않습니다.

인덱스된 컬럼의 크기와 성능

인덱스된 컬럼의 크기가 작을수록 성능이 향상됩니다. 아래는 데이터 타입별 크기와 성능을 비교한 예시입니다.

풀 테이블 스캔을 피하는 방법

풀 테이블 스캔은 대량의 데이터를 처리할 때 성능 저하를 유발합니다. 이를 피하기 위해 다음을 고려해야 합니다.

1. 적절한 인덱스 설정: 자주 조회되는 컬럼에 인덱스를 설정합니다.
2. 쿼리 최적화: 불필요한 전체 테이블 검색을 피하도록 쿼리를 작성합니다.
3. 통계 정보 최신화: ANALYZE TABLE 명령어를 사용하여 통계 정보를 업데이트합니다.

참고 자료:

• MySQL 8.0 Reference Manual - Avoiding Full Table Scans